SQLServer开发概述.ppt

《SQLServer开发概述.ppt》由会员分享，可在线阅读，更多相关《SQLServer开发概述.ppt（58页珍藏版）》请在三一办公上搜索。

1、1,C#程序设计教程,第14章 SQL Server开发概述,2,本章重点,SQL Server 2005简介SQL Server Management Studio的使用基础数据库维护SqlConnection对象和SqlCommand对象书店零售管理系统,3,SQL Server 2005的版本2-1,标准版（Standard）包含关系数据库管理所需的基本功能，适合绝大多数中小型企业企业版（Enterprise）在关系数据库管理所需的基本功能基础上，提供了高度复杂的数据分析和事务处理功能，支持高智能化和高可靠性的数据仓库系统，适合大型和超大型企业开发版（Development）包含企业版所

2、有功能，但存在许可证限制，主要供开发人员进行系统开发和测试，不允许商业用途,4,SQL Server 2005的版本2-2,工作组版（Workgroup）包含关系数据库管理的核心功能，适合入门级的小型企业简易版（Express）包含基本的数据库服务器和客户端管理功能，可免费从微软公司网站下载，Visual Studio 2005也集成了该数据库版本，主要供初学者或非专业开发人员使用由于不包括Management Studio等一些图形用户界面工具，数据管理大都需要通过Transact-SQL语句来完成,5,SQL Server Management Studio（SQL Server管理控制台

3、）3-1,SQL Server Management Studio是一个集成的环境，用于访问、配置和管理所有SQL Server组件SQL Server Management Studio组合了大量图形工具和丰富的脚本编辑器，是SQL Server 2005中最重要的管理工具组件,6,SQL Server Management Studio（SQL Server管理控制台）3-2,SQL Server Management Studio主要功能管理SQL Server服务包括注册新的服务器、连接服务器、配置服务器等管理数据库包括创建、修改和删除数据库、数据表、视图、存储过程、函数、触发器、数据

4、库用户、多维数据集等对象,7,SQL Server Management Studio（SQL Server管理控制台）3-3,使用脚本编辑工具创建Transact-SQL(T-SQL)、MDX、DMX、XML等各种脚本对服务器角色、登录名、凭据等安全性功能进行管理监控当前活动，维护系统日志和全文索引,8,服务器注册2-1,服务器注册是指将本地或网络上的SQL Server服务器注册到本地SQL Server Management Studio中，之后就可以对这些服务器进行统一管理通过菜单命令“视图已注册的服务器”可以打开“已注册的服务器”窗口，查看所有已经注册的服务器在该窗口中单击右键，在弹

5、出菜单中选择“新建服务器注册”，可以注册服务器在右键菜单中选择“新建服务器组”，可以对注册的服务器进行分组管理,9,服务器注册2-2,在“已注册的服务器”窗口中选中某个服务器，通过右键菜单命令“SQL Server配置管理器”可以直接启动SQL Server Configuration Manager，配置服务器自学SQL Server Configuration Manager的使用,10,脚本编辑2-1,工具栏中，在“打开文件”按钮之前的“新建查询”、“数据库引擎查询”等按钮用于打开编辑脚本的查询编辑器，这是Management Studio中最常用的工具脚本的编辑有两种方式联机方式选择指

6、定的服务器，单击“新建查询”按钮，此时代码自动与该服务器连接，脚本的执行也将作用于此服务器,11,脚本编辑2-2,脱机方式单击“数据库引擎查询”按钮，在打开的“连接到数据库引擎”对话框中选择“取消”，则代码和服务器断开连接这种方式通常在服务器不可用时使用，而后同样可以创建、编辑和保存脚本代码执行脚本时，“连接到数据库引擎”对话框将再次打开，此时可以选择要作用的服务器,12,创建、修改和删除数据库6-1,在SQL Server Management Studio的对象资源管理器中，选择指定服务器下的“数据库”结点，通过右键菜单命令“新建数据库”，可以新建数据库还可以在查询编辑器中编写T-SQL语

7、句来新建数据库下面给出示例,13,创建、修改和删除数据库6-2,按照SQL Server默认设置新建数据库BookStore CREATE DATABASE BookStore,也可以设置数据的各种选项下面的语句将数据库文件和日志文件都存放在C盘的“Data”目录下。数据库文件初始大小为10MB，增量为1MB，不限制增长；日志文件初始大小为1MB，增量为10%，增长限制为10MB CREATE DATABASE BookStore ON PRIMARY(NAME=NBookStore,FILENAME=NC:DataBookStore.mdf,SIZE=10240KB,MAXSIZE=UNLI

8、MITED,FILEGROWTH=1024KB)LOG ON(NAME=NBookStore_log,FILENAME=NC:DataBookStore_log.ldf,SIZE=1024KB,MAXSIZE=10240KB,FILEGROWTH=10%),编写好代码后，按下快捷键F5或者工具栏中的“执行”按钮，即可执行SQL语句,14,创建、修改和删除数据库6-3,注意在Management Studio中执行的数据库操作直接显示在对象资源管理器中而执行T-SQL语句所做的修改，需要刷新对象资源管理器才能显示出来,15,创建、修改和删除数据库6-4,如果基于已有的数据库文件来创建数据库在Ma

