ADO.NET的结构，提供程序和数据连接，执行数据库命令Command对象

 

ADO.NET的核心组件由Data Provider模块和DataSet模块组成。Data Provider程序库实现数据的连接、操作和对数据快速只进只读访问。DataSet实现独立于数据源的数据访问、操作，有点类似于ADO的断开连接的静态数据集。

ADO.NET的结构图如下：

一、.NET Framework Data Provider
.NET Framework数据提供程序是一组连接数据源，并且能够对数据执行命令，获取数据的程序结构。

（一）.NET Framework的四个数据提供程序
.NET Framework提供了四组数据提供程序，用于访问四类数据源。
1、SQL Server .NET Framework数据提供程序，该程序只能访问MS SQL Server7.0或更高版本，更早版本的只能通过OLE DB数据提供程序访问。它的命名空间为System.Data.SqlClient。
2、OLE DB .NET Framework 数据提供程序，用于访问OLE DB数据提供程序，该程序不支持OLE DB 2.5版接口。它的命名空间为System.Data.OleDb。
3、ODBC .NET Framework 数据提供程序。用于访问ODBC数据提供程序。它的命名空间为System.Data.Odbc。
4、Oracle .NET Framework 数据提供程序。用于访问Oracle数据，该程序需要Oracle客户端软件8.1.7或更高版本的支持。它的命名空间为System.Data.OracleClient。

通过对IDbConnection接口、IDbCommand接口、IDataAdapter接口、IDbDataAdapter接口、IDataReader接口、IDataParameter接口、IDbTransaction接口等的实现我们也可以编写自己的数据提供程序组。

（二）.Net数据提供程序的四个核心对象
.Net数据提供程序通常包含有四个核心对象：
1、Connection 对象提供与数据源的连接。数据提供程序的Connection类是继承System.Data.IDbConnection接口的实现。
2、Command对象使您能够访问用于返回数据、修改数据、运行存储过程以及发送或检索参数信息的数据库命令。数据提供程序的Command类是继承System.Data.IDbCommand接口的实现。
3、DataReader 从数据源中提供高性能的数据流。DataReader的数据流是只进且只读的。数据提供程序的DataReader类是继承System.Data.IDataReader接口的实现。
4、DataAdapter 提供连接DataSet对象和数据源的桥梁。DataAdapter 使用Command 对象在数据源中执行 SQL 命令，以便将数据加载到 DataSet 中，并使对 DataSet中数据的更改与数据源保持一致。数据提供程序的DataAdapter类是继承System.Data.IDbDataAdapter接口的实现。
.Net数据提供程序还包括Transaction对象、Parameter对象等。

二、.NET Framework DataSet
连接、命令、事务、数据读取作用于特定的提供程序，唯有数据集可以独立于特定的数据提供的，在.NET Framework中数据集对象DataSet的命名空间位于System.Data中。

（一）、DataSet对象模型
DataSet对象是支持ADO.NET的断开式、分布式数据方案的核心对象。DataSet是数据的内存驻留表示形式，无论数据源是什么，它都会提供一致的关系编程模型。你可以把它想象成一个浓缩在内存当中的关系数据库。它的对象模型如下图：

 

 

DataSet包含三组集合：
(1)、DataTableCollection。关系数据库当中最主要的对象就是Table，那么在DataSet当中使用该集合来包含多个DataTable对象。DataTable就是是在内存中的数据表，你可以通过唯一名称来标识一个表。DataSet的DataTable可存储的最大行数是16,777,216。
(2)、DataRelationCollection。在关系数据库当中除了表之外，还有表示表和表之间的关系Relation。该集合就是包含表关系对象的集合。
(3)、ExtendedProperties。该是一组自定义信息，概念上点类似于ASP的Session之类的。

（二）、DataSet与DataAdapter
DataAdapter对象是数据提供程序和DataSet连接的桥梁。数据提供程序的DataAdapter类是IDataAdapter或者IDbDataAdapter的实现。通常DataAdapter的构造函数是：
XxxDataAdapter(SqlCommand selectCommand)
XxxDataAdapter(String selectCommandText, String selectConnectionString)
XxxDataAdapter(String selectCommandText, SqlConnection selectConnection)

