/// <summary> /// 生成 数据库创建表的sql语句 /// </summary> /// <param name="path">excel路径</param> /// <param name="entityName">实体名称</param> /// <returns></returns> public string Create(string path, string entityName) { if (string.IsNullOrWhiteSpace(path)) { return(null); } Excel.ExcelReadHandler excel = new Excel.ExcelReadHandler(path); List <string> keys = new List <string>(); List <TableInfo> tableList = excel.Read <TableInfo>(typeof(TableInfo).GetProperties().Select(s => s.Name).ToArray()).ToList(); StringBuilder sb = new StringBuilder(); sb.AppendLine($"CREATE TABLE {entityName} ("); foreach (TableInfo table in tableList) { if (table.IsKey == "1") { keys.Add(table.ColumnName); } sb.AppendLine($" {table.ColumnName} {table.ColumnType}{GetLength(table.ColumnLength)} {GetCharacter(table)} {GetNullable(table)} COMMENT '{table.ColumnDescription}',"); } if (keys.Count > 0) { sb.AppendLine($"PRIMARY KEY ({string.Join(",", keys)}),"); } sb = sb.Remove(sb.Length - 3, 1); sb.AppendLine(")"); sb.AppendLine(" ENGINE=InnoDB DEFAULT CHARSET=utf8;"); return(sb.ToString()); }
/// <summary> /// 生成 数据库创建表的sql语句 /// </summary> /// <param name="path">excel路径</param> /// <param name="entityName">实体名称</param> /// <returns></returns> public string Create(string path, string entityName) { if (string.IsNullOrWhiteSpace(path)) { return(null); } Excel.ExcelReadHandler excel = new Excel.ExcelReadHandler(path); List <string> keys = new List <string>(); List <TableInfo> tableList = excel.Read <TableInfo>(typeof(TableInfo).GetProperties().Select(s => s.Name).ToArray()).ToList(); StringBuilder sb = new StringBuilder(); sb.AppendLine($"CREATE TABLE [dbo].[{entityName}] ("); foreach (TableInfo table in tableList) { if (table.IsKey == "1") { keys.Add("[" + table.ColumnName + "]"); } sb.AppendLine($" [{table.ColumnName}] {table.ColumnType}{GetLength(table.ColumnLength)} {GetSortRule(table.ColumnType)} {GetDefault(table)} {GetNullable(table)},"); } sb = sb.Remove(sb.Length - 3, 1); sb.AppendLine(")"); sb.AppendLine("GO"); sb.AppendLine(); sb.AppendLine($"ALTER TABLE [dbo].[{entityName}] SET (LOCK_ESCALATION = TABLE)"); sb.AppendLine("GO"); foreach (TableInfo table in tableList) { sb.AppendLine($@" EXEC sp_addextendedproperty 'MS_Description', N'{table.ColumnDescription}', 'SCHEMA', N'dbo', 'TABLE', N'{entityName}', 'COLUMN', N'{table.ColumnName}' GO"); sb.AppendLine(); } if (keys.Count > 0) { sb.AppendLine($@" ALTER TABLE [dbo].[{entityName}] ADD CONSTRAINT [PK_{entityName}] PRIMARY KEY CLUSTERED ({string.Join(",", keys)}) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO"); } return(sb.ToString()); }