游标，视图（这两都不推荐用），临时表，事务，锁的概念，触发器，存储过程，sql中函数，sql的权限分配

ADO.NET复习(二)

游标

游标性能较差，仅做了解。是面向过程的，相当于c#里面的for(只是作用上),写法尼玛跟之前学的c一个套路

declare democursor cursor for
	select UserName,UserPass FROM UserInfo
	--打开游标
	open democursor
	--定义几个变量
	declare @UserName nvarchar(32)
	declare @UserPass nvarchar(32)
	--把游标指向的数据取出来，把数据放到后面的三个变量中
	fetch next from democursor into @UserName,@UserPass --这里只是拿到它的第0行数据

	while(@@FETCH_STATUS=0)--@@FETCH_STATUS=0的时候表示，取数据成功
	begin
		print @UserName
		print @UserPass
	print '----------'
	fetch next from democursor into @UserName,@UserPass
	end
close democursor --记得关闭游标,但是可以继续打开游标
deallocate democursor --释放游标，释放之后就不能open了

视图

--02 视图
create view VM_UserInfo as select * from UserInfo where ID>15
--select * from VM_UserInfo

临时表

简单点说临时表就是在创建表的时候加上**#**符号，临时表会在会话结束的时候删除。

--03 临时表
--select * into #TB from userinfo --常用的创建临时表的做法
--select * from #TB
--drop table #TB --必须释放临时表

关于共享锁和排他锁：https://blog.csdn.net/qq_32862391/article/details/55261179

简单说查询会给数据库加一把共享锁，其他人也可以添加锁进行操作，而删除，修改，添加的操作会添加一把排他锁，当其他人想添加锁的话是失败的(也不是失败，会类似等待，等待解锁之后 )

为了解决高并发中的多表连接查询，我们可以使用临时表

select * into #Orders from [Orders] as e
select * into #OrderBook from [OrderBook] as p

select ID , bookid from #Orders as E
left join #OrderBook as p on e.OrderID=p.OrderId

注意以上这些都是非全局表，全局表是两个#，##Orders这样，所有人都能访问的全局表、

事务

这里顺便提点一些sql里面的错误判断，也是try，catch

begin tran --开启一个事务
begin try
 update userinfo set UserName=N'牛逼' where id=3
 update userinfo set RegTime=N'牛逼' where id=3--这里肯定报错，因为我们这个时间不是字符串类型
commit tran--提交一个事务
end try
begin catch
rollback tran--回滚一个事务
end catch

执行结果（实际上啥也没有执行，因为try catch的缘故回滚了）

(1 行受影响)

(0 行受影响)

接下来如果我们在执行那句错误代码添加一个断点，然后执行这一句话：

select * from userinfo

这句话的执行会一直等待查询结果，因为事务对这个表添加了一个排他锁，如果写成下面这样:

select * from userinfo with(nolock)

不带锁，是可以查询出数据，但是数据是有问题的（id=3的userName=牛逼）

ADO.NET中执行事务

static void Main(string[] args)
       {
           using (SqlConnection conn = new SqlConnection(ConnectionStr()))
           {
               using (SqlCommand command = conn.CreateCommand())
               {
                   conn.Open();
                   SqlTransaction tran = conn.BeginTransaction();
                   try
                   {
                       command.CommandText = @" update userinfo set UserName=N'牛逼' where id=3

                   update userinfo set RegTime = N'牛逼' where id = 3";
                       command.ExecuteNonQuery();
                       command.Transaction = tran;
                       tran.Commit();
                   }
                   catch (Exception e)
                   {
                       tran.Rollback();
                       Console.WriteLine("报错啦");
                   }


               }
           }
       }

第二种写法

try
{
    using (TransactionScope scope = new TransactionScope())
    {
        using (SqlConnection conn = new SqlConnection(ConnectionStr()))
        {
            using (SqlCommand command = conn.CreateCommand())
            {
                conn.Open();
                command.CommandText = @" update userinfo set UserName=N'牛逼' where id=3

        update userinfo set RegTime = N'牛逼' where id = 3";
                command.ExecuteNonQuery();
                
            }
        }
        scope.Complete();//提交事务
    }
}
catch (Exception e)
{
    Console.WriteLine("异常了");
}

这里有一篇写得非常好的关于事务和锁的文章：https://www.cnblogs.com/knowledgesea/p/3714417.html

储存过程

存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

这篇文章详细的介绍了存储过程的用法：http://www.cnblogs.com/Jennie/archive/2011/07/28/2119496.html