类似于ADODB连接模式的数据库操作程序的，是ADO.NET的数据提供程序。.NET Framework 1.1版的ADO.NET提供了四种托管数据提供程序。

 

一、ADO.NET的数据提供程序
ADO.NET配套的四种数据提供程序，分别是用于SQL Server7.0及更高版本的SqlClient数据提供程序，对应的名字空间是System.Data.SqlClient；用于OLE DB数据源的OleDb数据提供程序，对应的名字空间是System.Data.OleDb；用于ODBC数据源的Odbc数据提供程序，对应的名字空间是System.Data.Odbc；用于Oracle数据源的OracleClient数据提供程序，对应的名字空间是System.Data.OracleClient。（注意：.NET Framework1.0版本不包括Odbc和OracleClient数据提供程序，需要从微软官方站点下载安装）

ADO.NET的SQL Server数据提供程序比OLE DB数据提供程序有着更优异的性能。前者专用于SQL Server，并有针对的优化，通过TDS数据包与SQL Server直接对话，后者必须通过一个COM组件集，即数据源的OLE DB提供程序和OLE DB服务组件来与数据源进行交流。因此对于SQL Server的访问，推荐使用SQL Server数据提供程序。下图为两者的数据访问比较图：

 

二、使用Connection对象连接数据库
需要访问数据源的数据，首先要通过Connection对象，连接到指定的数据源，FCL数据提供程序的Connection类是一个通用接口System.Data.IDBConnection的实现。
Connection的构造函数通常都有一个连接字符串作为参数，连接字符串也可以使用Connction.ConnectionString属性设置，如果使用c#表示，它们创建连接语句形式通常如下：

IDBConnection conn = new xxxConnection("connection string");
conn.Open();

Connection的Close方法能够关闭数据连接，但是有些时候Close不能够物理性质的中断到数据库的连接。除了Close方法，还有一个Dispose方法，它会调用Close方法，此外通过设置一个Boolean参数指定非托管资源是否应该释放（包括COM接口指针、ODBC句柄等），该方法调用之后，Connection对象就完全释放而无法重用了。

（一）连接字符串的差异
对于不同的数据提供程序，连接字符串存在着差异。
SqlConnection的数据库连接字符串通常如下：
"Server=mySQLServer;Database=northwind;User ID=sa;Password=mypwd;"
OleDbConnection的数据库连接字符串如下：
"Provider=SQLOLEDB;Data Source=mySqlServer;Initial Catalog=northwind;User ID=sa;Password=mypwd;"
OdbcConnection的数据库连接字符串如下：
"Driver={SQL Server};Server=localhost;Database=northwind"或者"DSN=dsnname"
OracleConnection的数据库连接字符串如下：
"Data Source=Oracle8i;User ID=sa;pwd=mypwd"

（二）应用程序中连接字符串的存储
为了方便管理数据连接，通常数据连接字符串不写为硬码，而存储在应用程序之外。
《.NET 数据访问架构指南》一文用一节讨论了链接字符串的存储。链接字符串的存储可以采用下面5种方式：
1、应用程序配置文件 例如用于ASP.NET Web应用程序的Web.config文件。
2、通用数据链接文件(UDL) （只被OLE DB .NET 数据供应器所支持)
3、Windows 注册表
4、定制文件
5、COM+ 目录，通过过使用构造字符串(只用于服务组件)
这些方式各有优缺点，在ASP.NET中最常见的是使用Web.config存储连接字符串，包含在元素appSettings的一个add节点当中。
<appSettings>
<add key="DBConnStr"
     value="server=(local);Integrated Security=SSPI;database=northwind"/>
</appSettings>
在ASP.NET应用程序当中使用System.Configuration.ConfigurationSettings类的AppSettings静态属性，可以获取应用程序的定制设置。

