ADO.NET 小记（2012-4-18）

为了表示对周公（周金桥）的尊敬，在此声明，此文摘抄，只为学习

　

　ADO.NET介绍

　　ADO.NET是对Microsoft ActiveX Data Objects (ADO)一个跨时代的改进，它提供了平台互用性和可伸缩的数据访问。由于传送的数据都是XML格式的，因此任何能够读取XML格式的应用程序都可以进行 数据处理。事实上，接受数据的组件不一定要是ADO .NET组件，它可以是基于一个Microsoft Visual Studio的解决方案，也可以是任何运行在其它平台上的任何应用程序。
以前做数据库访问的时候，需要一直与数据库保持连接，直到获取完所有满足 需要的数据之后才会断开数据库连接，这种数据库访问方式称之为连接式数据访问技术。相比于以前的连接式数据访问技术，ADO.NET除了提供连接式数据访 问技术之外，还提供了另一种断开式解决方案，那就是在内存中模拟一个数据库，也就是内存中的数据库。
我们知道在实际的数据库技术中，每个数据库就 是一个业务逻辑单元，一般来说这个数据库包含了实现一个应用软件或者一个网站所需要的全部数据。在这里数据库就是顶级对象，我们引用创建数据库时所用到的 名词database来表示（因为创建数据库的SQL语句是create database），在一个数据库里可以包含有多个表(table)和视图(view)，除此之外还可以包含有一些外键关系等。在一个表(table)或 者视图(view)里可以包含多个列(column)和行(row)。
　　在ADO.NET中对上面提到的对象都在内存中进行了模拟，在内存中的数据 库对象称之为DataSet，一个内存中的数据库(DataSet)可以包含多个在内存中的表(DataTable)和内存中的视图 (DataView)，并且也允许在表存在一些关系(DataRelation)。同时在一个内存中的表(DataTable)或者内存中的视图 (DataView)中也允许存在行(DataRow)和列(DataColumn)。

　　物理数据库与内存数据库之间的各对象的对应关系如下：

　　　　
 　　　　　　

在上图中颜色相同的表示二者基本上是一种对应关系。

6个ADO.NET中的常用对象

　　Connection对象
　　Command对象
　　DataReader对象
　　DataAdapter对象
　　DataSet对象
　　DataTable对象
　　DataRow对象
　　DataColumn对象
　　参数化SQL语句
　　分页查询SQL语句

　　严格地说，在.net类库中并没有Connection、Command、DataAdapter和DataReader对象的，这是对相关的对象做了一 个抽象。在实际的开发中，我们经常用到的数据库有Access、SQL Server、Oracle、MySQL等，尽管大部分都遵循SQL国际化标准，但是它们在遵循标准的前提下又做了一些扩充，并且即使遵循了相同的标准， 但是实现方法并不相同，所以在某些情况下实现相同的功能可能在不同的数据库中SQL语句并不相同。
　　于是，在ADO.NET也定义了一套用于访问数 据库的标准，当然这个标准是以接口(interface)的形式提供的，各数据库厂商只要实现了这个接口就能在ADO.NET下正常工作（这也是接口的作 用，接口就是用于指定规范，自己本身并不实现，在Java中针对数据库访问也有一套接口留待各数据库来实现）。当然在.net类库中微软已经提供对 Access、SQL Server和Oracle数据库对上面提到的接口的实现。

　　在ADO.NET中定义的这一套接口是IDbConnection、IDbCommand、IDbDataAdapter和IDataReader，并且 还有一套实现这些接口的抽象类，分别是DbConnection、DbCommand、DbDataAdapter和DataReader。

　　　　　　 

　　上图最上面的是数据库访问接口，紧接着的是数据库访问抽象类，下面是针对不同数据库中实现这些接口的类，并且在括号中注明了其所在的命名空间，比如针对 MySQL数据的数据库访问类就位于MySql.Data.MySqlClient命名空间下，关于MySql的数据库访问类需要到MySql数据库的官 方网站去下载，它的官方网站是http://www.mysql.com。其它的数据库访问类都在.net类库中自带了。
注意： 访问SQL Server7.0以下版本的SQL Server数据库时需要用System.Data.OleDb命名空间下一套数据库访问类，虽然这一套数据库访问类同样也能访问SQL Server2000以上版本，但是没有办法利用SQL Server数据库的一些新特性，所以不建议使用System.Data.OleDb命名空间下的类访问SQL Server2000以上的版本。

