SQLServer开发概述.ppt

C#程序设计教程,第14章SQLServer开发概述,2,本章重点,SQLServer2005简介SQLServerManagementStudio的使用基础数据库维护SqlConnection对象和SqlCommand对象书店零售管理系统,3,SQLServer2005的版本2-1,标准版（Standard）包含关系数据库管理所需的基本功能，适合绝大多数中小型企业企业版（Enterprise）在关系数据库管理所需的基本功能基础上，提供了高度复杂的数据分析和事务处理功能，支持高智能化和高可靠性的数据仓库系统，适合大型和超大型企业开发版（Development）包含企业版所有功能，但存在许可证限制，主要供开发人员进行系统开发和测试，不允许商业用途,4,SQLServer2005的版本2-2,工作组版（Workgroup）包含关系数据库管理的核心功能，适合入门级的小型企业简易版（Express）包含基本的数据库服务器和客户端管理功能，可免费从微软公司网站下载，VisualStudio2005也集成了该数据库版本，主要供初学者或非专业开发人员使用由于不包括ManagementStudio等一些图形用户界面工具，数据管理大都需要通过Transact-SQL语句来完成,5,SQLServerManagementStudio（SQLServer管理控制台）3-1,SQLServerManagementStudio是一个集成的环境，用于访问、配置和管理所有SQLServer组件SQLServerManagementStudio组合了大量图形工具和丰富的脚本编辑器，是SQLServer2005中最重要的管理工具组件,6,SQLServerManagementStudio（SQLServer管理控制台）3-2,SQLServerManagementStudio主要功能管理SQLServer服务包括注册新的服务器、连接服务器、配置服务器等管理数据库包括创建、修改和删除数据库、数据表、视图、存储过程、函数、触发器、数据库用户、多维数据集等对象,7,SQLServerManagementStudio（SQLServer管理控制台）3-3,使用脚本编辑工具创建Transact-SQL(T-SQL)、MDX、DMX、XML等各种脚本对服务器角色、登录名、凭据等安全性功能进行管理监控当前活动，维护系统日志和全文索引,8,服务器注册2-1,服务器注册是指将本地或网络上的SQLServer服务器注册到本地SQLServerManagementStudio中，之后就可以对这些服务器进行统一管理通过菜单命令“视图已注册的服务器”可以打开“已注册的服务器”窗口，查看所有已经注册的服务器在该窗口中单击右键，在弹出菜单中选择“新建服务器注册”，可以注册服务器在右键菜单中选择“新建服务器组”，可以对注册的服务器进行分组管理,9,服务器注册2-2,在“已注册的服务器”窗口中选中某个服务器，通过右键菜单命令“SQLServer配置管理器”可以直接启动SQLServerConfigurationManager，配置服务器自学SQLServerConfigurationManager的使用,10,脚本编辑2-1,工具栏中，在“打开文件”按钮之前的“新建查询”、“数据库引擎查询”等按钮用于打开编辑脚本的查询编辑器，这是ManagementStudio中最常用的工具脚本的编辑有两种方式联机方式选择指定的服务器，单击“新建查询”按钮，此时代码自动与该服务器连接，脚本的执行也将作用于此服务器,11,脚本编辑2-2,脱机方式单击“数据库引擎查询”按钮，在打开的“连接到数据库引擎”对话框中选择“取消”，则代码和服务器断开连接这种方式通常在服务器不可用时使用，而后同样可以创建、编辑和保存脚本代码执行脚本时，“连接到数据库引擎”对话框将再次打开，此时可以选择要作用的服务器,12,创建、修改和删除数据库6-1,在SQLServerManagementStudio的对象资源管理器中，选择指定服务器下的“数据库”结点，通过右键菜单命令“新建数据库”，可以新建数据库还可以在查询编辑器中编写T-SQL语句来新建数据库下面给出示例,13,创建、修改和删除数据库6-2,按照SQLServer默认设置新建数据库BookStoreCREATEDATABASEBookStore,也可以设置数据的各种选项下面的语句将数据库文件和日志文件都存放在C盘的“Data”目录下。数据库文件初始大小为10MB，增量为1MB，不限制增长；日志文件初始大小为1MB，增量为10%，增长限制为10MBCREATEDATABASEBookStoreONPRIMARY(NAME=NBookStore,FILENAME=NC:DataBookStore.mdf,SIZE=10240KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB)LOGON(NAME=NBookStore_log,FILENAME=NC:DataBookStore_log.ldf,SIZE=1024KB,MAXSIZE=10240KB,FILEGROWTH=10%),编写好代码后，按下快捷键F5或者工具栏中的“执行”按钮，即可执行SQL语句,14,创建、修改和删除数据库6-3,注意在ManagementStudio中执行的数据库操作直接显示在对象资源管理器中而执行T-SQL语句所做的修改，需要刷新对象资源管理器才能显示出来,15,创建、修改和删除数据库6-4,如果基于已有的数据库文件来创建数据库在ManagementStudio的对象资源管理器中，选择指定服务器下的“数据库”结点，通过右键菜单命令“附加”，可以从已有的数据库文件新建数据库如果采用T-SQL语句方式，示例代码如下,CREATEDATABASEBookStoreON(FILENAME=C:DataBookStore.mdf)FORATTACH,16,创建、修改和删除数据库6-5,修改数据库设置在ManagementStudio的对象资源管理器中，选择指定数据库，通过右键菜单命令“属性”，可以修改数据库设置如果采用T-SQL语句方式，示例代码如下,ALTERDATABASEBookStoreADDLOGFILE(NAME=NBookStore_log1,FILENAME=NC:DataBookStore_log1.ldf,SIZE=1024KB,MAXSIZE=5120KB,FILEGROWTH=10%),17,创建、修改和删除数据库6-6,删除数据库在ManagementStudio的对象资源管理器中，选择指定数据库，通过右键菜单命令“删除”，可以删除数据库如果采用T-SQL语句方式，示例代码如下,DROPDATABASEBookStore,18,管理数据表2-1,新建数据表在ManagementStudio的对象资源管理器中，选择指定数据库下的“表”结点，通过右键菜单命令“新建表”，可以打开表设计器如果采用T-SQL语句方式，使用CREATETABLE语句注意：表名前要有数据库名，如BookStore.Book,19,管理数据表2-2,修改数据表在ManagementStudio的对象资源管理器中，选择指定的数据表通过右键菜单命令“修改”，可以打开表设计器修改表结构通过右键菜单命令“打开表”，可以查看和增删改表中数据记录如果采用T-SQL语句方式使用ALTERTABLE语句来修改表结构使用SELECT、INSERT、DELETE和UPDATE语句可以查看和增删改表中数据记录,20,管理数据视图2-1,视图是虚拟表，其结构和数据都来源于数据表或其他视图的查询结果新建视图在ManagementStudio的对象资源管理器中，选择指定数据库下的“视图”结点，通过右键菜单命令“新建视图”，可以打开视图设计器如果采用T-SQL语句方式，格式如下CREATEVIEW视图名ASSELECT语句注意：视图名前也要有数据库名,21,管理数据视图2-2,修改视图结构和删除视图的方式与修改表结构和删除表的方式类似，不再赘述视图中显示的数据记录一般不允许增删改,22,存储过程、函数和触发器,存储过程、函数和触发器都是存放在数据库中的T-SQL语言程序，其作用类似于高级程序设计语言中的过程、函数和事件,23,管理存储过程7-1,SQLServer中有三种存储过程用户定义的存储过程扩展存储过程（一般不用）系统存储过程执行数据库基础管理工作，通常以“sp_”为前缀名,24,管理存储过程7-2,新建存储过程在ManagementStudio的对象资源管理器中，选择指定数据库下的“可编程性”“存储过程”结点，通过右键菜单命令“新建存储过程”，可以打开查询编辑器，其中包含了创建存储过程的基本框架例如,25,管理存储过程7-3,CREATEPROCEDUREGetSellItemsByPress(Pressnvarchar(50)ASBEGINSETNOCOUNTON;SELECTISBN,Name,Price,Number,DiscountFROMBook,SellItemWHERE(Book.ID=SellItem.BookID)AND(Book.Press=Press)END参数必须以“”符号为前缀，并指定数据类型“SETNOCOUNTON;”表示不对受影响的行数进行计数,26,管理存储过程7-4,修改存储过程在ManagementStudio的对象资源管理器中，选择指定存储过程，通过右键菜单命令“修改”，可以打开查询编辑器，使用“ALTERPROCEDURE”语句来修改存储过程例如,27,管理存储过程7-5,ALTERPROCEDUREdbo.GetSellItemsByPress(Pressnvarchar(50),RecordsintOUTPUT,TotalsmallmoneyOUTPUT)ASBEGINSELECTTotal=SUM(SUM)FROMBook,SellItemWHERE(Book.ID=SellItem.BookID)AND(Book.Press=Press)SELECTRecords=ROWCOUNTEND对于输出参数，必须在数据类型之后添加关键字“OUTPUT”通常存储过程在执行后会返回受影响的行数（通过“ROWCOUNT”获取），如果不需要这一结果，可以去掉“Records”参数和最后一条语句，并在第一条语句前添加“SETNOCOUNTON;”语句来取消计数,28,管理存储过程7-6,执行存储过程在ManagementStudio的对象资源管理器中，选择指定存储过程，通过右键菜单命令“执行存储过程”，可以打开“执行过程”对话框，对每个输入参数赋值后即可执行也可以采用T-SQL语句方式来执行需要对输入参数赋值，并指定输出参数类型例如,DECLARERecordsint,TotalsmallmoneyEXECdbo.GetSellItemsByPressPress=N清华大学出版社,Records=RecordsOUTPUT,Total=TotalOUTPUT,29,管理存储过程7-7,删除存储过程在ManagementStudio的对象资源管理器中，选择指定存储过程，通过右键菜单命令“删除”，可以删除存储过程也可以采用T-SQL语句方式来删除，例如,DROPPROCEDUREdbo.GetSellItemsByPress,30,管理函数4-1,SQLServer中的函数和存储过程类似，只不过函数有显式的返回值，主要用于封装复杂的计算功能函数可分为系统函数包括数学函数、字符串函数、日期和时间函数、加密函数等用户自定义函数标量函数：返回单个数据值表值函数：返回一个记录集,31,管理函数4-2,标量函数示例,CREATEFUNCTIONCalcSum(BookIDint,Numberint,Discountfloat)RETURNSmoneyASBEGINDECLAREPricemoneySELECTPrice=PriceFROMBookWHEREID=BookIDRETURN(Price*Number*Discount)END,32,管理函数4-3,表值函数示例,CREATEFUNCTIONSearchSellItem(BookNamenvarchar(256)RETURNSTABLEASRETURN(SELECTSellID,Name,Price,Number,Discount,SUMFROMBook,SellItemWHERE(Book.ID=SellItem.BookID)AND(UPPER(Book.Name)LIKE%+UPPER(BookName)+%),33,管理函数4-4,和存储过程类似，分别通过ALTERFUNCTION和DROPFUNCTION语句来修改和删除函数但是，EXEC语句只能执行标量函数函数可以在SQL语句中直接调用例如,CREATEPROCEDUREResetSellItemSumASBEGINSETNOCOUNTON;UPDATESellItemSETSUM=dbo.CalcSum(BookID,Number,Discount)WHERESUMISNULLEND,34,管理触发器4-1,触发器可以被看作一种特殊的存储过程，它能被特定的SQL语言程序激活，但不能使用EXEC语句来执行触发器分为以下两类DML（数据操纵语言）触发器属于表格所有，在对表格执行指定的INSERT、UPDATE、DELETE语句时触发DDL（数据定义语言）触发器属于数据库所有，在对数据库执行执行指定的CREATE、ALTER、DROP语句时触发,35,管理触发器4-2,DML触发器示例在对表格Book执行DELETE语句时触发，删除表格SellItem中与之相关的记录,CREATETRIGGEROnDelBookONBookFORDELETEASBEGINDECLAREBookIDintSELECTBookID=IDFROMdeletedDELETEFROMSellItemWHERESellItem.BookID=BookIDEND,deleted表示被删除的记录；类似的，inserted表示被插入的记录而对于UPDATE操作实际上是先删除原有记录，再插入新记录，则deleted表示更新前的记录；类似的，inserted表示更新后的记录,36,管理触发器4-3,对于DML触发器，还能够在不删除的情况下临时禁用通过对象资源管理器的右键菜单命令“禁用”通过SQL语句DISABLETRIGGEROnDelBook重新启用通过对象资源管理器的右键菜单命令“启用”通过SQL语句ENABLETRIGGEROnDelBook,37,管理触发器4-4,DDL触发器示例禁止删除数据库中的任何表格,CREATETRIGGEROnDropTableONDATABASEFORDROP_TABLEASBEGINPRINT不允许删除此数据库中的表格;ROLLBACK;END,38,基础数据库维护2-1,常见的数据维护工作包括数据导入、导出、备份和还原等在对象资源管理器中选择指定的数据库，通过右键菜单命令“任务导入数据”或“任务导出数据”可以启动“SQLServer导入和导出向导”，即可以在其他数据源和目标数据库之间复制和转换数据在对象资源管理器中选择指定的数据库，通过右键菜单命令“任务备份”可以打开“备份数据库”对话框；通过右键菜单命令“任务还原”可以打开“还原数据库”对话框,39,基础数据库维护2-2,SQLServer还支持数据库的分离和附加，便于不同服务器之间的数据转移数据库的附加前面在创建数据库时已经介绍过数据库的分离在对象资源管理器中选择指定的数据库，通过右键菜单命令“任务分离”可以将指定的数据库从服务器中移除分离出去的数据库的数据库文件（.mdf）和日志文件（.ldf）位于SQLServer安装目录下的“MSSQL.1MSSQLData”文件夹下，可以复制到其他位置去,40,SqlConnection对象,用法与OleDbConnection对象基本相同常用的连接字符串示例,DataSource=GLZ;Database=BookStore;IntegratedSecurity=true其中IntegratedSecurity表示使用Windows身份验证，取之可以是true或SSPI,Server=(local);InitialCatalog=BookStore;UserID=Administrator;Password=123456其中UserID和Password表示使用SQLServer身份验证,addr=28;InitialCatalog=BookStore;UserID=Administrator;Password=123456,DataSource=.SQLEXPRESS;AttachDbFilename=D:BookStore.mdf;IntegratedSecurity=SSPI使用SQLServerExpress服务器直接连接到SQLServer数据库文件,41,SqlCommand对象4-1,用法与OleDbCommand对象基本相同存储过程的执行,对于不带参数的存储过程SqlCommandmycmd=newSqlCommand(ResetSellItemSum,myconn);mycmd.CommandType=CommandType.StoredProcedure;mycmd.ExecuteNonQuery();,对于带参数的存储过程SqlCommandmycmd=newSqlCommand(GetSellItemsByPress,myconn);mycmd.CommandType=CommandType.StoredProcedure;SqlParameterpar1=newSqlParameter(press,SqlDbType.NVarChar);par1.Value=清华大学出版社;mycmd.Parameters.Add(par1);SqlParameterpar2=newSqlParameter(Records,SqlDbType.Int);par2.Direction=ParameterDirection.Output;mycmd.Parameters.Add(par2);SqlParameterpar3=newSqlParameter(Total,SqlDbType.Money);par3.Direction=ParameterDirection.Output;mycmd.Parameters.Add(par3);mycmd.ExecuteNonQuery();textBox1.Text=par2.Value.ToString();textBox2.Text=par3.Value.ToString();,42,SqlCommand对象4-2,使用事务处理,SqlCommandmycmd=newSqlCommand(DELETEFROMSellItemWHEREBookID=100,myconn);SqlTransactionmytran=null;trymyconn.Open();mytran=myconn.BeginTransaction();mycmd.Transaction=mytran;mycmd.ExecuteNonQuery();/第一条SQL语句的执行mycmd.CommandText=DELETEFROMBookWHEREID=100;mycmd.ExecuteNonQuery();/第二条SQL语句的执行mytran.Commit();catch(Exceptionexp)mytran.Rollback();,43,SqlCommand对象4-3,数据命令通常使用同步执行的方式此时每次调用SqlCommand对象的ExecuteXXX的方法，通常都要等方法执行完毕，然后再执行下一行代码而某些数据命令可能需要长时间的执行，这就会大大影响程序的工作效率通过设置SqlCommand对象的CommandTimeout属性可以设置执行命令的等待时间，执行时间超过该值就会报告错误以秒为单位，默认值30，0表示不限时在异步执行数据命令时，当前程序无需等待，可以继续去执行其它代码，示例如下,44,SqlCommand对象4-4,SqlConnectionmyconn=newSqlConnection();myconn.ConnectionString=Server=(local);IntegratedSecurity=true;”+“AsynchronousProcessing=true;SqlCommandmycmd=newSqlCommand(SELECT*FROMBook,myconn);myconn.Open();DateTimemydt=DatwTime.Now;IAsyncResultmyres=mycmd.BeginExecuteNonQuery();while(!myres.IsCompleted)if(DateTime.Now-mydt).Milliseconds)15000)mycmd.Cancel();MessageBox.Show(命令超时，已取消);if(myres.IsCompleted)mycmd.EndExecuteNonQuery(myres);MessageBox.Show(命令成功结束);,45,书店零售管理系统14-1,系统功能模块图,46,书店零售管理系统14-2,图书信息管理维护图书的基本信息（如书号、书名、作者、出版社、定价等）和库存数量会员信息管理维护书店注册会员的基本信息，包括注册新会员和会员信息的修改与删除零售管理处理书店收银台的销售业务统计查询对销量和销售额进行统计和分析,47,书店零售管理系统14-3,系统运行环境对于小型书店，数据库和应用程序可以安装在同一台电脑上对于较大规模的书店，数据库运行在单独的服务器上，应用程序可以分别安装在多台客户机上,48,书店零售管理系统14-4,开发要点通过数据库触发器来维护数据完整性在Windows应用程序配置文件中保存连接字符串使用打印文档控件来打印销售小票使用ReportView控件创建简单报表,49,书店零售管理系统14-5,数据库设计数据表设计（5个）Book：存放图书信息Customer：存放会员信息Discount：会员级别与相应折扣Sell：会员一次购书的交易总额记录SellItem：会员一次购书的明细记录数据视图V_BookSell：对Sell、SellItem和Book进行联合查询,如果书店修改会员方案，无需修改程序,50,书店零售管理系统14-6,存储过程P_GetSellDetail：获取指定交易号的交易明细触发器（3个）T_NewSellItem(ONSellItem)：每新增一条交易明细记录，就从Book表中减少图书的库存量T_NewSell(ONSell)：每新增一笔会员交易，就为该会员加上相应的积分（每消费一元增加一个积分）T_UpdateScore(ONCustomer)：积分达到1000以上时会员级别设为3，达到500以上时为2，否则为1,如果书店修改会员方案，无需修改程序,51,书店零售管理系统14-7,添加数据源新建一个Windows应用程序后，选择菜单命令“数据添加数据源”在打开的“数据源配置向导”的第一步中选择数据源类型为数据库，第二步指定服务器和数据库名称之后向导会提示是否将连接字符串保存到应用程序配置文件中，选择“是”，接受默认连接名称之后进入“选择数据库对象”，选中除了Discount之外的4个数据表、数据视图V_BookSell，以及存储过程P_GetSellDetail，接受默认数据集名称，完成数据源配置,52,书店零售管理系统14-8,添加完数据源之后，项目中添加了一个名为BookStoreDataSet的数据集，其中包含6个DataTable（4个对应数据表，一个对应数据视图，一个对应存储过程返回的记录集），以及与该数据集相关联的SqlDataAdapter对象一个配置文件app.config，这是一个XML文件，connectionStrings节中保存了连接字符串,53,书店零售管理系统14-9,读取连接字符串通过System.Configuration命名空间（需要添加引用）中的ConfigurationManager类可以读取连接字符串，代码如下,SqlConnectionmyconn=newSqlConnection(ConfigurationManager.ConnectionStrings“项目名称.Properties.Settings.BookStoreConnectionString.Connecti