/// <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); }