1.SQLServer登录

　　1.1SQlServer三层安全管理机制

　　　　包括登录名、数据库、操作数据库权限

　　　　一个登录名对应多个数据库用户，属于一对多；一个数据库用户对应一个数据库，属于一对一

　　1.2登录名（登陆账户）

　　　　用以登录SQL Server数据库服务器；一个数据库服务器可能有若干个数据库

　　1.3数据库用户

　　　　用于访问指定的数据库。登陆成功后，根据登录名找到对应的数据库用户，再去访问某个具体的用户数据库。找到该数据库用户对应的权限，操作数据库

2.添加登陆账户

　　登录账户sa是超级管理员账户，拥有数据库管理的最高权限，可以管理所有的数据库，安装时自动创建

1 use master
2 go
3 exec sp_addlogin 'YangSan','1001'
4 --账户名是“YangSan”,密码是“1001”，中间用逗号隔开
添加SQLServer账户

 3.向数据库用户授权

　　3.1授权的语法

　　　　grant 权限[on 表名] to 数据库用户　　　　

　　3.2收回权限的语法

　　　　remoke 权限[on 表名] to 数据库用户

4.变量分类

　　局部变量：仅在过程中使用

　　　　局部变量必须以标记@作为前缀，如@age

　　　　局部变量的使用也是先声明（使用declare），再赋值

　　全局变量：任何时候均可以使用

　　　　全部变量必须以标记@@作为前缀，如@@version（查询版本）

　　　　全局变量由系统定义和维护，我们只能读取，不能修改全局变量

5.局部变量定义与赋值

　　局部变量的定义语法：declare @变量名 数据类型

　　赋值方法： set @变量名=值    或   select @变量名=值 （使用select赋值要确保筛选出的记录只有一条）

 1 use SMDB
 2 go
 3 --声明变量
 4 declare @stuName varchar(20),@stuId int
 5 --赋值变量
 6 set @stuName='李铭'
 7 select @stuId=StudentId from Students where Studentname=@stuName
 8 --事先查询下李铭的信息
 9 select StudentName,StudentId,Gender from Students where StudentName=@stuName
10 
11 --查询李铭学号前后学员的信息
12 select StudentName,StudentId,Gender from Students 
13 where StudentId=(@stuId-1) or StudentId=@stuId or StudentId=(@stuId+1)
14 order by StudentId DESC
15 
16 select StudentId,StudentName from Students
17 where StudentId in (@stuId-1,@stuId,@stuId+1)
18 
19 select @@servername as '服务器名称'
变量的简单使用

6.set与select比较

使用场景 set select
同时对多个变量赋值 不支持 支持
表达式返回多个值时 出错 将返回的最后一个值赋给变量
表达式未返回值时　　 变量被赋NULL值 变量保持原值

　　就赋值而言，set能做的select都能做。查询尽量多用select

 1 declare @stuAddress nvarchar(100),@stuName nvarchar(100)
 2 --set @stuAddress='昆山',@stuName='张三' --不允许这样赋值
 3 select @stuAddress='昆山',@stuName='张三'  --允许
 4 
 5 --set @stuAddress = (select StudentAddress from Students) --不允许
 6 select @stuAddress = StudentAddress from Students --得到最后一个
 7 --set @stuAddress = (select StudentAddress from Students where 1<0) --Null值
 8 select @stuAddress = StudentAddress from Students where 1<0 --保持原值
 9 
10 select @stuAddress as '地址',@stuName as '姓名'
set与select的使用

7.全局变量的使用

变量　　 含义
@@ERROR 最后一个T—SQL错误的错误号
@@IDENTITY 最后一次插入的标识值
@@LANGUAGE 当前使用的语言的名称
@@MAX_CONNECTIONS 可以创建的同时连接的最大数目
@@ROWCOUNT 受上一个SQL语句影响的行数
@@SERVERNAME 本地服务器的名称
@@TRANSCOUNT 当前连接打开的事务数
@@VERSION SWL Server的版本信息

8.错误号的使用

　　@@error 全局变量保留错误号

