以下是我編寫的DataAccess通用數據庫訪問類，簡單易用，支持：內聯式創建多個參數、支持多事務提交、支持參數復用、支持更換數據庫類型，希望能幫到大家，若需支持查出來後轉換成實體，可以自行擴展datarow轉實體類，也可以搭配dapper.net實現更強大的功能。
/// <summary>
/// 通用數據庫訪問類，支持多種數據庫，無直接依賴某個數據庫組件
/// 作者：左文俊
/// 日期：2016-6-3
/// </summary>
public class DataAccess : IDisposable
{
private static DbProviderFactory dbProviderFactory = null;
private static string connectionString = null;
public static string ConnectionStringName = "default";
private DbConnection dbConnection = null;
private DbTransaction dbTransaction = null;
private bool useTransaction = false;
private bool disposed = false;
private bool committed = false;
private ParameterHelperClass paramHelper = null;
public DataAccess()
: this(ConnectionStringName)
{ }
public DataAccess(string cnnStringName)
{
if (!ConnectionStringName.Equals(cnnStringName, StringComparison.OrdinalIgnoreCase) ||
dbProviderFactory == null || connectionString == null)
{
ConnectionStringName = cnnStringName;
var cnnStringSection = ConfigurationManager.ConnectionStrings[cnnStringName];
dbProviderFactory = DbProviderFactories.GetFactory(cnnStringSection.ProviderName);
connectionString = cnnStringSection.ConnectionString;
}
paramHelper = new ParameterHelperClass(this);
}
#region 私有方法
private DbConnection GetDbConnection()
{
if (dbConnection == null)
{
dbConnection = dbProviderFactory.CreateConnection();
dbConnection.ConnectionString = connectionString;
}
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
if (useTransaction && dbTransaction == null)
{
dbTransaction = dbConnection.BeginTransaction();
committed = false;
}
return dbConnection;
}
private DbCommand BuildDbCommand(string sqlCmdText, CommandType cmdType = CommandType.Text, DbParameter[] parameters = null)
{
var dbCmd = dbProviderFactory.CreateCommand();
var dbConn = GetDbConnection();
dbCmd.Connection = dbConn;
dbCmd.CommandText = sqlCmdText;
dbCmd.CommandType = cmdType;
if (useTransaction)
{
dbCmd.Transaction = dbTransaction;
}
if (parameters != null)
{
dbCmd.Parameters.AddRange(parameters);
}
return dbCmd;
}
private DbCommand BuildDbCommand(string sqlCmdText, CommandType cmdType = CommandType.Text, IDictionary<string, object> paramNameValues = null)
{
List<DbParameter> parameters = new List<DbParameter>();
if (paramNameValues != null)
{
foreach (var item in paramNameValues)
{
parameters.Add(BuildDbParameter(item.Key, item.Value));
}
}
return BuildDbCommand(sqlCmdText, cmdType, parameters.ToArray());
}
private DbCommand BuildDbCommand(string sqlCmdText, CommandType cmdType = CommandType.Text, params object[] paramObjs)
{
if (paramObjs != null)
{
if (paramObjs[0] is IDictionary<string, object>)
{
return BuildDbCommand(sqlCmdText, cmdType, paramObjs[0] as IDictionary<string, object>);
}
else if (paramObjs is DbParameter[])
{
return BuildDbCommand(sqlCmdText, cmdType, paramObjs as DbParameter[]);
}
else
{
List<DbParameter> parameters = new List<DbParameter>();
for (int i = 0; i < paramObjs.Length; i++)
{
parameters.Add(BuildDbParameter("@p" + i.ToString(), paramObjs[0]));
}
return BuildDbCommand(sqlCmdText, cmdType, parameters.ToArray());
}
}
else
{
return BuildDbCommand(sqlCmdText, cmdType, parameters: null);
}
}
private void ClearCommandParameters(DbCommand cmd)
{
bool canClear = true;
if (cmd.Connection != null && cmd.Connection.State != ConnectionState.Open)
{
foreach (DbParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
{
canClear = false;
break;
}
}
}
if (canClear)
{
cmd.Parameters.Clear();
}
}
#endregion
#region 公共方法
public void UseTransaction()
{
useTransaction = true;
}
public void Commit()
{
if (dbTransaction != null && useTransaction)
{
dbTransaction.Commit();
dbTransaction.Dispose();
dbTransaction = null;
committed = true;
useTransaction = false;
}
}
public DbParameter BuildDbParameter(string name, object value)
{
DbParameter parameter = dbProviderFactory.CreateParameter();
parameter.ParameterName = name;
parameter.Value = value;
return parameter;
}
public DbParameter BuildDbParameter(string name, object value, DbType dbType, ParameterDirection direction = ParameterDirection.Input)
{
DbParameter parameter = dbProviderFactory.CreateParameter();
parameter.ParameterName = name;
parameter.Value = value;
parameter.DbType = dbType;
parameter.Direction = direction;
return parameter;
}
public DbDataReader ExecuteReader(string sqlCmdText, CommandType cmdType = CommandType.Text, params object[] paramObjs)
{
var dbCmd = BuildDbCommand(sqlCmdText, cmdType, paramObjs);
var dr = dbCmd.ExecuteReader();
ClearCommandParameters(dbCmd);
return dr;
}
public T ExecuteScalar<T>(string sqlCmdText, CommandType cmdType = CommandType.Text, params object[] paramObjs)
{
T returnValue = default(T);
var dbCmd = BuildDbCommand(sqlCmdText, cmdType, paramObjs);
object result = dbCmd.ExecuteScalar();
try
{
returnValue = (T)Convert.ChangeType(result, typeof(T));
}
catch
{ }
ClearCommandParameters(dbCmd);
return returnValue;
}
public DataSet ExecuteDataSet(string sqlCmdText, CommandType cmdType = CommandType.Text, params object[] paramObjs)
{
var dbCmd = BuildDbCommand(sqlCmdText, cmdType, paramObjs);
var dbAdapter = dbProviderFactory.CreateDataAdapter();
dbAdapter.SelectCommand = dbCmd;
DataSet returnDataSet = new DataSet();
dbAdapter.Fill(returnDataSet);
ClearCommandParameters(dbCmd);
return returnDataSet;
}
public DataTable ExecuteDataTable(string sqlCmdText, CommandType cmdType = CommandType.Text, params object[] paramObjs)
{
DataTable returnTable = new DataTable();
DataSet resultDataSet = ExecuteDataSet(sqlCmdText, cmdType, paramObjs);
if (resultDataSet != null && resultDataSet.Tables.Count > 0)
{
returnTable = resultDataSet.Tables[0];
}
return returnTable;
}
public int ExecuteCommand(string sqlCmdText, CommandType cmdType = CommandType.Text, params object[] paramObjs)
{
var dbCmd = BuildDbCommand(sqlCmdText, cmdType, paramObjs);
int execResult = dbCmd.ExecuteNonQuery();
ClearCommandParameters(dbCmd);
return execResult;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
private void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
//釋放托管資源
}
if (dbTransaction != null)
{
if (!committed)
{
dbTransaction.Rollback();
}
dbTransaction.Dispose();
}
if (dbConnection != null)
{
if (dbConnection.State != ConnectionState.Closed)
{
dbConnection.Close();
}
dbConnection.Dispose();
}
disposed = true;
}
}
~DataAccess()
{
Dispose(false);
}
public ParameterHelperClass ParameterHelper
{
get
{
return paramHelper;
}
}
public class ParameterHelperClass
{
private List<DbParameter> parameterList = null;
private DataAccess parent = null;
public ParameterHelperClass(DataAccess da)
{
parent = da;
parameterList = new List<DbParameter>();
}
public ParameterHelperClass AddParameter(string name, object value)
{
parameterList.Add(parent.BuildDbParameter(name, value));
return this;
}
public ParameterHelperClass AddParameter(string name, object value, DbType dbType, ParameterDirection direction = ParameterDirection.Input)
{
parameterList.Add(parent.BuildDbParameter(name, value, dbType, direction));
return this;
}
public ParameterHelperClass AddParametersWithValue(params object[] paramValues)
{
for (int i = 0; i < paramValues.Length; i++)
{
parameterList.Add(parent.BuildDbParameter("@p" + i.ToString(), paramValues[0]));
}
return this;
}
public DbParameter[] ToParameterArray()
{
var paramList = parameterList;
parameterList = new List<DbParameter>();
return paramList.ToArray();
}
}
}
多種靈活用法，使用示例代碼如下：
用法一：采用內聯式創建參數數組對象，然後執行SQL命令
using (DataAccess da = new DataAccess())
{
var programInfo = new ProgramInfo() { Name="test", Version="1.0", InstalledLocation=AppDomain.CurrentDomain.BaseDirectory };
var parameters = da.ParameterHelper.AddParameter("@Mbno", "1882316708*")
.AddParameter("@Msg", string.Format("程序名：{0}，版本：{1}，安裝路徑：{2}，已停止運行了，請盡快處理！",
programInfo.Name, programInfo.Version, programInfo.InstalledLocation))
.AddParameter("@SendTime", DateTime.Now)
.AddParameter("@KndType", "監控異常通知")
.ToParameterArray();
da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);
}
用法二：在用法一基礎上使用事務來進行提交
using (DataAccess da = new DataAccess())
{
var programInfo = new ProgramInfo() { Name = "test", Version = "1.0", InstalledLocation = AppDomain.CurrentDomain.BaseDirectory };
var parameters = da.ParameterHelper.AddParameter("@Mbno", "1882316708*")
.AddParameter("@Msg", string.Format("程序名：{0}，版本：{1}，安裝路徑：{2}，已停止運行了，請盡快處理！",
programInfo.Name, programInfo.Version, programInfo.InstalledLocation))
.AddParameter("@SendTime", DateTime.Now)
.AddParameter("@KndType", "監控異常通知")
.ToParameterArray();
da.UseTransaction();
da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);
da.Commit();
}
用法三：在用法二基礎上使用事務一次性執行多個SQL命令
using (DataAccess da = new DataAccess())
{
var programInfo = new ProgramInfo() { Name = "test", Version = "1.0", InstalledLocation = AppDomain.CurrentDomain.BaseDirectory };
var parameters = da.ParameterHelper.AddParameter("@Mbno", "1882316708*")
.AddParameter("@Msg", string.Format("程序名：{0}，版本：{1}，安裝路徑：{2}，已停止運行了，請盡快處理！",
programInfo.Name, programInfo.Version, programInfo.InstalledLocation))
.AddParameter("@SendTime", DateTime.Now)
.AddParameter("@KndType", "監控異常通知")
.ToParameterArray();
da.UseTransaction();
da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);
da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);
da.Commit();
}
用法四：在用法一基礎上使用多個事務來執行多個SQL並進行多次提交
using (DataAccess da = new DataAccess())
{
var programInfo = new ProgramInfo() { Name = "test", Version = "1.0", InstalledLocation = AppDomain.CurrentDomain.BaseDirectory };
var parameters = da.ParameterHelper.AddParameter("@Mbno", "1882316708*")
.AddParameter("@Msg", string.Format("程序名：{0}，版本：{1}，安裝路徑：{2}，已停止運行了，請盡快處理！",
programInfo.Name, programInfo.Version, programInfo.InstalledLocation))
.AddParameter("@SendTime", DateTime.Now)
.AddParameter("@KndType", "監控異常通知")
.ToParameterArray();
da.UseTransaction();
da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);
da.Commit();
da.UseTransaction();
da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);
da.Commit();
}
用法五：事務提交+SQL命令查詢
using (DataAccess da = new DataAccess())
{
var programInfo = new ProgramInfo() { Name = "test", Version = "1.0", InstalledLocation = AppDomain.CurrentDomain.BaseDirectory };
var parameters = da.ParameterHelper.AddParameter("@Mbno", "1882316708*")
.AddParameter("@Msg", string.Format("程序名：{0}，版本：{1}，安裝路徑：{2}，已停止運行了，請盡快處理！",
programInfo.Name, programInfo.Version, programInfo.InstalledLocation))
.AddParameter("@SendTime", DateTime.Now)
.AddParameter("@KndType", "監控異常通知")
.ToParameterArray();
da.UseTransaction();
da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);
da.Commit();
parameters = da.ParameterHelper.AddParameter("@Mbno", "18823167089").ToParameterArray();
var table = da.ExecuteDataTable("select Mbno,Msg,SendTime,KndType from OutBox where Mbno=@Mbno", paramObjs: parameters);
System.Windows.Forms.MessageBox.Show(table.Rows.Count.ToString());
}
用法六：不采用內聯方式創建參數，而是執行SQL命令時直接傳入各類型的參數
using (DataAccess da = new DataAccess())
{
var programInfo = new ProgramInfo() { Name = "test", Version = "1.0", InstalledLocation = AppDomain.CurrentDomain.BaseDirectory };
da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)",
System.Data.CommandType.Text,
new Dictionary<string, object> {
{"@Mbno", "1882316708*"},
{"@Msg", string.Format("程序名：{0}，版本：{1}，安裝路徑：{2}，已停止運行了，請盡快處理！",
programInfo.Name, programInfo.Version, programInfo.InstalledLocation)},
{"@SendTime", DateTime.Now},
{"@KndType", "監控異常通知"}
});
var table = da.ExecuteDataTable("select Mbno,Msg,SendTime,KndType from OutBox where Mbno=@p0",
System.Data.CommandType.Text,
"18823167089"//若采用直接是輸入值數組，那麼SQL命令中的參數占位符必需定義成：@p0,@p1...
);
System.Windows.Forms.MessageBox.Show(table.Rows.Count.ToString());
}
用法七：除了上面使用DataAccess.ParameterHelper屬性的AddParameter(string name, object value)方法來創建參數，還可以使用AddParameter(string name, object value, DbType dbType, ParameterDirection direction = ParameterDirection.Input)來創建指定輸入輸出及類型的參數，還有AddParametersWithValue(params object[] paramValues)來根據值數組創建參數
若需要更換數據庫類型，只需要在配置文件的connectionStrings節點加入相關的連接子節點，注意providerName特性，providerName常用的如下：
Aceess數據庫：providerName="System.Data.OleDb"
Oracle 數據庫：providerName="System.Data.OracleClient"或者providerName="Oracle.DataAccess.Client"
SQLite數據庫：providerName="System.Data.SQLite"
SQL SERVER數據庫：providerName="System.Data.SqlClient"
MYSQL數據庫：providerName="MySql.Data.MySqlClient"
ODBC連接數據庫:providerName="System.Data.Odbc"
