跨数据库连接类(System.Data.Common)

a.net为为们们提供了各个数据库的链接.
比如说MSSQL就是System.Data.SqlClient
         Oracle就是System.Data.OracleClient
         MySql就是MySql.Data.MySqlClient
其它的相应就是的了.还有什么DB2.还有等等一些.
以前我们做跨数据库的时候用的是反射.只不过看到网上好多评论说反射效率不怎么好
所以近来在研究System.Data.Common
也就是通用的数据链接类吧

当然这里只是做测试.没有做太多的封装,
这里是我的目录结构


话不多说.上代码
当然第一步还是在web.config里面写上数据链接语句.
这里是最关键的
我这里测试的只有mysql,mssql

     < connectionStrings >
         < add  name ="ConnectionString1"  connectionString ="Data Source=LIUJU;Initial Catalog=MSPetShop4;Persist Security Info=True;User ID=sa;Password=***"  providerName ="System.Data.SqlClient" />
         < add  name ="ConnectionString"  connectionString ="server=localhost;user id=root;password=***;persist security info=True;database=mspetshop4"  providerName ="MySql.Data.MySqlClient" />
     </ connectionStrings >

这里的name你自己设置一个名字就可以了.connectionString就是数据库链接语句.这里没有什么好说的
最主要的就是后面的providerName这里就是写上你数据库要引入的名称空间.这样程序才会知道你用的是什么数据库

下面这里我就是写的一个webConfig的帮助类.

using  System;
using  System.Collections.Generic;
using  System.Linq;
using  System.Text;
using  System.Configuration;

namespace  DBHelper.Library
{
     public   static   class  WebConfigHelper
    {
         private   readonly   static   string  DBConnectionString  =  ConfigurationManager.ConnectionStrings[ " ConnectionString " ].ConnectionString;
         private   readonly   static   string  DBProviderName  =  ConfigurationManager.ConnectionStrings[ " ConnectionString " ].ProviderName;
         public   static   string  GetConnectionString
        {
             get  {  return  DBConnectionString; }
        }
         public   static   string  GetProviderName
        {
             get  {  return  DBProviderName; }
        }
    }
}

好.下面就是主要的了

using  System;
using  System.Data;
using  System.Data.Common;
using  System.Collections.Generic;
using  System.Linq;
using  System.Text;

namespace  DBHelper.Library
{
     public   static   class  DataAccessHelper
    {
         ///   <summary>
         ///  创建DbCommand对象
         ///   </summary>
         ///   <returns> DbCommand对象 </returns>
         public   static  DbCommand GetCommand()
        {
             // 得到webConfig里面的要引用的名称空间
             string  DBProviderName  =  WebConfigHelper.GetProviderName;
             // 得到webConfig里面的链接字符串
             string  DBConnectionString  =  WebConfigHelper.GetConnectionString;

             // 数据库工厂类为此数据库创建一个数据库链接对象
            DbProviderFactory dpf  =  DbProviderFactories.GetFactory(DBProviderName);
             // 创建Connection
            DbConnection conn  =  dpf.CreateConnection();
            conn.ConnectionString  =  DBConnectionString;
             // 创建Command
            DbCommand comm  =  conn.CreateCommand();
            comm.CommandType  =  CommandType.Text;
             return  comm;
        }

         ///   <summary>
         ///  执行查询，返回datatable
         ///   </summary>
         ///   <param name="command"></param>
         ///   <returns></returns>
         public   static  DataTable ExecuteSelectCommand(DbCommand command)
        {
            DataTable table;
             try
            {
                 if  (command.Connection.State  !=  ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                DbDataReader reader  =  command.ExecuteReader();
                table  =   new  DataTable();
                table.Load(reader);
                reader.Close();
            }
             catch  (Exception ex)
            {
                 throw   new  Exception(ex.Message, ex);
            }
             finally
            {
                command.Connection.Close();
            }
             return  table;

        }
         ///   <summary>
         ///  执行update insert del操作
         ///   </summary>
         ///   <param name="command"></param>
         ///   <returns> 返回影响行数 </returns>
         public   static   int  ExecuteNonQuery(DbCommand command)
        {
             int  affectRows  =   - 1 ;
             try
            {
                 if  (command.Connection.State  !=  ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                affectRows  =  command.ExecuteNonQuery();
            }
             catch  (Exception ex)
            {
                 throw   new  Exception(ex.Message, ex);
            }
             finally
            {
                command.Connection.Close();
            }
             return  affectRows;
        }
         ///   <summary>
         ///  返回第一列第一行
         ///   </summary>
         ///   <param name="command"></param>
         ///   <returns></returns>
         public   static   string  ExecuteScalar(DbCommand command)
        {
             string  value  =   "" ;
             try
            {
                 if  (command.Connection.State  !=  ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                value  =  command.ExecuteScalar().ToString();

            }
             catch  (Exception ex)
            {
                 throw   new  Exception(ex.Message, ex);
            }
             finally
            {
                command.Connection.Close();
            }
             return  value;
        }
    }
}

这样你就可以只要配置一下webConfig程序就自动知道你链接的是什么数据库,
就可以自动为你创建一个数据库链接对象了.

使用方法

         protected   void  Page_Load( object  sender, EventArgs e)
        {
            DbCommand dc  =  DataAccessHelper.GetCommand();
            dc.CommandText  =   " SELECT ProductId, CategoryId, Name, Descn FROM Product " ;
            DataTable dt  =  DataAccessHelper.ExecuteSelectCommand(dc);
            GridView1.DataSource  =  dt;
            GridView1.DataBind();
        }

运行结果

两个数据库都测试正常


参考网址
http://www.cnblogs.com/freegarden/archive/2009/09/25/1574044.html
此应用下载地址
http://files.cnblogs.com/liuju150/DBHelper20091008124542.zip