针对SQL Server数据库讲述Connection、Command、DataAdapter和DataReader对象的用法，它们在其它数据库中也有对应的类，除了有些SQL语句语法不一样之外，用法是基本一样的。

Connection对象
　   Connection对象也称为数据库连接对象，Connection对象的功能是负责对数据源的连接。所有Connection对象的基类都是DbConnection类。
　　Connection对象有两个重要属性：
　　ConnectionString：表示用于打开 SQL Server 数据库的字符串；
　　State：表示 Connection 的状态，有Closed和Open两种状态。
　　Connection对象有两个重要方法：
　　Open()方法：指示打开数据库；
　　Close()方法：指示关闭数据库。

Command对象
　　Command对象也称为数据库命令对象，Command对象主要执行包括添加、删除、修 改及查询数据的操作的命令。也可以用来执行存储过程。用于执行存储过程时需要将Command对象的CommandType 属性设置为CommandType.StoredProcedure，默认情况下CommandType 属性为CommandType.Text，表示执行的是普通SQL语句。
Command主要有三个方法：
　　ExecuteNonQuery () ：执行一个SQL语句，返回受影响的行数，这个方法主要用于执行对数据库执行增加、更新、删除操作，注意查询的时候不是调用这个方法。
　　ExecuteReader ()：执行一个查询的SQL语句，返回一个DataReader对象。
　　ExecuteScalar ()：从数据库检索单个值。这个方法主要用于统计操作。

ExecuteScalar ()的用法

　　这个方法只返回查询结果集的第一行第一列代码如下

　　

  1. <%@ Page Language="C#" %>
  2. <%@ Import Namespace="System.Data.SqlClient" %>
  3. <%@ Import Namespace="System.Data" %>
  4. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  5. <script runat="server">
  6.     protected void Page_Load(object sender, EventArgs e)
  7.     {
  8.         //实例化Connection对象
  9.         SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AspNetStudy;Persist Security Info=True;User ID=sa;Password=sa");
  10.         //实例化Command对象
  11.         SqlCommand command = new SqlCommand("select count(1) as 男性人数 from UserInfo where sex=1", connection);
  12.         //打开Connection对象
  13.         connection.Open();
  14.         //执行SQL语句
  15.         int count = int.Parse(command.ExecuteScalar().ToString());
  16.         //关闭Connection对象
  17.         connection.Close();
  18.         Response.Write("在UserInfo表里共有" + count + "个男性。");
  19.     }
  20. </script>
  21. <html xmlns="http://www.w3.org/1999/xhtml" >
  22. <head runat="server">
  23.     <title>无标题页</title>
  24. </head>
  25. <body>
  26.     <form id="form1" runat="server">
  27.     <div>
  28.     
  29.     </div>
  30.     </form>
  31. </body>
  32. </html>

这个页面执行的结果如下：
 

在操作数据库的时候，为了提高性能，都遵循一个原则：数据库连接对象应该尽可能晚打开，尽可能早关闭。在上面的例子中，在Command对象需要执行数据库操作之前才打开数据库连接对象，执行数据库操作之后马上就关闭了数据库连接对象。希望初学者们记住这个原则。