9、nagement Studio的对象资源管理器中，选择指定服务器下的“数据库”结点，通过右键菜单命令“附加”，可以从已有的数据库文件新建数据库如果采用T-SQL语句方式，示例代码如下,CREATE DATABASE BookStoreON(FILENAME=C:DataBookStore.mdf)FOR ATTACH,16,创建、修改和删除数据库6-5,修改数据库设置在Management Studio的对象资源管理器中，选择指定数据库，通过右键菜单命令“属性”，可以修改数据库设置如果采用T-SQL语句方式，示例代码如下,ALTER DATABASE BookStoreADD LOG FILE

10、(NAME=NBookStore_log1,FILENAME=NC:DataBookStore_log1.ldf,SIZE=1024KB,MAXSIZE=5120KB,FILEGROWTH=10%),17,创建、修改和删除数据库6-6,删除数据库在Management Studio的对象资源管理器中，选择指定数据库，通过右键菜单命令“删除”，可以删除数据库如果采用T-SQL语句方式，示例代码如下,DROP DATABASE BookStore,18,管理数据表2-1,新建数据表在Management Studio的对象资源管理器中，选择指定数据库下的“表”结点，通过右键菜单命令“新建表”，可以

11、打开表设计器如果采用T-SQL语句方式，使用CREATE TABLE语句注意：表名前要有数据库名，如BookStore.Book,19,管理数据表2-2,修改数据表在Management Studio的对象资源管理器中，选择指定的数据表通过右键菜单命令“修改”，可以打开表设计器修改表结构通过右键菜单命令“打开表”，可以查看和增删改表中数据记录如果采用T-SQL语句方式使用ALTER TABLE语句来修改表结构使用SELECT、INSERT、DELETE和UPDATE语句可以查看和增删改表中数据记录,20,管理数据视图2-1,视图是虚拟表，其结构和数据都来源于数据表或其他视图的查询结果新建视图在

12、Management Studio的对象资源管理器中，选择指定数据库下的“视图”结点，通过右键菜单命令“新建视图”，可以打开视图设计器如果采用T-SQL语句方式，格式如下CREATE VIEW 视图名 AS SELECT语句注意：视图名前也要有数据库名,21,管理数据视图2-2,修改视图结构和删除视图的方式与修改表结构和删除表的方式类似，不再赘述视图中显示的数据记录一般不允许增删改,22,存储过程、函数和触发器,存储过程、函数和触发器都是存放在数据库中的T-SQL语言程序，其作用类似于高级程序设计语言中的过程、函数和事件,23,管理存储过程7-1,SQL Server中有三种存储过程用户定义的

