反射エンティティモデル生成Oracle SQLスクリプト
42160 ワード
反射は本当に使いやすいものです。会社は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 ， 。 ，
///
///
/// 二次補正で、表名の分詞を追加します。 ///
/// 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
///
///
/// ・
/// ， OrderDetail
/// Order_Detail
///
///
///