5.5.1 存储过程的优点 ① 事务处理 ② 速度和性能 ③ 过程控制 ④ 安全性 ⑤ 减少网络流量和通信 ⑥ 模块化

Presentation on theme: "5.5.1 存储过程的优点 ① 事务处理 ② 速度和性能 ③ 过程控制 ④ 安全性 ⑤ 减少网络流量和通信 ⑥ 模块化"— Presentation transcript:

1

2 5.5.1 存储过程的优点 ① 事务处理 ② 速度和性能 ③ 过程控制 ④ 安全性 ⑤ 减少网络流量和通信 ⑥ 模块化
存储过程的优点 ① 事务处理 ② 速度和性能 ③ 过程控制 ④ 安全性 ⑤ 减少网络流量和通信 ⑥ 模块化 在数据库操作中，已经有了 SQL 语句，为何还需要存储过程。因为存储过程有 SQL 语句不能具备 的特点和优点，以至于存储过程能在严格的数据库驱动的应用程序中起到重要的作用。存储过程包括

3 存储过程的优点 ①事务处理 存储过程中，包括多个 SQL 语句，存储过程中的 SQL 语句属于事务处理的范畴。也就是说，存储过程类似于一个函数，当执行存储过程时，存储过程中的 SQL 语句要不都执行，要不都不执行。 ②速度和性能 存储过程由数据库服务器编译和优化，优化包括使用存储过程在运行时所必须的特定数据库的结构信息，这样在执行过程中会节约很多时间。存储过程完全在数据库服务器上执行，避免了大量的 SQL语句代码的传递，对于循环使用 SQL 语句而言，存储过程在速度和性能上都被优化。 ③过程控制 在编写存储过程中，可以使用 IF ELSE、FOR 以及 WHILE 循环，这些语句并不能在 SQL 语句中编 写，但是可以在存储过程中编写。当需要进行大量的和复杂的操作时，SQL 语句需要通过和编程语言一 同编写才能实现，而且实现复杂。相比之下，存储过程可以对过程进行控制。

4 存储过程的优点 ④安全性 存储过程也可以作为额外的安全层。开发人员或者用户，都只能对数据库中的存储过程进行使用，而无法直接对表进行数据操作，这样封装了数据操作，提高安全性。 ⑤减少网络流量和通信 存储过程是在数据库服务器上运行的，在使用存储过程中，无需将大量的 SQL 语句代码传递给数 据库服务器，而只需告诉数据库服务器执行哪个存储过程即可，而数据库服务器则会自行执行中间处理 操作，而不会通过网络传递不必要的数据。 ⑥模块化 正如代码编写规范和设计模式一样，通常情况下，开发团队或者公司需要严谨的代码编写风格和良好的协调能力，例如一个团队有人专门负责编码，有人专门负责数据库开发，那么可以让数据库开发人员负责数据库的开发，而编码的程序员只需要使用数据库开发人员设计的存储过程即可。在这种情况下， 数据库操作和应用程序编码的操作被分开，在维护、管理中，也非常方便，如果数据库存储过程的代码 出现问题，则只需要修改存储过程中的代码即可。

5 创建存储过程 存储过程可以通过 SQL Server Management Studio 创建，也可以使用.NET 框架通过编程实现.SQL Server Management Studio 创建存储过程比较方便，右击【对象资源管理器】中的相应的数据库，在下拉菜单中选择【可编程性】选项并选择【存储过程】选项。单击右键，选择【新建存储过程】选项，系统会自动创建一个新的标签（tab）窗口，以提供输入存储过程语句，如图所示。

6 创建存储过程 表示新闻的标题， C#中同样可以使用编程实现存储过程的创 建，示例代码如下所示。 string str = "CREATE PROC myproc" + "(" + int," + varchar(50) OUTPUT" + ")" + "AS" + "SET NOCOUNT ON" + int" + "SELECT + "FROM mynews" + "WHERE + "GROUP BY mynews.title" + SqlCommand cmd = new SqlCommand(str, con); cmd.ExecuteNonQuery(); //使用 cmd 的 ExecuteNonQuery 方法创建存储过程 上述代码通过使用 SqlCommand 对象的 ExecuteNonQuery()方法在数据库中创建了一个存储过程， 该存储过程用于返回了数据库中新闻的标题内容。

