一. 数据库简要介绍
数据库就不做详细介绍了，简单的说，就是为了存储数据，进行查看的，总之用的随处可见。
二. C#三层架构
我们这里也用到了，三层架构分为：表现层（UI（User Interface））、业务逻辑层（BLL（Business Logic Layer））、数据访问层（DAL（Data Access Layer）），再加上实体类库（Model），只不过是只用到了最下面三层，表现层在咱们这就当做调用BLL层吧，测试用例用。
三. Model层生成实体的前提
我们这里以MySQL为例，SQLserver的直接建立实体模板就可以了，但是MySQL需要做下配置，否则在实体来源这儿是找不到MySQL的相关信息的，我这里是已经添加了所以存在，本机配置VS2019专业版，Win10操作系统。
两个安装包需要安装下，mysql-connector-net-6.9.12.msi和mysql-for-visualstudio-1.2.8.msi，Nuget包选择MySql.Data 6.9.12、MySql.Data.Entity 6.9.12、EntityFramework 6.2.0，这些配置不好，造成的结构就是建立实体模板的时候链接不上数据库，或者闪退等现象，也有其他的配置可以成功生成。操作数据库的NuGet包选择Dapper 2.0.35和Dapper.SimpleCRUD 2.2.0.1，配置完毕看下项目的一个结构。
我这里也分为了三层，展开的三层，相对较为明显，DataBaseTest是启动项目，Common是一个通用类，里面没什么具体的东西，DBHelp是数据库操作帮助类，LogManager是写日志帮助类。
首先生成实体类模板，右键点击SqlModel，右键添加新建项，ANO.NET实体数据模型，输入你的MySQL数据库IP，用户名、密码，选择库，点击确定，
选择要生成的表，点击完成，之后就是点击确定，确定，
之后就会生成相应的文件，打开.cs文件就是生成的实体模板类，这样就会对应数据库的表结构生成相应的.cs文件，相当方便了。
SqlModel层就生成好了，接下来是DAL，接下来的就是用到了T4模板了，DAL中添加一个IObjectDAL接口，因为所有的表操作用到的方法都是一样的，只是实体模板类不一样而已，右键DAL项目，添加运行时文本模板DALTemplate.tt，之后ctrl+s保存就会自动生成相应的代码啦。
1 interface IObjectDAL<TEntity> 2 { 3 /// <summary> 4 /// 获取所有实体对象 5 /// </summary> 6 /// <returns></returns> 7 List<TEntity> GetAll(); 8 9 /// <summary> 10 /// 根据条件获取实体 11 /// </summary> 12 /// <returns></returns> 13 List<TEntity> GetObjectByCondition(string whereCondition); 14 15 /// <summary> 16 /// 根据多条件获取实体 17 /// </summary> 18 /// <param name="whereCondition"></param> 19 /// <returns></returns> 20 List<TEntity> GetObjectByCondition(object whereCondition); 21 22 /// <summary> 23 /// 更具ID判断是否存在 24 /// </summary> 25 /// <param name="id"></param> 26 /// <returns></returns> 27 bool Exists(string id); 28 29 /// <summary> 30 /// 插入列表信息 31 /// </summary> 32 /// <param name="objectIQuery"></param> 33 /// <returns></returns> 34 List<TEntity> InsertObjectList(List<TEntity> objectList, 35 IDbTransaction transaction = null, int? commandTimeout = null); 36 37 /// <summary> 38 /// 插入对象信息 39 /// </summary> 40 /// <param name="objectInfo"></param> 41 /// <returns></returns> 42 int? InsertObject(TEntity objectInfo, 43 IDbTransaction transaction = null, int? commandTimeout = null); 44 45 /// <summary> 46 /// 更新对象信息 47 /// </summary> 48 /// <param name="objectInfo"></param> 49 /// <returns></returns> 50 bool UpdateObject(TEntity objectInfo, 51 IDbTransaction transaction = null, int? commandTimeout = null); 52 53 /// <summary> 54 /// 更新对象信息 55 /// </summary> 56 /// <param name="objectInfo"></param> 57 /// <returns></returns> 58 bool UpdateObjectList(List<TEntity> objectList, 59 IDbTransaction transaction = null, int? commandTimeout = null); 60 61 /// <summary> 62 /// 删除对象列表信息 63 /// </summary> 64 /// <param name="objectList"></param> 65 /// <param name="transaction"></param> 66 /// <param name="commandTimeout"></param> 67 /// <returns></returns> 68 bool DeleteObjectList(List<TEntity> objectList, 69 IDbTransaction transaction = null, int? commandTimeout = null); 70 71 /// <summary> 72 /// 根据ID删除对象 73 /// </summary> 74 /// <param name="id"></param> 75 /// <param name="transaction"></param> 76 /// <param name="commandTimeout"></param> 77 bool DeleteObjectByID(object id, 78 IDbTransaction transaction = null, int? commandTimeout = null); 79 80 /// <summary> 81 /// 删除对象列表信息 82 /// </summary> 83 /// <param name="objectList"></param> 84 /// <param name="transaction"></param> 85 /// <param name="commandTimeout"></param> 86 /// <returns></returns> 87 bool DeleteObjectListByCondition(string whereCondition, 88 IDbTransaction transaction = null, int? commandTimeout = null); 89 90 /// <summary> 91 /// 通过条件删除对象信息 92 /// </summary> 93 /// <param name="whereConditions"></param> 94 /// <param name="transaction"></param> 95 /// <param name="commandTimeout"></param> 96 /// <returns></returns> 97 bool DeleteObjectListByCondition(object whereConditions, 98 IDbTransaction transaction = null, int? commandTimeout = null); 99 100 /// <summary> 101 /// 删除选定的对象 102 /// </summary> 103 /// <param name="ObjectInfo"></param> 104 /// <returns></returns> 105 bool Delete(TEntity objectInfo, IDbTransaction transaction = null, int? commandTimeout = null); 106 107 /// <summary> 108 /// 执行SQL 109 /// </summary> 110 /// <param name="sql"></param> 111 /// <param name="whereCondition"></param> 112 /// <param name="transaction"></param> 113 /// <returns></returns> 114 IEnumerable<T> Query<T>(string sql, object whereCondition = null, IDbTransaction transaction = null); 115 }
1 <#@ template language="C#" debug="false" hostspecific="true"#> 2 <#@ include file="EF6.Utility.CS.ttinclude"#> 3 <#@ output extension=".cs"#> 4 5 <# 6 const string inputFile = @"..\SqlModel\Model.edmx"; 7 var textTransform = DynamicTextTransformation.Create(this); 8 var code = new CodeGenerationTools(this); 9 var ef = new MetadataTools(this); 10 var typeMapper = new TypeMapper(code, ef, textTransform.Errors); 11 var fileManager = EntityFrameworkTemplateFileManager.Create(this); 12 var itemCollection = new EdmMetadataLoader(textTransform.Host, textTransform.Errors).CreateEdmItemCollection(inputFile); 13 var codeStringGenerator = new CodeStringGenerator(code, typeMapper, ef); 14 15 if (!typeMapper.VerifyCaseInsensitiveTypeUniqueness(typeMapper.GetAllGlobalItems(itemCollection), inputFile)) 16 { 17 return string.Empty; 18 } 19 20 foreach (var entity in typeMapper.GetItemsToGenerate<EntityType>(itemCollection)) 21 { 22 fileManager.StartNewFile(entity.Name + "DAL.cs"); 23 #> 24 using System; 25 using System.Collections.Generic; 26 using System.Linq; 27 using System.Text; 28 using System.Threading.Tasks; 29 using SqlModel; 30 using DBHelp; 31 using Dapper; 32 using System.Data.SqlClient; 33 using System.Data; 34 namespace DAL 35 { 36 [Table("<#=entity.Name#>")] 37 public partial class <#=entity.Name#>DAL : IObjectDAL<<#=entity.Name#>> 38 { 39 DbHelper DBHelper { get; set; } 40 41 public <#=entity.Name#>DAL(DbHelper dbHelper) 42 { 43 DBHelper = dbHelper; 44 } 45 46 /// <summary> 47 /// 获取所有实体对象 48 /// </summary> 49 /// <returns></returns> 50 public List<<#=entity.Name#>> GetAll() 51 { 52 try 53 { 54 return DBHelper.GetList<<#=entity.Name#>>().ToList(); 55 } 56 catch (Exception ex) 57 { 58 throw (ex); 59 } 60 } 61 62 /// <summary> 63 /// 根据ID获取实体 64 /// </summary> 65 /// <returns></returns> 66 public List<<#=entity.Name#>> GetObjectByCondition(string whereCondition) 67 { 68 try 69 { 70 return DBHelper.GetList<<#=entity.Name#>>(whereCondition).ToList(); 71 } 72 catch (Exception ex) 73 { 74 throw (ex); 75 } 76 } 77 78 /// <summary> 79 /// 根据多条件获取实体 80 /// </summary> 81 /// <param name="whereCondition"></param> 82 /// <returns></returns> 83 public List<<#=entity.Name#>> GetObjectByCondition(object whereCondition) 84 { 85 try 86 { 87 return DBHelper.GetList<<#=entity.Name#>>(whereCondition).ToList(); 88 } 89 catch (Exception ex) 90 { 91 throw (ex); 92 } 93 } 94 95 /// <summary> 96 /// 更具ID判断是否存在 97 /// </summary> 98 /// <param name="id"></param> 99 /// <returns></returns> 100 public bool Exists(string id) 101 { 102 try 103 { 104 <#=entity.Name#> alarm = DBHelper.Get<<#=entity.Name#>>(id); 105 if (alarm == null) 106 return false; 107 else 108 return true; 109 } 110 catch (Exception ex) 111 { 112 throw (ex); 113 } 114 } 115 116 /// <summary> 117 /// 插入列表信息 118 /// </summary> 119 /// <param name="objectList"></param> 120 /// <returns></returns> 121 public List<<#=entity.Name#>> InsertObjectList(List<<#=entity.Name#>> objectList, 122 IDbTransaction transaction = null, int? commandTimeout = null) 123 { 124 try 125 { 126 List<<#=entity.Name#>> errorList = new List<<#=entity.Name#>>(); 127 foreach (<#=entity.Name#> objectInfo in objectList) 128 { 129 int? key = DBHelper.Insert(objectInfo, transaction, commandTimeout); 130 if (key == null) 131 { 132 errorList.Add(objectInfo); 133 } 134 } 135 return errorList; 136 } 137 catch (Exception ex) 138 { 139 throw (ex); 140 } 141 } 142 143 /// <summary> 144 /// 插入对象信息 145 /// </summary> 146 /// <param name="objectInfo"></param> 147 /// <returns></returns> 148 public int? InsertObject(<#=entity.Name#> objectInfo, 149 IDbTransaction transaction = null, int? commandTimeout = null) 150 { 151 try 152 { 153 return DBHelper.Insert(objectInfo, transaction,commandTimeout ); 154 } 155 catch (Exception ex) 156 { 157 throw (ex); 158 } 159 } 160 161 /// <summary> 162 /// 更新对象信息 163 /// </summary> 164 /// <param name="objectInfo"></param> 165 /// <returns></returns> 166 public bool UpdateObject(<#=entity.Name#> objectInfo, 167 IDbTransaction transaction = null, int? commandTimeout = null) 168 { 169 try 170 { 171 int effectrows = DBHelper.Update(objectInfo, transaction, commandTimeout); 172 if (effectrows == 0) 173 return false; 174 else 175 return true; 176 } 177 catch (Exception ex) 178 { 179 throw (ex); 180 } 181 } 182 183 184 /// <summary> 185 /// 更新列表对象信息 186 /// </summary> 187 /// <param name="objectInfo"></param> 188 /// <returns></returns> 189 public bool UpdateObjectList(List<<#=entity.Name#>> objectList, 190 IDbTransaction transaction = null, int? commandTimeout = null) 191 { 192 try 193 { 194 int rowCount = 0; 195 foreach (Object entity in objectList) 196 { 197 rowCount += DBHelper.Update(entity, transaction, commandTimeout); 198 } 199 return true; 200 } 201 catch (Exception ex) 202 { 203 throw (ex); 204 } 205 } 206 207 /// <summary> 208 /// 删除对象列表信息 209 /// </summary> 210 /// <param name="objectList"></param> 211 /// <param name="transaction"></param> 212 /// <param name="commandTimeout"></param> 213 /// <returns></returns> 214 public bool DeleteObjectList(List<<#=entity.Name#>> objectList, 215 IDbTransaction transaction = null, int? commandTimeout = null) 216 { 217 try 218 { 219 int rowCount = 0; 220 foreach (Object entity in objectList) 221 { 222 rowCount += DBHelper.Deleted(entity, transaction, commandTimeout); 223 } 224 return true; 225 } 226 catch (Exception ex) 227 { 228 throw (ex); 229 } 230 } 231 232 /// <summary> 233 /// 删除对象列表信息 234 /// </summary> 235 /// <param name="objectList"></param> 236 /// <param name="transaction"></param> 237 /// <param name="commandTimeout"></param> 238 /// <returns></returns> 239 public bool DeleteObjectByID(object id, 240 IDbTransaction transaction = null, int? commandTimeout = null) 241 { 242 try 243 { 244 DBHelper.Deleted(id, transaction, commandTimeout); 245 return true; 246 } 247 catch (Exception ex) 248 { 249 throw (ex); 250 } 251 } 252 253 /// <summary> 254 /// 删除对象列表信息 255 /// </summary> 256 /// <param name="whereCondition"></param> 257 /// <param name="transaction"></param> 258 /// <param name="commandTimeout"></param> 259 /// <returns></returns> 260 public bool DeleteObjectListByCondition(string whereCondition, 261 IDbTransaction transaction = null, int? commandTimeout = null) 262 { 263 try 264 { 265 DBHelper.DeletedList<<#=entity.Name#>>(whereCondition, transaction, commandTimeout); 266 return true; 267 } 268 catch (Exception ex) 269 { 270 throw (ex); 271 } 272 } 273 274 /// <summary> 275 /// 删除对象列表信息 276 /// </summary> 277 /// <param name="whereCondition"></param> 278 /// <param name="transaction"></param> 279 /// <param name="commandTimeout"></param> 280 /// <returns></returns> 281 public bool DeleteObjectListByCondition(object whereCondition, 282 IDbTransaction transaction = null, int? commandTimeout = null) 283 { 284 try 285 { 286 DBHelper.DeletedList<<#=entity.Name#>>(whereCondition, transaction, commandTimeout); 287 return true; 288 } 289 catch (Exception ex) 290 { 291 throw (ex); 292 } 293 } 294 295 /// <summary> 296 /// 删除选定的对象 297 /// </summary> 298 /// <param name="ObjectInfo"></param> 299 /// <returns></returns> 300 public bool Delete(<#=entity.Name#> objectInfo, 301 IDbTransaction transaction = null, int? commandTimeout = null) 302 { 303 try 304 { 305 int effectrows = DBHelper.Deleted(objectInfo, transaction, commandTimeout); 306 if (effectrows == 0) 307 return false; 308 else 309 return true; 310 } 311 catch (Exception ex) 312 { 313 throw (ex); 314 } 315 } 316 317 318 /// <summary> 319 /// 执行SQL 320 /// </summary> 321 /// <param name="sql"></param> 322 /// <param name="whereCondition"></param> 323 /// <param name="transaction"></param> 324 /// <returns></returns> 325 public IEnumerable<T> Query<T>(string sql, 326 object whereCondition = null, IDbTransaction transaction = null) 327 { 328 try 329 { 330 return DBHelper.Query<T>(sql, whereCondition, transaction); 331 } 332 catch (Exception ex) 333 { 334 throw (ex); 335 } 336 } 337 } 338 } 339 <# 340 fileManager.EndBlock(); 341 } 342 fileManager.Process(); 343 #> 344 345 <#+ 346 public const string TemplateId = "CSharp_DbContext_Types_EF6"; 347 348 public class CodeStringGenerator 349 { 350 private readonly CodeGenerationTools _code; 351 private readonly TypeMapper _typeMapper; 352 private readonly MetadataTools _ef; 353 354 public CodeStringGenerator(CodeGenerationTools code, TypeMapper typeMapper, MetadataTools ef) 355 { 356 ArgumentNotNull(code, "code"); 357 ArgumentNotNull(typeMapper, "typeMapper"); 358 ArgumentNotNull(ef, "ef"); 359 360 _code = code; 361 _typeMapper = typeMapper; 362 _ef = ef; 363 } 364 365 public string Property(EdmProperty edmProperty) 366 { 367 return string.Format( 368 CultureInfo.InvariantCulture, 369 "{0} {1} {2} {{ {3}get; {4}set; }}", 370 Accessibility.ForProperty(edmProperty), 371 _typeMapper.GetTypeName(edmProperty.TypeUsage), 372 _code.Escape(edmProperty), 373 _code.SpaceAfter(Accessibility.ForGetter(edmProperty)), 374 _code.SpaceAfter(Accessibility.ForSetter(edmProperty))); 375 } 376 377 public string NavigationProperty(NavigationProperty navProp) 378 { 379 var endType = _typeMapper.GetTypeName(navProp.ToEndMember.GetEntityType()); 380 return string.Format( 381 CultureInfo.InvariantCulture, 382 "{0} {1} {2} {{ {3}get; {4}set; }}", 383 AccessibilityAndVirtual(Accessibility.ForNavigationProperty(navProp)), 384 navProp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many ? ("ICollection<" + endType + ">") : endType, 385 _code.Escape(navProp), 386 _code.SpaceAfter(Accessibility.ForGetter(navProp)), 387 _code.SpaceAfter(Accessibility.ForSetter(navProp))); 388 } 389 390 public string AccessibilityAndVirtual(string accessibility) 391 { 392 return accessibility + (accessibility != "private" ? " virtual" : ""); 393 } 394 395 public string EntityClassOpening(EntityType entity) 396 { 397 return string.Format( 398 CultureInfo.InvariantCulture, 399 "{0} {1}partial class {2}{3}", 400 Accessibility.ForType(entity), 401 _code.SpaceAfter(_code.AbstractOption(entity)), 402 _code.Escape(entity), 403 _code.StringBefore(" : ", _typeMapper.GetTypeName(entity.BaseType))); 404 } 405 406 public string EnumOpening(SimpleType enumType) 407 { 408 return string.Format( 409 CultureInfo.InvariantCulture, 410 "{0} enum {1} : {2}", 411 Accessibility.ForType(enumType), 412 _code.Escape(enumType), 413 _code.Escape(_typeMapper.UnderlyingClrType(enumType))); 414 } 415 416 public void WriteFunctionParameters(EdmFunction edmFunction, Action<string, string, string, string> writeParameter) 417 { 418 var parameters = FunctionImportParameter.Create(edmFunction.Parameters, _code, _ef); 419 foreach (var parameter in parameters.Where(p => p.NeedsLocalVariable)) 420 { 421 var isNotNull = parameter.IsNullableOfT ? parameter.FunctionParameterName + ".HasValue" : parameter.FunctionParameterName + " != null"; 422 var notNullInit = "new ObjectParameter(\"" + parameter.EsqlParameterName + "\", " + parameter.FunctionParameterName + ")"; 423 var nullInit = "new ObjectParameter(\"" + parameter.EsqlParameterName + "\", typeof(" + TypeMapper.FixNamespaces(parameter.RawClrTypeName) + "))"; 424 writeParameter(parameter.LocalVariableName, isNotNull, notNullInit, nullInit); 425 } 426 } 427 428 public string ComposableFunctionMethod(EdmFunction edmFunction, string modelNamespace) 429 { 430 var parameters = _typeMapper.GetParameters(edmFunction); 431 432 return string.Format( 433 CultureInfo.InvariantCulture, 434 "{0} IQueryable<{1}> {2}({3})", 435 AccessibilityAndVirtual(Accessibility.ForMethod(edmFunction)), 436 _typeMapper.GetTypeName(_typeMapper.GetReturnType(edmFunction), modelNamespace), 437 _code.Escape(edmFunction), 438 string.Join(", ", parameters.Select(p => TypeMapper.FixNamespaces(p.FunctionParameterType) + " " + p.FunctionParameterName).ToArray())); 439 } 440 441 public string ComposableCreateQuery(EdmFunction edmFunction, string modelNamespace) 442 { 443 var parameters = _typeMapper.GetParameters(edmFunction); 444 445 return string.Format( 446 CultureInfo.InvariantCulture, 447 "return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<{0}>(\"[{1}].[{2}]({3})\"{4});", 448 _typeMapper.GetTypeName(_typeMapper.GetReturnType(edmFunction), modelNamespace), 449 edmFunction.NamespaceName, 450 edmFunction.Name, 451 string.Join(", ", parameters.Select(p => "@" + p.EsqlParameterName).ToArray()), 452 _code.StringBefore(", ", string.Join(", ", parameters.Select(p => p.ExecuteParameterName).ToArray()))); 453 } 454 455 public string FunctionMethod(EdmFunction edmFunction, string modelNamespace, bool includeMergeOption) 456 { 457 var parameters = _typeMapper.GetParameters(edmFunction); 458 var returnType = _typeMapper.GetReturnType(edmFunction); 459 460 var paramList = String.Join(", ", parameters.Select(p => TypeMapper.FixNamespaces(p.FunctionParameterType) + " " + p.FunctionParameterName).ToArray()); 461 if (includeMergeOption) 462 { 463 paramList = _code.StringAfter(paramList, ", ") + "MergeOption mergeOption"; 464 } 465 466 return string.Format( 467 CultureInfo.InvariantCulture, 468 "{0} {1} {2}({3})", 469 AccessibilityAndVirtual(Accessibility.ForMethod(edmFunction)), 470 returnType == null ? "int" : "ObjectResult<" + _typeMapper.GetTypeName(returnType, modelNamespace) + ">", 471 _code.Escape(edmFunction), 472 paramList); 473 } 474 475 public string ExecuteFunction(EdmFunction edmFunction, string modelNamespace, bool includeMergeOption) 476 { 477 var parameters = _typeMapper.GetParameters(edmFunction); 478 var returnType = _typeMapper.GetReturnType(edmFunction); 479 480 var callParams = _code.StringBefore(", ", String.Join(", ", parameters.Select(p => p.ExecuteParameterName).ToArray())); 481 if (includeMergeOption) 482 { 483 callParams = ", mergeOption" + callParams; 484 } 485 486 return string.Format( 487 CultureInfo.InvariantCulture, 488 "return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction{0}(\"{1}\"{2});", 489 returnType == null ? "" : "<" + _typeMapper.GetTypeName(returnType, modelNamespace) + ">", 490 edmFunction.Name, 491 callParams); 492 } 493 494 public string DbSet(EntitySet entitySet) 495 { 496 return string.Format( 497 CultureInfo.InvariantCulture, 498 "{0} virtual DbSet<{1}> {2} {{ get; set; }}", 499 Accessibility.ForReadOnlyProperty(entitySet), 500 _typeMapper.GetTypeName(entitySet.ElementType), 501 _code.Escape(entitySet)); 502 } 503 504 public string UsingDirectives(bool inHeader, bool includeCollections = true) 505 { 506 return inHeader == string.IsNullOrEmpty(_code.VsNamespaceSuggestion()) 507 ? string.Format( 508 CultureInfo.InvariantCulture, 509 "{0}using System;{1}" + 510 "{2}", 511 inHeader ? Environment.NewLine : "", 512 includeCollections ? (Environment.NewLine + "using System.Collections.Generic;") : "", 513 inHeader ? "" : Environment.NewLine) 514 : ""; 515 } 516 } 517 518 public class TypeMapper 519 { 520 private const string ExternalTypeNameAttributeName = @"http://schemas.microsoft.com/ado/2006/04/codegeneration:ExternalTypeName"; 521 522 private readonly System.Collections.IList _errors; 523 private readonly CodeGenerationTools _code; 524 private readonly MetadataTools _ef; 525 526 public TypeMapper(CodeGenerationTools code, MetadataTools ef, System.Collections.IList errors) 527 { 528 ArgumentNotNull(code, "code"); 529 ArgumentNotNull(ef, "ef"); 530 ArgumentNotNull(errors, "errors"); 531 532 _code = code; 533 _ef = ef; 534 _errors = errors; 535 } 536 537 public static string FixNamespaces(string typeName) 538 { 539 return typeName.Replace("System.Data.Spatial.", "System.Data.Entity.Spatial."); 540 } 541 542 public string GetTypeName(TypeUsage typeUsage) 543 { 544 return typeUsage == null ? null : GetTypeName(typeUsage.EdmType, _ef.IsNullable(typeUsage), modelNamespace: null); 545 } 546 547 public string GetTypeName(EdmType edmType) 548 { 549 return GetTypeName(edmType, isNullable: null, modelNamespace: null); 550 } 551 552 public string GetTypeName(TypeUsage typeUsage, string modelNamespace) 553 { 554 return typeUsage == null ? null : GetTypeName(typeUsage.EdmType, _ef.IsNullable(typeUsage), modelNamespace); 555 } 556 557 public string GetTypeName(EdmType edmType, string modelNamespace) 558 { 559 return GetTypeName(edmType, isNullable: null, modelNamespace: modelNamespace); 560 } 561 562 public string GetTypeName(EdmType edmType, bool? isNullable, string modelNamespace) 563 { 564 if (edmType == null) 565 { 566 return null; 567 } 568 569 var collectionType = edmType as CollectionType; 570 if (collectionType != null) 571 { 572 return String.Format(CultureInfo.InvariantCulture, "ICollection<{0}>", GetTypeName(collectionType.TypeUsage, modelNamespace)); 573 } 574 575 var typeName = _code.Escape(edmType.MetadataProperties 576 .Where(p => p.Name == ExternalTypeNameAttributeName) 577 .Select(p => (string)p.Value) 578 .FirstOrDefault()) 579 ?? (modelNamespace != null && edmType.NamespaceName != modelNamespace ? 580 _code.CreateFullName(_code.EscapeNamespace(edmType.NamespaceName), _code.Escape(edmType)) : 581 _code.Escape(edmType)); 582 583 if (edmType is StructuralType) 584 { 585 return typeName; 586 } 587 588 if (edmType is SimpleType) 589 { 590 var clrType = UnderlyingClrType(edmType); 591 if (!IsEnumType(edmType)) 592 { 593 typeName = _code.Escape(clrType); 594 } 595 596 typeName = FixNamespaces(typeName); 597 598 return clrType.IsValueType && isNullable == true ? 599 String.Format(CultureInfo.InvariantCulture, "Nullable<{0}>", typeName) : 600 typeName; 601 } 602 603 throw new ArgumentException("edmType"); 604 } 605 606 public Type UnderlyingClrType(EdmType edmType) 607 { 608 ArgumentNotNull(edmType, "edmType"); 609 610 var primitiveType = edmType as PrimitiveType; 611 if (primitiveType != null) 612 { 613 return primitiveType.ClrEquivalentType; 614 } 615 616 if (IsEnumType(edmType)) 617 { 618 return GetEnumUnderlyingType(edmType).ClrEquivalentType; 619 } 620 621 return typeof(object); 622 } 623 624 public object GetEnumMemberValue(MetadataItem enumMember) 625 { 626 ArgumentNotNull(enumMember, "enumMember"); 627 628 var valueProperty = enumMember.GetType().GetProperty("Value"); 629 return valueProperty == null ? null : valueProperty.GetValue(enumMember, null); 630 } 631 632 public string GetEnumMemberName(MetadataItem enumMember) 633 { 634 ArgumentNotNull(enumMember, "enumMember"); 635 636 var nameProperty = enumMember.GetType().GetProperty("Name"); 637 return nameProperty == null ? null : (string)nameProperty.GetValue(enumMember, null); 638 } 639 640 public System.Collections.IEnumerable GetEnumMembers(EdmType enumType) 641 { 642 ArgumentNotNull(enumType, "enumType"); 643 644 var membersProperty = enumType.GetType().GetProperty("Members"); 645 return membersProperty != null 646 ? (System.Collections.IEnumerable)membersProperty.GetValue(enumType, null) 647 : Enumerable.Empty<MetadataItem>(); 648 } 649 650 public bool EnumIsFlags(EdmType enumType) 651 { 652 ArgumentNotNull(enumType, "enumType"); 653 654 var isFlagsProperty = enumType.GetType().GetProperty("IsFlags"); 655 return isFlagsProperty != null && (bool)isFlagsProperty.GetValue(enumType, null); 656 } 657 658 public bool IsEnumType(GlobalItem edmType) 659 { 660 ArgumentNotNull(edmType, "edmType"); 661 662 return edmType.GetType().Name == "EnumType"; 663 } 664 665 public PrimitiveType GetEnumUnderlyingType(EdmType enumType) 666 { 667 ArgumentNotNull(enumType, "enumType"); 668 669 return (PrimitiveType)enumType.GetType().GetProperty("UnderlyingType").GetValue(enumType, null); 670 } 671 672 public string CreateLiteral(object value) 673 { 674 if (value == null || value.GetType() != typeof(TimeSpan)) 675 { 676 return _code.CreateLiteral(value); 677 } 678 679 return string.Format(CultureInfo.InvariantCulture, "new TimeSpan({0})", ((TimeSpan)value).Ticks); 680 } 681 682 public bool VerifyCaseInsensitiveTypeUniqueness(IEnumerable<string> types, string sourceFile) 683 { 684 ArgumentNotNull(types, "types"); 685 ArgumentNotNull(sourceFile, "sourceFile"); 686 687 var hash = new HashSet<string>(StringComparer.InvariantCultureIgnoreCase); 688 if (types.Any(item => !hash.Add(item))) 689 { 690 _errors.Add( 691 new CompilerError(sourceFile, -1, -1, "6023", 692 String.Format(CultureInfo.CurrentCulture, CodeGenerationTools.GetResourceString("Template_CaseInsensitiveTypeConflict")))); 693 return false; 694 } 695 return true; 696 } 697 698 public IEnumerable<SimpleType> GetEnumItemsToGenerate(IEnumerable<GlobalItem> itemCollection) 699 { 700 return GetItemsToGenerate<SimpleType>(itemCollection) 701 .Where(e => IsEnumType(e)); 702 } 703 704 public IEnumerable<T> GetItemsToGenerate<T>(IEnumerable<GlobalItem> itemCollection) where T: EdmType 705 { 706 return itemCollection 707 .OfType<T>() 708 .Where(i => !i.MetadataProperties.Any(p => p.Name == ExternalTypeNameAttributeName)) 709 .OrderBy(i => i.Name); 710 } 711 712 public IEnumerable<string> GetAllGlobalItems(IEnumerable<GlobalItem> itemCollection) 713 { 714 return itemCollection 715 .Where(i => i is EntityType || i is ComplexType || i is EntityContainer || IsEnumType(i)) 716 .Select(g => GetGlobalItemName(g)); 717 } 718 719 public string GetGlobalItemName(GlobalItem item) 720 { 721 if (item is EdmType) 722 { 723 return ((EdmType)item).Name; 724 } 725 else 726 { 727 return ((EntityContainer)item).Name; 728 } 729 } 730 731 public IEnumerable<EdmProperty> GetSimpleProperties(EntityType type) 732 { 733 return type.Properties.Where(p => p.TypeUsage.EdmType is SimpleType && p.DeclaringType == type); 734 } 735 736 public IEnumerable<EdmProperty> GetSimpleProperties(ComplexType type) 737 { 738 return type.Properties.Where(p => p.TypeUsage.EdmType is SimpleType && p.DeclaringType == type); 739 } 740 741 public IEnumerable<EdmProperty> GetComplexProperties(EntityType type) 742 { 743 return type.Properties.Where(p => p.TypeUsage.EdmType is ComplexType && p.DeclaringType == type); 744 } 745 746 public IEnumerable<EdmProperty> GetComplexProperties(ComplexType type) 747 { 748 return type.Properties.Where(p => p.TypeUsage.EdmType is ComplexType && p.DeclaringType == type); 749 } 750 751 public IEnumerable<EdmProperty> GetPropertiesWithDefaultValues(EntityType type) 752 { 753 return type.Properties.Where(p => p.TypeUsage.EdmType is SimpleType && p.DeclaringType == type && p.DefaultValue != null); 754 } 755 756 public IEnumerable<EdmProperty> GetPropertiesWithDefaultValues(ComplexType type) 757 { 758 return type.Properties.Where(p => p.TypeUsage.EdmType is SimpleType && p.DeclaringType == type && p.DefaultValue != null); 759 } 760 761 public IEnumerable<NavigationProperty> GetNavigationProperties(EntityType type) 762 { 763 return type.NavigationProperties.Where(np => np.DeclaringType == type); 764 } 765 766 public IEnumerable<NavigationProperty> GetCollectionNavigationProperties(EntityType type) 767 { 768 return type.NavigationProperties.Where(np => np.DeclaringType == type && np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many); 769 } 770 771 public FunctionParameter GetReturnParameter(EdmFunction edmFunction) 772 { 773 ArgumentNotNull(edmFunction, "edmFunction"); 774 775 var returnParamsProperty = edmFunction.GetType().GetProperty("ReturnParameters"); 776 return returnParamsProperty == null 777 ? edmFunction.ReturnParameter 778 : ((IEnumerable<FunctionParameter>)returnParamsProperty.GetValue(edmFunction, null)).FirstOrDefault(); 779 } 780 781 public bool IsComposable(EdmFunction edmFunction) 782 { 783 ArgumentNotNull(edmFunction, "edmFunction"); 784 785 var isComposableProperty = edmFunction.GetType().GetProperty("IsComposableAttribute"); 786 return isComposableProperty != null && (bool)isComposableProperty.GetValue(edmFunction, null); 787 } 788 789 public IEnumerable<FunctionImportParameter> GetParameters(EdmFunction edmFunction) 790 { 791 return FunctionImportParameter.Create(edmFunction.Parameters, _code, _ef); 792 } 793 794 public TypeUsage GetReturnType(EdmFunction edmFunction) 795 { 796 var returnParam = GetReturnParameter(edmFunction); 797 return returnParam == null ? null : _ef.GetElementType(returnParam.TypeUsage); 798 } 799 800 public bool GenerateMergeOptionFunction(EdmFunction edmFunction, bool includeMergeOption) 801 { 802 var returnType = GetReturnType(edmFunction); 803 return !includeMergeOption && returnType != null && returnType.EdmType.BuiltInTypeKind == BuiltInTypeKind.EntityType; 804 } 805 } 806 807 public static void ArgumentNotNull<T>(T arg, string name) where T : class 808 { 809 if (arg == null) 810 { 811 throw new ArgumentNullException(name); 812 } 813 } 814 #>
接下来BLL层，和DAL层类似，添加一个IObjeceBLL接口，添加运行时文本模板BLLTextTemplate.tt，代码如下，ctrl+s保存就会自动生成相应的代码了。
1 public interface IObjeceBLL<TEntity> 2 { 3 /// <summary> 4 /// 获取所有实体对象 5 /// </summary> 6 /// <returns></returns> 7 List<TEntity> GetAll(); 8 9 /// <summary> 10 /// 根据条件获取单个实体 11 /// </summary> 12 /// <returns></returns> 13 TEntity GetObjectByConditionFrist(string whereCondition); 14 15 /// <summary> 16 /// 根据条件获取实体 17 /// </summary> 18 /// <returns></returns> 19 List<TEntity> GetObjectByCondition(string whereCondition); 20 21 /// <summary> 22 /// 根据多条件获取单个实体 23 /// </summary> 24 /// <returns></returns> 25 TEntity GetObjectByConditionFrist(object whereCondition); 26 27 /// <summary> 28 /// 根据多条件获取实体 29 /// </summary> 30 /// <param name="whereCondition"></param> 31 /// <returns></returns> 32 List<TEntity> GetObjectByCondition(object whereCondition); 33 34 /// <summary> 35 /// 更具ID判断是否存在 36 /// </summary> 37 /// <param name="id"></param> 38 /// <returns></returns> 39 bool Exists(string id); 40 41 /// <summary> 42 /// 插入列表信息 43 /// </summary> 44 /// <param name="objectIQuery"></param> 45 /// <returns></returns> 46 List<TEntity> InsertObjectList(List<TEntity> objectList); 47 48 /// <summary> 49 /// 插入对象信息 50 /// </summary> 51 /// <param name="objectInfo"></param> 52 /// <returns></returns> 53 int? InsertObject(TEntity objectInfo); 54 55 /// <summary> 56 /// 更新对象信息 57 /// </summary> 58 /// <param name="objectInfo"></param> 59 /// <returns></returns> 60 bool UpdateObject(TEntity objectInfo); 61 62 /// <summary> 63 /// 删除对象信息 64 /// </summary> 65 /// <param name="objectInfo"></param> 66 /// <returns></returns> 67 bool DeleteObjectList(List<TEntity> objectInfoList); 68 69 /// <summary> 70 /// 删除选定的对象 71 /// </summary> 72 /// <param name="ObjectInfo"></param> 73 /// <returns></returns> 74 bool Delete(TEntity objectInfo); 75 }
1 <#@ template debug="false" hostspecific="true" language="C#" #> 2 <#@ assembly name="System.Core" #> 3 <#@ import namespace="System.Linq" #> 4 <#@ import namespace="System.Text" #> 5 <#@ import namespace="System.Collections.Generic" #> 6 <#@ include file="EF6.Utility.CS.ttinclude"#> 7 <#@ output extension=".cs" #> 8 9 <# 10 const string inputFile = @"..\SqlModel\Model.edmx"; 11 var textTransform = DynamicTextTransformation.Create(this); 12 var code = new CodeGenerationTools(this); 13 var ef = new MetadataTools(this); 14 var typeMapper = new TypeMapper(code, ef, textTransform.Errors); 15 var fileManager = EntityFrameworkTemplateFileManager.Create(this); 16 var itemCollection = new EdmMetadataLoader(textTransform.Host, textTransform.Errors).CreateEdmItemCollection(inputFile); 17 var codeStringGenerator = new CodeStringGenerator(code, typeMapper, ef); 18 19 if (!typeMapper.VerifyCaseInsensitiveTypeUniqueness(typeMapper.GetAllGlobalItems(itemCollection), inputFile)) 20 { 21 return string.Empty; 22 } 23 24 foreach (var entity in typeMapper.GetItemsToGenerate<EntityType>(itemCollection)) 25 { 26 fileManager.StartNewFile(entity.Name + "BLL.cs"); 27 28 #> 29 using System; 30 using System.Collections.Generic; 31 using System.Linq; 32 using System.Text; 33 using System.Threading.Tasks; 34 using SqlModel; 35 using DAL; 36 using Dapper; 37 using Common; 38 using System.Data.SqlClient; 39 using LogManager; 40 using DBHelp; 41 using System.Data; 42 namespace BLL 43 { 44 [Table("<#=entity.Name#>")] 45 public partial class <#=entity.Name#>BLL : IObjeceBLL<<#=entity.Name#>> 46 { 47 public <#=entity.Name#>DAL ObjectDAL { get; set; } 48 public List<<#=entity.Name#>DAL> ObjectDALList { get; set; } 49 50 public DbHelper DBHelper {set; get; } 51 52 public <#=entity.Name#>BLL() 53 { 54 //获取连接字符串 55 ConnectStringManager manager = new ConnectStringManager(); 56 string connectionString = manager.GetDefaultString(); 57 IDbConnection connection = DBConnectManager.CreatDBConnect(connectionString); 58 59 //初始化数据库操作类 60 DBHelper = new DbHelper(connection); 61 ObjectDAL = new <#=entity.Name#>DAL(DBHelper); 62 } 63 64 public <#=entity.Name#>BLL(DbHelper dbhelper) 65 { 66 DBHelper = dbhelper; 67 ObjectDAL = new <#=entity.Name#>DAL(DBHelper); 68 } 69 70 public <#=entity.Name#>BLL(string connectionStrings) 71 { 72 IDbConnection connection = DBConnectManager.CreatDBConnect(connectionStrings); 73 74 //初始化数据库操作类 75 DBHelper = new DbHelper(connection); 76 ObjectDAL = new <#=entity.Name#>DAL(DBHelper); 77 } 78 79 public <#=entity.Name#>BLL(List<string> connectionStringsList) 80 { 81 foreach( string connectionstrings in connectionStringsList ) 82 { 83 IDbConnection connection = DBConnectManager.CreatDBConnect(connectionstrings); 84 85 DBHelper = new DbHelper(connection); 86 <#=entity.Name#>DAL objectDAL = new <#=entity.Name#>DAL(DBHelper); 87 ObjectDALList.Add(objectDAL); 88 } 89 } 90 91 /// <summary> 92 /// 获取所有实体对象 93 /// </summary> 94 /// <returns></returns> 95 public List<<#=entity.Name#>> GetAll() 96 { 97 try 98 { 99 IDbConnection connection = DBHelper.GetConnection(); 100 if ( connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed ) 101 connection.Open(); 102 103 List<<#=entity.Name#>> objectList = ObjectDAL.GetAll(); 104 connection.Close(); 105 return objectList; 106 } 107 catch (Exception ex) 108 { 109 LogEvent.LogInfo.Fatal(ex.ToString()); 110 } 111 return null; 112 } 113 114 /// <summary> 115 /// 根据条件获取实体 116 /// </summary> 117 /// <returns></returns> 118 public List<<#=entity.Name#>> GetObjectByCondition(string whereCondition) 119 { 120 try 121 { 122 IDbConnection connection = DBHelper.GetConnection(); 123 if ( connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed ) 124 connection.Open(); 125 126 List<<#=entity.Name#>> objectList = ObjectDAL.GetObjectByCondition( whereCondition ); 127 connection.Close(); 128 return objectList; 129 } 130 catch (Exception ex) 131 { 132 LogEvent.LogInfo.Fatal(ex.ToString()); 133 } 134 return null; 135 } 136 137 138 /// <summary> 139 /// 根据条件获取实体 140 /// </summary> 141 /// <returns></returns> 142 public <#=entity.Name#> GetObjectByConditionFrist(string whereCondition) 143 { 144 <#=entity.Name#> ObjectInfo = null; 145 try 146 { 147 IDbConnection connection = DBHelper.GetConnection(); 148 if ( connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed ) 149 connection.Open(); 150 151 List<<#=entity.Name#>> ObjectList = ObjectDAL.GetObjectByCondition( whereCondition ); 152 connection.Close(); 153 154 if ( ObjectList == null || ObjectList.Count == 0 ) 155 { 156 return null; 157 } 158 159 ObjectInfo = ObjectList[0]; 160 161 } 162 catch (Exception ex) 163 { 164 LogEvent.LogInfo.Fatal(ex.ToString()); 165 } 166 return ObjectInfo; 167 } 168 169 /// <summary> 170 /// 根据多条件获取实体 171 /// </summary> 172 /// <param name="whereCondition"></param> 173 /// <returns></returns> 174 public List<<#=entity.Name#>> GetObjectByCondition(object whereCondition) 175 { 176 try 177 { 178 IDbConnection connection = DBHelper.GetConnection(); 179 if ( connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed ) 180 connection.Open(); 181 182 List<<#=entity.Name#>> objectList = ObjectDAL.GetObjectByCondition( whereCondition ); 183 connection.Close(); 184 return objectList; 185 } 186 catch (Exception ex) 187 { 188 LogEvent.LogInfo.Fatal(ex.ToString()); 189 } 190 return null; 191 } 192 193 /// <summary> 194 /// 根据多条件获取单个实体 195 /// </summary> 196 /// <returns></returns> 197 public <#=entity.Name#> GetObjectByConditionFrist(object whereCondition) 198 { 199 <#=entity.Name#> ObjectInfo = null; 200 try 201 { 202 IDbConnection connection = DBHelper.GetConnection(); 203 if ( connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed ) 204 connection.Open(); 205 206 List<<#=entity.Name#>> ObjectList = ObjectDAL.GetObjectByCondition( whereCondition ); 207 connection.Close(); 208 if ( ObjectList == null || ObjectList.Count == 0 ) 209 { 210 return null; 211 } 212 213 ObjectInfo = ObjectList[0]; 214 } 215 catch (Exception ex) 216 { 217 LogEvent.LogInfo.Fatal(ex.ToString()); 218 } 219 return ObjectInfo; 220 } 221 222 /// <summary> 223 /// 更具ID判断是否存在 224 /// </summary> 225 /// <param name="id"></param> 226 /// <returns></returns> 227 public bool Exists(string id) 228 { 229 try 230 { 231 IDbConnection connection = DBHelper.GetConnection(); 232 if ( connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed ) 233 connection.Open(); 234 235 bool result = ObjectDAL.Exists(id); 236 connection.Close(); 237 return result; 238 } 239 catch (Exception ex) 240 { 241 LogEvent.LogInfo.Fatal(ex.ToString()); 242 } 243 return false; 244 } 245 246 /// <summary> 247 /// 插入列表信息 248 /// </summary> 249 /// <param name="objectIQuery"></param> 250 /// <returns></returns> 251 public List<<#=entity.Name#>> InsertObjectList(List<<#=entity.Name#>> objectList) 252 { 253 IDbTransaction transaction = null; 254 List<<#=entity.Name#>> errorList = null; 255 try 256 { 257 IDbConnection connection = DBHelper.GetConnection(); 258 if ( connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed ) 259 connection.Open(); 260 transaction = connection.BeginTransaction(); 261 262 errorList = ObjectDAL.InsertObjectList(objectList, transaction); 263 transaction.Commit(); 264 connection.Close(); 265 } 266 catch (Exception ex) 267 { 268 if(transaction != null) 269 transaction.Rollback(); 270 LogEvent.LogInfo.Fatal(ex.ToString()); 271 } 272 return errorList; 273 } 274 275 /// <summary> 276 /// 插入对象信息 277 /// </summary> 278 /// <param name="objectInfo"></param> 279 /// <returns></returns> 280 public int? InsertObject(<#=entity.Name#> objectInfo) 281 { 282 try 283 { 284 IDbConnection connection = DBHelper.GetConnection(); 285 if ( connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed ) 286 connection.Open(); 287 288 int? result = ObjectDAL.InsertObject(objectInfo); 289 connection.Close(); 290 return result; 291 } 292 catch (Exception ex) 293 { 294 LogEvent.LogInfo.Fatal(ex.ToString()); 295 } 296 return null; 297 } 298 299 /// <summary> 300 /// 更新对象信息 301 /// </summary> 302 /// <param name="objectInfo"></param> 303 /// <returns></returns> 304 public bool UpdateObjectList(List<<#=entity.Name#>> objectInfoList) 305 { 306 IDbTransaction transaction = null; 307 bool result = false; 308 try 309 { 310 IDbConnection connection = DBHelper.GetConnection(); 311 if ( connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed ) 312 connection.Open(); 313 transaction = connection.BeginTransaction(); 314 315 result = ObjectDAL.UpdateObjectList(objectInfoList, transaction); 316 transaction.Commit(); 317 connection.Close(); 318 } 319 catch (Exception ex) 320 { 321 if(transaction != null) 322 transaction.Rollback(); 323 LogEvent.LogInfo.Fatal(ex.ToString()); 324 } 325 326 return result; 327 } 328 329 /// <summary> 330 /// 更新对象信息 331 /// </summary> 332 /// <param name="objectInfo"></param> 333 /// <returns></returns> 334 public bool UpdateObject(<#=entity.Name#> objectInfo) 335 { 336 try 337 { 338 IDbConnection connection = DBHelper.GetConnection(); 339 340 if ( connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed ) 341 connection.Open(); 342 343 bool result = ObjectDAL.UpdateObject(objectInfo); 344 connection.Close(); 345 return result; 346 } 347 catch (Exception ex) 348 { 349 LogEvent.LogInfo.Fatal(ex.ToString()); 350 } 351 return false; 352 } 353 354 /// <summary> 355 /// 删除对象信息 356 /// </summary> 357 /// <param name="objectInfo"></param> 358 /// <returns></returns> 359 public bool DeleteObjectList(List<<#=entity.Name#>> objectInfoList) 360 { 361 IDbTransaction transaction = null; 362 bool result = false; 363 try 364 { 365 IDbConnection connection = DBHelper.GetConnection(); 366 if ( connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed ) 367 connection.Open(); 368 transaction = connection.BeginTransaction(); 369 370 371 result = ObjectDAL.DeleteObjectList(objectInfoList, transaction); 372 transaction.Commit(); 373 connection.Close(); 374 } 375 catch (Exception ex) 376 { 377 if ( transaction != null ) 378 transaction.Rollback(); 379 LogEvent.LogInfo.Fatal(ex.ToString()); 380 } 381 382 return result; 383 } 384 385 /// <summary> 386 /// 删除选定的对象 387 /// </summary> 388 /// <param name="ObjectInfo"></param> 389 /// <returns></returns> 390 public bool Delete(<#=entity.Name#> objectInfo) 391 { 392 try 393 { 394 IDbConnection connection = DBHelper.GetConnection(); 395 if ( connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed ) 396 connection.Open(); 397 398 bool result = ObjectDAL.Delete(objectInfo); 399 connection.Close(); 400 return result; 401 } 402 catch (Exception ex) 403 { 404 LogEvent.LogInfo.Fatal(ex.ToString()); 405 } 406 return false; 407 } 408 } 409 } 410 411 <# 412 fileManager.EndBlock(); 413 } 414 fileManager.Process(); 415 #> 416 417 <#+ 418 public const string TemplateId = "CSharp_DbContext_Types_EF6"; 419 420 public class CodeStringGenerator 421 { 422 private readonly CodeGenerationTools _code; 423 private readonly TypeMapper _typeMapper; 424 private readonly MetadataTools _ef; 425 426 public CodeStringGenerator(CodeGenerationTools code, TypeMapper typeMapper, MetadataTools ef) 427 { 428 ArgumentNotNull(code, "code"); 429 ArgumentNotNull(typeMapper, "typeMapper"); 430 ArgumentNotNull(ef, "ef"); 431 432 _code = code; 433 _typeMapper = typeMapper; 434 _ef = ef; 435 } 436 437 public string Property(EdmProperty edmProperty) 438 { 439 return string.Format( 440 CultureInfo.InvariantCulture, 441 "{0} {1} {2} {{ {3}get; {4}set; }}", 442 Accessibility.ForProperty(edmProperty), 443 _typeMapper.GetTypeName(edmProperty.TypeUsage), 444 _code.Escape(edmProperty), 445 _code.SpaceAfter(Accessibility.ForGetter(edmProperty)), 446 _code.SpaceAfter(Accessibility.ForSetter(edmProperty))); 447 } 448 449 public string NavigationProperty(NavigationProperty navProp) 450 { 451 var endType = _typeMapper.GetTypeName(navProp.ToEndMember.GetEntityType()); 452 return string.Format( 453 CultureInfo.InvariantCulture, 454 "{0} {1} {2} {{ {3}get; {4}set; }}", 455 AccessibilityAndVirtual(Accessibility.ForNavigationProperty(navProp)), 456 navProp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many ? ("ICollection<" + endType + ">") : endType, 457 _code.Escape(navProp), 458 _code.SpaceAfter(Accessibility.ForGetter(navProp)), 459 _code.SpaceAfter(Accessibility.ForSetter(navProp))); 460 } 461 462 public string AccessibilityAndVirtual(string accessibility) 463 { 464 return accessibility + (accessibility != "private" ? " virtual" : ""); 465 } 466 467 public string EntityClassOpening(EntityType entity) 468 { 469 return string.Format( 470 CultureInfo.InvariantCulture, 471 "{0} {1}partial class {2}{3}", 472 Accessibility.ForType(entity), 473 _code.SpaceAfter(_code.AbstractOption(entity)), 474 _code.Escape(entity), 475 _code.StringBefore(" : ", _typeMapper.GetTypeName(entity.BaseType))); 476 } 477 478 public string EnumOpening(SimpleType enumType) 479 { 480 return string.Format( 481 CultureInfo.InvariantCulture, 482 "{0} enum {1} : {2}", 483 Accessibility.ForType(enumType), 484 _code.Escape(enumType), 485 _code.Escape(_typeMapper.UnderlyingClrType(enumType))); 486 } 487 488 public void WriteFunctionParameters(EdmFunction edmFunction, Action<string, string, string, string> writeParameter) 489 { 490 var parameters = FunctionImportParameter.Create(edmFunction.Parameters, _code, _ef); 491 foreach (var parameter in parameters.Where(p => p.NeedsLocalVariable)) 492 { 493 var isNotNull = parameter.IsNullableOfT ? parameter.FunctionParameterName + ".HasValue" : parameter.FunctionParameterName + " != null"; 494 var notNullInit = "new ObjectParameter(\"" + parameter.EsqlParameterName + "\", " + parameter.FunctionParameterName + ")"; 495 var nullInit = "new ObjectParameter(\"" + parameter.EsqlParameterName + "\", typeof(" + TypeMapper.FixNamespaces(parameter.RawClrTypeName) + "))"; 496 writeParameter(parameter.LocalVariableName, isNotNull, notNullInit, nullInit); 497 } 498 } 499 500 public string ComposableFunctionMethod(EdmFunction edmFunction, string modelNamespace) 501 { 502 var parameters = _typeMapper.GetParameters(edmFunction); 503 504 return string.Format( 505 CultureInfo.InvariantCulture, 506 "{0} IQueryable<{1}> {2}({3})", 507 AccessibilityAndVirtual(Accessibility.ForMethod(edmFunction)), 508 _typeMapper.GetTypeName(_typeMapper.GetReturnType(edmFunction), modelNamespace), 509 _code.Escape(edmFunction), 510 string.Join(", ", parameters.Select(p => TypeMapper.FixNamespaces(p.FunctionParameterType) + " " + p.FunctionParameterName).ToArray())); 511 } 512 513 public string ComposableCreateQuery(EdmFunction edmFunction, string modelNamespace) 514 { 515 var parameters = _typeMapper.GetParameters(edmFunction); 516 517 return string.Format( 518 CultureInfo.InvariantCulture, 519 "return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<{0}>(\"[{1}].[{2}]({3})\"{4});", 520 _typeMapper.GetTypeName(_typeMapper.GetReturnType(edmFunction), modelNamespace), 521 edmFunction.NamespaceName, 522 edmFunction.Name, 523 string.Join(", ", parameters.Select(p => "@" + p.EsqlParameterName).ToArray()), 524 _code.StringBefore(", ", string.Join(", ", parameters.Select(p => p.ExecuteParameterName).ToArray()))); 525 } 526 527 public string FunctionMethod(EdmFunction edmFunction, string modelNamespace, bool includeMergeOption) 528 { 529 var parameters = _typeMapper.GetParameters(edmFunction); 530 var returnType = _typeMapper.GetReturnType(edmFunction); 531 532 var paramList = String.Join(", ", parameters.Select(p => TypeMapper.FixNamespaces(p.FunctionParameterType) + " " + p.FunctionParameterName).ToArray()); 533 if (includeMergeOption) 534 { 535 paramList = _code.StringAfter(paramList, ", ") + "MergeOption mergeOption"; 536 } 537 538 return string.Format( 539 CultureInfo.InvariantCulture, 540 "{0} {1} {2}({3})", 541 AccessibilityAndVirtual(Accessibility.ForMethod(edmFunction)), 542 returnType == null ? "int" : "ObjectResult<" + _typeMapper.GetTypeName(returnType, modelNamespace) + ">", 543 _code.Escape(edmFunction), 544 paramList); 545 } 546 547 public string ExecuteFunction(EdmFunction edmFunction, string modelNamespace, bool includeMergeOption) 548 { 549 var parameters = _typeMapper.GetParameters(edmFunction); 550 var returnType = _typeMapper.GetReturnType(edmFunction); 551 552 var callParams = _code.StringBefore(", ", String.Join(", ", parameters.Select(p => p.ExecuteParameterName).ToArray())); 553 if (includeMergeOption) 554 { 555 callParams = ", mergeOption" + callParams; 556 } 557 558 return string.Format( 559 CultureInfo.InvariantCulture, 560 "return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction{0}(\"{1}\"{2});", 561 returnType == null ? "" : "<" + _typeMapper.GetTypeName(returnType, modelNamespace) + ">", 562 edmFunction.Name, 563 callParams); 564 } 565 566 public string DbSet(EntitySet entitySet) 567 { 568 return string.Format( 569 CultureInfo.InvariantCulture, 570 "{0} virtual DbSet<{1}> {2} {{ get; set; }}", 571 Accessibility.ForReadOnlyProperty(entitySet), 572 _typeMapper.GetTypeName(entitySet.ElementType), 573 _code.Escape(entitySet)); 574 } 575 576 public string UsingDirectives(bool inHeader, bool includeCollections = true) 577 { 578 return inHeader == string.IsNullOrEmpty(_code.VsNamespaceSuggestion()) 579 ? string.Format( 580 CultureInfo.InvariantCulture, 581 "{0}using System;{1}" + 582 "{2}", 583 inHeader ? Environment.NewLine : "", 584 includeCollections ? (Environment.NewLine + "using System.Collections.Generic;") : "", 585 inHeader ? "" : Environment.NewLine) 586 : ""; 587 } 588 } 589 590 public class TypeMapper 591 { 592 private const string ExternalTypeNameAttributeName = @"http://schemas.microsoft.com/ado/2006/04/codegeneration:ExternalTypeName"; 593 594 private readonly System.Collections.IList _errors; 595 private readonly CodeGenerationTools _code; 596 private readonly MetadataTools _ef; 597 598 public TypeMapper(CodeGenerationTools code, MetadataTools ef, System.Collections.IList errors) 599 { 600 ArgumentNotNull(code, "code"); 601 ArgumentNotNull(ef, "ef"); 602 ArgumentNotNull(errors, "errors"); 603 604 _code = code; 605 _ef = ef; 606 _errors = errors; 607 } 608 609 public static string FixNamespaces(string typeName) 610 { 611 return typeName.Replace("System.Data.Spatial.", "System.Data.Entity.Spatial."); 612 } 613 614 public string GetTypeName(TypeUsage typeUsage) 615 { 616 return typeUsage == null ? null : GetTypeName(typeUsage.EdmType, _ef.IsNullable(typeUsage), modelNamespace: null); 617 } 618 619 public string GetTypeName(EdmType edmType) 620 { 621 return GetTypeName(edmType, isNullable: null, modelNamespace: null); 622 } 623 624 public string GetTypeName(TypeUsage typeUsage, string modelNamespace) 625 { 626 return typeUsage == null ? null : GetTypeName(typeUsage.EdmType, _ef.IsNullable(typeUsage), modelNamespace); 627 } 628 629 public string GetTypeName(EdmType edmType, string modelNamespace) 630 { 631 return GetTypeName(edmType, isNullable: null, modelNamespace: modelNamespace); 632 } 633 634 public string GetTypeName(EdmType edmType, bool? isNullable, string modelNamespace) 635 { 636 if (edmType == null) 637 { 638 return null; 639 } 640 641 var collectionType = edmType as CollectionType; 642 if (collectionType != null) 643 { 644 return String.Format(CultureInfo.InvariantCulture, "ICollection<{0}>", GetTypeName(collectionType.TypeUsage, modelNamespace)); 645 } 646 647 var typeName = _code.Escape(edmType.MetadataProperties 648 .Where(p => p.Name == ExternalTypeNameAttributeName) 649 .Select(p => (string)p.Value) 650 .FirstOrDefault()) 651 ?? (modelNamespace != null && edmType.NamespaceName != modelNamespace ? 652 _code.CreateFullName(_code.EscapeNamespace(edmType.NamespaceName), _code.Escape(edmType)) : 653 _code.Escape(edmType)); 654 655 if (edmType is StructuralType) 656 { 657 return typeName; 658 } 659 660 if (edmType is SimpleType) 661 { 662 var clrType = UnderlyingClrType(edmType); 663 if (!IsEnumType(edmType)) 664 { 665 typeName = _code.Escape(clrType); 666 } 667 668 typeName = FixNamespaces(typeName); 669 670 return clrType.IsValueType && isNullable == true ? 671 String.Format(CultureInfo.InvariantCulture, "Nullable<{0}>", typeName) : 672 typeName; 673 } 674 675 throw new ArgumentException("edmType"); 676 } 677 678 public Type UnderlyingClrType(EdmType edmType) 679 { 680 ArgumentNotNull(edmType, "edmType"); 681 682 var primitiveType = edmType as PrimitiveType; 683 if (primitiveType != null) 684 { 685 return primitiveType.ClrEquivalentType; 686 } 687 688 if (IsEnumType(edmType)) 689 { 690 return GetEnumUnderlyingType(edmType).ClrEquivalentType; 691 } 692 693 return typeof(object); 694 } 695 696 public object GetEnumMemberValue(MetadataItem enumMember) 697 { 698 ArgumentNotNull(enumMember, "enumMember"); 699 700 var valueProperty = enumMember.GetType().GetProperty("Value"); 701 return valueProperty == null ? null : valueProperty.GetValue(enumMember, null); 702 } 703 704 public string GetEnumMemberName(MetadataItem enumMember) 705 { 706 ArgumentNotNull(enumMember, "enumMember"); 707 708 var nameProperty = enumMember.GetType().GetProperty("Name"); 709 return nameProperty == null ? null : (string)nameProperty.GetValue(enumMember, null); 710 } 711 712 public System.Collections.IEnumerable GetEnumMembers(EdmType enumType) 713 { 714 ArgumentNotNull(enumType, "enumType"); 715 716 var membersProperty = enumType.GetType().GetProperty("Members"); 717 return membersProperty != null 718 ? (System.Collections.IEnumerable)membersProperty.GetValue(enumType, null) 719 : Enumerable.Empty<MetadataItem>(); 720 } 721 722 public bool EnumIsFlags(EdmType enumType) 723 { 724 ArgumentNotNull(enumType, "enumType"); 725 726 var isFlagsProperty = enumType.GetType().GetProperty("IsFlags"); 727 return isFlagsProperty != null && (bool)isFlagsProperty.GetValue(enumType, null); 728 } 729 730 public bool IsEnumType(GlobalItem edmType) 731 { 732 ArgumentNotNull(edmType, "edmType"); 733 734 return edmType.GetType().Name == "EnumType"; 735 } 736 737 public PrimitiveType GetEnumUnderlyingType(EdmType enumType) 738 { 739 ArgumentNotNull(enumType, "enumType"); 740 741 return (PrimitiveType)enumType.GetType().GetProperty("UnderlyingType").GetValue(enumType, null); 742 } 743 744 public string CreateLiteral(object value) 745 { 746 if (value == null || value.GetType() != typeof(TimeSpan)) 747 { 748 return _code.CreateLiteral(value); 749 } 750 751 return string.Format(CultureInfo.InvariantCulture, "new TimeSpan({0})", ((TimeSpan)value).Ticks); 752 } 753 754 public bool VerifyCaseInsensitiveTypeUniqueness(IEnumerable<string> types, string sourceFile) 755 { 756 ArgumentNotNull(types, "types"); 757 ArgumentNotNull(sourceFile, "sourceFile"); 758 759 var hash = new HashSet<string>(StringComparer.InvariantCultureIgnoreCase); 760 if (types.Any(item => !hash.Add(item))) 761 { 762 _errors.Add( 763 new CompilerError(sourceFile, -1, -1, "6023", 764 String.Format(CultureInfo.CurrentCulture, CodeGenerationTools.GetResourceString("Template_CaseInsensitiveTypeConflict")))); 765 return false; 766 } 767 return true; 768 } 769 770 public IEnumerable<SimpleType> GetEnumItemsToGenerate(IEnumerable<GlobalItem> itemCollection) 771 { 772 return GetItemsToGenerate<SimpleType>(itemCollection) 773 .Where(e => IsEnumType(e)); 774 } 775 776 public IEnumerable<T> GetItemsToGenerate<T>(IEnumerable<GlobalItem> itemCollection) where T: EdmType 777 { 778 return itemCollection 779 .OfType<T>() 780 .Where(i => !i.MetadataProperties.Any(p => p.Name == ExternalTypeNameAttributeName)) 781 .OrderBy(i => i.Name); 782 } 783 784 public IEnumerable<string> GetAllGlobalItems(IEnumerable<GlobalItem> itemCollection) 785 { 786 return itemCollection 787 .Where(i => i is EntityType || i is ComplexType || i is EntityContainer || IsEnumType(i)) 788 .Select(g => GetGlobalItemName(g)); 789 } 790 791 public string GetGlobalItemName(GlobalItem item) 792 { 793 if (item is EdmType) 794 { 795 return ((EdmType)item).Name; 796 } 797 else 798 { 799 return ((EntityContainer)item).Name; 800 } 801 } 802 803 public IEnumerable<EdmProperty> GetSimpleProperties(EntityType type) 804 { 805 return type.Properties.Where(p => p.TypeUsage.EdmType is SimpleType && p.DeclaringType == type); 806 } 807 808 public IEnumerable<EdmProperty> GetSimpleProperties(ComplexType type) 809 { 810 return type.Properties.Where(p => p.TypeUsage.EdmType is SimpleType && p.DeclaringType == type); 811 } 812 813 public IEnumerable<EdmProperty> GetComplexProperties(EntityType type) 814 { 815 return type.Properties.Where(p => p.TypeUsage.EdmType is ComplexType && p.DeclaringType == type); 816 } 817 818 public IEnumerable<EdmProperty> GetComplexProperties(ComplexType type) 819 { 820 return type.Properties.Where(p => p.TypeUsage.EdmType is ComplexType && p.DeclaringType == type); 821 } 822 823 public IEnumerable<EdmProperty> GetPropertiesWithDefaultValues(EntityType type) 824 { 825 return type.Properties.Where(p => p.TypeUsage.EdmType is SimpleType && p.DeclaringType == type && p.DefaultValue != null); 826 } 827 828 public IEnumerable<EdmProperty> GetPropertiesWithDefaultValues(ComplexType type) 829 { 830 return type.Properties.Where(p => p.TypeUsage.EdmType is SimpleType && p.DeclaringType == type && p.DefaultValue != null); 831 } 832 833 public IEnumerable<NavigationProperty> GetNavigationProperties(EntityType type) 834 { 835 return type.NavigationProperties.Where(np => np.DeclaringType == type); 836 } 837 838 public IEnumerable<NavigationProperty> GetCollectionNavigationProperties(EntityType type) 839 { 840 return type.NavigationProperties.Where(np => np.DeclaringType == type && np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many); 841 } 842 843 public FunctionParameter GetReturnParameter(EdmFunction edmFunction) 844 { 845 ArgumentNotNull(edmFunction, "edmFunction"); 846 847 var returnParamsProperty = edmFunction.GetType().GetProperty("ReturnParameters"); 848 return returnParamsProperty == null 849 ? edmFunction.ReturnParameter 850 : ((IEnumerable<FunctionParameter>)returnParamsProperty.GetValue(edmFunction, null)).FirstOrDefault(); 851 } 852 853 public bool IsComposable(EdmFunction edmFunction) 854 { 855 ArgumentNotNull(edmFunction, "edmFunction"); 856 857 var isComposableProperty = edmFunction.GetType().GetProperty("IsComposableAttribute"); 858 return isComposableProperty != null && (bool)isComposableProperty.GetValue(edmFunction, null); 859 } 860 861 public IEnumerable<FunctionImportParameter> GetParameters(EdmFunction edmFunction) 862 { 863 return FunctionImportParameter.Create(edmFunction.Parameters, _code, _ef); 864 } 865 866 public TypeUsage GetReturnType(EdmFunction edmFunction) 867 { 868 var returnParam = GetReturnParameter(edmFunction); 869 return returnParam == null ? null : _ef.GetElementType(returnParam.TypeUsage); 870 } 871 872 public bool GenerateMergeOptionFunction(EdmFunction edmFunction, bool includeMergeOption) 873 { 874 var returnType = GetReturnType(edmFunction); 875 return !includeMergeOption && returnType != null && returnType.EdmType.BuiltInTypeKind == BuiltInTypeKind.EntityType; 876 } 877 } 878 879 public static void ArgumentNotNull<T>(T arg, string name) where T : class 880 { 881 if (arg == null) 882 { 883 throw new ArgumentNullException(name); 884 } 885 } 886 #>
三层结构至此生成完毕。接下里就是操作数据库了。
启动项目配置文件，appSettings中添加链接数据库的字符串，
1 <!--MySQL--> 2 <add key="ConnectionStrings" value="database=test1;server=127.0.0.1;port=3306;user id=root;password=123456;pooling=False;characterset=utf8" /> 3 <add key="SqlType" value="MySQL" />
链接字符串ConnectStringManager.cs，其中用到了一个枚举，
1 /// <summary> 2 /// 连接类型 3 /// </summary> 4 public enum EnumGetConnectStringType { Config = 1, DataBase = 2, Default = 0 }
1 public class ConnectStringManager 2 { 3 #region 属性 4 5 /// <summary> 6 /// 连接字符串 7 /// </summary> 8 private string ConnectString { set; get; } 9 10 /// <summary> 11 /// 批量操作使用的连接串LIST 12 /// </summary> 13 private List<string> ConnectStringList { set; get; } 14 15 #endregion 16 17 #region 构造 18 19 /// <summary> 20 /// 默认构造 21 /// </summary> 22 public ConnectStringManager() 23 { 24 ConnectString = ConfigurationManager.AppSettings["ConnectionStrings"].ToString(); 25 } 26 27 /// <summary> 28 /// 初始化连接方式 29 /// </summary> 30 /// <param name="type"></param> 31 public ConnectStringManager(EnumGetConnectStringType type = EnumGetConnectStringType.Default) 32 { 33 switch (type) 34 { 35 case EnumGetConnectStringType.Default: 36 break; 37 case EnumGetConnectStringType.DataBase: 38 break; 39 case EnumGetConnectStringType.Config: 40 break; 41 default: 42 break; 43 } 44 } 45 46 #endregion 47 48 #region 方法 49 50 /// <summary> 51 /// 获取操作连接字符串 52 /// </summary> 53 /// <returns></returns> 54 public string GetDefaultString() 55 { 56 return ConnectString; 57 } 58 59 /// <summary> 60 /// 获取批量连接字符串 61 /// </summary> 62 /// <returns></returns> 63 public List<string> GetConnectionStringList() 64 { 65 return ConnectStringList; 66 } 67 68 #endregion 69 70 }
数据库链接DBConnectManager.cs，这是获取数据库链接的操作，
1 public class DBConnectManager 2 { 3 #region 属性 4 5 /// <summary> 6 /// 数据库连接 7 /// </summary> 8 static private IDbConnection IConnection { get; set; } 9 10 /// <summary> 11 /// 数据库类型 12 /// </summary> 13 static private SimpleCRUD.Dialect EnumDBType { get; set; } 14 15 #endregion 16 17 #region 方法 18 19 /// <summary> 20 /// 创建数据库连接 21 /// </summary> 22 /// <param name="connectionString"></param> 23 /// <returns></returns> 24 static public IDbConnection CreatDBConnect(string connectionString) 25 { 26 EnumDBType = (SimpleCRUD.Dialect)Enum.Parse(typeof(SimpleCRUD.Dialect), ConfigurationManager.AppSettings["SqlType"].ToString()); 27 switch (EnumDBType) 28 { 29 case SimpleCRUD.Dialect.SQLServer: 30 IConnection = new SqlConnection(connectionString); 31 break; 32 case SimpleCRUD.Dialect.PostgreSQL: 33 IConnection = new NpgsqlConnection(connectionString); ; 34 break; 35 case SimpleCRUD.Dialect.MySQL: 36 IConnection = new MySqlConnection(connectionString); 37 break; 38 } 39 return IConnection; 40 } 41 42 /// <summary> 43 /// 获取数据库类型 44 /// </summary> 45 /// <returns></returns> 46 static public SimpleCRUD.Dialect GetDBType() 47 { 48 return EnumDBType; 49 } 50 51 #endregion 52 53 }
数据操作帮助类DbHelper.cs，这主要是操作数据库，增删改查等，
1 public class DbHelper 2 { 3 #region 属性 4 5 /// <summary> 6 /// 数据库链接 7 /// </summary> 8 IDbConnection Connection { set; get; } 9 10 #endregion 11 12 #region 构造 13 14 /// <summary> 15 /// 构造 16 /// </summary> 17 /// <param name="connection"></param> 18 public DbHelper(IDbConnection connection) 19 { 20 Connection = connection; 21 SimpleCRUD.SetDialect(DBConnectManager.GetDBType()); 22 } 23 24 /// <summary> 25 /// 获取链接 26 /// </summary> 27 /// <returns></returns> 28 public IDbConnection GetConnection() 29 { 30 if (Connection.State == ConnectionState.Closed) 31 Connection.Open(); 32 33 if (Connection.State == ConnectionState.Broken) 34 { 35 Connection.Close(); 36 Connection.Open(); 37 } 38 39 return Connection; 40 } 41 42 #endregion 43 44 #region 插入操作 45 46 /// <summary> 47 /// 同步插入单条数据 48 /// </summary> 49 /// <param name="entity">需要插入的数据</param> 50 /// <returns>返回新插入的记录的主键</returns> 51 public int? Insert(object entity, IDbTransaction transaction = null, int? commandTimeout = null) 52 { 53 try 54 { 55 return SimpleCRUD.Insert(Connection, entity, transaction, commandTimeout); 56 } 57 catch (Exception ex) 58 { 59 throw (ex); 60 } 61 } 62 63 /// <summary> 64 /// 同步插入单条数据 65 /// </summary> 66 /// <typeparam name="TEntity">返回新插入的记录的标识</typeparam> 67 /// <param name="entity">需要插入的数据</param> 68 /// <returns></returns> 69 public int? Insert<TEntity>(TEntity entity, IDbTransaction transaction = null, int? commandTimeout = null) 70 { 71 try 72 { 73 return SimpleCRUD.Insert<TEntity>(Connection, entity, transaction, commandTimeout); 74 } 75 catch (Exception ex) 76 { 77 throw (ex); 78 } 79 } 80 81 /// <summary> 82 /// 异步插入单条数据 83 /// </summary> 84 /// <param name="entity">需要插入的数据</param> 85 /// <returns>返回新插入的记录的主键</returns> 86 public Task<int?> InsertAsync(object entity, IDbTransaction transaction = null, int? commandTimeout = null) 87 { 88 try 89 { 90 return SimpleCRUD.InsertAsync(Connection, entity, transaction, commandTimeout); 91 } 92 catch (Exception ex) 93 { 94 throw (ex); 95 } 96 } 97 98 /// <summary> 99 /// 异步插入单条数据 100 /// </summary> 101 /// <typeparam name="TEntity">数据类型</typeparam> 102 /// <param name="entity">需要插入的数据</param> 103 /// <returns></returns> 104 public Task<int?> InsertAsync<TEntity>(TEntity entity, IDbTransaction transaction = null, int? commandTimeout = null) 105 { 106 try 107 { 108 return SimpleCRUD.InsertAsync(Connection, entity, transaction, commandTimeout); 109 } 110 catch (Exception ex) 111 { 112 throw (ex); 113 } 114 } 115 116 #endregion 117 118 #region 删除操作 119 120 /// <summary> 121 /// 通过标识删除数据库中的记录 122 /// </summary> 123 /// <typeparam name="T"></typeparam> 124 /// <param name="id">标识</param> 125 /// <returns>影响的记录数</returns> 126 public int Deleted<T>(Object id, IDbTransaction transaction = null, int? commandTimeout = null) 127 { 128 try 129 { 130 return SimpleCRUD.Delete<T>(Connection, id, transaction, commandTimeout); 131 } 132 catch (Exception ex) 133 { 134 throw (ex); 135 } 136 } 137 138 /// <summary> 139 /// 在匹配对象的数据库中删除记录 140 /// </summary> 141 /// <typeparam name="T">对象类型</typeparam> 142 /// <param name="entityToDelete">需要删除的对象</param> 143 /// <returns>影响的记录数</returns> 144 public int Deleted<T>(T entityToDelete, IDbTransaction transaction = null, int? commandTimeout = null) 145 { 146 try 147 { 148 return SimpleCRUD.Delete<T>(Connection, entityToDelete, transaction, commandTimeout); 149 } 150 catch (Exception ex) 151 { 152 throw (ex); 153 } 154 } 155 156 /// <summary> 157 /// 通过标识异步删除数据库中的记录 158 /// </summary> 159 /// <typeparam name="T"></typeparam> 160 /// <param name="id">标识</param> 161 /// <returns>影响的记录数</returns> 162 public Task<int> DeletedAsync<T>(Object id, IDbTransaction transaction = null, int? commandTimeout = null) 163 { 164 try 165 { 166 return SimpleCRUD.DeleteAsync<T>(Connection, id, transaction, commandTimeout); 167 } 168 catch (Exception ex) 169 { 170 throw (ex); 171 } 172 } 173 174 /// <summary> 175 /// 在匹配对象的数据库中异步删除记录 176 /// </summary> 177 /// <typeparam name="T">对象类型</typeparam> 178 /// <param name="entityToDelete">需要删除的对象</param> 179 /// <returns>影响的记录数</returns> 180 public Task<int> DeletedAsync<T>(T entityToDelete, IDbTransaction transaction = null, int? commandTimeout = null) 181 { 182 try 183 { 184 return SimpleCRUD.DeleteAsync<T>(Connection, entityToDelete, transaction, commandTimeout); 185 } 186 catch (Exception ex) 187 { 188 throw (ex); 189 } 190 } 191 192 /// <summary> 193 /// 根据条件删除数据库中的记录 194 /// </summary> 195 /// <typeparam name="T"></typeparam> 196 /// <param name="conditions">条件</param> 197 /// <returns>影响的行数</returns> 198 public int DeletedList<T>(string conditions, IDbTransaction transaction = null, int? commandTimeout = null) 199 { 200 try 201 { 202 return SimpleCRUD.DeleteList<T>(Connection, conditions, transaction, commandTimeout); 203 } 204 catch (Exception ex) 205 { 206 throw (ex); 207 } 208 } 209 210 /// <summary> 211 /// 删除数据库中的记录列表 212 /// </summary> 213 /// <typeparam name="T">类型</typeparam> 214 /// <param name="whereConditions">有条件的类型</param> 215 /// <returns>影响的条数</returns> 216 public int DeletedList<T>(object whereConditions, IDbTransaction transaction = null, int? commandTimeout = null) 217 { 218 try 219 { 220 return SimpleCRUD.DeleteList<T>(Connection, whereConditions, transaction, commandTimeout); 221 } 222 catch (Exception ex) 223 { 224 throw (ex); 225 } 226 } 227 228 /// <summary> 229 /// 根据条件异步删除数据库中的记录 230 /// </summary> 231 /// <typeparam name="T"></typeparam> 232 /// <param name="conditions">条件</param> 233 /// <returns>影响的行数</returns> 234 public Task<int> DeletedListAsync<T>(string conditions, IDbTransaction transaction = null, int? commandTimeout = null) 235 { 236 try 237 { 238 return SimpleCRUD.DeleteListAsync<T>(Connection, conditions, transaction, commandTimeout); 239 } 240 catch (Exception ex) 241 { 242 throw (ex); 243 } 244 } 245 246 /// <summary> 247 /// 删除数据库中的记录列表 248 /// </summary> 249 /// <typeparam name="T">类型</typeparam> 250 /// <param name="whereConditions">有条件的类型</param> 251 /// <returns>影响的条数</returns> 252 public Task<int> DeletedListAsync<T>(object whereConditions, IDbTransaction transaction = null, int? commandTimeout = null) 253 { 254 try 255 { 256 return SimpleCRUD.DeleteListAsync<T>(Connection, whereConditions, transaction, commandTimeout); 257 } 258 catch (Exception ex) 259 { 260 throw (ex); 261 } 262 } 263 264 #endregion 265 266 #region 修改操作 267 268 /// <summary> 269 /// 在数据库中更新记录 270 /// </summary> 271 /// <param name="entityToUpdate">需要更新的实体</param> 272 /// <returns>影响记录的数量</returns> 273 public int Update(Object entityToUpdate, IDbTransaction transaction = null, int? commandTimeout = null) 274 { 275 try 276 { 277 return SimpleCRUD.Update(Connection, entityToUpdate, transaction, commandTimeout); 278 } 279 catch (Exception ex) 280 { 281 throw (ex); 282 } 283 } 284 285 /// <summary> 286 /// 异步在数据库中更新记录 287 /// </summary> 288 /// <param name="entityToUpdate">需要更新的实体</param> 289 /// <returns>影响记录的数量</returns> 290 public Task<int> UpdateAsync(Object entityToUpdate, IDbTransaction transaction = null, int? commandTimeout = null) 291 { 292 try 293 { 294 return SimpleCRUD.UpdateAsync(Connection, entityToUpdate, transaction, commandTimeout); 295 } 296 catch (Exception ex) 297 { 298 throw (ex); 299 } 300 } 301 302 #endregion 303 304 #region 查询操作 305 306 /// <summary> 307 /// 获取表所有记录 308 /// </summary> 309 /// <typeparam name="T">返回类型</typeparam> 310 /// <returns>表记录</returns> 311 public IEnumerable<T> GetList<T>() 312 { 313 try 314 { 315 return SimpleCRUD.GetList<T>(Connection); 316 } 317 catch (Exception ex) 318 { 319 throw (ex); 320 } 321 } 322 323 /// <summary> 324 /// 异步获取表所有记录 325 /// </summary> 326 /// <typeparam name="T">返回类型</typeparam> 327 /// <returns>表记录</returns> 328 public Task<IEnumerable<T>> GetListAsync<T>() 329 { 330 try 331 { 332 return SimpleCRUD.GetListAsync<T>(Connection); 333 } 334 catch (Exception ex) 335 { 336 throw (ex); 337 } 338 } 339 340 /// <summary> 341 /// 获取数据库记录 342 /// </summary> 343 /// <typeparam name="T">类型</typeparam> 344 /// <param name="conditions">条件</param> 345 /// <returns>数据库记录</returns> 346 public IEnumerable<T> GetList<T>(string conditions) 347 { 348 try 349 { 350 return SimpleCRUD.GetList<T>(Connection, conditions); 351 } 352 catch (Exception ex) 353 { 354 throw (ex); 355 } 356 } 357 358 /// <summary> 359 /// 异步获取数据库记录 360 /// </summary> 361 /// <typeparam name="T">类型</typeparam> 362 /// <param name="conditions">条件</param> 363 /// <returns>数据库记录</returns> 364 public Task<IEnumerable<T>> GetListAsync<T>(string conditions) 365 { 366 try 367 { 368 return SimpleCRUD.GetListAsync<T>(Connection, conditions); 369 } 370 catch (Exception ex) 371 { 372 throw (ex); 373 } 374 } 375 376 /// <summary> 377 /// 查询表匹配的数据库记录 378 /// </summary> 379 /// <typeparam name="T">类型</typeparam> 380 /// <param name="whereConditions">有条件的对象</param> 381 /// <returns>数据库记录</returns> 382 public IEnumerable<T> GetList<T>(object whereConditions) 383 { 384 try 385 { 386 return SimpleCRUD.GetList<T>(Connection, whereConditions); 387 } 388 catch (Exception ex) 389 { 390 throw (ex); 391 } 392 } 393 394 /// <summary> 395 /// 异步查询表匹配的数据库记录 396 /// </summary> 397 /// <typeparam name="T">类型</typeparam> 398 /// <param name="whereConditions">有条件的对象</param> 399 /// <returns>数据库记录</returns> 400 public Task<IEnumerable<T>> GetListAsync<T>(object whereConditions) 401 { 402 try 403 { 404 return SimpleCRUD.GetListAsync<T>(Connection, whereConditions); 405 } 406 catch (Exception ex) 407 { 408 throw (ex); 409 } 410 } 411 412 /// <summary> 413 /// 通过ID获取数据库表记录 414 /// </summary> 415 /// <typeparam name="T">类型</typeparam> 416 /// <param name="id">KEY</param> 417 /// <returns>数据库表记录</returns> 418 public T Get<T>(object id) 419 { 420 try 421 { 422 return SimpleCRUD.Get<T>(Connection, id); 423 } 424 catch (Exception ex) 425 { 426 throw (ex); 427 } 428 } 429 430 /// <summary> 431 /// 通过ID异步获取数据库表记录 432 /// </summary> 433 /// <typeparam name="T">类型</typeparam> 434 /// <param name="id">KEY</param> 435 /// <returns>数据库表记录</returns> 436 public Task<T> GetAsync<T>(object id) 437 { 438 try 439 { 440 return SimpleCRUD.GetAsync<T>(Connection, id); 441 } 442 catch (Exception ex) 443 { 444 throw (ex); 445 } 446 } 447 448 /// <summary> 449 /// 获取分页记录信息 450 /// </summary> 451 /// <typeparam name="T">类型</typeparam> 452 /// <param name="pageNumber">页数</param> 453 /// <param name="rowsPerPage">记录数</param> 454 /// <param name="conditions">查询条件</param> 455 /// <param name="orderby">排序条件</param> 456 /// <returns>分页记录</returns> 457 public IEnumerable<T> GetListPaged<T>(int pageNumber, int rowsPerPage, string conditions, string orderby) 458 { 459 try 460 { 461 return SimpleCRUD.GetListPaged<T>(Connection, pageNumber, rowsPerPage, conditions, orderby); 462 } 463 catch (Exception ex) 464 { 465 throw (ex); 466 } 467 } 468 469 /// <summary> 470 /// 异步获取分页记录信息 471 /// </summary> 472 /// <typeparam name="T">类型</typeparam> 473 /// <param name="pageNumber">页数</param> 474 /// <param name="rowsPerPage">记录数</param> 475 /// <param name="conditions">查询条件</param> 476 /// <param name="orderby">排序条件</param> 477 /// <returns>分页记录</returns> 478 public Task<IEnumerable<T>> GetListPagedAsync<T>(int pageNumber, int rowsPerPage, string conditions, string orderby) 479 { 480 try 481 { 482 return SimpleCRUD.GetListPagedAsync<T>(Connection, pageNumber, rowsPerPage, conditions, orderby); 483 } 484 catch (Exception ex) 485 { 486 throw (ex); 487 } 488 } 489 490 #endregion 491 492 #region 获取Count 493 494 /// <summary> 495 /// 获取记录的数量 496 /// </summary> 497 /// <typeparam name="T">类型</typeparam> 498 /// <param name="conditions">条件</param> 499 /// <returns>记录的数量</returns> 500 public int RecordCount<T>(string conditions = "") 501 { 502 try 503 { 504 return SimpleCRUD.RecordCount<T>(Connection, conditions); 505 } 506 catch (Exception ex) 507 { 508 throw (ex); 509 } 510 } 511 512 /// <summary> 513 /// 异步获取记录的数量 514 /// </summary> 515 /// <typeparam name="T">类型</typeparam> 516 /// <param name="conditions">条件</param> 517 /// <returns>记录的数量</returns> 518 public Task<int> RecordCountAsync<T>(string conditions = "") 519 { 520 try 521 { 522 return SimpleCRUD.RecordCountAsync<T>(Connection, conditions); 523 } 524 catch (Exception ex) 525 { 526 throw (ex); 527 } 528 } 529 530 #endregion 531 532 #region 执行SQL 533 534 /// <summary> 535 /// 执行SQL语句并返回结果 536 /// </summary> 537 /// <param name="sql"></param> 538 /// <returns></returns> 539 public IEnumerable<T> Query<T>(string sql, object whereCondition = null, IDbTransaction transaction = null) 540 { 541 try 542 { 543 return Connection.Query<T>(sql, whereCondition, transaction); 544 } 545 catch (Exception ex) 546 { 547 throw (ex); 548 } 549 } 550 551 /// <summary> 552 /// 异步执行SQL语句并返回结果 553 /// </summary> 554 /// <param name="sql"></param> 555 /// <returns></returns> 556 public Task<IEnumerable<dynamic>> QueryAsync(string sql, object whereCondition = null, IDbTransaction transaction = null) 557 { 558 try 559 { 560 return Connection.QueryAsync(sql, whereCondition, transaction); 561 } 562 catch (Exception ex) 563 { 564 throw (ex); 565 } 566 } 567 568 /// <summary> 569 /// 执行sql 影响的行数 570 /// </summary> 571 /// <param name="sql"></param> 572 /// <param name="whereCondition"></param> 573 /// <param name="transaction"></param> 574 /// <returns></returns> 575 public int ExecSql(string sql, object whereCondition = null, IDbTransaction transaction = null) 576 { 577 try 578 { 579 return Connection.Execute(sql, whereCondition, transaction); 580 } 581 catch (Exception ex) 582 { 583 throw (ex); 584 } 585 } 586 587 #endregion 588 }
接下来表现层操作数据库表，查看当前表中无数据，
执行代码，
1 profit_rule_profitrulemainBLL bll = new profit_rule_profitrulemainBLL(); 2 3 Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff")); 4 List<profit_rule_profitrulemain> rules = bll.GetAll(); 5 6 Console.WriteLine("rules.count = {0}", rules.Count); 7 Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff")); 8 9 List<profit_rule_profitrulemain> ruleList = new List<profit_rule_profitrulemain>(); 10 11 for(int i = 0; i < 100; i++) 12 { 13 profit_rule_profitrulemain ruleMain = new profit_rule_profitrulemain 14 { 15 ruleid = Guid.NewGuid().ToString(), 16 stationid = Guid.NewGuid().ToString(), 17 statistictype = i, 18 statisticname = i.ToString(), 19 sectionid = i, 20 sectionname = i.ToString(), 21 expression = Guid.NewGuid().ToString() 22 }; 23 24 ruleList.Add(ruleMain); 25 26 bll.InsertObject(ruleMain); 27 } 28 Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff")); 29 30 bll.InsertObjectList(ruleList); 31 Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff"));
再次查看表中数据，表中数据200行，这是因为执行了单个对象插入和多行插入，
查询、删除，
1 string sql = string.Format("where id > 29"); 2 List<profit_rule_profitrulemain> ruleList = bll.GetObjectByCondition(sql); 3 4 bll.DeleteObjectList(ruleList);
获取单条信息，
1 List<profit_rule_profitrulemain> mainList = bll.GetObjectByCondition(new { id = 29 });
更新，
1 profit_rule_profitrulemain main = bll.GetObjectByConditionFrist(new { id = 29 }); 2 main.stationid = "2222"; 3 bll.UpdateObject(main);