Dapper 基本使用

Dapper 基本使用

项目下引用右键->管理NuGet程序包->浏览，搜索Dapper

 

 

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Linq;  
  7.   
  8. namespace Dapper.Repository  
  9. {  
  10.     public class DapperDemo  
  11.     {  
  12.         public static string ConnectionString  
  13.         {  
  14.             get  
  15.             {  
  16.                 string _connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();  
  17.                 return _connectionString;  
  18.             }  
  19.         }  
  20.   
  21.         public SqlConnection OpenConnection()  
  22.         {  
  23.             SqlConnection connection = new SqlConnection(ConnectionString);  
  24.             connection.Open();  
  25.             return connection;  
  26.         }  
  27.   
  28.         /// <summary>  
  29.         /// 添加  
  30.         /// </summary>  
  31.         /// <returns></returns>  
  32.         public bool Add()  
  33.         {  
  34.             int row = 0;  
  35.             ED_Data model = new ED_Data();  
  36.             model.TableName = "123";  
  37.             model.DataKey = "123";  
  38.             model.FieldName = "123";  
  39.             model.Value = "123";  
  40.             model.Reference = "123";  
  41.             model.Branch = 1;  
  42.             model.InActive = false;  
  43.             model.Updated = DateTime.Now;  
  44.   
  45.             string query = "INSERT INTO ED_Data(TableName,DataKey,FieldName,Value,Reference,Branch,InActive,Updated) VALUES (@TableName,@DataKey,@FieldName,@Value,@Reference,@Branch,@InActive,@Updated)";  
  46.             using (IDbConnection conn = OpenConnection())  
  47.             {  
  48.                 row = conn.Execute(query, model);  
  49.             }  
  50.             if (row > 0)  
  51.                 return true;  
  52.             else  
  53.                 return false;  
  54.         }  
  55.   
  56.         /// <summary>  
  57.         /// 修改  
  58.         /// </summary>  
  59.         /// <returns></returns>  
  60.         public int Update()  
  61.         {  
  62.             int row = 0;  
  63.             ED_Data model = new ED_Data();  
  64.             model.TableName = "123";  
  65.             model.DataKey = "123";  
  66.             model.Updated = DateTime.Now;  
  67.             using (IDbConnection conn = OpenConnection())  
  68.             {  
  69.                 const string query = "UPDATE ED_Data SET DataKey=@DataKey,Updated=@Updated WHERE TableName=@TableName";  
  70.                 row = conn.Execute(query, model);  
  71.             }  
  72.             return row;  
  73.         }  
  74.   
  75.         /// <summary>  
  76.         /// 删除  
  77.         /// </summary>  
  78.         /// <returns></returns>  
  79.         public int Delete()  
  80.         {  
  81.             int row = 0;  
  82.             ED_Data model = new ED_Data();  
  83.             model.TableName = "123";  
  84.             using (IDbConnection conn = OpenConnection())  
  85.             {  
  86.                 const string query = "DELETE FROM ED_Data WHERE TableName=@TableName";  
  87.                 row = conn.Execute(query, model);  
  88.             }  
  89.             return row;  
  90.         }  
  91.   
  92.         /// <summary>  
  93.         /// 查询一条数据  
  94.         /// </summary>  
  95.         /// <param name="columnCatId"></param>  
  96.         /// <returns>ED_Data</returns>  
  97.         public ED_Data GetModel(string TableName)  
  98.         {  
  99.             using (IDbConnection conn = OpenConnection())  
  100.             {  
  101.                 const string query = "SELECT * FROM ED_Data WHERE TableName = @TableName";  
  102.                 return conn.Query<ED_Data>(query, new { tableName = TableName }).SingleOrDefault<ED_Data>();  
  103.             }  
  104.         }  
  105.   
  106.         /// <summary>  
  107.         /// 查询list集合  
  108.         /// </summary>  
  109.         /// <returns>List</returns>  
  110.         public List<ED_Data> GetED_DataList()  
  111.         {  
  112.             using (IDbConnection conn = OpenConnection())  
  113.             {  
  114.                 const string query = "SELECT * FROM ED_Data";  
  115.                 return conn.Query<ED_Data>(query, null).ToList();  
  116.             }  
  117.         }  
  118.   
  119.         /// <summary>  
  120.         /// 事务处理  
  121.         /// 删除  
  122.         /// </summary>  
  123.         /// <param name="cat"></param>  
  124.         /// <returns></returns>  
  125.         public int DeleteColumnCatAndColumn(ED_Data cat)  
  126.         {  
  127.             try  
  128.             {  
  129.                 using (IDbConnection conn = OpenConnection())  
  130.                 {  
  131.                     string delete1 = "DELETE FROM ED_Data WHERE TableName=@TableName";  
  132.                     string delete2 = "DELETE FROM ED_Data WHERE TableName=@TableName";  
  133.   
  134.                     IDbTransaction transaction = conn.BeginTransaction();  
  135.                     int row = conn.Execute(delete1, new { TableName = cat.TableName }, transaction, null, null);  
  136.                     row += conn.Execute(delete2, new { TableName = cat.TableName }, transaction, null, null);  
  137.                     transaction.Commit();  
  138.                     return row;  
  139.                 }  
  140.             }  
  141.             catch (Exception)  
  142.             {  
  143.                 throw;  
  144.             }  
  145.         }  
  146.           
  147.         /// <summary>  
  148.         /// 执行存储过程  
  149.         /// </summary>  
  150.         public void ExecuteStoredProcedure()  
  151.         {  
  152.             try  
  153.             {  
  154.                 DynamicParameters para = new DynamicParameters();  
  155.                 para.Add("@param1", 1);  
  156.                 para.Add("@param2", 2);  
  157.   
  158.                 using (IDbConnection conn = OpenConnection())  
  159.                 {  
  160.                     int row = conn.Execute("存储过程名称", para, null, null, CommandType.StoredProcedure);  
  161.                 }  
  162.             }  
  163.             catch (Exception)  
  164.             {  
  165.                 throw;  
  166.             }  
  167.         }  
  168.   
  169.         /// <summary>  
  170.         /// 批量添加  
  171.         /// </summary>  
  172.         public void InsertBatch()  
  173.         {  
  174.             try  
  175.             {  
  176.                 string sqlStr = "INSERT INTO ED_Data(TableName,DataKey,FieldName,Value,Reference,Branch,InActive,Updated) VALUES (@TableName,@DataKey,@FieldName,@Value,@Reference,@Branch,@InActive,@Updated)";  
  177.                 using (IDbConnection conn = OpenConnection())  
  178.                 {  
  179.                     conn.Execute(sqlStr, new[]   
  180.                     {  
  181.                         new { TableName = "user1", DataKey = "a", FieldName = "name", Value = "000001", Reference = "", Branch = "", InActive = "", Updated = DateTime.Now },  
  182.                         new { TableName = "user2", DataKey = "b", FieldName = "age", Value = "000002", Reference = "", Branch= "", InActive = "", Updated = DateTime.Now },  
  183.                         new { TableName = "user3", DataKey = "c", FieldName = "phone", Value = "000003", Reference= "", Branch= "", InActive= "", Updated= DateTime.Now },  
  184.                     }, null, null, null);  
  185.                 }  
  186.             }  
  187.             catch (Exception)  
  188.             {  
  189.                 throw;  
  190.             }  
  191.         }  
  192.     }  
  193. }  

 

 

  1. sing System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Linq;  
  7.   
  8. namespace Dapper.Repository  
  9. {  
  10.     public class ED_Data  
  11.     {  
  12.         public string TableName { get; set; }  
  13.   
  14.         public string DataKey { get; set; }  
  15.   
  16.         public string FieldName { get; set; }  
  17.   
  18.         public string Value { get; set; }  
  19.   
  20.         public string Reference { get; set; }  
  21.   
  22.         public int Branch { get; set; }  
  23.   
  24.         public bool InActive { get; set; }  
  25.   
  26.         public DateTime Updated { get; set; }  
  27.     }  
  28. }  