7 调用存储过程 创建存储过程之后，可以在.NET 应用程序中使用存储过程。存储过程可以看成是一个函数，可以 对存储过程进行调用，传递参数，接受返回值。在调用存储过程前，首先要与数据库建立连接，示例代 码如如下所示。 string str = "server='(local)';database='mytable';uid='sa';pwd='Sa'"; SqlConnection con = new SqlConnection(str); con.Open(); //打开连接 建立与数据库连接后，需要使用Command对象使用存储过程，Command对象接受的两个参数分别 为SQL 语句和Connection对象，在使用存储过程时，其中表示SQL语句的参数可以直接编写为存储过 程名，代码如下所示。 SqlCommand cmd = new SqlCommand("getdetail", con); //使用存储过程 默认情况下，Command 对象的类型是 SQL 语句，必须将 Command 对象的 CommandType 属性设置 为存储过程，系统才会调用存储过程，示例代码如下所示。 cmd.CommandType = CommandType.StoredProcedure; //设置 Command 对象的类型

8 5.5.3 调用存储过程 设置执行类型后，需要为存储过程增加参数，示例代码如下所示。
调用存储过程 设置执行类型后，需要为存储过程增加参数，示例代码如下所示。 参数设置完毕后，执行 ExecuteNonQuery 方法能够执行存储过程，就相当于开始调用函数，示例代 码如下所示。 SqlParameter spr; spr = SqlDbType.Int); spr = SqlDbType.NChar,50); spr.Direction = ParameterDirection.Output; spr = SqlDbType.Int); spr.Direction = ParameterDirection.ReturnValue; = 1; = null; //表示执行一个存储过程 //增加参数 id //增加参数 title //该参数是输出参数 //增加 count 参数 //该参数是返回值 //为参数初始化 cmd.ExecuteNonQuery(); //执行存储过程 当存储过程执行完毕后，能够获取参数和返回值，示例代码如下所示。 Label1.Text = //获取返回值

9 调用存储过程 使用 SQL Server Management Studio 同样能够执行存储过程，单击存储过程，单击右键，选择执行 存储过程，系统会提示输入参数，如图左所示。输入相应的参数，单击确定，系统会执行存储过程并返回相应的值，如图右所示。 使用 SQL Server Management Studio 能够快速的创建和使用存储过程，同样，能够通过编程的方法 实现存储过程的创建、参数的传递以及执行。存储过程的优点就在于速度比较快，能够控制过程、减少 网络通信和模块化，熟练的使用存储过程能够提高应用程序的性能和复用性。

10 制作用户界面（UI） 使用数据控件和数据源控件显式数据，则需要为控件制作相应的用户界面，让数据控件对用户呈现 的效果更好。首先，需要使用创建数据绑定控件GridView和数据源控件，并配置数据源控件，如图所示。 显然，对于用户而言，该数据源控件和数据绑定控件显然很不友好，这里就需要对数据绑定控件的 界面进行修改。通过配置数据绑定控件的相应格式可以修改数据绑定控件的外观，如图所示。

11 制作用户界面（UI） 开发人员能够自定义数据绑定控件的样式，并且修改某些列的顺序，这里使用了自动套用格式，并 将数据绑定控件的 width 属性设置为 100%，这样编写宽度就能够适应浏览器的大小，从而随着浏览器的大小而改变。数据绑定控件配置完成后，值得注意的是，需要勾选 SQL 语句的高级选项，让数据绑 定控件支持编辑、删除和选择，如图所示。

