Sqlite CodeFirst的初级实现
示例实体:
using System; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Diagnostics.CodeAnalysis; using Honeysuckle.Domain.Entities; namespace Chagoi.Pos.Core.Entity.Goods { [Table("a2")] [SuppressMessage("ReSharper", "InconsistentNaming")] public class GoodsInfo : Entity<int> { [Column("c1")] public string Name { get; set; } /// <summary> /// 商品条码 /// </summary> [Column("c2")] [StringLength(50)] [Index] public string goods_barcode { get; set; } /// <summary> /// 简称 /// </summary> [Column("c3")] [StringLength(50)] [Index("fdafd")] public string goods_short_name { get; set; } [Column("c4")] [StringLength(150)] [Required] public string hello { get; set; } [Column("c5")] public DateTime UpdateTime { get; set; } } }
生成代码:
using Castle.Core.Logging; using Chagoi.Pos.Core.Entity.Device; using Chagoi.Pos.Core.Entity.Goods; using Chagoi.Pos.Core.Entity.Order; using Chagoi.Pos.Core.Entity.Pay; using Chagoi.Pos.Core.Entity.Users; using Honeysuckle.Dependency; using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Configuration; using System.Data; using System.Data.SQLite; using System.Diagnostics; using System.Linq; using System.Text; namespace Chagoi.Pos.EntityFramework.Seed { public static class SeedHelper { private static readonly string DbPath; private static ILogger _logger; static SeedHelper() { DbPath = ConfigurationManager.ConnectionStrings["Default"].ConnectionString; } public static void SeedHostDb(IIocResolver iocResolver, ILogger logger) { _logger = logger; SeedHostDb(); } private static void SeedHostDb() { var tobeCreateTables = new List<Type>() { typeof(IotDevice), typeof(GoodsInfo), typeof(GoodsCategory), typeof(GoodsBrand), typeof(AlipayParameter), typeof(WxpayParameter), typeof(AppUser), typeof(Order) }; _logger.Info("#region 开始执行数据库的创建或结构调整"); var stopwatch = new Stopwatch(); stopwatch.Start(); using (var connection = new SQLiteConnection(DbPath)) { connection.Open(); using (var transaction = connection.BeginTransaction()) { try { foreach (var entity in tobeCreateTables) { //表名称,这里使用混淆表明,要求必须输入 var tableAttribute = entity.GetCustomAttributes(typeof(TableAttribute), true).FirstOrDefault(); if (null == tableAttribute) { throw new ArgumentNullException(nameof(TableAttribute)); } var tableName = ((TableAttribute)tableAttribute).Name; //填充数据库中标的原始结构 var tableStructureCommandText = $"PRAGMA table_info(\"{tableName}\");"; var sqLiteCommand = new SQLiteCommand(connection) { CommandText = tableStructureCommandText }; var sQLiteDataAdapter = new SQLiteDataAdapter(sqLiteCommand); var tableStructure = new DataTable(tableName); sQLiteDataAdapter.Fill(tableStructure); var hasTable = tableStructure.Rows.Count > 0; if (!hasTable) { _logger.Info($"表{tableName}不存在,开始执行创建过程..."); //执行创建表的SQL语句 var createTableSql = GenerateCreateTableCommandText(entity, tableName); sqLiteCommand.CommandText = createTableSql; _logger.Debug(sqLiteCommand.CommandText); sqLiteCommand.ExecuteNonQuery(); _logger.Info($"表{tableName}的创建语句已执行."); } else { _logger.Info($"表{tableName}已经存在,开始执行差异对比过程..."); var updateTableSql = GenerateUpdateTableCommandText(entity, tableStructure); if (string.IsNullOrEmpty(updateTableSql)) { _logger.Info($"表{tableName}结构无差异,无需调整."); continue; } sqLiteCommand.CommandText = updateTableSql; _logger.Debug(sqLiteCommand.CommandText); var nonQuery = sqLiteCommand.ExecuteNonQuery(); _logger.Info($"表{tableName}的调整语句已执行,返回结果:{nonQuery}"); } } transaction.Commit(); } catch (Exception ex) { _logger.Error("执行数据库的创建或结构调整过程发生异常",ex); transaction.Rollback(); } } connection.Dispose(); } stopwatch.Stop(); var elapsedMilliseconds = stopwatch.ElapsedMilliseconds; _logger.Info($"#endregion 执行数据库的创建或结构调整过程完毕,耗时{elapsedMilliseconds}毫秒"); } /// <summary> /// 生成创建表的sql命令,包含索引的创建 /// </summary> /// <param name="entityType">实体类型</param> /// <param name="tableName">表的名称</param> /// <returns></returns> private static string GenerateCreateTableCommandText(Type entityType, string tableName) { var properties = entityType.GetProperties(); //索引创建语句,根据实体的Index特性来生成 var createTableIndexCommandTexts = new List<string>(); var createTableCommandText = $"CREATE TABLE \"{tableName}\" ("; foreach (var property in properties) { //列名,优先取特性名称 var columnName = GetColumnName(property); //是否可空 var notNull = IsNotNull(property); //字段类型 var dbType = GetDbType(property); createTableCommandText += $"\"{columnName}\" {dbType} {notNull},"; //处理索引 var indexAttributeObj = property.GetCustomAttributes(typeof(IndexAttribute), true).FirstOrDefault(); if (null != indexAttributeObj) { var indexAttribute = ((IndexAttribute)indexAttributeObj); //是否是唯一索引 var unique = indexAttribute.IsUnique ? "UNIQUE" : ""; //索引名称 var indexName = string.IsNullOrEmpty(indexAttribute.Name) ? $"Index_{columnName}" : indexAttribute.Name; //创建索引的语句 var indexSql = $"CREATE {unique} INDEX \"{indexName}\" ON \"{tableName}\"(\"{columnName}\" ASC); "; createTableIndexCommandTexts.Add(indexSql); } } createTableCommandText += "PRIMARY KEY (\"Id\" ASC)"; createTableCommandText += ");"; var allCommandText = new StringBuilder(createTableCommandText); foreach (var createTableIndexCommandText in createTableIndexCommandTexts) { allCommandText.Append(createTableIndexCommandText); } return allCommandText.ToString(); } /// <summary> /// 生成更新表的sql命令,只支持新增列 /// 不支持添加索引列,不支持删除列 /// 新增的列必须允许为空 /// </summary> /// <param name="entityType">实体类型</param> /// <param name="dataTable">数据中的表结构</param> /// <returns></returns> private static string GenerateUpdateTableCommandText(Type entityType, DataTable dataTable) { var properties = entityType.GetProperties(); var alreadyCreatedColumnNames = dataTable.AsEnumerable().Select(t => t.Field<string>("name").ToString()).ToArray(); var addColumnCommandTexts = new List<string>(); foreach (var property in properties) { var colName = GetColumnName(property); var alreadyCreated = alreadyCreatedColumnNames.Any(m => m == colName); if (!alreadyCreated) { var dbType = GetDbType(property); var addColumnCommandText = $"ALTER TABLE \"{dataTable.TableName}\" ADD COLUMN \"{colName}\" {dbType} NULL;"; addColumnCommandTexts.Add(addColumnCommandText); } } var allCommandText = new StringBuilder(); foreach (var addColumnCommand in addColumnCommandTexts) { allCommandText.Append(addColumnCommand); } return allCommandText.ToString(); } #region 基础函数 /// <summary> /// 获取数据类型 /// </summary> /// <param name="property"></param> /// <returns></returns> private static string GetDbType(System.Reflection.PropertyInfo property) { string dbType; if (property.PropertyType.FullName != null) { var propertyTypeFullName = property.PropertyType.FullName.ToUpper(); switch (propertyTypeFullName) { case "SYSTEM.BOOLEAN": dbType = "BOOLEAN"; break; case "SYSTEM.INT32": case "SYSTEM.INT64": dbType = "INTEGER"; break; case "SYSTEM.STRING": //是否标记了最大长度 var stringLengthAttributeObj = property.GetCustomAttributes(typeof(StringLengthAttribute), true).FirstOrDefault(); if (stringLengthAttributeObj != null) { var length = ((StringLengthAttribute)stringLengthAttributeObj).MaximumLength; dbType = $"VARCHAR({length})"; } else { dbType = "TEXT"; } break; case "SYSTEM.DATETIME": dbType = "DATETIME"; break; default: throw new ArgumentOutOfRangeException(nameof(propertyTypeFullName)); } } else { throw new ArgumentNullException(nameof(property.PropertyType.FullName)); } return dbType; } /// <summary> /// 字段是否可用 /// </summary> /// <param name="property"></param> /// <returns></returns> private static string IsNotNull(System.Reflection.PropertyInfo property) { var hasRequiredAttribute = property.GetCustomAttributes(typeof(RequiredAttribute), true).Length > 0; var notNull = hasRequiredAttribute ? "NOT NULL" : ""; if (property.Name.ToUpper() == "ID") //ID始终不能为空 { notNull = "NOT NULL"; } return notNull; } /// <summary> /// 获取列名 /// </summary> /// <param name="property"></param> /// <returns></returns> private static string GetColumnName(System.Reflection.PropertyInfo property) { var columnName = property.Name; var columnAttributeObj = property.GetCustomAttributes(typeof(ColumnAttribute), true).FirstOrDefault(); if (null != columnAttributeObj) { columnName = ((ColumnAttribute)columnAttributeObj).Name; } return columnName; } #endregion } }
请尽量按照自己期望的生活
email:18980489167@189.cn