反射真是很好用的东东。因为公司要用Oracle数据库，而实体模型是现有的。于是就想着用反射来找到实体中的实体类和属性，然后来生成SQL脚本。
贴一下代码：
/// <summary> /// 生成SQL的方法 /// </summary> static void GenerateOracleSql() { //架构名称 string schemal = "GYOUNG"; //加载程序集 Assembly ase = Assembly.LoadFrom(@"G:\学习项目\ServiceTest\test.dll"); //获取程序集中的类 Type[] types = ase.GetTypes(); if (types.Count() > 0) { foreach (var type in types) { //排除接口 if (type.Name.StartsWith("I")) continue; StringBuilder sb = new StringBuilder(); StringBuilder builder = new StringBuilder(); sb.AppendLine("-- Creating table '" + type.Name + "'"); sb.AppendLine("CREATE TABLE \"" + schemal + "\".\"" + type.Name.ToUpper() + "\" ("); //获取类中的属性 PropertyInfo[] propertyInfos = type.GetProperties(); foreach (var p in propertyInfos) { //排除队列属性 if (p.PropertyType.Name.StartsWith("ICollection")) continue; //外键关联，如果不是系统类型，则认为是有一个导航属性 if (!p.PropertyType.FullName.StartsWith("System")) { builder.AppendLine("--Create Foreign Key on table "+type.Name); builder.AppendLine("ALTER TABLE \""+schemal+"\".\""+type.Name.ToUpper()+"\""); builder.AppendLine("ADD CONSTRAINT FK_"+type.Name.ToUpper()+p.PropertyType.Name.ToUpper()); builder.AppendLine("FOREIGN KEY (" + p.PropertyType.Name.ToUpper() + "ID) REFERENCES "+p.PropertyType.Name.ToUpper()); } if (p.Name.ToLower().EndsWith("id")) {//"ID" NUMBER(9,0) NOT NULL, sb.AppendLine("\"" + p.Name.ToUpper() + "\" " + GetSqlType(p.Name) + " NOT NULL"); } else { sb.AppendLine("\"" + p.Name.ToUpper() + "\" " + GetSqlType(p.Name) + " NULL"); } } sb.AppendLine(");"); sb.AppendLine("-- Creating primary key on \"ID\" in table '" + type.Name + "'"); sb.AppendLine("ALTER TABLE \"" + schemal + "\".\"" + type.Name + "\""); sb.AppendLine("ADD CONSTRAINT \"PK_" + type.Name + "\""); sb.AppendLine(" PRIMARY KEY (\"ID\" )"); sb.AppendLine("ENABLE"); sb.AppendLine("VALIDATE;"); using (StreamWriter sw = new StreamWriter("ahmt.sql", true)) { sw.Write(sb.ToString()); sw.Write(builder.ToString()); } } } } /// <summary> /// 根据.NET类型，返回数据库的类型。因为只作测试，不一定准确 /// </summary> /// <param name="typeName"></param> /// <returns></returns> static string GetSqlType(string typeName) { string type = string.Empty; switch (typeName) { case "Int32": type = "NUMBER(9,0)"; break; case "String": type = "VARCHAR2"; break; case "Decimal": type = "NUMBER(36,4)"; break; case "Double": type = "NUMBER(36,4)"; break; case "DateTime": type = "DATE"; break; case "Boolean": type = "NUMBER(1,0)"; break; case "Char": type = "VARCHAR2"; break; default: type = "VARCHAR2"; break; } return type; }
二次修改，增加表名的分词
/// <summary> /// 生成SQL的方法 /// </summary> static void GenerateOracleSql() { string schemal="GYOUNG"; Assembly ase = Assembly.LoadFrom(@"D:\My Documents\Visual Studio 2012\Projects\ServiceTest\UniCloud.AHMT\bin\Debug\UniCloud.AHMT.dll"); Type[] types = ase.GetTypes(); //创建表 StringBuilder tableBuilder = new StringBuilder(); tableBuilder.AppendLine("-- --------------------------------------------------"); tableBuilder.AppendLine("--Creating all tables"); tableBuilder.AppendLine("-- --------------------------------------------------"); tableBuilder.AppendLine(); //创建主键 StringBuilder pkBuilder = new StringBuilder(); pkBuilder.AppendLine("-- --------------------------------------------------"); pkBuilder.AppendLine("-- Creating all PRIMARY KEY constraints"); pkBuilder.AppendLine("-- --------------------------------------------------"); pkBuilder.AppendLine(); //创建外键 StringBuilder fkbuilder = new StringBuilder(); fkbuilder.AppendLine("-- --------------------------------------------------"); fkbuilder.AppendLine("-- Creating all FOREIGN KEY constraints"); fkbuilder.AppendLine("-- --------------------------------------------------"); fkbuilder.AppendLine(); if (types.Count() > 0) { foreach (var tp in types) { if (tp.Name.ToUpper() == "FOCFLIGHT") { } if ((tp.Name.StartsWith("I")&&tp.BaseType==null) || tp.Name == "Entity") continue; tableBuilder.AppendLine("-- Creating table '" + tp.Name.SplitWord() + "'"); tableBuilder.AppendLine("CREATE TABLE \"" + schemal + "\".\"" +tp.Name.SplitWord().ToUpper() + "\" ("); PropertyInfo[] propertyInfos = tp.GetProperties(); foreach (var p in propertyInfos) { if (p.PropertyType.Name.StartsWith("ICollection") || p.Name == "UncommittedEvents") continue; //外键关联，如果不是系统类型，则认为是有一个导航属性 if (!p.PropertyType.FullName.StartsWith("System")) { if (p.PropertyType.Name.ToUpper() == "INTUNIT") { } fkbuilder.AppendLine(); fkbuilder.AppendLine("--Create Foreign Key on table " + tp.Name.SplitWord()); fkbuilder.AppendLine("ALTER TABLE \"" + schemal + "\".\"" + tp.Name.SplitWord().ToUpper() + "\""); //因为oracle名字不能超过30个字符，所以如果大于30则截断 string constraintName= tp.Name.ToUpper() +"_"+ p.PropertyType.Name.ToUpper(); if (constraintName.Length > 24) { constraintName = constraintName.Substring(0, 24); } fkbuilder.AppendLine("ADD CONSTRAINT FK_" + constraintName); fkbuilder.AppendLine("FOREIGN KEY (\"" + p.PropertyType.Name.SplitWord().ToUpper() + "ID\") "); fkbuilder.AppendLine("REFERENCES \""+schemal+"\".\"" + p.PropertyType.Name.SplitWord().ToUpper()+"\""); fkbuilder.AppendLine("(\"ID\")"); fkbuilder.AppendLine("ENABLE"); fkbuilder.AppendLine("VALIDATE;"); fkbuilder.AppendLine(); fkbuilder.AppendLine("-- Creating index for FOREIGN KEY "); fkbuilder.AppendLine("CREATE INDEX \"IX_FK_" + constraintName + "\""); fkbuilder.AppendLine("ON \"" + schemal + "\".\"" + tp.Name.SplitWord().ToUpper() + "\""); //("AcTypeID"); fkbuilder.AppendLine("(\""+p.PropertyType.Name.SplitWord().ToUpper()+"ID\");"); fkbuilder.AppendLine(); continue; } if (p.Name.ToLower().EndsWith("id")) { if (p.PropertyType.Name.StartsWith("Nullable")) { var bp = p.PropertyType.GenericTypeArguments[0].Name; tableBuilder.AppendLine("\"" + p.Name.SplitWord().ToUpper() + "\" " + GetSqlType(bp) + " NULL,"); } else { tableBuilder.AppendLine("\"" + p.Name.SplitWord().ToUpper() + "\" " + GetSqlType(p.PropertyType.Name) + " NOT NULL,"); } } else { //处理可空类型 if (p.PropertyType.Name.StartsWith("Nullable")) { var bp = p.PropertyType.GenericTypeArguments[0].Name; tableBuilder.AppendLine("\"" + p.Name.SplitWord().ToUpper() + "\" " + GetSqlType(bp) + " NULL,"); } else { tableBuilder.AppendLine("\"" + p.Name.SplitWord().ToUpper() + "\" " + GetSqlType(p.PropertyType.Name) + " NULL,"); } } } tableBuilder=tableBuilder.Remove(tableBuilder.Length -3, 1); tableBuilder.AppendLine(");"); tableBuilder.AppendLine(); //创建主键 pkBuilder.AppendLine("-- Creating primary key on \"ID\"in table '" + tp.Name + "'"); pkBuilder.AppendLine("ALTER TABLE \"" + schemal + "\".\"" + tp.Name.SplitWord().ToUpper() + "\""); pkBuilder.AppendLine("ADD CONSTRAINT \"PK_" + tp.Name.ToUpper() + "\""); pkBuilder.AppendLine(" PRIMARY KEY (\"ID\" )"); pkBuilder.AppendLine("ENABLE"); pkBuilder.AppendLine("VALIDATE;"); pkBuilder.AppendLine(); } } using (StreamWriter sw = new StreamWriter("ahmt.sql", true)) { sw.Write(tableBuilder.ToString()); sw.Write(pkBuilder.ToString()); sw.Write(fkbuilder.ToString()); } } /// <summary> /// 返回.NET类型对应的Oralce类型 /// </summary> /// <param name="typeName"></param> /// <returns></returns> static string GetSqlType(string typeName) { string tp = string.Empty; switch (typeName) { case "Int32": tp = "NUMBER(9,0)"; break; case "String": tp = "NVARCHAR2(100)"; break; case "Decimal": tp = "NUMBER(38,4)"; break; case "Double": tp = "NUMBER(38,4)"; break; case "DateTime": tp = "DATE"; break; case "Boolean": tp = "NUMBER(1,0)"; break; case "Char": tp = "NVARCHAR2(10)"; break; default: tp = "NVARCHAR2(100)"; break; } return tp; } /// <summary> /// 分割复合类型的英文名，如OrderDetail /// 分割成Order_Detail /// </summary> /// <param name="word"></param> /// <returns></returns> static string SplitWord(this string word) { string fw = string.Empty; char[] cs = word.ToCharArray(); Regex r2 = new Regex("[A-Z]"); List<int> indexs = new List<int>(); for (int i = 1; i < cs.Length; i++) { bool f = r2.IsMatch(cs[i].ToString()); if (f) { //最后有大于两个字母在分词 if (cs.Length - i > 2) { indexs.Add(i); } } } int start = 0; for (int i = 0; i < indexs.Count; i++) { int length = indexs[i] - start; fw += word.Substring(start, length) + "_"; start = indexs[i]; } fw += word.Substring(start, word.Length - start); return fw; }