第7章 ADO.NET操作SQL Server数据库

内容提要 本章主要介绍SQL Server2000的基本使用。介绍SQL Server2000集成开发环境的使用。 如何使用ADO.NET操作数据库表。 重点介绍如何使用ADO.NET的命名空间“System.Data.SqlClient”操作SQL Server的存储过程。

SQL Server简介 与Access相比较，它具有更好的应用特征，如下所示。 （1）支持企业级运算、支持C/S模型、更好的性能和更方便的操作。 （2）功能增强：海量数据存储、数据复制、数据转换服务、分布式事务、全文检索。 （3）支持多种协议（TCP/IP、NETBEUI）和支持分布式计算，分布式计算模型。 （4）ANSI/92标准兼容并进行T-SQL（Transact SQL，事务SQL语言）的增强。

SQL Server的集成环境介绍 SQL Server2000提供强大的GUI（Graphic User Interface，图形用户接口）界面，用户可以直接通过界面或者通过T-SQL语句操作数据库。 常用的图形界面包括：SQL Server服务管理器、企业管理器、查询分析器、事件查看器和联机帮助，等等。

SQL服务管理器

企业管理器

查询分析器

事件探查器

联机帮助

创建数据库 案例名称：创建单数据文件的数据库 程序名称：7-01.sql CREATE DATABASE MySales ON   CREATE DATABASE MySales ON ( NAME = Sales_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\Mysaledat.mdf', SIZE = 2, MAXSIZE = 2, FILEGROWTH = 2 ) LOG ON ( NAME = 'Sales_log', FILENAME = 'c:\program files\microsoft sql server\mssql\data\Mysalelog. ldf', SIZE = 1MB, MAXSIZE = 1MB, FILEGROWTH = 1MB ) GO

案例名称：创建多数据文件的数据库 程序名称：7-02.sql   CREATE DATABASE Archive ON PRIMARY ( NAME = Arch1, FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1. mdf', SIZE = 1MB, MAXSIZE =1, FILEGROWTH = 1), ( NAME = Arch2, FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat2.ndf', MAXSIZE = 1, ( NAME = Arch3, FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat3.ndf', FILEGROWTH = 1) LOG ON ( NAME = Archlog1, FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog1.ldf', FILEGROWTH =1), ( NAME = Archlog2, FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog2.ldf', FILEGROWTH =1) GO

删除数据库 删除数据库的关键词是：DROP DATABASE。 比如要删除名为TEST1的数据库，可以利用语句“DROP DATABASE TEST1”。不要轻易删除数据库，否则将导致所有数据完全丢失！

SQL Server提供的数据类型 数 据 类 型 描 述 需 要 空 间 Binary 固定长度的二进制数据，最大长度为8000字节 描 述 需 要 空 间 Binary 固定长度的二进制数据，最大长度为8000字节 0到8000字节 Char 固定长度的非Unicode字符数据，最大长度为8000个字符 Datetime 日期和时间数据 8字节 Int 整型数据，从–231到231–1 4个字节 Money 货币数据值，从–263到263–1 Smallint 整型数据，从–215到215–1 2字节 Varchar 可变长度的非Unicode数据，最大长度为231–1个字符 存储大小是输入数据的实际长度 Uniqueidentifier 存储作为全局惟一标识(GUID)的16字节的二进制数值。GUID是确保惟一性的二进制数字 16字节

创建数据库表 表是关系型数据库中的逻辑单元，用于存储实体数据。表由行和列组成。行描述实体的实例，列定义实体的属性。表命名时必须确保表名称在数据库中是惟一的，并且应遵循标识符命名规则。对表的命名约定有4条： （1）可以包含1到128个字符，包括字母、符号和数字。 （2）第一个字符必须是字母、下划线（_）、@符号。 （3）首字母之后的字符可以包括字母、数字或#、$符号及其_。 （4）除非在引号内定义对象名称，否则不允许有空格。

案例名称：创建数据库表 案例名称：创建数据库表 程序名称：8-03.sql CREATE TABLE MyTable (   CREATE TABLE MyTable ( MyName CHAR(10) NOT NULL, MyBorthDay DATETIME, )

修改表结构 案例名称：添加列 程序名称：7-04.sql   ALTER TABLE MyTABLE ADD MySistName CHAR(20)

删除列 案例名称：删除列 程序名称：7-05.sql   ALTER TABLE MyTABLE DROP COLUMN MySistName

删除数据库表 使用“企业管理器”或者用Drop Table语句删除SQL Server中的表。 语法：“Drop Table table_name” 比如删除MyTable表，利用语句“Drop Table MyTable”。

数据完整性 一旦创建并填充完数据库，应确保存储数据的可靠性，这对于任何企业都很关键。因此必须在设计数据库的时候考虑数据完整性。 数据完整性指数据库中存储数据的一致性。常规数据库管理系统需要在每个应用程序中编码实现数据完整性逻辑。

实现数据完整型利用三种方法。 （1）使用Identity属性。 （2）使用Uniqueidentifier数据类型和NEWID()函数。 （3）使用六大约束。

使用Identity属性 表中一般会包含连续值的列，将Identity属性添加到该列上，SQL Server可自动生成这些值。Identity属性生成的值惟一地标识表中的每一行，每次表中插入一行时，该属性就会自动生成值。 在创建表的时候创建Identity列，定义Identity列的语法如下： Identity [（Seed, Increment）] 参数Seed（种子）指定Identity列的初始值。参数Increment指定每次自动增加多少。Seed和Increment参数是可选的，如果没有指定，则两个参数都默认为1。

创建Identity列 案例名称：创建Identity列 程序名称：7-06.sql CREATE TABLE Student (   CREATE TABLE Student ( StudID int Identity (101, 5), FirstName Varchar(20), LastName varchar(20) ) Insert into Student(FirstName, LastName) Values('runfa','zhou') Select * from student Alter Table Student add StudID int Identity (101,5)

使用Uniqueidentifier类型 程序名称：7-07.sql   CREATE TABLE MYFRIEND ( NID UNIQUEIDENTIFIER, STUDENTXING VARCHAR(20), STUDENTMING VARCHAR(20) ) GO INSERT MyFriend Values(NEWID(),'周','润发 ') INSERT MyFriend Values(NEWID(),'周','敏') Select * from MyFriend

使用六大约束 对表强制执行完整性的最常用方法是使用约束，限制表或列中的值。 约束有六种，分别是：主键约束（Primary Key）、外键约束（Foreign key）、惟一约束（Unique）、非空约束（Not Null）、检查约束（Check）和默认约束（Default）。

1. 主键约束 案例名称：使用主键 程序名称：7-08.sql CREATE TABLE STUDENT_PRI (   CREATE TABLE STUDENT_PRI ( STUDID INT PRIMARY KEY, FIRSTNAME VARCHAR(20), LASTNAME VARCHAR(20), ) Insert into STUDENT_PRI Values(1001, 'runfa', 'zhou')

2. 外键约束 外键（Foreign key）约束定义列值与另一个表的主键相匹配。使用外键时应该注意：Foreign key 约束必须引用另一个表的主键列或者Unique列。 案例名称：使用外键 程序名称：7-09.sql   Create Table basicinfo ( stu_id int Identity(1001,1) Primary Key, Firstname Varchar(10) , Lastname Varchar(10) ) Create Table history historyid int Primary Key, stu_id int, stu_grade int foreign key(stu_id) references basicinfo(stu_id)

3. 惟一约束 案例名称：使用惟一约束 程序名称：7-10.sql Create Table testUnique (   Create Table testUnique ( stu_id int Identity(1001,1) Primary Key, Firstname Varchar(10) Unique, Lastname Varchar(10) ) Insert into testUnique Values('runfa', 'zhou')

4. 非空约束 案例名称：使用非空约束 程序名称：7-11.sql Create Table testNotNull (   Create Table testNotNull ( Firstname Varchar(10) Not Null, Lastname Varchar(10) ) Insert into testNotNull(lastname) Values('zhou')

5. 检查约束 案例名称：使用检查约束 程序名称：7-12.sql Create Table testCheck (   Create Table testCheck ( stu_id int IDENTITY(100000,1) Primary Key, Firstname Varchar(10) not null, lastname Varchar(10) not null, age int CHECK (age > 6), sex Varchar(30) CHECK(sex in('M','F')) ) Insert Into testCheck Values('runfa','zhou',5,'M') Insert Into testCheck Values('runfa','zhou',7,'A')

6. 默认约束 案例名称：使用默认约束 程序名称：7-13.sql Create Table testDefault (   Create Table testDefault ( stu_id int IDENTITY(100000,1) Primary Key, Firstname Varchar(10) not null, Lastname Varchar(10) not null, Sex Varchar(30) Default 'M' ) Insert into testDefault(Firstname,Lastname) Values('runfa','zhou') Select * from testDefault

ADO.NET对象操作SQL Server数据库 ADO.NET中操作SQL Server的命名空间是：“System.Date.SqlClient” 主要包括：SqlConnection对象、SqlCommand对象、SqlDataReader对象、SqlDataAdapter对象。

建立SQL Server数据库表 案例名称：新建数据库表 程序名称：7-14.SQL use pubs go   use pubs go Create Table grade ( 学号 int Identity (100, 1), 性别 Varchar(30) CHECK(性别 in('男','女')), 姓名 Varchar(10), 语文 int default 0, 数学 int default 0, 英语 int default 0 )

添加测试数据记录 案例名称：添加测试数据记录 程序名称：7-15.SQL   insert into grade values('男','小俞',100,80,70) insert into grade values('女','小徐',90,80,60) insert into grade values('男','小包',50,60,90) insert into grade values('男','小王',79,90,50) insert into grade values('男','小栗',89,90,91) insert into grade values('女','小卢',90,91,92) insert into grade values('男','小李',89,91,95)

使用ADO.NET对象 总体上，操作SQL Server的程序和操作Access的程序方法一致。只需要做3个地方的修改，就可以把操作Access数据库的程序改成操作SQL Server的程序。 （1）修改引入的命名空间。操作Access数据库使用的是“System.Data.OleDb”，改成“System.Data.SqlClient”。 （2）修改ADO.NET的对象。分别把OleDbConnection、OleDbCommand、OleDbDataReader和OleDbDataAdapter等对象修改成SqlConnection、SqlCommand、SqlDataReader和SqlDataAdapter等对象。 （3）修改Connection对象的数据库连接串。操作Access数据库一般用： “"Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+Server.MapPath("person.mdb");” 修改为SQL Server连接串：“"server=localhost;database=pubs;uid=sa;pwd=''"”。

案例名称：使用DataView对象 程序名称：7-16.aspx   <%@ Page Language="C#" %> <%@Import Namespace="System.Data"%> <%@Import Namespace="System.Data.SqlClient"%> <Script runat="server"> void Page_Load(Object Src, EventArgs E) { SqlConnection Conn; Conn = new SqlConnection("server=localhost;database=pubs;uid=sa;pwd=''"); String strSQL = "select * from grade"; SqlCommand Comm = new SqlCommand(strSQL,Conn); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = Comm; Conn.Open(); DataSet ds = new DataSet(); da.Fill(ds,"grade"); DataView dv = new DataView(ds.Tables["grade"]); dv.RowFilter = " 数学 > 60"; dv.Sort = "学号 desc, 数学 DESC"; Response.Write("满足条件的记录有：" + dv.Count + "条"); dg.DataSource = dv; dg.DataBind(); Conn.Close(); } </script> <ASP:DataGrid id="dg" runat="server"/>

SQL Server存储过程 存储过程对任何数据库来说都是非常重要的。 数据库开发人员和数据库管理员会经常编写自己的存储过程，以便运行一般的管理任务或者应用复杂的业务规则。 这些类型的过程中可以包括流程控制结构、数据更改或者数据检索语句及错误处理语句。

存储过程的概念 存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。 存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行及其他强大的编程功能。

创建SQL Server存储过程 案例名称：创建存储过程 程序名称：7-17.sql use pubs GO   use pubs GO CREATE PROCEDURE demo_proc (@name char(16)='SQL Server',@major int = 7,@minor int =0) AS PRINT @name + STR(@major,5) + '.' + STR(@minor,5)

调用存储过程 案例名称：调用存储过程 程序名称：7-18.sql use pubs GO demo_proc   use pubs GO demo_proc EXECUTE demo_proc DEFAULT,7 EXECUTE demo_proc 'Oracle',8 EXECUTE demo_proc DEFAULT,7,DEFAULT EXECUTE demo_proc 'Oracle',8,DEFAULT EXECUTE demo_proc 'Oracle',8,1 EXECUTE demo_proc @major=8,@name='Oracle',@minor=0

创建带有Select语句的存储过程 案例名称：创建带有Select语句的存储过程 程序名称：7-19.sql use pubs GO   use pubs GO Create proc GetEmployeeCount @v_hiredate DateTime as Print '在输入日期之后雇佣的员工有：' select count(*) from Employee where hire_date>@v_hiredate

调用存储过程 案例名称：调用存储过程 程序名称：7-20.sql --调用存储过程   --调用存储过程 execute GetEmployeeCount '01/01/1993'

删除存储过程 案例名称：删除存储过程 程序名称：7-21.sql   --删除存储过程 drop proc GetEmployeeCount

创建数据表 案例名称：创建数据表 程序名称：7-22.sql --创建表 use pubs go create table WebUsers   --创建表 use pubs go create table WebUsers ( username varchar(20), userpass varchar(10) ) --向表中添加数据 insert into webusers values('aa','aa') insert into webusers values('bb','bb')

创建存储过程 案例名称：创建存储过程 程序名称：7-23.sql --创建存储过程   --创建存储过程 CREATE PROCEDURE sp_CheckPass (@CHKName VARCHAR(30),@CHKPass VARCHAR(30),@ISValid varCHAR(12) OUTPUT) AS IF EXISTS(SELECT UserName FROM WebUsers WHERE UserName=@CHKName AND UserPass= @CHKPass) SELECT @ISValid='Good' ELSE SELECT @ISValid='Bad'

案例名称：测试存储过程 案例名称：测试存储过程 程序名称：7-24.sql --调用存储过程 declare @aa Varchar(12)   --调用存储过程 declare @aa Varchar(12) exec sp_CheckPass 'aa','aa',@aa output select @aa '返回值'

ADO.NET操作SQL Server存储过程 存储过程是SQL Server数据库的一个最重要的特色，可以利用Command对象方便地调用SQL Server 的存储过程，为什么要利用存储过程呢？ SQL存储过程执行起来比SQL命令文本快得多。当一个SQL语句包含在存储过程中时，服务器不必在每次执行它时都要分析和编译它。 可以在多个网页中调用同一个存储过程，使站点易于维护。如果一个SQL语句需要做某些改动，只要做一次即可。

调用无输入输出参数存储过程 简而言之，能用存储过程时就要用存储过程。 存储过程有着极大的优点，也是SQL Server数据库的生命力所在。应学会如何利用Command 来调用存储过程。首先利用查询分析器创建存储过程。如程序7-25.sql所示。

创建无输入输出参数存储过程 案例名称：创建无输入输出参数存储过程 程序名称：7-25.sql use pubs go   use pubs go CREATE PROCEDURE testProc AS select pub_id, title_id, price, pubdate from titles where price is NOT NULL order by pub_id

案例名称：调用SQL Server的存储过程 程序名称：7-26.aspx   <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script runat="server"> SqlConnection Conn; protected void Page_Load(Object Src, EventArgs E ){ Conn = new SqlConnection("server=localhost;database=pubs;uid=sa;pwd=''"); SqlCommand Comm = new SqlCommand("testProc",Conn); // 将命令类型设为存储过程 Comm.CommandType=CommandType.StoredProcedure; Conn.Open(); SqlDataReader dr = Comm.ExecuteReader(); dg.DataSource = dr; dg.DataBind(); Conn.Close(); } </script> <h1>调用SQL Server的存储过程</h1> <form runat="server"> <asp:DataGrid id="dg" runat="server"/> </form>

调用带输入输出参数的存储过程 案例名称：调用存储过程的输入和输出参数 程序名称：7-27.aspx   <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script runat="server"> SqlConnection Conn; protected void Page_Load(Object Src, EventArgs E ) { Conn = new SqlConnection("server=localhost;database=pubs;uid=sa;pwd=''"); SqlCommand Comm = new SqlCommand("sp_CheckPass",Conn); // 将命令类型设为存储过程 Comm.CommandType=CommandType.StoredProcedure; // 添加并给参数赋值 SqlParameter Parm = Comm.Parameters.Add("@CHKName", SqlDbType.VarChar, 12); Parm.Value = "aa"; Parm = Comm.Parameters.Add("@CHKPass", SqlDbType.VarChar, 12); Parm = Comm.Parameters.Add("@ISValid", SqlDbType.VarChar, 28); Parm.Direction = ParameterDirection.Output; Conn.Open(); SqlDataReader dr = Comm.ExecuteReader(); Response.Write(Comm.Parameters["@ISValid"].Value); Conn.Close(); } </script> 调用带输入输出参数的存储过程

SQL Server的触发器 SQL Server 触发器是一类特殊的存储过程，被定义为在对表或视图发出 UPDATE、INSERT 或 DELETE 语句时自动执行的预编译SQL语句。

触发器的作用 触发器具有3个重要的作用： 1、使每个数据库可以在有数据修改时自动强制执行其业务规则； 2、触发器可使处理任务自动进行； 3、触发器可以扩展 SQL Server 约束、默认值和规则的完整性检查逻辑；

触发器的类型 SQL Server中只有3种类型的触发器，分别为： 1、INSERT触发器，当执行Insert语句的时候，自动调用触发器。插入的数据在触发器中通过Inserted表读取。 2、UPDATE触发器，当执行Update语句的时候，自动调用执行。可以从DELETED表和INSERTED表中读取信息。 3、DELETE触发器，当执行Delete语句的时候，自动调用执行，可以从Deleted表中读取信息。

创建Insert触发器 程序名称：7-28.sql CREATE TABLE TB_table ( col1 int IDENTITY,   CREATE TABLE TB_table ( col1 int IDENTITY, col2 char(10) null, col3 int not null default(1), col4 int ) GO --创建Insert触发器 CREATE TRIGGER trigger1_1 ON tb_table FOR INSERT AS PRINT '执行了Insert语句' Select * from Inserted

当执行Insert语句的时候，触发器自动被触发，测试触发器利用代码：“insert into tb_table values('aaa',111,99)”，执行的结果如图

创建Delete触发器 案例名称：创建Delete触发器 程序名称：7-29.sql --创建Delete触发器   --创建Delete触发器 CREATE TRIGGER trigger1_2 ON tb_table FOR delete AS PRINT '执行了 Delete语句' Select * from Deleted GO

利用delete语句来测试：“delete from tb_table where col4=99”，执行完毕可以分别查看“网格”栏目和“消息”栏目，其中在“网格”栏目将从Deleted虚拟表中读出删除的数据。如图

创建Update触发器 GO 案例名称：创建Update触发器 程序名称：7-30.sql   --创建Update触发器，当程序执行Update操作的时候，自动触发 CREATE TRIGGER trigger1_3 ON TB_table FOR UPDATE AS PRINT '执行了Update语句' Select * from Inserted Select * from Deleted GO

创建混合触发器 案例名称：创建混合触发器 程序名称：7-31.sql   --创建Insert和Update触发器，当程序执行Update或者Insert操作的时候，自动触发 CREATE TRIGGER trigger1_4 ON TB_table FOR INSERT,UPDATE AS PRINT '抓住Insert和Update语句' GO

创建事务触发器 案例名称：创建事务触发器 程序名称：7-32.sql --创建触发器 Create Trigger CheckRoyalty   --创建触发器 Create Trigger CheckRoyalty on roysched for insert as if (select royalty from inserted)>30 begin Print '版税不能大于30%' print '请修改版税，使其小于30%' rollback transaction end --删除该触发器 --drop trigger CheckRoyalty --测试触发器 --insert into roysched values('PC9999',1000,4500,40)

小结 本章介绍ADO.NET和SQL Server。了解SQL Server与Access数据库的联系和区别。 熟练掌握SQL Server存储过程，以及如何使用ADO.NET调用SQL Server的存储过程。熟悉SQL Server触发器的概念和使用。

本章习题 7-1. SQL Server与Access的联系和区别？ 7-2. 如何在SQL Server查询分析中建立数据库和数据库表？ 7-3. 简述Identity属性的功能。 7-4. 六大约束包括哪些？如何使用？外键约束的功能是什么？ 7-5. 比较操作SQL Server数据库的三大基本格式和操作Access的三大基本格式的异同。 7-6. 存储过程有什么作用？如何建立和调用存储过程？ 7-7. 如何利用ADO.NET调用带参数的存储过程？程序如何与存储过程传递参数？ 7-8. 完善案例8-2，添加功能：（1）模糊查找某用户；（2）修改某人的信息；（3）删除某人的信息。（上机练习） 7-9 简述触发器的功能，类型。如何创建触发器。