[ASP.NET]Dapper小型ORM的使用

先介绍下Dapper这个ORM
1，Dapper是一个轻型的ORM类。代码就一个SqlMapper.cs文件，编译后就40K的一个很小的Dll.  小型ORM
2，Dapper很快。Dapper的速度接近与IDataReader，取列表的数据超过了DataTable。 速度快
3，Dapper支持什么数据库。Dapper支持Mysql,SqlLite,Mssql2000,Mssql2005,Oracle等一系列的数据库  支持多数据库
4，Dapper的r支持多表并联的对象。支持一对多 多对多的关系。并且没侵入性，想用就用，不想用就不用。无XML无属性。代码以前怎么写现在还怎么写。 灵活性高
5，Dapper原理通过Emit反射IDataReader的序列队列，来快速的得到和产生对象。性能实在高高高。 性能高
6，Dapper支持net2.0,3.0,3.5,4.0。【如果想在Net2.0下使用，可以去网上找一下Net2.0下如何配置运行Net3.5即可。】 支持多个.net版本
7，Dapper语法十分简单。并且无须迁就数据库的设计。 语法简单，可扩展性强
Dapper官网：https://code.google.com/p/dapper-dot-net/
Dapper简单使用：http://www.cnblogs.com/wywnet/p/3422150.html
  SqlMapper.cs 是最基础的底层文件，为了更好的运用，先对Dapper进行扩展，这里写一个Dapper的扩展类（这里只列出主要的类，一些辅助类就不列出了）

  Dapper扩展类，DapperEx.cs

