1、第7讲 ADO.NET操作SQL Server数据库,内容提要,本章主要介绍SQL Server2000的基本使用。介绍SQL Server2000集成开发环境的使用。 介绍了如何在SQL Server2000中使用查询分析器建立数据库和数据库表， 如何使用ADO.NET操作数据库表。 重点介绍如何使用ADO.NET的命名空间“System.Data.SqlClient”操作SQL Server的存储过程。,SQL Server简介,与Access相比较，它具有更好的应用特征，如下所示。 （1）支持企业级运算、支持C/S模型、更好的性能和更方便的操作。 （2）功能增强：海量数据存储、数据复制、
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服务管理器,企业管理器,查询分析器,事件探查器,联机帮助,创建数
3、据库,案例名称：创建单数据文件的数据库 程序名称：7-01.sqlCREATE DATABASE MySales ON ( NAME = Sales_dat,FILENAME = c:program filesmicrosoft sql servermssqldataMysaledat.mdf,SIZE = 2,MAXSIZE = 2,FILEGROWTH = 2 ) LOG ON ( NAME = Sales_log,FILENAME = c:program filesmicrosoft sql servermssqldataMysalelog. ldf,SIZE = 1MB,MAXSIZE
4、 = 1MB,FILEGROWTH = 1MB ) GO,案例名称：创建多数据文件的数据库 程序名称：7-02.sqlCREATE DATABASE Archive ON PRIMARY ( NAME = Arch1,FILENAME = c:program filesmicrosoft sql servermssqldataarchdat1. mdf,SIZE = 1MB,MAXSIZE =1,FILEGROWTH = 1), ( NAME = Arch2,FILENAME = c:program filesmicrosoft sql servermssqldataarchdat2.ndf,
5、SIZE = 1MB,MAXSIZE = 1,FILEGROWTH = 1), ( NAME = Arch3,FILENAME = c:program filesmicrosoft sql servermssqldataarchdat3.ndf,SIZE = 1MB,MAXSIZE = 1,FILEGROWTH = 1) LOG ON ( NAME = Archlog1,FILENAME = c:program filesmicrosoft sql servermssqldataarchlog1.ldf,SIZE = 1MB,MAXSIZE = 1,FILEGROWTH =1), ( NAME
6、 = Archlog2,FILENAME = c:program filesmicrosoft sql servermssqldataarchlog2.ldf,SIZE = 1MB,MAXSIZE = 1,FILEGROWTH =1) GO,删除数据库,删除数据库的关键词是：DROP DATABASE。比如要删除名为TEST1的数据库，可以利用语句“DROP DATABASE TEST1”。不要轻易删除数据库，否则将导致所有数据完全丢失！,SQL Server提供的数据类型,创建数据库表,表是关系型数据库中的逻辑单元，用于存储实体数据。表由行和列组成。行描述实体的实例，列定义实体的属性。表命名
7、时必须确保表名称在数据库中是惟一的，并且应遵循标识符命名规则。对表的命名约定有4条：（1）可以包含1到128个字符，包括字母、符号和数字。 （2）第一个字符必须是字母、下划线（_）、符号。 （3）首字母之后的字符可以包括字母、数字或#、$符号及其_。 （4）除非在引号内定义对象名称，否则不允许有空格。,案例名称：创建数据库表,案例名称：创建数据库表 程序名称：8-03.sqlCREATE TABLE MyTable (MyName CHAR(10) NOT NULL,MyBorthDay DATETIME, ),修改表结构,案例名称：添加列 程序名称：7-04.sqlALTER TABLE M
8、yTABLE ADD MySistName CHAR(20),删除列,案例名称：删除列 程序名称：7-05.sqlALTER TABLE MyTABLE DROP COLUMN MySistName,删除数据库表,使用“企业管理器”或者用Drop Table语句删除SQL Server中的表。 语法：“Drop Table table_name” 比如删除MyTable表，利用语句“Drop Table MyTable”。,数据完整性,一旦创建并填充完数据库，应确保存储数据的可靠性，这对于任何企业都很关键。因此必须在设计数据库的时候考虑数据完整性。数据完整性指数据库中存储数据的一致性。常规数据
9、库管理系统需要在每个应用程序中编码实现数据完整性逻辑。,实现数据完整型利用三种方法。,（1）使用Identity属性。 （2）使用Uniqueidentifier数据类型和NEWID()函数。 （3）使用六大约束。,使用Identity属性,表中一般会包含连续值的列，将Identity属性添加到该列上，SQL Server可自动生成这些值。Identity属性生成的值惟一地标识表中的每一行，每次表中插入一行时，该属性就会自动生成值。 在创建表的时候创建Identity列，定义Identity列的语法如下： Identity （Seed, Increment） 参数Seed（种子）指定Ident
10、ity列的初始值。参数Increment指定每次自动增加多少。Seed和Increment参数是可选的，如果没有指定，则两个参数都默认为1。,创建Identity列,案例名称：创建Identity列 程序名称：7-06.sqlCREATE 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 Tabl
11、e Student add StudID int Identity (101,5),使用Uniqueidentifier类型,案例名称：创建Uniqueidentifier类型 程序名称：7-07.sqlCREATE TABLE MYFRIEND (NID UNIQUEIDENTIFIER,STUDENTXING VARCHAR(20),STUDENTMING VARCHAR(20) ) GO INSERT MyFriend Values(NEWID(),周,润发 ) GO INSERT MyFriend Values(NEWID(),周,敏) Select * from MyFriend,使
12、用六大约束,对表强制执行完整性的最常用方法是使用约束，限制表或列中的值。 约束有六种，分别是：主键约束（Primary Key）、外键约束（Foreign key）、惟一约束（Unique）、非空约束（Not Null）、检查约束（Check）和默认约束（Default）。,1. 主键约束,案例名称：使用主键 程序名称：7-08.sqlCREATE TABLE STUDENT_PRI (STUDID INT PRIMARY KEY,FIRSTNAME VARCHAR(20),LASTNAME VARCHAR(20), ) Insert into STUDENT_PRI Values(1001,
13、 runfa, zhou),2. 外键约束,外键（Foreign key）约束定义列值与另一个表的主键相匹配。使用外键时应该注意：Foreign key 约束必须引用另一个表的主键列或者Unique列。,案例名称：使用外键 程序名称：7-09.sqlCreate 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_gr
14、ade intforeign key(stu_id) references basicinfo(stu_id) ),3. 惟一约束,案例名称：使用惟一约束 程序名称：7-10.sqlCreate 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.sqlCreate Table tes
15、tNotNull (Firstname Varchar(10) Not Null,Lastname Varchar(10) ) Insert into testNotNull(lastname) Values(zhou),5. 检查约束,案例名称：使用检查约束 程序名称：7-12.sqlCreate 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 Var
16、char(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.sqlCreate Table testDefault (stu_id int IDENTITY(100000,1) Primary Key,Firstname Varchar(10) not null,Lastname Varchar(10) not null,Sex Varchar(30) Def
17、ault 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.SQLuse pubs go Create
18、 Table grade (学号 int Identity (100, 1),性别 Varchar(30) CHECK(性别 in(男,女),姓名 Varchar(10),语文 int default 0,数学 int default 0,英语 int default 0 ),添加测试数据记录,案例名称：添加测试数据记录 程序名称：7-15.SQLinsert into grade values(男,小俞,100,80,70) insert into grade values(女,小徐,90,80,60) insert into grade values(男,小包,50,60,90) inse
19、rt 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”，
20、改成“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 S
21、erver连接串：“server=localhost;database=pubs;uid=sa;pwd=“”。,案例名称：使用DataView对象 程序名称：7-16.aspxvoid 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
22、);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(
23、);Conn.Close(); ,SQL Server存储过程,存储过程对任何数据库来说都是非常重要的。数据库开发人员和数据库管理员会经常编写自己的存储过程，以便运行一般的管理任务或者应用复杂的业务规则。这些类型的过程中可以包括流程控制结构、数据更改或者数据检索语句及错误处理语句。,存储过程的概念,存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行及其他强大的编程功能。,创建SQL Server存储过程,案例名称：创建存储过程 程序名称：7-17.sqluse pubs GO
24、 CREATE PROCEDURE demo_proc (name char(16)=SQL Server,major int = 7,minor int =0) ASPRINT name + STR(major,5) + . + STR(minor,5),调用存储过程,案例名称：调用存储过程 程序名称：7-18.sqluse 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
25、,DEFAULT EXECUTE demo_proc Oracle,8,1 EXECUTE demo_proc major=8,name=Oracle,minor=0,创建带有Select语句的存储过程,案例名称：创建带有Select语句的存储过程 程序名称：7-19.sqluse pubs GO Create proc GetEmployeeCount v_hiredate DateTime as Print 在输入日期之后雇佣的员工有： select count(*) from Employee where hire_datev_hiredate,调用存储过程,案例名称：调用存储过程 程序
26、名称：7-20.sql-调用存储过程 execute GetEmployeeCount 01/01/1993,删除存储过程,案例名称：删除存储过程 程序名称：7-21.sql-删除存储过程 drop proc GetEmployeeCount,创建数据表,案例名称：创建数据表 程序名称：7-22.sql-创建表 use pubs go create table WebUsers (username varchar(20),userpass varchar(10) ) -向表中添加数据 insert into webusers values(aa,aa) insert into webusers
27、 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 ELSESELECT ISValid=Bad,案例名称：测试存储过程,案例名
28、称：测试存储过程 程序名称：7-24.sql-调用存储过程 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语句需要做
29、某些改动，只要做一次即可。,调用无输入输出参数存储过程,简而言之，能用存储过程时就要用存储过程。存储过程有着极大的优点，也是SQL Server数据库的生命力所在。应学会如何利用Command 来调用存储过程。首先利用查询分析器创建存储过程。如程序7-25.sql所示。,创建无输入输出参数存储过程,案例名称：创建无输入输出参数存储过程 程序名称：7-25.sqluse pubs go CREATE PROCEDURE testProc AS select pub_id, title_id, price, pubdate from titles where price is NOT NULL o
30、rder by pub_id,案例名称：调用SQL Server的存储过程 程序名称：7-26.aspxSqlConnection 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
31、;Conn.Open();SqlDataReader dr = Comm.ExecuteReader();dg.DataSource = dr;dg.DataBind();Conn.Close(); 调用SQL Server的存储过程,调用带输入输出参数的存储过程,案例名称：调用存储过程的输入和输出参数 程序名称：7-27.aspxSqlConnection Conn; protected void Page_Load(Object Src, EventArgs E ) Conn = new SqlConnection(“server=localhost;database=pubs;uid=s
32、a;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.Value = “aa
33、“;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(); ,SQL Server的触发器,SQL Server 触发器是一类特殊的存储过程，被定义为在对表或视图发出 UPDATE、INSERT 或 DELETE 语句时自
34、动执行的预编译SQL语句。,触发器的作用,触发器具有3个重要的作用： 1、使每个数据库可以在有数据修改时自动强制执行其业务规则； 2、触发器可使处理任务自动进行； 3、触发器可以扩展 SQL Server 约束、默认值和规则的完整性检查逻辑；,触发器的类型,SQL Server中只有3种类型的触发器，分别为： 1、INSERT触发器，当执行Insert语句的时候，自动调用触发器。插入的数据在触发器中通过Inserted表读取。 2、UPDATE触发器，当执行Update语句的时候，自动调用执行。可以从DELETED表和INSERTED表中读取信息。 3、DELETE触发器，当执行Delete语
35、句的时候，自动调用执行，可以从Deleted表中读取信息。,创建Insert触发器,程序名称：7-28.sqlCREATE 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 INSERTAS PRINT 执行了Insert语句 Select * from Inserted GO,当执行Insert语句的时候，触发器自动被触发，测试触发器利用代码：
36、“insert into tb_table values(aaa,111,99)”，执行的结果如图,创建Delete触发器,案例名称：创建Delete触发器 程序名称：7-29.sql-创建Delete触发器 CREATE TRIGGER trigger1_2 ON tb_table FOR deleteAS PRINT 执行了 Delete语句 Select * from Deleted GO,利用delete语句来测试：“delete from tb_table where col4=99”，执行完毕可以分别查看“网格”栏目和“消息”栏目，其中在“网格”栏目将从Deleted虚拟表中读出删
37、除的数据。如图,创建Update触发器,案例名称：创建Update触发器 程序名称：7-30.sql-创建Update触发器，当程序执行Update操作的时候，自动触发 CREATE TRIGGER trigger1_3ON TB_tableFOR UPDATEAS PRINT 执行了Update语句 Select * from Inserted Select * from Deleted GO,创建混合触发器,案例名称：创建混合触发器 程序名称：7-31.sql-创建Insert和Update触发器，当程序执行Update或者Insert操作的时候，自动触发 CREATE TRIGGER t
38、rigger1_4ON TB_tableFOR INSERT,UPDATEASPRINT 抓住Insert和Update语句 GO,创建事务触发器,案例名称：创建事务触发器 程序名称：7-32.sql-创建触发器 Create Trigger CheckRoyalty on roysched for insert as if (select royalty from inserted)30 beginPrint 版税不能大于30%print 请修改版税，使其小于30%rollback transaction end -删除该触发器 -drop trigger CheckRoyalty -测试触
39、发器 -insert into roysched values(PC9999,1000,4500,40),小结,本章介绍ADO.NET和SQL Server。了解SQL Server与Access数据库的联系和区别。 熟悉SQL Server各个图形界面，掌握如何利用SQL语句创建数据库和数据库表，掌握数据完整性的实现方法。 掌握如何使用ADO.NET操作SQL Server数据库。 熟练掌握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 简述触发器的功能，类型。如何创建触发器。,
