SQLServer开发概述.ppt

C#程序设计教程,第14章 SQL Server开发概述,2,本章重点,SQL Server 2005简介 SQL Server Management Studio的使用 基础数据库维护 SqlConnection对象和SqlCommand对象 书店零售管理系统,3,SQL Server 2005的版本2-1,标准版（Standard） 包含关系数据库管理所需的基本功能，适合绝大多数中小型企业 企业版（Enterprise） 在关系数据库管理所需的基本功能基础上，提供了高度复杂的数据分析和事务处理功能，支持高智能化和高可靠性的数据仓库系统，适合大型和超大型企业 开发版（Development） 包含企业版所有功能，但存在许可证限制，主要供开发人员进行系统开发和测试，不允许商业用途,4,SQL Server 2005的版本2-2,工作组版（Workgroup） 包含关系数据库管理的核心功能，适合入门级的小型企业 简易版（Express） 包含基本的数据库服务器和客户端管理功能，可免费从微软公司网站下载，Visual Studio 2005也集成了该数据库版本，主要供初学者或非专业开发人员使用 由于不包括Management Studio等一些图形用户界面工具，数据管理大都需要通过Transact-SQL语句来完成,5,SQL Server Management Studio（SQL Server管理控制台）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服务 包括注册新的服务器、连接服务器、配置服务器等 管理数据库 包括创建、修改和删除数据库、数据表、视图、存储过程、函数、触发器、数据库用户、多维数据集等对象,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中，之后就可以对这些服务器进行统一管理 通过菜单命令“视图已注册的服务器”可以打开“已注册的服务器”窗口，查看所有已经注册的服务器 在该窗口中单击右键，在弹出菜单中选择“新建服务器注册”，可以注册服务器 在右键菜单中选择“新建服务器组”，可以对注册的服务器进行分组管理,9,服务器注册2-2,在“已注册的服务器”窗口中选中某个服务器，通过右键菜单命令“SQL Server配置管理器”可以直接启动SQL Server Configuration Manager，配置服务器 自学SQL Server Configuration Manager的使用,10,脚本编辑2-1,工具栏中，在“打开文件”按钮之前的“新建查询”、“数据库引擎查询”等按钮用于打开编辑脚本的查询编辑器，这是Management Studio中最常用的工具 脚本的编辑有两种方式 联机方式 选择指定的服务器，单击“新建查询”按钮，此时代码自动与该服务器连接，脚本的执行也将作用于此服务器,11,脚本编辑2-2,脱机方式 单击“数据库引擎查询”按钮，在打开的“连接到数据库引擎”对话框中选择“取消”，则代码和服务器断开连接 这种方式通常在服务器不可用时使用，而后同样可以创建、编辑和保存脚本代码 执行脚本时， “连接到数据库引擎”对话框将再次打开，此时可以选择要作用的服务器,12,创建、修改和删除数据库6-1,在SQL Server Management Studio的对象资源管理器中，选择指定服务器下的“数据库”结点，通过右键菜单命令“新建数据库”，可以新建数据库 还可以在查询编辑器中编写T-SQL语句来新建数据库 下面给出示例,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=UNLIMITED, 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,如果基于已有的数据库文件来创建数据库 在Management Studio的对象资源管理器中，选择指定服务器下的“数据库”结点，通过右键菜单命令“附加”，可以从已有的数据库文件新建数据库 如果采用T-SQL语句方式，示例代码如下,CREATE DATABASE BookStore ON (FILENAME=C:DataBookStore.mdf) FOR ATTACH,16,创建、修改和删除数据库6-5,修改数据库设置 在Management Studio的对象资源管理器中，选择指定数据库，通过右键菜单命令“属性”，可以修改数据库设置 如果采用T-SQL语句方式，示例代码如下,ALTER DATABASE BookStore ADD LOG FILE(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的对象资源管理器中，选择指定数据库下的“表”结点，通过右键菜单命令“新建表”，可以打开表设计器 如果采用T-SQL语句方式，使用CREATE TABLE语句 注意：表名前要有数据库名，如BookStore.Book,19,管理数据表2-2,修改数据表 在Management Studio的对象资源管理器中，选择指定的数据表 通过右键菜单命令“修改”，可以打开表设计器修改表结构 通过右键菜单命令“打开表”，可以查看和增删改表中数据记录 如果采用T-SQL语句方式 使用ALTER TABLE语句来修改表结构 使用SELECT、INSERT、DELETE和UPDATE语句可以查看和增删改表中数据记录,20,管理数据视图2-1,视图是虚拟表，其结构和数据都来源于数据表或其他视图的查询结果 新建视图 在Management Studio的对象资源管理器中，选择指定数据库下的“视图”结点，通过右键菜单命令“新建视图”，可以打开视图设计器 如果采用T-SQL语句方式，格式如下 CREATE VIEW 视图名 AS SELECT语句 注意：视图名前也要有数据库名,21,管理数据视图2-2,修改视图结构和删除视图的方式与修改表结构和删除表的方式类似，不再赘述 视图中显示的数据记录一般不允许增删改,22,存储过程、函数和触发器,存储过程、函数和触发器都是存放在数据库中的T-SQL语言程序，其作用类似于高级程序设计语言中的过程、函数和事件,23,管理存储过程7-1,SQL Server中有三种存储过程 用户定义的存储过程 扩展存储过程（一般不用） 系统存储过程 执行数据库基础管理工作，通常以“sp_”为前缀名,24,管理存储过程7-2,新建存储过程 在Management Studio的对象资源管理器中，选择指定数据库下的“可编程性”“存储过程”结点，通过右键菜单命令“新建存储过程”，可以打开查询编辑器，其中包含了创建存储过程的基本框架 例如,25,管理存储过程7-3,CREATE PROCEDURE GetSellItemsByPress (Press nvarchar(50) AS BEGIN SET NOCOUNT ON; SELECT ISBN, Name, Price, Number, Discount FROM Book, 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), Records int OUTPUT, Total smallmoney OUTPUT) AS BEGIN SELECT Total = SUM(SUM) FROM Book, SellItem WHERE (Book.ID = SellItem.BookID) AND (Book.Press = Press) SELECT Records= ROWCOUNT END 对于输出参数，必须在数据类型之后添加关键字“OUTPUT” 通常存储过程在执行后会返回受影响的行数（通过“ROWCOUNT”获取），如果不需要这一结果，可以去掉“Records ”参数和最后一条语句，并在第一条语句前添加 “SET NOCOUNT ON;”语句来取消计数,28,管理存储过程7-6,执行存储过程 在Management Studio的对象资源管理器中，选择指定存储过程，通过右键菜单命令“执行存储过程”，可以打开“执行过程”对话框，对每个输入参数赋值后即可执行 也可以采用T-SQL语句方式来执行 需要对输入参数赋值，并指定输出参数类型 例如,DECLARE Records int, Total smallmoney EXEC dbo.GetSellItemsByPress Press=N清华大学出版社, Records=Records OUTPUT, Total=Total OUTPUT,29,管理存储过程7-7,删除存储过程 在Management Studio的对象资源管理器中，选择指定存储过程，通过右键菜单命令“删除”，可以删除存储过程 也可以采用T-SQL语句方式来删除，例如,DROP PROCEDURE dbo.GetSellItemsByPress,30,管理函数4-1,SQL Server中的函数和存储过程类似，只不过函数有显式的返回值，主要用于封装复杂的计算功能 函数可分为 系统函数 包括数学函数、字符串函数、日期和时间函数、加密函数等 用户自定义函数 标量函数：返回单个数据值 表值函数：返回一个记录集,31,管理函数4-2,标量函数示例,CREATE FUNCTION CalcSum (BookID int, Number int, Discount float) RETURNS money AS BEGIN DECLARE Price money SELECT Price = Price FROM Book WHERE ID = BookID RETURN (Price * Number * Discount) END,32,管理函数4-3,表值函数示例,CREATE FUNCTION SearchSellItem (BookName nvarchar(256) RETURNS TABLE AS RETURN ( SELECT SellID, Name, Price, Number, Discount, SUM FROM Book, SellItem WHERE (Book.ID = SellItem.BookID) AND (UPPER(Book.Name) LIKE % + UPPER(BookName) + %) ),33,管理函数4-4,和存储过程类似，分别通过ALTER FUNCTION和DROP FUNCTION语句来修改和删除函数 但是，EXEC语句只能执行标量函数 函数可以在SQL语句中直接调用 例如,CREATE PROCEDURE ResetSellItemSum AS BEGIN SET NOCOUNT ON; UPDATE SellItem SET SUM = dbo.CalcSum(BookID, Number, Discount) WHERE SUM IS NULL END,34,管理触发器4-1,触发器可以被看作一种特殊的存储过程，它能被特定的SQL语言程序激活，但不能使用EXEC语句来执行 触发器分为以下两类 DML（数据操纵语言）触发器 属于表格所有，在对表格执行指定的INSERT、UPDATE、DELETE语句时触发 DDL（数据定义语言）触发器 属于数据库所有，在对数据库执行执行指定的CREATE、ALTER、DROP语句时触发,35,管理触发器4-2,DML触发器示例 在对表格Book执行DELETE语句时触发，删除表格SellItem中与之相关的记录,CREATE TRIGGER OnDelBook ON Book FOR DELETE AS BEGIN DECLARE BookID int SELECT BookID = ID FROM deleted DELETE FROM SellItem WHERE SellItem.BookID = BookID END,deleted表示被删除的记录；类似的，inserted表示被插入的记录 而对于UPDATE操作实际上是先删除原有记录，再插入新记录，则deleted表示更新前的记录；类似的，inserted表示更新后的记录,36,管理触发器4-3,对于DML触发器，还能够在不删除的情况下临时禁用 通过对象资源管理器的右键菜单命令“禁用” 通过SQL语句 DISABLE TRIGGER OnDelBook 重新启用 通过对象资源管理器的右键菜单命令“启用” 通过SQL语句 ENABLE TRIGGER OnDelBook,37,管理触发器4-4,DDL触发器示例 禁止删除数据库中的任何表格,CREATE TRIGGER OnDropTable ON DATABASE FOR DROP_TABLE AS BEGIN PRINT 不允许删除此数据库中的表格; ROLLBACK; END,38,基础数据库维护2-1,常见的数据维护工作包括数据导入、导出、备份和还原等 在对象资源管理器中选择指定的数据库，通过右键菜单命令“任务导入数据”或“任务导出数据”可以启动“SQL Server导入和导出向导”，即可以在其他数据源和目标数据库之间复制和转换数据 在对象资源管理器中选择指定的数据库，通过右键菜单命令“任务备份” 可以打开“备份数据库”对话框；通过右键菜单命令“任务还原” 可以打开“还原数据库”对话框,39,基础数据库维护2-2,SQL Server还支持数据库的分离和附加，便于不同服务器之间的数据转移 数据库的附加前面在创建数据库时已经介绍过 数据库的分离 在对象资源管理器中选择指定的数据库，通过右键菜单命令“任务分离” 可以将指定的数据库从服务器中移除 分离出去的数据库的数据库文件（.mdf）和日志文件（.ldf）位于SQL Server安装目录下的“MSSQL.1MSSQLData”文件夹下，可以复制到其他位置去,40,SqlConnection对象,用法与OleDbConnection对象基本相同 常用的连接字符串示例,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; 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 = CommandType.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 SqlParameter(“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 = 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 Book WHERE ID=100“; mycmd.ExecuteNonQuery(); /第二条SQL语句的执行 mytran.Commit(); catch(Exception exp) mytran.Rollback(); ,43,SqlCommand对象4-3,数据命令通常使用同步执行的方式 此时每次调用SqlCommand对象的ExecuteXXX的方法，通常都要等方法执行完毕，然后再执行下一行代码 而某些数据命令可能需要长时间的执行，这就会大大影响程序的工作效率 通过设置SqlCommand对象的CommandTimeout属性可以设置执行命令的等待时间，执行时间超过该值就会报告错误 以秒为单位，默认值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; IAsyncResult 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,图书信息管理 维护图书的基本信息（如书号、书名、作者、出版社、定价等）和库存数量 会员信息管理 维护书店注册会员的基本信息，包括注册新会员和会员信息的修改与删除 零售管理 处理书店收银台的销售业务 统计查询 对销量和销售额进行统计和分析,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 (ON SellItem)：每新增一条交易明细记录，就从Book表中减少图书的库存量 T_NewSell (ON Sell)：每新增一笔会员交易，就为该会员加上相应的积分（每消费一元增加一个积分） T_UpdateScore (ON Customer)：积分达到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类可以读取连接字符串，代码如下,SqlConnection myconn = new SqlConnection (ConfigurationManager.ConnectionStrings“项目名称.Properties.Settings. BookStoreConnectionString“.ConnectionString);,只要修改配置文件就可以保证数据连接的正确性 该配置文件默认保存在“项目目录binDebug”下,54,书店零售管理系统14-10,打印文档控件PrintDocument的使用 通过其PrintPage事件响应方法 private void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e) 在该方法中，使用e.Graphics对象的方法可以打印文本、图形、图像 如果需要打印预览功能，可以使用PrintPreviewDialog，将其Document属性设置为对应的PrintDocument控件名称即可,55,书店零售管理系统14-11,统计信息显示 TabControl控件提供多个选项卡TabPage，可以选择显示任意一个选项卡的内容 在每一个TabPage中放置一个ReportViewer控件（在工具箱的“数据”选项卡下） 添加报表 通过菜单命令“项目添加新项”打开“添加新项”对话框，选择其中的“报表”项，接受默认名称Report1.rdlc并确定,56,书店零售管理系统14-12,配置报表 双击打开报表，使用菜单命令“报表数据源”打开“报表数据源”对话框，选择“BookStoreDataSet_V_BookSell”，单击“添加到报表”按钮即完成数据源设置 可以通过数据视图的筛选表达式来控制报表中显示的数据记录 此时会显示一个“数据源”窗口 可以将其中的字段直接拖放到报表上，这样报表在运行时会自动读取和显示记录中的字段值 在打开报表编辑器的情况下，工具箱中会显示可用于报表的控件，拖放一个表控件到报表上,57,书店零售管理系统14-13,选择表的一行，通过右键菜单命令“插入组”，打开“分组和排序属性”对话框 选择第一个表达式为“=Fields!Name.Value”，表示按照书名分组 若设置父组，可实现多层分组报表 切换到“排序选项卡”，设置前两个表达式分别为“=Sum(Fields!Sum.Value)”、“=Sum(Fields!Number.Value)”，并设置它们的方向均为“Descending”，表示先按照销售额由高到低排序，再按照销量由高到低排序 之后再设计报表的字段内容与格式，包括要添加的字段、表头和表尾的文字,58,书店零售管理系统14-14,切换回窗体控件，选中一个ReportViewer控件，单击右上角的小箭头，在“ReportViewer任务”窗口中选择报表为Report1.rdlc即可 一般一个ReportViewer控件要与一个报表相关联 当修改了数据视图的筛选表达式后，要调用ReportViewer控件的RefreshReport方法刷新显示,