/// <summary>
/// 执行 SQL 语句，并返回受影响的行数。
/// </summary>
/// <param name="isOpenTrans">事务对象</param>
/// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
/// <param name="cmdText">存储过程名称或者T-SQL命令行</param>
/// <param name="parameters">执行命令所需的sql语句对应参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(DbTransaction isOpenTrans, CommandType cmdType, string cmdText, params DbParameter[] parameters)
{
int num = 0;
try
{
DbCommand cmd = DbFactory.CreateDbCommand();
if (cmdText.Contains("Base_Subsystem_Function"))
{
//子系统功能点
using (DbConnection conn = DbFactory.CreateDbConnection(ConnectionString))
{
PrepareCommand(cmd, conn, isOpenTrans, cmdType, cmdText, parameters);
num = cmd.ExecuteNonQuery();
}
}
else
{
if (isOpenTrans == null || isOpenTrans.Connection == null)
{
using (DbConnection conn = DbFactory.CreateDbConnection(ConnectionString))
{
PrepareCommand(cmd, conn, isOpenTrans, cmdType, cmdText, parameters);
num = cmd.ExecuteNonQuery();
}
}
else
{
PrepareCommand(cmd, isOpenTrans.Connection, isOpenTrans, cmdType, cmdText, parameters);
num = cmd.ExecuteNonQuery();
}
}
cmd.Parameters.Clear();
//Test Log4net
//LogMessage msg = new LogMessage();
//msg.CreatedUserId = "leike";
//msg.LogLevel = LogLevelEnum.INFO;
//msg.IPAddress = "127.0.0.1";
//msg.LogDesc = "LogDesc";
//msg.LogSql = cmdText;
//msg.LogType = "Database Update 操作";
//msg.LogType = "LogType";
//msg.Remark = "Remark";
//msg.SubSystem = SubSystemEnum.系统管理子系统;
//msg.SubSystemModuleName = "SubSystemModuleName";
//log.Info(msg);
//log.Info(cmdText);
}
catch (Exception ex)
{
num = -1;
//log.Error(ex.Message);
}
return(num);
}
/// <summary>
/// 依靠数据库连接字符串connectionString,
/// 使用所提供参数，执行返回首行首列命令
/// </summary>
/// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>
/// <param name="commandText">存储过程名称或者T-SQL命令行</param>
/// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>
public static object ExecuteScalar(CommandType cmdType, string cmdText)
{
try
{
DbCommand cmd = DbFactory.CreateDbCommand();
using (DbConnection connection = DbFactory.CreateDbConnection(ConnectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, null);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return(val);
}
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 执行 SQL 语句，并返回受影响的行数。
/// </summary>
/// <param name="isOpenTrans">事务对象</param>
/// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
/// <param name="cmdText">存储过程名称或者T-SQL命令行</param>
/// <param name="parameters">执行命令所需的sql语句对应参数</param>
/// <returns></returns>
public static async Task <int> ExecuteNonQuery(DbTransaction isOpenTrans, CommandType cmdType, string cmdText, params DbParameter[] parameters)
{
int num = 0;
try
{
DbCommand cmd = DbFactory.CreateDbCommand();
if (cmdText.Contains("Base_Subsystem_Function"))
{
//子系统功能点
using (DbConnection conn = DbFactory.CreateDbConnection(ConnectionString))
{
await PrepareCommand(cmd, conn, isOpenTrans, cmdType, cmdText, parameters);
num = await cmd.ExecuteNonQueryAsync();
}
}
else
{
if (isOpenTrans == null || isOpenTrans.Connection == null)
{
using (DbConnection conn = DbFactory.CreateDbConnection(ConnectionString))
{
await PrepareCommand(cmd, conn, isOpenTrans, cmdType, cmdText, parameters);
num = await cmd.ExecuteNonQueryAsync();
}
}
else
{
await PrepareCommand(cmd, isOpenTrans.Connection, isOpenTrans, cmdType, cmdText, parameters);
num = await cmd.ExecuteNonQueryAsync();
}
}
cmd.Parameters.Clear();
}
catch (Exception ex)
{
num = -1;
}
return(num);
}
/// <summary>
/// 依靠数据库连接字符串connectionString,
/// 使用所提供参数，执行返回首行首列命令
/// </summary>
/// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>
/// <param name="commandText">存储过程名称或者T-SQL命令行</param>
/// <param name="parameters">执行命令所需的sql语句对应参数</param>
/// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>
public static async Task <object> ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] parameters)
{
try
{
DbCommand cmd = DbFactory.CreateDbCommand();
using (DbConnection connection = DbFactory.CreateDbConnection(ConnectionString))
{
await PrepareCommand(cmd, connection, null, cmdType, cmdText, parameters);
object val = await cmd.ExecuteScalarAsync();
cmd.Parameters.Clear();
return(val);
}
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
///使用提供的参数，执行有结果集返回的数据库操作命令、并返回SqlDataReader对象
/// </summary>
/// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>
/// <param name="commandText">存储过程名称或者T-SQL命令行<</param>
/// <returns>返回SqlDataReader对象</returns>
public static IDataReader ExecuteReader(CommandType cmdType, string cmdText)
{
DbCommand cmd = DbFactory.CreateDbCommand();
DbConnection conn = DbFactory.CreateDbConnection(ConnectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, null);
IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return(rdr);
}
catch (Exception ex)
{
conn.Close();
cmd.Dispose();
//log.Error(ex.Message);
throw;
}
}
/// <summary>
///使用提供的参数，执行有结果集返回的数据库操作命令、并返回SqlDataReader对象
/// </summary>
/// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>
/// <param name="commandText">存储过程名称或者T-SQL命令行<</param>
/// <returns>返回SqlDataReader对象</returns>
public static async Task <DbDataReader> ExecuteReader(CommandType cmdType, string cmdText)
{
DbCommand cmd = DbFactory.CreateDbCommand();
DbConnection conn = DbFactory.CreateDbConnection(ConnectionString);
try
{
await PrepareCommand(cmd, conn, null, cmdType, cmdText, null);
DbDataReader rdr = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return(rdr);
}
catch (Exception ex)
{
conn.Close();
cmd.Dispose();
throw;
}
}
/// <summary>
/// 执行 SQL 语句，并返回受影响的行数。
/// </summary>
/// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
/// <param name="cmdText">存储过程名称或者T-SQL命令行</param>
/// <returns></returns>
public static int ExecuteNonQuery(CommandType cmdType, string cmdText)
{
int num = 0;
try
{
DbCommand cmd = DbFactory.CreateDbCommand();
using (DbConnection conn = DbFactory.CreateDbConnection(ConnectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, null);
num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
catch (Exception ex)
{
num = -1;
//log.Error(ex.Message);
}
return(num);
}
/// <summary>
/// 查询数据填充到数据集DataSet中
/// </summary>
/// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
/// <param name="cmdText">命令文本</param>
/// <returns>数据集DataSet对象</returns>
public static DataSet GetDataSet(CommandType cmdType, string cmdText)
{
DataSet ds = new DataSet();
DbCommand cmd = DbFactory.CreateDbCommand();
DbConnection conn = DbFactory.CreateDbConnection(ConnectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, null);
IDbDataAdapter sda = DbFactory.CreateDataAdapter(cmd);
sda.Fill(ds);
return(ds);
}
catch (Exception ex)
{
conn.Close();
cmd.Dispose();
//log.Error(ex.Message);
throw;
}
}
/// <summary>
/// 执行 SQL 语句，并返回受影响的行数。
/// </summary>
/// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
/// <param name="cmdText">存储过程名称或者T-SQL命令行</param>
/// <param name="parameters">执行命令所需的sql语句对应参数</param>
/// <returns></returns>
public static async Task <int> ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] parameters)
{
int num = 0;
try
{
DbCommand cmd = DbFactory.CreateDbCommand();
using (DbConnection conn = DbFactory.CreateDbConnection(ConnectionString))
{
await PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters);
num = await cmd.ExecuteNonQueryAsync();
cmd.Parameters.Clear();
}
}
catch (Exception ex)
{
num = -1;
}
return(num);
}