/// <summary> /// 生成数据库注释部分 /// </summary> private static string CreateDbComment() { string result = "/****** 创建数据库所有表的注释说明备注 Script Date:" + DateTime.Now + " ******/\r\n"; string tablename, tableComment; string column_name, column_comment; tablenames.Clear(); foreach (Dictionary <string, string> tableInfo in tableInfos.Values) { //获取表名 tablename = tableInfo["Name"]; if (IsSqlserverDefault) { tablename = UtilString.UcFirst(tablename); } tablenames.Add(tablename); tableComment = tableInfo["Comment"]; result += string.Format("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{0}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{1}'\r\nGO\r\n\r\n", tableComment, tablename); Dictionary <string, Dictionary <string, string> > columnInfos; columnInfos = UtilMysql.FieldInfoList(tablename); //获取主键名称 foreach (Dictionary <string, string> columnInfo in columnInfos.Values) { column_name = columnInfo["Field"]; if (IsSqlserverDefault) { column_name = UtilString.UcFirst(column_name); } column_comment = columnInfo["Comment"]; result += string.Format("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{0}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{1}', @level2type=N'COLUMN',@level2name=N'{2}'\r\nGO\r\n\r\n", column_comment, tablename, column_name); } } foreach (string tablename_n in tablenames) { Console.Write("\"" + tablename_n + "\"" + ","); } return(result); }
/// <summary> /// 查看指定表列名,如果没有指定表,默认指定第一张表 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnColumns_Click(object sender, EventArgs e) { btnColumns.Enabled = false; this.listResult.Clear(); string tablename; if (cbTables.Items.Count <= 0) { Dictionary <string, string> tables; tables = (cbDbType.SelectedIndex == 0) ? UtilSqlserver.TableList() : UtilMysql.TableList(); tablename = tables.Values.First(); foreach (string cur_tablename in tables.Values) { this.cbTables.Items.Add(cur_tablename); } this.cbTables.SelectedIndex = 0; } else { tablename = (string)cbTables.SelectedItem; } Dictionary <string, Dictionary <string, string> > columnInfos; columnInfos = (cbDbType.SelectedIndex == 0) ? UtilSqlserver.FieldInfoList(tablename) : UtilMysql.FieldInfoList(tablename); this.listResult.AppendText("显示指定表的信息:" + tablename + "\r\n"); string columnResult, comment; foreach (Dictionary <string, string> columnInfo in columnInfos.Values) { comment = ""; if (columnInfo.ContainsKey("Comment")) { comment = columnInfo["Comment"]; string[] commentArr = comment.Split(new char[2] { '\r', '\n' }); comment = commentArr[0]; comment = "|备注:" + comment; } columnResult = "列名:" + columnInfo["Field"] + comment + "|类型:" + columnInfo["Type"] + "|是否允许为空:" + columnInfo["Null"] + "\r\n"; this.listResult.AppendText(columnResult); } btnColumns.Enabled = true; }
/// <summary> /// 点击生成数据库说明Excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnExportexcel_Click(object sender, EventArgs e) { btnExportexcel.Enabled = false; UtilExcelCom.Release(); ExcelBE be = null; Dictionary <string, Dictionary <string, string> > tableInfos; tableInfos = (cbDbType.SelectedIndex == 0) ? UtilSqlserver.TableinfoList() : UtilMysql.TableinfoList(); int rowHeight = 25; //数据库表说明 UtilExcelCom.Current().SetSheet("Summary"); be = new ExcelBE(1, 1, "编号", "A1", "A1", "GRAYDARK", false, 10, 13.50, rowHeight, 2, null, "Century Gothic", 10, true, null); UtilExcelCom.Current().InsertData(be); be = new ExcelBE(1, 2, "表名称", "B1", "B1", "GRAYDARK", false, 10, 32.63, rowHeight, 2, null, "Century Gothic", 10, true, null); UtilExcelCom.Current().InsertData(be); be = new ExcelBE(1, 3, "说明", "C1", "C1", "GRAYDARK", false, 10, 24.50, rowHeight, 2, null, "Century Gothic", 10, true, null); UtilExcelCom.Current().InsertData(be); int rowno = 2; int index = 1; int line = 1; string idIndex = ""; string tablet_name = "", table_comment = ""; string[] commentArr; ArrayList commentList; foreach (Dictionary <string, string> tablename in tableInfos.Values) { table_comment = ""; idIndex = index.ToString(); if (idIndex.Length < 2) { idIndex = "0" + idIndex; } be = new ExcelBE(rowno, 1, "A" + idIndex, "A" + rowno, "A" + rowno, null, false, 10, 13.50, rowHeight, 2, null, "Century Gothic", 10, false, null); UtilExcelCom.Current().InsertData(be); tablet_name = tablename["Name"]; be = new ExcelBE(rowno, 2, tablet_name, "B" + rowno, "B" + rowno, null, false, 10, 32.63, rowHeight, 1, null, "Century Gothic", 10, false, null); UtilExcelCom.Current().InsertData(be); //添加链接 UtilExcelCom.Current().AddLink(be, tablet_name, "列名"); table_comment = tablename["Comment"]; table_comment = table_comment.Trim(); line = 1; rowHeight = 25; if (UtilString.Contains(table_comment, "\r", "\n")) { rowHeight = 16; commentArr = table_comment.Split(new char[2] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries); rowHeight = rowHeight * line; commentList = new ArrayList(commentArr); table_comment = ""; foreach (var commenti in commentList) { table_comment += commenti + "\r\n"; line += 1; } line -= 1; table_comment = table_comment.Substring(0, table_comment.Length - 2); rowHeight = rowHeight * line + 8; } be = new ExcelBE(rowno, 3, table_comment, "C" + rowno, "C" + rowno, null, false, 10, 24.50, rowHeight, 1, null, "Century Gothic", 10, false, null); UtilExcelCom.Current().InsertData(be); rowno++; index++; } //数据库各表详细说明 Dictionary <string, Dictionary <string, string> > columnInfos; string comment = "", dicComment = ""; foreach (Dictionary <string, string> tablename in tableInfos.Values) { rowHeight = 16; columnInfos = (cbDbType.SelectedIndex == 0) ? UtilSqlserver.FieldInfoList(tablename["Name"]) : UtilMysql.FieldInfoList(tablename["Name"]); UtilExcelCom.Current().AddSheet(tablename["Name"]); be = new ExcelBE(1, 1, "列名", "A1", "A1", "GRAYDARK", false, 10, 36.50, rowHeight, 2, null, "Century Gothic", 10, true, null); UtilExcelCom.Current().InsertData(be); be = new ExcelBE(1, 2, "数据类型", "B1", "B1", "GRAYDARK", false, 10, 24.50, rowHeight, 2, null, "Century Gothic", 10, true, null); UtilExcelCom.Current().InsertData(be); be = new ExcelBE(1, 3, "长度", "C1", "C1", "GRAYDARK", false, 10, 24.50, rowHeight, 2, null, "Century Gothic", 10, true, null); UtilExcelCom.Current().InsertData(be); be = new ExcelBE(1, 4, "允许NULL", "D1", "D1", "GRAYDARK", false, 10, 24.50, rowHeight, 2, null, "Century Gothic", 10, true, null); UtilExcelCom.Current().InsertData(be); be = new ExcelBE(1, 5, "键值", "E1", "E1", "GRAYDARK", false, 10, 24.50, rowHeight, 2, null, "Century Gothic", 10, true, null); UtilExcelCom.Current().InsertData(be); be = new ExcelBE(1, 6, "说明", "F1", "F1", "GRAYDARK", false, 10, 39, rowHeight, 2, null, "Century Gothic", 10, true, null); UtilExcelCom.Current().InsertData(be); bool isDicComment = false; foreach (Dictionary <string, string> columnInfo in columnInfos.Values) { if (columnInfo.Keys.Contains("Comment")) { comment = columnInfo["Comment"]; } else { comment = ""; } if (UtilString.Contains(comment, "\r", "\n")) { if (columnInfo["Type"].Equals("char")) { isDicComment = true; break; } } } if (isDicComment) { be = new ExcelBE(1, 7, "数据字典定义", "G1", "G1", "GRAYDARK", false, 10, 48.50, rowHeight, 2, null, "Century Gothic", 10, true, null); UtilExcelCom.Current().InsertData(be); } int tablerowno = 2; bool isColumnDicComment = false; foreach (Dictionary <string, string> columnInfo in columnInfos.Values) { rowHeight = 16; if (columnInfo.Keys.Contains("Comment")) { comment = columnInfo["Comment"]; } else { comment = ""; } isColumnDicComment = false; dicComment = ""; line = 1; comment = comment.Trim(); if (isDicComment) { if (UtilString.Contains(comment, "\r", "\n")) { commentArr = comment.Split(new char[2] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries); comment = commentArr[0]; commentList = new ArrayList(commentArr); commentList.Remove(0); foreach (var commenti in commentList) { dicComment += commenti + "\r\n"; line += 1; } line -= 1; dicComment = dicComment.Substring(0, dicComment.Length - 2); rowHeight = rowHeight * line + 8; if (columnInfo["Type"].Equals("char")) { isColumnDicComment = true; } else { comment = dicComment; } } else { comment = comment.Trim(); if (UtilString.Contains(comment, "\r", "\n")) { commentArr = comment.Split(new char[2] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries); commentList = new ArrayList(commentArr); comment = ""; foreach (var commenti in commentList) { comment += commenti + "\r\n"; line += 1; } line -= 1; comment = comment.Substring(0, comment.Length - 2); rowHeight = rowHeight * line + 8; } } } else { comment = comment.Trim(); if (UtilString.Contains(comment, "\r", "\n")) { commentArr = comment.Split(new char[2] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries); commentList = new ArrayList(commentArr); comment = ""; foreach (var commenti in commentList) { comment += commenti + "\r\n"; line += 1; } line -= 1; comment = comment.Substring(0, comment.Length - 2); rowHeight = rowHeight * line + 8; } } be = new ExcelBE(tablerowno, 1, columnInfo["Field"], "A" + tablerowno, "A" + tablerowno, null, false, 10, 18, rowHeight, 2, null, "Century Gothic", 10, false, null); UtilExcelCom.Current().InsertData(be); string Type_Only = ""; if (cbDbType.SelectedIndex == 0) { Type_Only = columnInfo["Type"]; } else { Type_Only = columnInfo["Type_Only"]; } be = new ExcelBE(tablerowno, 2, Type_Only, "B" + tablerowno, "B" + tablerowno, null, false, 10, 15, rowHeight, 1, null, "Century Gothic", 10, false, null); UtilExcelCom.Current().InsertData(be); be = new ExcelBE(tablerowno, 3, columnInfo["Length"], "C" + tablerowno, "C" + tablerowno, null, false, 10, 9, rowHeight, 1, null, "Century Gothic", 10, false, null); UtilExcelCom.Current().InsertData(be); be = new ExcelBE(tablerowno, 4, columnInfo["Null"], "D" + tablerowno, "D" + tablerowno, null, false, 10, 9, rowHeight, 1, null, "Century Gothic", 10, false, null); UtilExcelCom.Current().InsertData(be); be = new ExcelBE(tablerowno, 5, columnInfo["Fkpk"], "E" + tablerowno, "E" + tablerowno, null, false, 10, 9, rowHeight, 1, null, "Century Gothic", 10, false, null); UtilExcelCom.Current().InsertData(be); be = new ExcelBE(tablerowno, 6, comment, "F" + tablerowno, "F" + tablerowno, null, false, 10, 39, rowHeight, 1, null, "Century Gothic", 10, false, null); UtilExcelCom.Current().InsertData(be); if (isDicComment) { if (!isColumnDicComment) { dicComment = ""; } be = new ExcelBE(tablerowno, 7, dicComment, "G" + tablerowno, "G" + tablerowno, null, false, 10, 45, rowHeight, 1, null, "Century Gothic", 10, false, null); UtilExcelCom.Current().InsertData(be); } tablerowno++; } } UtilExcelCom.Current().SetActivateSheet(1); //显示Excel UtilExcelCom.Current().DoExport(); string sitename = ConfigurationManager.AppSettings["SiteName"]; UtilExcelCom.Current().Save(Path.Combine(System.Environment.CurrentDirectory, sitename + "数据模型.xlsx")); btnExportexcel.Enabled = true; }
/// <summary> /// 修改数据库主键部分 /// </summary> private static string CreateDbKeyDefine() { string result = "/****** 创建数据库的外键部分 Script Date:" + DateTime.Now + " ******/\r\n"; string tablename, refer_tablename, id_column; string column_name, sql_template; foreach (Dictionary <string, string> tableInfo in tableInfos.Values) { //获取表名 tablename = tableInfo["Name"]; if (IsSqlserverDefault) { tablename = UtilString.UcFirst(tablename); } Dictionary <string, Dictionary <string, string> > columnInfos; columnInfos = UtilMysql.FieldInfoList(tablename); id_column = "ID"; //获取主键名称 foreach (Dictionary <string, string> columnInfo in columnInfos.Values) { column_name = columnInfo["Field"]; if (!IsSqlserverDefault) { string[] tbl = tablename.Split('_'); if (tbl.Length > 1) { if (column_name.ToUpper().Contains("ID") && column_name.ToUpper().Contains(tbl[tbl.Length - 1].ToUpper())) { id_column = column_name; break; } } } } //获取主键名称 foreach (Dictionary <string, string> columnInfo in columnInfos.Values) { column_name = columnInfo["Field"]; string tmptbl = tablename; if (IsSqlserverDefault) { column_name = UtilString.UcFirst(column_name); } else { string[] tbl = tablename.Split('_'); if (tbl.Length > 1) { tmptbl = tbl[tbl.Length - 1]; } } if (column_name.ToUpper().Contains("_ID") && (!column_name.ToUpper().Contains(tmptbl.ToUpper()))) { refer_tablename = column_name.Replace("_ID", ""); refer_tablename = refer_tablename.Replace("_id", ""); if (refer_tablename.ToUpper().Equals("PARENT")) { refer_tablename = tablename; } if (tablenames.Contains(refer_tablename)) { sql_template = @" ALTER TABLE [dbo].[{0}] WITH CHECK ADD CONSTRAINT [FK_{0}_{1}] FOREIGN KEY([{2}]) REFERENCES [dbo].[{1}] ([{3}]) GO ALTER TABLE [dbo].[{0}] CHECK CONSTRAINT [FK_{0}_{1}] GO "; result += string.Format(sql_template, tablename, refer_tablename, column_name, id_column); } } } } return(result); }
/// <summary> /// 生成数据库主体部分 /// </summary> private static string CreateDbDefine() { string result = "/****** 创建数据库所有表 Script Date:" + DateTime.Now + " ******/\r\n"; string database_name = UtilMysql.Database_Name; if (IsSqlserverDefault) { database_name = UtilString.UcFirst(database_name); } result += "USE " + database_name + "\r\n"; string tablename, refer_tablename, tableComment, columnDefine; string sqlTemplate, column_name, column_type, column_null, column_default, resetSeed; string defaultValue, id_column; Dictionary <string, Dictionary <string, string> > columnInfos; foreach (Dictionary <string, string> tableInfo in tableInfos.Values) { //获取表名 tablename = tableInfo["Name"]; if (IsSqlserverDefault) { tablename = UtilString.UcFirst(tablename); } tableComment = tableInfo["Comment"]; columnInfos = UtilMysql.FieldInfoList(tablename); columnDefine = ""; defaultValue = ""; id_column = "ID"; resetSeed = ""; //获取主键名称 foreach (Dictionary <string, string> columnInfo in columnInfos.Values) { column_name = columnInfo["Field"]; bool IsKeyColumn = false; if (IsSqlserverDefault) { column_name = UtilString.UcFirst(column_name); if (column_name.ToUpper().Equals("ID")) { IsKeyColumn = true; } } else { string[] tbl = tablename.Split('_'); if (tbl.Length > 1) { if (column_name.ToUpper().Contains("ID") && column_name.ToUpper().Contains(tbl[tbl.Length - 1].ToUpper())) { id_column = column_name; IsKeyColumn = true; } } } if (IsKeyColumn) { if (tablesIDTypeGuid.Contains(tablename)) { columnDefine += "[" + id_column + "] [uniqueidentifier] NOT NULL,"; defaultValue += string.Format("ALTER TABLE [dbo].[{0}] ADD CONSTRAINT [DF_{0}_ID] DEFAULT (newid()) FOR " + id_column + "]\r\nGO\r\n", tablename); } else { columnDefine += "[" + id_column + "][int] IDENTITY(1,1) NOT NULL,"; resetSeed += "DBCC CHECKIDENT ('" + tablename + "', RESEED, 1)\r\n"; } } else { //获取列名|列类型|是否Null column_type = columnInfo["Type"]; column_type = ConvertType(column_type); column_null = (columnInfo["Null"].Equals("YES")) ? "NULL" : "NOT NULL"; column_default = columnInfo["Default"]; if (column_name.ToUpper().Contains("_ID")) { refer_tablename = column_name.Replace("_ID", ""); refer_tablename = refer_tablename.Replace("_id", ""); if (refer_tablename.ToUpper().Equals("PARENT")) { refer_tablename = tablename; } if (tablesIDTypeGuid.Contains(refer_tablename)) { column_type = "[uniqueidentifier]"; } else { column_type = "[int]"; } column_null = "NOT NULL"; } if (UtilString.Contains(column_name.ToUpper(), "TIME", "DATE")) { if (UtilString.Contains(column_name.ToUpper(), "TIMES")) { column_type = "[int]"; } else { if (IsSqlserverDefault) { column_type = "[datetime]"; } } } if (!string.IsNullOrEmpty(column_default) && (!column_name.ToUpper().Equals("UPDATETIME"))) { if (IsSqlserverDefault) { if (!column_name.ToUpper().Contains("_ID")) { defaultValue += string.Format("ALTER TABLE [dbo].[{0}] ADD CONSTRAINT [DF_{0}_{1}] DEFAULT ({2}) FOR [{1}]\r\nGO\r\n", tablename, column_name, column_default); } } } if ((column_name.ToUpper().Equals("COMMITTIME")) || (column_name.ToUpper().Equals("UPDATETIME"))) { if (IsSqlserverDefault) { defaultValue += string.Format("ALTER TABLE [dbo].[{0}] ADD CONSTRAINT [DF_{0}_{1}] DEFAULT (getdate()) FOR [{1}]\r\nGO\r\n", tablename, column_name); } } string[] type_length = column_type.Split(new char[4] { '[', ']', '(', ')' }, StringSplitOptions.RemoveEmptyEntries); if (type_length.Length == 2) { string length = type_length[1]; int i_len = UtilNumber.Parse(length); if (i_len > 4000) { i_len = 4000; column_type = "[" + type_length[0] + "]" + "(" + i_len + ")"; } } columnDefine += string.Format(" [{0}] {1} {2},", column_name, column_type, column_null); } columnDefine += "\r\n"; } columnDefine = columnDefine.Substring(0, columnDefine.Length - 2); //生成表脚本 sqlTemplate = @" SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[{0}] ( {1} CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ([{2}] ASC) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)ON [PRIMARY] ) ON [PRIMARY] GO "; result += string.Format(sqlTemplate, tablename, columnDefine, id_column); //生成列默认值 result += defaultValue; //自增长重置为0 result += resetSeed; } return(result); }