ADO.NET连接SQL数据库大总结

 ADO.NET连接SQL数据库

ADO.NET是.NET Framework中的一系列类库，它能够让开发人员更加方便的在应用程序中使用和操作数据。在ADO.NET中，大量的复杂的数据操作的代码被封装起来，所以当开发人员在ASP.NET应用程序开发中，只需要编写少量的代码即可处理大量的操作。ADO.NET和C#.NET、VB.NET不同的是，ADO.NET并不是一种语言，而是对象的集合。

7.3.1  ADO.NET基础

ADO.NET是由微软编写代码，提供了在.NET开发中数据库所需要的操作的类。在.NET应用程序开发中，C#和VB.NET都可以使用ADO.NET。

ADO.NET可以被看作是一个介于数据源和数据使用者之间的转换器。ADO.NET接受使用者语言中的命令，如连接数据库、返回数据集之类，然后将这些命令转换成在数据源中可以正确执行的语句。在传统的应用程序开发中，应用程序可以连接ODBC来访问数据库，虽然微软提供的类库非常的丰富，但是开发过程却并不简单。ADO.NET在另一方面，可以说简化了这个过程。用户无需了解数据库产品的API或接口，也可以使用ADO.NET对数据进行了操作。ADO.NET中常用的对象有：

q SqlConnection：该对象表示与数据库服务器进行连接。

q SqlCommand：该对象表示要执行的SQL命令。

q SqlParameter；该对象代表了一个将被命令中标记代替的值。

q SqlDataAdapter：该对象表示填充命令中的DataSet对象的能力。

q DataSet：表示命令的结果，可以是数据集，并且可以同BulletedList进行绑定。

通过使用上述的对象，可以轻松的连接数据库并对数据库中的数据进行操作。对开发人员而言，可以使用ADO.NET对数据库进行操作，在ASP.NET中，还提供了高效的控件，这些控件同样使用了ADO.NET让开发人员能够连接、绑定数据集并进行相应的数据操作。

7.3.2  连接SQL数据库

ADO.NET通过ADOConnection连接到数据库。和ADO的Connection对象相似的是，ADOConnection同样包括Open和Close方法。Open表示打开数据库连接，Close表示关闭数据库连接。在每次打开数据库连接后，都需要关闭数据库连接。

1．建立连接

在SQL数据库的连接中，需要使用.NET提供的SqlConnection对象来对数据库进行连接。在连接数据库前，需要为连接设置连接串，连接串就相当于告诉应用程序怎样找到数据库去进行连接，然后程序才能正确的与SQL建立连接，连接字串示例代码如下所示。

            server='服务器地址';database='数据库名称';uid='数据库用户名';pwd='数据库密码';

上述代码说明了数据库连接字串的基本格式，如果需要连接到本地的mytable数据库，则编写相应的SQL连接字串进行数据库的连接，示例代码如下所示。

            string strcon; //声明连接字串

            strcon = "server='(local)';database='mytable';uid='sa';pwd='sa';"; //设置连接字串

上述代码声明了一个数据库连接字串，SqlConnection类将会通过此字串来进行数据库的连接。其中，server是SQL服务器的地址，如果相对于应用程序而言数据库服务器是本地服务器，则只需要配置为(loacal)即可，而如果是远程服务器，则需要填写具体的ip。另外，uid是数据库登录时的用户名，pwd是数据库登录时使用的密码。在声明了数据库连接字串后，可以使用SqlConnection类进行连接，示例代码如下所示。

            string strcon; //声明连接字串

            strcon = "server='(local)';database='mytable';uid='sa';pwd='sa';"; //编写连接字串

            SqlConnection con = new SqlConnection(strcon); //新建SQL连接

            try

            {

                con.Open(); //打开SQL连接

                Label1.Text = "连接数据库成功"; //提示成功信息

            }

            catch

            {

                Label1.Text = "无法连接数据库"; //提示失败信息

            }

上述代码连接了本地数据库服务器中的mytable数据库，如果连接成功，则提示“连接数据库成功”，出现异常时，则提示“无法连接数据库”。

注意：在使用SqlConnection类时，需要使用命名空间using System.Data.SqlClient;而连接Access数据库时，需要使用命名空间using System.Data.OleDb。

2．填充DataSet数据集

DataSet数据集表示来自一个或多个数据源数据的本地副本，是数据的集合，也可以看作是一个虚拟的表。DataSet对象允许Web窗体半独立于数据源运行。DataSet能够提高程序性能，因为DataSet从数据源中加载数据后，就会断开与数据源的连接，开发人员可以直接使用和处理这些数据，当数据发生变化并要更新时，则可以使用DataAdapter重新连接并更新数据源。DataAdapter可以进行数据集的填充，创建DataAdapter对象的代码如下所示。

            SqlDataAdapter da=new SqlDataAdapter("select * from news",con); //创建适配器

上述代码创建了一个DataAdapter对象并初始化DataAdapter对象，DataAdapter对象的构造函数允许传递两个参数初始化，第一个参数为SQL查询语句，第二个参数为数据库连接的SqlConnection对象。初始化DataAdapter后，就需要将返回的数据的集合存放到数据集中，示例代码如下所示。

            DataSet ds = new DataSet(); //创建数据集

            da.Fill(ds, "tablename"); //Fill方法填充

上述代码创建了一个DataSet对象并初始化DataSet对象，通过DataAdapter对象的Fill方法，可以将返回的数据存放到数据集DataSet中。DataSet可以被看作是一个虚拟的表或表的集合，这个表的名称在Fill方法中被命名为tablename。

3．显式DataSet

当返回的数据被存放到数据集中后，可以通过循环语句遍历和显示数据集中的信息。当需要显示表中某一行字段的值时，可以通过DataSet对象获取相应行的某一列的值，示例代码如下所示。

            ds.Tables["tablename"].Rows[0]["title"].ToString(); //获取数据集

上述代码从DataSet对象中的虚表tablename中的第0行中获取title列的值，当需要遍历DataSet时，可以使用DataSet对象中的Count来获取行数，示例代码如下所示。

            for (int i = 0; i < ds.Tables["tablename"].Rows.Count; i++) //遍历DataSet数据集

            {

                Response.Write(ds.Tables["tablename"].Rows[i]["title"].ToString()+"<br/>");

            }