use SMDB 
go
select * from StudentClass
--插入一条学员信息，但班级为10，当前没有此班级
--会引发外键冲突
insert into Students(StudentName,Gender,Age,Birthday,StudentIdNo,CardNo,PhoneNumber,STudentAddress,ClassId)
values('王小欣','男',24,'1992-9-9',42109876543456,2011129876,'0512-88765678','昆山',10)

print @@error
@@error的使用
 1             //删除一名学生，但成绩表还有改学生信息，是不能被删除的
 2             string sql = "delete from Students where StudentId=" + studentId;
 3             try
 4             {
 5                 return SQLHelper.Update(sql);
 6             }
 7             catch (SqlException ex)
 8             {
 9                 if (ex.Number == 547)
10                     throw new Exception("该学号被其他实体引用，不能直接删除该学员对象！");
11                 else
12                     throw new Exception("数据库操作出现异常！具体信息：\r\n" + ex.Message);
13             }
14             catch (Exception ex)
15             {
16                 throw ex;
17             }
18
@@error在C#中的应用

 9.数据类型转换

　　convert(数据类型，表达式，样式)

　　　　第三个参数可以省略，它一般用于日期类型转换为字符来行，或浮点类型数据转换为字符类型

　　cast(表达式 as 数据类型)

 数据类型转换及日期函数

10.if_else语句

 1 --SQL中的if语句
 2 if(条件)
 3     begin
 4         语句1
 5         语句2
 6         ......
 7     END
 8 else
 9     begin
10         语句1
11         语句2
12         ......
13     end
14 
15 --注：else是可选部分
16 --如果有多条语句，才需要begin-end语句块，相当于c#中的{ }
if_else语句
 1 use SMDB 
 2 go
 3 declare @avg int
 4 select @avg=avg(CSharp) from ScoreList 
 5 inner join Students on Students.StudentId=ScoreList.StudentId
 6 where Students.ClassId=1
 7 print 'CSharp平均分：'+convert(varchar(20),@avg)
 8 --执行判断
 9 if(@avg>=80)
10     print '成绩合格'
11 else
12     print '成绩不合格'
eg

11.while语句

 1 --SQL中的while语句
 2 while(条件)
 3     begin
 4         语句1
 5         语句2
 6         ......
 7         break
 8     end
 9 --break表示跳出循环
10 --如果有多条语句，才需要begin-end语句块
while语句

12.case_end语句

1 case
2     when 条件1 then 结果1
3     when 条件2 then 结果2
4     ......
5     else 其他结果
6 end
7 --else表示case中所有when条件均不为then是返回的结果，相当于C#中的default
8 --如果省略else且when条件都为false时，case语句返回null
case_end语句
 1 use SMDB 
 2 go
 3 select 学号=Students.StudentId,姓名=Studentname,C#成绩=CSharp,
 4        等级=case
 5                 when CSharp>=90 then 'A'
 6                 when CSharp between 80 and 89 then 'B'
 7                 when CSharp between 70 and 79 then 'C'
 8                 when CSharp between 60 and 69 then 'D'
 9                 else '不及格'
10             end
11 from ScoreList inner join Students on ScoreList.StudentId=Students.StudentId
eg

 13.子查询

　　语法规范：子查询（总是用括号括起来）

　　　　select ... from 表1 where 字段1 比较运算符（子查询）

　　子查询注意：

　　　　将子查询与比较运算符联合使用，必须保证子查询返回的值不能多于1个

　　　　子查询是一个嵌套在select、insert、update、update或delete语句或其他子查询中的查询

　　理解子查询执行过程：

　　　　首先，执行小括号中的子查询，返回的结果是所有子查询的结果

　　　　其次，才开始执行外围的父查询，返回查询的最终结果

 1 use SMDB
 2 go
 3 --查询学号在赵小金后面的学员信息
 4 
 5 --方法一：定义变量，可读性好
 6 declare @stuId int
 7 select @stuId=StudentId from Students where Studentname='赵小金'
 8 select StudentId,Studentname from Students where StudentId>@stuId
 9 
10 --父查询中嵌套子查询
11 select StudentId,Studentname from Students 
12 where StudentId>(select StudentId from STudents where Studentname='赵小金')
子查询

14.in子查询（范围查询 in 后面类似数组）

　　in后面的子查询可以返回多条记录

　　常用in替换等于=的比较子查询 

