ADO.Net编程_第2章连接类

第2章          ADO.NET连接类概述

2.1    使用“提供者”类移动数据

微软.Net框架包含以下几个提供者：OLEDB、ODBC、SQL SERVER、Oracle这四大类。

ADO.NET中主要的提供者的基类有：

DbConnection/DbCommand/DbDataReader/DbTransaction/DbParameter/DbParameterCollection/DbDataAdapter/DbCommandBuilder/DbConnectionStringBuilder/DbDataPermission。

2.2     DbConnection对象

该抽象类的几个继承类：SqlConnection、OdbcConnection、OleDbConnection、OracleConnection。

2.2.1      ODBC字符串

       Odbc连接字符串的关键字包括：Driver、DSN、Server、Trusted_Connection、Database、DBQ。

示例：略

2.2.2   配置一个OLEDB字符串

2.2.3   配置一个Oracle字符串

2.2.4   配置一个SQLServer连接字符串

示例1：

Persist Security Info = False;

Integrated Security = SSPI;

Database = northwind;

Server = Localhost;

Connect TimeOut = 30;

示例2：使用套接字

Network Library = DBMSSOCN;

Data Source = 10.1.2.3,1433;

Initial Catalog = MyDbName;

User ID=myUserName;

Password = 32324;

示例3：使用SQL Express绑定一个本地的SQL数据库

示例4：

//string connstr = "server=(Local);integrated security=SSPI;database=exam";

string connstr = "server=(Local);database=exam;user='sa';pwd='sa'";

SqlConnection myconn = new SqlConnection(connstr);

try

{

myconn.Open();

}

catch (SqlException oe)

{

myconn.Close();

}

finally

{

myconn.Close();

}

或者可以使用：

using (myconn)

{

myconn.Open();

……

myconn.Close();

}

2.2.5   将连接字符串保存到应用程序配置文件app.config中

首先，要将连接字符串添加到App.config文件中。如下例：

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <configSections>

  </configSections>

  <connectionStrings>

    <clear/>

    <add name="PubsData"

         connectionString="server=(Local);database=exam;user=sa;pwd=sa"

         providerName="System.Data.SqlClient"/>

  </connectionStrings>

</configuration>

加下划线部分是我们添加进去的连接字符串节点；

     第二，在引用的程序中使用using System.Configuration;

     第三，特别要注意，要在项目的引用中添加System.configuration引用，否则程序编译时会找不到相关的类；

     第四，使用相关代码从App.Config中获取信息。如：

     ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"];

SqlConnection myconn1 = new SqlConnection(pubs.ConnectionString);

第五，可以对App.config文件加密，保证数据库用户名和密码的安全性；

2.2.6      GetSchema方法

用于查询一个数据存储区的模式信息。使用该方法可以动态地查找一个数据存储区的元数据。示例：

ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"];

DbConnection sqlconn = new SqlConnection(pubs.ConnectionString);

try

{sqlconn.Open();

 DataTable schema = sqlconn.GetSchema();

 sqlconn.Close();

 dataGridView1.DataSource = schema;

}

catch (SqlException oe)

{ sqlconn.Close();

}

finally

{sqlconn.Close();

}

返回的模式包含了连接字符串的指定数据库的整体结构的信息。返回的模式包含三个列：

²        CollectionName  标识出一个可查询的更详细信息的集合的名称；

²        NumberOfRestrictions       该对象表示一个限定符qualifier数组。在获取模式信息时，该数组用于约束模式信息的范围；

²        NumberOfIdentifierParts     对于每个集合项，该对象表示可以组合使用以构成一个完全限定对象名的标识符数目。

通过这个返回的信息，可以进一步查询更特殊的信息。如：

DataTable schema1 = (DataTable)dataGridView1.DataSource;

DataRow currentRow = schema1.Rows[dataGridView1.CurrentCell.RowIndex];

string collection = (string)currentRow["CollectionName"];

ConnectionStringSettings pubs1 = ConfigurationManager.ConnectionStrings["PubsData"];

DbConnection sqlconn1 = new SqlConnection(pubs1.ConnectionString);

try

{

sqlconn1.Open();

DataTable schema2 = sqlconn1.GetSchema(collection);

sqlconn1.Close();

dataGridView2.DataSource = schema2;

}