Base基类

 

 

[csharp] view plain copy
 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Linq;  
  7.   
  8. namespace Dapper.Repository  
  9. {  
  10.     public class DapperDemo  
  11.     {  
  12.         public static string ConnectionString  
  13.         {  
  14.             get  
  15.             {  
  16.                 string _connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();  
  17.                 return _connectionString;  
  18.             }  
  19.         }  
  20.   
  21.         public SqlConnection OpenConnection()  
  22.         {  
  23.             SqlConnection connection = new SqlConnection(ConnectionString);  
  24.             connection.Open();  
  25.             return connection;  
  26.         }  
  27.   
  28.         /// <summary>  
  29.         /// 添加  
  30.         /// </summary>  
  31.         /// <returns></returns>  
  32.         public bool Add()  
  33.         {  
  34.             int row = 0;  
  35.             ED_Data model = new ED_Data();  
  36.             model.TableName = "123";  
  37.             model.DataKey = "123";  
  38.             model.FieldName = "123";  
  39.             model.Value = "123";  
  40.             model.Reference = "123";  
  41.             model.Branch = 1;  
  42.             model.InActive = false;  
  43.             model.Updated = DateTime.Now;  
  44.   
  45.             string query = "INSERT INTO ED_Data(TableName,DataKey,FieldName,Value,Reference,Branch,InActive,Updated) VALUES (@TableName,@DataKey,@FieldName,@Value,@Reference,@Branch,@InActive,@Updated)";  
  46.             using (IDbConnection conn = OpenConnection())  
  47.             {  
  48.                 row = conn.Execute(query, model);  
  49.             }  
  50.             if (row > 0)  
  51.                 return true;  
  52.             else  
  53.                 return false;  
  54.         }  
  55.   
  56.         /// <summary>  
  57.         /// 修改  
  58.         /// </summary>  
  59.         /// <returns></returns>  
  60.         public int Update()  
  61.         {  
  62.             int row = 0;  
  63.             ED_Data model = new ED_Data();  
  64.             model.TableName = "123";  
  65.             model.DataKey = "123";  
  66.             model.Updated = DateTime.Now;  
  67.             using (IDbConnection conn = OpenConnection())  
  68.             {  
  69.                 const string query = "UPDATE ED_Data SET DataKey=@DataKey,Updated=@Updated WHERE TableName=@TableName";  
  70.                 row = conn.Execute(query, model);  
  71.             }  
  72.             return row;  
  73.         }  
  74.   
  75.         /// <summary>  
  76.         /// 删除  
  77.         /// </summary>  
  78.         /// <returns></returns>  
  79.         public int Delete()  
  80.         {  
  81.             int row = 0;  
  82.             ED_Data model = new ED_Data();  
  83.             model.TableName = "123";  
  84.             using (IDbConnection conn = OpenConnection())  
  85.             {  
  86.                 const string query = "DELETE FROM ED_Data WHERE TableName=@TableName";  
  87.                 row = conn.Execute(query, model);  
  88.             }  
  89.             return row;  
  90.         }  
  91.   
  92.         /// <summary>  
  93.         /// 查询一条数据  
  94.         /// </summary>  
  95.         /// <param name="columnCatId"></param>  
  96.         /// <returns>ED_Data</returns>  
  97.         public ED_Data GetModel(string TableName)  
  98.         {  
  99.             using (IDbConnection conn = OpenConnection())  
  100.             {  
  101.                 const string query = "SELECT * FROM ED_Data WHERE TableName = @TableName";  
  102.                 return conn.Query<ED_Data>(query, new { tableName = TableName }).SingleOrDefault<ED_Data>();  
  103.             }  
  104.         }  
  105.   
  106.         /// <summary>  
  107.         /// 查询list集合  
  108.         /// </summary>  
  109.         /// <returns>List</returns>  
  110.         public List<ED_Data> GetED_DataList()  
  111.         {  
  112.             using (IDbConnection conn = OpenConnection())  
  113.             {  
  114.                 const string query = "SELECT * FROM ED_Data";  
  115.                 return conn.Query<ED_Data>(query, null).ToList();  
  116.             }  
  117.         }  
  118.   
  119.         /// <summary>  
  120.         /// 事务处理  
  121.         /// 删除  
  122.         /// </summary>  
  123.         /// <param name="cat"></param>  
  124.         /// <returns></returns>  
  125.         public int DeleteColumnCatAndColumn(ED_Data cat)  
  126.         {  
  127.             try  
  128.             {  
  129.                 using (IDbConnection conn = OpenConnection())  
  130.                 {  
  131.                     string delete1 = "DELETE FROM ED_Data WHERE TableName=@TableName";  
  132.                     string delete2 = "DELETE FROM ED_Data WHERE TableName=@TableName";  
  133.   
  134.                     IDbTransaction transaction = conn.BeginTransaction();  
  135.                     int row = conn.Execute(delete1, new { TableName = cat.TableName }, transaction, null, null);  
  136.                     row += conn.Execute(delete2, new { TableName = cat.TableName }, transaction, null, null);  
  137.                     transaction.Commit();  
  138.                     return row;  
  139.                 }  
  140.             }  
  141.             catch (Exception)  
  142.             {  
  143.                 throw;  
  144.             }  
  145.         }  
  146.           
  147.         /// <summary>  
  148.         /// 执行存储过程  
  149.         /// </summary>  
  150.         public void ExecuteStoredProcedure()  
  151.         {  
  152.             try  
  153.             {  
  154.                 DynamicParameters para = new DynamicParameters();  
  155.                 para.Add("@param1", 1);  
  156.                 para.Add("@param2", 2);  
  157.   
  158.                 using (IDbConnection conn = OpenConnection())  
  159.                 {  
  160.                     int row = conn.Execute("存储过程名称", para, null, null, CommandType.StoredProcedure);  
  161.                 }  
  162.             }  
  163.             catch (Exception)  
  164.             {  
  165.                 throw;  
  166.             }  
  167.         }  
  168.   
  169.         /// <summary>  
  170.         /// 批量添加  
  171.         /// </summary>  
  172.         public void InsertBatch()  
  173.         {  
  174.             try  
  175.             {  
  176.                 string sqlStr = "INSERT INTO ED_Data(TableName,DataKey,FieldName,Value,Reference,Branch,InActive,Updated) VALUES (@TableName,@DataKey,@FieldName,@Value,@Reference,@Branch,@InActive,@Updated)";  
  177.                 using (IDbConnection conn = OpenConnection())  
  178.                 {  
  179.                     conn.Execute(sqlStr, new[]   
  180.                     {  
  181.                         new { TableName = "user1", DataKey = "a", FieldName = "name", Value = "000001", Reference = "", Branch = "", InActive = "", Updated = DateTime.Now },  
  182.                         new { TableName = "user2", DataKey = "b", FieldName = "age", Value = "000002", Reference = "", Branch= "", InActive = "", Updated = DateTime.Now },  
  183.                         new { TableName = "user3", DataKey = "c", FieldName = "phone", Value = "000003", Reference= "", Branch= "", InActive= "", Updated= DateTime.Now },  
  184.                     }, null, null, null);  
  185.                 }  
  186.             }  
  187.             catch (Exception)  
  188.             {  
  189.                 throw;  
  190.             }  
  191.         }  
  192.     }  
  193. }  

 

 

 

 