　　not in 表示不在此范围

 1 use SMDB
 2 go
 3 select Students.StudentId,StudentName from Students
 4 inner join ScoreList on ScoreList.StudentId=Students.StudentId
 5 where SQLServerDB>80
 6 
 7 select StudentId,StudentName from Students
 8 where StudentId in (select StudentId from ScoreList where SQLServerDB>80)
 9 
10 --查询没有参加考试的学员
11 select StudentId,Studentname from Students
12 where StudentId not in(select StudentId from ScoreList)
in

15.exists子查询（判断是否存在）

　　if exists (子查询)

　　   语句

　　查询结果非空，记录数1条以上，则exists子查询返回真，否则返回假

　　not exists 不存在

1 use master
2 go
3 if exists(select * from sysdatabases where name='SMDB')
4 print '数据库已存在'
5 
6 use SMDB
7 go
8 if    exists(select * from sysobjects where name='Students')
9 print '数据表已存在'
exists

 16.视图

　　视图的概念：

    1. 是存储在服务器端的一个查询块，是一张虚拟表
    2. 表示一张表的部分数据或多张表的综合数据
    3. 其结构和数据是建立在对表的查询基础上
    4. 视图的使用，跟对普通的表的查询使用完全一样

 　　视图中不存放数据：数据存放在视图所引用的原始表中

　　表视图的多样性：一个或多个原始表，根据不同用户的不同需求，可以创建不同的视图

　　视图的用途：

    1. 筛选表中的行
    2. 防止未经许可的用户访问敏感数据
    3. 降低数据库的复杂程度
    4. 将多个物理数据库抽象成一个逻辑数据库

 　　视图可以手动创建，简单快捷，引用查询视图像引用表一样简单快捷

　　select * from V_Stu 

17.创建和使用视图

create view view_StuInfo
    as
    <select 语句>
T-SQL语句创建视图规范
if exists(select * from sysobjects where name=view_StuInfo)
    drop view view_StuInfo
T-SQL语句删除视图规范
select * from view_StuInfo
T-SQL语句查看视图规范
 1 use SMDB
 2 go
 3 --判断视图是否存在
 4 if exists(select * from sysobjects where name='V_StuScore')
 5 drop view V_StuScore
 6 go
 7 --创建视图
 8 create view V_StuScore
 9 as
10     select top 100 ID=Students.StudentId,姓名=StudentName,班级=ClassName,C#=CSharp,
11     SQL=SQLServerDB,总分=(CSharp+SQLServerDB) from Students 
12     inner join ScoreList on Students.StudentId=ScoreList.StudentId
13     inner join StudentClass on StudentClass.ClassId=Students.ClassId
14     order by 总分 DESC
15 go
16 
17 select * from V_StuScore order by ID asc
创建与引用视图实例

18.视图中select语句不能包括下列内容（视图相对于存储过程的不足）

  1. order by子句，除非在select语句得选择列表中有top子句
  2. into子句
  3. 引用临时表或变量：视图只能查询，不能增删改，视图仅表示一种查询的逻辑关系，视图内不包括实体数据

19.存储过程

　　概念：

    1. 预先存储好的SQL程序
    2. 保存在SQL Server中（跟视图一样）
    3. 通过名称和参数执行：可以在数据库服务器端直接调用（DBA）、供应用程序调用可带参数、可返回结果
    4. 可以包含数据操纵语句（单个select语句或select语句块）、变量、逻辑控制语句等

　　优点：

    1. 执行速度块
    2. 允许模块化程序设计
    3. 提高系统安全性
    4. 减少网络流通量
    5. 视图和存储过程的重要优点：安全且执行速度块

　　SQL语句和存储过程的比较：

    1. 应用程序发送SQL的过程：  传输语句——>>>语法检查——>>>语法优化——>>>语句编译——>>>语法执行
    2. 调用存储过程或视图过程：  传输参数——>>>语句执行

　　存储过程分类：

    1. 系统存储过程：以“sp_”开头，由SQL Server创建、管理和使用，存放在master数据库中，类似于C#语言类库中的方法
    2. 扩展存储过程：以“xp_”开头，使用编程语言（C#）创建的外部存储过程，以DLL形式单独存在  xp_cmdshell：可以执行DOS命令下的一些操作，以文本方式返回任何输出
    3. 用户自定义存储过程：由用户在自己的数据库中创建的存储过程，类似于C#中程序员自定义的方法