catch (SqlException oe)

{sqlconn1.Close();

}

finally

{sqlconn1.Close();

}

2.3     DbCommand对象

该对象用于向数据存储区发送一条Sql命令。该对象不仅可以作为获取、插入、更新或删除数据的数据操纵语言DML；还可以作为一条数据定义语言DDL命令，以在数据库中创建表和修改模式信息。

创建一个新的DbCommand对象的方法有3种：

1）  向构造函数构造传递一个DbConnection对象；

2）  将一个DbConnection对象赋值给DbCommand对象的Connection属性；

3）  调用DbConnection对象的CreatCommand方法（这种是常用的方法）；

同时，在创建DbCommand对象时，还要分别为其CommandText和CommandType属性赋予一个合法值。

如：

DbCommand mycommand = cmdconn.CreateCommand();

mycommand.CommandType = CommandType.Text;

mycommand.CommandText = "slecect * from jiaoshi";

       如果CommandType使用的存储过程的话，往往还需要使用DbParameter对象向存储过程传递参数。如：

DbParameter parm = mycommand.CreateParameter();

parm.ParameterName = “@ID”;

……

2.3.1      ExecuteNonQuery

如果是修改数据，无需返回查询结果的SQL命令，则使用这个方法执行Command命令；

DbCommand mycommand = cmdconn.CreateCommand();

mycommand.CommandType = CommandType.Text;

mycommand.CommandText = "update kemu set kmmc ='English' where kmmc ='maths'";

cmdconn.Open();

int count=mycommand.ExecuteNonQuery();

cmdconn.Close();

2.3.2      ExecuteScalar方法

       如果我们希望查询返回一个只包含单行单列的结果集。可以使用该方法；

DbCommand mycommand = cmdconn.CreateCommand();

mycommand.CommandType = CommandType.Text;

mycommand.CommandText = " select count(*) from kemu";

cmdconn.Open();

int count1 = (int)mycommand.ExecuteScalar();

cmdconn.Close();

这种方法的好处就是在.Net运行时不会为结果创建一个DataTable实例，这意味着将使用更少的资源，并提高性能。

2.3.3      ExecuteReader方法

该方法主要是用于对数据库执行查询操作，并返回一个DbDataReader对象实例。关于这个对象的方法使用将在后面介绍；

2.4     DbDataReader对象

ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"];

SqlConnection cmdconn = new SqlConnection(pubs.ConnectionString);

SqlCommand mycommand = cmdconn.CreateCommand();

mycommand.CommandType = CommandType.Text;

mycommand.CommandText = "select * from kemu where kmmc ='English'";

cmdconn.Open();

SqlDataReader dr = mycommand.ExecuteReader();

该对象还有一个load方法向DataTable表中装载数据。但是使用这种方法，可能出现同步错误。一种解决的方法是再次加载DataTable对象，并使用默认的PreserveCurrentValues枚举值加载原先保存数据库数据的DataRowVersion，而使当前的DataRowVersion保持不变。再执行updata方法，就可以成功执行更新操作。这种方法要求表必须要有主键。

2.5    使用多个活动结果集对单个连接执行多条命令

DbDataReader对象是获取数据库中数据的最快方法，但其中一个问题是在遍历过程中，该对象始终打开一个服务器端游标。这个时候，就不能执行另一条命令。在连接诸如SQLServer2005等支持多个活动集的主机时，通过将MultipleActiveResultSets连接字符串选项设置为true，就能避免该异常。因为该功能不能提高性能，只是简化了代码的编写，还容易给性能带来负面影响，因此，最好不要随意使用该功能。这里也不详细讨论了。

当然，MARS的特点可用于一些  根据数据库的连接数目购买相应的数据库客户证书。如果没有MARS，对于需要同时运行的每条命令，都不得不单独打开一个数据库连接，这意味着要购买更多的数据库客户证书。

2.6    使用SqlBulkCopy对象执行批量复制操作

许多情况下，需要将大量的数据从一个位置复制到另一个位置。大多数的数据库服务器都提供了一种在数据库之间复制数据的方法：如，使用SQL企业管理器的WindowsGUI接口，或者使用BulkCopy工具BCP.exe。我们还可以使用SqlBulkCopy类自己编写。

