public static int ExecuteNonQuery(this IDbCommand command, string sql, params object[] parameters)
{
command.CommandText = sql;
command.Parameters.Clear();
command.AddParameters(parameters);
return(command.ExecuteNonQuery());
}
public static T ExecuteScalar <T>(this IDbCommand command, string sql, params object[] parameters)
{
command.CommandText = sql;
if (parameters != null)
{
command.Parameters.Clear();
command.AddParameters(parameters);
}
var scalar = command.ExecuteScalar();
if (scalar == null || scalar == DBNull.Value)
{
return(default(T));
}
var scalarType = typeof(T);
if (scalarType == typeof(object))
{
return((T)scalar);
}
if (scalarType.Name == "Nullable`1")
{
scalarType = scalarType.GetGenericArguments()[0];
}
return((T)Convert.ChangeType(scalar, scalarType));
}
public static object ExecuteScalar(string sql, Dictionary <string, object> parameters = null, CustomConnection connection = null)
{
object result = null;
connection = connection ?? new OracleConnection();
IDbCommand command = null;
try
{
connection.Connect();
command = connection.CreateCommand();
command.CommandText = sql;
if (parameters != null)
{
command.AddParameters(parameters);
}
result = command.ExecuteScalar();
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Disconnect();
}
return(result);
}
public static int ExecuteNonQuery(string sql, Dictionary <string, object> parameters = null, CustomConnection connection = null)
{
int affectedRowsCount;
connection = connection ?? new OracleConnection();
IDbCommand command = null;
try
{
connection.Connect();
command = connection.CreateCommand();
command.CommandText = sql;
if (parameters != null)
{
command.AddParameters(parameters);
}
affectedRowsCount = command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Disconnect();
}
return(affectedRowsCount);
}
public static IDbCommand CreateStoredProcedure(this IDbConnection connection, string storedProcedureName, object parameters)
{
IDbCommand command = connection.CreateStoredProcedure(storedProcedureName);
command.AddParameters(parameters);
return(command);
}
public static IDbCommand CreateCustomCommand(this IDbConnection connection, SqlCommand query)
{
IDbCommand command = connection.CreateCommand();
command.CommandText = query.Statement;
command.AddParameters(query.Parameters);
command.CommandTimeout = query.Timeout;
return(command);
}
/// <summary>
/// 执行Sql，返回物件
/// </summary>
/// <param name="sSql">SQL</param>
/// <returns></returns>
protected object ExecuteScalar(string Sql, IDbTransaction Atran)
{
// 实例化命令
IDbCommand NewCommand = CommandUnit.NewCommand(Sql, Atran.Connection, Atran);
// 加入参数
NewCommand.AddParameters(this.Parameter);
return(NewCommand.ExeScalar());
}
public static List <object[]> ExecuteList(this IDbCommand command, string sql, params object[] parameters)
{
command.CommandText = sql;
if (parameters != null)
{
command.Parameters.Clear();
command.AddParameters(parameters);
}
return(ExecuteListReader(command));
}
/// <summary>
/// 查询结果集
/// </summary>
/// <param name="sSql">SQL</param>
/// <param name="tran">tran事务</param>
/// <returns></returns>
/// <remarks></remarks>
protected DataSet SearchDataSet(string Sql, IDbTransaction tran)
{
// 实例化命令
IDbCommand NewCommand = CommandUnit.NewCommand(Sql, tran.Connection, tran);
// 加入参数
NewCommand.AddParameters(this.Parameter);
// 返回结果集
return(NewCommand.SearchDataSet());
}
/// <summary>
/// 执行Sql，返回影响行数
/// </summary>
/// <param name="Sql">Sql</param>
/// <param name="ATans">事务</param>
/// <returns></returns>
protected int ExeNonQuery(string Sql, IDbTransaction ATans)
{
// 实例化命令
IDbCommand NewCommand = CommandUnit.NewCommand(Sql, ATans.Connection, ATans);
// 加入参数
NewCommand.AddParameters(this.Parameter);
// 返回结果
return(NewCommand.ExeNonQuery());
}
/// <summary>
/// 执行Sql，返回影响行数
/// </summary>
/// <param name="sSql">SQL</param>
/// <returns></returns>
protected int ExecuteNonQueryByAtt(string Sql)
{
using (IDbConnection AConnection = this.OpenConnection())
{
IDbCommand NewCommand = CommandUnit.NewCommand(Sql, AConnection);
NewCommand.AddParameters(this.Parameter);
return(NewCommand.ExeNonQuery());
}
}
public static T ExecuteScalar <T>(this IDbCommand command, string sql, params object[] parameters)
{
command.CommandText = sql;
if (parameters != null)
{
command.Parameters.Clear();
command.AddParameters(parameters);
}
var result = command.ExecuteScalar();
return(result == null || result == DBNull.Value ? default(T) : (T)Convert.ChangeType(result, typeof(T)));
}
public static object ExecuteScalar(this IDbCommand command, string sql, params object[] parameters)
{
command.CommandText = sql;
if (parameters != null)
{
command.Parameters.Clear();
command.AddParameters(parameters);
}
object result = command.ExecuteScalar();
return(result);
}
public static IDbCommand CreateCustomCommand(this IDbConnection connection, string sql, IEnumerable <Parameter> parameters = null, int timeOut = 15)
{
IDbCommand command = connection.CreateCommand();
command.CommandText = sql;
if (parameters != null)
{
command.AddParameters(parameters.ToList());
}
command.CommandTimeout = timeOut;
return(command);
}
public static List <Dictionary <string, object> > ExecuteQuery(string sql, Dictionary <string, object> parameters = null, CustomConnection connection = null)
{
var list = new List <Dictionary <string, object> >();
IDataReader dataReader = null;
IDbCommand command = null;
connection = connection ?? new OracleConnection();
try
{
connection.Connect();
command = connection.CreateCommand();
command.CommandText = sql;
if (parameters != null)
{
command.AddParameters(parameters);
}
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
var dictionary = new Dictionary <string, object>();
for (int i = 0; i < dataReader.FieldCount; i++)
{
dictionary.Add(dataReader.GetName(i), dataReader.GetValue(i));
}
list.Add(dictionary);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
connection.Disconnect();
}
return(list);
}
/// <summary>
/// 执行Sql，返回影响行数
/// </summary>
/// <param name="Sql">Sql</param>
/// <param name="bPrepare">是否要预编译</param>
/// <returns></returns>
protected int ExeNonQuery(string Sql, bool bPrepare)
{
using (IDbConnection AConnection = this.OpenConnection())
{
// 实例化命令
IDbCommand NewCommand = CommandUnit.NewCommand(Sql, AConnection);
// 加入参数
NewCommand.AddParameters(this.Parameter);
// 返回结果
return(NewCommand.ExeNonQuery());
}
}
/// <summary>
/// 查询结果集
/// </summary>
/// <param name="Sql">SQL</param>
/// <returns></returns>
protected DataSet SearchDataSet(string Sql)
{
using (IDbConnection AConnection = this.OpenConnection())
{
// 实例化命令
IDbCommand NewCommand = CommandUnit.NewCommand(Sql, AConnection);
// 加入参数
NewCommand.AddParameters(this.Parameter);
// 返回结果集
return(NewCommand.SearchDataSet());
}
}
/// <summary>
/// 执行Sql，返回物件
/// </summary>
/// <param name="Sql">Sql</param>
/// <param name="bPrepare">是否要预编译</param>
/// <returns></returns>
protected object ExecuteScalar(string Sql, bool bPrepare)
{
using (IDbConnection AConnection = this.OpenConnection())
{
// 實例化命令
IDbCommand NewCommand = CommandUnit.NewCommand(Sql, AConnection);
// 加入參數
NewCommand.AddParameters(this.Parameter);
// 返回結果物件
return(NewCommand.ExeScalar());
}
}
/// <summary>
/// 执行Sql，返回Reader物件
/// </summary>
/// <param name="sSql">Sql</param>
/// <param name="bPrepare">是否要预编译</param>
/// <returns></returns>
protected IDataReader ExecuteReader(string Sql, bool bPrepare)
{
// 实例化并开启连接
using (IDbConnection AConnection = this.OpenConnection())
{
// 实例化命令
IDbCommand NewCommand = CommandUnit.NewCommand(Sql, AConnection);
// 加入参数
NewCommand.AddParameters(this.Parameter);
// 返回Reader
return(NewCommand.ExeReader(CommandBehavior.CloseConnection));
}
}
public static int ExecuteNonQuery(string sql, Dictionary <string, object> parameters = null, CustomConnection connection = null)
{
int affectedRowsCount = 0;
connection = connection ?? new MsSqlConnection();
IDbCommand command = null;
using (connection)
{
try
{
connection.Connect();
command = connection.CreateCommand();
command.CommandText = sql;
if (parameters != null)
{
var pKeys = parameters.Select(p => p.Key).ToList();
foreach (var key in pKeys)
{
if (!sql.Contains(key))
{
parameters.Remove(key);
}
}
command.AddParameters(parameters);
}
affectedRowsCount = command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}
return(affectedRowsCount);
}
/// <summary>
/// 使用StoredProcedure查询结果集
/// </summary>
/// <param name="spName">SP名称</param>
/// <returns></returns>
protected int ExecuteNonQuerySP(string spName)
{
using (IDbConnection AConnection = this.OpenConnection())
{
// 实例化命令
IDbCommand newCommand = CommandUnit.NewCommand(AConnection);
// 存储过程类型
newCommand.CommandType = CommandType.StoredProcedure;
// 存储过程名称
newCommand.CommandText = spName;
// 加入参数
newCommand.AddParameters(this.Parameter);
// 返回结果
return(newCommand.ExeNonQuery());;
}
}
/// <summary>
/// 使用StoredProcedure查询结果集
/// </summary>
/// <param name="spName">SP名称</param>
/// <returns></returns>
protected DataTable ExecuteSP(string spName)
{
using (IDbConnection AConnection = this.OpenConnection())
{
// 实例化命令
IDbCommand NewCommand = CommandUnit.NewCommand(AConnection);
// 存储过程命令类型
NewCommand.CommandType = CommandType.StoredProcedure;
// 命令文本
NewCommand.CommandText = spName;
// 加入参数
NewCommand.AddParameters(this.Parameter);
// 返回结果集
return(NewCommand.Search());
}
}
public static List <Dictionary <string, object> > ExecuteQuery(string sql, Dictionary <string, object> parameters = null, CustomConnection connection = null)
{
var list = new List <Dictionary <string, object> >();
IDataReader dataReader = null;
IDbCommand command = null;
connection = connection ?? new MsSqlConnection();
using (connection)
{
try
{
connection.Connect();
command = connection.CreateCommand();
command.CommandText = sql;
if (parameters != null)
{
var pKeys = parameters.Select(p => p.Key).ToList();
foreach (var key in pKeys)
{
if (!sql.Contains(key))
{
parameters.Remove(key);
}
}
command.AddParameters(parameters);
}
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
var dictionary = new Dictionary <string, object>();
for (int i = 0; i < dataReader.FieldCount; i++)
{
dictionary.Add(dataReader.GetName(i), dataReader.GetValue(i));
}
list.Add(dictionary);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
}
}
return(list);
}
public static void AddParameters(this IDbCommand command, params KeyValuePair <string, object>[] parametersData)
{
command.AddParameters(parametersData.ToDictionary());
}
public static void AddParameters(this IDbCommand command, object parametersData)
{
command.AddParameters(parametersData.ObjectToDictionary());
}
