比如說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 = DBConnectionStri
ng;
//創建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();
}
運行結果