DataSet不仅可以通过编程的方法来实现显示，也可以使用ASP.NET中提供的控件来绑定数据集并显示。ASP.NET中提供了常用的显示DataSet数据集的控件，包括Repeater、DataList、GridView等数据绑定控件。将DataSet数据集绑定到DataList控件中可以方便的在控件中显示数据库中的数据并实现分页操作，示例代码如下所示。

            DataList1.DataSource = ds; //绑定数据集

            DataList1.DataMember = "tablename";

            DataList1.DataBind(); //绑定数据

上述代码就能够将数据集ds中的数据绑定到DataList控件中。DataList控件还能够实现分页、自定义模板等操作，非常方便开发人员对数据开发。

7.3.3  ADO.NET过程

从上一节中可以看出，在ADO.NET中对数据库的操作基本上需要三个步骤，即创建一个连接、执行命令对象并显式，最后再关闭连接。使用ADO.NET的对象，不仅能够通过控件绑定数据源，也可以通过程序实现数据源的访问。ADO.NET的过程如图7-20所示。

图7-20  ADO.NET规范步骤

从上图中可以归纳出，ADO.NET的规范步骤如下：

q 创建一个连接对象。

q 使用对象的Open方法打开连接。

q 创建一个封装SQL命令的对象。

q 调用执行命令的对象。

q 执行数据库操作。

q 执行完毕，释放连接。

掌握了这些初步的知识，就能够使用ADO.NET进行数据库开发。

7.4  ADO与ADO.NET

ADO.NET相比于ADO有很大的改进。使用ADO.NET，能够更加容易的进行数据库的开发，其中，一部分是针对开发人员做出的更改，包括易用性、适用性等，其次的更改让ADO.NET相比于ADO，更加灵活、强大、易于升级使用。

7.4.1  ADO概述

微软公司的ADO（ActiveX Data Objects）是一个用于存取数据源的COM组件。它提供了编程语言和统一数据访问方式OLEDB的一个中间层。允许开发人员编写访问数据的代码而不用关心数据库是如何实现的，而只用关心到数据库的连接。访问数据库的时候，关于SQL的知识不是必要的，但是特定数据库支持的SQL命令仍可以通过ADO中的命令对象来执行。

在开发过程中，ADO为OLEDB数据提供给予COM的应用程序级别的接口，对数据库的操作进行了封装，ADO支持各种开发者的需要。同时，ADO也能够像ADO.NET一样构建客户端记录集，使用松耦合记录集，并处理OLEDB的数据整形集合。

相比于ADO.NET，ADO还支持一些特殊的方法，例如可滚动的服务器端游标MOVENEXT。但是，使用服务器游标需要使用和保存数据库资源，所以当大量的游标在服务器端被使用时，则可能对应用程序的性能和可缩放性产生极大的负面影响。使用ADO，还需要对防火墙进行配置以启用COM的发送请求才能够进行数据交互，这样可能造成一定的安全问题。ADO编程模型如下所示：

q 连接数据源(Connection)，可选择开始事务。

q 可选择创建表示SQL命令的对象(Command)。

q 可选择指定列、表，以及 SQL 命令中的值作为变量参数(Parameter)。

q 执行命令（Command、Connection或Recordset）。

q 如果命令以行返回，将行存储在存储对象中(Recordset)。

q 可选择创建存储对象的视图以便进行排序、筛选和定位数据(Recordset)。

q 编辑数据。可以添加、删除或更改行、列(Recordset)。

q 在适当情况下，可以使用存储对象中的变更对数据源进行更新(Recordset)。

q 在使用事务之后，可以接受或拒绝在事务中所做的更改。结束事务(Connection)。

从上述的编程模型可以看出，ADO.NET在很多方面和ADO比较相近，但是ADO.NET并不是ADO的.NET版本，ADO和ADO.NET是两种不同的数据访问方式。

7.4.2  ADO.NET与ADO

ADO.NET的名称起源于ADO（ActiveX Data Objects），ADO用于在以往的Microsoft技术中进行数据的访问。所以微软希望通过使用ADO.NET名称来向开发人员表明，这是在.NET编程环境和Windows环境中优先使用的数据访问接口。

ADO.NET提供了平台互用性和可伸缩的数据访问，ADO.NET增强了对非连接编程模式是支持，并支持RICH XML。由于传送的数据都是XML格式的，因此任何能够读取XML格式的应用程序都可以进行数据处理。事实上，接受数据的组件不一定要是ADO.NET组件，它可以是基于一个Microsoft Visual Studio的解决方案，也可以是任何运行在其他平台上的任何应用程序。

可以说传统的ADO和ADO.NET是两种不同的数据访问方式，无论是在内存中保存数据，还是打开和关闭数据库的操作模式都不尽相同。

1．数据在内存中的存在形式

使用ADO.NET时，数据保存在内存中并且是以DataSet数据集的形式存放在内存中，而ADO中，则是以RecordSet记录集的形式存放在内存中。

在ASP的开发过程中，连接数据库后，通常将查询操作的数据的集合保存在记录集中，并使用MOVENEXT等方法进行遍历，也可以使用其他的方法进行查询，提取任意行。而在ASP.NET中，ADO.NET提供的DataRelation对象维护有关主记录和详细资料记录的信息，并提供方法使用户可以获取与正在操作的记录相关的记录。

2．数据的表现形式

在ADO中，记录集的表现形式像一个表。如果需要包含来自多个数据库的表的数据，就必须使用复杂的SQL语句中的JOIN查询将各个数据库的表的数据组合到单个记录集中。

而在ADO.NET中，数据集本身是一个表或多个表的集合。相对于记录集而言，数据集可以保存多个独立的表并维护有关表之间关系的信息。因此，ADO.NET能够维护和保存数据结构复杂的表，模仿数据库的结构，例如表的自关联，以及有一对多或多对多的关系表。

3．数据的连接和断开

