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

C#数据库操作通⽤⽅法类平时使⽤的数据库操作类整理更新后备份，记录下来以供以后使⽤，并顺便分享给⼤家⼀起交流。直接上源码：整个底层操作⽅法分为4个类，基础⽅法类：SqlBase 基础⼯具类：SqlTool ⽇志类：DbLog 和MSSQL操作类：MsSqlHelper。由于平时⼯作未⽤到其他类型数据库，因此未整理其他数据库的操作类，以后⽤到的话会进⾏更新。⾸先是通⽤的数据库底层操作⽅法整理：///

/// 数据库操作基础⽅法类 /// public class SqlBase { #region 字段与属性声明 private static int _cmdTimeOut = 30; /// /// 等待命令执⾏的时间（以秒为单位） /// public static int CMDTimeOut { get { return _cmdTimeOut; } set { _cmdTimeOut = value; } } private static string _defaultDb = "conn"; /// /// 默认数据库连接配置名称 /// public static string DefaultDb { get { return _defaultDb; } set { _defaultDb = value; } } /// /// 默认数据集名称 /// private static string _defaultDataSet = "ds"; /// /// 默认数据表名称 /// private static string _defaultDataTable = "dt"; /// /// 数据库连接失败返回信息 /// private static string _linkErrMsg = "数据库连接失败"; /// /// 存储过程返回值参数名 /// private static string _returnParaName = "ReturnValue"; #endregion /// /// 默认数据库连接字符串 /// public static string _connStr = ; /// /// 获取数据库连接 /// /// 数据库简写 public static string GetConnectionString(string useDb) { switch (useDb) { case "conn": return _connStr; default: useDb = DefaultDb; break; } return tings[useDb].ToString(); } /// /// 获取数据库连接 /// /// 数据库简写 private static SqlConnection GetConnection(string useDb) { if (OrEmpty(useDb)) useDb = DefaultDb; var connStr = GetConnectionString(useDb); if (!OrEmpty(connStr)) { return new SqlConnection(connStr); } return null; } /// /// 执⾏数据库操作 /// /// 数据库执⾏语句 /// 数据库简写 /// 受到影响的⾏数 public static int ExecuteNonQuery(string sql, string useDb = "") { return ExecuteNonQuery(sql, null, useDb); } /// /// 执⾏数据库操作 /// /// 数据库执⾏语句 /// 参数集 /// 数据库简写 /// 受影响的⾏数 public static int ExecuteNonQuery(string sql, SqlParameter[] paras, string useDb = "") { SqlConnection conn = GetConnection(useDb); try { if (conn == null) throw new Exception(_linkErrMsg); (); var cmd = new SqlCommand(sql, conn); dTimeout = CMDTimeOut; (); if (paras != null) { ge(paras); } return eNonQuery(); } catch (eption e) { throw e; } finally { if (conn != null) (); } } /// /// 执⾏SQL语句并返回受影响的⾏数,带事务操作 /// /// 事务 /// 数据库执⾏语句 /// 参数集 /// public static int ExecuteNonQuery(SqlTransaction trans, string sql, SqlParameter[] paras = null) { try { if ( == ) { (); } SqlCommand command = new SqlCommand(sql, tion); dTimeout = CMDTimeOut; ction = trans; (); if (paras != null) { ge(paras); } return eNonQuery(); } catch (Exception e) { throw e; } finally { (); } } /// /// 执⾏SQL语句并返回受影响的⾏数,带事务操作(只针对sqlserver) /// /// /// /// public static int ExecuteNonQueryWithTransation(string sql, SqlParameter[] paras = null, string useDB = "") { SqlTransaction sqlTransaction = null; SqlConnection conn = GetConnection(useDB); try { if (conn == null) throw new Exception(_linkErrMsg); (); sqlTransaction = ransaction(); var cmd = new SqlCommand(sql, conn); dTimeout = CMDTimeOut; ction = sqlTransaction; (); if (paras != null) { ge(paras); } var ret = eNonQuery(); (); return ret; } catch (eption e) { ck(); throw e; } finally { if (conn != null) (); } } /// /// 执⾏查询，并返回查询所返回的结果集中第⼀⾏的第⼀列。忽略其他列或⾏。 /// /// 数据库执⾏语句 /// 参数集 /// 数据库简写 /// 返回查询所返回的结果集中第⼀⾏的第⼀列或空引⽤（如果结果集为空).忽略其他列或⾏ public static object ExecuteScalar(string sql, string useDb = "") { return ExecuteScalar(sql, null, useDb); } /// /// 执⾏查询，并返回查询所返回的结果集中第⼀⾏的第⼀列。忽略其他列或⾏。 /// /// 计算查询结果语句 /// 查询结果（object） public static object ExecuteScalar(string sql, SqlParameter[] paras, string useDB = "") { SqlConnection conn = GetConnection(useDB); try { if (conn == null) throw new Exception(_linkErrMsg); (); var cmd = new SqlCommand(sql, conn); dTimeout = CMDTimeOut; (); if (paras != null) { ge(paras); } var obj = eScalar(); if ((obj, null) || (obj, )) { return null; } else { return obj; } } catch (eption e) { throw e; } finally { if (conn != null) (); } } /// /// 执⾏查询，并返回查询所返回的结果集中第⼀⾏的第⼀列。忽略其他列或⾏。 /// /// 事务 /// 数据库执⾏语句 /// 参数集 /// public static object ExecuteScalar(SqlTransaction trans, string sql, SqlParameter[] cmdParms = null) { try { if ( == ) { (); } SqlCommand cmd = new SqlCommand(sql, tion); dTimeout = CMDTimeOut; ction = trans; (); if (cmdParms != null) { ge(cmdParms); } var obj = eScalar(); if ((obj, null) || (obj, )) { return null; } else { return obj; } } catch (Exception e) { throw e; } finally { (); } } /// /// 执⾏查询语句，返回DataSet /// /// 查询语句 /// 数据库简写 /// 数据集 public static DataSet Query(string sql, string useDb = "") { var ds = new DataSet(); SqlConnection conn = GetConnection(useDb); try { if (conn == null) throw new Exception(_linkErrMsg); (); SqlCommand cmd = new SqlCommand(sql, conn); dTimeout = CMDTimeOut; var command = new SqlDataAdapter(cmd); (ds, _defaultDataSet); } catch (eption e) { throw e; } finally { if (conn != null) (); } return ds; } /// /// 执⾏查询语句，返回DataSet(带参数化) /// /// sql语句 /// 参数 /// 数据库简写 /// 数据集 public static DataSet QueryWithParams(string sql, SqlParameter[] cmdParms = null, string useDb = "") { var ds = new DataSet(); SqlConnection conn = GetConnection(useDb); try { if (conn == null) throw new Exception(_linkErrMsg); (); SqlCommand cmd = new SqlCommand(sql, conn); dTimeout = CMDTimeOut; (); if (cmdParms != null) { ge(cmdParms); } var command = new SqlDataAdapter(cmd); (ds, _defaultDataSet); } catch (eption e) { throw e; } finally { if (conn != null) (); } return ds; } #region 存储过程⽅法

///

/// 执⾏存储过程 /// /// 存储过程名 /// 存储过程参数 /// 数据库简写 /// 超时时间 /// DataSet public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string useDB = "") { SqlConnection conn = GetConnection(useDB); try { if (conn == null) throw new Exception(_linkErrMsg); (); DataSet dataSet = new DataSet(); SqlDataAdapter sqlDA = new SqlDataAdapter(); Command = BuildQueryCommand(conn, storedProcName, parameters); dTimeout = CMDTimeOut; (dataSet, _defaultDataTable); return dataSet; } finally { if (conn != null) (); } } /// /// 构建 SqlCommand 对象(⽤来返回⼀个结果集，⽽不是⼀个整数值) /// /// 数据库连接 /// 存储过程名 /// 存储过程参数 /// SqlCommand private 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; } /// /// 执⾏存储过程，返回请求结果

///

/// 存储过程名 /// 存储过程参数 /// 影响的⾏数 /// 请求结果 public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected, string useDB = "") { SqlConnection conn = GetConnection(useDB); try { rowsAffected = 0; if (conn == null) throw new Exception(_linkErrMsg); (); int result; SqlCommand command = BuildIntCommand(conn, storedProcName, parameters); rowsAffected = eNonQuery(); result = (int)ters[_returnParaName].Value; return result; } finally { if (conn != null) (); } } /// /// 创建 SqlCommand 对象实例(⽤来返回⼀个整数值)

