//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;
}
}
}
