精简Command版SqlHelper

显示全部楼层 · 2019-12-26 09:07:13

我在写CSharp步伐对数据库举利用用时发现Connection对象起到了连接数据库的做用，实际实行SQL语句使用的是Command对象的方法，以是对SqlHelper举行了重写，详细如下：

一、创建一个ParameterCommand对象，只包罗CommandText和Parameters属性，紧张用于以事件的方式批量实行SQL语句，我感觉比创建List commandTexts和List> paras两个参数方便，也不轻易堕落

public class ParameterCommand
{
private List<DbParameter> paras = new List<DbParameter>();
public string CommandText { get; set; }
public List<DbParameter> Parameters
{
get
{
return paras;
}
}
}

复制代码

二、精简Command版SqlHelper代码如下，传入Command对象做为参数用于实行SQL语句

public static class SqlHelper
{
private static void ResetCommandProperty(DbCommand command, string commandText, params DbParameter[] paras)
{
command.Parameters.Clear();
command.CommandText = commandText;
command.Parameters.AddRange(paras);
}
public static void ExecuteNonQuery(DbCommand command, List<ParameterCommand> paraCommands)
{
command.Transaction = command.Connection.BeginTransaction();
foreach (ParameterCommand paraCommand in paraCommands)
{
try
{
ResetCommandProperty(command, paraCommand.CommandText,paraCommand.Parameters.ToArray());
command.ExecuteNonQuery();
}
catch (Exception ex)
{
command.Transaction.Rollback();
throw ex;
}
}
command.Transaction.Commit();
}
public static void ExecuteNonQuery(DbCommand command, string commandText, params DbParameter[] paras)
{
ResetCommandProperty(command, commandText, paras);
command.ExecuteNonQuery();
}
public static DbDataReader ExecuteReader(DbCommand command, ParameterCommand paraCommand)
{
ResetCommandProperty(command, paraCommand.CommandText, paraCommand.Parameters.ToArray());
return command.ExecuteReader();
}
public static DbDataReader ExecuteReader(DbCommand command, string commandText, params DbParameter[] paras)
{
ResetCommandProperty(command, commandText, paras);
return command.ExecuteReader();
}
public static object ExecuteScalar(DbCommand command, ParameterCommand paraCommand)
{
ResetCommandProperty(command, paraCommand.CommandText,paraCommand.Parameters.ToArray());
return command.ExecuteScalar();
}
public static object ExecuteScalar(DbCommand command, string commandText, params DbParameter[] paras)
{
ResetCommandProperty(command, commandText, paras);
return command.ExecuteScalar();
}
public static DataTable ExecuteTable(DbCommand command, ParameterCommand paraCommand)
{
return ExecuteTable(command, paraCommand.CommandText, paraCommand.Parameters.ToArray());
}
public static DataTable ExecuteTable(DbCommand command, string commandText, params DbParameter[] paras)
{
DataTable table = new DataTable();
ResetCommandProperty(command, commandText, paras);
using (DbDataAdapter adapter = DbProviderFactories.GetFactory(command.Connection).CreateDataAdapter())
{
adapter.SelectCommand = command;
adapter.Fill(table);
}
return table;
}
}

复制代码

三、封装的通用DatabaseClient

public abstract class DatabaseClient
{
private DbConnection connection;
public abstract DbConnection GetConnection();
private DbCommand GetCommand()
{
if (connection == null)
{
connection = GetConnection();
}
if (connection.State == ConnectionState.Broken)
{
connection.Close();
}
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
return connection.CreateCommand();
}
public void ExecuteNonQuery(List<ParameterCommand> paraCommands)
{
using (DbCommand command = GetCommand())
{
SqlHelper.ExecuteNonQuery(command, paraCommands);
}
}
public void ExecuteNonQuery(string commandText, params DbParameter[] paras)
{
using (DbCommand command = GetCommand())
{
SqlHelper.ExecuteNonQuery(command, commandText, paras);
}
}
public DbDataReader ExecuteReader(ParameterCommand paraCommand)
{
using (DbCommand command = GetCommand())
{
return SqlHelper.ExecuteReader(command, paraCommand);
}
}
public DbDataReader ExecuteReader(string commandText, params DbParameter[] paras)
{
using (DbCommand command = GetCommand())
{
return SqlHelper.ExecuteReader(command, commandText, paras);
}
}
public object ExecuteScalar(ParameterCommand paraCommand)
{
using (DbCommand command = GetCommand())
{
return SqlHelper.ExecuteScalar(command, paraCommand);
}
}
public object ExecuteScalar(string commandText, params DbParameter[] paras)
{
using (DbCommand command = GetCommand())
{
return SqlHelper.ExecuteScalar(command, commandText, paras);
}
}
public DataTable ExecuteTable(ParameterCommand paraCommand)
{
using (DbCommand command = GetCommand())
{
return SqlHelper.ExecuteTable(command, paraCommand);
}
}
public DataTable ExecuteTable(string commandText, params DbParameter[] paras)
{
using (DbCommand command = GetCommand())
{
return SqlHelper.ExecuteTable(command, commandText, paras);
}
}
}

复制代码

四、举个栗子：MySQL版客户端

public class MySqlClient : DatabaseClient
{
private string connectionString;
public MySqlClient(string dataSource, string userName, string password)
{
connectionString = "DataSource=" + dataSource + ";UserName=" + userName + ";Password=" + password + ";Charset=utf8";
}
public MySqlClient(string dataSource, string userName, string password, string database)
{
connectionString = "DataSource=" + dataSource + ";UserName=" + userName + ";Password=" + password + "Database=" + database + ";Charset=utf8";
}
public override System.Data.Common.DbConnection GetConnection()
{
return new MySqlConnection(connectionString);
}
}

复制代码

精简Command版SqlHelper

推广达人

突出贡献

优秀版主

荣誉管理

论坛元老