ADO是为连接的访问而设计的，相比之下，ADO.NET打开连接的时间仅仅足够数据库的操作。数据集可以将行读入，然后断开与数据库的连接，再对数据集中记录进行更改。当需要将数据集中的资源更新到数据库时，ADO.NET再与数据库连接并更新。

4．数据共享

再ADO中，如果需要实现ADO.NET中断开数据连接传送数据的功能，则必须在COM组件之间互相传送，这样就造成了安全性问题。在杀毒软件中，一些杀毒软件可能会默认禁止COM组件之间的通信，这样就造成了开发人员的维护困难。而ADO.NET能够使用DataSet传送数据而不需要考虑防火墙的限制，是因为DataSet传送的数据集会被转换成XML流来传送。ADO.NET相对于ADO再数据共享上，有如下优点：

q 突破COM数据类型的限制：由于ADO.NET基于XML流传送数据，所以对数据类型没有限制。

q 减少数据类型的转换：ADO.NET相对于ADO而言，减少了大量的数据类型的转换，提高了性能。

q 可以穿透防火墙：基于XML流传送数据的方法能够轻松穿透防火墙。

5．构架设计

在构架设计上ADO.NET与ADO也是不同的，ADO.NET相对于ADO更加方便和简洁，从设计的角度上来说，ADO.NET设计的更加完善。

在ADO中，通过使用ADORecordSet对象进行数据的连接和操作，ADORecordSet对象是一个庞大的对象，它提供了多种类型的游标能力，例如快速的即时的游标到无连接的客户端游标。但是使用ADORecordSet对象，很难对数据的操作的方法进行自定义。而在ADO.NET中，ADO.NET将ADO中ADORecordSet对象的方法进行了一个拆分，将其中的若干功能分成多个类，通过类之间的调用来实现。这样就方便了开发人员自定义数据的连接和操作。

注意：在应用程序中，ADO与ADO.NET是可以共存的，因为在.NET中同样可以使用COM互操作服务使用ADO。

7.5  ADO.NET 常用对象

ADO.NET提供了一些常用对象来方便开发人员进行数据库的操作，这些常用的对象通常会使用在应用程序开发中，对于中级的开发人员而言，熟练的掌握这些常用的ADO.NET对象，能够自行封装数据库操作类，来简化开发。ADO.NET的常用对象包括：

q Connection对象。

q DataAdapter对象。

q Command对象。

q DataSet对象。

q DataReader对象。

上面的对象在.NET应用程序操作数据中是非常重要的，它们不仅提供了数据操作的便利，同时，还提供了高级的功能给开发人员。为开发人员解决特定的需求。

7.6  Connection连接对象

在.NET开发中，通常情况下开发人员被推荐使用Access或者SQL作为数据源，若需要连接Access数据库，可以使用System.Data.Oledb.OleDbConnection对象来连接；若需要连接SQL数据库，则可以使用System.Data.SqlClient.SqlConnection对象来连接。使用System.Data.Odbc.OdbcConnection可以连接ODBC数据源，而System.Data.OracleClient.OracleConnecton提供了连接Oracle的一些方法。本章主要讨论连接Access和SQL数据库。

7.6.1  连接SQL数据库

如需要连接SQL数据库，则需要使用命名空间System.Data.SqlClient和System.Data.OleDb。使用System.Data.SqlClient和System.Data.OleDb能够快速的连接SQL数据库，因为System.Data.SqlClient和System.Data.OleDb都分别为开发人员提供了连接方法，示例代码如下所示。

            using System.Data.SqlClient; //使用SQL命名空间

            using System.Data.Oledb //使用Oledb命名空间

1．使用System.Data.SqlClient

连接SQL数据库，则需要创建SqlConnection对象，SqlConnection对象创建代码如下所示。

            SqlConnection con = new SqlConnection(); //创建连接对象

            con.ConnectionString = "server='(local)';database='mytable';uid='sa';pwd='sa'"; //设置连接字串

上述代码创建了一个SqlConnection对象，并且配置了连接字串。SqlConnection对象专门定义了一个专门接受连接字符串的变量ConnectionString，当配置了ConnectionString变量后，就可以使用Open()方法来打开数据库连接，示例代码如下所示。

            SqlConnection con = new SqlConnection(); //创建连接对象

            con.ConnectionString = "server='(local)';database='mytable';uid='sa';pwd='sa'";

            try

            {

                con.Open(); //尝试打开连接

                Label1.Text = "连接成功"; //提示打开成功

                con.Close(); //关闭连接

            }

            catch

            {

                Label1.Text = "连接失败"; //提示打开失败

            }

上述代码尝试判断是否数据库连接被打开，使用Open方法能够建立应用程序与数据库之间的连接。与之相同的是，可以使用默认的构造函数来对数据库连接对象进行初始化，示例代码如下所示。

            string str = "server='(local)';database='mytable';uid='sa';pwd='Sa'"; //设置连接字串

            SqlConnection con = new SqlConnection(str); //默认构造函数

上述代码与使用ConnectionString变量的方法等价，其默认的构造函数中已经为ConnectionString变量进行了初始化。

2．使用System.Data.OleDb

ADO.NET中，具有相同功能的函数一般具有相同的参数和字段以及方法。所以，在.NET开发中，开发人员能够很快的适应新的操作。同样System.Data.OleDb也提供了Open方法以及ConnectionString字段，示例代码如下所示。

            OleDbConnection con= new OleDbConnection(); //创建连接对象

            con.ConnectionString="Provider=SQLOLEDB;Data 

            Source=(local);Initial Catalog=mytable;uid=sa;pwd=sa"; //初始化连接字串

            try

            {

                con.Open(); //尝试打开连接

                Label1.Text = "连接成功"; //提示连接成功

                con.Close(); //关闭连接

            }

            catch

            {

                Label1.Text = "连接失败"; //提示连接失败

            }

同样，OleDbConnection也提供默认的构造函数来初始化连接变量，示例代码如下所示。

            string str =

            "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=mytable;uid=sa;pwd=sa";

            OleDbConnection con = new OleDbConnection(str);

