private DbCommand PrepareCommand(string sql, IDbParms parameters, CommandType CommandType)
{
DbCommand Command;
if (Trans != null)
{
//Command = _ProviderFactory.CreateCommand();
Command = Trans.Connection.CreateCommand();
Command.Transaction = Trans;
}
else
{
//Command = _ProviderFactory.CreateCommand();
Command = Conn.CreateCommand();
//Command = _ProviderFactory.CreateCommand();
//Command.Connection = Conn;
}
Command.CommandType = CommandType;
Command.CommandText = sql;
Command.CommandTimeout = CommandTimeOut;
//Command.SetParameters(parameters);
if (parameters != null)
{
SetParameters(Command, parameters);
}
//Command.Disposed += Command_Disposed;
return(Command);
}
public RptCommonUnitTable(Form owner, string RptFileName, string SPName, IDbParms Parms)
: base(owner, RptFileName)
{
ISSP = true;
this.SPName = SPName;
this.Parms = Parms;
}
//private void Command_Disposed(object sender, EventArgs e)
//{
//}
//private void ClearCommandParams()
//{
// Command.Parameters.Clear();
//}
private void SetParameters(DbCommand Command, IDbParms parameters)
{
if (parameters == null)
{
return;
}
var parms = parameters.GetParms();
var pv = parameters.GetParmArrary();
if (parms != null && parms.Length > 0)
{
Command.Parameters.AddRange(parms);
}
if (pv != null)
{
foreach (string key in pv.Keys)
{
var cp = Command.CreateParameter();
cp.ParameterName = key;
cp.Value = pv[key];
Command.Parameters.Add(cp);
}
}
}
/// <summary>
/// 单表数据报表 存储过程获得数据源
/// </summary>
/// <param name="owner"></param>
/// <param name="RptFileName"></param>
/// <param name="SPName"></param>
/// <param name="Parms"></param>
public RptCommonCustomer(Form owner, string RptFileName, string SPName, IDbParms Parms)
: base(owner, RptFileName)
{
this.SPName = SPName;
this.Parms = Parms;
ISSP = true;
}
/// <summary>
/// 执行SQL语句，委托处理结果
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">查询参数</param>
/// <param name="action">处理委托</param>
public void ExecuteDataReader(string sql, IDbParms parameters, Action <DbDataReader> action)
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
db.ExecuteDataReader(sql, parameters, action);
}
}
private int ExecuteNonQuery(CommandType CommandType, string sql, IDbParms parameters)
{
using (var Command = PrepareCommand(sql, parameters, CommandType))
{
int query = Command.ExecuteNonQuery();
return(query);
}
}
/// <summary>
/// 执行存储过程，判断是否有返回数据
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="parameters">查询参数</param>
/// <returns></returns>
public bool HasRowSP(string StoredProcedureName, IDbParms parameters)
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
return(db.HasRowSP(StoredProcedureName, parameters));
}
}
/// <summary>
/// 执行存储过程，返回第一行第一列
/// </summary>
/// <typeparam name="T">返回类型</typeparam>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="parameters">查询参数</param>
/// <returns></returns>
public T ExecuteScalarSP <T>(string StoredProcedureName, IDbParms parameters)
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
return(db.ExecuteScalarSP <T>(StoredProcedureName, parameters));
}
}
/// <summary>
/// 执行存储过程，返回DataTable结构
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="TableName">表名</param>
/// <param name="parameters">查询参数</param>
/// <returns></returns>
public DataTable GetTableSP(string StoredProcedureName, string TableName, IDbParms parameters)
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
return(db.GetTableSP(StoredProcedureName, TableName, parameters));
}
}
/// <summary>
/// 执行存储过程，委托处理结果
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="parameters"></param>
/// <param name="action"></param>
public void ExecuteDataReaderSP(string StoredProcedureName, IDbParms parameters, Action <DbDataReader> action)
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
db.ExecuteDataReaderSP(StoredProcedureName, parameters, action);
}
}
/// <summary>
/// 执行存储过程，并返回受影响行数
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="parameters">查询参数</param>
/// <returns></returns>
public int ExecuteNonQuerySP(string StoredProcedureName, IDbParms parameters)
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
return(db.ExecuteNonQuerySP(StoredProcedureName, parameters));
}
}
/// <summary>
/// 执行SQL语句，判断是否有返回数据
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public bool HasRow(string sql, IDbParms parameters)
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
return(db.HasRow(sql, parameters));
}
}
/// <summary>
/// 执行SQL语句，返回第一行第一列
/// </summary>
/// <typeparam name="T">返回类型</typeparam>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">查询参数</param>
/// <returns></returns>
public T ExecuteScalar <T>(string sql, IDbParms parameters)
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
return(db.ExecuteScalar <T>(sql, parameters));
}
}
/// <summary>
/// 执行SQL语句，返回DataSet结构
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">查询参数</param>
/// <returns></returns>
public DataSet GetDataSet(string sql, IDbParms parameters = null)
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
return(db.GetDataSet(sql, parameters));
}
}
/// <summary>
/// 执行SQL语句，返回DataTable结构
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="TableName">表名</param>
/// <param name="parameters">查询参数</param>
/// <returns></returns>
public DataTable GetTable(string sql, string TableName, IDbParms parameters = null)
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
return(db.GetTable(sql, TableName, parameters));
}
}
/// <summary>
/// 执行存储过程，并返回第一条数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="StoredProcedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public T ExecuteDataFirstSP <T>(string StoredProcedureName, IDbParms parameters) where T : new()
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
return(db.ExecuteDataFirstSP <T>(StoredProcedureName, parameters));
// 这里一定要用yield，这样可以延迟执行，直接用return db.ExecuteDataReader(sql, parameters, action)在执行dr.Read()的时候，cmd对象早就释放掉了
//foreach (var r in db.ExecuteDataReaderSP(StoredProcedureName, parameters, action))
// yield return r;
}
}
/// <summary>
/// 执行SQL语句，并返回第一条数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <param name="action"></param>
/// <returns></returns>
public T ExecuteDataFirst <T>(string sql, IDbParms parameters, Func <DbDataReader, T> action)
{
using (var connection = CreateConnection())
{
var db = GetCommandDataBase(connection);
return(db.ExecuteDataFirst(sql, parameters, action));
// 这里一定要用yield，这样可以延迟执行，直接用return db.ExecuteDataReader(sql, parameters, action)在执行dr.Read()的时候，cmd对象早就释放掉了
//foreach (var r in db.ExecuteDataReader(sql, parameters, action))
// yield return r;
}
}
private bool HasRow(CommandType CommandType, string sql, IDbParms parameters)
{
using (var Command = PrepareCommand(sql, parameters, CommandType))
{
bool success = false;
using (var dr = Command.ExecuteReader())
{
success = dr.HasRows;
}
//ClearCommandParams();
return(success);
}
}
private void ExecuteDataReader(CommandType CommandType, string sql, IDbParms parameters, Action <DbDataReader> action)
{
using (var Command = PrepareCommand(sql, parameters, CommandType))
{
using (var dr = Command.ExecuteReader())
{
while (dr.Read())
{
action.Invoke(dr);
}
}
//ClearCommandParams();
}
}
private DataSet GetDataSet(CommandType CommandType, string sql, IDbParms parameters)
{
DataSet ds = new DataSet();
using (var DataAdapter = CreateDataAdapter())
{
using (var Command = PrepareCommand(sql, parameters, CommandType))
{
DataAdapter.SelectCommand = Command;
DataAdapter.Fill(ds);
}
//ClearCommandParams();
return(ds);
}
}
private T ExecuteDataFirst <T>(CommandType CommandType, string sql, IDbParms parameters) where T : new()
{
using (var Command = PrepareCommand(sql, parameters, CommandType))
{
T result = default(T);
using (var dr = Command.ExecuteReader())
{
if (dr.Read())
{
result = dr.ToObject <T>();
}
//yield return action.Invoke(dr);
}
//ClearCommandParams();
return(result);
}
}
private List <T> ExecuteDataList <T>(CommandType CommandType, string sql, IDbParms parameters) where T : new()
{
using (var Command = PrepareCommand(sql, parameters, CommandType))
{
List <T> lst = new List <T>();
using (var dr = Command.ExecuteReader())
{
while (dr.Read())
{
lst.Add(dr.ToObject <T>());
}
//yield return action.Invoke(dr);
}
//ClearCommandParams();
return(lst);
}
}
private T ExecuteScalar <T>(CommandType CommandType, string sql, IDbParms parameters)
{
using (var Command = PrepareCommand(sql, parameters, CommandType))
{
var v = Command.ExecuteScalar();
//ClearCommandParams();
//return (T)v;
if (v == DBNull.Value || v == null)
{
return(default(T));
}
else
{
return((T)v);
}
}
}
/// <summary>
/// 执行存储过程，判断是否有返回数据
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="parameters">查询参数</param>
/// <returns></returns>
public bool HasRowSP(string StoredProcedureName, IDbParms parameters)
{
return(HasRow(CommandType.StoredProcedure, StoredProcedureName, parameters));
}
/// <summary>
/// 执行存储过程，返回DataSet结构
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="parameters">查询参数</param>
/// <returns></returns>
public DataSet GetDataSetSP(string StoredProcedureName, IDbParms parameters)
{
return(GetDataSet(CommandType.StoredProcedure, StoredProcedureName, parameters));
}
/// <summary>
/// 执行存储过程，返回DataTable结构
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="TableName">表名</param>
/// <param name="parameters">查询参数</param>
/// <returns></returns>
public DataTable GetTableSP(string StoredProcedureName, string TableName, IDbParms parameters)
{
return(GetTable(CommandType.StoredProcedure, TableName, StoredProcedureName, parameters));
}
/// <summary>
/// 执行存储过程，返回第一行第一列
/// </summary>
/// <typeparam name="T">返回类型</typeparam>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="parameters">查询参数</param>
/// <returns></returns>
public T ExecuteScalarSP <T>(string StoredProcedureName, IDbParms parameters)
{
return(ExecuteScalar <T>(CommandType.StoredProcedure, StoredProcedureName, parameters));
}
/// <summary>
/// 执行存储过程，委托处理结果
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="parameters"></param>
/// <param name="action"></param>
public void ExecuteDataReaderSP(string StoredProcedureName, IDbParms parameters, Action <DbDataReader> action)
{
ExecuteDataReader(CommandType.StoredProcedure, StoredProcedureName, parameters, action);
}
/// <summary>
/// 执行存储过程，返回第一行数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="StoredProcedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public T ExecuteDataFirstSP <T>(string StoredProcedureName, IDbParms parameters) where T : new()
{
return(ExecuteDataFirst <T>(CommandType.StoredProcedure, StoredProcedureName, parameters));
}
/// <summary>
/// 执行存储过程，返回第一行数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="StoredProcedureName"></param>
/// <param name="parameters"></param>
/// <param name="action"></param>
/// <returns></returns>
public T ExecuteDataFirstSP <T>(string StoredProcedureName, IDbParms parameters, Func <DbDataReader, T> action)
{
return(ExecuteDataFirst <T>(CommandType.StoredProcedure, StoredProcedureName, parameters, action));
}