///

/// 存储过程名 /// 存储过程参数 /// SqlCommand 对象实例 private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); (new SqlParameter(_returnParaName, , 4, Value, false, 0, 0, , t, null)); return command; } #endregion }其次，常⽤的数据库底层操作⼯具类整理：/// /// 数据库操作⼯具⽅法类 /// public class SqlTool { /// /// string扩展⽅法(忽略⼤⼩写⽐较字符串) /// /// 源字符串 /// ⽬标字符串 /// public static bool Contains(string source, string value, StringComparison comparisonType = lIgnoreCase) { return f(value, comparisonType) >= 0; } #region 校验⽅法 /// /// 验证sql匹配条件是否正确(若以and开头则⾃动去除) /// /// sql匹配条件 public static string CheckWhere(string where) { if (!OrWhiteSpace(where)) { var str = art();//去除前置空格 if (f("and ", lIgnoreCase) == 0)//若以and开头则⾃动去除第⼀个and { where = ing(4);//若要保留前⾯⼀个空格，可以改为3 } //where = filterSql(where);//防SQL注⼊ if (f("order by", lIgnoreCase) == 0) { where = " " + where; } else if (!OrWhiteSpace(where)) { where = " where " + where; } } return where; } /// /// 表名安全校验 /// /// 数据表名 public static string CheckTbName(string tbName) { //if (ns(" ") && !Contains(tbName, " join ")) //⾮连接语句 //{ // tbName = e(" ", "");//防⽌SQL注⼊ //} return tbName; } /// /// 字段安全校验 /// /// 字段集合 public static string CheckFields(string fields) { //var str = r(); //if (ns(" ") && !ns(",case ") && f("distinct ") < 0) //{ // str = e(" as ", "#"); // fields = e(" ", "").Replace("#", " as "); ;//防⽌SQL注⼊ //} return fields; } /// /// 过滤SQL语句,防⽌注⼊ /// /// Sql语句 public static string filterSql(string strSql) { var str = r().Trim(); str = e("exec", ""); str = e("delete", ""); str = e("master", ""); str = e("truncate", ""); str = e("declare", ""); str = e("create", ""); str = e("xp_", "no"); return str; } #endregion #region 实体赋值⽅法 /// /// 类型转换字典 /// private static Dictionary TypeDic = new Dictionary{{typeof(bool?),typeof(bool)},{typeof(int?),typeof(int)}, {typeof(long?),typeof(long)},{typeof(decimal?),typeof(decimal)},{typeof(DateTime?),typeof(DateTime)} }; /// /// 获取标准类型 /// public static Type GetStandardType(Type t) { if (nsKey(t)) { t = TypeDic[t]; } return t; } /// /// 根据读取到的数据为实体实例赋值 /// /// 实体 /// 读取到的⼀⾏数据 /// 数据列名集合 /// 实体属性集合 /// 实体实例 /// 赋值后的实体实例 public static T GetTValue(IDataReader dataReader, List columns, tyInfo[] fields, T info) where T : class { try { var cols = new List(); ge(columns); foreach (var p in fields)//为实体实例赋值 { if ( < 1) break; var key = r(); var idx = f(key); if (idx >= 0) { At(idx); var ovalue = dataReader[]; if (ovalue == null || ovalue == ) continue; var ptype = GetStandardType(tyType); ue(info, Type(ovalue, ptype), null); } } } catch (Exception ex) { ("GetTValue⽅法异常:" + e); } return info; } /// /// 根据读取到的数据为实体实例赋值 /// /// 实体 /// 数据⾏ /// 数据列名集合 /// 实体属性集合 /// 实体实例 /// 赋值后的实体实例 public static T GetTValue(DataRow row, List columns, tyInfo[] fields, T info) where T : class { try { var cols = new List(); ge(columns); foreach (var p in fields)//为实体实例赋值 { if ( < 1) break; var key = r(); var idx = f(key); if (idx >= 0) { At(idx); var ovalue = row[]; if (ovalue == null || ovalue == ) continue; var ptype = GetStandardType(tyType); ue(info, Type(ovalue, ptype), null); } } } catch (Exception ex) { ("GetTValue1⽅法异常:" + e); } return info; } #endregion #region 实例⼯具⽅法 /// /// 对象实例缓存 /// private static rentDictionary TCache = new rentDictionary(); /// /// 缓存操作锁 /// private static object lockCache = new object(); /// /// 实体克隆⽅法 /// private static MethodInfo wiseClone = typeof(object).GetMethod("MemberwiseClone", lic | ce); /// /// 实体克隆委托⽅法 /// public static Func doClone = Delegate(typeof(Func), wiseClone) as Func; /// /// 获取对象实例 /// /// 对象类型 public static object GetInstance(Type type) { object obj; if (!Value(, out obj)) { obj = Instance(type);//构建实例 lock (lockCache) { (, obj); } } return obj; } /// /// DataSet转实体集 /// /// 实体类 public static List DataSetToList(DataSet ds) where T : class { if (ds != null) { var dt = [0]; return DataTableToList(dt); } return new List(); } /// /// DataTable转实体集 /// /// 实体类 public static List DataTableToList(DataTable dt) where T : class { var list = new List(); if (dt != null && > 0) { var type = typeof(T); var fields = perties();//实体属性集合 var columns = new List();//数据列集合 int count = ; for (int i = 0; i < count; i++) { (s[i].r()); } var obj = GetInstance(type); foreach (DataRow row in ) { var val = GetTValue(row, columns, fields, doClone(obj) as T); (val); } } return list; } #endregion }SQL Server数据库操作帮助类/// /// SQL Server数据库操作帮助类 /// public class MsSqlHelper { /// /// 参数前缀 /// private static string PreParameter = "@"; #region ⼯具⽅法 // /// 获取数据插⼊SQL语句 /// 性能：13字段⼗万次约0.76s，49字段⼗万次约1.81s /// 实体 /// 实体实例 /// 数据表名(字段>=实体属性) /// SQL参数集 /// 第⼀个属性是否为⾃增主键 /// 数据插⼊SQL语句 private static string GetInsertSQL(T info, string tbName, out List sqlParams, bool identify = false) where T : class { var sql = ; sqlParams = new List();//返回值参数 if (OrWhiteSpace(tbName) || info == null) { return sql; } var type = typeof(T); var fields = perties();//获取实体成员字段 if ( < 2) //错误的数据实体 { return sql; } var fmt = "Insert into " + tbName + "({0}) values ({1});"; var cb = new StringBuilder(256);//列集合 var vb = new StringBuilder(512);//值集合 var link = ",";//字段连接符 var start = identify ? 1 : 0; var count = ; for (int i = start; i < count; i++) { var p = fields[i]; var v = ue(info, null); if (v == null || v == ) continue;//值为null不处理，使⽤数据库默认值 (link + ); (link + PreParameter + ); (new SqlParameter(PreParameter + , v)); } if ( > 0) //实体属性值不全为null { sql = (fmt, (0, 1).ToString(), (0, 1).ToString()); } return sql; } /// /// 获取数据更新SQL语句(要求实体第⼀个属性必须为主键) /// /// 实体 /// 实体实例 /// 数据表名(字段>=实体属性) /// SQL参数集 /// 数据更新SQL语句 private static string GetUpdateSQL(T info, string tbName, out List sqlParams) { var sql = ; sqlParams = new List();//返回值参数 if (OrWhiteSpace(tbName) || info == null) { return sql; } var type = typeof(T); var fields = perties();//获取实体成员字段 if ( < 2) //错误的数据实体 { return sql; } var cv = new StringBuilder(512);//列值对集合 var count = ; for (int i = 1; i < count; i++) { var p = fields[i]; var v = ue(info, null); if (v == null || v == ) continue;//值为null不处理，使⽤数据库默认值 ("," + + "=" + PreParameter + ); (new SqlParameter(PreParameter + , v)); } if ( > 0) //实体属性值不全为null { var sets = (0, 1).ToString(); (new SqlParameter(PreParameter + fields[0].Name, fields[0].GetValue(info, null))); sql = ("update {0} set {1} where " + fields[0].Name + "=" + PreParameter + fields[0].Name, tbName, sets); } return sql; } /// /// 获取数据更新(先删除后添加)SQL语句(要求实体第⼀个属性必须为主键) /// /// 实体 /// 实体实例 /// 数据表名(字段>=实体属性) /// SQL参数集 /// 第⼀个属性是否为⾃增主键 /// 数据更新SQL语句 private static string GetUpdateByDelAndAdd(T info, string tbName, out List sqlParams, bool identify = false) { var sql = ; sqlParams = new List();//返回值参数 if (OrWhiteSpace(tbName) || info == null) { return sql; } var type = typeof(T); var fields = perties();//获取实体成员字段 if ( < 2) //错误的数据实体 { return sql; } var sb = new StringBuilder(1024); ("delete from " + tbName + " where " + fields[0].Name + "=" + PreParameter + fields[0].Name); ("Insert into " + tbName + "({0}) values ({1});"); var cb = new StringBuilder(256);//列集合 var vb = new StringBuilder(512);//值集合 var link = ",";//字段连接符 var start = identify ? 1 : 0; var count = ; for (int i = start; i < count; i++) { var p = fields[i]; var v = ue(info, null); if (v == null || v == ) continue;//值为null不处理，使⽤数据库默认值 (link + ); (link + PreParameter + ); (new SqlParameter(PreParameter + , v)); } if ( > 0) //实体属性值不全为null { sql = (ng(), (0, 1).ToString(), (0, 1).ToString()); } return sql; } #endregion #region 取数据 /// /// 获取指定表中指定列的值 /// /// 列编码 /// 数据表名 /// 匹配条件 /// 数据库简写 /// 满⾜条件的第⼀⾏的值

