客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
|
写一个属于自己的数据库帮助类，温故而知新~~ 数据库操作核心部分： 1 /// <summary> 2 /// 数据库操作基类 3 /// </summary> 4 public class DbServiceBase : IDisposable 5 { 6 #region 私有字段 7 8 /// <summary> 9 /// 自动释放数据库连接 10 /// </summary> 11 private bool AutoClearConnection = true; 12 13 /// <summary> 14 /// 数据库连接字符串 15 /// </summary> 16 private string ConnectionStrings; 17 18 /// <summary> 19 /// 数据库连接对象 20 /// </summary> 21 private SqlConnection conn = null; 22 23 /// <summary> 24 /// 事务对象 25 /// </summary> 26 private SqlTransaction tran = null; 27 28 #endregion 29 30 #region 私有方法 31 32 /// <summary> 33 /// 创建数据库连接 34 /// </summary> 35 /// <returns></returns> 36 private void CreateConnection() 37 { 38 if (conn.IsNull()) 39 { 40 conn = new SqlConnection(ConnectionStrings); 41 } 42 if (conn.State != ConnectionState.Open) 43 { 44 conn.Open(); 45 } 46 } 47 48 /// <summary> 49 /// 释放数据库连接 50 /// </summary> 51 private void ClearConnection() 52 { 53 if (!tran.IsNull()) 54 { 55 tran = null; 56 } 57 if (!conn.IsNull()) 58 { 59 SqlConnection.ClearPool(conn); //用于清除每次的连接，防止已经进行的数据库连接进入sleeping而导致连接用户数增加 60 conn.Close(); 61 conn.Dispose(); 62 conn = null; 63 } 64 } 65 66 /// <summary> 67 /// 获得表名 68 /// </summary> 69 /// <typeparam name="T">类名</typeparam> 70 /// <returns>表名</returns> 71 private string GetTableName<T>() where T : class 72 { 73 string tableName = typeof(T).Name; 74 return tableName; 75 } 76 77 /// <summary> 78 /// 获得 where sql 语句 79 /// </summary> 80 /// <typeparam name="T">类名</typeparam> 81 /// <param name="where">筛选条件</param> 82 /// <param name="arrListSqlPara">SqlParameter参数</param> 83 /// <returns>sql语句</returns> 84 private string GetWhereSql<T>(Expression<Func<T, bool>> where, ref ArrayList arrListSqlPara) where T : class 85 { 86 List<SqlParaModel> listSqlParaModel = new List<SqlParaModel>(); 87 string whereSql = where == null ? "" : LambdaToSqlHelper.GetWhereSql(where, listSqlParaModel); 88 for (var i = 0; i < listSqlParaModel.Count; i++) 89 { 90 var key = listSqlParaModel[i].name; 91 var val = listSqlParaModel[i].value; 92 arrListSqlPara.Add(new SqlParameter("@" + key, val)); 93 } 94 return whereSql; 95 } 96 97 /// <summary> 98 /// 获得查询字段列表 99 /// </summary> 100 /// <typeparam name="T">类名</typeparam> 101 /// <param name="field">查询字段</param> 102 /// <returns>查询字段</returns> 103 private string GetQueryField<T>(Expression<Func<T, object>> field) where T : class 104 { 105 string queryField = field == null ? "*" : LambdaToSqlHelper.GetQueryField(field); 106 return queryField; 107 } 108 109 /// <summary> 110 /// 获得 order sql 语句 111 /// </summary> 112 /// <typeparam name="T">类名</typeparam> 113 /// <param name="orderBy">排序</param> 114 /// <returns>sql语句</returns> 115 private string GetOrderBySql<T>(Expression<Func<IQueryable<T>, IOrderedQueryable<T>>> orderBy) where T : class 116 { 117 string orderBySql = orderBy == null ? "" : LambdaToSqlHelper.GetOrderBySql(orderBy); 118 return orderBySql; 119 } 120 121 /// <summary> 122 /// 获得 insert sql 语句 123 /// </summary> 124 /// <typeparam name="T">类名</typeparam> 125 /// <param name="model">对象实例</param> 126 /// <param name="arrSqlPara">SqlParameter参数</param> 127 /// <returns>sql语句</returns> 128 private string GetCreateSql<T>(object model, ref SqlParameter[] arrSqlPara) where T : class 129 { 130 string resultSql = string.Empty; 131 ArrayList arrListSqlPara = new ArrayList(); 132 StringBuilder sbInsColName = new StringBuilder(); 133 StringBuilder sbInsColVal = new StringBuilder(); 134 string tableName = GetTableName<T>(); 135 var dic = ExtendMethod.Foreach(model); 136 foreach (var item in dic) 137 { 138 var val = item.Value; 139 if (!val.IsNull()) 140 { 141 var key = item.Key; 142 sbInsColName.Append(string.Format("{0},", key)); 143 sbInsColVal.Append(string.Format("@{0},", key)); 144 arrListSqlPara.Add(new SqlParameter("@" + key, val)); 145 } 146 } 147 sbInsColName.Remove(sbInsColName.Length - 1, 1); 148 sbInsColVal.Remove(sbInsColVal.Length - 1, 1); 149 arrSqlPara = (SqlParameter[])arrListSqlPara.ToArray(typeof(SqlParameter)); 150 resultSql = "insert into {0} ({1}) values ({2})".FormatWith(tableName, sbInsColName.ToString(), sbInsColVal.ToString()); 151 return resultSql; 152 } 153 154 /// <summary> 155 /// 156 /// </summary> 157 /// <param name="model">对象实例</param> 158 /// <param name="arrListSqlPara">SqlParameter参数</param> 159 /// <returns>sql语句</returns> 160 private string GetUpdateSqlParameter(object model, ref ArrayList arrListSqlPara) 161 { 162 string strUpdSql = string.Empty; 163 StringBuilder sbUpdCol = new StringBuilder(); 164 var dic = ExtendMethod.Foreach(model); 165 foreach (var item in dic) 166 { 167 var val = item.Value; 168 var key = item.Key; 169 sbUpdCol.Append(string.Format("{0} = @{1},", key, key)); 170 arrListSqlPara.Add(new SqlParameter("@" + key, val)); 171 } 172 sbUpdCol.Remove(sbUpdCol.Length - 1, 1); 173 strUpdSql = sbUpdCol.ToString(); 174 return strUpdSql; 175 } 176 177 /// <summary> 178 /// 获得 update sql 语句 179 /// </summary> 180 /// <typeparam name="T">类名</typeparam> 181 /// <param name="model">对象实例</param> 182 /// <param name="where">筛选条件</param> 183 /// <param name="arrSqlPara">SqlParameter参数</param> 184 /// <returns>sql语句</returns> 185 private string GetUpdateSql<T>(object model, Expression<Func<T, bool>> where, ref SqlParameter[] arrSqlPara) where T : class 186 { 187 ArrayList arrListSqlPara = new ArrayList(); 188 string tableName = string.Empty; 189 string updateSql = string.Empty; 190 string whereSql = string.Empty; 191 string resultSql = string.Empty; 192 tableName = GetTableName<T>(); 193 updateSql = GetUpdateSqlParameter(model, ref arrListSqlPara); 194 whereSql = GetWhereSql<T>(where, ref arrListSqlPara); 195 arrSqlPara = (SqlParameter[])arrListSqlPara.ToArray(typeof(SqlParameter)); 196 resultSql = string.Format("update {0} set {1} {2}", tableName, updateSql, whereSql); 197 return resultSql; 198 } 199 200 /// <summary> 201 /// 获得 delete sql 语句 202 /// </summary> 203 /// <typeparam name="T">类名</typeparam> 204 /// <param name="where">筛选条件</param> 205 /// <param name="arrSqlPara">SqlParameter参数</param> 206 /// <returns>sql语句</returns> 207 private string GetDeleteSql<T>(Expression<Func<T, bool>> where, ref SqlParameter[] arrSqlPara) where T : class 208 { 209 string resultSql = string.Empty; 210 ArrayList arrListSqlPara = new ArrayList(); 211 string tableName = GetTableName<T>(); 212 string whereSql = GetWhereSql<T>(where, ref arrListSqlPara); ; 213 arrSqlPara = (SqlParameter[])arrListSqlPara.ToArray(typeof(SqlParameter)); 214 resultSql = string.Format("delete from {0} {1}", tableName, whereSql); 215 return resultSql; 216 } 217 218 #region 获得 select sql 语句 219 220 private string GetFindListSql<T>(Expression<Func<T, object>> field, Expression<Func<T, bool>> where, Expression<Func<IQueryable<T>, IOrderedQueryable<T>>> orderBy, ref SqlParameter[] arrSqlPara) where T : class 221 { 222 string resultSql = string.Empty; 223 ArrayList arrListSqlPara = new ArrayList(); 224 string tableName = GetTableName<T>(); 225 string queryField = GetQueryField<T>(field); 226 string whereSql = GetWhereSql<T>(where, ref arrListSqlPara); 227 string orderBySql = GetOrderBySql<T>(orderBy); 228 arrSqlPara = (SqlParameter[])arrListSqlPara.ToArray(typeof(SqlParameter)); 229 resultSql = "select {0} from {1} {2} {3}".FormatWith(queryField, tableName, whereSql, orderBySql); 230 return resultSql; 231 } 232 233 private string GetPageListSql<T>(int pageIndex, int pageSize, Expression<Func<T, object>> field, Expression<Func<T, bool>> where, Expression<Func<IQueryable<T>, IOrderedQueryable<T>>> orderBy, ref SqlParameter[] arrSqlPara) where T : class 234 { 235 string resultSql = string.Empty; 236 ArrayList arrListSqlPara = new ArrayList(); 237 string tableName = GetTableName<T>(); 238 string queryField = GetQueryField<T>(field); 239 string whereSql = GetWhereSql<T>(where, ref arrListSqlPara); 240 string orderBySql = GetOrderBySql<T>(orderBy); 241 arrSqlPara = (SqlParameter[])arrListSqlPara.ToArray(typeof(SqlParameter)); 242 resultSql = "select top {0} {1} from (select row_number() over({2}) rownumber,{3} from {4} {5} ) tt_{6} where rownumber > {7}".FormatWith(pageSize, queryField, orderBySql, queryField, tableName, whereSql, tableName, (pageIndex - 1) * pageSize); 243 return resultSql; 244 } 245 246 private string GetFindSql<T>(Expression<Func<T, object>> field, Expression<Func<T, bool>> where, ref SqlParameter[] arrSqlPara) where T : class 247 { 248 string resultSql = string.Empty; 249 ArrayList arrListSqlPara = new ArrayList(); 250 string tableName = GetTableName<T>(); 251 string queryField = GetQueryField<T>(field); 252 string whereSql = GetWhereSql<T>(where, ref arrListSqlPara); 253 arrSqlPara = (SqlParameter[])arrListSqlPara.ToArray(typeof(SqlParameter)); 254 resultSql = "select {0} from {1} {2}".FormatWith(queryField, tableName, whereSql); 255 return resultSql; 256 } 257 258 private string GetCountSql<T>(Expression<Func<T, bool>> where, ref SqlParameter[] arrSqlPara) where T : class 259 { 260 string resultSql = string.Empty; 261 ArrayList arrListSqlPara = new ArrayList(); 262 string tableName = GetTableName<T>(); 263 string whereSql = GetWhereSql<T>(where, ref arrListSqlPara); 264 arrSqlPara = (SqlParameter[])arrListSqlPara.ToArray(typeof(SqlParameter)); 265 resultSql = "select count(*) from {0} {1}".FormatWith(tableName, whereSql); 266 return resultSql; 267 } 268 269 #endregion 270 271 #endregion 272 273 #region 事务操作 274 275 /// <summary> 276 /// 创建事务 277 /// </summary> 278 /// <returns></returns> 279 public void BeginTransaction() 280 { 281 tran = conn.BeginTransaction(); 282 } 283 284 /// <summary> 285 /// 提交事务 286 /// </summary> 287 public void CommitTransaction() 288 { 289 tran.Commit(); 290 } 291 292 /// <summary> 293 /// 回滚事务 294 /// </summary> 295 public void RollbackTransaction() 296 { 297 tran.Rollback(); 298 } 299 300 #endregion 301 302 #region 释放资源 303 304 /// <summary> 305 /// 306 /// </summary> 307 public void Dispose() 308 { 309 //throw new NotImplementedException(); 310 ClearConnection(); 311 } 312 313 #endregion 314 315 #region 构造函数 316 317 /// <summary> 318 /// 构造函数 319 /// </summary> 320 /// <param name="_ConnectionStrings">数据库连接字符串</param> 321 /// <param name="_AutoClearConnection">单个DML(增删查改)操作后是否释放资源。true释放，false不释放。默认true</param> 322 public DbServiceBase(string _ConnectionStrings, bool _AutoClearConnection = true) 323 { 324 this.ConnectionStrings = _ConnectionStrings; 325 this.AutoClearConnection = _AutoClearConnection; 326 } 327 328 #endregi |
2023-10-27
2022-08-15
2022-08-17
2022-09-23
2022-08-13
六六分期app的软件客服如何联系？不知道吗？加qq群【895510560】即可！标题：六六分期
今天小编告诉大家如何处理win10系统火狐flash插件总是崩溃的问题，可能很多用户都不知
今天小编告诉大家如何对win10系统删除桌面回收站图标进行设置，可能很多用户都不知道
今天小编告诉大家如何对win10系统电脑设置节能降温的设置方法，想必大家都遇到过需要
我们在使用xp系统的过程中,经常需要对xp系统无线网络安装向导设置进行设置，可能很多
今天小编告诉大家如何处理win7系统玩cf老是与主机连接不稳定的问题，可能很多用户都不
电脑对日常生活的重要性小编就不多说了，可是一旦碰到win7系统设置cf烟雾头的问题，很
我们在日常使用电脑的时候，有的小伙伴们可能在打开应用的时候会遇见提示应用程序无法
今天小编告诉大家如何对win7系统打开vcf文件进行设置，可能很多用户都不知道怎么对win
今天小编告诉大家如何对win10系统s4开启USB调试模式进行设置，可能很多用户都不知道怎
请发表评论