计算机ASPNET框架

第7章ADO.NET操作SQLServer数据库\n内容提要本章主要介绍SQLServer2000的基本使用。介绍SQLServer2000集成开发环境的使用。介绍了如何在SQLServer2000中使用查询分析器建立数据库和数据库表，如何使用ADO.NET操作数据库表。重点介绍如何使用ADO.NET的命名空间“System.Data.SqlClient”操作SQLServer的存储过程。\nSQLServer简介与Access相比较，它具有更好的应用特征，如下所示。（1）支持企业级运算、支持C/S模型、更好的性能和更方便的操作。（2）功能增强：海量数据存储、数据复制、数据转换服务、分布式事务、全文检索。（3）支持多种协议（TCP/IP、NETBEUI）和支持分布式计算，分布式计算模型。（4）ANSI/92标准兼容并进行T-SQL（TransactSQL，事务SQL语言）的增强。\nSQLServer的集成环境介绍SQLServer2000提供强大的GUI（GraphicUserInterface，图形用户接口）界面，用户可以直接通过界面或者通过T-SQL语句操作数据库。常用的图形界面包括：SQLServer服务管理器、企业管理器、查询分析器、事件查看器和联机帮助，等等。\nSQL服务管理器\n企业管理器\n查询分析器\n事件探查器\n联机帮助\n创建数据库案例名称：创建单数据文件的数据库程序名称：7-01.sqlCREATEDATABASEMySalesON(NAME=Sales_dat,FILENAME='c:\programfiles\microsoftsqlserver\mssql\data\Mysaledat.mdf',SIZE=2,MAXSIZE=2,FILEGROWTH=2)LOGON(NAME='Sales_log',FILENAME='c:\programfiles\microsoftsqlserver\mssql\data\Mysalelog.ldf',SIZE=1MB,MAXSIZE=1MB,FILEGROWTH=1MB)GO\n案例名称：创建多数据文件的数据库程序名称：7-02.sqlCREATEDATABASEArchiveONPRIMARY(NAME=Arch1,FILENAME='c:\programfiles\microsoftsqlserver\mssql\data\archdat1.mdf',SIZE=1MB,MAXSIZE=1,FILEGROWTH=1),(NAME=Arch2,FILENAME='c:\programfiles\microsoftsqlserver\mssql\data\archdat2.ndf',SIZE=1MB,MAXSIZE=1,FILEGROWTH=1),(NAME=Arch3,FILENAME='c:\programfiles\microsoftsqlserver\mssql\data\archdat3.ndf',SIZE=1MB,MAXSIZE=1,FILEGROWTH=1)LOGON(NAME=Archlog1,FILENAME='c:\programfiles\microsoftsqlserver\mssql\data\archlog1.ldf',SIZE=1MB,MAXSIZE=1,FILEGROWTH=1),(NAME=Archlog2,FILENAME='c:\programfiles\microsoftsqlserver\mssql\data\archlog2.ldf',SIZE=1MB,MAXSIZE=1,FILEGROWTH=1)GO\n删除数据库删除数据库的关键词是：DROPDATABASE。比如要删除名为TEST1的数据库，可以利用语句“DROPDATABASETEST1”。不要轻易删除数据库，否则将导致所有数据完全丢失！\nSQLServer提供的数据类型数据类型描述需要空间Binary固定长度的二进制数据，最大长度为8000字节0到8000字节Char固定长度的非Unicode字符数据，最大长度为8000个字符0到8000字节Datetime日期和时间数据8字节Int整型数据，从–231到231–14个字节Money货币数据值，从–263到263–18字节Smallint整型数据，从–215到215–12字节Varchar可变长度的非Unicode数据，最大长度为231–1个字符存储大小是输入数据的实际长度Uniqueidentifier存储作为全局惟一标识(GUID)的16字节的二进制数值。GUID是确保惟一性的二进制数字16字节\n创建数据库表表是关系型数据库中的逻辑单元，用于存储实体数据。表由行和列组成。行描述实体的实例，列定义实体的属性。表命名时必须确保表名称在数据库中是惟一的，并且应遵循标识符命名规则。对表的命名约定有4条：（1）可以包含1到128个字符，包括字母、符号和数字。（2）第一个字符必须是字母、下划线（_）、@符号。（3）首字母之后的字符可以包括字母、数字或#、$符号及其_。（4）除非在引号内定义对象名称，否则不允许有空格。\n案例名称：创建数据库表案例名称：创建数据库表程序名称：8-03.sqlCREATETABLEMyTable(MyNameCHAR(10)NOTNULL,MyBorthDayDATETIME,)\n修改表结构案例名称：添加列程序名称：7-04.sqlALTERTABLEMyTABLEADDMySistNameCHAR(20)\n删除列案例名称：删除列程序名称：7-05.sqlALTERTABLEMyTABLEDROPCOLUMNMySistName\n删除数据库表使用“企业管理器”或者用DropTable语句删除SQLServer中的表。语法：“DropTabletable_name”比如删除MyTable表，利用语句“DropTableMyTable”。\n数据完整性一旦创建并填充完数据库，应确保存储数据的可靠性，这对于任何企业都很关键。因此必须在设计数据库的时候考虑数据完整性。数据完整性指数据库中存储数据的一致性。常规数据库管理系统需要在每个应用程序中编码实现数据完整性逻辑。\n实现数据完整型利用三种方法。（1）使用Identity属性。（2）使用Uniqueidentifier数据类型和NEWID()函数。（3）使用六大约束。\n使用Identity属性表中一般会包含连续值的列，将Identity属性添加到该列上，SQLServer可自动生成这些值。Identity属性生成的值惟一地标识表中的每一行，每次表中插入一行时，该属性就会自动生成值。在创建表的时候创建Identity列，定义Identity列的语法如下：Identity[（Seed,Increment）]参数Seed（种子）指定Identity列的初始值。参数Increment指定每次自动增加多少。Seed和Increment参数是可选的，如果没有指定，则两个参数都默认为1。\n创建Identity列案例名称：创建Identity列程序名称：7-06.sqlCREATETABLEStudent(StudIDintIdentity(101,5),FirstNameVarchar(20),LastNamevarchar(20))InsertintoStudent(FirstName,LastName)Values('runfa','zhou')Select*fromstudentAlterTableStudentaddStudIDintIdentity(101,5)\n使用Uniqueidentifier类型案例名称：创建Uniqueidentifier类型程序名称：7-07.sqlCREATETABLEMYFRIEND(NIDUNIQUEIDENTIFIER,STUDENTXINGVARCHAR(20),STUDENTMINGVARCHAR(20))GOINSERTMyFriendValues(NEWID(),'周','润发')GOINSERTMyFriendValues(NEWID(),'周','敏')Select*fromMyFriend\n使用六大约束对表强制执行完整性的最常用方法是使用约束，限制表或列中的值。约束有六种，分别是：主键约束（PrimaryKey）、外键约束（Foreignkey）、惟一约束（Unique）、非空约束（NotNull）、检查约束（Check）和默认约束（Default）。\n1.主键约束案例名称：使用主键程序名称：7-08.sqlCREATETABLESTUDENT_PRI(STUDIDINTPRIMARYKEY,FIRSTNAMEVARCHAR(20),LASTNAMEVARCHAR(20),)InsertintoSTUDENT_PRIValues(1001,'runfa','zhou')\n2.外键约束外键（Foreignkey）约束定义列值与另一个表的主键相匹配。使用外键时应该注意：Foreignkey约束必须引用另一个表的主键列或者Unique列。案例名称：使用外键程序名称：7-09.sqlCreateTablebasicinfo(stu_idintIdentity(1001,1)PrimaryKey,FirstnameVarchar(10),LastnameVarchar(10))CreateTablehistory(historyidintPrimaryKey,stu_idint,stu_gradeintforeignkey(stu_id)referencesbasicinfo(stu_id))\n3.惟一约束案例名称：使用惟一约束程序名称：7-10.sqlCreateTabletestUnique(stu_idintIdentity(1001,1)PrimaryKey,FirstnameVarchar(10)Unique,LastnameVarchar(10))InsertintotestUniqueValues('runfa','zhou')\n4.非空约束案例名称：使用非空约束程序名称：7-11.sqlCreateTabletestNotNull(FirstnameVarchar(10)NotNull,LastnameVarchar(10))InsertintotestNotNull(lastname)Values('zhou')\n5.检查约束案例名称：使用检查约束程序名称：7-12.sqlCreateTabletestCheck(stu_idintIDENTITY(100000,1)PrimaryKey,FirstnameVarchar(10)notnull,lastnameVarchar(10)notnull,ageintCHECK(age>6),sexVarchar(30)CHECK(sexin('M','F')))InsertIntotestCheckValues('runfa','zhou',5,'M')InsertIntotestCheckValues('runfa','zhou',7,'A')\n6.默认约束案例名称：使用默认约束程序名称：7-13.sqlCreateTabletestDefault(stu_idintIDENTITY(100000,1)PrimaryKey,FirstnameVarchar(10)notnull,LastnameVarchar(10)notnull,SexVarchar(30)Default'M')InsertintotestDefault(Firstname,Lastname)Values('runfa','zhou')Select*fromtestDefault\nADO.NET对象操作SQLServer数据库ADO.NET中操作SQLServer的命名空间是：“System.Date.SqlClient”主要包括：SqlConnection对象、SqlCommand对象、SqlDataReader对象、SqlDataAdapter对象。\n建立SQLServer数据库表案例名称：新建数据库表程序名称：7-14.SQLusepubsgoCreateTablegrade(学号intIdentity(100,1),性别Varchar(30)CHECK(性别in('男','女')),姓名Varchar(10),语文intdefault0,数学intdefault0,英语intdefault0)\n添加测试数据记录案例名称：添加测试数据记录程序名称：7-15.SQLinsertintogradevalues('男','小俞',100,80,70)insertintogradevalues('女','小徐',90,80,60)insertintogradevalues('男','小包',50,60,90)insertintogradevalues('男','小王',79,90,50)insertintogradevalues('男','小栗',89,90,91)insertintogradevalues('女','小卢',90,91,92)insertintogradevalues('男','小李',89,91,95)\n使用ADO.NET对象总体上，操作SQLServer的程序和操作Access的程序方法一致。只需要做3个地方的修改，就可以把操作Access数据库的程序改成操作SQLServer的程序。（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;DataSource="+Server.MapPath("person.mdb");”修改为SQLServer连接串：“"server=localhost;database=pubs;uid=sa;pwd=''"”。\n案例名称：使用DataView对象程序名称：7-16.aspx<%@PageLanguage="C#"%><%@ImportNamespace="System.Data"%><%@ImportNamespace="System.Data.SqlClient"%>voidPage_Load(ObjectSrc,EventArgsE){SqlConnectionConn;Conn=newSqlConnection("server=localhost;database=pubs;uid=sa;pwd=''");StringstrSQL="select*fromgrade";SqlCommandComm=newSqlCommand(strSQL,Conn);SqlDataAdapterda=newSqlDataAdapter();da.SelectCommand=Comm;Conn.Open();DataSetds=newDataSet();da.Fill(ds,"grade");DataViewdv=newDataView(ds.Tables["grade"]);dv.RowFilter="数学>60";dv.Sort="学号desc,数学DESC";Response.Write("满足条件的记录有："+dv.Count+"条");dg.DataSource=dv;dg.DataBind();Conn.Close();}\nSQLServer存储过程存储过程对任何数据库来说都是非常重要的。数据库开发人员和数据库管理员会经常编写自己的存储过程，以便运行一般的管理任务或者应用复杂的业务规则。这些类型的过程中可以包括流程控制结构、数据更改或者数据检索语句及错误处理语句。\n存储过程的概念存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行及其他强大的编程功能。\n创建SQLServer存储过程案例名称：创建存储过程程序名称：7-17.sqlusepubsGOCREATEPROCEDUREdemo_proc(@namechar(16)='SQLServer',@majorint=7,@minorint=0)ASPRINT@name+STR(@major,5)+'.'+STR(@minor,5)\n调用存储过程案例名称：调用存储过程程序名称：7-18.sqlusepubsGOdemo_procEXECUTEdemo_procDEFAULT,7EXECUTEdemo_proc'Oracle',8EXECUTEdemo_procDEFAULT,7,DEFAULTEXECUTEdemo_proc'Oracle',8,DEFAULTEXECUTEdemo_proc'Oracle',8,1EXECUTEdemo_proc@major=8,@name='Oracle',@minor=0\n创建带有Select语句的存储过程案例名称：创建带有Select语句的存储过程程序名称：7-19.sqlusepubsGOCreateprocGetEmployeeCount@v_hiredateDateTimeasPrint'在输入日期之后雇佣的员工有：'selectcount(*)fromEmployeewherehire_date>@v_hiredate\n调用存储过程案例名称：调用存储过程程序名称：7-20.sql--调用存储过程executeGetEmployeeCount'01/01/1993'\n删除存储过程案例名称：删除存储过程程序名称：7-21.sql--删除存储过程dropprocGetEmployeeCount\n创建数据表案例名称：创建数据表程序名称：7-22.sql--创建表usepubsgocreatetableWebUsers(usernamevarchar(20),userpassvarchar(10))--向表中添加数据insertintowebusersvalues('aa','aa')insertintowebusersvalues('bb','bb')\n创建存储过程案例名称：创建存储过程程序名称：7-23.sql--创建存储过程CREATEPROCEDUREsp_CheckPass(@CHKNameVARCHAR(30),@CHKPassVARCHAR(30),@ISValidvarCHAR(12)OUTPUT)ASIFEXISTS(SELECTUserNameFROMWebUsersWHEREUserName=@CHKNameANDUserPass=@CHKPass)SELECT@ISValid='Good'ELSESELECT@ISValid='Bad'\n案例名称：测试存储过程案例名称：测试存储过程程序名称：7-24.sql--调用存储过程declare@aaVarchar(12)execsp_CheckPass'aa','aa',@aaoutputselect@aa'返回值'\nADO.NET操作SQLServer存储过程存储过程是SQLServer数据库的一个最重要的特色，可以利用Command对象方便地调用SQLServer的存储过程，为什么要利用存储过程呢？SQL存储过程执行起来比SQL命令文本快得多。当一个SQL语句包含在存储过程中时，服务器不必在每次执行它时都要分析和编译它。可以在多个网页中调用同一个存储过程，使站点易于维护。如果一个SQL语句需要做某些改动，只要做一次即可。\n调用无输入输出参数存储过程简而言之，能用存储过程时就要用存储过程。存储过程有着极大的优点，也是SQLServer数据库的生命力所在。应学会如何利用Command来调用存储过程。首先利用查询分析器创建存储过程。如程序7-25.sql所示。\n创建无输入输出参数存储过程案例名称：创建无输入输出参数存储过程程序名称：7-25.sqlusepubsgoCREATEPROCEDUREtestProcASselectpub_id,title_id,price,pubdatefromtitleswherepriceisNOTNULLorderbypub_id\n案例名称：调用SQLServer的存储过程程序名称：7-26.aspx<%@PageLanguage="C#"%><%@ImportNamespace="System.Data"%><%@ImportNamespace="System.Data.SqlClient"%>SqlConnectionConn;protectedvoidPage_Load(ObjectSrc,EventArgsE){Conn=newSqlConnection("server=localhost;database=pubs;uid=sa;pwd=''");SqlCommandComm=newSqlCommand("testProc",Conn);//将命令类型设为存储过程Comm.CommandType=CommandType.StoredProcedure;Conn.Open();SqlDataReaderdr=Comm.ExecuteReader();dg.DataSource=dr;dg.DataBind();Conn.Close();}

