Introduction
Time flies, and before we know it, it's already 2024. In this era of rapid technological advancement, some classics never go out of style. DbHelper, a name that has always been highly regarded in the developer community, still shines brightly today. Recently, a tech expert once again demonstrated the powerful charm of DbHelper. Let's take a look together!
After reading this insightful article, I was deeply inspired and couldn't help but recommend this expert's excellent work. Whether you're a beginner or an experienced developer, I believe you'll gain a lot from it.
- Original article link: https://blog.csdn.net/ftfmatlab/article/details/135655836
- Source code link: https://download.csdn.net/download/ftfmatlab/88765289
Encapsulation of DbHelper: Simple Yet Powerful
Based on the ADO.NET framework, this expert cleverly encapsulated DbHelper to support multiple relational databases. Through clean and concise code, it achieves efficient operations across various databases.
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>
/// Execute a statement
/// </summary>
/// <param name="sql">SQL statement</param>
/// <param name="cmdParms">Parameters</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>
/// Batch query
/// </summary>
/// <param name="sql">SQL statement</param>
/// <param name="cmdParms">Parameters</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>
/// Batch query
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">SQL statement</param>
/// <param name="reader">Data reader</param>
/// <param name="cmdParms">Parameters</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("Data reader is null!");
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>
/// Single query
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">SQL statement</param>
/// <param name="reader">Data reader</param>
/// <param name="cmdParms">Parameters</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("Data reader is null!");
}
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>
/// Execute stored procedure
/// </summary>
/// <param name="storedProcName">Stored procedure name</param>
/// <param name="parameters">Stored procedure 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>
/// Execute stored procedure, return SqlDataReader (Note: After calling this method, SqlDataReader must be closed)
/// </summary>
/// <param name="storedProcName">Stored procedure name</param>
/// <param name="parameters">Stored procedure 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>
/// Execute stored procedure
/// </summary>
/// <param name="storedProcName">Stored procedure name</param>
/// <param name="parameters">Stored procedure parameters</param>
/// <returns>SqlDataReader</returns>
public T RunProcedure<T>(string storedProcName, Func<IDataReader, T> reader, DbParameter[] parameters)
{
if (reader == null)
{
throw new Exception("Data reader is null!");
}
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>
/// Execute stored procedure
/// </summary>
/// <param name="storedProcName">Stored procedure name</param>
/// <param name="parameters">Stored procedure parameters</param>
/// <returns>SqlDataReader</returns>
public List<T> RunProcedureToList<T>(string storedProcName, Func<IDataReader, T> reader, DbParameter[] parameters)
{
if (reader == null)
{
throw new Exception("Data reader is null!");
}
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>
/// Return the first row, first column
/// </summary>
/// <param name="sql">SQL statement</param>
/// <param name="cmdParms">Parameters</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>
/// Paginated list
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tablename">Table name (can be customized)</param>
/// <param name="page">Pagination info</param>
/// <param name="reader">Reader</param>
/// <param name="where">Condition</param>
/// <param name="field">Fields</param>
/// <param name="order">Sort</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>
/// Build pagination SQL
/// </summary>
/// <param name="sql">Base SQL</param>
/// <param name="where">Condition</param>
/// <param name="order">Sort</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>
/// Pagination
/// </summary>
/// <param name="offset">Offset</param>
/// <param name="size">Number of items per page</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("Other pagination syntax is not currently supported");
}
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);
}
}
}
}
Explore the Demo Project
Want to see DbHelper in action? No problem—the expert has prepared a Demo project for us. Take a sneak peek with the screenshot below:
In the Demo project, the DataAchieve class inherits from the abstract DataBase class and overrides the CreationConnection() method. Using the DataBaseFactory factory class, we can easily create DataBase instances. This design pattern not only improves code reusability but also makes the project structure clearer.
The project has been uploaded, so if you're interested, feel free to download it and explore.
2024/01/20: Added Multi-Parameter Generation
To meet the needs of more scenarios, the expert recently upgraded DbHelper. The newly added multi-parameter generation feature will undoubtedly bring greater convenience to developers. Let's take a look at this new addition!
The expert's writing style is quite casual, so you're encouraged to download the code and study it:
- Original article link: https://blog.csdn.net/ftfmatlab/article/details/135655836
- Source code link: https://download.csdn.net/download/ftfmatlab/88765289