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