（三）连接池(Connection pooling)
连接池能让数据库中使用同一个帐号的不同会话共享连接，避免频繁的打开和关闭连接，它能够显著的提高应用程序的性能。
使用SQL Server数据提供程序的连接池需要注意，每当应用打开一个连接，.NET Framework将创建池，每个连接池都和一个不同的连接字符串相关联。也就是说，如果新创建的一个连接使用的连接字符串和现有的池中连接相匹配，将创建新的池。注意：池化机制对名称-值对间的空格敏感。
下面的三个连接创建了三个不同的池：
SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=pubs");
conn.Open(); //创建池A

SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Northwind");
conn.Open(); //创建池B，和前面的连接字符串不一样

SqlConnection conn = new SqlConnection("Integrated Security=SSPI ;Initial Catalog=Northwind");
conn.Open(); //创建池C，SSPI后多了一个空格

数据提供程序的Command类是IDBCommand接口的实现，通过Command来执行数据库命令，数据库数据的查询、更新、插入都通过Command来实现。
Command的构造函数通常都有下面三种形式：
1, public xxxCommand();
2, public xxxCommand(string);
3, public xxxCommand(string,xxxConnection);
一般我们创建Command对象都通过类似于下面的语句来实现：
xxxConnection conn = new xxxConnection("myString");
xxxCommand myCmd = new xxxCommand("select * from orders",conn);
构造函数的两个参数是SQL语句和Connection对象，创建了Command对象。

下面说明Command对象的执行，程序范例将采用SqlClient数据提供程序来访问一个Sql Server的数据库Northwind。

一、设置连接和SQL命令
Command类的属性CommandText用来设置命令语句，Connection属性用来设置连接对象。设置命令对象的数据连接和命令语句除了可以在通过创建对象时通过构造函数定义外，这两个属性设置和更改。
SqlConnection conn = new SqlConnection("Server=localhost;Database=Northwind;User ID=sa;PWD=sa");
conn.Open();
SqlCommand cmd = new SqlCommand("select * from [Orders]",conn);
cmd.CommandText = "delete [Orders] where [OrderID]=10248";

二、执行命令
建立了数据源的连接和设置了命令之后，Command对象执行SQL命令有三种方法：ExecuteNonQuery、ExecuteReader和ExecuteScalar。
使用ExecuteNonQuery执行命令不会返回结果集，只会返回语句影响的记录行数，它适合执行插入、更新、删除之类不返回结果集的命令。如果是SELECT语句，那么返回的结果是-1，如果发生回滚这个结果也是-1。下面的程序范例对Orders表执行了更新并做了查询。

using System;
using System.Data;
using System.Data.SqlClient;
public class myDataAccess{
public static void Main(){
SqlConnection conn = new SqlConnection("Server=localhost;Database=Northwind;User ID=sa;PWD=sa");
SqlCommand cmd = new SqlCommand("update [Orders] set [OrderDate]='2004-9-1' where [OrderID]=10248",conn);
try{
   conn.Open();
   int i = cmd.ExecuteNonQuery();
   Console.WriteLine(i.ToString() + " rows affected by UPDATE");
   cmd.CommandText = "select * from [Orders]";
   i = cmd.ExecuteNonQuery();
   Console.WriteLine(i.ToString() + " rows affected by SELECT");
}
catch(Exception ex){
   Console.WriteLine(ex.Message);
}
finally{
   conn.Close();
}
}
}

使用ExecuteReader方法执行的命令，可以返回一个类型化的DataReader实例或者IDataReader接口的结果集。通过DataReader对象就能够获得数据的行集合，本文不准备详细讨论DataReader，关于DataReader的使用将来再说明。下面是一个例子。
using System;
using System.Data;
using System.Data.SqlClient;
public class myDataAccess{
public static void Main(){
SqlConnection conn = new SqlConnection("Server=localhost;Database=Northwind;User ID=sa;PWD=sa");
SqlCommand cmd = new SqlCommand("select top 20 * from [Orders]",conn);
SqlDataReader reader; //或者IDataReader reader;
try{
   conn.Open();
   reader = cmd.ExecuteReader();
   while(reader.Read()){
    Console.WriteLine(reader[0].ToString());
   }
   reader.Close();
}
catch(Exception ex){
   Console.WriteLine(ex.Message);
}
finally{
   conn.Close();
}
}
}



