2023年6月21日发(作者：)

SqlSever帮助类///

/// 数据库访问接⼝ /// internal 只有同⼀命名空间才能访问，防⽌逻辑层调⽤此接⼝ /// public class AgentDBHelper { /// /// 数据库连接字符串 /// internal static string connectionString = tionStrings["AgentDB"].ConnectionString; #region 构造函数 public AgentDBHelper() { } #endregion #region 执⾏简单SQL语句 /// /// 执⾏SQL语句（增删改），返回影响的记录数 /// /// SQL语句 /// 影响的记录数 internal static int ExecuteSql(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { (); int rows = eNonQuery(); return rows; } catch (SqlException e) { (); throw e; } finally { (); } } } } /// /// RunSql(SqlCommand,bool) 执⾏Sql语句 /// /// SqlCommand 对象 /// 影响⾏数 internal static int ExecuteSql(SqlCommand m_SqlCommand) { using (SqlConnection connection = new SqlConnection(connectionString)) { try { m_tion = connection; (); return m_eNonQuery(); } catch (SqlException e) { throw e; } finally { (); } } } /// /// 执⾏多条SQL语句，实现数据库事务。 /// /// 多条SQL语句

internal static int ExecuteSqlTran(List SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { (); SqlCommand cmd = new SqlCommand(); tion = conn; SqlTransaction tx = ransaction(); ction = tx; try { int count = 0; for (int n = 0; n < ; n++) { string strsql = SQLStringList[n]; if (().Length > 1) { dText = strsql; count += eNonQuery(); } } (); return count; } catch { ck(); return 0; } finally { (); } } } ///

/// 执⾏⼀条语句，返回查询结果（object）。 /// /// 计算查询结果语句 /// 查询结果（object） internal static object GetSingle(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { (); object obj = eScalar(); if (((obj, null)) || ((obj, ))) { return null; } else { return obj; } } catch (SqlException e) { throw e; } finally { (); } } } } /// /// 执⾏⼀条语句，返回⼀个值(第⼀⾏第⼀列) /// /// SqlCommand 对象 /// object对象 internal static object GetSingle(SqlCommand m_SqlCommand) { SqlConnection conn = new SqlConnection(connectionString); try { (); m_tion = conn; return m_eScalar(); } catch (Exception exE) { (); throw new Exception("执⾏GetSingleItem错误，请检查Sql语句。n" + m_dText +e + e); } finally { (); } } /// /// 根据查询语句获取结果 /// /// 查询语句 /// DataTable名 /// internal static DataTable GetDataTable(string m_strSqlString, string m_strTableName) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dt = new DataTable(m_strTableName); try { (); SqlDataAdapter command = new SqlDataAdapter(m_strSqlString, connection); (dt); return dt; } catch (SqlException ex) { throw new Exception(e); } finally { (); } } } /// /// 根据查询语句获取结果 /// /// 查询语句 /// 返回DataTable internal static DataTable GetDataTable(string m_strSqlString) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dt = new DataTable(); try { (); SqlDataAdapter command = new SqlDataAdapter(m_strSqlString, connection); (dt); return dt; } catch (SqlException ex) { throw new Exception(e); } finally { (); } } } /// /// 根据查询语句获取结果(带参数查询) /// /// SqlCommand 对象 /// DataTable表名 /// DataTable对象 internal static DataTable GetDataTable(SqlCommand m_SqlCommand) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dt = new DataTable(); try { (); m_tion = connection; SqlDataAdapter command = new SqlDataAdapter(m_SqlCommand); (dt); return dt; } catch (SqlException ex) { throw new Exception(e); } finally { (); } } } #endregion

#region 存储过程操作 ///