public static string GetColumnValue(string ColumnCode, string tbName, string strWhere, string useDb = "") { var value = ; tbName = bName(tbName); ColumnCode = ields(ColumnCode); if (OrEmpty(ColumnCode) || OrEmpty(tbName)) { return value; } var strSql = ("select top 1 {0} from {1}", ColumnCode, tbName); strWhere = here(strWhere); if (!OrWhiteSpace(strWhere)) { strSql += strWhere; } try { var obj = eScalar(strSql, useDb); value = obj == null ? : ng(); } catch (Exception e) { (("GetColumnValue⽅法异常:{0},表_{1} 字段_{2}", e, tbName, ColumnCode)); } return value; } ///

/// 获取满⾜条件的第⼀⾏多列的值 /// /// 列编码集合 /// 数据表名 /// 匹配条件 /// 数据库简写 /// 满⾜条件的⾸⾏多列的值 public static List GetValues(List columns, string tbName, string strWhere, string useDb = "") { int step = 0; var value = new List(); tbName = bName(tbName); if ( < 1 || OrEmpty(tbName)) { return value; } step = 1; var cols = ields((",", columns)); var strSql = ("select top 1 {0} from {1}", cols, tbName); step = 2; strWhere = here(strWhere); if (!OrWhiteSpace(strWhere)) { strSql += strWhere; } try { step = 3; var ds = (strSql, useDb); if (ds != null && [0] != null && [0]. > 0) { var dr = [0].Rows[0]; var count = ; for (int i = 0; i < count; i++) { (dr[i] == null ? "" : dr[i].ToString()); } e(); } } catch (Exception e) { (("GetValues⽅法异常:{0} step:{1} 表_{2} 字段_{3}", e, step, tbName, cols)); } return value; } /// /// 获取指定表中记录总数 /// /// 数据表名 /// 匹配条件 /// 数据库简写 /// 满⾜条件的记录总数

public static int GetRecordCount(string tbName, string strWhere, string useDb = "") { int value = 0; tbName = bName(tbName); if (OrEmpty(tbName)) { return value; } var strSql = ("select count(1) from {0}", tbName); strWhere = here(strWhere); if (!OrWhiteSpace(strWhere)) { strSql += strWhere; } try { object obj = eScalar(strSql, useDb); value = obj == null ? 0 : 32(obj); } catch (Exception ex) { (("GetRecordCount⽅法异常:{0},表_{1}", e, tbName)); } return value; } ///

/// 获取数据集 /// /// 列集合，形如：col1,col2,...,coln /// 数据表名 /// 匹配条件 /// 获取记录最⼤数量,0为不限制 /// 数据库简写 /// 满⾜条件的数据集

public static DataSet GetList(string fields, string tbName, string strWhere, int top = 0, string useDb = "") { DataSet ds = null; tbName = bName(tbName); fields = ields(fields); if (OrEmpty(tbName)) { return ds; } var strSql = ("select {0} {1} from {2}", top < 1 ? "" : ("top {0}", top), OrEmpty(fields) ? "*" : fields, tbName); strWhere = here(strWhere); if (!OrWhiteSpace(strWhere)) { strSql += strWhere; } try { ds = (strSql, useDb); } catch (Exception ex) { (("GetList⽅法异常:{0},表_{1}", e, tbName)); } return ds; } ///

/// 分页获取数据集 /// /// 列集合，形如：col1,col2,...,coln /// 数据表名 /// 匹配条件 /// 排序字段 如：addtime desc /// 当前页号 /// 每页数据量 /// 数据库简写 /// 满⾜条件的数据集