上述代码通过使用构造函数初始化连接变量进行相应的ConnectionString变量的配置。值得注意的是，从上面代码可以看出，连接字串一般都通过使用用户名和密码的形式连接，这样保证了连接的安全性。另外，连接字串还可以使用Trusted_Connection=Yes来声明这是一个值得信任的连接字串，而不需要输入用户名和密码，示例代码如下所示。

            string str2 =

            "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=mytable;Trusted_Connection=Yes";

            OleDbConnection con = new OleDbConnection(str2);

7.6.2  连接Access数据库

Access是一种桌面级数据库，虽然与SQL相比，Access数据库的性能和功能都并不强大，但是Access却是最常用的数据库之一。对于小型应用和小型企业来说，Access数据库也是开发中小型软件的最佳选择。

1．创建Access数据库

Access是Office组件之一，当安装了Office后，就可以新建Access数据库，在桌面或任何文件夹中单击右键就能够创建Access数据库。创建完成后，双击数据库文件就能够打开数据库并建立表和字段，如图7-21所示。

同样，Access数据库也需要创建表和字段，基本方法与SQL数据库相同，但是在数据类型上，自动增长编号作为单独的数据类型而存在。开发人员能够在表窗口中创建表mytable和相应字段，如图7-22所示。

    

图7-21  创建Access数据库                       图7-22  创建Access数据库的表

创建完成后可以使用System.Data.OleDb的对象进行数据库的连接和数据操作。

注意：Access数据库是一个桌面级的数据库，其数据都会存放在一个文件中而不是存放在数据库服务器中。

2．使用System.Data.OleDb

在使用System.Data.OleDb时，只需要修改连接字串即可。在这里需要强调一点的是，Access数据库是一种桌面级的数据库，同文件类型的数据库类似，所以连接Access数据库时，必须指定数据库文件的路径，或者使用Server.MapPath来确定数据库文件的相对位置。示例代码如下所示。

            string str = "provider=Microsoft.Jet.OLEDB.4.0 ;Data Source="

            + Server.MapPath("access.mdb") + ""; //使用相对路径

            OleDbConnection con = new OleDbConnection(str); //构造连接对象

            try

            {

                con.Open(); //打开连接

                Label1.Text = "连接成功"; //提示连接成功

                con.Close();

            }

            catch(Exception ee) //抛出异常

            {

                Label1.Text = "连接失败";

            }

Server.MapPath能够确定文件相对于当前目录的路径，如果不使用Server.MapPath，则需要指定文件在计算机的路径，如“D:/服务器/文件夹/数据库路径”。但是这样会暴露数据库的物理路径，让程序长期处于不安全的状态。

7.6.3  打开和关闭连接

无论是使用System.Data.SqlClient还是System.Data.OleDb创建数据库连接对象，都可以使用Open方法来打开连接。同样，也可以使用Close方法来关闭连接，示例代码如下所示。

            SqlConnection con = new SqlConnection(str); //创建连接对象

            OleDbConnection con2 = new OleDbConnection(str2); //创建连接对象

            con.Open(); //打开连接

            con.Close(); //关闭连接

            con2.Open(); //打开连接

            con2.Close(); //关闭连接

注意：如果使用了连接池，虽然显式的关闭了连接对象，其实并不会真正的关闭与数据库之间的连接，这样能够保证再次进行连接时的连接性能。

7.7  DataAdapter适配器对象

在创建了数据库连接后，就需要对数据集DataSet进行填充，在这里就需要使用DataAdapter对象。在没有数据源时，DataSet对象对保存在Web窗体可访问的本地数据库是非常实用的，这样降低了应用程序和数据库之间的通信次数。然而DataSet必须要与一个或多个数据源进行交互，DataAdapter就提供DataSet对象和数据源之间的连接。

为了实现这种交互，微软提供了SqlDataAdapter类和OleDbDataAdapter类。SqlDataAdapter类和OleDbDataAdapter类各自适用情况如下。

q SqlDataAdapter：该类专用于SQL数据库，在SQL数据库中使用该类能够提高性能，SqlDataAdapter与OleDbDataAdapter相比，无需适用OLEDB提供程序层，可直接在SQL Server上使用。

q OleDbDataAdapter：该类适用于由OLEDB数据提供程序公开的任何数据源，包括SQL数据库和Access数据库。

若要使一个使用DataAdapter对象的DataSet要能够和一个数据源之间交换数据，则可以使用DataAdapter属性来指定需要执行的操作，这个属性可以是一条SQL语句或者是存储过程，示例代码如下所示。

            string str = "server='(local)';database='mytable';uid='sa';pwd='sa'"; //创建连接字串

            SqlConnection con = new SqlConnection(str);

            con.Open(); //打开连接

            SqlDataAdapter da = new SqlDataAdapter("select * from news", con); //DataAdapter对象

            con.Close(); //关闭连接

上述代码创建了一个DataAdapter对象，DataSet对象可以使用该对象的Fill方法来填充数据集。

7.8  Command执行对象

Command对象可以使用数据命令直接与数据源进行通信。例如，当需要执行一条插入语句，或者删除数据库中的某条数据的时候，就需要使用到Command对象。Command对象的属性包括了数据库在执行某个语句的所有必要的信息，这些信息如下所示：

q Name：Command的程序化名称。

q Connection：对Connection对象的引用。

q CommandType：指定是使用SQL语句或存储过程，默认情况下是SQL语句。

q CommandTest：命令对象包含的SQL语句或存储过程名。

q Parameters：命令对象的参数。

通常情况下，Command对象用于数据的操作，例如执行数据的插入和删除，也可以执行数据库结构的更改，包括表和数据库。示例代码如下所示。

            string str = "server='(local)';database='mytable';uid='sa';pwd='sa'"; //创建数据库连接字串

            SqlConnection con = new SqlConnection(str);

            con.Open(); //打开数据库连接

            SqlCommand cmd = new SqlCommand("insert into news values ('title')",con);//建立Command对象

上述代码使用了可用的构造函数并指定了查询字符串和Connection对象来初始化Command对象cmd。通过指定Command对象的方法可以对数据执行具体的操作。

7.8.1  ExecuteNonQuery方法