DataReader对象
DataReader对象是一个读取行的只读流的方式，绑定数据时比使用数据集方式性能要高，因为它是只读的，所以如果要对数据库中的数据进行修改就需要借助其它方法将所作的更改保存到数据库。
DataReader对象不能通过直接实例化，必须借助与相关的Command对象来创建实例，例如用SqlCommand的实例的ExecuteReader()方法可以创建SqlDataReader实例。
因 为DataReader对象读取数据时需要与数据库保持连接，所以在使用完DataReader对象读取完数据之后应该立即调用它的Close()方法关 闭，并且还应该关闭与之相关的Connection对象。在.net类库中提供了一种方法，在关闭DataReader对象的同时自动关闭掉与之相关的 Connection对象，使用这种方法是可以为ExecuteReader()方法指定一个参数，如：
SqlDataReader reader =command.ExecuteReader(CommandBehavior.CloseConnection);
CommandBehavior是一个枚举，上面使用了CommandBehavior枚举的CloseConnection值，它能在关闭SqlDataReader时关闭相应的SqlConnection对象。
并且DataReader对象读取数据有三种方式：
一 种是按查询的时候列的索引用指定的方式来读取列值，无需做相应转换，如GetByte（int i）就是读取第i列的值并且转换成byte类型的值。第这种方法的优点是指定列后直接将该列的直接读取出来了，无需再转换，缺点是一旦指定的列不能按照指 定的方式转换时就会抛出异常，比如数据库里字段的类型是string类型或者该字段的值为空时按照GetByte(i)这种方式读取会抛出异常。
第 二种方式就是按照列索引的方式读取，在读取的时候并不进行值转换，如：reader[5]就是读取第5列的值（这里reader是一个Reader对象的 实例），这样得到的值是一个object类型的值，这也很好理解，因为在数据库可能存储各种类型的值，而object是所有类的基类，所以这个方法不会抛 出异常。如果要得到它的正确类型，还需要根据数据库里的字段进行进行相应转换。
最后一种是按照列名的方式去读，并且在读的时候也不进行相应转换，得到的是object类型的值。
综 合前面三种方式各有特点，第一种方式最直接，但是有可能抛出异常，第二种方式比第一种稍微灵活一些，我们可以根据读取到值为空（在.net里用 DBNull类来表示，可以表示数据库中任意数据类型的空值），我们就不进行相应的类型转换，避免出现异常。第三种方式按照列的名字来读取数据，也需要按 照第二种方式进行一定的转换。就性能来说第一种最高，第二种稍低，第三种最低（这很好理解，假设要在一个旅馆里找人直通过房间号找肯定比通过名字找快）， 就灵活性来说第三种最灵活，第二种次之，第一种最不灵活（假如在后来编写SQL语句中更改了列的索引，第一种和第二种都可能出现问题）。实际开发中根据实 际情况选择合适的方式。
下面是一个使用DataReader对象读取数据的例子，因为代码不是很多并且也不复杂，所以采用了单页模式，并且还演示了三种读取数据的方式，代码如下：


 

  1. <%@ Page Language="C#" %>
  2. <%@ Import Namespace="System.Data.SqlClient" %>
  3. <%@ Import Namespace="System.Data" %>
  4. <%@ Import Namespace="System.Text" %>
  5. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  6. <script runat="server">
  7. //注：为了方便没有使用代码页面方式，实际开发中这种做大很少见
  8.     protected void Page_Load(object sender, EventArgs e)
  9.     {
  10.         
  11.         
  12.     }
  13.     public void ShowData()
  14.     {
  15.         //实例化Connection对象
  16.         SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AspNetStudy;Persist Security Info=True;User ID=sa;Password=sa");
  17.         //实例化Command对象
  18.         SqlCommand command = new SqlCommand("select * from UserInfo where sex=1", connection);
  19.         //打开Connection对象
  20.         connection.Open();
  21.         //得到DataReader的实例，注意使用了CommandBehavior这个参数，以便同时关闭Connection
  22.         SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
  23.         //如果当前记录还有下一条记录，则循环不会终止
  24.         while (reader.Read())
  25.         {
  26.             Response.Write("<tr><td>" + reader.GetInt32(0) + "</td>");//按照列顺序和对应类型直接读取值
  27.             Response.Write("<td>" + reader.GetString(1) + "</td>");//按照列顺序和对应类型直接读取值
  28.             Response.Write("<td>" + reader.GetString(2) + "</td>");//按照列顺序和对应类型直接读取值
  29.             Response.Write("<td>" + reader.GetByte(3) + "</td>");//按照列顺序和对应类型直接读取值
  30.             //下面是按照列顺序直接读取值，并且根据值来判断最终显示结果
  31.             Response.Write("<td>" + (reader.GetBoolean(4)==true?"男":"女") + "</td>");
  32.             //根据列顺序读，列的值需要做相应转换
  33.             Response.Write("<td>" + reader[5].ToString() + "</td>");
  34.             //根据列名来读取，列的值需要做相应转换
  35.             Response.Write("<td>" + reader["Phone"] + "</td>");
  36.             Response.Write("<td>" + reader["Email"].ToString() + "</td></tr>/n");
  37.         }
  38.         reader.Close();
  39.     }
  40. </script>
  41. <html xmlns="http://www.w3.org/1999/xhtml" >
  42. <head runat="server">
  43.     <title>无标题页</title>
  44. </head>
  45. <body>
  46.     <form id="form1" runat="server">
  47.     <div>
  48.     <table border="1" cellpadding="0" cellspacing="0">
  49.     <tr><td>编号</td><td>帐号</td><td>真实姓名</td><td>年龄</td><td>性别</td><td>手机</td><td>电话</td><td>电子邮件</td></tr>
  50.     <% 
  51.         //在页面中调用后台代码，这样也能保证生成的代码不会位于<html></html>标记之外
  52.         ShowData(); 
  53.     %>
  54.     </table>
  55.     </div>
  56.     </form>
  57. </body>
  58. </html>