下面我们用存储过程来实现我们的分页功能

--先判断有没有这个存储过程存在
if(exists(select * from sys.all_objects where name='Pro_UserInfo_GetPageUserInfos'))
	drop proc Pro_UserInfo_GetPageUserInfos
	go

--创建储存过程
create proc Pro_UserInfo_GetPageUserInfos
	@PageSize int,
	@PageIndex int,
	@TotalCount int output
as
	select * from (select *,row_number() over(order by ID asc)  as RowNum from UserInfo) as T
	where T.RowNum between ((@PageIndex-1)*@PageSize+1) and (@PageSize*@PageIndex)
--给输出变量赋值
	select @TotalCount=count(1) from UserInfo

执行这个存储过程

--调用分页存储过程
declare @total int--晟敏一个变量接收output的值
exec Pro_UserInfo_GetPageUserInfos 2,4 ,@total out--调用存储过程
print @total

ado中如何执行存储过程（控制台应用）

static void Main(string[] args)
       {
           string sqlconfig = "server=.;uid=sa;pwd=Long735~!~;database=book_shop3";
           using (SqlConnection conn = new SqlConnection(sqlconfig))
           {
               using (SqlCommand comm = conn.CreateCommand())
               {
                   conn.Open();
                   comm.CommandText = @"Pro_UserInfo_GetPageUserInfos";
                   comm.Parameters.Add(new SqlParameter("@PageSize", (object) 2));//这里不能用addwidthvalue
                   comm.Parameters.Add(new SqlParameter("@PageIndex", (object) 2));//这里不能用addwidthvalue
                   //创建一个输出参数
                   SqlParameter sqltotalParameter = new SqlParameter();
                   sqltotalParameter.ParameterName = "@TotalCount";
                   sqltotalParameter.SqlDbType = SqlDbType.Int;
                   //设置参数是输出方向
                   sqltotalParameter.Direction = ParameterDirection.Output;
                   comm.Parameters.Add(sqltotalParameter);

                   //执行存储过程
                   comm.CommandType = CommandType.StoredProcedure;
                   using (SqlDataReader reader = comm.ExecuteReader())
                   {
                       while (reader.Read())
                       {
                           Console.WriteLine(reader[0]+"  "+reader[1]);
                       }
                   }
               }
           }
       }

这里要注意的地方，用sqldatareader执行存储过程的时候，输出的参数一定要给他指定数据的类型。输出参数的值，只能等reader释放之后才能读取到参数的Value值，使用SqlDataAdapter就不存在这个问题

简单的一个执行sql存储过程的封装

public static DataTable ExcuteDataTable(string sqlText, CommandType commandType, params SqlParameter[] parameters)
       {
           using (SqlDataAdapter adapter = new SqlDataAdapter(sqlText, ConnectionStr()))
           {
               DataTable dt = new DataTable();
               adapter.SelectCommand.Parameters.AddRange(parameters);

               //兼容存储过程
               adapter.SelectCommand.CommandType = commandType;
               adapter.Fill(dt);
               return dt;
           }
       }

存储过程的优缺点

函数

数据库函数跟c#函数差不多，以下是它的格式

create function FUN_GetPassNum(@IDnum int)
returns int
as
begin
 declare @num int
 set @num=(select count(1) from UserInfo where Id>@IDnum)
 return @num
end

select dbo.FUN_GetPassNum(20)

我们在写存储过程的时候可以写方法调方法

表值函数，也就是返回值是table，但是不能用begin end

触发器

create trigger TR_TestTrigger on UserInfo
for insert
as
begin
	declare @ID int
	select @ID=Id from inserted --它是一张表
	update UserInfo set UName=UName+'sasas' where ID=@ID
end 

insert into UserInfo(UName,UPwd,SubTime,DelFlag,ModifiedOn) values('jjj',111,getdate(),0,GETDATE())

效果是在执行insert操作的时候会在它刚插入的那个UName加上 sasas

create trigger TR_TestTrigger_Delete on UserInfo
instead of delete
as
begin
	declare @ID int
	select @ID=Id from deleted --它是一张表
	update UserInfo set DelFlag=1 where ID=@ID
end 

delete from UserInfo where id=2

再执行删除操作是后触发DelFlag=1

关于查询的刚操作的数据的id（output inserted.ID）

insert into UserInfo(UName,UPwd,SubTime,DelFlag,ModifiedOn) output inserted.ID values('jjj2',111,getdate(),0,GETDATE())

权限