该类主要功能是提供一个将数据复制到SQLServer数据库表中的高性能方法。

步骤：new两个Connection对象，打开对应的数据库；

查询源数据库的数据；

new一个SqlBulkCopy对象；

调用SqlBulkCopy的WriteToServer方法复制数据。

2.7     DbDataAdapter对象 （重点）

该对象主要用于获取和更新DataTable对象与数据存储之间的数据。该对象的主要属性有SelectCommand、InsertCommand、UpdateCommand、DeleteCommand。

注意：如果只是读取数据存储区中的数据，只需要创建SelectCommand对象，但是如果要使用InsertCommand、UpdateCommand、DeleteCommand三个对象的任何一个的话，则必须创建全部四个DbCommand对象。

2.7.1   使用Fill方法将数据从数据存储区域取出

ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"];

DbConnection conn = new SqlConnection(pubs.ConnectionString);

SqlCommand cmd = (SqlCommand)conn.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = " select * from kemu";

SqlDataAdapter myadapter=new SqlDataAdapter(cmd);

conn.Open();

DataSet mydataset = new DataSet("Kemu");

myadapter.Fill(mydataset);              

conn.Close();

注意：很多人员往往试图使用单个DbDataAdapter对象执行所有的查询，或者试图使用单个DbDataAdapter对象执行一条SQL语句查询多个相关的表来返回结果集。如果需要保存数据的变化情况，则应该考虑为每个加载的DataTabel对象都单独的使用一个DbDataAdapter对象。如果只是需要一个只读的DataTable对象，则可以使用一个DbCommand对象和一个DbDataReader对象来加载该DataTable对象，这样可以提高效率。

2.7.2   使用Update方法将修改结果保存到数据库中

Update方法首先获取DataTable对象中的变换情况，然后基于逐行更新的方式，分别使用InsertCommand、UpdateCommand、DeleteCommand，将相应的变换情况发送给数据库，以将DataTable对象的修改结果保存到数据库中。

Update方法通过查看DataRow对象的RowState属性，获取那些已发生变换的DataRow对象，如果DataRow对象的RowState属性值不是Unchanged，Update方法就将修改结果发送给数据库。

要执行Update方法，就必须为DbDataAdapter对象的4个DbCommand命令对象属性赋值。通常必须为这4个命令属性分别单独地创建一个对应的DbCommand对象。也可以使用DbDataAdapter对象的配置向导，向导可以为这4个命令对象一一生成存储过程。

手工设置4个命令属性的话，常用的方法是使用DbCommandBuilder对象来建立。只要存在一个合法的SelectCommand对象，该对象就能创建InsertCommand、UpdateCommand、DeleteCommand对象。

ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"];

DbConnection conn = new SqlConnection(pubs.ConnectionString);

SqlCommand cmd = (SqlCommand)conn.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = " select * from kemu";

SqlDataAdapter myadapter=new SqlDataAdapter(cmd);           

DataSet mydataset = new DataSet("Kemu");

SqlCommandBuilder bldr = new SqlCommandBuilder(myadapter);

conn.Open();

myadapter.Fill(mydataset,"kemu");

//modify

mydataset.Tables["kemu"].Rows[0]["kmbh"] = "4005";

mydataset.Tables["kemu"].Rows.Add("4006", "maths2", DateTime.Now);

mydataset.Tables["kemu"].Rows[3].Delete();

myadapter.Update(mydataset, "kemu");

conn.Close();

myadapter.Fill(mydataset);

mydataset.Tables[0].Rows[0]["kmbh"] = "4012";

mydataset.Tables[0].Rows.Add("4013", "maths2", DateTime.Now);

mydataset.Tables[0].Rows[3].Delete();

myadapter.Update(mydataset);

conn.Close();

边框和底纹标识的可以使用任何一个都可以，因为这里的Adapter中就一个表

这种方式是将修改一条一条发送给源数据，如果设置DbDataAdapter对象的UpdateBatchSize属性值就可以设置一次发送的最大条数；设置为0，则会自动创建最大的分批尺寸。

