/// <summary> /// 表关系主键显示配置 /// </summary> /// <param name="Table_Name">表名称</param> /// <param name="relationShowsElement">表关系主键显示配置生成元素</param> private void RelationShowsToConfig(string Table_Name, XElement relationShowsElement) { string ClassName = Table_Name; Dictionary <string, Dictionary <string, string> > Fields = FieldInfos[Table_Name]; foreach (KeyValuePair <string, Dictionary <string, string> > Entry in Fields) { string FieldName = Entry.Key; if (FieldName.ToUpper().Equals("ID")) { continue; } if (UtilString.Contains(FieldName.ToUpper(), "COMMITTIME", "UPDATETIME")) { continue; } if (FieldName.ToUpper().Contains("_ID")) { string Relation_Classname = FieldName.Replace("_ID", ""); Relation_Classname = UtilString.UcFirst(Relation_Classname); string ShowFieldName = ""; if (Relation_Classname.ToUpper().Equals("PARENT")) { ShowFieldName = GetShowFieldNameByClassname(ClassName); if (!string.IsNullOrEmpty(ShowFieldName)) { relationShowsElement.Add(new XElement("show", ShowFieldName, new XAttribute("local_key", FieldName), new XAttribute("relation_class", ClassName))); } } else { if (TableList.Contains(Relation_Classname)) { ShowFieldName = GetShowFieldNameByClassname(Relation_Classname); if (!string.IsNullOrEmpty(ShowFieldName)) { relationShowsElement.Add(new XElement("show", ShowFieldName, new XAttribute("local_key", FieldName), new XAttribute("relation_class", Relation_Classname))); } } } } } }
/// <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> /// <see cref="http://www.xmlplease.com/update-xml-linq" title="Update XML with LINQ to XML in ASP.NET"/> /// <param name="Table_Name">表名称</param> /// <param name="ClassElement">表五种关系映射配置</param> private void RelationFives(string Table_Name, XDocument XmlDoc) { XElement ClassElement = XmlDoc.XPathSelectElement("classes/class[@name='" + Table_Name + "']"); XElement Has_OneElement = new XElement("has_one"); XElement Belong_Has_OneElement = new XElement("belong_has_one"); XElement Has_ManyElement = new XElement("has_many"); XElement Many_ManyElement = new XElement("many_many"); XElement Belongs_Many_ManyElement = new XElement("belongs_many_many"); string ClassName = Table_Name; Dictionary <string, Dictionary <string, string> > Fields = FieldInfos[Table_Name]; XElement HasManyClassElement = null, HasOneClassElement = null; foreach (KeyValuePair <string, Dictionary <string, string> > Entry in Fields) { string FieldName = Entry.Key; if (FieldName.ToUpper().Equals("ID")) { continue; } if (UtilString.Contains(FieldName.ToUpper(), "COMMITTIME", "UPDATETIME")) { continue; } if (FieldName.ToUpper().Contains("_ID")) { string Relation_Classname = FieldName.Replace("_ID", ""); Relation_Classname = UtilString.UcFirst(Relation_Classname); if (Relation_Classname.ToUpper().Equals("PARENT")) { string instance_name = UtilString.LcFirst(ClassName); Belong_Has_OneElement.Add("relationclass", instance_name + "_p", new XAttribute("name", ClassName)); ClassElement.Add(Belong_Has_OneElement); } else { if (TableList.Contains(Relation_Classname)) { //belong_has_one:[当前表有归属表的标识,归属表没有当前表的类名+"_ID"] Dictionary <string, Dictionary <string, string> > Fields_Relation = FieldInfos[Relation_Classname]; if (!Fields_Relation.Keys.Contains(ClassName + "_ID")) { string instance_name = UtilString.LcFirst(Relation_Classname); Belong_Has_OneElement.Add(new XElement("relationclass", instance_name, new XAttribute("name", Relation_Classname))); } //has_many[归属表没有当前表的标识,当前表有归属表的标识] //has_one:[归属表没有当前表的标识,当前表有归属表的标识,并且当前表里归属表的标识为Unique] if (!Fields_Relation.Keys.Contains(ClassName + "_ID")) { string instance_name = UtilString.LcFirst(ClassName) + "s"; bool Is_Unique = false; if (Is_Unique) { HasOneClassElement = XmlDoc.XPathSelectElement("classes/class[@name='" + Relation_Classname + "']"); if (HasManyClassElement.Element("has_one") == null) { Has_OneElement.Add(new XElement("relationclass", instance_name, new XAttribute("name", ClassName))); HasOneClassElement.Add(Has_OneElement); } else { HasOneClassElement.Element("has_one").Add(new XElement("relationclass", instance_name, new XAttribute("name", ClassName))); } } else { bool is_create_hasmany = true; if (IsMany2ManyByClassname(ClassName)) { is_create_hasmany = false; } if (is_create_hasmany) { HasManyClassElement = XmlDoc.XPathSelectElement("classes/class[@name='" + Relation_Classname + "']"); if (HasManyClassElement.Element("has_many") == null) { Has_ManyElement.Add(new XElement("relationclass", instance_name, new XAttribute("name", ClassName))); HasManyClassElement.Add(Has_ManyElement); } else { HasManyClassElement.Element("has_many").Add(new XElement("relationclass", instance_name, new XAttribute("name", ClassName))); } } } } } } } } if (Belong_Has_OneElement.Elements().Count() > 0) { ClassElement.Add(Belong_Has_OneElement); } if (IsMany2ManyByClassname(ClassName)) { Dictionary <string, Dictionary <string, string> > FieldInfo_m2m = FieldInfos[Table_Name]; if (FieldInfo_m2m.Keys.Count() == 5) { //many_many[在关系表中有两个关系主键,并且表名的前半部分是其中一个主键] //belongs_many_many[在关系表中有两个关系主键,并且表名的后半部分是其中一个主键] string[] class_onetwo = new string[2]; int index = 0; foreach (KeyValuePair <string, Dictionary <string, string> > Entry in Fields) { string FieldName = Entry.Key; if (FieldName.ToUpper().Contains("_ID")) { string class_onetwo_element = FieldName.Replace("_ID", ""); class_onetwo.SetValue(FieldName, index); index += 1; } } string ownerClassname = "", belongClassname = "", ownerInstancename = "", belongInstancename = ""; if ((class_onetwo[0] + "Re" + class_onetwo[1]).ToLower().Equals(ClassName.ToLower())) { ownerClassname = class_onetwo[0]; belongClassname = class_onetwo[1]; ownerInstancename = class_onetwo[0] + "s"; belongInstancename = class_onetwo[1] + "s"; } else if ((class_onetwo[1] + "Re" + class_onetwo[0]).ToLower().Equals(ClassName.ToLower())) { ownerClassname = class_onetwo[1]; belongClassname = class_onetwo[0]; ownerInstancename = class_onetwo[1] + "s"; belongInstancename = class_onetwo[0] + "s"; } ownerClassname = UtilString.UcFirst(ownerClassname); belongClassname = UtilString.UcFirst(belongClassname); XElement ClassOwnerElement = XmlDoc.XPathSelectElement("classes/class[@name='" + ownerClassname + "']"); if (ClassOwnerElement != null) { if (ClassOwnerElement.Element("many_many") == null) { Many_ManyElement.Add(new XElement("relationclass", belongInstancename, new XAttribute("name", belongClassname))); ClassOwnerElement.Add(Many_ManyElement); } else { ClassOwnerElement.Element("many_many").Add(new XElement("relationclass", belongInstancename, new XAttribute("name", belongClassname))); } } XElement ClassBelongElement = XmlDoc.XPathSelectElement("classes/class[@name='" + belongClassname + "']"); if (ClassOwnerElement != null) { if (ClassOwnerElement.Element("belongs_many_many") == null) { Belongs_Many_ManyElement.Add(new XElement("relationclass", belongInstancename, new XAttribute("name", belongClassname))); ClassBelongElement.Add(Belongs_Many_ManyElement); } else { ClassBelongElement.Element("belongs_many_many").Add(new XElement("relationclass", ownerInstancename, new XAttribute("name", ownerClassname))); } } } } }
/// <summary> /// 添加查询条件配置 /// </summary> /// <param name="Table_Name">表名称</param> /// <param name="conditionsElement">条件生成元素</param> private void ConditionsToConfig(string Table_Name, XElement conditionsElement) { string ClassName = Table_Name; List <string> Exists_Condition = new List <string>(); string ShowFieldName = ""; if (!IsMany2ManyByClassname(ClassName)) { ShowFieldName = GetShowFieldNameByClassname(ClassName); if (!string.IsNullOrEmpty(ShowFieldName)) { bool IsValid = true; string[] MultiTablesMaybe = Regex.Split(Table_Name, "Re", RegexOptions.IgnoreCase); if (MultiTablesMaybe.Count() > 1) { IsValid = false; foreach (string TableName in MultiTablesMaybe) { if (!TableList.Contains(TableName)) { IsValid = true; break; } } } if (IsValid) { if (!string.IsNullOrEmpty(ShowFieldName)) { conditionsElement.Add(new XElement("condition", ShowFieldName)); Exists_Condition.Add(ShowFieldName); } } } } Dictionary <string, Dictionary <string, string> > Fields = FieldInfos[Table_Name]; foreach (KeyValuePair <string, Dictionary <string, string> > Entry in Fields) { string Fieldname = Entry.Key; if (Fieldname.ToUpper().Equals("ID")) { continue; } if (UtilString.Contains(Fieldname.ToUpper(), "COMMITTIME", "UPDATETIME")) { continue; } if (!string.IsNullOrEmpty(ShowFieldName)) { if (!Exists_Condition.Contains(Fieldname) && UtilString.Contains(Fieldname, "Name", "Title") && (!Fieldname.Equals(ShowFieldName)) && (!Fieldname.ToUpper().Contains("_ID"))) { conditionsElement.Add(new XElement("condition", Fieldname)); Exists_Condition.Add(Fieldname); } if (conditionsElement.Elements().Count() < count_condition) { if (!Exists_Condition.Contains(Fieldname) && UtilString.Contains(Fieldname.ToUpper(), "CODE", "_NO", "STATUS", "TYPE") && (!Fieldname.ToUpper().Contains("_ID"))) { conditionsElement.Add(new XElement("condition", Fieldname)); Exists_Condition.Add(Fieldname); } if (Fieldname.ToUpper().Contains("_ID") && (!Fieldname.ToUpper().Contains("PARENT_ID"))) { string relation_classname = Fieldname.Replace("_ID", ""); relation_classname = UtilString.UcFirst(relation_classname); string ShowFieldName_Relation = ""; if (TableList.Contains(relation_classname)) { ShowFieldName_Relation = GetShowFieldNameByClassname(relation_classname); if (!Exists_Condition.Contains(ShowFieldName_Relation)) { if (!string.IsNullOrEmpty(ShowFieldName_Relation)) { conditionsElement.Add(new XElement("condition", Fieldname, new XAttribute("relation_class", relation_classname), new XAttribute("show_name", ShowFieldName_Relation))); } Exists_Condition.Add(Fieldname); } } } } else { break; } } } }
/// <summary> /// 3.生成枚举类【一个文件】 /// [模板文件]:domain/enum.txt /// 生成文件名称:Enum.cs /// </summary> private void CreateEnum() { string ClassName = "Admin"; string Table_Comment = "系统管理员"; string Template_Name, Content; string Unit_Template; string EnumDefineBlock, EnumD2VBlock, EnumV2DBlock; string Column_Name, Column_Table_Name, Column_Type, Column_Comment, MainContent; List <Dictionary <string, string> > Enum_ColumnDefine; List <string> enumL = new List <string>();//如果重名只放一个 MainContent = ""; foreach (string Table_Name in TableList) { //读取原文件内容到内存 ClassName = Table_Name; if (TableInfoList.ContainsKey(Table_Name)) { Table_Comment = TableInfoList[Table_Name]["Comment"]; Template_Name = @"AutoCode/Model/domain/enum.txt"; Content = UtilFile.ReadFile2String(Template_Name); Dictionary <string, Dictionary <string, string> > FieldInfo = FieldInfos[Table_Name]; foreach (KeyValuePair <String, Dictionary <string, string> > entry in FieldInfo) { Column_Name = entry.Key; Column_Comment = entry.Value["Comment"]; Column_Type = entry.Value["Type"]; if (Column_Type.Equals("tinyint")) { string[] c_c = Column_Comment.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries); if (c_c.Length > 1) { if (!enumL.Contains(Column_Name)) { enumL.Add(Column_Name); Enum_ColumnDefine = EnumDefines(Column_Comment); if ((Enum_ColumnDefine != null) && (Enum_ColumnDefine.Count > 0)) { EnumDefineBlock = ""; EnumD2VBlock = ""; EnumV2DBlock = ""; foreach (Dictionary <string, string> ColumnDefine in Enum_ColumnDefine) { ColumnDefine["Name"] = UtilString.UcFirst(ColumnDefine["Name"]); EnumDefineBlock += " /// <summary>\r\n" + " /// " + ColumnDefine["Comment"] + "\r\n" + " /// </summary>\r\n" + " public const byte " + ColumnDefine["Name"] + " = " + ColumnDefine["Value"] + ";\r\n"; EnumD2VBlock += " case " + ColumnDefine["Name"] + ":\r\n" + " return \"" + ColumnDefine["Comment"] + "\";\r\n"; EnumV2DBlock += " case \"" + ColumnDefine["Comment"] + "\":\r\n" + " result= " + ColumnDefine["Name"] + ";\r\n" + " break;\r\n"; } Unit_Template = @" /// <summary> /// {$Column_Comment} /// </summary> public class Enum{$Column_Table_Name} { {$EnumDefineBlock} /// <summary> /// 显示{$Column_Comment} /// </summary> public static String {$Column_Name}Show(byte? {$Column_Name}) { switch ({$Column_Name}) { {$EnumD2VBlock} } return ""未知""; } /// <summary> /// 根据{$Column_Comment}显示文字获取{$Column_Comment} /// </summary> public static byte? {$Column_Name}ByShow(string Content) { byte result=byte.MinValue; switch (Content) { {$EnumV2DBlock} } return result; } } "; Unit_Template = Unit_Template.Replace("{$EnumDefineBlock}", EnumDefineBlock); Unit_Template = Unit_Template.Replace("{$EnumD2VBlock}", EnumD2VBlock); Unit_Template = Unit_Template.Replace("{$EnumV2DBlock}", EnumV2DBlock); Unit_Template = Unit_Template.Replace("{$Column_Name}", Column_Name); Column_Table_Name = Column_Name; if (Array.IndexOf(Same_Column_Names, Column_Name) > -1) { Column_Table_Name = ClassName + "_" + Column_Name; } Unit_Template = Unit_Template.Replace("{$Column_Table_Name}", Column_Table_Name); Column_Comment = c_c[0]; Unit_Template = Unit_Template.Replace("{$Column_Comment}", Column_Comment); MainContent += Unit_Template; } } } } } Content = Content.Replace("{$MainContent}", MainContent); //存入目标文件内容 UtilFile.WriteString2File(Save_Dir + "Enum.cs", Content); } } }
/// <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); }
/// <summary> /// 生成删除数据库所有表的脚本 /// </summary> /// <returns></returns> public static string DropAllTables() { tableInfos = UtilSqlserver.TableinfoList(); string result = "/****** 删除数据库所有表的脚本 Script Date:" + DateTime.Now + " ******/\r\n"; string tablename; string sql_template, sql_refer_tempalte, sql_df_template; Dictionary <string, Dictionary <string, string> > columnInfos; sql_template = @" /****** Object: Table [dbo].[{0}] Script Date: 08/03/2013 21:46:21 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U')) DROP TABLE [dbo].[{0}] GO "; sql_refer_tempalte = @" IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND parent_object_id = OBJECT_ID(N'[dbo].[{1}]')) ALTER TABLE [dbo].[{1}] DROP CONSTRAINT [{0}] GO "; sql_df_template = @" IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_{0}_{1}]') AND type = 'D') BEGIN ALTER TABLE [dbo].[{0}] DROP CONSTRAINT [DF_{0}_{1}] END GO "; Dictionary <string, Dictionary <string, string> > table_fk = UtilSqlserver.Table_Foreign_Key(); string fkname, ftablename; foreach (Dictionary <string, string> table_fkInfo in table_fk.Values) { fkname = table_fkInfo["fkname"]; ftablename = table_fkInfo["ftablename"]; //fkcol = table_fkInfo["fkcol"]; //rtable = table_fkInfo["rtable"]; result += string.Format(sql_refer_tempalte, fkname, ftablename); } ArrayList allTbNames = new ArrayList(tableInfos.Keys.Count()); foreach (Dictionary <string, string> tableInfo in tableInfos.Values) { //获取表名 tablename = tableInfo["Name"]; tablename = UtilString.UcFirst(tablename); columnInfos = UtilSqlserver.FieldInfoList(tablename); allTbNames.Add(tablename); string column_name; //获取主键名称 foreach (Dictionary <string, string> columnInfo in columnInfos.Values) { column_name = columnInfo["Field"]; column_name = UtilString.UcFirst(column_name); if ((column_name.ToUpper().Equals("COMMITTIME")) || (column_name.ToUpper().Equals("UPDATETIME"))) { result += string.Format(sql_df_template, tablename, column_name); } } //result += string.Format(sql_template, tablename); } allTbNames.Sort(); allTbNames.Reverse(); foreach (var tbname in allTbNames) { result += string.Format(sql_template, tbname); } return(result); }
/// <summary> /// 生成数据库主体部分 /// </summary> private static string CreateDbDefine() { string now = DateTime.Now.ToString(); string result = @" /* Betterlife.Net Sqlserver Convert to Mysql Source Server : localhost_3306 Source Server Version : 50520 Source Host : localhost:3306 Source Database : {0} Target Server Type : MYSQL Target Server Version : 50520 File Encoding : 65001 Date: {1} */ SET FOREIGN_KEY_CHECKS=0; "; result = string.Format(result, UtilSqlserver.Database_Name, now); string tablename, tableComment, columnDefine; string sqlTemplate, column_name, column_comment, column_type, column_default, column_pkeys; List <string> primary_keys; Dictionary <string, Dictionary <string, string> > columnInfos; string[] type_length, p_keys, enum_comments; foreach (Dictionary <string, string> tableInfo in tableInfos.Values) { //获取表名 tablename = tableInfo["Name"]; tablename = UtilString.UcFirst(tablename); tableComment = tableInfo["Comment"]; tableComment = tableComment.Replace("\r", "\\r"); tableComment = tableComment.Replace("\n", "\\n"); columnInfos = UtilSqlserver.FieldInfoList(tablename); columnDefine = ""; column_default = ""; primary_keys = new List <string>(); //获取主键名称 foreach (Dictionary <string, string> columnInfo in columnInfos.Values) { column_name = columnInfo["Field"]; column_name = UtilString.UcFirst(column_name); if (columnInfo.Keys.Contains("Comment")) { column_comment = columnInfo["Comment"]; } else { column_comment = column_name; } if (column_name.ToUpper().Equals("ID")) { columnDefine += " `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '" + column_comment + "',"; primary_keys.Add("`ID`"); } else { //获取列名|列类型|是否Null column_type = columnInfo["Type"]; //column_default = columnInfo["Default"]; if (column_name.ToUpper().Contains("_ID")) { column_type = "int(11) NOT NULL "; primary_keys.Add("`" + column_name + "`"); column_default = "DEFAULT '0'"; } else { if (columnInfo["Null"].Equals("YES") || columnInfo["Null"].Equals("是")) { column_default = "DEFAULT NULL"; } else { column_default = "NOT NULL"; } if (UtilString.Contains(column_name.ToUpper(), "TIME", "DATE")) { if (UtilString.Contains(column_name.ToUpper(), "TIMES")) { column_type = "int"; column_default = "DEFAULT '0'"; } else { column_type = "datetime"; } } } type_length = column_type.Split(new char[4] { '[', ']', '(', ')' }, StringSplitOptions.RemoveEmptyEntries); if (type_length.Length == 1) { column_type = type_length[0]; if (column_type.Equals("nvarchar")) { column_type = "varchar"; } if (column_type.Equals("tinyint")) { enum_comments = Enum_ColumnDefine(column_comment); if ((enum_comments != null) && (enum_comments.Length > 1)) { column_type = "enum(" + string.Join(",", enum_comments) + ")"; } } else { if (!column_type.Equals("datetime") && !type_length.Contains("date")) { string length = columnInfo["Length"]; int i_len = UtilNumber.Parse(length); column_type += "(" + i_len + ")"; } } } column_comment = column_comment.Replace("'", "\""); column_comment = column_comment.Replace("\r", "\\r"); column_comment = column_comment.Replace("\n", "\\n"); column_comment = "COMMENT '" + column_comment + "'"; columnDefine += string.Format(" `{0}` {1} {2} {3},", column_name, column_type, column_default, column_comment); } columnDefine += "\r\n"; } if (columnDefine.Length > 2) { columnDefine = columnDefine.Substring(0, columnDefine.Length - 2); } //生成表脚本 sqlTemplate = @" -- ---------------------------- -- Table structure for `{0}` -- ---------------------------- DROP TABLE IF EXISTS `{0}`; CREATE TABLE `{0}` ( {1} ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='{2}'; "; p_keys = primary_keys.ToArray(); column_pkeys = "\r\n PRIMARY KEY (" + string.Join(",", p_keys) + ")"; columnDefine += column_pkeys; result += string.Format(sqlTemplate, tablename, columnDefine, tableComment); } return(result); }