13、存储过程扩展存储过程（一般不用）系统存储过程执行数据库基础管理工作，通常以“sp_”为前缀名,24,管理存储过程7-2,新建存储过程在Management Studio的对象资源管理器中，选择指定数据库下的“可编程性”“存储过程”结点，通过右键菜单命令“新建存储过程”，可以打开查询编辑器，其中包含了创建存储过程的基本框架例如,25,管理存储过程7-3,CREATE PROCEDURE GetSellItemsByPress(Press nvarchar(50)ASBEGIN SET NOCOUNT ON;SELECT ISBN,Name,Price,Number,Discount FROM B

14、ook,SellItem WHERE(Book.ID=SellItem.BookID)AND(Book.Press=Press)END参数必须以“”符号为前缀，并指定数据类型“SET NOCOUNT ON;”表示不对受影响的行数进行计数,26,管理存储过程7-4,修改存储过程在Management Studio的对象资源管理器中，选择指定存储过程，通过右键菜单命令“修改”，可以打开查询编辑器，使用“ALTER PROCEDURE”语句来修改存储过程例如,27,管理存储过程7-5,ALTER PROCEDURE dbo.GetSellItemsByPress(Press nvarchar(50)

15、,Records int OUTPUT,Total smallmoney OUTPUT)ASBEGIN SELECT Total=SUM(SUM)FROM Book,SellItem WHERE(Book.ID=SellItem.BookID)AND(Book.Press=Press)SELECT Records=ROWCOUNTEND对于输出参数，必须在数据类型之后添加关键字“OUTPUT”通常存储过程在执行后会返回受影响的行数（通过“ROWCOUNT”获取），如果不需要这一结果，可以去掉“Records”参数和最后一条语句，并在第一条语句前添加“SET NOCOUNT ON;”语句来取消计

16、数,28,管理存储过程7-6,执行存储过程在Management Studio的对象资源管理器中，选择指定存储过程，通过右键菜单命令“执行存储过程”，可以打开“执行过程”对话框，对每个输入参数赋值后即可执行也可以采用T-SQL语句方式来执行需要对输入参数赋值，并指定输出参数类型例如,DECLARE Records int,Total smallmoneyEXEC dbo.GetSellItemsByPressPress=N清华大学出版社,Records=Records OUTPUT,Total=Total OUTPUT,29,管理存储过程7-7,删除存储过程在Management Studio

17、的对象资源管理器中，选择指定存储过程，通过右键菜单命令“删除”，可以删除存储过程也可以采用T-SQL语句方式来删除，例如,DROP PROCEDURE dbo.GetSellItemsByPress,30,管理函数4-1,SQL Server中的函数和存储过程类似，只不过函数有显式的返回值，主要用于封装复杂的计算功能函数可分为系统函数包括数学函数、字符串函数、日期和时间函数、加密函数等用户自定义函数标量函数：返回单个数据值表值函数：返回一个记录集,31,管理函数4-2,标量函数示例,CREATE FUNCTION CalcSum(BookID int,Number int,Discount f

18、loat)RETURNS moneyASBEGINDECLARE Price moneySELECT Price=Price FROM Book WHERE ID=BookIDRETURN(Price*Number*Discount)END,32,管理函数4-3,表值函数示例,CREATE FUNCTION SearchSellItem(BookName nvarchar(256)RETURNS TABLE ASRETURN(SELECT SellID,Name,Price,Number,Discount,SUM FROM Book,SellItem WHERE(Book.ID=SellIte

19、m.BookID)AND(UPPER(Book.Name)LIKE%+UPPER(BookName)+%),33,管理函数4-4,和存储过程类似，分别通过ALTER FUNCTION和DROP FUNCTION语句来修改和删除函数但是，EXEC语句只能执行标量函数函数可以在SQL语句中直接调用例如,CREATE PROCEDURE ResetSellItemSum ASBEGIN SET NOCOUNT ON;UPDATE SellItem SET SUM=dbo.CalcSum(BookID,Number,Discount)WHERE SUM IS NULLEND,34,管理触发器4-1,触

20、发器可以被看作一种特殊的存储过程，它能被特定的SQL语言程序激活，但不能使用EXEC语句来执行触发器分为以下两类DML（数据操纵语言）触发器属于表格所有，在对表格执行指定的INSERT、UPDATE、DELETE语句时触发DDL（数据定义语言）触发器属于数据库所有，在对数据库执行执行指定的CREATE、ALTER、DROP语句时触发,35,管理触发器4-2,DML触发器示例在对表格Book执行DELETE语句时触发，删除表格SellItem中与之相关的记录,CREATE TRIGGER OnDelBook ON Book FOR DELETE ASBEGIN DECLARE BookID in

21、t SELECT BookID=ID FROM deleted DELETE FROM SellItem WHERE SellItem.BookID=BookIDEND,deleted表示被删除的记录；类似的，inserted表示被插入的记录而对于UPDATE操作实际上是先删除原有记录，再插入新记录，则deleted表示更新前的记录；类似的，inserted表示更新后的记录,36,管理触发器4-3,对于DML触发器，还能够在不删除的情况下临时禁用通过对象资源管理器的右键菜单命令“禁用”通过SQL语句DISABLE TRIGGER OnDelBook重新启用通过对象资源管理器的右键菜单命令“启用

22、”通过SQL语句ENABLE TRIGGER OnDelBook,37,管理触发器4-4,DDL触发器示例禁止删除数据库中的任何表格,CREATE TRIGGER OnDropTable ON DATABASE FOR DROP_TABLE ASBEGIN PRINT 不允许删除此数据库中的表格;ROLLBACK;END,38,基础数据库维护2-1,常见的数据维护工作包括数据导入、导出、备份和还原等在对象资源管理器中选择指定的数据库，通过右键菜单命令“任务导入数据”或“任务导出数据”可以启动“SQL Server导入和导出向导”，即可以在其他数据源和目标数据库之间复制和转换数据在对象资源管理器

23、中选择指定的数据库，通过右键菜单命令“任务备份”可以打开“备份数据库”对话框；通过右键菜单命令“任务还原”可以打开“还原数据库”对话框,39,基础数据库维护2-2,SQL Server还支持数据库的分离和附加，便于不同服务器之间的数据转移数据库的附加前面在创建数据库时已经介绍过数据库的分离在对象资源管理器中选择指定的数据库，通过右键菜单命令“任务分离”可以将指定的数据库从服务器中移除分离出去的数据库的数据库文件（.mdf）和日志文件（.ldf）位于SQL Server安装目录下的“MSSQL.1MSSQLData”文件夹下，可以复制到其他位置去,40,SqlConnection对象,用法与Ol

24、eDbConnection对象基本相同常用的连接字符串示例,Data Source=GLZ;Database=BookStore;Integrated Security=true其中Integrated Security表示使用Windows身份验证，取之可以是true或SSPI,Server=(local);Initial Catalog=BookStore;User ID=Administrator;Password=123456其中User ID和Password表示使用SQL Server身份验证,addr=192.168.0.128;Initial Catalog=BookStore

25、;User ID=Administrator;Password=123456,Data Source=.SQLEXPRESS;AttachDbFilename=D:BookStore.mdf;Integrated Security=SSPI使用SQL Server Express服务器直接连接到SQL Server数据库文件,41,SqlCommand对象4-1,用法与OleDbCommand对象基本相同存储过程的执行,对于不带参数的存储过程SqlCommand mycmd=new SqlCommand(ResetSellItemSum,myconn);mycmd.CommandType=Co

26、mmandType.StoredProcedure;mycmd.ExecuteNonQuery();,对于带参数的存储过程SqlCommand mycmd=new SqlCommand(GetSellItemsByPress,myconn);mycmd.CommandType=CommandType.StoredProcedure;SqlParameter par1=new SqlParameter(press,SqlDbType.NVarChar);par1.Value=清华大学出版社;mycmd.Parameters.Add(par1);SqlParameter par2=new SqlP

27、arameter(Records,SqlDbType.Int);par2.Direction=ParameterDirection.Output;mycmd.Parameters.Add(par2);SqlParameter par3=new SqlParameter(Total,SqlDbType.Money);par3.Direction=ParameterDirection.Output;mycmd.Parameters.Add(par3);mycmd.ExecuteNonQuery();textBox1.Text=par2.Value.ToString();textBox2.Text=

28、par3.Value.ToString();,42,SqlCommand对象4-2,使用事务处理,SqlCommand mycmd=new SqlCommand(DELETE FROM SellItem WHERE BookID=100,myconn);SqlTransaction mytran=null;try myconn.Open();mytran=myconn.BeginTransaction();mycmd.Transaction=mytran;mycmd.ExecuteNonQuery();/第一条SQL语句的执行 mycmd.CommandText=DELETE FROM Boo

29、k WHERE ID=100;mycmd.ExecuteNonQuery();/第二条SQL语句的执行 mytran.Commit();catch(Exception exp)mytran.Rollback();,43,SqlCommand对象4-3,数据命令通常使用同步执行的方式此时每次调用SqlCommand对象的ExecuteXXX的方法，通常都要等方法执行完毕，然后再执行下一行代码而某些数据命令可能需要长时间的执行，这就会大大影响程序的工作效率通过设置SqlCommand对象的CommandTimeout属性可以设置执行命令的等待时间，执行时间超过该值就会报告错误以秒为单位，默认值30

30、，0表示不限时在异步执行数据命令时，当前程序无需等待，可以继续去执行其它代码，示例如下,44,SqlCommand对象4-4,SqlConnection myconn=new SqlConnection();myconn.ConnectionString=Server=(local);Integrated Security=true;”+“Asynchronous Processing=true;SqlCommand mycmd=new SqlCommand(SELECT*FROM Book,myconn);myconn.Open();DateTime mydt=DatwTime.Now;IA

31、syncResult myres=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,图书信息管理维护图书的基本信息（如书号、书名、作者、出版社

32、、定价等）和库存数量会员信息管理维护书店注册会员的基本信息，包括注册新会员和会员信息的修改与删除零售管理处理书店收银台的销售业务统计查询对销量和销售额进行统计和分析,47,书店零售管理系统14-3,系统运行环境对于小型书店，数据库和应用程序可以安装在同一台电脑上对于较大规模的书店，数据库运行在单独的服务器上，应用程序可以分别安装在多台客户机上,48,书店零售管理系统14-4,开发要点通过数据库触发器来维护数据完整性在Windows应用程序配置文件中保存连接字符串使用打印文档控件来打印销售小票使用ReportView控件创建简单报表,49,书店零售管理系统14-5,数据库设计数据表设计（5个）B

33、ook：存放图书信息Customer：存放会员信息Discount：会员级别与相应折扣Sell：会员一次购书的交易总额记录SellItem：会员一次购书的明细记录数据视图V_BookSell：对Sell、SellItem和Book进行联合查询,如果书店修改会员方案，无需修改程序,50,书店零售管理系统14-6,存储过程P_GetSellDetail：获取指定交易号的交易明细触发器（3个）T_NewSellItem(ON SellItem)：每新增一条交易明细记录，就从Book表中减少图书的库存量T_NewSell(ON Sell)：每新增一笔会员交易，就为该会员加上相应的积分（每消费一元增加一

34、个积分）T_UpdateScore(ON Customer)：积分达到1000以上时会员级别设为3，达到500以上时为2，否则为1,如果书店修改会员方案，无需修改程序,51,书店零售管理系统14-7,添加数据源新建一个Windows应用程序后，选择菜单命令“数据添加数据源”在打开的“数据源配置向导”的第一步中选择数据源类型为数据库，第二步指定服务器和数据库名称之后向导会提示是否将连接字符串保存到应用程序配置文件中，选择“是”，接受默认连接名称之后进入“选择数据库对象”，选中除了Discount之外的4个数据表、数据视图V_BookSell，以及存储过程P_GetSellDetail，接受默认数

35、据集名称，完成数据源配置,52,书店零售管理系统14-8,添加完数据源之后，项目中添加了一个名为BookStoreDataSet的数据集，其中包含6个DataTable（4个对应数据表，一个对应数据视图，一个对应存储过程返回的记录集），以及与该数据集相关联的SqlDataAdapter对象一个配置文件app.config，这是一个XML文件，connectionStrings节中保存了连接字符串,53,书店零售管理系统14-9,读取连接字符串通过System.Configuration命名空间（需要添加引用）中的ConfigurationManager类可以读取连接字符串，代码如下,SqlCo

36、nnection myconn=new SqlConnection(ConfigurationManager.ConnectionStrings“项目名称.Properties.Settings.BookStoreConnectionString.ConnectionString);,只要修改配置文件就可以保证数据连接的正确性该配置文件默认保存在“项目目录binDebug”下,54,书店零售管理系统14-10,打印文档控件PrintDocument的使用通过其PrintPage事件响应方法private void printDocument1_PrintPage(object sender,S

37、ystem.Drawing.Printing.PrintPageEventArgs e)在该方法中，使用e.Graphics对象的方法可以打印文本、图形、图像如果需要打印预览功能，可以使用PrintPreviewDialog，将其Document属性设置为对应的PrintDocument控件名称即可,55,书店零售管理系统14-11,统计信息显示TabControl控件提供多个选项卡TabPage，可以选择显示任意一个选项卡的内容在每一个TabPage中放置一个ReportViewer控件（在工具箱的“数据”选项卡下）添加报表通过菜单命令“项目添加新项”打开“添加新项”对话框，选择其中的“报表

38、”项，接受默认名称Report1.rdlc并确定,56,书店零售管理系统14-12,配置报表双击打开报表，使用菜单命令“报表数据源”打开“报表数据源”对话框，选择“BookStoreDataSet_V_BookSell”，单击“添加到报表”按钮即完成数据源设置可以通过数据视图的筛选表达式来控制报表中显示的数据记录此时会显示一个“数据源”窗口可以将其中的字段直接拖放到报表上，这样报表在运行时会自动读取和显示记录中的字段值在打开报表编辑器的情况下，工具箱中会显示可用于报表的控件，拖放一个表控件到报表上,57,书店零售管理系统14-13,选择表的一行，通过右键菜单命令“插入组”，打开“分组和排序属性

39、”对话框选择第一个表达式为“=Fields!Name.Value”，表示按照书名分组若设置父组，可实现多层分组报表切换到“排序选项卡”，设置前两个表达式分别为“=Sum(Fields!Sum.Value)”、“=Sum(Fields!Number.Value)”，并设置它们的方向均为“Descending”，表示先按照销售额由高到低排序，再按照销量由高到低排序之后再设计报表的字段内容与格式，包括要添加的字段、表头和表尾的文字,58,书店零售管理系统14-14,切换回窗体控件，选中一个ReportViewer控件，单击右上角的小箭头，在“ReportViewer任务”窗口中选择报表为Report1.rdlc即可一般一个ReportViewer控件要与一个报表相关联当修改了数据视图的筛选表达式后，要调用ReportViewer控件的RefreshReport方法刷新显示,