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