系统存储过程　　 说明
sp_databases 列出数据库上的所有数据库
sp_helpdb 报告有关指定数据库或所有数据库的信息
sp_renamedb 更改数据库的名称
sp_tables 返回当前环境下可查询的对象的列表
sp_columns 返回某个表列的信息
sp_help 查看某个表的所有信息
sp_helpconstraint 查看某个表的约束
sp_helpindex 查看某个表的索引
sp_stored_procedures 列出当前环境中的所有存储过程
sp_password 添加或修改登录账户的密码
sp_helptext 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本

　　调用存储过程：exec 过程名 [参数]

　　　　如果执行存储过程的语句是批处理中的第一个语句，则可以不指定exec关键字

20.无参数存储过程

1 create proc[EDURE] 存储过程名
2     @参数1 数据类型=默认值 output,
3     ......
4     @参数n 数据类型=默认值 output
5 as
6     SQL语句
7 go
定义存储过程的语法

　　参数：参数可选、分输入输出、输入参数允许有默认值

 1 use SMDB
 2 go
 3 if exists(select * from sysobjects where name='usp_QueryScore')
 4 drop proc usp_QueryScore
 5 go
 6 create proc usp_QueryScore
 7 as
 8     select ID=Students.StudentId,姓名=StudentName,班级=ClassName,C#=CSharp,SQL=SQLServerDB,
 9         总分=(CSHarp+SQLServerDB) from Students
10         inner join StudentClass on StudentClass.ClassId=Students.ClassId
11         inner join ScoreList on ScoreList.StudentId=Students.StudentId
12         order by 总分 DESC
13 
14     select 班级=ClassName,C#平均分=AVG(CSharp),SQL平均分=AVG(SQLServerDB)
15     from Students
16     inner join StudentClass on StudentClass.ClassId=Students.ClassId
17     inner join ScoreList on ScoreList.StudentId=Students.StudentId
18     group by ClassName order by ClassName
19     --group by排序的内容必须出现在select之后才可以被引用
20 
21     select StudentClass.ClassId,C#平均分=AVG(CSharp),SQL平均分=AVG(SQLServerDB)
22     from Students
23     inner join StudentClass on StudentClass.ClassId=Students.ClassId
24     inner join ScoreList on ScoreList.StudentId=Students.StudentId
25     group by StudentClass.ClassId order by StudentClass.ClassId
26 
27     select StudentClass.ClassId,C#平均分=AVG(CSharp),SQL平均分=AVG(SQLServerDB)
28     into #ScoreTemp
29     from Students
30     inner join StudentClass on StudentClass.ClassId=Students.ClassId
31     inner join ScoreList on ScoreList.StudentId=Students.StudentId
32     group by StudentClass.ClassId order by StudentClass.ClassId
33     --将临时表和班级表关联查询
34     select ClassName,C#平均分,SQL平均分 from #ScoreTemp
35     inner join  StudentClass on StudentClass.ClassId=#ScoreTemp.ClassId
36 go
37 
38 exec usp_QueryScore
创建无参数存储过程&&分组排序&&临时表

 21.带输入参数存储过程

 1 use SMDB
 2 go
 3 if exists(select * from sysobjects where name='usp_QueryScore')
 4 drop procedure usp_QueryScore
 5 go 
 6 --创建带参数的存储过程
 7 create proc usp_QueryScore
 8 @CSharp_Min int=60,     --默认参数输入
 9 @CSharp_Max int=80
10 as
11     select Students.StudentId,StudentName,CSharp,SQLServerDB 
12     from Students
13     inner join ScoreList on Students.StudentId=ScoreList.StudentId
14     where CSharp>@CSharp_Min and CSharp<@CSharp_Max
15 go
16 
17 select * from ScoreList
18 --调用带参数的存储过程
19 exec usp_QueryScore 65,85                             --默认要和定义的参数一一对应
20 exec usp_QueryScore @CSharp_Max=80,@CSharp_Min=60     --也可以不一一对应但要声明参数名称
21 exec usp_QueryScore                    --两个参数都是用默认参数
22 exec usp_QueryScore 65                --只写一个参数，@CSharp_Min=65,C@Sharp_Max默认
23 exec usp_QueryScore dsefault,90        --第一个参数默认，@CSharp_Max为90
24 exec usp_QueryScore @CSharp_Min=70  --第一个参数为70，第二个参数默认
带输入参数存储过程、默认输入及调用方式

