反射エンティティモデル生成Oracle SQLスクリプト

反射は本当に使いやすいものです。会社はOracleデータベースを使いますが、本体モデルは既存のものです。反射で本体の実体類と属性を見つけてSQLスクリプトを生成するつもりです。
コードを貼り付けてください: /// /// SQL /// 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()); } } } } /// /// .NET ， 。 ， /// /// /// 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; } 二次補正で、表名の分詞を追加します。 /// /// SQL /// 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()); } } /// /// .NET Oralce /// /// /// 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; } /// ・ /// ， OrderDetail /// Order_Detail /// /// /// 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; }