下面是运行结果：
 

 

DataAdapter对象
DataAdapter对象也称之为数据适配器对 象，DataAdapter对象利用数据库连接对象(Connection)连接的数据源，使用数据库命令对象(Command)规定的操作从数据源中检 索出数据送往数据集对象(DataSet)，或者将数据集中经过编辑后的数据送回数据源。
数据适配器将数据填入数据集时调用方法Fill()，语句如下：

 

  1. dataAdapter1.Fill (dataTable);//直接填充表

  或者

 

  1. dataAdapter1.Fill (dataSet11, "Products");//填充dataSet11数据集中的"Products"表

当dataAdapter1调用Fill() 方法时将使用与之相关联的命令组件所指定的 SELECT 语句从数据源中检索行。然后将行中的数据添加到 DataSet 中的DataTable 对象中或者直接填充到DataTable的实例中，如果 DataTable 对象不存在，则自动创建该对象。
当执行上述SELECT语句时，与数据库的连接必须有效，但不需要用语句将连接对象打开。如 果调用Fill()方法之前与数据库的连接已经关闭，则将自动打开它以检索数据，执行完毕后再自动将其关闭。如果调用Fill()方法之前连接对象已经打 开，则检索后继续保持打开状态。
注意：一个数据集中可以放置多张数据表。但是每个数据适配器只能够对应于一张数据表。

 

DataSet对象
DataSet对象也称为数据集对象，DataSet对象用于表示那些储存在内存中的数据，它相当于一个内存中的数据库。它可以包括多个DataTable对象及DataView对象。DataSet主要用于管理存储在内存中的数据以及对数据的断开操作。
由于DataSet对象提供了一个离线的数据源，这样减轻了数据库以及网络的负担，在设计程序的时候可以将DataSet对象作为程序的数据源。
下面是一个利用Adapter对象填充数据到DataTable（或DataSet）并显示的例子，这个例子采用了页面和代码分离模式，前台代码如下：


 

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="DataAdapter.aspx.cs" Inherits="DataAdapter" %>
  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  3. <html xmlns="http://www.w3.org/1999/xhtml" >
  4. <head runat="server">
  5.     <title>利用Adapter对象填充DataTable的例子</title>
  6. </head>
  7. <body>
  8.     <form id="form1" runat="server">
  9.     <div>
  10.     <table border="1" cellpadding="0" cellspacing="0">
  11.     <tr><td>编号</td><td>帐号</td><td>真实姓名</td><td>年龄</td><td>性别</td><td>手机</td><td>电话</td><td>电子邮件</td></tr>
  12.     <% 
  13.         //在页面中调用后台代码，这样也能保证生成的代码不会位于<html></html>标记之外
  14.         ShowData(); 
  15.     %>
  16.     </table>
  17.     </div>
  18.     </form>
  19. </body>
  20. </html>