22.带输入输出存储过程

 1 use SMDB
 2 go
 3 if exists(select * from sysobjects where name='usp_QueryScore')
 4 drop procedure usp_QueryScore
 5 go 
 6 --创建带参数的存储过程
 7 create proc usp_QueryScore
 8 @AbsentCount int output,  --缺考总人数
 9 @FailedCount int output,  --不及格总人数
10 @CSharp_Min int=60,     --默认参数输入
11 @CSharp_Max int=80
12 as
13     select Students.StudentId,StudentName,CSharp,SQLServerDB 
14     from Students
15     inner join ScoreList on Students.StudentId=ScoreList.StudentId
16     where CSharp>@CSharp_Min and CSharp<@CSharp_Max
17     --输出参数要在SQL语句中赋值
18     select @AbsentCount=count(*) from Students        --查询缺考总人数
19         where StudentId not in (select StudentId from ScoreList)
20     select @FailedCount=count(*) from ScoreList where CSharp<@CSharp_Min --查询不及格总人数
21 go
22 
23 --调用带输出参数的存储过程
24 declare @Absent int,@Failed int  --首先定义输出参数
25 exec usp_QueryScore @Absent output,@Failed output,@CSharp_Min=70,@CSharp_Max=99
26 select 缺考总人数=@Absent,不及格总数=@Failed
带输入输出存储过程

 23.事务

　　23.1事务的概念：

  1. 事务是作为单个逻辑工作单元执行的一系列操作
  2. 多个操作作为一个整体向系统提交，要么都执行、要么都不执行
  3. 事务是一个不可分割的工作逻辑单元
  4. 通俗讲，如果一个事务中包括两条语句，如果第一条语句执行成功，第二条语句执行失败，会重新撤销第一条语句所执行的结果

　   23.2事务的四个属性

  1. 原子性：事务是一个完成的操作，事务的各部操作是不可分的，要么都执行，要么都不执行
  2. 一致性：当事务完成时，数据必须都处于一致状态
  3. 隔离性：并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务
  4. 永久性：事务完成后，它对数据库的修改被永久保存

　　23.3事务分类

  1. 显式事务：用begin transaction明确指定事务的开始，是最常用的事务类型
  2. 隐式事务：通过设置set implicit_transactions on语句，将隐性事务模式设置为打开；其后的T-SQL语句自动启动一个新事务，提交或回滚一个事务后，下一个T-SQL语句又将启动一个新事务
  3. 自动提交事务：SQL Server的默认模式，每条单独的T-SQL语句视为一个事务 

　　23.4使用SQL语句管理事务的基本步骤

  1. 开始事务：begin transaction
  2. 提交事务：commit transaction
  3. 回滚（撤销）事务：rollback transaction
  4. 一旦事务提交或回滚，则事务结束
  5. 事务处理中的关键问题主要是对insert、update、delete语句实时跟踪
 1 use StudentManager
 2 go
 3 select * from CardAccount
 4 go
 5 declare @errorSum int  --定义变量，用于累计事务执行中的错误
 6 set @errorSum=0   --初始化为0，代表没有错误
 7 begin transaction
 8     begin
 9         --转出
10         update CardAccount set CurrentMoney=CurrentMoney-1000 where StudentId=100001
11         set @errorSum=@errorSum+@@ERROR
12         --转入
13         update CardAccount set CurrentMoney=CurrentMoney+1000 where StudentId=100002
14         set @errorSum=@errorSum+@@ERROR
15         if(@errorSum=0)
16             commit transaction
17         else
18             rollback transaction
19     end
20 go
21 select * from CardAccount
22 go
事务