对于ExecuteReader方法，如果想获得数据的记录行数，可以通过select count(*)这样的语句取得一个聚合的行集合。对于这样求单个值的语句，Command对象还有更有效率的方法——ExecuteScalar。它能够返回对应于第一行第一列的对象（System.Object），通常使用它来求聚合查询结果。需要注意的是，如果需要把返回结果转化成精确的类型，数据库在查询中就必须强制将返回的结果转换，否则引发异常。下面是例子：

using System;
using System.Data;
using System.Data.SqlClient;
public class myDataAccess{
public static void Main(){
SqlConnection conn = new SqlConnection("Server=localhost;Database=Northwind;User ID=sa;PWD=sa");
SqlCommand cmd = new SqlCommand("select count(*) from [Orders]",conn);
try{
   conn.Open();
   int i = (int)cmd.ExecuteScalar();
   Console.WriteLine("record num : " + i.ToString());
   cmd.CommandText = "select cast(avg([Freight]) as int) from [Orders]";
   int avg = (int)cmd.ExecuteScalar();
   Console.WriteLine("avg : " + avg.ToString());
   cmd.CommandText = "select avg([Freight]) from [Orders]";
   avg = (int)cmd.ExecuteScalar(); //引发异常
   Console.WriteLine("avg : " + avg.ToString());
}
catch(Exception ex){
   Console.WriteLine(ex.Message);
}
finally{
   conn.Close();
}
}
}


这个程序中，最后一个查询将引发异常，因为聚合返回的结果是float类型的，无法转换。

三、参数化查询
参数化的查询能够对性能有一定的优化，因为带参数的SQL语句只需要被SQL执行引擎分析过一次。Command的Parameters能够为参数化查询设置参数值。Parameters是一个实现IDataParamterCollection接口的参数集合。
不同的数据提供程序的Command对参数传递的使用不太一样，其中SqlClient和OracleClient只支持SQL语句中命名参数而不支持问号占位符，必须使用命名参数，而OleDb和Odbc数据提供程序只支持问号占位符，不支持命名参数。
对于查询语句SqlClient必须使用命名参数，类似于下面的写法：
SELECT * FROM Customers WHERE CustomerID = @CustomerID --Oracle的命名参数前面不用@，使用(:)，写为(:CustomerID)
而对于OleDb或者Odbc必须使用?占位符，类似于下面的写法：
SELECT * FROM Customers WHERE CustomerID = ?

下面以Sql Server为范例，说明其使用方法：
using System;
using System.Data;
using System.Data.SqlClient;
public class myDataAccess{
public static void Main(String[] args){
SqlConnection conn = new SqlConnection("Server=localhost;Database=Northwind;User ID=sa;PWD=sa");
SqlCommand cmd = new SqlCommand("select * from [Orders] where [OrderID]=@oid",conn);
SqlDataReader reader;
try{
   int param = Convert.ToInt32(args[0]);
   cmd.Parameters.Add("@oid",param); //使用命名参数
   cmd.Parameters[0].Direction = ParameterDirection.Input;
   conn.Open();
   reader = cmd.ExecuteReader();
   while(reader.Read()){
    Console.WriteLine(reader[0].ToString());
   }
   reader.Close();
}
catch(Exception ex){
   Console.WriteLine(ex.Message);
}
finally{
   conn.Close();
}
}
}



对于OleDb或者Odbc数据提供程序的命令参数，只需要把参数按照占位符从左到右的顺序，匹配给Parameters集合就行了。 下面是程序范例：