当指定了一个SQL语句，就可以通过ExecuteNonQuery方法来执行语句的操作。ExecuteNonQuery不仅可以执行SQL语句，开发人员也可以执行存储过程或数据定义语言语句来对数据库或目录执行构架操作。而使用ExecuteNonQuery时，ExecuteNonQuery并不返回行，但是可以通过Command对象和Parameters进行参数传递。示例代码如下所示。

            string str = "server='(local)';database='mytable';uid='sa';pwd='sa'"; //创建数据库连接字串

            SqlConnection con = new SqlConnection(str);

            con.Open();

            SqlCommand cmd = new SqlCommand("insert into news values ('title')",con);

            cmd.ExecuteNonQuery(); //执行SQL语句

运行上述代码后，会执行“insert into news values (‘title’)”这条SQL语句并向数据库中插入数据。值得注意的是，修改数据库的SQL语句，例如常用的INSERT、UPDATE以及DELETE并不返回行。同样，很多存储过程同样不返回任何行。当执行这些不返回任何行的语句或存储过程时，可以使用ExecuteNonQuery。但是ExecuteNonQuery语句也会返回一个整数，表示受已执行的SQL语句或存储过程影响的行数，示例代码如下所示。

            string str = "server='(local)';database='mytable';uid='sa';pwd='sa'";

            SqlConnection con = new SqlConnection(str); //创建连接对象

            con.Open(); //打开连接

            SqlCommand cmd = new SqlCommand("delete from mynews", con); //构造Command对象

            Response.Write("该操作影响了("+cmd.ExecuteNonQuery().ToString()+"）行");//执行SQL语句

上述代码执行了语句“delete from mynews”并将影响的行数输出到字符串中。开发人员能够使用ExecuteNonQuery语句进行数据库操作和数据库操作所影响行数的统计。

7.8.2  ExecuteNonQuery执行存储过程

ExecuteNonQuery不仅能够执行SQL语句，同样可以执行存储过程和数据定义语言来对数据库或目录执行构架操作如CREATE TABLE等。在执行存储过程之前，必须先创建一个存储过程，然后在SqlCommand方法中使用存储过程。在SQL Server管理器中可以新建查询创建存储过程，示例代码如下所示。

    CREATE PROC getdetail

     (

    @id int,

    @title varchar(50) OUTPUT

    )

    AS

    SET NOCOUNT ON

    DECLARE @newscount int

    SELECT @title=mynews.title,@newscount=COUNT(mynews.id)

    FROM mynews

    WHERE (id=@id)

    GROUP BY mynews.title

    RETURN @newscount

上述存储过程返回了数据库中新闻的标题内容。“@id”表示新闻的id，“@title”表示新闻的标题，此存储过程将返回“@title”的值，并且返回新闻的总数。上述代码可以直接在SQL管理器中菜单栏中单击【新建查询】后创建的TAB中使用，同样也可以使用SqlCommand对象进行存储过程的创建，示例代码如下所示。

    string str = "CREATE PROC getdetail" +

    "(" +

    "@id int," +

    "@title varchar(50) OUTPUT" +

    ")" +

    "AS" +

    "SET NOCOUNT ON" +

    "DECLARE @newscount int" +

    "SELECT @title=mynews.title,@newscount=COUNT(mynews.id)" +

    "FROM mynews" +

    "WHERE (id=@id)" +

    "GROUP BY mynews.title" +

    "RETURN @newscount";

    SqlCommand cmd = new SqlCommand(str, con);

    cmd.ExecuteNonQuery(); //使用cmd的ExecuteNonQuery方法创建存储过程

创建存储过程后，就可以使用SqlParameter调用命令对象Parameters参数的集合的Add方法进行参数传递，并指定相应的参数，示例代码如下所示。

    string str = "server='(local)';database='mytable';uid='sa';pwd='Sa'";

    SqlConnection con = new SqlConnection(str);

    con.Open(); //打开连接

    SqlCommand cmd = new SqlCommand("getdetail", con); //使用存储过程

    cmd.CommandType = CommandType.StoredProcedure; //设置Command对象的类型

    SqlParameter spr; //表示执行一个存储过程

    spr = cmd.Parameters.Add("@id", SqlDbType.Int); //增加参数id

    spr = cmd.Parameters.Add("@title", SqlDbType.NChar,50); //增加参数title

    spr.Direction = ParameterDirection.Output; //该参数是输出参数

    spr = cmd.Parameters.Add("@count", SqlDbType.Int); //增加count参数

    spr.Direction = ParameterDirection.ReturnValue; //该参数是返回值

    cmd.Parameters["@id"].Value = 1; //为参数初始化

    cmd.Parameters["@title"].Value = null; //为参数初始化

    cmd.ExecuteNonQuery(); //执行存储过程

    Label1.Text = cmd.Parameters["@count"].Value.ToString(); //获取返回值

上述代码使用了现有的存储过程，并为存储过程传递了参数，当参数被存储过程接受并运行后，会返回一个存储过程中指定的返回值。当执行完毕后，开发人员可以通过cmd.Parameters来获取其中一个变量的值。

7.8.3  ExecuteScalar方法

Command的Execute方法提供了返回单个值的功能。在很多时候，开发人员需要获取刚刚插入的数据的ID值，或者可能需要返回Count(*)，Sum(Money)等聚合函数的结果，则可以使用ExecuteScalar方法。示例代码如下所示。

            string str = "server='(local)';database='mytable';uid='sa';pwd='sa'"; //设置连接字串

            SqlConnection con = new SqlConnection(str); //创建连接

            con.Open(); //打开连接

            SqlCommand cmd = new SqlCommand("select count(*) from mynews", con); //创建Command

            Label1.Text = cmd.ExecuteScalar().ToString(); //使用ExecuteScalar执行

上述代码创建了一个连接，并创建了一个Command对象，使用了可用的构造函数来初始化对象。当使用ExecuteScalar执行方法时，会返回单个值。ExecuteScalar方法同样可以执行SQL语句，但是与ExecuteNonQuery方法不同的是，当语句不为SELECT时，则返回一个没有任何数据的System.Data.SqlClient.SqlDataReader类型的集合。

