跨数据库连接类(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
此应用下载地址
https://files.cnblogs.com/liuju150/DBHelper20091008124542.zip
posted @ 2009-10-08 12:36  Giant150  阅读(6299)  评论(13编辑  收藏  举报