　　23.5判断某条语句执行是否出错的方法

  1. 使用全局变量@@ERROR
  2. @@ERROR只判断当前一条T-SQL语句执行是否有错
  3. 为了判断事务中所有T-SQL语句是否有错，可以对错误进行累计
  4. set @errorSum=@errorSum+@@error
 1 use StudentManager
 2 go
 3 if exists(select * from sysobjects where name='usp_TransferAccounts')
 4 drop proc usp_TransferAccounts
 5 go
 6 create proc usp_TransferAccounts
 7 @intputAccount int,  --转入账户
 8 @outputAccount int,  --转出账户
 9 @transferMoney int --交易金额
10 as
11     declare @errorSum int  --定义变量，用于累计事务执行中的错误
12     set @errorSum=0   --初始化为0，代表没有错误
13     begin transaction
14         begin
15             --转出
16             update CardAccount set CurrentMoney=CurrentMoney-@transferMoney 
17                 where StudentId=@outputAccount
18             set @errorSum=@errorSum+@@ERROR
19             --转入
20             update CardAccount set CurrentMoney=CurrentMoney+@transferMoney 
21                 where StudentId=@intputAccount
22             set @errorSum=@errorSum+@@ERROR
23             if(@errorSum=0)
24                 commit transaction
25             else
26                 rollback transaction
27         end
28 go
29 --测试成功的转换
30 select * from CardAccount 
31 exec usp_TransferAccounts 100002,100001,100
32 select * from CardAccount
33 --测试失败的转换
34 exec usp_TransferAccounts 100002,100001,1000
35 select * from CardAccount
将事务引用在存储过程中

 24.索引

　　添加的索引越多，占用的存储空间越多。每插入、删除、修改一条记录，数据库有可能会更改很多。

　　24.1分类

　　　　聚集索引：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个

    1. 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型
    2. 主键索引要求主键中的每一值是唯一的，并且不能为空

　　　　非聚集索引（nonclustered index ）：非聚集索引指定表的逻辑顺序

    1. 数据可以存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针
    2. 可以有多个，小于249
    3. 唯一索引：唯一索引不允许两行具有相同的索引值

 25.在ADO.NET中添加事务

        public static int UpdateByTran(List<string> sqlList)
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.Transaction = conn.BeginTransaction();//开启事务
                int result = 0;
                foreach (string sql in sqlList)
                {
                    cmd.CommandText = sql;
                    result += cmd.ExecuteNonQuery();//执行sql语句
                }
                cmd.Transaction.Commit();//提交事务
                return result;
            }
            catch (Exception ex)
            {
                //写入日志
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();//回滚事务
                }
                throw new Exception("调用事务更新方法时出现异常:" + ex.Message);
            }
            finally
            {
                if (cmd.Transaction!=null)
                {
                    cmd.Transaction = null;
                }
                conn.Close();
            }
        }
C#中事务的使用
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
    class SQLHelper
    {
        private static string ConnStr = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=****";

        /// <summary>
        /// 返回单一结果查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSignalResult(string sql)
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            SqlCommand cmd = new SqlCommand(sql,conn);
            conn.Open();
            object re = cmd.ExecuteScalar();
            conn.Close();
            return re;
        }

        /// <summary>
        /// 更新数据操作（删、增、改）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetUpdate(string sql)
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            SqlCommand cmd = new SqlCommand(sql,conn);
            conn.Open();
            int re = cmd.ExecuteNonQuery();
            conn.Close();
            return re;
        }

        /// <summary>
        /// 返回一个结果集的查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GerReader(string sql)
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 多条sql语句事务的应用
        /// </summary>
        /// <param name="sqlList">多条sql语句</param>
        /// <returns></returns>
        public static int UpdateByTran(List<string> sqlList)
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.Transaction = conn.BeginTransaction();//开启事务
                int result = 0;
                foreach (string sql in sqlList)
                {
                    cmd.CommandText = sql;
                    result += cmd.ExecuteNonQuery();//执行sql语句
                }
                cmd.Transaction.Commit();//提交事务
                return result;
            }
            catch (Exception ex)
            {
                //写入日志
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();//回滚事务
                }
                throw new Exception("调用事务更新方法时出现异常:" + ex.Message);
            }
            finally
            {
                if (cmd.Transaction!=null)
                {
                    cmd.Transaction = null;
                }
                conn.Close();
            }
        }
    }
}
SqlHelper_连接字符串直接写在类中
        public DataTable ExecuteDataTable1(string conStr, string sql, CommandType type, params SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(sql, con))
                    {
                        command.Connection = con;
                        command.CommandType = type;
                        command.CommandText = sql;
                        if (!(param == null || param.Length == 0))
                        {
                            foreach (SqlParameter parm in param)
                            {
                                command.Parameters.Add(parm);
                            }
                        }
                        //添加参数
                        adapter.SelectCommand = command;
                        //1.打开链接，如果连接没有打开，则它给你打开；如果打开，就算了
                        //2.去执行sql语句，读取数据库
                        //3.sqlDataReader,把读取到的数据填充到内存表中
                        adapter.Fill(dt);
                    }
                }
            }
            return dt;
        }