相应的后台代码：


 

  1. using System;
  2. using System.Data;
  3. using System.Configuration;
  4. using System.Collections;
  5. using System.Web;
  6. using System.Web.Security;
  7. using System.Web.UI;
  8. using System.Web.UI.WebControls;
  9. using System.Web.UI.WebControls.WebParts;
  10. using System.Web.UI.HtmlControls;
  11. using System.Data.SqlClient;
  12. public partial class DataAdapter : System.Web.UI.Page
  13. {
  14.     protected void Page_Load(object sender, EventArgs e)
  15.     {
  16.     }
  17.     //供前台页面调用的方法，这个方法必须是protected或者public
  18.     protected void ShowData()
  19.     {
  20.         //实例化Connection对象
  21.         SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AspNetStudy;Persist Security Info=True;User ID=sa;Password=sa");
  22.         //实例化Command对象
  23.         SqlCommand command = new SqlCommand("select * from UserInfo where sex=0", connection);
  24.         SqlDataAdapter adapter = new SqlDataAdapter(command);
  25.         /*
  26.          下面的被注释掉的代码与上面的代码是等效的
  27.         SqlDataAdapter adapter = new SqlDataAdapter("select * from UserInfo where sex=0", connection);
  28.          */
  29.         DataTable data = new DataTable();
  30.         adapter.Fill(data);
  31.         /* 下面的被注释掉语句与上面填充DataTable的效果是一样的，我更倾向于没有注释掉的部分
  32.         DataSet ds = new DataSet();//实例化DataSet
  33.         adapter.Fill(ds, "UserInfo");//填充ds中的"UserInfo"表
  34.         DataTable data = ds.Tables["UserInfo"];
  35.         */
  36.         for (int i = 0; i < data.Rows.Count; i++)
  37.         {
  38.             Response.Write("<tr><td>" + data.Rows[i]["UserId"].ToString() + "</td>");
  39.             Response.Write("<td>" + data.Rows[i]["UserName"].ToString() + "</td>");
  40.             Response.Write("<td>" + data.Rows[i]["RealName"].ToString() + "</td>");
  41.             Response.Write("<td>" + data.Rows[i]["Age"].ToString() + "</td>");
  42.             //下面是按照列顺序直接读取值，并且根据值来判断最终显示结果
  43.             Response.Write("<td>" + (bool.Parse(data.Rows[i]["Sex"].ToString()) == true ? "男" : "女") + "</td>");
  44.             //根据列顺序读，列的值需要做相应转换
  45.             Response.Write("<td>" + data.Rows[i]["Mobile"].ToString() + "</td>");
  46.             //根据列名来读取，列的值需要做相应转换
  47.             Response.Write("<td>" + data.Rows[i]["Phone"].ToString() + "</td>");
  48.             Response.Write("<td>" + data.Rows[i]["Email"].ToString() + "</td></tr>/n");
  49.         }
  50.     }
  51. }

上面的程序代码基本与使用DataReader对象的例子相同，不过这里查询的所有女性用户，效果如下：
 

 

