引言
時光荏苒，轉眼已是 2024 年。在這個技術日新月異的時代，有些經典卻永遠不會過時。DbHelper，這個在開發者社群中一直備受矚目的名字，如今依然熠熠生輝。最近，一位技術大神再次展示了 DbHelper 的強大魅力，讓我們一起來欣賞一下吧！
在閱讀了這篇深入淺出的文章後，站長我深受啟發，忍不住要為大家推薦這位大神的佳作。無論你是初學者還是資深開發者，相信都能從中收穫滿滿。
- 原文連結：https://blog.csdn.net/ftfmatlab/article/details/135655836
- 原始碼位置：https://download.csdn.net/download/ftfmatlab/88765289
DbHelper 的封裝：簡約而不簡單
基於ADO.NET框架，這位大神巧妙地封裝了適用於多個關聯式資料庫的DbHelper。透過簡潔明瞭的程式碼，實現了對各種資料庫的高效操作。
public class DbHelper
{
private readonly DataBase _dataBase;
public DbHelper(DataBase dataBase)
{
_dataBase = dataBase;
}
public DataBase GetDataBase()
{
return _dataBase;
}
public DbConnection GetDbConnection()
{
var conn = _dataBase.CreationConnection();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return conn;
}
/// <summary>
/// 執行陳述式
/// </summary>
/// <param name="sql">sql 陳述式</param>
/// <param name="cmdParms">參數</param>
/// <returns></returns>
public int Execute(string sql, params DbParameter[] cmdParms)
{
using (DbConnection connection = GetDbConnection())
{
using (DbCommand cmd = connection.CreateCommand())
{
try
{
PrepareCommand(cmd, connection, null, sql, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (DbException e)
{
throw e;
}
}
}
}
/// <summary>
/// 批次查詢
/// </summary>
/// <param name="sql">sql 陳述式</param>
/// <param name="cmdParms">參數</param>
/// <returns></returns>
public DataSet Query(string sql, params DbParameter[] cmdParms)
{
using (DbConnection connection = GetDbConnection())
{
DataSet ds = new DataSet();
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory(connection);
DbCommand command = factory.CreateCommand();
PrepareCommand(command, connection, null, sql, cmdParms);
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;
adapter.Fill(ds, "ds");
adapter.Dispose();
command.Dispose();
}
catch (DbException ex)
{
throw ex;
}
return ds;
}
}
/// <summary>
/// 批次查詢
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">sql 陳述式</param>
/// <param name="reader">資料讀取器</param>
/// <param name="cmdParms">參數</param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public List<T> Query<T>(string sql, Func<IDataReader, T> reader, params DbParameter[] cmdParms)
{
if (reader == null)
throw new Exception("資料讀取器是空的!");
List<T> list = new List<T>();
using (DbConnection connection = GetDbConnection())
{
using (DbCommand cmd = connection.CreateCommand())
{
try
{
PrepareCommand(cmd, connection, null, sql, cmdParms);
DbDataReader myReader = cmd.ExecuteReader();
cmd.Parameters.Clear();
while (myReader.Read())
{
list.Add(reader(myReader));
}
myReader.Close();
}
catch (DbException e)
{
throw e;
}
}
}
return list;
}
/// <summary>
/// 單筆查詢
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">sql 陳述式</param>
/// <param name="reader">資料讀取器</param>
/// <param name="cmdParms">參數</param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public T QueryFirstOrDefault<T>(string sql, Func<IDataReader, T> reader, params DbParameter[] cmdParms)
{
if (reader == null)
{
throw new Exception("資料讀取器是空的!");
}
var model = default(T);
using (DbConnection connection = GetDbConnection())
{
using (DbCommand cmd = connection.CreateCommand())
{
try
{
PrepareCommand(cmd, connection, null, sql, cmdParms);
DbDataReader myReader = cmd.ExecuteReader();
cmd.Parameters.Clear();
if (myReader.Read())
model = reader(myReader);
myReader.Close();
}
catch (DbException e)
{
throw e;
}
}
}
return model;
}
/// <summary>
/// 執行預存程序
/// </summary>
/// <param name="storedProcName">預存程序名稱</param>
/// <param name="parameters">預存程序參數</param>
/// <returns></returns>
public DataSet RunProcedure(string storedProcName, DbParameter[] parameters)
{
using (DbConnection connection = GetDbConnection())
{
DataSet dataSet = new DataSet();
connection.Open();
DbDataAdapter sqlDA = DbProviderFactories.GetFactory(connection).CreateDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, "ds");
sqlDA.SelectCommand.Dispose();
sqlDA.Dispose();
return dataSet;
}
}
/// <summary>
/// 執行預存程序，傳回 SqlDataReader ( 注意：呼叫此方法後，一定要對 SqlDataReader 進行 Close )
/// </summary>
/// <param name="storedProcName">預存程序名稱</param>
/// <param name="parameters">預存程序參數</param>
/// <returns>SqlDataReader</returns>
public DbDataReader RunProcedureToReader(string storedProcName, DbParameter[] parameters)
{
using (DbConnection connection = GetDbConnection())
{
DbDataReader returnReader;
connection.Open();
DbCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
command.Dispose();
return returnReader;
}
}
/// <summary>
/// 執行預存程序
/// </summary>
/// <param name="storedProcName">預存程序名稱</param>
/// <param name="parameters">預存程序參數</param>
/// <returns>SqlDataReader</returns>
public T RunProcedure<T>(string storedProcName, Func<IDataReader, T> reader, DbParameter[] parameters)
{
if (reader == null)
{
throw new Exception("資料讀取器是空的!");
}
T t = default(T);
using (DbConnection connection = GetDbConnection())
{
DbDataReader returnReader;
connection.Open();
DbCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
command.Dispose();
if (returnReader.Read())
t = reader(returnReader);
returnReader.Close();
}
return t;
}
/// <summary>
/// 執行預存程序
/// </summary>
/// <param name="storedProcName">預存程序名稱</param>
/// <param name="parameters">預存程序參數</param>
/// <returns>SqlDataReader</returns>
public List<T> RunProcedureToList<T>(string storedProcName, Func<IDataReader, T> reader, DbParameter[] parameters)
{
if (reader == null)
{
throw new Exception("資料讀取器是空的!");
}
List<T> list = new List<T>();
using (DbConnection connection = GetDbConnection())
{
DbDataReader returnReader;
connection.Open();
DbCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
command.Dispose();
while (returnReader.Read())
list.Add(reader(returnReader));
returnReader.Close();
}
return list;
}
/// <summary>
/// 傳回首行首列
/// </summary>
/// <param name="sql">sql 陳述式</param>
/// <param name="cmdParms">參數</param>
/// <returns></returns>
public object ExecuteScalar(string sql, params DbParameter[] cmdParms)
{
object result = null;
using (DbConnection connection = GetDbConnection())
{
using (DbCommand cmd = connection.CreateCommand())
{
try
{
PrepareCommand(cmd, connection, null, sql, cmdParms);
result = cmd.ExecuteScalar();
}
catch (DbException e)
{
throw e;
}
}
}
return result;
}
/// <summary>
/// 分頁列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tablename">資料表名稱(可自訂)</param>
/// <param name="page">分頁資訊</param>
/// <param name="reader">讀取器</param>
/// <param name="where">條件</param>
/// <param name="field">欄位</param>
/// <param name="order">排序</param>
public List<T> QueryWithPage<T>(string tablename, PageInfo page, Func<IDataReader, T> reader, string where = "", string field = "*", string order = "", params DbParameter[] cmdParms)
{
long offset = page.Index * page.PageSize;
string sql = "SELECT " + field + " FROM " + tablename;
sql = ListPageSql(sql, where, order);
sql = sql + " " + Limit(offset, page.PageSize);
string sql2 = "SELECT COUNT(0) FROM " + tablename;
sql2 = ListPageSql(sql2, where, "");
string sql3 = sql + ";" + sql2;
List<T> list = new List<T>();
using (DbConnection conn = GetDbConnection())
{
using (DbCommand cmd = conn.CreateCommand())
{
try
{
PrepareCommand(cmd, conn, null, sql3, cmdParms);
DbDataReader myReader = cmd.ExecuteReader();
cmd.Parameters.Clear();
while (myReader.Read())
{
list.Add(reader(myReader));
}
if (myReader.NextResult() && myReader.Read())
page.Count = myReader.GetInt64Ex(0);
myReader.Close();
}
catch (MySqlException e)
{
throw new Exception(e.Message);
}
}
}
return list;
}
/// <summary>
/// 組裝分頁 sql
/// </summary>
/// <param name="sql">基礎 sql</param>
/// <param name="where">條件</param>
/// <param name="order">排序</param>
/// <returns></returns>
private string ListPageSql(string sql, string where, string order)
{
if (!string.IsNullOrEmpty(where))
{
sql = sql + " WHERE " + where;
}
if (!string.IsNullOrEmpty(order))
{
sql = sql + " " + order;
}
return sql;
}
/// <summary>
/// 分頁
/// </summary>
/// <param name="offset">偏移量</param>
/// <param name="size">每頁顯示資料筆數</param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public string Limit(long offset, long size)
{
if (offset == -1)
{
if (_dataBase.DbType != DbBaseType.SqlServer)
{
return "LIMIT " + size;
}
}
else
{
if (_dataBase.DbType == DbBaseType.MySql)
{
return string.Format("LIMIT {0},{1}", offset, size);
}
if (_dataBase.DbType == DbBaseType.PostgreSql || _dataBase.DbType == DbBaseType.Sqlite)
{
return string.Format(" LIMIT {0} OFFSET {1}", size, offset);
}
}
throw new Exception("暫時不支援其他分頁語法");
}
public DbParameter CreateDbParameter(string parameterName, DbType dbType, object value)
{
using(DbConnection connection = GetDbConnection())
{
DbParameter dbParameter = DbProviderFactories.GetFactory(connection).CreateParameter();
dbParameter.ParameterName = parameterName;
dbParameter.DbType = dbType;
dbParameter.Value = value;
return dbParameter;
}
}
protected void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms)
{
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
SetParameters(cmd, cmdParms);
}
private DbCommand BuildQueryCommand(DbConnection connection, string storedProcName, DbParameter[] parameters)
{
DbCommand command = connection.CreateCommand();
command.CommandText = storedProcName;
command.CommandType = CommandType.StoredProcedure;
SetParameters(command, parameters);
return command;
}
private void SetParameters(DbCommand command, DbParameter[] cmdParms)
{
if (cmdParms != null)
{
foreach (var parameter in cmdParms)
{
if (
(parameter.Direction == ParameterDirection.InputOutput
||
parameter.Direction == ParameterDirection.Input)
&&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
}
}
Demo 專案中一探究竟
想要一睹 DbHelper 在實戰中的風采？沒問題，大神已經為我們準備好了 Demo 專案。透過以下截圖，我們可以先睹為快：
在 Demo 專案中，DataAchieve 類別繼承了 DataBase 抽象類別，並覆寫了 CreationConnection() 方法。透過 DataBaseFactory 工廠類別，我們可以輕鬆建立 DataBase 實例。這種設計模式不僅提高了程式碼的複用性，還使得專案結構更加清晰。
該專案已上傳，有興趣的朋友可以下載下來一探究竟。
2024/01/20 新增多個參數產生
為了滿足更多場景下的需求，大神在近期對 DbHelper 進行了升級。新增的多個參數產生功能無疑將為開發者們帶來更多便利。讓我們一起來看看這個新特性吧！
大神寫作過於隨性，大家可下載程式碼研究：
- 原文連結：https://blog.csdn.net/ftfmatlab/article/details/135655836
- 原始碼位置：https://download.csdn.net/download/ftfmatlab/88765289