1 public static class DapperEx 2 { 3 4 5 /// <summary> 6 /// 插入数据 7 /// </summary> 8 /// <typeparam name="T"></typeparam> 9 /// <param name="dbs"></param> 10 /// <param name="t"></param> 11 /// <param name="transaction"></param> 12 /// <param name="commandTimeout"></param> 13 /// <returns></returns> 14 public static int Insert<T>(this DbBase dbs, T t, IDbTransaction transaction = null, int? commandTimeout = null) where T : class 15 { 16 var db = dbs.DbConnecttion; 17 var sql = SqlQuery<T>.Builder(dbs); 18 var flag = db.Execute(sql.InsertSql, t, transaction, commandTimeout); 19 int KeyID = 0; 20 SetIdentity(db, (id) => { KeyID = id; }, transaction); 21 return KeyID; 22 //return flag == 1; 23 } 24 25 /// <summary> 26 /// 批量插入数据 27 /// </summary> 28 /// <typeparam name="T"></typeparam> 29 /// <param name="dbs"></param> 30 /// <param name="lt"></param> 31 /// <param name="transaction"></param> 32 /// <param name="commandTimeout"></param> 33 /// <returns></returns> 34 public static bool InsertBatch<T>(this DbBase dbs, IList<T> lt, IDbTransaction transaction = null, int? commandTimeout = null) where T : class 35 { 36 var db = dbs.DbConnecttion; 37 var sql = SqlQuery<T>.Builder(dbs); 38 var flag = db.Execute(sql.InsertSql, lt, transaction, commandTimeout); 39 return flag == lt.Count; 40 } 41 42 /// <summary> 43 /// 按条件删除 44 /// </summary> 45 /// <typeparam name="T"></typeparam> 46 /// <param name="dbs"></param> 47 /// <param name="sql"></param> 48 /// <returns></returns> 49 public static bool Delete<T>(this DbBase dbs, SqlQuery sql = null, IDbTransaction transaction = null) where T : class 50 { 51 var db = dbs.DbConnecttion; 52 if (sql == null) 53 { 54 sql = SqlQuery<T>.Builder(dbs); 55 } 56 var f = db.Execute(sql.DeleteSql, sql.Param, transaction); 57 return f > 0; 58 } 59 60 /// <summary> 61 /// 按指定某型删除 62 /// </summary> 63 /// <typeparam name="T"></typeparam> 64 /// <param name="dbs"></param> 65 /// <param name="sql">如果sql为null，则根据t的主键进行修改</param> 66 /// <returns></returns> 67 public static bool Delete<T>(this DbBase dbs, T t, IDbTransaction transaction = null) where T : class 68 { 69 var db = dbs.DbConnecttion; 70 SqlQuery sql = SqlQuery<T>.Builder(dbs); 71 sql = sql.AppendParam<T>(t); 72 var f = db.Execute(sql.DeleteSql, sql.Param, transaction); 73 return f > 0; 74 } 75 76 /// <summary> 77 /// 指定主键ID删除数据 78 /// </summary> 79 /// <typeparam name="T"></typeparam> 80 /// <param name="dbs"></param> 81 /// <param name="ID"></param> 82 /// <param name="transaction"></param> 83 /// <returns></returns> 84 public static bool DeleteByID<T>(this DbBase dbs, object ID, IDbTransaction transaction = null) where T : class 85 { 86 var db = dbs.DbConnecttion; 87 SqlQuery sql = SqlQuery<T>.Builder(dbs); 88 sql.KeyValue = ID; 89 var f = db.Execute(sql.DeleteKeySql, sql.Param, transaction); 90 return f > 0; 91 } 92 93 /// <summary> 94 /// 修改 95 /// </summary> 96 /// <typeparam name="T"></typeparam> 97 /// <param name="dbs"></param> 98 /// <param name="t">如果sql为null，则根据t的主键进行修改</param> 99 /// <param name="sql">按条件修改</param> 100 /// <returns></returns> 101 public static bool Update<T>(this DbBase dbs, T t, SqlQuery sql = null, IDbTransaction transaction = null) where T : class 102 { 103 var db = dbs.DbConnecttion; 104 if (sql == null) 105 { 106 sql = SqlQuery<T>.Builder(dbs); 107 } 108 sql = sql.AppendParam<T>(t); 109 var f = db.Execute(sql.UpdateSql, sql.Param, transaction); 110 return f > 0; 111 } 112 113 /// <summary> 114 /// 修改 115 /// </summary> 116 /// <typeparam name="T"></typeparam> 117 /// <param name="dbs"></param> 118 /// <param name="t">如果sql为null，则根据t的主键进行修改</param> 119 /// <param name="updateProperties">要修改的属性集合</param> 120 /// <param name="sql">按条件修改</param> 121 /// <returns></returns> 122 public static bool Update<T>(this DbBase dbs, T t, IList<string> updateProperties, SqlQuery sql = null, IDbTransaction transaction = null) where T : class 123 { 124 var db = dbs.DbConnecttion; 125 if (sql == null) 126 { 127 sql = SqlQuery<T>.Builder(dbs); 128 } 129 sql = sql.AppendParam<T>(t) 130 .SetExcProperties<T>(updateProperties); 131 var f = db.Execute(sql.UpdateSql, sql.Param, transaction); 132 return f > 0; 133 } 134 135 /// <summary> 136 /// 获取默认一条数据，没有则为NULL 137 /// </summary> 138 /// <typeparam name="T"></typeparam> 139 /// <param name="dbs"></param> 140 /// <param name="sql"></param> 141 /// <returns></returns> 142 public static T SingleOrDefault<T>(this DbBase dbs, SqlQuery sql, IDbTransaction transaction = null) where T : class 143 { 144 var db = dbs.DbConnecttion; 145 if (sql == null) 146 { 147 sql = SqlQuery<T>.Builder(dbs); 148 } 149 sql = sql.Top(1); 150 var result = db.Query<T>(sql.QuerySql, sql.Param, transaction); 151 return result.FirstOrDefault(); 152 } 153 154 /// <summary> 155 /// 分页查询 156 /// </summary> 157 /// <typeparam name="T"></typeparam> 158 /// <param name="dbs"></param> 159 /// <param name="pageIndex"></param> 160 /// <param name="pageSize"></param> 161 /// <param name="dataCount"></param> 162 /// <param name="sqlQuery"></param> 163 /// <returns></returns> 164 public static IList<T> Page<T>(this DbBase dbs, int pageIndex, int pageSize, out long dataCount, SqlQuery sqlQuery = null, IDbTransaction transaction = null) where T : class 165 { 166 var db = dbs.DbConnecttion; 167 var result = new List<T>(); 168 dataCount = 0; 169 if (sqlQuery == null) 170 { 171 sqlQuery = SqlQuery<T>.Builder(dbs); 172 } 173 sqlQuery = sqlQuery.Page(pageIndex, pageSize); 174 var para = sqlQuery.Param; 175 var cr = db.Query(sqlQuery.CountSql, para, transaction).SingleOrDefault(); 176 dataCount = (long)cr.DataCount; 177 result = db.Query<T>(sqlQuery.PageSql, para, transaction).ToList(); 178 return result; 179 } 180 181 /// <summary> 182 /// 查询 183 /// </summary> 184 /// <typeparam name="T"></typeparam> 185 /// <param name="dbs"></param> 186 /// <param name="sql"></param> 187 /// <returns></returns> 188 public static IList<T> Query<T>(this DbBase dbs, SqlQuery sql = null, IDbTransaction transaction = null) where T : class 189 { 190 var db = dbs.DbConnecttion; 191 if (sql == null) 192 { 193 sql = SqlQuery<T>.Builder(dbs); 194 } 195 var result = db.Query<T>(sql.QuerySql, sql.Param, transaction); 196 return result.ToList(); 197 } 198 199 /// <summary> 200 /// 通过主键查询 201 /// </summary> 202 /// <typeparam name="T"></typeparam> 203 /// <param name="dbs"></param> 204 /// <param name="sql"></param> 205 /// <returns></returns> 206 public static T QueryByID<T>(this DbBase dbs, object ID, IDbTransaction transaction = null) where T : class 207 { 208 var db = dbs.DbConnecttion; 209 SqlQuery sql = SqlQuery<T>.Builder(dbs); 210 sql.KeyValue = ID; 211 var result = db.Query<T>(sql.QueryKeySql, sql.Param, transaction).FirstOrDefault(); 212 return result; 213 } 214 215 /// <summary> 216 /// 数据数量 217 /// </summary> 218 /// <typeparam name="T"></typeparam> 219 /// <param name="dbs"></param> 220 /// <param name="sql"></param> 221 /// <returns></returns> 222 public static long Count<T>(this DbBase dbs, SqlQuery sql = null, IDbTransaction transaction = null) where T : class 223 { 224 var db = dbs.DbConnecttion; 225 if (sql == null) 226 { 227 sql = SqlQuery<T>.Builder(dbs); 228 } 229 var cr = db.Query(sql.CountSql, sql.Param, transaction).SingleOrDefault(); 230 return (long)cr.DataCount; 231 } 232 233 public static void SetIdentity(IDbConnection conn, Action<int> setId, IDbTransaction transaction = null) 234 { 235 dynamic identity = conn.Query("SELECT @@IDENTITY AS Id", null, transaction).Single(); 236 int newId = (int)identity.Id; 237 setId(newId); 238 } 239 240 /// <summary> 241 /// 判断对象是否存在 242 /// </summary> 243 /// <typeparam name="T"></typeparam> 244 /// <param name="dbs"></param> 245 /// <param name="ID"></param> 246 /// <param name="transaction"></param> 247 /// <returns></returns> 248 public static bool Exists<T>(this DbBase dbs, object ID, IDbTransaction transaction = null) where T : class 249 { 250 var db = dbs.DbConnecttion; 251 SqlQuery sql = SqlQuery<T>.Builder(dbs); 252 sql.KeyValue = ID; 253 var f = db.Query(sql.ExistsSql, sql.Param, transaction).SingleOrDefault(); 254 return f.DataCount > 0; ;// f > 0; 255 } 256 257 /// <summary> 258 ///自定义语句和存储过程查询--返回集合 259 /// </summary> 260 /// <typeparam name="T">返回集合</typeparam> 261 /// <param name="sql">sql语句或存储过程名字</param> 262 /// <param name="p">参数</param> 263 /// <param name="cmdType">执行的命令类型</param> 264 /// <param name="transaction">事物控制</param> 265 /// DynamicParameters 266 /// <returns></returns> 267 public static IEnumerable<T> Query<T>(this DbBase dbs, string query, object p = null, CommandType cmdType = CommandType.Text, IDbTransaction transaction = null) 268 { 269 var db = dbs.DbConnecttion; 270 return db.Query<T>(query, p, transaction, true, null, cmdType); 271 } 272 273 /// <summary> 274 /// 自定义语句和存储过程的增删改--返回影响的行数 275 /// </summary> 276 /// <typeparam name="T"></typeparam> 277 /// <param name="dbs"></param> 278 /// <param name="query">执行的语句</param> 279 /// <param name="parans">参数</param> 280 /// <param name="transaction">事物控制</param> 281 /// <returns>影响的行数</returns> 282 public static int Execute(this DbBase dbs, string query, object parans, CommandType cmdType = CommandType.Text,IDbTransaction transaction = null) 283 { 284 var db = dbs.DbConnecttion; 285 int row = db.Execute(query, parans, transaction,null,cmdType); 286 return row; 287 } 288 289 } DapperEx.cs  有这个扩展类后面的操作的方便了，接下来是用DAL层来调用，这里写个封装个基类  DAL基类  DataAccessBase.cs

1 public class DataAccessBase 2 { 3 public DbBase db { get; private set; } 4 5 public DataAccessBase(DbBase Db) 6 { 7 this.db = Db; 8 } 9 #region 自定义其他方法 10 11 #endregion 12 } 13 public class DataAccessBase<T> : DataAccessBase where T : class 14 { 15 public DataAccessBase(DbBase db) : base(db) { } 16 17 #region INSERT 18 /// <summary> 19 /// //插入一条数据 20 /// </summary> 21 /// <param name="user"></param> 22 /// <returns></returns> 23 public int Insert(T model, IDbTransaction tran = null) 24 { 25 var result = db.Insert<T>(model, tran); 26 return result; 27 } 28 29 /// <summary> 30 /// 插入批量数据 31 /// </summary> 32 /// <param name="models"></param> 33 public bool InsertBatch(List<T> models, IDbTransaction tran = null) 34 { 35 var result = db.InsertBatch<T>(models, tran); 36 return result; 37 38 } 39 #endregion 40 41 #region SELECT 42 /// <summary> 43 /// 获取默认一条数据，没有则为NULL 44 /// </summary> 45 /// <param name="sqlWhere"></param> 46 /// <returns></returns> 47 public T SingleOrDefault(SqlQuery sqlWhere = null, IDbTransaction tran = null) 48 { 49 var result = db.SingleOrDefault<T>(sqlWhere, tran); 50 return result; 51 } 52 53 /// <summary> 54 /// 根据主键查询 55 /// </summary> 56 /// <param name="ID"></param> 57 /// <returns></returns> 58 public T GetByID(object ID, IDbTransaction tran = null) 59 { 60 var result = db.QueryByID<T>(ID, tran); 61 return result; 62 } 63 64 /// <summary> 65 /// 获取全部数据 66 /// </summary> 67 /// <returns></returns> 68 public IList<T> GetAll(IDbTransaction tran = null) 69 { 70 var result = db.Query<T>(null, tran); 71 return result; 72 73 } 74 75 /// <summary> 76 /// 带条件查询 77 /// </summary> 78 /// <param name="d"></param> 79 /// <returns></returns> 80 public IList<T> GetAll(SqlQuery sqlWhere, IDbTransaction tran = null) 81 { 82 var result = db.Query<T>(sqlWhere, tran); 83 return result; 84 } 85 86 /// <summary> 87 /// 分页查询 88 /// </summary> 89 /// <param name="PageIndex"></param> 90 /// <param name="PageSize"></param> 91 /// <param name="row"></param> 92 /// <param name="sql"></param> 93 /// <returns></returns> 94 public IList<T> Page(int PageIndex, int PageSize, out long row, SqlQuery sql = null, IDbTransaction tran = null) 95 { 96 var result = db.Page<T>(PageIndex, PageSize, out row, sql, tran); 97 return result; 98 99 } 100 #endregion 101 102 #region DELETE 103 /// <summary> 104 /// 自定义条件删除 105 /// </summary> 106 /// <param name="sqlWhere"></param> 107 /// <returns></returns> 108 public bool Delete(SqlQuery sqlWhere, IDbTransaction tran = null) 109 { 110 var result = db.Delete<T>(sqlWhere, tran); 111 return result; 112 } 113 114 /// <summary> 115 /// 按模型删除 116 /// </summary> 117 /// <param name="model"></param> 118 /// <returns></returns> 119 public bool Delete(T model, IDbTransaction tran = null) 120 { 121 var result = db.Delete<T>(model, tran); 122 return result; 123 } 124 125 /// <summary> 126 /// 根据主键ID删除 127 /// </summary> 128 /// <param name="ID"></param> 129 /// <returns></returns> 130 public bool DeleteByID(object ID, IDbTransaction tran = null) 131 { 132 var result = db.DeleteByID<T>(ID, tran); 133 return result; 134 } 135 136 /// <summary> 137 /// 按主键批量删除 138 /// </summary> 139 /// <param name="idValues"></param> 140 /// <returns></returns> 141 public bool DeleteByIds(IEnumerable idValues, IDbTransaction tran = null) 142 { 143 bool result = false; 144 //开启事务 145 if (tran == null) 146 { 147 tran = db.DbTransaction; 148 } 149 foreach (var item in idValues) 150 { 151 result = db.DeleteByID<T>(item, tran); 152 if (!result) 153 { 154 break; 155 } 156 } 157 if (result) 158 { 159 tran.Commit(); 160 } 161 else 162 { 163 tran.Rollback(); 164 } 165 return result; 166 } 167 168 /// <summary> 169 /// 批量删除 170 /// </summary> 171 /// <param name="model"></param> 172 /// <returns></returns> 173 public bool DeleteBatch(List<T> model, IDbTransaction tran = null) 174 { 175 bool result = false; 176 //开启事务 177 if (tran == null) 178 { 179 tran = db.DbTransaction; 180 } 181 foreach (var item in model) 182 { 183 result = db.Delete<T>(item, tran); 184 if (!result) 185 { 186 break; 187 } 188 } 189 if (result) 190 { 191 tran.Commit(); 192 } 193 else 194 { 195 tran.Rollback(); 196 } 197 return result; 198 } 199 #endregion 200 201 #region UPDATE 202 /// <summary> 203 /// 修改--（带T和sqlWhere时可实现统一修改） 204 /// </summary> 205 /// <param name="model">如果sql为null，则根据model的主键进行修改</param> 206 /// <param name="sqlWhere">按条件修改</param> 207 public bool Update(T model, SqlQuery sqlWhere = null, IDbTransaction tran = null) 208 { 209 var result = db.Update<T>(model, sqlWhere, tran); 210 return result; 211 } 212 213 /// <summary> 214 /// 修改--可指定属性修改 215 /// </summary> 216 /// <param name="model">如果sql为null，则根据t的主键进行修改</param> 217 /// <param name="updateProperties">要修改的属性集合</param> 218 /// <param name="sqlWhere">按条件修改</param> 219 /// <returns></returns> 220 public bool Update(T model, IList<string> updateProperties, SqlQuery sqlWhere = null, IDbTransaction tran = null) 221 { 222 var result = db.Update<T>(model, updateProperties, sqlWhere, tran); 223 return result; 224 225 } 226 227 /// <summary> 228 /// 批量插入 229 /// </summary> 230 /// <param name="model"></param> 231 /// <returns></returns> 232 public bool UpdateBatch(List<T> model, IDbTransaction tran = null) 233 { 234 bool result = false; 235 //开启事务 236 if (tran == null) 237 { 238 tran = db.DbTransaction; 239 } 240 foreach (var item in model) 241 { 242 result = db.Update<T>(item, null, tran); 243 if (!result) 244 { 245 break; 246 } 247 } 248 if (result) 249 { 250 tran.Commit(); 251 } 252 else 253 { 254 tran.Rollback(); 255 } 256 return result; 257 } 258 #endregion 259 260 #region ORTHER 261 /// <summary> 262 /// 获取数量 263 /// </summary> 264 /// <param name="sqlWhere"></param> 265 /// <returns></returns> 266 public long GetCount(SqlQuery sqlWhere = null, IDbTransaction tran = null) 267 { 268 return db.Count<T>(sqlWhere, tran); 269 } 270 271 /// <summary> 272 /// 判断对象是否存在 273 /// </summary> 274 /// <param name="ID"></param> 275 /// <returns></returns> 276 public bool Exists(object ID, IDbTransaction tran = null) 277 { 278 return db.Exists<T>(ID, tran); 279 } 280 281 /// <summary> 282 /// 自定义语句和存储过程查询--返回集合 283 /// </summary> 284 /// <param name="sql">自定的语句或存储过程名字</param> 285 /// <param name="param">参数</param> 286 /// <param name="cmdType">类型</param> 287 /// <returns></returns> 288 public IEnumerable<T> Query<T>(string sql, object param = null, CommandType cmdType = CommandType.Text, IDbTransaction tran = null) 289 { 290 return db.Query<T>(sql, param, cmdType, tran); 291 } 292 293 /// <summary> 294 /// 自定义语句和存储过程的增删改--返回影响的行数 295 /// </summary> 296 /// <param name="sql">自定的语句或存储过程名字</param> 297 /// <param name="param">参数</param> 298 /// <param name="cmdType">类型</param> 299 /// <returns></returns> 300 public int Execute(string sql, object param = null, CommandType cmdType = CommandType.Text, IDbTransaction tran = null) 301 { 302 return db.Execute(sql, param, cmdType, tran); 303 } 304 305 /// <summary> 306 /// 使用DynamicParameters方式 307 /// </summary> 308 /// <param name="sql"></param> 309 /// <param name="param"></param> 310 /// <param name="cmdType"></param> 311 /// <returns></returns> 312 public int Execute(string sql, DynamicParameters param = null, CommandType cmdType = CommandType.Text, IDbTransaction tran = null) 313 { 314 //param.Add("@ID", 123); 315 return db.Execute(sql, param, cmdType, tran); 316 } 317 #endregion 318 319 } DataAccessBase.cs  再写个生成DAL的T4模板  DALAuto.tt

1 <#@ template debug="true" hostspecific="true" language="C#" #> 2 <#@ output extension=".cs" #> 3 <#@ assembly name="System.Core"#> 4 <#@ import namespace="System"#> 5 <#@ import namespace="System.Collections.Generic"#> 6 <#@ include file="DBSchema.ttinclude"#> 7 8 using DapperEx; 9 using System; 10 using System.Collections.Generic; 11 using System.Linq; 12 using System.Text; 13 using Entity; 14 15 namespace DAL 16 { 17 18 <# 19 var dbSchema=DBSchemaFactory.GetDBSchema(); 20 List<string> tableList=dbSchema.GetTablesList(); 21 string Extension="Info"; 22 foreach(string tableName in tableList) 23 { #> 24 public partial class <#=tableName#>DAL: DataAccessBase<<#=tableName#><#=Extension#>> 25 { 26 public <#=tableName#>DAL(DbBase db) : base(db) { } 27 } 28 <# } #> 29 } DALAuto.tt 然后通过BLL层调用，也封装个基类和生成BLL的T4模板 BLL 基类  BusinessBase.cs

1 public class BusinessBase 2 { 3 public DbBase OpenConnection(string name = null) 4 { 5 if (String.IsNullOrWhiteSpace(name)) 6 { 7 name = "strSqlCe"; 8 } 9 return new DbBase(name); 10 } 11 } 12 13 public class BusinessBase<T> : BusinessBase where T : class 14 { 15 public int Insert(T model) 16 { 17 using (var db = OpenConnection()) 18 { 19 DataAccessBase<T> dal = new DataAccessBase<T>(db); 20 return dal.Insert(model); 21 } 22 } 23 public bool InsertBatch(List<T> models) 24 { 25 using (var db = OpenConnection()) 26 { 27 DataAccessBase<T> dal = new DataAccessBase<T>(db); 28 return dal.InsertBatch(models); 29 } 30 } 31 public T SingleOrDefault(SqlQuery sqlWhere = null) 32 { 33 using (var db = OpenConnection()) 34 { 35 DataAccessBase<T> dal = new DataAccessBase<T>(db); 36 var result = dal.SingleOrDefault(sqlWhere); 37 return result; 38 } 39 } 40 public T GetByID(object ID) 41 { 42 using (var db = OpenConnection()) 43 { 44 DataAccessBase<T> dal = new DataAccessBase<T>(db); 45 var result = dal.GetByID(ID); 46 return result; 47 } 48 } 49 public IList<T> GetAll() 50 { 51 using (var db = OpenConnection()) 52 { 53 DataAccessBase<T> dal = new DataAccessBase<T>(db); 54 return dal.GetAll(); 55 } 56 } 57 public IList<T> GetAll(SqlQuery<T> sqlWhere) 58 { 59 using (var db = OpenConnection()) 60 { 61 if (sqlWhere == null) 62 { 63 sqlWhere = SqlQuery<T>.Builder(db); 64 } 65 DataAccessBase<T> dal = new DataAccessBase<T>(db); 66 return dal.GetAll(sqlWhere); 67 } 68 } 69 public IList<T> Page(int PageIndex, int PageSize, out long row, SqlQuery sql = null) 70 { 71 using (var db = OpenConnection()) 72 { 73 DataAccessBase<T> dal = new DataAccessBase<T>(db); 74 var result = dal.Page(PageIndex, PageSize, out row, sql); 75 return result; 76 } 77 78 } 79 public bool Delete(SqlQuery sqlWhere) 80 { 81 using (var db = OpenConnection()) 82 { 83 DataAccessBase<T> dal = new DataAccessBase<T>(db); 84 var result = dal.Delete(sqlWhere); 85 return result; 86 } 87 } 88 public bool Delete(T model) 89 { 90 using (var db = OpenConnection()) 91 { 92 DataAccessBase<T> dal = new DataAccessBase<T>(db); 93 var result = dal.Delete(model); 94 return result; 95 } 96 } 97 public bool DeleteByID(object ID) 98 { 99 using (var db = OpenConnection()) 100 { 101 DataAccessBase<T> dal = new DataAccessBase<T>(db); 102 var result = dal.DeleteByID(ID); 103 return result; 104 } 105 } 106 public bool DeleteByIds(IEnumerable idValues) 107 { 108 using (var db = OpenConnection()) 109 { 110 DataAccessBase<T> dal = new DataAccessBase<T>(db); 111 var result = dal.DeleteByIds(idValues); 112 return result; 113 } 114 } 115 public bool DeleteBatch(List<T> model) 116 { 117 using (var db = OpenConnection()) 118 { 119 DataAccessBase<T> dal = new DataAccessBase<T>(db); 120 var result = dal.DeleteBatch(model); 121 return result; 122 } 123 } 124 public bool Update(T Model, SqlQuery sqlWhere = null) 125 { 126 using (var db = OpenConnection()) 127 { 128 DataAccessBase<T> dal = new DataAccessBase<T>(db); 129 var result = dal.Update(Model, sqlWhere); 130 return result; 131 } 132 } 133 public bool UpdateAll(List<T> model) 134 { 135 using (var db = OpenConnection()) 136 { 137 DataAccessBase<T> dal = new DataAccessBase<T>(db); 138 var result = dal.UpdateBatch(model); 139 return result; 140 } 141 } 142 public long GetCount(SqlQuery sqlWhere = null) 143 { 144 using (var db = OpenConnection()) 145 { 146 DataAccessBase<T> dal = new DataAccessBase<T>(db); 147 var result = dal.GetCount(sqlWhere); 148 return result; 149 } 150 } 151 public bool Exists(object ID) 152 { 153 using (var db = OpenConnection()) 154 { 155 DataAccessBase<T> dal = new DataAccessBase<T>(db); 156 var result = dal.Exists(ID); 157 return result; 158 } 159 } 160 public IEnumerable<T> Query<T>(string sql, object param = null, CommandType cmdType = CommandType.Text) where T : class 161 { 162 using (var db = OpenConnection()) 163 { 164 DataAccessBase<T> dal = new DataAccessBase<T>(db); 165 return dal.Query<T>(sql, param, cmdType); 166 } 167 } 168 public int Execute(string sql, object param = null, CommandType cmdType = CommandType.Text) 169 { 170 using (var db = OpenConnection()) 171 { 172 DataAccessBase<T> dal = new DataAccessBase<T>(db); 173 return dal.Execute(sql, param, cmdType); 174 } 175 } 176 177 } BusinessBase.cs BLLAuto.tt

1 <#@ template debug="true" hostspecific="true" language="C#" #> 2 <#@ output extension=".cs" #> 3 <#@ assembly name="System.Core"#> 4 <#@ import namespace="System"#> 5 <#@ import namespace="System.Collections.Generic"#> 6 <#@ include file="DBSchema.ttinclude"#> 7 8 using Entity; 9 using System; 10 using System.Collections.Generic; 11 using System.Linq; 12 using System.Text; 13 using DapperEx; 14 using System.Collections; 15 using System.Data; 16 using DAL; 17 18 namespace BLL 19 { 20 21 <# 22 var dbSchema=DBSchemaFactory.GetDBSchema(); 23 List<string> tableList=dbSchema.GetTablesList(); 24 foreach(string tableName in tableList) 25 { 26 string Entity=tableName+"Info"; 27 string DAL=tableName+"DAL"; 28 #> 29 30 public partial class <#=tableName#>BLL : BusinessBase<<#=Entity#>> 31 { 32 33 } 34 <# } #> 35 } BLLAuto.tt 噢~~这里一开始少了个Model层，同样，也是写个生成Model的T4模板 ModelAuto.tt

1 <#@ template debug="true" hostspecific="true" language="C#" #> 2 <#@ output extension=".cs" #> 3 <#@ assembly name="System.Core"#> 4 <#@ import namespace="System"#> 5 <#@ import namespace="System.Collections.Generic"#> 6 <#@ include file="DBSchema.ttinclude"#> 7 8 using System; 9 using System.Collections.Generic; 10 using System.Text; 11 using System.IO; 12 using System.Runtime.Serialization.Formatters.Binary; 13 using DapperEx; 14 15 namespace Entity 16 { 17 18 <# 19 var dbSchema=DBSchemaFactory.GetDBSchema(); 20 List<string> tableList=dbSchema.GetTablesList(); 21 string Extension="Info"; 22 foreach(string tableName in tableList) 23 { 24 Table table=dbSchema.GetTableMetadata(tableName); 25 #> 26 27 [Serializable] 28 [TableAttribute(Name = "<#=tableName#>")] 29 /// <summary> 30 ///<#=tableName#><#=Extension#><#= table.TableExplain??""#> 31 /// </summary> 32 public partial class <#=tableName#><#=Extension#> 33 { 34 #region 构造函数 35 public <#=tableName#><#=Extension#>() { } 36 37 public <#=tableName#><#=Extension#>(<#=table.ColumnTypeNames#>) 38 { 39 <# 40 foreach(Column c in table.Columns) 41 { 42 #> 43 this.<#=c.LowerColumnName#> = <#=c.LowerColumnName#>; 44 <# 45 } 46 #> 47 } 48 #endregion 49 50 #region 属性 51 <# 52 foreach(Column c in table.Columns) 53 { 54 #> 55 56 private <#=GeneratorHelper.GetQuesMarkByType(c.AllowDBNull,c.TypeName)#> <#=c.LowerColumnName#>; 57 58 /// <summary> 59 <# 60 string ColumnExplain=""; 61 if(!String.IsNullOrWhiteSpace(c.ColumnExplain)) 62 { 63 string [] ColumnExplains = c.ColumnExplain.Split(new [] {'\r', '\n'}, StringSplitOptions.RemoveEmptyEntries); 64 foreach (var line in ColumnExplains) 65 {#> 66 ///<#=line#> 67 <# } 68 } 69 #> 70 /// </summary> 71 <# 72 if(c.AutoIncrement) 73 {#> 74 [Id(true)] 75 <# } 76 #> 77 public <#=GeneratorHelper.GetQuesMarkByType(c.AllowDBNull,c.TypeName)#> <#=c.UpColumnName#> 78 { 79 get { return <#=c.LowerColumnName#>; } 80 set { <#=c.LowerColumnName#> = value; } 81 } 82 <# 83 } 84 #> 85 #endregion 86 87 #region 验证 88 public List<string> ErrorList = new List<string>(); 89 private bool Validator() 90 { 91 bool validatorResult = true; 92 <# 93 foreach(Column c in table.Columns) 94 { 95 if(!c.AllowDBNull) 96 { 97 if(c.TypeName==GeneratorHelper.StringType) 98 { 99 #> 100 if (string.IsNullOrEmpty(this.<#=c.UpColumnName#>)) 101 { 102 validatorResult = false; 103 this.ErrorList.Add("The <#=c.UpColumnName#> should not be empty!"); 104 } 105 <# 106 } 107 if(c.TypeName==GeneratorHelper.DateTimeType) 108 { 109 #> 110 if (this.<#=c.UpColumnName#>==null) 111 { 112 validatorResult = false; 113 this.ErrorList.Add("The <#=c.UpColumnName#> should not be empty!"); 114 } 115 <# 116 } 117 } 118 if(c.TypeName==GeneratorHelper.StringType) 119 { 120 #> 121 if (this.<#=c.UpColumnName#> != null && <#=c.MaxLength#> < this.<#=c.UpColumnName#>.Length) 122 { 123 validatorResult = false; 124 this.ErrorList.Add("The length of <#=c.UpColumnName#> should not be greater then <#=c.MaxLength#>!"); 125 } 126 <# 127 } 128 } 129 #> 130 return validatorResult; 131 } 132 133 #endregion 134 135 #region 辅助方法 136 public <#=tableName#><#=Extension#> Clone(bool isDeepCopy) 137 { 138 <#=tableName#><#=Extension#> footman; 139 if (isDeepCopy) 140 { 141 MemoryStream memoryStream = new MemoryStream(); 142 BinaryFormatter formatter = new BinaryFormatter(); 143 formatter.Serialize(memoryStream, this); 144 memoryStream.Position = 0; 145 footman = (<#=tableName#><#=Extension#>)formatter.Deserialize(memoryStream); 146 } 147 else 148 { 149 footman = (<#=tableName#><#=Extension#>)this.MemberwiseClone(); 150 } 151 return footman; 152 } 153 #endregion 154 155 } 156 157 <# 158 } 159 dbSchema.Dispose(); 160 #> 161 }