[csharp] view plain copy
 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Linq;  
  7.   
  8. namespace Dapper.Repository  
  9. {  
  10.     public class ED_Data  
  11.     {  
  12.         public string TableName { get; set; }  
  13.   
  14.         public string DataKey { get; set; }  
  15.   
  16.         public string FieldName { get; set; }  
  17.   
  18.         public string Value { get; set; }  
  19.   
  20.         public string Reference { get; set; }  
  21.   
  22.         public int Branch { get; set; }  
  23.   
  24.         public bool InActive { get; set; }  
  25.   
  26.         public DateTime Updated { get; set; }  
  27.     }  
  28. }  

 

 

 

Base基类

[csharp] view plain copy
 
  1. using Dapper.CoreLibrary;  
  2. using Dapper.Entity;  
  3. using System;  
  4. using System.Collections.Generic;  
  5. using System.Data;  
  6. using System.Linq;  
  7. using System.Reflection;  
  8. using System.Text;  
  9. using System.Transactions;  
  10.   
  11. namespace Dapper.Repository  
  12. {  
  13.     public abstract class RepositoryBase<T> : DbConnectionFactory, IRepositoryBase<T> where T : IEntityBase<T>  
  14.     {  
  15.         public RepositoryBase(IDbConnection db)  
  16.             : base(db)  
  17.         { }  
  18.   
  19.         /// <summary>  
  20.         ///   
  21.         /// </summary>  
  22.         /// <param name="model"></param>  
  23.         /// <returns></returns>  
  24.         public virtual int Add(T model)  
  25.         {  
  26.             int result = 0;  
  27.             try  
  28.             {  
  29.                 var ps = model.GetType().GetProperties();  
  30.                 List<string> @colms = new List<string>();  
  31.                 List<string> @params = new List<string>();  
  32.                 foreach (var p in ps)  
  33.                 {  
  34.                     if (!p.CustomAttributes.Any(x => x.AttributeType == typeof(PrimaryKeyAttribute)) && !p.CustomAttributes.Any(x => x.AttributeType == typeof(DBIgnoreAttribute)))  
  35.                     {  
  36.                         @colms.Add(string.Format("[{0}]", p.Name));  
  37.                         @params.Add(string.Format("@{0}", p.Name));  
  38.                     }  
  39.                 }  
  40.                 var sql = string.Format("INSERT INTO [{0}] ({1}) VALUES({2}); SELECT @@IDENTITY;", typeof(T).Name, string.Join(", ", @colms), string.Join(", ", @params));  
  41.                 result = _conn.ExecuteScalar<int>(sql, model);  
  42.             }  
  43.             catch (Exception ex)  
  44.             {  
  45.                 throw;  
  46.             }  
  47.             return result;  
  48.         }  
  49.   
  50.         /// <summary>  
  51.         ///   
  52.         /// </summary>  
  53.         /// <param name="listModel"></param>  
  54.         public virtual void Add(List<T> listModel)  
  55.         {  
  56.             try  
  57.             {  
  58.                 using (var scope = new TransactionScope())  
  59.                 {  
  60.                     listModel.ForEach(model =>  
  61.                     {  
  62.                         var ps = model.GetType().GetProperties();  
  63.                         List<string> @colms = new List<string>();  
  64.                         List<string> @params = new List<string>();  
  65.                         foreach (var p in ps)  
  66.                         {  
  67.                             if (!p.CustomAttributes.Any(x => x.AttributeType == typeof(PrimaryKeyAttribute)) && !p.CustomAttributes.Any(x => x.AttributeType == typeof(DBIgnoreAttribute)))  
  68.                             {  
  69.                                 @colms.Add(string.Format("[{0}]", p.Name));  
  70.                                 @params.Add(string.Format("@{0}", p.Name));  
  71.                             }  
  72.                         }  
  73.                         var sql = string.Format("INSERT INTO [{0}] ({1}) VALUES({2}); SELECT @@IDENTITY;", typeof(T).Name, string.Join(", ", @colms), string.Join(", ", @params));  
  74.                         _conn.ExecuteScalar<int>(sql, model);  
  75.                     });  
  76.                     scope.Complete();  
  77.                 }  
  78.             }  
  79.             catch (Exception ex)  
  80.             {  
  81.             }  
  82.         }  
  83.   
  84.         /// <summary>  
  85.         ///   
  86.         /// </summary>  
  87.         /// <param name="model"></param>  
  88.         /// <returns></returns>  
  89.         public virtual int AddWithGuid(T model)  
  90.         {  
  91.             int result = 0;  
  92.             try  
  93.             {  
  94.                 var ps = model.GetType().GetProperties();  
  95.                 List<string> @colms = new List<string>();  
  96.                 List<string> @params = new List<string>();  
  97.                 foreach (var p in ps)  
  98.                 {  
  99.                     if (!p.CustomAttributes.Any(x => x.AttributeType == typeof(DBIgnoreAttribute)))  
  100.                     {  
  101.                         @colms.Add(string.Format("[{0}]", p.Name));  
  102.                         @params.Add(string.Format("@{0}", p.Name));  
  103.                     }  
  104.                 }  
  105.                 var sql = string.Format("INSERT INTO [{0}] ({1}) VALUES({2});", typeof(T).Name, string.Join(", ", @colms), string.Join(", ", @params));  
  106.                 result = _conn.Execute(sql, model);  
  107.             }  
  108.             catch (Exception ex)  
  109.             {  
  110.                 throw;  
  111.             }  
  112.             return result;  
  113.         }  
  114.   
  115.         /// <summary>  
  116.         ///   
  117.         /// </summary>  
  118.         /// <param name="model"></param>  
  119.         public virtual void Update(T model)  
  120.         {  
  121.             PropertyInfo pkInfo = null;  
  122.             var ps = model.GetType().GetProperties();  
  123.             List<string> @params = new List<string>();  
  124.             foreach (var p in ps)  
  125.             {  
  126.                 if (p.CustomAttributes.Any(x => x.AttributeType == typeof(DBIgnoreAttribute)))  
  127.                 {  
  128.                     continue;  
  129.                 }  
  130.                 if (p.CustomAttributes.Any(x => x.AttributeType == typeof(PrimaryKeyAttribute)))  
  131.                 {  
  132.                     pkInfo = p;  
  133.                 }  
  134.                 else  
  135.                 {  
  136.                     @params.Add(string.Format("[{0}]=@{0}", p.Name));  
  137.                 }  
  138.             }  
  139.             var sql = string.Format("UPDATE [{0}] SET {1} WHERE [{2}] = @{2}", typeof(T).Name, string.Join(", ", @params), pkInfo.Name);  
  140.             _conn.Execute(sql, model);  
  141.         }  
  142.   
  143.         /// <summary>  
  144.         ///   
  145.         /// </summary>  
  146.         /// <param name="listModel"></param>  
  147.         public virtual void Update(List<T> listModel)  
  148.         {  
  149.             using (var scope = new TransactionScope())  
  150.             {  
  151.                 listModel.ForEach(model =>  
  152.                 {  
  153.                     PropertyInfo pkInfo = null;  
  154.                     var ps = model.GetType().GetProperties();  
  155.                     List<string> @params = new List<string>();  
  156.                     foreach (var p in ps)  
  157.                     {  
  158.                         if (p.CustomAttributes.Any(x => x.AttributeType == typeof(DBIgnoreAttribute)))  
  159.                         {  
  160.                             continue;  
  161.                         }  
  162.                         if (p.CustomAttributes.Any(x => x.AttributeType == typeof(PrimaryKeyAttribute)))  
  163.                         {  
  164.                             pkInfo = p;  
  165.                         }  
  166.                         else  
  167.                         {  
  168.                             @params.Add(string.Format("[{0}] = @{0}", p.Name));  
  169.                         }  
  170.                     }  
  171.                     var sql = string.Format("UPDATE [{0}] SET {1} WHERE [{2}] = @{2}", typeof(T).Name, string.Join(", ", @params), pkInfo.Name);  
  172.                     _conn.Execute(sql, model);  
  173.                 });  
  174.                 scope.Complete();  
  175.             }  
  176.         }  
  177.   
  178.         /// <summary>  
  179.         ///   
  180.         /// </summary>  
  181.         /// <param name="primaryValue">主键ID</param>  
  182.         /// <param name="tableName">表名</param>  
  183.         /// <returns></returns>  
  184.         public virtual T GetModel(string primaryValue, string tableName = "")  
  185.         {  
  186.             try  
  187.             {  
  188.                 string primaryWhere = string.Empty;  
  189.                 var ps = typeof(T).GetProperties();  
  190.                 if (string.IsNullOrEmpty(tableName))  
  191.                 {  
  192.                     tableName = typeof(T).Name;  
  193.                 }  
  194.                 var primary = ps.Single(p => p.CustomAttributes.FirstOrDefault(c => c.AttributeType == typeof(PrimaryKeyAttribute)) != null);  
  195.   
  196.                 primaryWhere = (string.Format("[{0}] = @primarykey", primary.Name));  
  197.   
  198.                 var sql = string.Format("SELECT * FROM [{0}] WHERE {1}", tableName, primaryWhere);  
  199.   
  200.                 return _conn.Query<T>(sql, new { primarykey = primaryValue }).FirstOrDefault();  
  201.             }  
  202.             catch (Exception)  
  203.             {  
  204.   
  205.                 throw;  
  206.             }  
  207.         }  
  208.   
  209.         /// <summary>  
  210.         ///   
  211.         /// </summary>  
  212.         /// <param name="strWhere">where条件</param>  
  213.         /// <param name="tableName">表名</param>  
  214.         /// <returns></returns>  
  215.         public virtual T GetModelQuery(string strWhere, string tableName = "")  
  216.         {  
  217.             try  
  218.             {  
  219.                 var sql = string.Format("SELECT * FROM [{0}] WHERE {1}", tableName, strWhere);  
  220.                 return _conn.Query<T>(sql, new { where = strWhere }).FirstOrDefault();  
  221.             }  
  222.             catch (Exception)  
  223.             {  
  224.                 throw;  
  225.             }  
  226.         }  
  227.   
  228.         /// <summary>  
  229.         /// 根据主键删除  
  230.         /// </summary>  
  231.         /// <param name="primaryValue"></param>  
  232.         public virtual void Delete(string primaryValue)  
  233.         {  
  234.             try  
  235.             {  
  236.                 string primaryWhere = string.Empty;  
  237.                 var ps = typeof(T).GetProperties();  
  238.                 var primary = ps.Single(p => p.CustomAttributes.FirstOrDefault(c => c.AttributeType == typeof(PrimaryKeyAttribute)) != null);  
  239.                 var sql = string.Format("DELETE FROM [{0}] WHERE {1} = @primarykey", typeof(T).Name, primary.Name);  
  240.                 _conn.Execute(sql, new { primarykey = primaryValue });  
  241.             }  
  242.             catch (Exception)  
  243.             {  
  244.                 throw;  
  245.             }  
  246.         }  
  247.   
  248.         /// <summary>  
  249.         ///   
  250.         /// </summary>  
  251.         /// <param name="strWhere"></param>  
  252.         public void DeleteStrWhere(string strWhere)  
  253.         {  
  254.             try  
  255.             {  
  256.                 var sql = string.Format("DELETE FROM [{0}] WHERE {1}", typeof(T).Name, strWhere);  
  257.                 _conn.Execute(sql);  
  258.             }  
  259.             catch (Exception)  
  260.             {  
  261.                 throw;  
  262.             }  
  263.         }  
  264.   
  265.         /// <summary>  
  266.         ///   
  267.         /// </summary>  
  268.         /// <param name="strWhere"></param>  
  269.         /// <param name="tableName"></param>  
  270.         /// <returns></returns>  
  271.         public virtual List<T> GetList(string strWhere, string tableName = "")  
  272.         {  
  273.             try  
  274.             {  
  275.                 if (string.IsNullOrEmpty(tableName))  
  276.                     tableName = typeof(T).Name;  
  277.                 var sql = string.Format("SELECT * FROM [{0}] " + (strWhere == "" ? "" : " WHERE " + " {1} "), tableName, strWhere);  
  278.                 return _conn.Query<T>(sql).ToList();  
  279.             }  
  280.             catch (Exception)  
  281.             {  
  282.                 throw;  
  283.             }  
  284.         }  
  285.   
  286.         /// <summary>  
  287.         ///   
  288.         /// </summary>  
  289.         /// <param name="param"></param>  
  290.         /// <returns></returns>  
  291.         public virtual PagerListResult<List<T>> GetPageList(PagerRequestParam param)  
  292.         {  
  293.             PagerListResult<List<T>> result = null;  
  294.             List<T> list = new List<T>();  
  295.             int pageTotal = 1;  
  296.             int recordTotal = 0;  
  297.             int startIndex = 1;  
  298.             int endIndex = param.PageSize;  
  299.             try  
  300.             {  
  301.                 if (param.PageIndex - 1 > 0)  
  302.                 {  
  303.                     startIndex = (param.PageIndex - 1 <= 0 ? 1 : param.PageIndex - 1) * param.PageSize + 1;  
  304.                     endIndex = param.PageIndex * param.PageSize;  
  305.                 }  
  306.   
  307.                 if (string.IsNullOrEmpty(param.TableName))  
  308.                     param.TableName = typeof(T).Name;  
  309.                 StringBuilder strSql = new StringBuilder();  
  310.                 strSql.Append("SELECT * FROM ( ");  
  311.                 strSql.Append(" SELECT ROW_NUMBER() OVER (");  
  312.                 if (!string.IsNullOrEmpty(param.OrderBy))  
  313.                 {  
  314.                     strSql.Append("ORDER BY T." + param.OrderBy);  
  315.                 }  
  316.                 else  
  317.                 {  
  318.                     strSql.Append("ORDER BY T.ID DESC");  
  319.                 }  
  320.                 strSql.Append(")AS Row, T.*  FROM " + param.TableName + " T ");  
  321.                 if (!string.IsNullOrEmpty(param.StrWhere))  
  322.                 {  
  323.                     strSql.Append(" WHERE " + param.StrWhere);  
  324.                 }  
  325.                 strSql.Append(" ) TT");  
  326.                 strSql.AppendFormat(" WHERE TT.Row BETWEEN {0} AND {1}", startIndex, endIndex);  
  327.   
  328.                 list = _conn.Query<T>(strSql.ToString(), param.StrWhere).ToList();  
  329.                 if (list.Count > 0)  
  330.                 {  
  331.                     recordTotal = this.GetRecordCount(param.StrWhere, param.TableName);  
  332.                     pageTotal = PagerRequestParam.Tool.PageTotal(param);  
  333.                 }  
  334.                 result = new PagerListResult<List<T>>(list, pageTotal, recordTotal);  
  335.             }  
  336.             catch (Exception ex)  
  337.             {  
  338.                 result = new PagerListResult<List<T>>(ex);  
  339.             }  
  340.             return result;  
  341.         }  
  342.   
  343.         /// <summary>  
  344.         /// 事务处理  
  345.         /// Demo  
  346.         /// </summary>  
  347.         /// <returns></returns>  
  348.         public int DeleteTransaction()  
  349.         {  
  350.             try  
  351.             {  
  352.                 const string delete1 = "DELETE FROM ED_Data WHERE TableName=@TableName";  
  353.                 const string delete2 = "DELETE FROM ED_Data WHERE TableName=@TableName";  
  354.   
  355.                 IDbTransaction transaction = _conn.BeginTransaction();  
  356.                 int row = _conn.Execute(delete1, new { TableName = "user" }, transaction, null, null);  
  357.                 row += _conn.Execute(delete2, new { TableName = "customer" }, transaction, null, null);  
  358.                 transaction.Commit();  
  359.                 return row;  
  360.             }  
  361.             catch (Exception)  
  362.             {  
  363.                 throw;  
  364.             }  
  365.         }  
  366.   
  367.         /// <summary>  
  368.         /// 获取记录数  
  369.         /// </summary>  
  370.         /// <param name="strWhere">Where条件</param>  
  371.         /// <returns></returns>  
  372.         public virtual int GetRecordCount(string strWhere, string tableName = "")  
  373.         {  
  374.             int count = 0;  
  375.             try  
  376.             {  
  377.                 if (string.IsNullOrEmpty(tableName))  
  378.                     tableName = typeof(T).Name;  
  379.                 StringBuilder strSql = new StringBuilder();  
  380.                 strSql.Append("SELECT COUNT(1) FROM " + tableName);  
  381.                 if (!string.IsNullOrEmpty(strWhere))  
  382.                 {  
  383.                     strSql.Append(" WHERE " + strWhere);  
  384.                 }  
  385.                 count = _conn.ExecuteScalar<int>(strSql.ToString());  
  386.             }  
  387.             catch (Exception)  
  388.             {  
  389.   
  390.                 throw;  
  391.             }  
  392.             return count;  
  393.         }  
  394.     }  
  395. }  
posted @ 2018-01-11 09:19  家族帝国  阅读(357)  评论(3)  编辑  收藏  举报