写一个属于自己的数据库帮助类，温故而知新~~
数据库操作核心部分：
1 /// <summary>View Code
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 #endregion
329
330 #region 增删查改
331
332 #region 插入数据
333
334 /// <summary>
335 /// 插入数据
336 /// </summary>
337 /// <typeparam name="T">类名</typeparam>
338 /// <param name="model">对象实例</param>
339 /// <returns></returns>
340 public int Create<T>(object model) where T : class
341 {
342 int affectedRows = 0;
343 SqlParameter[] arrSqlPara = new SqlParameter[] { };
344 var sql = GetCreateSql<T>(model, ref arrSqlPara);
345 CreateConnection();
346 SqlCommand cmd = new SqlCommand();
347 cmd.Connection = conn;
348 cmd.Parameters.AddRange(arrSqlPara);
349 cmd.CommandText = sql;
350 affectedRows = cmd.ExecuteNonQuery();
351 if (AutoClearConnection)
352 {
353 ClearConnection();
354 }
355 return affectedRows;
356 }
357
358 #endregion
359
360 #region 更新数据
361
362 /// <summary>
363 /// 更新数据
364 /// </summary>
365 /// <typeparam name="T">类名</typeparam>
366 /// <param name="model">对象实例</param>
367 /// <param name="where">筛选条件</param>
368 /// <returns>受影响行数</returns>
369 public int Update<T>(object model, Expression<Func<T, bool>> where) where T : class
370 {
371 int affectedRows = 0;
372 SqlParameter[] arrSqlPara = new SqlParameter[] { };
373 var sql = GetUpdateSql<T>(model, where, ref arrSqlPara);
374 CreateConnection();
375 SqlCommand cmd = new SqlCommand();
376 cmd.Connection = conn;
377 cmd.Parameters.AddRange(arrSqlPara);
378 cmd.CommandText = sql;
379 affectedRows = cmd.ExecuteNonQuery();
380 if (AutoClearConnection)
381 {
382 ClearConnection();
383 }
384 return affectedRows;
385 }
386
387 #endregion
388
389 #region 删除数据
390
391 /// <summary>
392 /// 删除数据
393 /// </summary>
394 /// <typeparam name="T">类名</typeparam>
395 /// <param name="where">筛选条件</param>
396 /// <returns>受影响行数</returns>
397 public int Delete<T>(Expression<Func<T, bool>> where) where T : class
398 {
399 int affectedRows = 0;
400 SqlParameter[] arrSqlPara = new SqlParameter[] { };
401 var sql = GetDeleteSql<T>(where, ref arrSqlPara);
402 CreateConnection();
403 SqlCommand cmd = new SqlCommand();
404 cmd.Connection = conn;
405 cmd.Parameters.AddRange(arrSqlPara);
406 cmd.CommandText = sql;
407 affectedRows = cmd.ExecuteNonQuery();
408 if (AutoClearConnection)
409 {
410 ClearConnection();
411 }
412 return affectedRows;
413 }
414
415 #endregion
416
417 #region 查询数据
418
419 #region 分页查询数据
420
421 /// <summary>
422 /// 分页查询数据
423 /// </summary>
424 /// <typeparam name="T">类名</typeparam>
425 /// <param name="pageIndex">页码</param>
426 /// <param name="pageSize">页大小</param>
427 /// <param name="field">查询字段</param>
428 /// <param name="where">筛选条件</param>
429 /// <param name="orderBy">排序</param>
430 /// <returns></returns>
431 public List<T> PageList<T>(int pageIndex, int pageSize, Expression<Func<IQueryable<T>, IOrderedQueryable<T>>> orderBy, Expression<Func<T, object>> field = null, Expression<Func<T, bool>> where = null) where T : class
432 {
433 if (orderBy == null)
434 {
435 throw new Exception("分页必须设置排序方式");
436 }
437 SqlParameter[] arrSqlPara = new SqlParameter[] { };
438 var sql = GetPageListSql<T>(pageIndex, pageSize, field, where, orderBy, ref arrSqlPara);
439 CreateConnection();
440 SqlCommand cmd = new SqlCommand();
441 cmd.Connection = conn;
442 cmd.Parameters.AddRange(arrSqlPara);
443 cmd.CommandText = sql;
444 SqlDataAdapter da = new SqlDataAdapter(cmd);
445 DataSet ds = new DataSet();
446 da.Fill(ds);
447 da.Dispose();
448 da = null;
449 if (AutoClearConnection)
450 {
451 ClearConnection();
452 }
453 List<T> list = null;
454 if (ds.Tables.Count > 0)
455 {
456 list = ds.Tables[0].DataTableToList<T>(); //DataTable转换为实体对象列表
457 }
458 return list;
459 }
460
461 #endregion
462
463 #region 查询多条数据
464
465 /// <summary>
466 /// 查询多条数据
467 /// </summary>
468 /// <typeparam name="T">类名</typeparam>
469 /// <param name="field">查询字段</param>
470 /// <param name="where">筛选条件</param>
471 /// <param name="orderBy">排序</param>
472 /// <returns></returns>
473 public List<T> FindList<T>(Expression<Func<T, object>> field = null, Expression<Func<T, bool>> where = null, Expression<Func<IQueryable<T>, IOrderedQueryable<T>>> orderBy = null) where T : class
474 {
475 SqlParameter[] arrSqlPara = new SqlParameter[] { };
476 var sql = GetFindListSql<T>(field, where, orderBy, ref arrSqlPara);
477 CreateConnection();
478 SqlCommand cmd = new SqlCommand();
479 cmd.Connection = conn;
480 cmd.Parameters.AddRange(arrSqlPara);
481 cmd.CommandText = sql;
482 SqlDataAdapter da = new SqlDataAdapter(cmd);
483 DataSet ds = new DataSet();
484 da.Fill(ds);
485 da.Dispose();
486 da = null;
487 if (AutoClearConnection)
488 {
489 ClearConnection();
490 }
491 List<T> list = null;
492 if (ds.Tables.Count > 0)
493 {
494 list = ds.Tables[0].DataTableToList<T>(); //DataTable转换为实体对象列表
495 }
496 return list;
497 }
498
499 #endregion
500
501 #region 查询单条数据
502
503 /// <summary>
504 /// 查询单条数据
505 /// </summary>
506 /// <typeparam name="T">类名</typeparam>
507 /// <param name="field">查询字段</param>
508 /// <param name="where">筛选条件</param>
509 /// <returns></returns>
510 public T Find<T>(Expression<Func<T, object>> field = null, Expression<Func<T, bool>> where = null) where T : class
511 {
512 SqlParameter[] arrSqlPara = new SqlParameter[] { };
513 var sql = GetFindSql<T>(field, where, ref arrSqlPara);
514 CreateConnection();
515 SqlCommand cmd = new SqlCommand();
516 cmd.Connection = conn;
517 cmd.Parameters.AddRange(arrSqlPara);
518 cmd.CommandText = sql;
519 SqlDataAdapter da = new SqlDataAdapter(cmd);
520 DataSet ds = new DataSet();
521 da.Fill(ds);
522 da.Dispose();
523 da = null;
524 if (AutoClearConnection)
525 {
526 ClearConnection();
527 }
528 T model = null;
529 if (ds.Tables.Count > 0)
530 {
531 if (ds.Tables[0].Rows.Count > 0)
532 {
533 model = ds.Tables[0].Rows[0].DataRowToEntity<T>(); //DataRow转换为实体对象
534 }
535 }
536 return model;
537 }
538
539 #endregion
540
541 #region 返回行数
542
543 /// <summary>
544 /// 返回行数
545 /// </summary>
546 /// <typeparam name="T">类名</typeparam>
547 /// <param name="where">筛选条件</param>
548 /// <returns></returns>
549 public int Count<T>(Expression<Func<T, bool>> where = null) where T : class
550 {
551 var cnt = 0;
552 SqlParameter[] arrSqlPara = new SqlParameter[] { };
553 var sql = GetCountSql<T>(where, ref arrSqlPara);
554 CreateConnection();
555 SqlCommand cmd = new SqlCommand();
556 cmd.Connection = conn;
557 cmd.Parameters.AddRange(arrSqlPara);
558 cmd.CommandText = sql;
559 SqlDataAdapter da = new SqlDataAdapter(cmd);
560 DataSet ds = new DataSet();
561 da.Fill(ds);
562 da.Dispose();
563 da = null;
564 if (AutoClearConnection)
565 {
566 ClearConnection();
567 }
568 if (ds.Tables.Count > 0)
569 {
570 if (ds.Tables[0].Rows.Count > 0)
571 {
572 cnt = ds.Tables[0].Rows[0][0].ToInt32();
573 }
574 }
575 return cnt;
576 }
577
578 #endregion
579
580 #region 是否存在数据行
581
582 /// <summary>
583 /// 是否存在数据行
584 /// </summary>
585 /// <typeparam name="T">类名</typeparam>
586 /// <param name="where">筛选条件</param>
587 /// <returns></returns>
588 public bool Exist<T>(Expression<Func<T, bool>> where = null) where T : class
589 {
590 var cnt = Count(where);
591 return cnt > 0;
592 }
593
594 #endregion
595
596 #endregion
597
598 #region ExecuteNonQuery
599
600 /// <summary>
601 /// ExecuteNonQuery
602 /// </summary>
603 /// <typeparam name="T">类名</typeparam>
604 /// <param name="sql">sql语句</param>
605 /// <param name="arrSqlPara">SqlParameter参数</param>
606 /// <returns></returns>
607 public int ExecuteNonQuery<T>(string sql, params SqlParameter[] arrSqlPara) where T : class
608 {
609 int affectedRows = 0;
610 CreateConnection();
611 SqlCommand cmd = new SqlCommand();
612 cmd.Connection = conn;
613 cmd.Parameters.AddRange(arrSqlPara);
614 cmd.CommandText = sql;
615 affectedRows = cmd.ExecuteNonQuery();
616 if (AutoClearConnection)
617 {
618 ClearConnection();
619 }
620 return affectedRows;
621 }
622
623 #endregion
624
625 #region ExecuteQuery
626
627 /// <summary>
628 /// ExecuteQuery
629 /// </summary>
630 /// <typeparam name="T">类名</typeparam>
631 /// <param name="sql">sql语句</param>
632 /// <param name="arrSqlPara">SqlParameter参数</param>
633 /// <returns></returns>
634 public DataSet ExecuteQuery<T>(string sql, params SqlParameter[] arrSqlPara) where T : class
635 {
636 CreateConnection();
637 SqlCommand cmd = new SqlCommand();
638 cmd.Connection = conn;
639 cmd.Parameters.AddRange(arrSqlPara);
640 cmd.CommandText = sql;
641 SqlDataAdapter da = new SqlDataAdapter(cmd);
642 DataSet ds = new DataSet();
643 da.Fill(ds);
644 da.Dispose();
645 da = null;
646 if (AutoClearConnection)
647 {
648 ClearConnection();
649 }
650 return ds;
651 }
652
653 #endregion
654
655 #endregion
656 }
针对哪个数据库操作，需要在配置文件中配置连接字符串，可以配置若干个。
1 public static class DbConnStrKey
2 {
3 internal static string MaiDb
4 {
5 get
6 {
7 return "MaiDb".ValueOfConnectionStrings();
8 }
9 }
10 }
访问数据库的公用入口
1 /// <summary>View Code
2 /// 用于单个数据库操作
3 /// </summary>
4 public class QueryService
5 {
6 /// <summary>
7 /// 数据库名
8 /// </summary>
9 public static DbServiceBase MaiDb
10 {
11 get
12 {
13 return new DbServiceBase(DbConnStrKey.MaiDb);
14 }
15 }
16 }
17
18 /// <summary>
19 /// 用于一组数据库操作
20 /// </summary>
21 public class UnitService
22 {
23 /// <summary>
24 /// 数据库名
25 /// </summary>
26 public static DbServiceBase MaiDb
27 {
28 get
29 {
30 return new DbServiceBase(DbConnStrKey.MaiDb, false);
31 }
32 }
33
34 }
以下是Demo
增
1 public static int Add(object model)
2 {
3 return QueryService.MaiDb.Create<SYS_Resource>(model);
4 }
exec sp_executesql N'insert into SYS_Resource (ResoId,ResoParentId,ResoType,ResoName,ResoUrl,ResoDesc,ResoIsShow,ResoOrder) values (@ResoId,@ResoParentId,@ResoType,@ResoName,@ResoUrl,@ResoDesc,@ResoIsShow,@ResoOrder)',N'@ResoId nvarchar(36),@ResoParentId nvarchar(36),@ResoType nvarchar(4),@ResoName nvarchar(4),@ResoUrl nvarchar(4000),@ResoDesc nvarchar(4000),@ResoIsShow bit,@ResoOrder int',@ResoId=N'cbaf5484-cdcb-4511-afee-0abb7937d31d',@ResoParentId=N'89fa1a64-6e49-42b9-b2a7-bd5fce0ea54e',@ResoType=N'page',@ResoName=N'添加资源',@ResoUrl=N'',@ResoDesc=N'',@ResoIsShow=1,@ResoOrder=0
go
删
1 public static ServiceResult Del(string ResoId)
2 {
3 var result = new ServiceResult();
4 using (var dbs = UnitService.MaiDb)
5 {
6 TryCatch(()=>{
7 var hasChildren = dbs.Exist<SYS_Resource>(w => w.ResoParentId == ResoId);
8 if (hasChildren)
9 {
10 result.IsFailure("删除失败！该资源有下属资源！");
11 return;
12 }
13 else
14 {
15 dbs.Delete<SYS_Resource>(w => w.ResoId == ResoId);
16 }
17 },result);
18 }
19 return result;
20 }
exec sp_executesql N'select count(*) from SYS_Resource where (ResoParentId = @para1)',N'@para1 nvarchar(36)',@para1=N'cbaf5484-cdcb-4511-afee-0abb7937d31d'
go
exec sp_executesql N'delete from SYS_Resource where (ResoId = @para1)',N'@para1 nvarchar(36)',@para1=N'cbaf5484-cdcb-4511-afee-0abb7937d31d'
go
查
1 public static IList<SYS_Resource> FindList()
2 {
3 return QueryService.MaiDb.FindList<SYS_Resource>();
4 }
改
1 public static int Update(object model, Expression<Func<SYS_Resource, bool>> where)
2 {
3 return QueryService.MaiDb.Update<SYS_Resource>(model, where);
4 }
exec sp_executesql N'update SYS_Resource set ResoType = @ResoType,ResoName = @ResoName,ResoUrl = @ResoUrl,ResoDesc = @ResoDesc,ResoIsShow = @ResoIsShow,ResoOrder = @ResoOrder,ResoUpdateTime = @ResoUpdateTime where (ResoId = @para1)',N'@ResoType nvarchar(4),@ResoName nvarchar(4),@ResoUrl nvarchar(4000),@ResoDesc nvarchar(4000),@ResoIsShow bit,@ResoOrder int,@ResoUpdateTime datetime,@para1 nvarchar(36)',@ResoType=N'page',@ResoName=N'编辑角色',@ResoUrl=N'',@ResoDesc=N'',@ResoIsShow=0,@ResoOrder=0,@ResoUpdateTime='2017-08-01 15:37:47.597',@para1=N'3e2fb3cb-92f6-44cb-80a1-3b15157d53c5'
go
一组数据库操作
1 public static EditModel Find(string ResoId)
2 {
3 EditModel editModel = new EditModel();
4 using (var dbs = UnitService.MaiDb)
5 {
6 editModel.model = dbs.Find<SYS_Resource>(s => s, w => w.ResoId == ResoId);
7 if (!editModel.model.IsNull())
8 {
9 var ds = dbs.ExecuteQuery<SYS_Resource>("select ResoName from SYS_Resource where ResoId = @ResoId", new SqlParameter[1] { new SqlParameter("@ResoId", editModel.model.ResoParentId) });
10 if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
11 {
12 editModel.ResoParentName = ds.Tables[0].Rows[0][0].ToString();
13 }
14 }
15 }
16 return editModel;
17 }
exec sp_executesql N'select * from SYS_Resource where (ResoId = @para1)',N'@para1 nvarchar(36)',@para1=N'89fa1a64-6e49-42b9-b2a7-bd5fce0ea54e'
go
exec sp_executesql N'select ResoName from SYS_Resource where ResoId = @ResoId',N'@ResoId nvarchar(36)',@ResoId=N'74f6bb3b-8995-49b7-9b4b-db0e34aac304'
go
事务操作
1 using (var dbs = UnitService.MaiDb)
2 {
3 try
4 {
5 dbs.BeginTransaction();
6 //你的代码...
7 dbs.CommitTransaction();
8 }
9 catch (Exception ex)
10 {
11 dbs.RollbackTransaction();
12 }
13 }
Lambda如何转换SQL语句，请参考lambda表达式转换sql
码农的世界，不知道可以走多远，坚持多久，好好珍惜当下。