C#-数据库帮助类 -清零世界

bubuko 2022/1/25 19:42:11 dotnet 字数 391435 阅读 962 1 来源 http://www.bubuko.com/infolist-5-1.html

　　一. 数据库简要介绍

　　数据库就不做详细介绍了，简单的说，就是为了存储数据，进行查看的，总之用的随处可见。

　　二. 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     }

IObjectDAL.cs

  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 #>

DALTemplate.tt

技术分享图片

　　接下来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     }

IObjeceBLL.cs

  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 #>

BLLTextTemplate.tt

技术分享图片

　　三层结构至此生成完毕。接下里就是操作数据库了。

启动项目配置文件，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     }

ConnectStringManager.cs

　　数据库链接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     }

DBConnectManager.cs

　　数据操作帮助类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     }

DbHelper.cs

　　接下来表现层操作数据库表，查看当前表中无数据，

技术分享图片

　　执行代码，

 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);

C#-数据库帮助类

原文：https://www.cnblogs.com/7haihai/p/12954656.html

如果您也喜欢它,动动您的小指点个赞吧

C#-数据库帮助类

分类汇总

您的鼓励是对我最大的支持