using System;
using System.Data;
using System.Data.OleDb;
public class myDataAccess{
public static void Main(String[] args){
OleDbConnection conn = new OleDbConnection("Provider=SQLOLEDB;Server=localhost;Database=Northwind;User ID=sa;PWD=sa");
OleDbCommand cmd = new OleDbCommand("select * from [Orders] where [OrderID]=? or [EmployeeID]=?",conn);
OleDbDataReader reader;
try{
   int param1 = Convert.ToInt32(args[0]);
   int param2 = Convert.ToInt32(args[1]);
   cmd.Parameters.Add("aaa",param1);
   cmd.Parameters.Add("bbb",param2); //参数对象还需要名字，但是和查询语句中的参数名无关
   cmd.Parameters[0].Direction = ParameterDirection.Input;
   cmd.Parameters[1].Direction = ParameterDirection.Input;
   conn.Open();
   reader = cmd.ExecuteReader();
   while(reader.Read()){
    Console.WriteLine(reader[0].ToString());
   }
   reader.Close();
}
catch(Exception ex){
   Console.WriteLine(ex.Message);
}
finally{
   conn.Close();
}
}
}

四、执行存储过程
使用Command对象访问数据库的存储过程，需要指定CommandType属性，这是一个CommandType枚举类型，默认情况下CommandType表示CommandText命令为SQL批处理，CommandType.StoredProcedure值指定执行的命令是存储过程。类似于参数化查询，存储过程的参数也可以使用Parameters集合来设置，其中Parameter对象的Direction属性用于指示参数是只可输入、只可输出、双向还是存储过程返回值参数。
需要注意的是如果使用ExecuteReader返回存储过程的结果集，那么除非DataReader关闭，否则无法使用输出参数。下面是一个例子：
存储过程
---------------------------------------------
CREATE procedure myProTest (
@orderID as int,
@elyTitle as varchar(50) output
)
as
select @elyTitle=ely.Title from [Orders] o join [Employees] ely on ely.EmployeeID=o.EmployeeID where o.OrderID=@orderID
select * from [Orders] where OrderID=@orderID
return 1
---------------------------------------------

程序
---------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
public class myDataAccess{
public static void Main(){
SqlConnection conn = new SqlConnection("Server=localhost;Database=Northwind;User ID=sa;PWD=sa");
SqlCommand cmd = new SqlCommand("myProTest",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@orderID",10252);
cmd.Parameters.Add("@elyTitle",SqlDbType.VarChar,50);
cmd.Parameters.Add("@return",SqlDbType.Int);
cmd.Parameters[0].Direction = ParameterDirection.Input;
cmd.Parameters[1].Direction = ParameterDirection.Output;
cmd.Parameters[2].Direction = ParameterDirection.ReturnValue;
SqlDataReader reader;
try{
   conn.Open();
   Console.WriteLine("execute reader...");
   reader = cmd.ExecuteReader();
   Console.WriteLine("@orderID = {0}",cmd.Parameters[0].Value);
   Console.WriteLine("@elyTitle = {0}",cmd.Parameters[1].Value);
   Console.WriteLine("Return = {0}",cmd.Parameters[2].Value);
   Console.WriteLine("reader close...");
   reader.Close();
   Console.WriteLine("@orderID = {0}",cmd.Parameters[0].Value);
   Console.WriteLine("@elyTitle = {0}",cmd.Parameters[1].Value);
   Console.WriteLine("Return = {0}",cmd.Parameters[2].Value);
   Console.WriteLine("execute none query...");
   cmd.ExecuteNonQuery();
   Console.WriteLine("@orderID = {0}",cmd.Parameters[0].Value);
   Console.WriteLine("@elyTitle = {0}",cmd.Parameters[1].Value);
   Console.WriteLine("Return = {0}",cmd.Parameters[2].Value);
}
catch(Exception ex){
   Console.WriteLine(ex.Message);
}
finally{
   conn.Close();
}
}
}


和参数化查询一样，OleDb或者Odbc数据提供程序不支持存储过程的命名参数，需要把参数按照从左到右的顺序，匹配Parameters集合。