DbDataAdapter对象适用于特殊的修改和示例，但是对于所有的数据库访问，最好使用存储过程，以避免由恶意攻击SQL而产生的安全风险。

2.8     DbProviderFactory类

有多种原因要求编写一个不需要特定于数据库提供者代码的程序，要求该程序能够连接任何数据源。通过使用通用接口和App.config，可以实现改功能。

示例：

1）app.config添加：

<appSettings>

    <add key="provider" value="SqlClient"/>

</appSettings>

2）public enum DbProvider    {SqlClient, OleDb, Odbc, Oracle}

public IDbConnection GetConnection()

{

//Get the provider from the app.config

DbProvider provider = (DbProvider)Enum.Parse(typeof(DbProvider), (string)ConfigurationManager.AppSettings["provider"]);

IDbConnection connection = null;

switch (provider)

   {

       case DbProvider.SqlClient:

            connection = new System.Data.SqlClient.SqlConnection();

             break;

        case DbProvider.Oracle:

             connection = new System.Data.Odbc.OdbcConnection();

               break;

         case DbProvider.OleDb:

              connection = new System.Data.OleDb.OleDbConnection();

              break;

           case DbProvider.Odbc:

               connection = new System.Data.Odbc.OdbcConnection();

                break;

            default:

                 break;

            }

return connection;

}

这种方法还存在着一些不足。一是不能直接创建接口实例，因此必须包含特定于提供者的代码，以确定所需要创建的连接类型。二是接口在创建后就不能再更改，因此不增加新的接口，就不能方便地增加新的功能。如果想要创建其他多个如Adapter、Command等提供者对象，就要复制上述的代码。但是如果所需要的对象很多时，这种方法还是很笨拙地。ADO.NET提供了一个负责创建合适提供者对象的Factory对象，其实这种方法本质上就是简单工厂模式的应用。

示例：

DbProviderFactory factory = SqlClientFactory.Instance;

public DbConnection GetProviderConnection()

{

DbConnection connection = factory.CreateConnection();

connection.ConnectionString = ConfigurationManager.ConnectionStrings["PubsData"].ConnectionString;

return connection;

}

可以使用factory变量创建其他任何特定于SQLSERVER的对象。但是要注意创建对象的顺序关系。

2.9     DbProviderFactories类

DbProviderFactory对象的子类可用于获得一个创建任何特定于提供者对象的提供者工厂，但最好是获取一个包含可在某台计算机或某个应用程序中使用地提供者工厂列表。通过DbProviderFactories类就可以查询可用的工厂列表。该类是一个获取可用工厂对象的工厂。如：

DataTable providerlist = DbProviderFactories.GetFactoryClasses();

dataGridView3.DataSource = providerlist;

需要说明的是，这个表中的查询结果来自于Machine.config这个XML文件中（C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/CONFIG）。但是我们可以在我们的App.config文件中编辑可以使用的提供者。

在App.config文件中，添加 <DbProviderFactories>节点，使用remove删除提供者，使用add添加提供者。也可以使用clear清除所有的提供者，然后添加你所需的。但是个人认为这个功能不是常用的功能。

2.10  枚举数据源

下面一段代码显示了可以提供给 提供者 的可用数据源。

在DataTable providerlist = DbProviderFactories.GetFactoryClasses();

dataGridView3.DataSource = providerlist;

的基础上，我们添加如下代码：

private void dataGridView3_RowHeaderMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)

{

try

{

    DataRow providerRow = providerlist.DefaultView[e.RowIndex].Row;

    DbProviderFactory factory = DbProviderFactories.GetFactory(providerRow);

    //get sql server instance

    DataTable sources = factory.CreateDataSourceEnumerator().GetDataSources();

    if (sources == null)

       ;

    else

       dataGridView3.DataSource = sources;

}

catch

{

}

}

就可以枚举数据源（注意，不是数据库，可以继续采用类似的方法，进一步获得该数据源里面包含的数据库的信息。

2.11  异常捕获

       通常try或catch语句可以捕获DbException异常。其实，我们可以这样去理解，在.Net中，所有的异常都是从基类Exception继承下来，所以如果我们使用的是Sql，我们象捕获Sql相关的异常，我们就可以使用SqlException类，依次类推。