12 开发人员可以编写以上 HTML 实现更多的效果，当确定用户界面编写完毕后，就可以为数据绑定 控件选择操作了。
制作用户界面（UI） 配置SQL高级选项后，数据源控件就会自动生成INSERT、UPDATE、DELETE 语句，示例代码如 下所示。 在完成用户界面的配置后，系统生成的 HTML 代码如下所示。 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:mytableConnectionString %>" DeleteCommand="DELETE FROM [mynews] WHERE [ID] InsertCommand="INSERT INTO [mynews] ([TITLE]) VALUES SelectCommand="SELECT * FROM [mynews]" UpdateCommand="UPDATE [mynews] SET [TITLE] WHERE [ID] <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="TITLE" Type="String" /> </UpdateParameters> <InsertParameters> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3" DataKeyNames="ID" DataSourceID="SqlDataSource1" GridLines="Horizontal" Width="100%"> <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" /> <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" /> <Columns> <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" /> <asp:BoundField DataField="TITLE" HeaderText="TITLE" SortExpression="TITLE" /> </Columns> <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" /> <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" /> <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" /> <AlternatingRowStyle BackColor="#F7F7F7" /> </asp:GridView> 开发人员可以编写以上 HTML 实现更多的效果，当确定用户界面编写完毕后，就可以为数据绑定 控件选择操作了。

13 配置完成用户界面，则需要选择 GridView 控件的属性并配置 GridView 任务，如图所示。

14 5.6.2 使用 GridView 显示、删除、修改数据
GridView控件支持分页、排序、编辑、删除和选定内容等操作。在GridView控件中，首先必须勾 选【分页】复选框，然后再配置PageSize属性才能够让GridView控件支持分页功能。在GridView控件属性中如果勾选了【分页】复选框而不配置PageSize属性，则默认按10条数据分页。勾选了以启用分页、启用编辑、启用删除和启用选定内容后，GridView控件的界面如图左所示。因为在数据源控件配置的过程中，已经配置了支持编辑、删除和选择，所以在数据绑定控件中可以 选择启用编辑，启用删除和选定内容等操作，并且系统默认支持更新、插入、删除等操作，运行后如图右所示。

15 5.6.2 使用 GridView 显示、删除、修改数据
GridView 控件支持编辑、删除和选择，当单击编辑时，能够对选择的行进行数据编辑，如图左所示。编辑完成后，单击更新按钮则可以执行更新操作，而无需手动编写 UPDATE 操作，如图右所示。 当单击删除时，则会执行 DELETE 命令，而无需手动编写 DELETE 命令。GridView 控件支持分页、 排序、编辑、删除和选定内容，开发人员无需手动编写更新、删除、编辑、也无需手动编写分页，对 GridView 控件进行缓存设置能够提高应用程序性能，在对数据库的操作，编辑及更新中，GridView 控 件能够方便开发人员，简化代码。

16 使用 DataList 显示数据 DataList控件需要编辑HTML模板来显式数据，虽然在开发上，DataList控件比GridView更加复杂， 但是DataList控件能够实现更多效果。相比之下，DataList控件比GridView控件更加灵活，能够进行复 杂的事件编写和样式控制。选择【自动套用格式】复选框并将DataList控件的宽度设置为 100%，编辑 基本的用户界面，如图左所示。通过编辑ItemTemplate能够实现自定义模板，而无需像GridView一样，以表格形式呈现，编辑后 运行如图右所示。 DataList 控件执行数据操作基本上同 GridView 一样，DataList 控件与 GridView 相比只下，有着更 灵活的模板方案，能够实现更多的显示效果。

17 DataList 分页实现 DataList控件本身并不带分页实现，如果需要DataList能够实现分页效果，则需要通过代码实现DataList 控件的分页。DataList控件分页需要增加若干标签（Label）控件来显式“上一页”，“下一页” 等分页所需要的连接，示例代码如下所示。 <asp:Label ID="Label4" runat="server" Text="Label"></asp:Label> <asp:Label ID="Label3" runat="server" Text="Label"></asp:Label> <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label> 上述代码创建了三个 Label 控件，这三个控件并无需初始化，这三个控件通过编程实现上一页，下 一页的分页形式。如果需要执行分页，则需要编写 cs 页面代码，cs 页面代码如下所示。 PagedDataSource objPds = new PagedDataSource(); objPds.DataSource = this.SqlDataSource1.Select(new DataSourceSelectArguments()); objPds.AllowPaging = true; //设置是否允许分页 objPds.PageSize = 3; //设置分页条目数 int CurPage; //设置当前页码 Label2.Visible = false; //隐藏标签 Label4.Visible = false;