public static DataSet GetListByPage(string fields, string tbName, string strWhere, string orderby, int pageSize, int pageIndex, string useDb = "") { DataSet ds = null; tbName = bName(tbName); fields = ields(fields); if (OrEmpty(fields) || OrEmpty(tbName) || OrEmpty(orderby)) { return ds; } if (pageSize < 1) pageSize = 10;//默认每页10条 if (pageIndex < 1) pageIndex = 1;//默认第⼀页 int start = (pageIndex - 1) * pageSize + 1; int end = pageIndex * pageSize; var strSql = new StringBuilder(512); ("select * from ("); (("select ROW_NUMBER() OVER (ORDER BY {0}) as row,{1} from {2}", orderby, fields, tbName)); strWhere = here(strWhere); if (!OrWhiteSpace(strWhere)) { (strWhere); } ((") as T where between {0} and {1}", start, end)); try { ds = (ng(), useDb); } catch (Exception ex) { (("GetListByPage⽅法异常:{0},表_{1}", e, tbName)); } return ds; } ///

/// 获取对象实体 /// /// 实体类 /// 数据表名 /// 匹配条件 /// 数据库简写 /// 数据实体 public static T GetModel(string tbName, string strWhere, string useDb = "") where T : class { return GetInfo(tbName, strWhere, "*", useDb); } /// /// 获取对象实体 /// /// 实体类 /// 数据表(可级联) /// 字段集合，形如：col1,col2,...,coln /// 匹配条件 /// 数据库简写 /// 数据实体 public static T GetInfo(string tbName, string strWhere, string fields = "*", string useDb = "") where T : class { T val = null; tbName = bName(tbName); fields = ields(fields); if (!OrEmpty(tbName)) { var strSql = ("select top 1 {0} from {1}", OrWhiteSpace(fields) ? "*" : fields, tbName); strWhere = here(strWhere); if (!OrWhiteSpace(strWhere)) { strSql += strWhere; } try { var ds = (strSql, useDb); if (ds != null && > 0) { var dt = [0]; if (dt != null && > 0) { var columns = new List();//数据列集合 var count = ; for (int i = 0; i < count; i++) { (s[i].r()); } var type = typeof(T); var flist = perties();//实体属性集合 var obj = tance(type); val = lue([0], columns, flist, e(obj) as T); } } } catch (Exception ex) { (("GetInfo⽅法异常:{0},表_{1}", e, tbName)); } } return val; }

///

/// 获取实体集合 /// /// 实体类 /// 数据表名 /// 匹配条件 /// 要获取的最⼤记录数量,0为全部 /// 字段集合，形如：col1,col2,...,coln /// 数据库简写 /// 满⾜条件的实体集 public static List GetInfoList(string tbName, string strWhere, int top = 0, string cols = "*", string useDb = "") where T : class { try { var ds = GetList(cols, tbName, strWhere, top, useDb); return tToList(ds); } catch (Exception ex) { (("GetInfoList⽅法异常:{0},表_{1}", e, tbName)); } return new List(); } /// /// 分页获取实体集 /// /// 实体类 /// 数据表名 /// 匹配条件 /// 排序字段 如：addtime desc /// 当前页号 /// 每页数据量 /// 数据库简写 /// public static List GetInfoByPage(string tbName, string strWhere, string orderby, int pageSize, int pageIndex, string useDb = "") where T : class { try { var ds = GetListByPage("*", tbName, strWhere, orderby, pageSize, pageIndex, useDb); return tToList(ds); } catch (Exception ex) { (("GetInfoByPage⽅法异常:{0},表_{1}", e, tbName)); } return new List(); } /// /// 执⾏存储过程，返回实体列表 /// /// 实体类 /// 存储过程名 /// 存储过程参数 /// 数据库简写 /// public static List GetEntityList(string storedProcName, IDataParameter[] parameters, string useDb = "") where T : class { try { var ds = cedure(storedProcName, parameters, useDb); return tToList(ds); } catch (Exception ex) { (("GetEntityList⽅法异常:{0},存储过程名称_{1}", e, storedProcName)); } return new List(); } #endregion #region 添加数据 /// /// 添加记录到数据库（实体主键为⾃增整形）(带事务) /// /// 实体(第⼀个属性必须为⾃增主键) /// 事务(不使⽤时，传⼊null) /// 实体实例 /// 数据表名(字段>=实体属性) /// 添加后⽣成的记录ID public static long RecordAddIdentity(SqlTransaction trans, T info, string tbName, string useDB = "") where T : class { var list = new List(); var sql = GetInsertSQL(info, e(" ", ""), out list, true); if (!OrEmpty(sql)) { try { object obj; if (trans == null) { obj = eScalar(d(';') + ";Select @@IDENTITY", y(), useDB); } else { obj = eScalar(trans, d(';') + ";Select @@IDENTITY", y()); } return obj == null ? 0 : 64(obj); } catch (Exception ex) { (("RecordAddIdentity⽅法异常:{0},表_{1}", e, tbName)); } } return 0; } /// /// 添加记录到数据库(带事务) /// /// 实体 /// 事务(不使⽤时，传⼊null) /// 实体实例 /// 数据表名(字段>=实体属性) /// 成功：True，失败：False public static bool RecordAdd(SqlTransaction trans, T info, string tbName, string useDB = "") where T : class { var list = new List(); var sql = GetInsertSQL(info, e(" ", ""), out list); if (!OrEmpty(sql)) { try { int rows = 0; if (trans == null) { rows = eNonQuery(sql, y(), useDB); } else { rows = eNonQuery(trans, sql, y()); } return rows > 0; } catch (Exception ex) { (("RecordAdd⽅法异常:{0},表_{1}", e, tbName)); } } return false; } /// /// 批量插⼊记录(逐条) /// /// 实体 /// 事务(不使⽤时，传⼊null) /// 数据表名(字段>=实体属性) /// 数据实体集合 /// 实体主键是否为⾃增整形 /// 数据库简写 /// 执⾏成功的数据数 public static int RecordsInsert(SqlTransaction trans, string tbName, List list, bool identity = false, string useDb = "") where T : class { int val = 0; var count = ; if (identity) { for (int i = 0; i < count; i++) { if (RecordAddIdentity(trans, list[i], tbName, useDb) > 0) { val++; } } } else { for (int i = 0; i < count; i++) { if (RecordAdd(trans, list[i], tbName, useDb)) { val++; } } } return val; } /// /// 批量插⼊记录(不⽀持html数据),不建议使⽤ /// /// 数据表名 /// 字段集合 如：col1,col2,... /// 值集合(值中不能包含",") 如：'val1','val2',... /// 数据库简写 /// 受影响⾏数 public static int RecordInsert(string tbName, string fields, List values, string useDb = "") { if (OrWhiteSpace(tbName) || OrWhiteSpace(fields) || < 1) { return 0; } var strSql = new StringBuilder(512); (("insert into {0}({1}) ", e(" ", ""), e(" ", ""))); var colLength = (',').Length; var equalLength = false;//字段长度是否与值长度是否相同 var count = ; for (int i = 0; i < count; i++) { if (values[i].Split(',').Length == colLength) { equalLength = true; if (i == 0) { (" select " + values[i]); } else { (" union all select " + values[i]); } } } if (equalLength) { try { return eNonQuery(ng(), useDb); } catch (Exception ex) { (("RecordInsert⽅法异常:{0},表_{1}", e, tbName)); } } return 0; } #endregion #region 更新数据 /// /// 更新指定数据库指定表中信息 /// zlf 2014-12-10 /// 事务(不使⽤时，传⼊null) /// 列+值(形如：col = 'val',col2='val2') /// 数据表名 /// 匹配条件 /// 是否更新成功

public static bool SetValue(SqlTransaction trans, string ColAndVal, string tbName, string strWhere, string useDb = "") { bool value = false; if (ns(" ") && !ns(ColAndVal, "case ")) { ColAndVal = e(" ", "");//防⽌SQL注⼊ } if (OrEmpty(ColAndVal) || OrEmpty(tbName)) { return false; } var strSql = ("update {0} set {1}", e(" ", ""), ColAndVal); strWhere = here(strWhere); if (!OrEmpty(strWhere)) { strSql += strWhere; } try { int rows = 0; if (trans == null) { rows = eNonQuery(strSql, useDb); } else { rows = eNonQuery(trans, strSql); } return rows > 0; } catch (Exception ex) { (("SetValue⽅法异常:{0},表_{1}", e, tbName)); } return value; } ///

/// 更新⼀条记录（实体第⼀个属性必须为主键）(带事务) /// /// 实体类 /// 事务(不使⽤时，传⼊null) /// 数据表名(字段>=实体属性) /// 数据实体 public static bool RecordUpdate(SqlTransaction trans, string tbName, T info, string useDb = "") where T : class { var list = new List(); var sql = GetUpdateSQL(info, e(" ", ""), out list); if (!OrEmpty(sql)) { try { int rows = 0; if (trans == null) { rows = eNonQuery(sql, y(), useDb); } else { rows = eNonQuery(trans, sql, y()); } return rows > 0; } catch (Exception ex) { (("RecordUpdate⽅法异常:{0},表_{1}", e, tbName)); } } return false; } #endregion #region 删除数据 /// /// 物理删除数据⾏ /// /// 事务(不使⽤时，传⼊null) /// 数据表名 /// 匹配条件 /// 数据库简写 public static bool DeleteRows(SqlTransaction trans, string tbName, string strWhere, string useDb = "") { var val = false; var strSql = ("delete from {0}", e(" ", "")); strWhere = here(strWhere); if (!OrEmpty(strWhere)) { strSql += strWhere; } else { return val; } try { int rows = 0; if (trans == null) { rows = eNonQuery(strSql, useDb); } else { rows = eNonQuery(trans, strSql); } return rows > 0; } catch (Exception ex) { (("DeleteRows⽅法异常:{0},表_{1}", e, tbName)); } return val; } #endregion }最后是操作⽇志类/// /// 数据库操作⽇志类 /// public class DbLog { /// /// 锁 /// private static object lockObj = new object(); /// /// ⽇志路径 /// private static string logPath = rectory + "DbLog"; /// /// 记录运⾏⽇志 /// /// ⽇志名称 /// ⽇志信息 public static void WriteLog(string msg, string fileName) { //添加排他锁，解决并发写⼊的问题 (lockObj); try { fileName = ("{0}_{1}", ng("yyyyMMdd"), fileName); if (!(logPath)) { Directory(logPath); } //如果⽇志⽂件⼤⼩超过了指定最⼤值，则转存为新的⽂件 var fi = new fo(logPath + "" + fileName); if ( && >= 2 * 1024 * 1024) { (logPath + "" + ng("yyyyMMddHHmmss") + "_long_" + fileName); } string logContent = msg; logContent = ("{0} {1}", ng("yyyy-MM-dd HH:mm:ss fff"), msg); using (Writer SW = Text(logPath + "" + fileName)) { ine(logContent); } } catch { return; } finally { (lockObj); } } /// /// 异步写⽇志 /// public static void LogAsync(string msg, string fileName = "") { var logAsyn = new Action(() => { WriteLog(msg, fileName); }); nvoke(null, null); } /// /// 记录错误⽇志 /// /// ⽇志信息 public static void LogErr(string msg, string fileName = "") { LogAsync(msg, fileName); } }

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

C#数据库操作通⽤⽅法类平时使⽤的数据库操作类整理更新后备份，记录下来以供以后使⽤，并顺便分享给⼤家⼀起交流。直接上源码：整个底层操作⽅法分为4个类，基础⽅法类：SqlBase 基础⼯具类：SqlTool ⽇志类：DbLog 和MSSQL操作类：MsSqlHelper。由于平时⼯作未⽤到其他类型数据库，因此未整理其他数据库的操作类，以后⽤到的话会进⾏更新。⾸先是通⽤的数据库底层操作⽅法整理：///

/// 数据库操作基础⽅法类 /// public class SqlBase { #region 字段与属性声明 private static int _cmdTimeOut = 30; /// /// 等待命令执⾏的时间（以秒为单位） /// public static int CMDTimeOut { get { return _cmdTimeOut; } set { _cmdTimeOut = value; } } private static string _defaultDb = "conn"; /// /// 默认数据库连接配置名称 /// public static string DefaultDb { get { return _defaultDb; } set { _defaultDb = value; } } /// /// 默认数据集名称 /// private static string _defaultDataSet = "ds"; /// /// 默认数据表名称 /// private static string _defaultDataTable = "dt"; /// /// 数据库连接失败返回信息 /// private static string _linkErrMsg = "数据库连接失败"; /// /// 存储过程返回值参数名 /// private static string _returnParaName = "ReturnValue"; #endregion /// /// 默认数据库连接字符串 /// public static string _connStr = ; /// /// 获取数据库连接 /// /// 数据库简写 public static string GetConnectionString(string useDb) { switch (useDb) { case "conn": return _connStr; default: useDb = DefaultDb; break; } return tings[useDb].ToString(); } /// /// 获取数据库连接 /// /// 数据库简写 private static SqlConnection GetConnection(string useDb) { if (OrEmpty(useDb)) useDb = DefaultDb; var connStr = GetConnectionString(useDb); if (!OrEmpty(connStr)) { return new SqlConnection(connStr); } return null; } /// /// 执⾏数据库操作 /// /// 数据库执⾏语句 /// 数据库简写 /// 受到影响的⾏数 public static int ExecuteNonQuery(string sql, string useDb = "") { return ExecuteNonQuery(sql, null, useDb); } /// /// 执⾏数据库操作 /// /// 数据库执⾏语句 /// 参数集 /// 数据库简写 /// 受影响的⾏数 public static int ExecuteNonQuery(string sql, SqlParameter[] paras, string useDb = "") { SqlConnection conn = GetConnection(useDb); try { if (conn == null) throw new Exception(_linkErrMsg); (); var cmd = new SqlCommand(sql, conn); dTimeout = CMDTimeOut; (); if (paras != null) { ge(paras); } return eNonQuery(); } catch (eption e) { throw e; } finally { if (conn != null) (); } } /// /// 执⾏SQL语句并返回受影响的⾏数,带事务操作 /// /// 事务 /// 数据库执⾏语句 /// 参数集 /// public static int ExecuteNonQuery(SqlTransaction trans, string sql, SqlParameter[] paras = null) { try { if ( == ) { (); } SqlCommand command = new SqlCommand(sql, tion); dTimeout = CMDTimeOut; ction = trans; (); if (paras != null) { ge(paras); } return eNonQuery(); } catch (Exception e) { throw e; } finally { (); } } /// /// 执⾏SQL语句并返回受影响的⾏数,带事务操作(只针对sqlserver) /// /// /// /// public static int ExecuteNonQueryWithTransation(string sql, SqlParameter[] paras = null, string useDB = "") { SqlTransaction sqlTransaction = null; SqlConnection conn = GetConnection(useDB); try { if (conn == null) throw new Exception(_linkErrMsg); (); sqlTransaction = ransaction(); var cmd = new SqlCommand(sql, conn); dTimeout = CMDTimeOut; ction = sqlTransaction; (); if (paras != null) { ge(paras); } var ret = eNonQuery(); (); return ret; } catch (eption e) { ck(); throw e; } finally { if (conn != null) (); } } /// /// 执⾏查询，并返回查询所返回的结果集中第⼀⾏的第⼀列。忽略其他列或⾏。 /// /// 数据库执⾏语句 /// 参数集 /// 数据库简写 /// 返回查询所返回的结果集中第⼀⾏的第⼀列或空引⽤（如果结果集为空).忽略其他列或⾏ public static object ExecuteScalar(string sql, string useDb = "") { return ExecuteScalar(sql, null, useDb); } /// /// 执⾏查询，并返回查询所返回的结果集中第⼀⾏的第⼀列。忽略其他列或⾏。 /// /// 计算查询结果语句 /// 查询结果（object） public static object ExecuteScalar(string sql, SqlParameter[] paras, string useDB = "") { SqlConnection conn = GetConnection(useDB); try { if (conn == null) throw new Exception(_linkErrMsg); (); var cmd = new SqlCommand(sql, conn); dTimeout = CMDTimeOut; (); if (paras != null) { ge(paras); } var obj = eScalar(); if ((obj, null) || (obj, )) { return null; } else { return obj; } } catch (eption e) { throw e; } finally { if (conn != null) (); } } /// /// 执⾏查询，并返回查询所返回的结果集中第⼀⾏的第⼀列。忽略其他列或⾏。 /// /// 事务 /// 数据库执⾏语句 /// 参数集 /// public static object ExecuteScalar(SqlTransaction trans, string sql, SqlParameter[] cmdParms = null) { try { if ( == ) { (); } SqlCommand cmd = new SqlCommand(sql, tion); dTimeout = CMDTimeOut; ction = trans; (); if (cmdParms != null) { ge(cmdParms); } var obj = eScalar(); if ((obj, null) || (obj, )) { return null; } else { return obj; } } catch (Exception e) { throw e; } finally { (); } } /// /// 执⾏查询语句，返回DataSet /// /// 查询语句 /// 数据库简写 /// 数据集 public static DataSet Query(string sql, string useDb = "") { var ds = new DataSet(); SqlConnection conn = GetConnection(useDb); try { if (conn == null) throw new Exception(_linkErrMsg); (); SqlCommand cmd = new SqlCommand(sql, conn); dTimeout = CMDTimeOut; var command = new SqlDataAdapter(cmd); (ds, _defaultDataSet); } catch (eption e) { throw e; } finally { if (conn != null) (); } return ds; } /// /// 执⾏查询语句，返回DataSet(带参数化) /// /// sql语句 /// 参数 /// 数据库简写 /// 数据集 public static DataSet QueryWithParams(string sql, SqlParameter[] cmdParms = null, string useDb = "") { var ds = new DataSet(); SqlConnection conn = GetConnection(useDb); try { if (conn == null) throw new Exception(_linkErrMsg); (); SqlCommand cmd = new SqlCommand(sql, conn); dTimeout = CMDTimeOut; (); if (cmdParms != null) { ge(cmdParms); } var command = new SqlDataAdapter(cmd); (ds, _defaultDataSet); } catch (eption e) { throw e; } finally { if (conn != null) (); } return ds; } #region 存储过程⽅法

///

/// 执⾏存储过程 /// /// 存储过程名 /// 存储过程参数 /// 数据库简写 /// 超时时间 /// DataSet public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string useDB = "") { SqlConnection conn = GetConnection(useDB); try { if (conn == null) throw new Exception(_linkErrMsg); (); DataSet dataSet = new DataSet(); SqlDataAdapter sqlDA = new SqlDataAdapter(); Command = BuildQueryCommand(conn, storedProcName, parameters); dTimeout = CMDTimeOut; (dataSet, _defaultDataTable); return dataSet; } finally { if (conn != null) (); } } /// /// 构建 SqlCommand 对象(⽤来返回⼀个结果集，⽽不是⼀个整数值) /// /// 数据库连接 /// 存储过程名 /// 存储过程参数 /// SqlCommand private 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; } /// /// 执⾏存储过程，返回请求结果

///

/// 存储过程名 /// 存储过程参数 /// 影响的⾏数 /// 请求结果 public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected, string useDB = "") { SqlConnection conn = GetConnection(useDB); try { rowsAffected = 0; if (conn == null) throw new Exception(_linkErrMsg); (); int result; SqlCommand command = BuildIntCommand(conn, storedProcName, parameters); rowsAffected = eNonQuery(); result = (int)ters[_returnParaName].Value; return result; } finally { if (conn != null) (); } } /// /// 创建 SqlCommand 对象实例(⽤来返回⼀个整数值)

///

/// 存储过程名 /// 存储过程参数 /// SqlCommand 对象实例 private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); (new SqlParameter(_returnParaName, , 4, Value, false, 0, 0, , t, null)); return command; } #endregion }其次，常⽤的数据库底层操作⼯具类整理：/// /// 数据库操作⼯具⽅法类 /// public class SqlTool { /// /// string扩展⽅法(忽略⼤⼩写⽐较字符串) /// /// 源字符串 /// ⽬标字符串 /// public static bool Contains(string source, string value, StringComparison comparisonType = lIgnoreCase) { return f(value, comparisonType) >= 0; } #region 校验⽅法 /// /// 验证sql匹配条件是否正确(若以and开头则⾃动去除) /// /// sql匹配条件 public static string CheckWhere(string where) { if (!OrWhiteSpace(where)) { var str = art();//去除前置空格 if (f("and ", lIgnoreCase) == 0)//若以and开头则⾃动去除第⼀个and { where = ing(4);//若要保留前⾯⼀个空格，可以改为3 } //where = filterSql(where);//防SQL注⼊ if (f("order by", lIgnoreCase) == 0) { where = " " + where; } else if (!OrWhiteSpace(where)) { where = " where " + where; } } return where; } /// /// 表名安全校验 /// /// 数据表名 public static string CheckTbName(string tbName) { //if (ns(" ") && !Contains(tbName, " join ")) //⾮连接语句 //{ // tbName = e(" ", "");//防⽌SQL注⼊ //} return tbName; } /// /// 字段安全校验 /// /// 字段集合 public static string CheckFields(string fields) { //var str = r(); //if (ns(" ") && !ns(",case ") && f("distinct ") < 0) //{ // str = e(" as ", "#"); // fields = e(" ", "").Replace("#", " as "); ;//防⽌SQL注⼊ //} return fields; } /// /// 过滤SQL语句,防⽌注⼊ /// /// Sql语句 public static string filterSql(string strSql) { var str = r().Trim(); str = e("exec", ""); str = e("delete", ""); str = e("master", ""); str = e("truncate", ""); str = e("declare", ""); str = e("create", ""); str = e("xp_", "no"); return str; } #endregion #region 实体赋值⽅法 /// /// 类型转换字典 /// private static Dictionary TypeDic = new Dictionary{{typeof(bool?),typeof(bool)},{typeof(int?),typeof(int)}, {typeof(long?),typeof(long)},{typeof(decimal?),typeof(decimal)},{typeof(DateTime?),typeof(DateTime)} }; /// /// 获取标准类型 /// public static Type GetStandardType(Type t) { if (nsKey(t)) { t = TypeDic[t]; } return t; } /// /// 根据读取到的数据为实体实例赋值 /// /// 实体 /// 读取到的⼀⾏数据 /// 数据列名集合 /// 实体属性集合 /// 实体实例 /// 赋值后的实体实例 public static T GetTValue(IDataReader dataReader, List columns, tyInfo[] fields, T info) where T : class { try { var cols = new List(); ge(columns); foreach (var p in fields)//为实体实例赋值 { if ( < 1) break; var key = r(); var idx = f(key); if (idx >= 0) { At(idx); var ovalue = dataReader[]; if (ovalue == null || ovalue == ) continue; var ptype = GetStandardType(tyType); ue(info, Type(ovalue, ptype), null); } } } catch (Exception ex) { ("GetTValue⽅法异常:" + e); } return info; } /// /// 根据读取到的数据为实体实例赋值 /// /// 实体 /// 数据⾏ /// 数据列名集合 /// 实体属性集合 /// 实体实例 /// 赋值后的实体实例 public static T GetTValue(DataRow row, List columns, tyInfo[] fields, T info) where T : class { try { var cols = new List(); ge(columns); foreach (var p in fields)//为实体实例赋值 { if ( < 1) break; var key = r(); var idx = f(key); if (idx >= 0) { At(idx); var ovalue = row[]; if (ovalue == null || ovalue == ) continue; var ptype = GetStandardType(tyType); ue(info, Type(ovalue, ptype), null); } } } catch (Exception ex) { ("GetTValue1⽅法异常:" + e); } return info; } #endregion #region 实例⼯具⽅法 /// /// 对象实例缓存 /// private static rentDictionary TCache = new rentDictionary(); /// /// 缓存操作锁 /// private static object lockCache = new object(); /// /// 实体克隆⽅法 /// private static MethodInfo wiseClone = typeof(object).GetMethod("MemberwiseClone", lic | ce); /// /// 实体克隆委托⽅法 /// public static Func doClone = Delegate(typeof(Func), wiseClone) as Func; /// /// 获取对象实例 /// /// 对象类型 public static object GetInstance(Type type) { object obj; if (!Value(, out obj)) { obj = Instance(type);//构建实例 lock (lockCache) { (, obj); } } return obj; } /// /// DataSet转实体集 /// /// 实体类 public static List DataSetToList(DataSet ds) where T : class { if (ds != null) { var dt = [0]; return DataTableToList(dt); } return new List(); } /// /// DataTable转实体集 /// /// 实体类 public static List DataTableToList(DataTable dt) where T : class { var list = new List(); if (dt != null && > 0) { var type = typeof(T); var fields = perties();//实体属性集合 var columns = new List();//数据列集合 int count = ; for (int i = 0; i < count; i++) { (s[i].r()); } var obj = GetInstance(type); foreach (DataRow row in ) { var val = GetTValue(row, columns, fields, doClone(obj) as T); (val); } } return list; } #endregion }SQL Server数据库操作帮助类/// /// SQL Server数据库操作帮助类 /// public class MsSqlHelper { /// /// 参数前缀 /// private static string PreParameter = "@"; #region ⼯具⽅法 // /// 获取数据插⼊SQL语句 /// 性能：13字段⼗万次约0.76s，49字段⼗万次约1.81s /// 实体 /// 实体实例 /// 数据表名(字段>=实体属性) /// SQL参数集 /// 第⼀个属性是否为⾃增主键 /// 数据插⼊SQL语句 private static string GetInsertSQL(T info, string tbName, out List sqlParams, bool identify = false) where T : class { var sql = ; sqlParams = new List();//返回值参数 if (OrWhiteSpace(tbName) || info == null) { return sql; } var type = typeof(T); var fields = perties();//获取实体成员字段 if ( < 2) //错误的数据实体 { return sql; } var fmt = "Insert into " + tbName + "({0}) values ({1});"; var cb = new StringBuilder(256);//列集合 var vb = new StringBuilder(512);//值集合 var link = ",";//字段连接符 var start = identify ? 1 : 0; var count = ; for (int i = start; i < count; i++) { var p = fields[i]; var v = ue(info, null); if (v == null || v == ) continue;//值为null不处理，使⽤数据库默认值 (link + ); (link + PreParameter + ); (new SqlParameter(PreParameter + , v)); } if ( > 0) //实体属性值不全为null { sql = (fmt, (0, 1).ToString(), (0, 1).ToString()); } return sql; } /// /// 获取数据更新SQL语句(要求实体第⼀个属性必须为主键) /// /// 实体 /// 实体实例 /// 数据表名(字段>=实体属性) /// SQL参数集 /// 数据更新SQL语句 private static string GetUpdateSQL(T info, string tbName, out List sqlParams) { var sql = ; sqlParams = new List();//返回值参数 if (OrWhiteSpace(tbName) || info == null) { return sql; } var type = typeof(T); var fields = perties();//获取实体成员字段 if ( < 2) //错误的数据实体 { return sql; } var cv = new StringBuilder(512);//列值对集合 var count = ; for (int i = 1; i < count; i++) { var p = fields[i]; var v = ue(info, null); if (v == null || v == ) continue;//值为null不处理，使⽤数据库默认值 ("," + + "=" + PreParameter + ); (new SqlParameter(PreParameter + , v)); } if ( > 0) //实体属性值不全为null { var sets = (0, 1).ToString(); (new SqlParameter(PreParameter + fields[0].Name, fields[0].GetValue(info, null))); sql = ("update {0} set {1} where " + fields[0].Name + "=" + PreParameter + fields[0].Name, tbName, sets); } return sql; } /// /// 获取数据更新(先删除后添加)SQL语句(要求实体第⼀个属性必须为主键) /// /// 实体 /// 实体实例 /// 数据表名(字段>=实体属性) /// SQL参数集 /// 第⼀个属性是否为⾃增主键 /// 数据更新SQL语句 private static string GetUpdateByDelAndAdd(T info, string tbName, out List sqlParams, bool identify = false) { var sql = ; sqlParams = new List();//返回值参数 if (OrWhiteSpace(tbName) || info == null) { return sql; } var type = typeof(T); var fields = perties();//获取实体成员字段 if ( < 2) //错误的数据实体 { return sql; } var sb = new StringBuilder(1024); ("delete from " + tbName + " where " + fields[0].Name + "=" + PreParameter + fields[0].Name); ("Insert into " + tbName + "({0}) values ({1});"); var cb = new StringBuilder(256);//列集合 var vb = new StringBuilder(512);//值集合 var link = ",";//字段连接符 var start = identify ? 1 : 0; var count = ; for (int i = start; i < count; i++) { var p = fields[i]; var v = ue(info, null); if (v == null || v == ) continue;//值为null不处理，使⽤数据库默认值 (link + ); (link + PreParameter + ); (new SqlParameter(PreParameter + , v)); } if ( > 0) //实体属性值不全为null { sql = (ng(), (0, 1).ToString(), (0, 1).ToString()); } return sql; } #endregion #region 取数据 /// /// 获取指定表中指定列的值 /// /// 列编码 /// 数据表名 /// 匹配条件 /// 数据库简写 /// 满⾜条件的第⼀⾏的值

public static string GetColumnValue(string ColumnCode, string tbName, string strWhere, string useDb = "") { var value = ; tbName = bName(tbName); ColumnCode = ields(ColumnCode); if (OrEmpty(ColumnCode) || OrEmpty(tbName)) { return value; } var strSql = ("select top 1 {0} from {1}", ColumnCode, tbName); strWhere = here(strWhere); if (!OrWhiteSpace(strWhere)) { strSql += strWhere; } try { var obj = eScalar(strSql, useDb); value = obj == null ? : ng(); } catch (Exception e) { (("GetColumnValue⽅法异常:{0},表_{1} 字段_{2}", e, tbName, ColumnCode)); } return value; } ///

/// 获取满⾜条件的第⼀⾏多列的值 /// /// 列编码集合 /// 数据表名 /// 匹配条件 /// 数据库简写 /// 满⾜条件的⾸⾏多列的值 public static List GetValues(List columns, string tbName, string strWhere, string useDb = "") { int step = 0; var value = new List(); tbName = bName(tbName); if ( < 1 || OrEmpty(tbName)) { return value; } step = 1; var cols = ields((",", columns)); var strSql = ("select top 1 {0} from {1}", cols, tbName); step = 2; strWhere = here(strWhere); if (!OrWhiteSpace(strWhere)) { strSql += strWhere; } try { step = 3; var ds = (strSql, useDb); if (ds != null && [0] != null && [0]. > 0) { var dr = [0].Rows[0]; var count = ; for (int i = 0; i < count; i++) { (dr[i] == null ? "" : dr[i].ToString()); } e(); } } catch (Exception e) { (("GetValues⽅法异常:{0} step:{1} 表_{2} 字段_{3}", e, step, tbName, cols)); } return value; } /// /// 获取指定表中记录总数 /// /// 数据表名 /// 匹配条件 /// 数据库简写 /// 满⾜条件的记录总数

public static int GetRecordCount(string tbName, string strWhere, string useDb = "") { int value = 0; tbName = bName(tbName); if (OrEmpty(tbName)) { return value; } var strSql = ("select count(1) from {0}", tbName); strWhere = here(strWhere); if (!OrWhiteSpace(strWhere)) { strSql += strWhere; } try { object obj = eScalar(strSql, useDb); value = obj == null ? 0 : 32(obj); } catch (Exception ex) { (("GetRecordCount⽅法异常:{0},表_{1}", e, tbName)); } return value; } ///

/// 获取数据集 /// /// 列集合，形如：col1,col2,...,coln /// 数据表名 /// 匹配条件 /// 获取记录最⼤数量,0为不限制 /// 数据库简写 /// 满⾜条件的数据集

public static DataSet GetList(string fields, string tbName, string strWhere, int top = 0, string useDb = "") { DataSet ds = null; tbName = bName(tbName); fields = ields(fields); if (OrEmpty(tbName)) { return ds; } var strSql = ("select {0} {1} from {2}", top < 1 ? "" : ("top {0}", top), OrEmpty(fields) ? "*" : fields, tbName); strWhere = here(strWhere); if (!OrWhiteSpace(strWhere)) { strSql += strWhere; } try { ds = (strSql, useDb); } catch (Exception ex) { (("GetList⽅法异常:{0},表_{1}", e, tbName)); } return ds; } ///

/// 分页获取数据集 /// /// 列集合，形如：col1,col2,...,coln /// 数据表名 /// 匹配条件 /// 排序字段 如：addtime desc /// 当前页号 /// 每页数据量 /// 数据库简写 /// 满⾜条件的数据集

public static DataSet GetListByPage(string fields, string tbName, string strWhere, string orderby, int pageSize, int pageIndex, string useDb = "") { DataSet ds = null; tbName = bName(tbName); fields = ields(fields); if (OrEmpty(fields) || OrEmpty(tbName) || OrEmpty(orderby)) { return ds; } if (pageSize < 1) pageSize = 10;//默认每页10条 if (pageIndex < 1) pageIndex = 1;//默认第⼀页 int start = (pageIndex - 1) * pageSize + 1; int end = pageIndex * pageSize; var strSql = new StringBuilder(512); ("select * from ("); (("select ROW_NUMBER() OVER (ORDER BY {0}) as row,{1} from {2}", orderby, fields, tbName)); strWhere = here(strWhere); if (!OrWhiteSpace(strWhere)) { (strWhere); } ((") as T where between {0} and {1}", start, end)); try { ds = (ng(), useDb); } catch (Exception ex) { (("GetListByPage⽅法异常:{0},表_{1}", e, tbName)); } return ds; } ///

/// 获取对象实体 /// /// 实体类 /// 数据表名 /// 匹配条件 /// 数据库简写 /// 数据实体 public static T GetModel(string tbName, string strWhere, string useDb = "") where T : class { return GetInfo(tbName, strWhere, "*", useDb); } /// /// 获取对象实体 /// /// 实体类 /// 数据表(可级联) /// 字段集合，形如：col1,col2,...,coln /// 匹配条件 /// 数据库简写 /// 数据实体 public static T GetInfo(string tbName, string strWhere, string fields = "*", string useDb = "") where T : class { T val = null; tbName = bName(tbName); fields = ields(fields); if (!OrEmpty(tbName)) { var strSql = ("select top 1 {0} from {1}", OrWhiteSpace(fields) ? "*" : fields, tbName); strWhere = here(strWhere); if (!OrWhiteSpace(strWhere)) { strSql += strWhere; } try { var ds = (strSql, useDb); if (ds != null && > 0) { var dt = [0]; if (dt != null && > 0) { var columns = new List();//数据列集合 var count = ; for (int i = 0; i < count; i++) { (s[i].r()); } var type = typeof(T); var flist = perties();//实体属性集合 var obj = tance(type); val = lue([0], columns, flist, e(obj) as T); } } } catch (Exception ex) { (("GetInfo⽅法异常:{0},表_{1}", e, tbName)); } } return val; }

///

/// 获取实体集合 /// /// 实体类 /// 数据表名 /// 匹配条件 /// 要获取的最⼤记录数量,0为全部 /// 字段集合，形如：col1,col2,...,coln /// 数据库简写 /// 满⾜条件的实体集 public static List GetInfoList(string tbName, string strWhere, int top = 0, string cols = "*", string useDb = "") where T : class { try { var ds = GetList(cols, tbName, strWhere, top, useDb); return tToList(ds); } catch (Exception ex) { (("GetInfoList⽅法异常:{0},表_{1}", e, tbName)); } return new List(); } /// /// 分页获取实体集 /// /// 实体类 /// 数据表名 /// 匹配条件 /// 排序字段 如：addtime desc /// 当前页号 /// 每页数据量 /// 数据库简写 /// public static List GetInfoByPage(string tbName, string strWhere, string orderby, int pageSize, int pageIndex, string useDb = "") where T : class { try { var ds = GetListByPage("*", tbName, strWhere, orderby, pageSize, pageIndex, useDb); return tToList(ds); } catch (Exception ex) { (("GetInfoByPage⽅法异常:{0},表_{1}", e, tbName)); } return new List(); } /// /// 执⾏存储过程，返回实体列表 /// /// 实体类 /// 存储过程名 /// 存储过程参数 /// 数据库简写 /// public static List GetEntityList(string storedProcName, IDataParameter[] parameters, string useDb = "") where T : class { try { var ds = cedure(storedProcName, parameters, useDb); return tToList(ds); } catch (Exception ex) { (("GetEntityList⽅法异常:{0},存储过程名称_{1}", e, storedProcName)); } return new List(); } #endregion #region 添加数据 /// /// 添加记录到数据库（实体主键为⾃增整形）(带事务) /// /// 实体(第⼀个属性必须为⾃增主键) /// 事务(不使⽤时，传⼊null) /// 实体实例 /// 数据表名(字段>=实体属性) /// 添加后⽣成的记录ID public static long RecordAddIdentity(SqlTransaction trans, T info, string tbName, string useDB = "") where T : class { var list = new List(); var sql = GetInsertSQL(info, e(" ", ""), out list, true); if (!OrEmpty(sql)) { try { object obj; if (trans == null) { obj = eScalar(d(';') + ";Select @@IDENTITY", y(), useDB); } else { obj = eScalar(trans, d(';') + ";Select @@IDENTITY", y()); } return obj == null ? 0 : 64(obj); } catch (Exception ex) { (("RecordAddIdentity⽅法异常:{0},表_{1}", e, tbName)); } } return 0; } /// /// 添加记录到数据库(带事务) /// /// 实体 /// 事务(不使⽤时，传⼊null) /// 实体实例 /// 数据表名(字段>=实体属性) /// 成功：True，失败：False public static bool RecordAdd(SqlTransaction trans, T info, string tbName, string useDB = "") where T : class { var list = new List(); var sql = GetInsertSQL(info, e(" ", ""), out list); if (!OrEmpty(sql)) { try { int rows = 0; if (trans == null) { rows = eNonQuery(sql, y(), useDB); } else { rows = eNonQuery(trans, sql, y()); } return rows > 0; } catch (Exception ex) { (("RecordAdd⽅法异常:{0},表_{1}", e, tbName)); } } return false; } /// /// 批量插⼊记录(逐条) /// /// 实体 /// 事务(不使⽤时，传⼊null) /// 数据表名(字段>=实体属性) /// 数据实体集合 /// 实体主键是否为⾃增整形 /// 数据库简写 /// 执⾏成功的数据数 public static int RecordsInsert(SqlTransaction trans, string tbName, List list, bool identity = false, string useDb = "") where T : class { int val = 0; var count = ; if (identity) { for (int i = 0; i < count; i++) { if (RecordAddIdentity(trans, list[i], tbName, useDb) > 0) { val++; } } } else { for (int i = 0; i < count; i++) { if (RecordAdd(trans, list[i], tbName, useDb)) { val++; } } } return val; } /// /// 批量插⼊记录(不⽀持html数据),不建议使⽤ /// /// 数据表名 /// 字段集合 如：col1,col2,... /// 值集合(值中不能包含",") 如：'val1','val2',... /// 数据库简写 /// 受影响⾏数 public static int RecordInsert(string tbName, string fields, List values, string useDb = "") { if (OrWhiteSpace(tbName) || OrWhiteSpace(fields) || < 1) { return 0; } var strSql = new StringBuilder(512); (("insert into {0}({1}) ", e(" ", ""), e(" ", ""))); var colLength = (',').Length; var equalLength = false;//字段长度是否与值长度是否相同 var count = ; for (int i = 0; i < count; i++) { if (values[i].Split(',').Length == colLength) { equalLength = true; if (i == 0) { (" select " + values[i]); } else { (" union all select " + values[i]); } } } if (equalLength) { try { return eNonQuery(ng(), useDb); } catch (Exception ex) { (("RecordInsert⽅法异常:{0},表_{1}", e, tbName)); } } return 0; } #endregion #region 更新数据 /// /// 更新指定数据库指定表中信息 /// zlf 2014-12-10 /// 事务(不使⽤时，传⼊null) /// 列+值(形如：col = 'val',col2='val2') /// 数据表名 /// 匹配条件 /// 是否更新成功

public static bool SetValue(SqlTransaction trans, string ColAndVal, string tbName, string strWhere, string useDb = "") { bool value = false; if (ns(" ") && !ns(ColAndVal, "case ")) { ColAndVal = e(" ", "");//防⽌SQL注⼊ } if (OrEmpty(ColAndVal) || OrEmpty(tbName)) { return false; } var strSql = ("update {0} set {1}", e(" ", ""), ColAndVal); strWhere = here(strWhere); if (!OrEmpty(strWhere)) { strSql += strWhere; } try { int rows = 0; if (trans == null) { rows = eNonQuery(strSql, useDb); } else { rows = eNonQuery(trans, strSql); } return rows > 0; } catch (Exception ex) { (("SetValue⽅法异常:{0},表_{1}", e, tbName)); } return value; } ///

/// 更新⼀条记录（实体第⼀个属性必须为主键）(带事务) /// /// 实体类 /// 事务(不使⽤时，传⼊null) /// 数据表名(字段>=实体属性) /// 数据实体 public static bool RecordUpdate(SqlTransaction trans, string tbName, T info, string useDb = "") where T : class { var list = new List(); var sql = GetUpdateSQL(info, e(" ", ""), out list); if (!OrEmpty(sql)) { try { int rows = 0; if (trans == null) { rows = eNonQuery(sql, y(), useDb); } else { rows = eNonQuery(trans, sql, y()); } return rows > 0; } catch (Exception ex) { (("RecordUpdate⽅法异常:{0},表_{1}", e, tbName)); } } return false; } #endregion #region 删除数据 /// /// 物理删除数据⾏ /// /// 事务(不使⽤时，传⼊null) /// 数据表名 /// 匹配条件 /// 数据库简写 public static bool DeleteRows(SqlTransaction trans, string tbName, string strWhere, string useDb = "") { var val = false; var strSql = ("delete from {0}", e(" ", "")); strWhere = here(strWhere); if (!OrEmpty(strWhere)) { strSql += strWhere; } else { return val; } try { int rows = 0; if (trans == null) { rows = eNonQuery(strSql, useDb); } else { rows = eNonQuery(trans, strSql); } return rows > 0; } catch (Exception ex) { (("DeleteRows⽅法异常:{0},表_{1}", e, tbName)); } return val; } #endregion }最后是操作⽇志类/// /// 数据库操作⽇志类 /// public class DbLog { /// /// 锁 /// private static object lockObj = new object(); /// /// ⽇志路径 /// private static string logPath = rectory + "DbLog"; /// /// 记录运⾏⽇志 /// /// ⽇志名称 /// ⽇志信息 public static void WriteLog(string msg, string fileName) { //添加排他锁，解决并发写⼊的问题 (lockObj); try { fileName = ("{0}_{1}", ng("yyyyMMdd"), fileName); if (!(logPath)) { Directory(logPath); } //如果⽇志⽂件⼤⼩超过了指定最⼤值，则转存为新的⽂件 var fi = new fo(logPath + "" + fileName); if ( && >= 2 * 1024 * 1024) { (logPath + "" + ng("yyyyMMddHHmmss") + "_long_" + fileName); } string logContent = msg; logContent = ("{0} {1}", ng("yyyy-MM-dd HH:mm:ss fff"), msg); using (Writer SW = Text(logPath + "" + fileName)) { ine(logContent); } } catch { return; } finally { (lockObj); } } /// /// 异步写⽇志 /// public static void LogAsync(string msg, string fileName = "") { var logAsyn = new Action(() => { WriteLog(msg, fileName); }); nvoke(null, null); } /// /// 记录错误⽇志 /// /// ⽇志信息 public static void LogErr(string msg, string fileName = "") { LogAsync(msg, fileName); } }