//1 将实体对象动态转换为原生sql语句（增，删,改，查），查暂时只有单表查询，多实体的动态生成暂时还没想出来
using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Metadata.Internal; using Microsoft.Extensions.Hosting; using MISSION.Entitys; using MISSION.Entitys.Enums; using MySqlConnector; using System; using System.Collections.Generic; using System.ComponentModel; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Data; using System.Diagnostics.CodeAnalysis; using System.Linq; using System.Reflection; using System.Text; using System.Threading.Tasks; using System.Xml.Linq; using static Microsoft.EntityFrameworkCore.DbLoggerCategory; namespace MISSION.Util { public class SqlBuilderHelper { /// <summary> /// Insert SQL语句 /// </summary> /// <param name="obj">要转换的对象，不可空</param> /// <returns> /// 空 /// sql语句 /// </returns> public static string InsertSql<T>(T t, bool dynamic = false) where T : class { var tableName = GetTableName<T>(dynamic); if (t == null || string.IsNullOrEmpty(tableName)) { return string.Empty; } string columns = GetColmons(t); if (string.IsNullOrEmpty(columns)) { return string.Empty; } string values = GetValues(t); if (string.IsNullOrEmpty(values)) { return string.Empty; } StringBuilder sql = new StringBuilder(); sql.Append("insert into " + tableName); sql.Append("(" + columns + ")"); sql.Append(" values(" + values + ")"); return sql.ToString(); } /// <summary> /// BulkInsert SQL语句（批量添加） /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="objs">要转换的对象集合，不可空</param> /// <returns> /// 空 /// sql语句 /// </returns> public static string BulkInsertSql<T>(List<T> objs, bool dynamic = false) where T : class { var tableName = GetTableName<T>(dynamic); if (objs == null || objs.Count == 0 || string.IsNullOrEmpty(tableName)) { return string.Empty; } string columns = GetColmons(objs[0]); if (string.IsNullOrEmpty(columns)) { return string.Empty; } string values = string.Join(",", objs.Select(p => string.Format("({0})", GetValues(p) == null ? null : GetValues(p))).ToArray()); StringBuilder sql = new StringBuilder(); sql.Append("insert into " + tableName); sql.Append("(" + columns + ")"); sql.Append(" values " + values + ""); return sql.ToString(); } /// <summary> /// 获得类型的列名 /// </summary> /// <param name="obj"></param> /// <returns></returns> private static string GetColmons<T>(T obj) { if (obj == null) { return string.Empty; } return string.Join(",", obj.GetType().GetProperties().Where(p => !p.PropertyType.FullName.Contains("System.Collections") || (p.PropertyType.BaseType != null && p.PropertyType.BaseType.FullName.Contains("System.Array"))). Select(p => p.Name.ToLower()).ToList()); } /// <summary> /// 获得值 /// </summary> /// <param name="obj"></param> /// <returns></returns> private static string GetValues<T>(T obj) { if (obj == null) { return string.Empty; } PropertyInfo[] propertys = obj.GetType().GetProperties(); StringBuilder sb = new StringBuilder(); var a = typeof(string).FullName; var b = typeof(Guid).FullName; foreach (var property in propertys) { if (property.PropertyType.FullName.Contains("System.Collections") || (property.PropertyType.BaseType != null && property.PropertyType.BaseType.FullName.Contains("System.Array"))) { continue; } if (property.GetValue(obj) == null) { sb.Append("null"); } else if (property.PropertyType.FullName.Contains("System.DateTime")) { sb.AppendFormat(" '{0}'", Convert.ToDateTime(property.GetValue(obj)).ToString("yyyy-MM-dd HH:mm:ss")); } else if (property.PropertyType.FullName.Contains("System.String") || property.PropertyType.FullName.Contains("System.Guid")) { sb.AppendFormat(" '{0}'", property.GetValue(obj)); } else if (property.PropertyType.BaseType.FullName.Contains("System.Enum")) { sb.AppendFormat(" {0} ", (int)property.GetValue(obj)); } else { sb.AppendFormat(" {0} ", property.GetValue(obj)); } sb.Append(","); } var insert = sb.ToString(); return insert.Remove(insert.LastIndexOf(","), 1); } /// <summary> /// sql修改语句 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <returns></returns> public static string UpdateSql<T>(T entity, bool dynamic = false) where T : class { var tableName = GetTableName<T>(dynamic); if (entity == null || string.IsNullOrEmpty(tableName)) { return string.Empty; } string pkName = GetPK<T>(); if (string.IsNullOrEmpty(pkName)) { return string.Empty; } string pkValue = string.Empty; StringBuilder sb = new StringBuilder(); sb.Append("update "); sb.Append(tableName); sb.Append(" set "); Type type = entity.GetType(); PropertyInfo[] props = type.GetProperties(); List<string> paraList = new List<string>(); foreach (var prop in props) { if (prop.Name == (string)pkName) { pkValue = (string)prop.GetValue(entity).ToString(); } else { if (!string.IsNullOrEmpty(GetUpdatePara(prop, entity))) { paraList.Add(GetUpdatePara(prop, entity)); } } } if (paraList.Count == 0) { return string.Empty; } sb.Append(string.Join(",", paraList)); if (string.IsNullOrEmpty(pkValue)) { throw new Exception("主键不能为空"); } sb.Append(" where "); sb.Append(pkName.ToLower()); sb.Append(" = "); sb.AppendFormat("'{0}'", pkValue); return sb.ToString(); } /// <summary> /// sql修改语句 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <returns></returns> public static string UpdateSqlByWhere<T>(T entity, string where, bool dynamic = false) where T : class { var tableName = GetTableName<T>(dynamic); if (entity == null || string.IsNullOrEmpty(tableName)) { return string.Empty; } //string pkName = GetPK<T>(); //if (string.IsNullOrEmpty(pkName)) //{ // return string.Empty; //} //string pkValue = string.Empty; StringBuilder sb = new StringBuilder(); sb.Append("update "); sb.Append(tableName); sb.Append(" set "); Type type = entity.GetType(); PropertyInfo[] props = type.GetProperties(); List<string> paraList = new List<string>(); foreach (var prop in props) { //if (prop.Name == (string)pkName) //{ // pkValue = (string)prop.GetValue(entity).ToString(); //} //else //{ if (!string.IsNullOrEmpty(GetUpdatePara(prop, entity))) { paraList.Add(GetUpdatePara(prop, entity)); } // } } if (paraList.Count == 0) { return string.Empty; } sb.Append(string.Join(",", paraList)); //if (string.IsNullOrEmpty(pkValue)) //{ // throw new Exception("主键不能为空"); //} sb.Append(" where "); sb.Append(where); return sb.ToString(); } /// <summary> /// 基类更新，一个从表（从表的外建是主表+主键） /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <returns></returns> public static string UpdateSql<T, T1>(T entity, List<T1> t1s, bool dynamic = false) where T : class where T1 : class { var tableName = GetTableName<T>(dynamic); var key = GetPK<T>(); PropertyInfo pkProp = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Length > 0).FirstOrDefault(); var keyValue = pkProp.GetValue(entity).ToString(); var strSql = UpdateSql(entity); tableName = tableName.Remove(0, 1); ///从表的外建是主表名(去掉第一个字符)+表主键 var fOREIGNKEY = tableName + key; var where = fOREIGNKEY + "='" + keyValue + "'"; strSql = strSql + ";" + DeleteByWhere<T1>(where); strSql = strSql + BulkInsertSql(t1s); return strSql; } /// <summary> /// 基类更新，其他表添加相应的数据（与主表没有关联,比如做一个记录表） /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <returns></returns> public static string UpdateAndAddSql<T, T1>(T entity, List<T1> t1s, bool dynamic = false) where T : class where T1 : class { var tableName = GetTableName<T>(dynamic); var key = GetPK<T>(); PropertyInfo pkProp = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Length > 0).FirstOrDefault(); var keyValue = pkProp.GetValue(entity).ToString(); var strSql = UpdateSql(entity); tableName = tableName.Remove(0, 1); strSql = strSql + ";" + BulkInsertSql(t1s); return strSql; } /// <summary> /// 基类更新，两个从表（从表的外建是主表+主键） /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <returns></returns> public static string UpdateSql<T, T1, T2>(T entity, List<T1> t1, List<T2> t2, bool dynamic = false) where T : class where T1 : class where T2 : class { var tableName = GetTableName<T>(dynamic); var key = GetPK<T>(); PropertyInfo pkProp = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Length > 0).FirstOrDefault(); var keyValue = pkProp.GetValue(entity).ToString(); var strSql = UpdateSql(entity); tableName = tableName.Remove(0, 1); ///从表的外建是主表名(去掉第一个字符)+表主键 var fOREIGNKEY = tableName + key; var where = fOREIGNKEY + "='" + keyValue + "'"; strSql = strSql + ";" + DeleteByWhere<T1>(where); strSql = strSql + DeleteByWhere<T2>(where); strSql = strSql + BulkInsertSql(t1); strSql = strSql + ";" + BulkInsertSql(t2); return strSql; } /// <summary> /// 基类更新，三个从表（从表的外建是主表+主键） /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <returns></returns> public static string UpdateSql<T, T1, T2, T3>(T entity, List<T1> t1, List<T2> t2, List<T3> t3, bool dynamic = false) where T : class where T1 : class where T2 : class where T3 : class { var tableName = GetTableName<T>(dynamic); var key = GetPK<T>(); PropertyInfo pkProp = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Length > 0).FirstOrDefault(); var keyValue = pkProp.GetValue(entity).ToString(); var strSql = UpdateSql(entity); tableName = tableName.Remove(0, 1); ///从表的外建是主表名(去掉第一个字符)+表主键 var fOREIGNKEY = tableName + key; var where = fOREIGNKEY + "='" + keyValue + "'"; strSql = strSql + ";" + DeleteByWhere<T1>(where); strSql = strSql + DeleteByWhere<T2>(where); strSql = strSql + DeleteByWhere<T3>(where); strSql = strSql + BulkInsertSql(t1); strSql = strSql + ";" + BulkInsertSql(t2); strSql = strSql + ";" + BulkInsertSql(t3); return strSql; } /// <summary> /// 获得修改参数 /// </summary> /// <param name="property"></param> /// <returns></returns> private static string GetUpdatePara<T>(PropertyInfo property, T entity) { StringBuilder sb = new StringBuilder(); if (property.Name.ToLower() != "createtime") { if (property.PropertyType.FullName.Contains("System.Collections") || (property.PropertyType.BaseType != null && property.PropertyType.BaseType.FullName.Contains("System.Array"))) { return ""; } if (property.GetValue(entity) == null) { sb.AppendFormat(" {0}= null ", property.Name.ToLower()); } else if (property.PropertyType.FullName.Contains("System.DateTime")) { sb.AppendFormat(" {0}='{1}' ", property.Name.ToLower(), Convert.ToDateTime(property.GetValue(entity)).ToString("yyyy-MM-dd HH:mm:ss")); } else if (property.PropertyType.FullName.Contains("System.String") || property.PropertyType.FullName.Contains("System.Guid")) { sb.AppendFormat(" {0}='{1}' ", property.Name.ToLower(), property.GetValue(entity)); } else if (property.PropertyType.BaseType.FullName.Contains("System.Enum")) { sb.AppendFormat(" {0}={1} ", property.Name.ToLower(), (int)property.GetValue(entity)); } else { sb.AppendFormat(" {0}={1} ", property.Name.ToLower(), property.GetValue(entity)); } } return sb.ToString(); } /// <summary> /// 获得主键名称 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> private static string GetPK<T>() where T : class { //获取主键的 PropertyInfo PropertyInfo pkProp = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Length > 0).FirstOrDefault(); //主键名称 var keyName = pkProp.Name; return keyName; } /// <summary> /// 获取表名 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> private static string GetTableName<T>(bool dynamic = false) where T : class { var tableName = (typeof(T).GetCustomAttributes(typeof(TableAttribute), false)[0] as TableAttribute).Name; //pemployeeschedulingdata员工排班数据每个月生成一个新表 if (tableName.Contains("pemployeeschedulingdata")) { // if (tableName.Contains("auto") || dynamic) { if (DateTime.Now.Month == 12) { tableName = "pemployeeschedulingdata_" + (DateTime.Now.Year + 1).ToString() + "_1"; } else { tableName = "pemployeeschedulingdata_" + DateTime.Now.Year.ToString() + "_" + (DateTime.Now.Month + 1).ToString(); } } else { tableName = "pemployeeschedulingdata_" + DateTime.Now.Year.ToString() + "_" + (DateTime.Now.Month).ToString(); } } return tableName.ToLower(); } /// <summary> /// 通过主键删除 /// </summary> /// <param name="ids"></param> /// <returns></returns> public static string DeleteByIds<T>(List<string> ids, bool dynamic = false) where T : class { var tableName = GetTableName<T>(dynamic); var id = ArrayToString(ids); string pkName = GetPK<T>(); string sqlText = string.Format(@"delete from {0} where {1} in ( {2} ); ", tableName, pkName.ToLower(), id); return sqlText; } /// <summary> /// 根据sql条件删除 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="where"></param> /// <returns></returns> public static string DeleteByWhere<T>(string where, bool dynamic = false) where T : class { var tableName = GetTableName<T>(dynamic); string sqlText = string.Format(@"delete from {0} where {1}; ", tableName, where); return sqlText; } /// <summary> /// 根据sql条件删除(T为主表，T1为从表) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="where"></param> /// <returns></returns> public static string DeleteByWhere<T, T1>(List<string> deleteByIds, bool dynamic = false) where T : class where T1 : class { var strsql = DeleteByIds<T>(deleteByIds); var ids = string.Join(',', deleteByIds.ToArray()); var tableName = GetTableName<T>(dynamic); var key = GetPK<T>(); tableName = tableName.Remove(0, 1); var fOREIGNKEY = tableName + key; var where = fOREIGNKEY + " in (" + ids + ")"; strsql = strsql + DeleteByWhere<T1>(where); return strsql; } /// <summary> /// 根据sql条件删除(T为主表，T1为从表) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="where"></param> /// <returns></returns> public static string DeleteByWhere<T, T1, T2>(List<string> deleteByIds, bool dynamic = false) where T : class where T1 : class where T2 : class { var strsql = DeleteByIds<T>(deleteByIds); var ids = string.Join(',', deleteByIds.ToArray()); var tableName = GetTableName<T>(dynamic); var key = GetPK<T>(); tableName = tableName.Remove(0, 1); var fOREIGNKEY = tableName + key.ToLower(); var where = fOREIGNKEY + " in (" + ids + ")"; strsql = strsql + DeleteByWhere<T1>(where); strsql = strsql + DeleteByWhere<T2>(where); return strsql; } /// <summary> /// 单表查询 /// </summary> /// <param name="type"></param> /// <param name="where"></param> /// <param name="dbType"></param> /// <returns></returns> public static string SelectToSql<T>(string where, bool dynamic = false) where T : class { if (!string.IsNullOrEmpty(where)) { if (!where.TrimStart().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase)) { where = "Where " + where; } } StringBuilder sql = new StringBuilder("SELECT ");//不考虑多表操作 //添加的sql语句 Type type = typeof(T); PropertyInfo[] infos = type.GetProperties(); foreach (PropertyInfo pro in infos) { if (pro.PropertyType.FullName.Contains("System.Collections") || (pro.PropertyType.BaseType != null && pro.PropertyType.BaseType.FullName.Contains("System.Array"))) { continue; } else { sql.Append(pro.Name + ','); } //if (ExcludeFieldAttribute.GetAttribute(pro) != null) continue;//自定义扩展属性不处理 } sql = sql.Remove(sql.Length - 1, 1); string tableName = GetTableName<T>(dynamic); //sql.AppendLine(); sql.AppendFormat(" FROM {0} ", tableName); sql.Append(where); string result = Recombine(sql.ToString().ToLower()); return result; } /// <summary> /// 单表查询(查询条件也是此表中的) /// </summary> /// <param name="type"></param> /// <param name="where"></param> /// <param name="dbType"></param> /// <returns></returns> public static string SelectToSql<T>(object param, bool dynamic = false) where T : class { StringBuilder sql = new StringBuilder("SELECT ");//不考虑多表操作 //添加的sql语句 Type type = typeof(T); PropertyInfo[] infos = type.GetProperties(); foreach (PropertyInfo pro in infos) { //if (ExcludeFieldAttribute.GetAttribute(pro) != null) continue;//自定义扩展属性不处理 sql.Append(pro.Name + ','); } sql = sql.Remove(sql.Length - 1, 1); string tableName = GetTableName<T>(dynamic); sql.AppendFormat(" FROM {0} where 1=1 ", tableName); foreach (PropertyInfo p in param.GetType().GetProperties()) { if (p.GetValue(param) != null && !string.IsNullOrEmpty(p.GetValue(param).ToString())) { var exit = infos.Where(c => c.Name.Contains(p.Name)).FirstOrDefault(); if (exit != null) { if (p.PropertyType.Name == typeof(Guid).Name) { sql.Append($"and {p.Name} = '%{p.GetValue(param)}%' "); } // string默认使用模糊查询 else if (p.PropertyType.Name == typeof(string).Name) { sql.Append($"and {p.Name} like '%{p.GetValue(param)}%' "); } else { sql.Append($"and {p.Name}= {p.GetValue(param)} "); } } } } string result = Recombine(sql.ToString().ToLower()); return result; } private static string _paramPrefix = @"@"; public static DbTypeEnums _dbType { get; set; } /// <summary> /// 替换特殊函数 /// </summary> /// <param name="sql"></param> /// <returns></returns> private static string Recombine(string sql) { _dbType = DbTypeEnums.mysql; switch (_dbType) { case DbTypeEnums.SqlServer: break; case DbTypeEnums.Oracle: sql = sql.Replace("@", _paramPrefix); sql = sql.Replace("isnull(", "NVL(") .Replace("ISNULL(", "NVL(") .Replace("getDate()", "SYSDATE") .Replace("getdate()", "SYSDATE") .Replace("GETDATE()", "SYSDATE"); break; case DbTypeEnums.mysql: sql = sql.Replace("isnull(", "ifnull(") .Replace("ISNULL(", "ifnull(") .Replace("getDate()", "now()") .Replace("getdate()", "now()") .Replace("GETDATE()", "now()"); break; } return sql; } /// <summary> /// 根据实体生成建表的sql，不支持创建子表 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public static string CreateTableScript<T>(bool dynamic = false) where T : class { StringBuilder script = new StringBuilder(); var tableName = GetTableName<T>(dynamic); script.Append($"CREATE TABLE IF NOT EXISTS {tableName} ("); var key = GetPK<T>(); ///表描述 var tableDescription = (typeof(T).GetCustomAttributes(typeof(DescriptionAttribute), false)).FirstOrDefault() as DescriptionAttribute; //var tableDescription = (typeof(T).GetCustomAttributes(typeof(DescriptionAttribute), false)[0] as DescriptionAttribute); //主键 var keys = new List<string>(); var pkProp = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Length > 0).ToList(); if (pkProp != null) { if (pkProp.Count == 1) { foreach (var item in pkProp) { var descriptionAttribute = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute)));// 属性值 var keyTypeName = item.PropertyType.FullName; if (keyTypeName == "System.Guid") { script.Append($"{item.Name.ToLower()} char(36) not null primary key,\r\n"); } else if (keyTypeName == "System.String") { var maxLength = 64; var maxLengthAttribute = item.GetCustomAttributes(typeof(MaxLengthAttribute), false).FirstOrDefault(); if (maxLengthAttribute != null) { maxLength = ((MaxLengthAttribute)maxLengthAttribute).Length; } script.Append($"{item.Name.ToLower()} nvarchar({maxLength}) not null primary key,\r\n"); } else if (keyTypeName == "System.DateTime") { script.Append($"{item.Name.ToLower()} datetime not null primary key,\r\n"); } else { script.Append($"{item.Name.ToLower()} int primary key auto_increment ,\r\n"); } if (descriptionAttribute != null) { var descript = descriptionAttribute.Description; if (!string.IsNullOrEmpty(descript)) { var lastIndex = script.ToString().LastIndexOf(","); var length = script.Length - lastIndex; script = script.Remove(lastIndex, length); script = script.Append(" comment '" + descript + "',\r\n"); } } keys.Add(item.Name); } } else { foreach (var item in pkProp) { //字段描述 var descriptionAttribute = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute)));// 属性值 var keyTypeName = item.PropertyType.FullName; if (keyTypeName == "System.Guid") { script.Append($"{item.Name.ToLower()} char(36) not null ,\r\n"); } else if (keyTypeName == "System.String") { var maxLength = 64; var maxLengthAttribute = item.GetCustomAttributes(typeof(MaxLengthAttribute), false).FirstOrDefault(); if (maxLengthAttribute != null) { maxLength = ((MaxLengthAttribute)maxLengthAttribute).Length; } script.Append($"{item.Name.ToLower()} nvarchar({maxLength}) not null,\r\n"); } else if (keyTypeName == "System.DateTime") { script.Append($"{item.Name.ToLower()} datetime not null,\r\n"); } else { script.Append($"{item.Name.ToLower()} int not null auto_increment ,\r\n"); } keys.Add(item.Name); if (descriptionAttribute != null) { var descript = descriptionAttribute.Description; if (!string.IsNullOrEmpty(descript)) { var lastIndex = script.ToString().LastIndexOf(","); var length = script.Length - lastIndex; script = script.Remove(lastIndex, length); script = script.Append(" comment '" + descript + "',\r\n"); } } } var keytostring = string.Join(',', keys.ToArray()).ToLower(); script.Append($"constraint pk_{tableName} primary key( {keytostring} ),\r\n"); } } Type t = typeof(T); PropertyInfo[] infos = t.GetProperties(); foreach (PropertyInfo pro in infos) { var descriptionAttribute = ((DescriptionAttribute)Attribute.GetCustomAttribute(pro, typeof(DescriptionAttribute)));// 属性值 if (keys.Contains(pro.Name) || pro.PropertyType.FullName.Contains("System.Collections") || (pro.PropertyType.BaseType != null && pro.PropertyType.BaseType.FullName.Contains("System.Array"))) { continue; } else { if (pro.PropertyType.FullName.Contains("Guid")) { if (pro.PropertyType.Name.Contains("Nullable")) { script.Append($"{pro.Name.ToLower()} char(36) null default '',\r\n"); } else { script.Append($"{pro.Name.ToLower()} char(36) not null default '',\r\n"); } } else if (pro.PropertyType.Name.Contains("String")) { //允许为为空 var allowStringNull = pro.GetCustomAttributes(typeof(AllowStringNullAttribute), false); var maxLength = 64; var maxLengthAttribute = pro.GetCustomAttributes(typeof(MaxLengthAttribute), false).FirstOrDefault(); if (maxLengthAttribute != null) { maxLength = ((MaxLengthAttribute)maxLengthAttribute).Length; } if (allowStringNull.Count() != 0) { script.Append($"{pro.Name.ToLower()} nvarchar({maxLength}) null,\r\n"); } else { script.Append($"{pro.Name.ToLower()} nvarchar({maxLength}) not null default '',\r\n"); } } else if (pro.PropertyType.FullName.Contains("System.Int16")) { if (pro.PropertyType.Name.Contains("Nullable")) { script.Append($"{pro.Name.ToLower()} smallint null,\r\n "); } else { script.Append($"{pro.Name.ToLower()} smallint not null default 0,\r\n "); } } else if (pro.PropertyType.FullName.Contains("Int32") || pro.PropertyType.BaseType.FullName.Contains("System.Enum")) { if (pro.PropertyType.Name.Contains("Nullable")) { script.Append($"{pro.Name.ToLower()} int null,\r\n "); } else { script.Append($"{pro.Name.ToLower()} int not null default 0,\r\n "); } } else if (pro.PropertyType.FullName.Contains("System.Int64")) { if (pro.PropertyType.Name.Contains("Nullable")) { script.Append($"{pro.Name.ToLower()} bigint null,\r\n "); } else { script.Append($"{pro.Name.ToLower()} bigint not null default 0,\r\n "); } } else if (pro.PropertyType.FullName.Contains("System.Double")) { if (pro.PropertyType.Name.Contains("Nullable")) { script.Append($"{pro.Name.ToLower()} double null,\r\n "); } else { script.Append($"{pro.Name.ToLower()} double not null default 0,\r\n"); } } else if (pro.PropertyType.FullName.Contains("System.Decimal")) { if (pro.PropertyType.Name.Contains("Nullable")) { script.Append($"{pro.Name.ToLower()} decimal null,\r\n "); } else { script.Append($"{pro.Name.ToLower()} decimal(8,2) not null default 0 , \r\n"); } } else if (pro.PropertyType.FullName.Contains("System.Single")) { if (pro.PropertyType.Name.Contains("Nullable")) { script.Append($"{pro.Name.ToLower()} float null,\r\n "); } else { script.Append($"{pro.Name.ToLower()} float not null default 0,\r\n"); } } else if (pro.PropertyType.FullName.Contains("System.Boolean")) { if (pro.PropertyType.Name.Contains("Nullable")) { script.Append($"{pro.Name.ToLower()} bool null,\r\n "); } else { script.Append($"{pro.Name.ToLower()} bool not null default '0',\r\n"); } } else if (pro.PropertyType.FullName.Contains("System.DateTime")) { if (pro.PropertyType.Name.Contains("Nullable")) { script.Append($"{pro.Name.ToLower()} datetime null,\r\n "); } else { script.Append($"{pro.Name.ToLower()} datetime not null default '{System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',\r\n"); } } else { script.Append($"{pro.Name.ToLower()} int not null default 0,\r\n"); } if (descriptionAttribute != null) { var descript = descriptionAttribute.Description; if (!string.IsNullOrEmpty(descript)) { var lastIndex = script.ToString().LastIndexOf(","); var length = script.Length - lastIndex; script = script.Remove(lastIndex, length); script = script.Append(" comment '" + descript + "',\r\n"); } } } } script = script.Remove(script.ToString().LastIndexOf(","), 1); script.AppendLine(")"); if (tableDescription != null) { script.AppendLine("comment ='" + tableDescription.Description + "'"); } return script.ToString(); } public static string ArrayToString(List<string> deleteByIds) { for (int i = 0; i < deleteByIds.Count; i++) { deleteByIds[i] = deleteByIds[i].Replace(deleteByIds[i], "'" + deleteByIds[i] + "'"); } var ids = String.Join(",", deleteByIds.ToArray()); return ids; } } }
标签：封装,Name,c#,pro,sql,var,null,Append,string From： https://www.cnblogs.com/carlpeng/p/17426976.html