sql语句或存储过程返回DataTable

26.清空数据库日志

    --MES_DB_Utility修改为自己想要操作的数据库
    --MES_DB_IO_log修改为自己想要操作的数据库日志文件
    USE [master]
    GO
    ALTER DATABASE MES_DB_Utility SET RECOVERY SIMPLE WITH NO_WAIT
    GO
    ALTER DATABASE MES_DB_Utility SET RECOVERY SIMPLE   --简单模式
    GO
    USE MES_DB_Utility
    GO
    DBCC SHRINKFILE (N'MES_DB_IO_log' , 0, TRUNCATEONLY)  --设置压缩后的日志大小为2M，可以自行指定
    GO
    USE [master]
    GO
    ALTER DATABASE MES_DB_Utility SET RECOVERY FULL WITH NO_WAIT
    GO
    ALTER DATABASE MES_DB_Utility SET RECOVERY FULL  --还原为完全模式
    GO
清空数据库日志

 27.ADO.NET命令执行方式比较

  1. 使用完整的SQL语句：Sql语句编写困难、容易出错；网络传输不安全，很容易发生“注入式攻击”；执行效率低
  2. 使用带参数的SQL语句：Sql语句编写容易，网络传输安全，执行效率一般
  3. 调用存储过程：不需要编写SQL语句，网络传输非常安全，执行效率非常高