/// 执⾏存储过程 /// /// 存储过程名 /// 存储过程参数 /// DataSet internal static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); (); SqlDataAdapter sqlDA = new SqlDataAdapter { SelectCommand = BuildQueryCommand(connection, storedProcName, parameters) }; (dataSet); (); return dataSet; } } /// /// 执⾏存储过程 /// /// 存储过程名 /// 存储过程参数 /// DataTable internal static DataTable RunProcedureDataTable(string storedProcName, IDataParameter[] parameters) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dataSet = new DataTable(); (); SqlDataAdapter sqlDA = new SqlDataAdapter(); Command = BuildQueryCommand(connection, storedProcName, parameters); (dataSet); (); return dataSet; } } /// /// 执⾏存储过程获取单个数据 /// /// 存储过程名 /// 存储过程参数 /// DataTable public static object RunProcedureSingle(string storedProcName, IDataParameter[] parameters) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); (); DataTable dt = new DataTable(); SqlDataAdapter sqlDA = new SqlDataAdapter(); Command = BuildQueryCommand(connection, storedProcName, parameters); (dt); (); if (dt != null && > 0) { return [0][0]; } else { return null; } } } /// /// 构建 SqlCommand 对象(⽤来返回⼀个结果集，⽽不是⼀个整数值) /// /// 数据库连接 /// 存储过程名 /// 存储过程参数 /// SqlCommand internal static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[]parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); dType = Procedure; foreach (SqlParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以. if ((ion == utput || ion ==) && ( == null)) { = ; } (parameter); } } return command; } /// /// 执⾏存储过程，返回影响的⾏数

///

/// 存储过程名 /// 存储过程参数 /// 影响的⾏数 /// result internal static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (SqlConnection connection = new SqlConnection(connectionString)) { int result; (); SqlCommand command = BuildIntCommand(connection, storedProcName, parameters); rowsAffected = eNonQuery(); result = (int)ters["ReturnValue"].Value; (); return result; } } /// /// 创建 SqlCommand 对象实例(⽤来返回⼀个整数值)

///

/// 存储过程名 /// 存储过程参数 /// SqlCommand 对象实例 internal static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[]parameters) { SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); (new SqlParameter("ReturnValue", , 4, Value,false, 0, 0, , t, null)); return command; } #endregion }

2023年6月21日发(作者：)

SqlSever帮助类///

/// 数据库访问接⼝ /// internal 只有同⼀命名空间才能访问，防⽌逻辑层调⽤此接⼝ /// public class AgentDBHelper { /// /// 数据库连接字符串 /// internal static string connectionString = tionStrings["AgentDB"].ConnectionString; #region 构造函数 public AgentDBHelper() { } #endregion #region 执⾏简单SQL语句 /// /// 执⾏SQL语句（增删改），返回影响的记录数 /// /// SQL语句 /// 影响的记录数 internal static int ExecuteSql(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { (); int rows = eNonQuery(); return rows; } catch (SqlException e) { (); throw e; } finally { (); } } } } /// /// RunSql(SqlCommand,bool) 执⾏Sql语句 /// /// SqlCommand 对象 /// 影响⾏数 internal static int ExecuteSql(SqlCommand m_SqlCommand) { using (SqlConnection connection = new SqlConnection(connectionString)) { try { m_tion = connection; (); return m_eNonQuery(); } catch (SqlException e) { throw e; } finally { (); } } } /// /// 执⾏多条SQL语句，实现数据库事务。 /// /// 多条SQL语句

internal static int ExecuteSqlTran(List SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { (); SqlCommand cmd = new SqlCommand(); tion = conn; SqlTransaction tx = ransaction(); ction = tx; try { int count = 0; for (int n = 0; n < ; n++) { string strsql = SQLStringList[n]; if (().Length > 1) { dText = strsql; count += eNonQuery(); } } (); return count; } catch { ck(); return 0; } finally { (); } } } ///