18 DataList 分页实现 上述代码初始化PagedDataSource对象，并将分页控件默认初始化属性Visible为false。其中 PagedDataSource 是封装分页相关属性的类。 if (Request.QueryString["Page"] != null) { //如果传递的页面不为空 CurPage = Convert.ToInt32(Request.QueryString["Page"]); //获取传递的参数 } els e CurPage = 1; //页面的值为 1 objPds.CurrentPageIndex = CurPage - 1; Label2.Visible = true; Label4.Visible = true; Label3.Text = "<a href=\"datalist.aspx\">首页</a>"; //设置索引 //显式标 签 //编写分页 Label2.Text = "<a href=\"datalist.aspx?page=" + Convert.ToString(CurPage + 1) + "\">下一页</a>"; Label4.Text = "<a href=\"datalist.aspx?page=" + Convert.ToString(CurPage - 1) + "\">上一页 </a>";

19 DataList 分页实现 上述代码通过传递的 Page 的值进行分页操作，如果传递的 Page 的值为不为空，则从数据源控件中 读取相应的数据，并显示到数据绑定控件中。 if (CurPage == 1) { Label4.Visible = false; } if (objPds.IsLastPage) Label2.Visible = false; DataList1.DataSourceID = ""; DataList1.DataSource = objPds; DataList1.DataBind(); //如果只有一个页面 //隐藏标签 //重新绑定数据 //编写 DataList 的数据源 //绑定数据源

20 DataList 分页实现 上述代码通过PagedDataSource 对象实现了分页效果，并且将分页条目数设置为3，当数据超过3条时，则会实现分页。运行后如图所示。 DataList 控件虽然不支持分页，但是能够通过编程实现 DataList 控件的分页效果。DataList 控件在 模板编辑和代码开发上虽然没有 GridView 方便，但是却提高了灵活性，能够自定义分页和数据显示。

21 1 5.6.5 使用 SQLHelper 操作数据库 创建SQLHelper
SQLHelper类经常在数据库开发中使用，不仅封装了数据库操作，也提高了数据库操作的安全性， SQLHelper在微软的开发中和DEMO中经常被使用，SQLHelper通常用于多层设计，如果需要使用SQLHelper类，可以到微软官方下载最新的SQLHelper类，也可以自行编写SQLHelper类。如果自行创建SQLHelper 类，则在解决方案管理器中新建一个类库，如左图所示。创建类库后，删除自动生成的Class1 类，并创建一个新类，类名为SQLHelper，如右图所示。

22 使用 SQLHelper 操作数据库 如果使用下载的SQLHelper类，则可以单击解决方案管理器，单击右键，选择添加现有项，然后选 择现有项目添加即可。在SQLHelper类下对数据操作进行封装，开发人员能够使用自己封装的类进行数 据操作，示例代码如下所示。

23 使用 SQLHelper 操作数据库 在上述代码中，还需要通过一个枚举类型进行switch操作，枚举类型用于判断执行的操作，示例代码如下所示。 public enum SDACmd { select, delete, update, insert } //定义枚举类型 定义的枚举类型用于在程序中进行筛选操作，用于指定 SQL 语句执行的操作。在 SQLHelper 类中， 还需要封装 DataReader 方法进行 DataReader 的封装和实现，开发人员能够使用 SQLHelper 类中的 DataReader 方法进行数据库的读取，示例代码如下所示。 public static SqlDataReader ExecReader(string sqlcmd, params SqlParameter[] paraList) { SqlConnection con = new SqlConnection(condb); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = sqlcmd; if (paraList != null) cmd.CommandType = CommandType.Text; foreach (SqlParameter para in paraList) { cmd.Parameters.Add(para); } } con.Open(); //创建连接对象 //创建 Command 对象 //使用连接 //配置 SQL 语句 //配置 Command 类型 //添加参数 //打开连接 SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);return sdr;

24 上述代码编写了 SQLHelper 类操作数据库的函数，通过执行函数并传递参数，即可实现数据库的插 入、更新和删除。
上述代码实现了DataReader对象，使用DataReader能够填充SqlDataReader对象并进行数据的循环输出。在ADO.NET中，通常需要执行SQL语句进行数据库的操作，在SQLHelper类中，同样需要封装执行 SQL 语句的操作以便能够快速执行数据操作。 public static void ExecNonQuery(string sqlcmd, params SqlParameter[] paraList) { using (SqlConnection con = new SqlConnection(condb)) SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = sqlcmd; if (paraList != null) cmd.CommandType = CommandType.Text; foreach (SqlParameter para in paraList) { cmd.Parameters.Add(para); } } con.Open(); cmd.ExecuteNonQuery(); //创建连接对象 //创建 Command 对象 //使用连接 //配置执行类型 //添加参数 //打开数据连接 //执行 SQL 语句 上述代码编写了 SQLHelper 类操作数据库的函数，通过执行函数并传递参数，即可实现数据库的插 入、更新和删除。

25 创建完成 SQLHelper 类后，需要为应用程序配置 SQLHelper 的基本属性，代码如下所示。
2 使用SQLHelper 创建完成 SQLHelper 类后，需要为应用程序配置 SQLHelper 的基本属性，代码如下所示。 private static readonly string database = "mytable"; private static readonly string uid = "sa"; private static readonly string pwd ="sa"; private static readonly string server = "local"; //配置数据库 //配置用户名 //配置用户会密码 // 配置服务器的值 上述代码为SQLHelper类配置了属性，当使用SQLHelper类时，系统会自动连接数据库，在完成使 用后，系统会自动关闭数据库。如果需要在当前项目使用SQLHelper类，则需要添加引用来使用SQLHelper类，右击现有项目，在下拉菜单中选择【添加】选项，在【添加】选项中选择【现有项】选 项，在弹出窗口中选择【项目】标签栏，就可以添加相同项目的类库，如图所示。

26 使用 SQLHelper 操作数据库 引用添加完毕，在使用 SQLHelper 页面的 CS 页面中，需要添加命名空间，命名空间的名称和创建 类库的名称相同，如果需要更改名称，可以通过修改类库的属性来修改。示例代码如下所示。 using MYSQL; 引用完毕后，就可以执行 SQL 语句，使用 SQLHelper 执行 SQL 语句非常方便，下面代码演示了如何执行插入、删除操作。 string strsql = "insert into mynews values ('SQLHelper 插入标题')"; SQLHelper.ExecNonQuery(strsql); //编写 SQL 语句 //执行 SQL 语句 上述代码运行后，则会执行插入操作，相比于 ADO.NET，封装后的代码更加简便易懂，删除操作 代码如下所示。 string strsql2 = "delete form mynews where id=3"; SQLHelper.ExecNonQuery(strsql2); //编写 SQL 语句 //执行 SQL 语句

27 当需要执行 SELECT 语句时，可以通过 SQLHelper.DataAdapter 获取数据，示例代码如下所示。
string strsql = "select * from mynews where id=3"; //编写 SQL 语句 DataSet ds = SQLHelper.DataAdapter(strsql, SQLHelper.SDACmd.select, "mydatatable"); 上述代码通过 SQLHelper.DataAdapter 获取数据，并创建了一个 mydatatable 虚拟表，填充 DataSet 对象。当需要获取 DataSet 对象中的数据时，和普通的 DataSet 对象一样。SQLHelper 封装了 ADO.NET 中的许多方法，为开发人员提高了效率，同时也增加了安全性和模块化的特性。 注意：上述代码中的 SQLHelper 类能够执行的是 SQL 语句，如果需要执行存储过程，则需要更改 SQLHelper 类中的相应的 CommandType 属性的值。

28