28.一般SQL语句与带参数的SQL语句

 1         public static int InsertStudent(Student objStu)
 2         {
 3             string sql = $@"insert into Students (StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)values 
 4                       ('{objStu.StudentName}','{objStu.Gender}','{objStu.Birthday}',{objStu.StudentIdNo},{objStu.Age},'{objStu.PhoneNumber}','{objStu.StudentAddress}',{objStu.ClassId})";
 5             return SQLHelper.GetUpdate(sql);
 6         }
 7 
 8         public static int GetUpdate(string sql,params SqlParameter[] paras)
 9         {
10             SqlConnection conn = new SqlConnection(ConnStr);
11             SqlCommand cmd = new SqlCommand(sql,conn);
12             try
13             {
14                 if (paras.Length > 0)
15                 {
16                     cmd.Parameters.AddRange(paras);
17                 }
18                 conn.Open();
19                 int re = cmd.ExecuteNonQuery();
20                 conn.Close();
21                 return re;
22             }
23             catch (Exception ex)
24             {
25                 return -1;
26             }
27         }
一般SQL语句
 1         public static int InsertStudent_Paras(Student objStu)
 2         {
 3             string sql = "insert into Students (StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)values"; 
 4             sql+=" (@StudentName,@Gender,@Birthday,@StudentIdNo,@Age,@PhoneNumber,@StudentAddress,@ClassId)";
 5             SqlParameter[] paras = new SqlParameter[] {
 6                 new SqlParameter("@Studentname",objStu.StudentName),
 7                 new SqlParameter("@Gender",objStu.Gender),
 8                 new SqlParameter("@Birthday",objStu.Birthday),
 9                 new SqlParameter("@StudentIdNo",objStu.StudentIdNo),
10                 new SqlParameter("@Age",objStu.Age),
11                 new SqlParameter("@PhoneNumber",objStu.PhoneNumber),
12                 new SqlParameter("@StudentAddress",objStu.StudentAddress),
13                 new SqlParameter("@ClassId",objStu.ClassId)
14             };
15             return SQLHelper.GetUpdate(sql, paras);
16         }
17 
18         public static int GetUpdate(string sql,params SqlParameter[] paras)
19         {
20             SqlConnection conn = new SqlConnection(ConnStr);
21             SqlCommand cmd = new SqlCommand(sql,conn);
22             try
23             {
24                 if (paras.Length > 0)
25                 {
26                     cmd.Parameters.AddRange(paras);
27                 }
28                 conn.Open();
29                 int re = cmd.ExecuteNonQuery();
30                 conn.Close();
31                 return re;
32             }
33             catch (Exception ex)
34             {
35                 return -1;
36             }
37         }
带参数的SQL语句

 29.ADO.NET调用存储过程

  1. 先在服务器端编写需要的存储过程，如果有参数，需要@定义好参数
  2. 编写调用存储过程的通用数据访问方法。注意cmd.CommandType=CommandType.StoredProcedure;  //声明是存储过程cmd.CommandText=text;  //指定存储过程的名称cmd.Parameters.AddRange(paras);  //添加输入参数
  3. 编写调用存储过程的方法
 1         /// <summary>
 2         /// 调用存储过程返回结果集（可以是多条结果集）
 3         /// </summary>
 4         /// <param name="procedureName">存储过程名</param>
 5         /// <param name="paras">参数</param>
 6         /// <returns></returns>
 7         public static SqlDataReader GetReader(string procedureName, SqlParameter[] paras)
 8         {
 9             SqlConnection conn = new SqlConnection();
10             SqlCommand cmd = new SqlCommand();
11             try
12             {
13                 conn.Open();
14                 cmd.Connection = conn;
15                 cmd.CommandType = CommandType.StoredProcedure;
16                 cmd.CommandText = procedureName;
17                 cmd.Parameters.AddRange(paras);
18                 return cmd.ExecuteReader(CommandBehavior.CloseConnection);
19             }
20             catch (Exception ex)
21             {
22                 conn.Close();
23                 throw ex;
24             }
25         }
调用存储过程返回结果集（可以是多条结果集）
 1         //执行存储过程（多个返回值与多个返回列表）
 2         public List<StudentExt> GetScoreInfo(string className, out Dictionary<string, string> dicParam, out List<string> absentList)
 3         {
 4             //定义输入参数，参数名+参数值
 5             SqlParameter inputClassName = new SqlParameter("@classname",className);
 6             inputClassName.Direction = ParameterDirection.Input;
 7             //定义输出参数，参数名+参数类型
 8             SqlParameter outStuCount = new SqlParameter("@stuCount", SqlDbType.Int);
 9             outStuCount.Direction = ParameterDirection.Output;
10             SqlParameter outAvgDB = new SqlParameter("@avgDB",SqlDbType.Int);
11             outAvgDB.Direction = ParameterDirection.Output;
12             //执行查询
13             SqlParameter[] paras = new SqlParameter[] { inputClassName,outAvgDB};
14             SqlDataReader objReader = SQLHelper.GetReader("usp_ScoreQuery",paras);
15             //读取考试成绩列表
16             List<StudentExt> scoreList = new List<StudentExt>();
17             while (objReader.Read())
18             {
19                 scoreList.Add(new StudentExt()
20                 {
21                     StudentId=objReader["StudentId"].ToString(),
22                     Age=Convert.ToInt32(objReader["Age"])
23                 });
24             }
25             //读取缺考人员列表
26             absentList = new List<string>();
27             if (objReader.NextResult())
28             {
29                 while (objReader.Read())
30                 {
31                     absentList.Add(objReader["StudentName"].ToString());
32                 }
33             }
34             objReader.Close();
35             //当执行完后，输出参数自动的已经赋值啦
36             //获取输出参数
37             dicParam = new Dictionary<string, string>();
38             dicParam["StuCount"] = outStuCount.Value.ToString();
39             dicParam["AvgDB"] = outAvgDB.Value.ToString();
40             return scoreList;
41         }
执行存储过程（多个返回值与多个返回列表）

 30.SQLHelper类中方法的总结

  1. 执行格式化的SQL语句（使用占位符）
  2. 执行带参数的SQL语句（类似存储过程参数，效率和安全性较高）
  3. 执行带参数的存储过程（效率最高，安全性最高）
  4. 启动事务执行更新（保证数据的一致性）
  5. 项目开发中应按照以上分类编写SQLHelper类中的方法，并在适时场合调用不同的的方法