DataTable对象
DataTable 是 ADO.NET 库中的核心对象，就像普通的数据库中的表一样，它也有行和列。它主要包括DataRow和DataColumn，分别代表行和列。
(1) 数据行(DataRow)
数据行是给定数据表中的一行数据，或者说是数据表中的一条记录。它可能代表一个学生、一位用户、一张订单或者一件货物的相关数据。DataRow对象的方法提供了对表中数据的插入、删除、更新和查看等功能。提取数据表中的行的语句如下：
DataRow dr = dt.Rows[n];
其中：DataRow代表数据行类；dr是数据行对象；dt代表数据表对象; n代表行的序号(序号从0开始)。
(2) 数据列(DataColumn)
数据表中的数据列(又称字段)定义了表的数据结构，例如，可以用它确定列中的数据类型和大小，还可以对其他属性进行设置。例如，确定列中的数据是否是只读的、是否是主键、是否允许空值等；还可以让列在一个初始值的基础上自动增殖，增值的步长还可以自行定义。
 某列的值需要在数据行的基础上进行。语句如下：

  1. string dc = dr.Columns["字段名"].ToString();

  或者 

  1. string dc = dr.Column[i].ToString();//i表示对应的列索引

 综合前面的语句，若想取出数据表(dt)中第3条记录中的“姓名”字段，并将该字段的值放入一输入框(textBox1)中时，语句可以写成：

  1. DataRow dRow = dt.Rows[2 ];   // 从数据表提取行
  2. string textBox1.Text=dRow["CompanyName"].ToString();  // 从行中取出字段的值

 下面是一个利用代码在内存中创建表并显示的例子（仍然是采用了单页模式）：


 

  1. <%@ Page Language="C#" %>
  2. <%@ Import Namespace="System.Data" %>
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  4. <script runat="server">
  5.     
  6.     protected void Page_Load(object sender, EventArgs e)
  7.     {
  8.     }
  9.     private void CreateDataTable()
  10.     {
  11.         //实例化DataTable
  12.         DataTable data = new DataTable();
  13.         //创建一个名为"ID"的列，值类型为int
  14.         DataColumn dc1 = new DataColumn("ID", typeof(int));
  15.         dc1.AllowDBNull = false;//不允许为空
  16.         dc1.AutoIncrement = true;//自动递增
  17.         dc1.AutoIncrementSeed = 1;//列起始值为1
  18.         dc1.AutoIncrementStep = 1;//步长为1
  19.         data.Columns.Add(dc1);//添加列到表中
  20.         //创建一个新列,列名为"UserName"，值类型为string
  21.         DataColumn dc = new DataColumn("UserName", typeof(string));
  22.         dc.Unique = true;//设置唯一索引
  23.         dc.MaxLength = 20;//设置字段最大长度
  24.         data.Columns.Add(dc);
  25.         dc = new DataColumn("Birthday", typeof(DateTime));
  26.         dc.DefaultValue = DateTime.Now.AddYears(-2000);
  27.         data.Columns.Add(dc);
  28.         DataRow row=data.NewRow();//得到与刚才创建的表有相同结构的行
  29.         row["UserName"]="张飞";//设置列的UserName值，Birthday列采用默认值
  30.         data.Rows.Add(row);//添加行
  31.         row = data.NewRow();
  32.         row["UserName"] = "刘备";
  33.         row["Birthday"] = new DateTime(1, 3, 4);
  34.         data.Rows.Add(row);
  35.         row = data.NewRow();
  36.         row["UserName"] = "关羽";
  37.         row["Birthday"] = new DateTime(6, 11, 7);//跟周公同月同日了:)
  38.         data.Rows.Add(row);
  39.         Session["Data"] = data;//将创建的表和添加的数据保存到Session中
  40.     }
  41.     
  42.     protected void ShowData()
  43.     {
  44.         if (Session["Data"] == null)
  45.         {
  46.             CreateDataTable();
  47.         }
  48.         DataTable data=(DataTable)Session["Data"];
  49.         for (int i = 0; i < data.Rows.Count; i++)
  50.         {
  51.             Response.Write("<tr>");
  52.             Response.Write("<td>" + data.Rows[i]["ID"].ToString() + "</td>");
  53.             Response.Write("<td>" + data.Rows[i]["UserName"].ToString() + "</td>");
  54.             Response.Write("<td>" + DateTime.Parse(data.Rows[i]["Birthday"].ToString()).ToShortDateString() + "</td>");
  55.             Response.Write("</tr>");
  56.         }
  57.     }
  58. </script>
  59. <html xmlns="http://www.w3.org/1999/xhtml" >
  60. <head runat="server">
  61.     <title>自己创建DataTable的例子</title>
  62. </head>
  63. <body>
  64.     <form id="form1" runat="server">
  65.     <div>
  66.     <table border="1" width="400">
  67.     <tr><td>编号</td><td>用户名</td><td>生日</td></tr>
  68.     <% ShowData(); %>
  69.     </table>
  70.     </div>
  71.     </form>
  72. </body>
  73. </html>

上面代码我已经做了详尽的注释，它的运行效果如下：

 