ExecuteScalar方法执行SQL语句通常是用来执行具有返回值的功能的SQL语句，例如上面所说的当插入一条新数据时，返回刚刚插入的数值的ID号。这种功能在自动增长类型的数据库设计中，经常被使用到，示例代码如下所示。

            string str = "server='(local)';database='mytable';uid='sa';pwd='sa'"; //设置连接字串

            SqlConnection con = new SqlConnection(str); //创建连接

            con.Open(); //打开连接

            SqlCommand cmd = new SqlCommand("insert into mynews values ('this is a new title!') 

            SELECT  @@IDENTITY  as  'bh'", con); //打开连接

            Label2.Text = cmd.ExecuteScalar().ToString(); //获取返回的ID值

上述代码使用了“SELECT  @@IDENTITY  as”语法，“SELECT  @@IDENTITY”语法会自动获取刚刚插入的自动增长类型的值，例如，当表中有100条数据时，插入一条数据后数据量就成101，为了不需要再次查询就获得101这个值，则可以使用“SELECT  @@IDENTITY  as”语法。运行结果如图7-23所示。

图7-23  ExecuteScalar方法示例

当使用了“SELECT  @@IDENTITY  as”语法进行数据操作时，ExecuteScalar方法会返回刚刚插入的数据的ID，这样就无需再次查询进行刚刚插入的数据的信息的获取。

7.9  DataSet数据集对象

DataSet是ADO.NET中的核心概念，作为初学者，可以把DataSet想象成虚拟的表，但是这个表不能用简单的表来表示，这个表可以想象成具有数据库结构的表，并且这个表是存放在内存中的。由于ADO.NET中DataSet的存在，开发人员能够屏蔽数据库与数据库之间的差异，从而获得一致的编程模型。

7.9.1  DataSet数据集基本对象

DataSet能够支持多表、表间关系、数据库约束等，可以模拟一个简单的数据库模型。DataSet对象模型如图7-24所示。

图7-24  DataSet对象模型

上图简要的介绍了常用对象之间的构架关系。在DataSet中，主要包括TablesCollection、RelationsCollection、ExtendedProperties几个重要对象：

1．TablesCollection对象

在DataSet中，表的概念是用DataTable来表示的。DataTable在System.Data中定义，它能够表示存储在内从中的一张表。它包含一个ColumnsCollection的对象，代表数据表的各个列的定义。同时，它也包含RowsCollection对象，这个对象包含DataTable中的所有数据。

2．RelationsCollection对象

在各个DataTable对象之间，是通过使用RelationsCollection来表达各个DataTable对象之间的关系。RelationsCollection对象可以模拟数据库中的约束的关系。例如当一个包含外键的表被更新时，如果不满足主键-外键约束，这个更新操作就会失败，系统会抛出异常。

3．ExtendedProperties对象

ExtendedProperties对象能够配置特定的信息，例如DataTable的密码，更新时间等等。

7.9.2  DataTable数据表对象

DataTable是DataSet中的常用的对象，它和数据库中的表的概念十分相似。开发人员能够将DataTable想象成一个表。并且可以通过编程的方式创建一个DataTable表。示例代码如下所示。

            DataTable Table = new DataTable("mytable"); //创建一个DataTable对象

            Table.CaseSensitive = false; //设置不区分大小写

            Table.MinimumCapacity = 100; //设置DataTable初始大小

            Table.TableName = "newtable"; //设置DataTable的名称

上述代码创建了一个DataTable对象，并为DataTable对象设置了若干属性，这些属性都是常用的属性，其作用分别如下所示。

q CaseSensitive：此属性设置表中的字符串是否区分大小写，若无特殊情况，一般设置为false，该属性对于查找，排序，过滤等操作有很大的影响。

q MinimumCapacity：设置创建的数据表的最小的记录空间。

q TableName：指定数据表的名称。

一个表必须有一个列，而DataTable必须包含列。当创建了一个DataTable后，就必须向DataTable中增加列的。表中列的集合形成了二维表的数据结构。开发人员可以使用Columns集合的Add方法向DataTable中增加列，Add方法带有两个参数，一个是表列的名称，一个是该列的数据类型。示例代码如下所示。

            DataTable Table = new DataTable("mytable"); //创建一个DataTable

            Table.CaseSensitive = false; //设置不区分大小写

            Table.MinimumCapacity = 100; //设置DataTable初始大小

            Table.TableName = "newtable"; //设置DataTable的名称

            DataColumn Colum = new DataColumn(); //创建一个DataColumn

            Colum = Table.Columns.Add("id", typeof(int)); //增加一个列

            Colum = Table.Columns.Add("title", typeof(string)); //增加一个列

上述代码创建了一个DataTable和一个DataColumn对象，并通过DataTable的Columns.Add方法增加DataTable的列，这两列的列名和数据类型如下：

q 新闻ID：整型，用于描述新闻的编号。

q 新闻标题TITLE：字符型，用于描述新闻发布的标题。

注意：上述代码中，DataTable的列的数据类型使用的只能是.net中数据类型，因为其并不是真实的数据库，所以不能直接使用数据库类型，必须使用typeof方法把.net中的数据类型转换成数据库类型。

7.9.3  DataRow数据行对象

在创建了表和表中列的集合，并使用约束定义表的结构后，可以使用DataRow对象向表中添加新的数据库行，这一操作同数据库中的INSERT语句的概念类似。插入一个新行，首先要声明一个DataRow类型的变量。使用NewRow方法能够返回一个新的DataRow对象。DataTable会根据DataColumnCollection定义的表的结构来创建DataRow对象。示例代码如下所示。

           DataRow Row = Table.NewRow(); //使用DataTable的NewRow方法创建一个新DataRow对象

上述代码使用DataTable的NewRow方法创建一个新DataRow对象，当使用该对象添加了新行之后，必须使用索引或者列名来操作新行，示例代码如下所示。

            Row[0] = 1; //使用索引赋值列

            Row[1] = "datarow"; //使用索引赋值列

上述代码通过索引来为一行中个各列赋值。从数组的语法可以知道，索引都是从第0个位置开始。将DataTable想象成一个表，从左到右从0开始索引，直到数值等于列数减1为止。为了提高代码的可读性，也可以通过直接使用列名来添加新行，示例代码如下所示。

            Row["bh"] = 1; //使用列名赋值列

            Row["title"] = "datarow"; //使用列名赋值列

通过直接使用列名来添加新行与使用索引添加新行的效果相同，但是通过使用列名能够让代码更加可读，便于理解，但是也暴露了一些机密内容（如列值）。在数据插入到新行后，使用Add方法将该行添加到DataRowCollection中，示例代码如下所示。

            Table.Rows.Add(Row); //增加列

7.9.4  DataView数据视图对象

当需要显示DataRow对象中的数据时，可以使用DataView对象来显示DataSet中的数据。在显示DataSet中的数据之前，需要将DataTable中的数据填充到DataSet。值得注意的是，DataSet是DataTable的集合，可以使用DataSet的Add方法将多个DataTable填充到DataSet中去，示例代码如下所示。

            DataSet ds = new DataSet(); //创建数据集

            ds.Tables.Add(Table); //增加表

填充完成后，可以通过DataView对象来显示DataSet数据集中的内容，示例代码如下所示。

            dv = ds.Tables["newtable"].DefaultView; //设置默认视图

DataSet对象中的每个DataTable对象都有一个DefaultView属性，该属性返回表的默认试图。上述代码访问了名为newtable表的DataTable对象。开发人员能够自定义DataView对象，该对象能够筛选表达式来设置DataView对象的RowFilter属性，筛选表达式的值必须为布尔值。同时，该对象能够设置Sort属性进行排序，排序表达式可以包括列名或一个算式，示例代码如下所示。

            DataView dv = new DataView(); //创建数据视图对象

            DataSet ds = new DataSet(); //创建数据集

            ds.Tables.Add(Table); //增加数据表

            dv = ds.Tables["newtable"].DefaultView; //设置默认视图

            dv.RowFilter = "id" = "1"; //设置筛选表达式

            dv.Sort = "id"; //设置排序表达式

技巧：要显示DataSet中某项的值，可以使用语法ds.Tables[“表名称”].Rows[0][“列名称”].ToString()来显示，这种语法通常需要知道行的数目以免在访问数据时越界。

7.10  DataReader数据访问对象

DataSet的最大好处在于，能够提供无连接的数据库副本，DataSet对象在表的生命周期内会为这些表进行内存的分配和维护。如果有多个用户同时对一台计算机进行操作，内存的使用就会变得非常的紧张。当对数据所需要进行一些简单的操作时，就无需保持DataSet对象的生命周期，可以使用DataReader对象。

7.10.1  DataReader对象概述

当使用DataReader对象时，不会像DataSet那样提供无连接的数据库副本。DataReader类被设计为产生只读，只进的数据流。这些数据流都是从数据库返回的。所以，每次的访问或操作只有一个记录保存在服务器的内存中。相比与DataSet而言，DataReader具有较快的访问能力，并且能够使用较少的服务器资源，DataReader具有以下快速的数据库访问、只进和只读、减少服务器资源等特色。

1．快速的数据库访问

DataReader类是轻量级的，相比之下DataReader对象的速度要比DataSet要快。因为DataSet在创建和初始化时，可能是一个或多个表的集合，并且DataSet具有向前，向后读写和浏览的能力，所以当创建一个DataSet对象时，会造成额外的开销。

2．只进和只读

当对数据库的操作没有太大的要求时，可以使用DataReader显示数据。这些数据可以与单个list-bound控件绑定，也可以填充List接口。当不需要复杂的数据库处理时，DataReader能够较快的完成数据显示。

3．减少服务器资源

因为DataReader并不是数据的内存的表示形式，所以使用DataReader对服务器占用的资源很少。

4．自定义数据库管理

DataReader对象可以使用Read方法来进行数据库遍历，当使用Read方法时，可以以编程的方式自定义数据库中数据的显示方式，当开发自定义控件时，可以将这些数据整合到HTML中，并显示数据。

5．手动连接管理

DataAdapter对象能够自动的打开和关闭连接，而DataReader对象需要用户手动的管理连接。DataReader对象和DataAdapter对象很相似，都可以从SQL语句和一个连接中初始化。

7.10.2  DataReader读取数据库

创建DataReader对象，需要创建一个SqlCommand对象来代替SqlDataAdapter对象。与SqlDataAdapter对象类似的是，DataReader可以从SQL语句和连接中创建Command对象。创建对象后，必须显式的打开Connection对象。示例代码如下所示。

            string str = "server='(local)';database='mytable';uid='sa';pwd='sa'";

            SqlConnection con = new SqlConnection(str);

            con.Open(); //打开连接

            SqlCommand cmd = new SqlCommand("select * from mynews", con); //创建Command对象

            SqlDataReader dr = cmd.ExecuteReader(); //创建DataReader对象

            con.Close();

上述代码创建了一个DataReader对象，从上述代码中可以看出，创建DataReader对象必须经过如下几个步骤：

q 创建和打开数据库连接。

q 创建一个Command对象。

q 从Command对象中创建DataReader对象。

q 调用ExecuteReader对象。

DataReader对象的Read方法可以判断DataReader对象中的数据是否还有下一行，并将游标下移到下一行。通过Read方法可以判断DataReader对象中的数据是否读完。示例代码如下所示。

            while (dr.Read())

通过Read方法可以遍历读取数据库中行的信息，当读取到一行时，需要获取某列的值只需要使用“[”和“]”运算符来确定某一列的值即可，示例代码如下所示。

            while (dr.Read())

            {

                Response.Write(dr["title"].ToString()+"<hr/>");

            }

上述代码通过dr[“title”]来获取数据库中title这一列的值，同样也可以通过索引来获取某一列的值，示例代码如下所示。

            while (dr.Read())

            {

                Response.Write(dr[1].ToString()+"<hr/>");

            }

7.10.3  异常处理

在使用DataReader对象进行连接时，需要使用Try…Catch…Finally语句进行异常处理，以保证如果代码出现异常时连接能够关闭，否则连接将保持打开状态，影响应用程序性能。示例代码如下所示。

        protected void Page_Load(object sender, EventArgs e)

        {

            string str = "server='(local)';database='mytable';uid='sa';pwd='Bbg0123456#'";

            SqlConnection con = new SqlConnection(str);

            con.Open();

            SqlCommand cmd = new SqlCommand("select * from mynews", con);

            SqlDataReader dr;

            try

            {

                dr = cmd.ExecuteReader();

                while (dr.Read())

                {

                    Response.Write(dr[1].ToString() + "<hr/>");

                }

            }

            catch (Exception ee) //出现异常

            {

                Response.Write(ee.ToString()); //出现异常则抛出错误语句

            }

            finally

            {

                dr.Close(); //强制关闭连接

                con.Close(); //强制关闭连接

            }

        }

上述代码当出现异常时，则会抛出异常，并强制关闭连接。这样做就能够在程序发生异常时，依旧关闭连接应用程序与数据库的连接，否则大量的异常连接状态的出现会影响应用程序性能。

7.11  连接池概述

在应用程序与数据库交互中，建立和关闭数据库连接都是非常消耗资源的过程。如果一个应用程序需要大量的与数据库进行交互，则很有可能造成假死，以及崩溃的情况。使用连接池能够提高应用程序的性能。

连接池是SQL Server或OLEDB数据源的功能，它可以使特定的用户重复使用连接，数据库连接池技术的思想非常简单，将数据库连接作为对象存储在一个Vector对象中，一旦数据库连接建立后，不同的数据库访问请求就可以共享这些连接，这样，通过复用这些已经建立的数据库连接，可以极大地节省系统资源和时间。连接池的主要操作如下所示：

q 建立数据库连接池对象。

q 对于一个数据库访问请求，直接从连接池中得到一个连接。如果数据库连接池对象中没有空闲的连接，且连接数没有达到最大，创建一个新的数据库连接。

q 存取数据库。

q 关闭数据库，释放所有数据库连接。

q 释放数据库连接池对象。

注意：在关闭数据库这一步中，并非真正的关闭，而是将其放入空闲队列中。如实际空闲连接数大于初始空闲连接数则释放连接。

当一个网站用户需要同数据库之间进行交互时，服务器会为网站用户建立一个业务对象，每个业务对象维护自身的连接，这些业务对象自身会创建连接。当用户无需该业务对象时，业务对象会释放连接，如图7-25所示。

图7-25  多层构架应用程序

当业务对数据库进行复杂的操作，并不停的打开和断开数据库连接，这样的操作会造成应用程序性能降低，因为重复的打开和断开数据库连接是非常消耗资源的，而使用连接池则可以避免这样的问题。连接池并不会真正的完全的关闭数据库与应用程序的连接，而将这些连接存放在应用程序连接池中。当一个新的业务对象产生时，会在连接池中检查是否已有连接，若无连接，则会创建一个新连接，否则会使用现有的匹配的连接，这样就提高了性能，如图7-26所示。

图7-26  使用连接池

使用连接池能够提升应用程序的性能，特别是开发Web应用程序时，Web应用程序通常需要频繁的与数据库之间进行交互，应用程序池能够解决Web引用中假死等情况，也能够节约服务器资源。但是，在创建连接时，良好的关闭习惯也是非常必要的。

7.12  参数化查询

在Web应用程序的开发过程中，Web安全是非常重要的，现存的很多网站也都存在一些非常严重的安全漏洞，其中SQL注入是非常常见的漏洞，如果将查询语句进行参数化查询，可以减少SQL注入漏洞的概率，参数化查询示例代码如下所示。

            string strsql = "select * from mynews where id= @id";

上述代码使用了参数化查询，在存储过程中，参数化是非常常见的，存储过程通过Command对象进行参数的添加和赋值。同样，参数化查询也可以通过Command对象进行添加和赋值，参数化查询过程如下所示。

q 创建一个Command对象。

q Command对象增加一个参数。

q 通过索引对Command参数进行赋值。

q 执行ExecuteReader方法返回个DataReader对象。

通过Command对象可以为存储过程，以及参数化查询语句进行参数的添加，示例代码如下所示。

        protected void Page_Load(object sender, EventArgs e)

        {

            string str = "server='(local)';database='mytable';uid='sa';pwd='sa'";

            SqlConnection con = new SqlConnection(str);

            con.Open();

            string strsql = "select * from mynews where id = @bh";

            SqlCommand cmd = new SqlCommand(strsql, con); //创建Command对象

            cmd.Parameters.Add("@bh", SqlDbType.Int); //增加参数@bh

            cmd.Parameters[0].Value = 4; //通过索引为参数赋值

            SqlDataReader dr = cmd.ExecuteReader(); //执行后返回DataReader对象

            while (dr.Read()) //遍历DataReader对象

            {

                Response.Write(dr["title"].ToString()+"<hr>");

            }

        }

参数化查询能够有效的解决一些安全问题，提高Web应用的安全性。同时，参数化查询能够极大的简化程序设计。只需要通过数值的更改而不需要修改SQL语句，极大的方便了应用程序的维护。

注意：如果未初始化Parameter数据类型的属性，但设置了Value属性，那么Parameter会自动选择合适的数据类型。

7.13  小结

本章接单的介绍了数据的基础知识，包括什么是数据库，数据库的作用。然后讲述了SQL Server 2005的数据库基本使用，并介绍了SQL Server Management管理工具的使用。通过介绍SQL Server Management管理工具，介绍了如何使用SQL Server Management管理工具和SQL语句创建表，删除表等过程。本章还包括：

q ADO.NET连接SQL数据库：使用ADO.NET连接SQL数据库示例。

q ADO.NET与ADO：ADO与ADO.NET发展史和利弊。

q Connection对象：Connection对象概述。

q 连接SQL数据库：使用Connection对象连接SQL数据库。

q 连接Access数据库：使用Connection对象连接Access数据库。

q DataAdapter对象：讲解了DataAdapter对象的使用。

q Command对象：讲解了Command对象的使用。

q DataSet对象：讲解了DataSet对象中常用的方法，并高效使用DataSet开发。

q DataReader对象：讲解了DataReader对象。

q 连接池概述：讲解了连接池。

q 参数化查询：讲解了使用参数化查询提供安全性保证和简化开发。