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”,中间用逗号隔开
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 '姓名'
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
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
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#中的{ }
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 '成绩不合格'
11.while语句
1 --SQL中的while语句
2 while(条件)
3 begin
4 语句1
5 语句2
6 ......
7 break
8 end
9 --break表示跳出循环
10 --如果有多条语句,才需要begin-end语句块
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
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
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)
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 '数据表已存在'
16.视图
视图的概念:
-
- 是存储在服务器端的一个查询块,是一张虚拟表
- 表示一张表的部分数据或多张表的综合数据
- 其结构和数据是建立在对表的查询基础上
- 视图的使用,跟对普通的表的查询使用完全一样
视图中不存放数据:数据存放在视图所引用的原始表中
表视图的多样性:一个或多个原始表,根据不同用户的不同需求,可以创建不同的视图
视图的用途:
-
- 筛选表中的行
- 防止未经许可的用户访问敏感数据
- 降低数据库的复杂程度
- 将多个物理数据库抽象成一个逻辑数据库
视图可以手动创建,简单快捷,引用查询视图像引用表一样简单快捷
select * from V_Stu
17.创建和使用视图
create view view_StuInfo
as
<select 语句>
if exists(select * from sysobjects where name=view_StuInfo)
drop view view_StuInfo
select * from view_StuInfo
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语句不能包括下列内容(视图相对于存储过程的不足)
- order by子句,除非在select语句得选择列表中有top子句
- into子句
- 引用临时表或变量:视图只能查询,不能增删改,视图仅表示一种查询的逻辑关系,视图内不包括实体数据
19.存储过程
概念:
-
- 预先存储好的SQL程序
- 保存在SQL Server中(跟视图一样)
- 通过名称和参数执行:可以在数据库服务器端直接调用(DBA)、供应用程序调用可带参数、可返回结果
- 可以包含数据操纵语句(单个select语句或select语句块)、变量、逻辑控制语句等
优点:
-
- 执行速度块
- 允许模块化程序设计
- 提高系统安全性
- 减少网络流通量
- 视图和存储过程的重要优点:安全且执行速度块
SQL语句和存储过程的比较:
-
- 应用程序发送SQL的过程: 传输语句——>>>语法检查——>>>语法优化——>>>语句编译——>>>语法执行
- 调用存储过程或视图过程: 传输参数——>>>语句执行
存储过程分类:
-
- 系统存储过程:以“sp_”开头,由SQL Server创建、管理和使用,存放在master数据库中,类似于C#语言类库中的方法
- 扩展存储过程:以“xp_”开头,使用编程语言(C#)创建的外部存储过程,以DLL形式单独存在 xp_cmdshell:可以执行DOS命令下的一些操作,以文本方式返回任何输出
- 用户自定义存储过程:由用户在自己的数据库中创建的存储过程,类似于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事务的概念:
- 事务是作为单个逻辑工作单元执行的一系列操作
- 多个操作作为一个整体向系统提交,要么都执行、要么都不执行
- 事务是一个不可分割的工作逻辑单元
- 通俗讲,如果一个事务中包括两条语句,如果第一条语句执行成功,第二条语句执行失败,会重新撤销第一条语句所执行的结果
23.2事务的四个属性
- 原子性:事务是一个完成的操作,事务的各部操作是不可分的,要么都执行,要么都不执行
- 一致性:当事务完成时,数据必须都处于一致状态
- 隔离性:并发事务之间彼此隔离、独立,它不应以任何方式依赖于或影响其他事务
- 永久性:事务完成后,它对数据库的修改被永久保存
23.3事务分类
- 显式事务:用begin transaction明确指定事务的开始,是最常用的事务类型
- 隐式事务:通过设置set implicit_transactions on语句,将隐性事务模式设置为打开;其后的T-SQL语句自动启动一个新事务,提交或回滚一个事务后,下一个T-SQL语句又将启动一个新事务
- 自动提交事务:SQL Server的默认模式,每条单独的T-SQL语句视为一个事务
23.4使用SQL语句管理事务的基本步骤
- 开始事务:begin transaction
- 提交事务:commit transaction
- 回滚(撤销)事务:rollback transaction
- 一旦事务提交或回滚,则事务结束
- 事务处理中的关键问题主要是对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判断某条语句执行是否出错的方法
- 使用全局变量@@ERROR
- @@ERROR只判断当前一条T-SQL语句执行是否有错
- 为了判断事务中所有T-SQL语句是否有错,可以对错误进行累计
- 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分类
聚集索引:表中各行的物理顺序与键值的逻辑(索引)顺序相同,每个表只能有一个
-
- 主键索引:为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型
- 主键索引要求主键中的每一值是唯一的,并且不能为空
非聚集索引(nonclustered index ):非聚集索引指定表的逻辑顺序
-
- 数据可以存储在一个位置,索引存储在另一个位置,索引中包含指向数据存储位置的指针
- 可以有多个,小于249
- 唯一索引:唯一索引不允许两行具有相同的索引值
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();
}
}
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();
}
}
}
}
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;
}
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命令执行方式比较
- 使用完整的SQL语句:Sql语句编写困难、容易出错;网络传输不安全,很容易发生“注入式攻击”;执行效率低
- 使用带参数的SQL语句:Sql语句编写容易,网络传输安全,执行效率一般
- 调用存储过程:不需要编写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 }
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 }
29.ADO.NET调用存储过程
- 先在服务器端编写需要的存储过程,如果有参数,需要@定义好参数
- 编写调用存储过程的通用数据访问方法。注意cmd.CommandType=CommandType.StoredProcedure; //声明是存储过程cmd.CommandText=text; //指定存储过程的名称cmd.Parameters.AddRange(paras); //添加输入参数
- 编写调用存储过程的方法
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类中方法的总结
- 执行格式化的SQL语句(使用占位符)
- 执行带参数的SQL语句(类似存储过程参数,效率和安全性较高)
- 执行带参数的存储过程(效率最高,安全性最高)
- 启动事务执行更新(保证数据的一致性)
- 项目开发中应按照以上分类编写SQLHelper类中的方法,并在适时场合调用不同的的方法