参数化SQL语句
在前面的系列文章《asp.net夜话之三：表单和控件》中我提到了SQL注入的问 题，避免SQL注入的方法有两种：一是所有的SQL语句都存放在存储过程中，这样不但可以避免SQL注入，还能提高一些性能，并且存储过程可以由专门的数 据库管理员(DBA)编写和集中管理（这种做法我在一些公司见过），不过这种做法有时候针对相同的几个表有不同条件的查询，SQL语句可能不同，这样就会 编写大量的存储过程，所以有人提出了第二种方案：参数化SQL语句。例如我们在本篇中创建的表UserInfo中查找所有女性用户，那么通常情况下我们的 SQL语句可能是这样：

  1. select * from UserInfo where sex=0

在参数化SQL语句中我们将数值以参数化的形式提供，对于上面的查询，我们用参数化SQL语句表示为：

 

  1. select * from UserInfo where sex=@sex

我们再对代码中对这个SQL语句中的参数进行赋值，假如我们要查找UserInfo表中所有年龄大于30岁的男性用户，这个参数化SQL语句可以这么写：

  1. select * from UserInfo where sex=@sex and age>@age

下面是执行这个查询并且将查询结果集以DataTable的方式返回的代码：

 

  1. //实例化Connection对象
  2. SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AspNetStudy;Persist Security Info=True;User ID=sa;Password=sa");
  3. //实例化Command对象
  4. SqlCommand command = new SqlCommand("select * from UserInfo where sex=@sex and age>@age", connection);
  5. //第一种添加查询参数的例子
  6. command.Parameters.AddWithValue("@sex", true);
  7. //第二种添加查询参数的例子
  8. SqlParameter parameter = new SqlParameter("@age", SqlDbType.Int);//注意UserInfo表里age字段是int类型的
  9. parameter.Value = 30;
  10. command.Parameters.Add(parameter);//添加参数
  11. //实例化DataAdapter
  12. SqlDataAdapter adapter = new SqlDataAdapter(command);
  13. DataTable data = new DataTable();

上面的代码是访问SQL Server数据库的代码。如果本文中提到的数据分别在Access、MySQL、Oracle数据库，那么对应的参数化SQL语句及参数分别如下：

 

数据库 Access MySQL Oracle

SQL

语句

select * from UserInfo

where sex=? and age>? 

select * from UserInfo

where sex=?sex and age>?age 

select * from UserInfo

where sex=:sex and age>:age
参数  OleDbParameter MySqlParameter OracleParameter
实例
化参
数 		 OleDbParameter p=new OleDbParameter(“?”, OleDbType. Boolean);  MySqlParameter p=new MySqlParameter(“?sex”, MySqlDbType.Bit);  OracleParameter p=new OracleParameter(“:sex”, OracleType.Byte);
 赋值 p.Value=true;  p.Value=1;  p.Value=1;

   

  通过上面的实例代码我们可以看出尽管SQL语句大体相似，但是在不同数据库的特点，可能参数化SQL语句不同，例如在 Access中参数化SQL语句是在参数直接以“?”作为参数名，在SQL Server中是参数有“@”前缀，在MySQL中是参数有“?”前缀，在Oracle中参数以“:”为前缀。
注意：因为在Access中参数名都是“?”，所以给参数赋值一定要按照列顺序赋值，否则就有可能执行出错。

 

 

分页查询SQL语句
在实际中我们经常遇到表里的记录数非常庞大（数万至数百万），而一次只显示几十条数据的情况，如果我们直接用下面的SQL语句查询并填充到DataTable的话，将是一个非常恐怖的事情(假设UserInfo有几百万用户数据)：
Select * from UserInfo
在csdn论坛里我曾经就见过有一位朋友一张表里有8万条左右的记录，他每次都是将全部数据填充到DataTable，然后在循环中决定显示哪些数据，最后他在论坛上求助，说那样做的效果太慢了，当时把我和其它几个回帖的朋友吓懵了。其实他这种情况就应该用分页查询。
分页查询就是根据需要每次只返回所需要的数据，而不用每次都从数据库中全部把数据提取出来，这样可以降低程序与数据库之间的数据传送量，并且还可以提高程序的性能。
一般来说我们在数据量大的情况下总是会分页显示（谁也不会一下子将几万条数据全部一次性显示给用户），这样决定我们返回的查询结果集的参数有两个：当前显示的页数pageIndex和每页显示的记录条数size。
这里来举例在SQL　Server中我们如何写分页查询的SQL语句，假设我们按照UserID字段降序查询，每页显示5条记录。
下面是按照UserID字段降序查询全部所有用户的截图（出于某些原因，我把前面的14条记录删除掉了）：
 