调用SQLServer的存储过程

\n调用带输入输出参数的存储过程案例名称：调用存储过程的输入和输出参数程序名称：7-27.aspx<%@PageLanguage="C#"%><%@ImportNamespace="System.Data"%><%@ImportNamespace="System.Data.SqlClient"%>SqlConnectionConn;protectedvoidPage_Load(ObjectSrc,EventArgsE){Conn=newSqlConnection("server=localhost;database=pubs;uid=sa;pwd=''");SqlCommandComm=newSqlCommand("sp_CheckPass",Conn);//将命令类型设为存储过程Comm.CommandType=CommandType.StoredProcedure;//添加并给参数赋值SqlParameterParm=Comm.Parameters.Add("@CHKName",SqlDbType.VarChar,12);Parm.Value="aa";Parm=Comm.Parameters.Add("@CHKPass",SqlDbType.VarChar,12);Parm.Value="aa";Parm=Comm.Parameters.Add("@ISValid",SqlDbType.VarChar,28);Parm.Direction=ParameterDirection.Output;Conn.Open();SqlDataReaderdr=Comm.ExecuteReader();Response.Write(Comm.Parameters["@ISValid"].Value);Conn.Close();}\nSQLServer的触发器SQLServer触发器是一类特殊的存储过程，被定义为在对表或视图发出UPDATE、INSERT或DELETE语句时自动执行的预编译SQL语句。\n触发器的作用触发器具有3个重要的作用：1、使每个数据库可以在有数据修改时自动强制执行其业务规则；2、触发器可使处理任务自动进行；3、触发器可以扩展SQLServer约束、默认值和规则的完整性检查逻辑；\n触发器的类型SQLServer中只有3种类型的触发器，分别为：1、INSERT触发器，当执行Insert语句的时候，自动调用触发器。插入的数据在触发器中通过Inserted表读取。2、UPDATE触发器，当执行Update语句的时候，自动调用执行。可以从DELETED表和INSERTED表中读取信息。3、DELETE触发器，当执行Delete语句的时候，自动调用执行，可以从Deleted表中读取信息。\n创建Insert触发器程序名称：7-28.sqlCREATETABLETB_table(col1intIDENTITY,col2char(10)null,col3intnotnulldefault(1),col4int)GO--创建Insert触发器CREATETRIGGERtrigger1_1ONtb_tableFORINSERTASPRINT'执行了Insert语句'Select*fromInsertedGO\n当执行Insert语句的时候，触发器自动被触发，测试触发器利用代码：“insertintotb_tablevalues('aaa',111,99)”，执行的结果如图\n创建Delete触发器案例名称：创建Delete触发器程序名称：7-29.sql--创建Delete触发器CREATETRIGGERtrigger1_2ONtb_tableFORdeleteASPRINT'执行了Delete语句'Select*fromDeletedGO\n利用delete语句来测试：“deletefromtb_tablewherecol4=99”，执行完毕可以分别查看“网格”栏目和“消息”栏目，其中在“网格”栏目将从Deleted虚拟表中读出删除的数据。如图\n创建Update触发器案例名称：创建Update触发器程序名称：7-30.sql--创建Update触发器，当程序执行Update操作的时候，自动触发CREATETRIGGERtrigger1_3ONTB_tableFORUPDATEASPRINT'执行了Update语句'Select*fromInsertedSelect*fromDeletedGO\n创建混合触发器案例名称：创建混合触发器程序名称：7-31.sql--创建Insert和Update触发器，当程序执行Update或者Insert操作的时候，自动触发CREATETRIGGERtrigger1_4ONTB_tableFORINSERT,UPDATEASPRINT'抓住Insert和Update语句'GO\n创建事务触发器案例名称：创建事务触发器程序名称：7-32.sql--创建触发器CreateTriggerCheckRoyaltyonroyschedforinsertasif(selectroyaltyfrominserted)>30beginPrint'版税不能大于30%'print'请修改版税，使其小于30%'rollbacktransactionend--删除该触发器--droptriggerCheckRoyalty--测试触发器--insertintoroyschedvalues('PC9999',1000,4500,40)\n小结本章介绍ADO.NET和SQLServer。了解SQLServer与Access数据库的联系和区别。熟悉SQLServer各个图形界面，掌握如何利用SQL语句创建数据库和数据库表，掌握数据完整性的实现方法。掌握如何使用ADO.NET操作SQLServer数据库。熟练掌握SQLServer存储过程，以及如何使用ADO.NET调用SQLServer的存储过程。熟悉SQLServer触发器的概念和使用。\n本章习题7-1.SQLServer与Access的联系和区别？7-2.如何在SQLServer查询分析中建立数据库和数据库表？7-3.简述Identity属性的功能。7-4.六大约束包括哪些？如何使用？外键约束的功能是什么？7-5.比较操作SQLServer数据库的三大基本格式和操作Access的三大基本格式的异同。7-6.存储过程有什么作用？如何建立和调用存储过程？7-7.如何利用ADO.NET调用带参数的存储过程？程序如何与存储过程传递参数？7-8.完善案例8-2，添加功能：（1）模糊查找某用户；（2）修改某人的信息；（3）删除某人的信息。（上机练习）7-9简述触发器的功能，类型。如何创建触发器。