/// <summary>
/// IDataReader //这里先不用using 调用转换时使用了using 在数据没读到之前数据库连接不能关闭。
/// </summary>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static IDataReader ExecuteDataReader(IDbConnection dbConnection, string cmdText, params DbParameter[] parameters)
{
try
{
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
using (IDbCommand cmd = dbConnection.CreateCommand())
{
cmd.CommandText = cmdText;
if (parameters != null)
{
parameters = DbParameters.ToDbParameter(parameters);
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//DataReader关闭时dbConnection也会关闭
cmd.Parameters.Clear();
return(dr);
}
}
catch (DbException ex)
{
DbLog.WriteLog(ex, cmdText);
if (dbConnection != null)
{
dbConnection.Close();
}
throw ex;
//return null;
}
}
/// <summary>
/// 执行带事务的SQL语句，并返回受影响的行数（但未真正提交）
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteNonQueryTran(IDbTransaction dbTransaction, string cmdText, params DbParameter[] parameters)
{
try
{
using (IDbCommand cmd = dbTransaction.Connection.CreateCommand())
{
cmd.Transaction = dbTransaction;
cmd.CommandText = cmdText;
if (parameters != null)
{
parameters = DbParameters.ToDbParameter(parameters);
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
return(cmd.ExecuteNonQuery());
}
}
catch (DbException e)
{
DbLog.WriteLog(e, cmdText);
throw e;
}
}
/// <summary>
/// 执行存储过程 存储过程必须带Out的Out_Res参数
/// </summary>
/// <param name="dbConnection">连接</param>
/// <param name="storedProcName">存储过程名称</param>
/// <param name="parameters">入参数</param>
/// <returns>Out_Res为Out参数的执行结果</returns>
public static object ExecuteByProc(IDbConnection dbConnection, string storedProcName, DbParameter[] parameters, out int rowsAffected)
{
try
{
using (IDbConnection conn = dbConnection)
{
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcName;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (parameters != null)
{
parameters = DbParameters.ToDbParameter(parameters);
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
cmd.Parameters.Add(DbParameters.CreateDbParameter("@Out_Res", "", DbType.String, 2000, ParameterDirection.Output));
rowsAffected = cmd.ExecuteNonQuery();
DbParameter obj = (DbParameter)cmd.Parameters["@Out_Res"]; //@Out_Res和具体的存储过程参数对应
cmd.Parameters.Clear();
return(Convert.ToString(obj.Value));
}
}
}
catch (DbException ex)
{
DbLog.WriteLog(ex, storedProcName);
if (dbConnection != null)
{
dbConnection.Close();
}
throw ex;
}
}
/// <summary>
/// 为即将执行准备一个命令
/// </summary>
/// <param name="conn">SqlConnection对象</param>
/// <param name="cmd">SqlCommand对象</param>
/// <param name="isOpenTrans">DbTransaction对象</param>
/// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
/// <param name="cmdText">存储过程名称或者T-SQL命令行, e.g. Select * from Products</param>
/// <param name="dbParameter">执行命令所需的sql语句对应参数</param>
private void PrepareCommand(IDbConnection conn, IDbCommand cmd, DbTransaction isOpenTrans, string cmdText, params DbParameter[] dbParameter)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (isOpenTrans != null)
{
cmd.Transaction = isOpenTrans;
}
//cmd.CommandType = cmdType;
if (dbParameter != null)
{
dbParameter = DbParameters.ToDbParameter(dbParameter);
foreach (var parameter in dbParameter)
{
cmd.Parameters.Add(parameter);
}
}
}
/// <summary>
/// 返回首行首列
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static object ExecuteScalar(IDbConnection dbConnection, string cmdText, params DbParameter[] parameters)
{
try
{
using (IDbConnection conn = dbConnection)
{
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (parameters != null)
{
parameters = DbParameters.ToDbParameter(parameters);
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return(obj);
}
}
}
catch (DbException ex)
{
DbLog.WriteLog(ex, cmdText);
if (dbConnection != null)
{
dbConnection.Close();
}
throw ex;
//return null;
}
}
/// <summary>
/// 执行 SQL 语句，并返回受影响的行数
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteNonQuery(IDbConnection dbConnection, string cmdText, params DbParameter[] parameters)
{
try
{
using (var conn = dbConnection)
{
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (parameters != null)
{
parameters = DbParameters.ToDbParameter(parameters);
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
int result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return(result);
}
}
}
catch (DbException e)
{
DbLog.WriteLog(e, cmdText);
if (dbConnection != null)
{
dbConnection.Close();
}
throw e;
//return -1;
}
}
public static DataTable ExecuteDataTableByProc(IDbConnection dbConnection, string storedProcName, params DbParameter[] parameters)
{
try
{
DataTable dt = new DataTable();
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
using (IDbCommand cmd = dbConnection.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcName;
if (parameters != null)
{
parameters = DbParameters.ToDbParameter(parameters);
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//DataReader关闭时dbConnection也会关闭
cmd.Parameters.Clear();
return(ConvertExtension.IDataReaderToDataTable(dr));
}
}
catch (DbException ex)
{
DbLog.WriteLog(ex, storedProcName);
if (dbConnection != null)
{
dbConnection.Close();
}
throw ex;
}
}