那么第一页显示的应该是UserID最小的前5位用户。查询第一页要显示的用户信息应该是：
 
第一页我们可以按照对UserID降序查询，并利用top 5取出前5条记录。可是第二页的SQL语句我们该怎么写呢？下面的语句是否正确呢？
select top 10 * from UserInfo order by UserID asc
上 面的语句肯定不正确！因为top 10将会取到前10条记录，而我们每页只显示5条记录，第二页显示的数据应该是按照UserID降序排列之后第6条到10条记录，有没有办法做到这一点 呢？有两种办法：第一种就是前面我所提到的一次性将全部数据提取并填充到DataTable中，然后在for循环中通过i从5开始，并且i小于10（因为 在C#中所有的集合下标都是从0开始的，第六条记录的下标就是5）这种方法显示5条数据，但是这种做法的缺点我在前面也提到了。第二种办法就是在数据库中 过滤。我们通过分析发现，第二页要显示的记录的UserID字段有以下特点：它们是紧跟这第一页显示的记录之后的5条记录，也就是通过对UserID字段 进行降序排列时，它们是除了第一页数据之后的5条记录，也就是它们的UserID不在第一页的UserID之中，在SQL语句有一个not in这个正好可以排上用场。
首先我们按照对UserID进行降序排序，查询出前面第一页使用的数据的UserID，SQL语句及执行结果如下：
 
然后我们再按照对UserID进行降序查询，并且UserID不在第一页所使用的数据集中，使用的SQL语句及查询结果如下：
 
因为我们的数据UserID是从14开始，所以UserID在14至19的记录在第一页显示，UserID为20至24的记录在第二页显示，UserID为25至29的记录在第三页显示……依此类推，如果每页显示5条记录，那么第n页显示的数据记录的公式应该是：
select top 5 * from UserInfo where UserId not in
(select top (n-1)*5 UserID from UserInfo order by UserID asc)
order by UserID asc
需要注意的是：上面的查询，两次都是对UserID进行排序，并且都是降序，也就是说我们分页查询的时候写这种SQL语句的时候一定要都是对同一字段进行相同方式(asc或desc)的排序查询，这样查询的结果才会正确。
还有一点，如何查询数据库满足条件的记录条数呢？
还记得我讲过Command对象的ExecuteScalar（）方法吧，这个方法就是返回查询结果的第一行第一列的。
下面是例子：


 

  1. //实例化Connection对象
  2. SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AspNetStudy;Persist Security Info=True;User ID=sa;Password=sa");
  3. //实例化Command对象
  4.  SqlCommand command = new SqlCommand("select count(1) as 男性人数 from UserInfo where sex=1", connection);
  5. //打开Connection对象
  6.  connection.Open();
  7.  //执行SQL语句
  8. //得到第一行第一列的结果，这里是所有用户总数
  9.  int count = int.Parse(command.ExecuteScalar().ToString());
  10.  //关闭Connection对象
  11.  connection.Close();

最后一点，如果计算总页数的问题。假如我们有20条记录，每页显示5条，毫无疑问总共分4页显示。如果是21条记录呢？答案是分5页显示，尽管最后 一页只有一条记录，但是还是要显示的。这里也有一个公式，假如总共有m条记录，每页显示n条记录（这里m,n都是大于0的整数）那么需要显示全部记录所用 到的页数page为：

  1. page=(m%n)==0?(m/n):(m/n+1);

 

posted on 2012-04-18 11:27  水行者  阅读(298)  评论(0)  编辑  收藏  举报

导航