/// 执⾏⼀条语句，返回查询结果（object）。 /// /// 计算查询结果语句 /// 查询结果（object） internal static object GetSingle(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { (); object obj = eScalar(); if (((obj, null)) || ((obj, ))) { return null; } else { return obj; } } catch (SqlException e) { throw e; } finally { (); } } } } /// /// 执⾏⼀条语句，返回⼀个值(第⼀⾏第⼀列) /// /// SqlCommand 对象 /// object对象 internal static object GetSingle(SqlCommand m_SqlCommand) { SqlConnection conn = new SqlConnection(connectionString); try { (); m_tion = conn; return m_eScalar(); } catch (Exception exE) { (); throw new Exception("执⾏GetSingleItem错误，请检查Sql语句。n" + m_dText +e + e); } finally { (); } } /// /// 根据查询语句获取结果 /// /// 查询语句 /// DataTable名 /// internal static DataTable GetDataTable(string m_strSqlString, string m_strTableName) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dt = new DataTable(m_strTableName); try { (); SqlDataAdapter command = new SqlDataAdapter(m_strSqlString, connection); (dt); return dt; } catch (SqlException ex) { throw new Exception(e); } finally { (); } } } /// /// 根据查询语句获取结果 /// /// 查询语句 /// 返回DataTable internal static DataTable GetDataTable(string m_strSqlString) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dt = new DataTable(); try { (); SqlDataAdapter command = new SqlDataAdapter(m_strSqlString, connection); (dt); return dt; } catch (SqlException ex) { throw new Exception(e); } finally { (); } } } /// /// 根据查询语句获取结果(带参数查询) /// /// SqlCommand 对象 /// DataTable表名 /// DataTable对象 internal static DataTable GetDataTable(SqlCommand m_SqlCommand) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dt = new DataTable(); try { (); m_tion = connection; SqlDataAdapter command = new SqlDataAdapter(m_SqlCommand); (dt); return dt; } catch (SqlException ex) { throw new Exception(e); } finally { (); } } } #endregion

#region 存储过程操作 ///

/// 执⾏存储过程 /// /// 存储过程名 /// 存储过程参数 /// DataSet internal static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); (); SqlDataAdapter sqlDA = new SqlDataAdapter { SelectCommand = BuildQueryCommand(connection, storedProcName, parameters) }; (dataSet); (); return dataSet; } } /// /// 执⾏存储过程 /// /// 存储过程名 /// 存储过程参数 /// DataTable internal static DataTable RunProcedureDataTable(string storedProcName, IDataParameter[] parameters) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dataSet = new DataTable(); (); SqlDataAdapter sqlDA = new SqlDataAdapter(); Command = BuildQueryCommand(connection, storedProcName, parameters); (dataSet); (); return dataSet; } } /// /// 执⾏存储过程获取单个数据 /// /// 存储过程名 /// 存储过程参数 /// DataTable public static object RunProcedureSingle(string storedProcName, IDataParameter[] parameters) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); (); DataTable dt = new DataTable(); SqlDataAdapter sqlDA = new SqlDataAdapter(); Command = BuildQueryCommand(connection, storedProcName, parameters); (dt); (); if (dt != null && > 0) { return [0][0]; } else { return null; } } } /// /// 构建 SqlCommand 对象(⽤来返回⼀个结果集，⽽不是⼀个整数值) /// /// 数据库连接 /// 存储过程名 /// 存储过程参数 /// SqlCommand internal static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[]parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); dType = Procedure; foreach (SqlParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以. if ((ion == utput || ion ==) && ( == null)) { = ; } (parameter); } } return command; } /// /// 执⾏存储过程，返回影响的⾏数

///

/// 存储过程名 /// 存储过程参数 /// 影响的⾏数 /// result internal static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (SqlConnection connection = new SqlConnection(connectionString)) { int result; (); SqlCommand command = BuildIntCommand(connection, storedProcName, parameters); rowsAffected = eNonQuery(); result = (int)ters["ReturnValue"].Value; (); return result; } } /// /// 创建 SqlCommand 对象实例(⽤来返回⼀个整数值)

///

/// 存储过程名 /// 存储过程参数 /// SqlCommand 对象实例 internal static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[]parameters) { SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); (new SqlParameter("ReturnValue", , 4, Value,false, 0, 0, , t, null)); return command; } #endregion }