public void LoadCols(DataTable columns)
{
Type type = typeof(OracleTableColClass);
foreach (DataRow item in columns.Rows)
{
OracleTableColClass occ = new OracleTableColClass();
foreach (FieldInfo fi in type.GetFields())
{
try
{
fi.SetValue(occ, item[fi.Name.ToUpper()]);
}
catch (System.Exception ex) { }
}
this.columns.Add(occ);
}
}
public List <CreateSqlObject> GetCreateOracleSql(string tableSpace = null)
{
DoLoadCols();
List <CreateSqlObject> list = new List <CreateSqlObject>();
if (string.IsNullOrWhiteSpace(GetObjectString(table_name)))
{
return(list);
}
string tbsp = string.IsNullOrWhiteSpace(tableSpace) ? Convert.ToString(tablespace_name) : tableSpace;
StringBuilder sb = new StringBuilder();
CreateSqlObject table = new CreateSqlObject();
table.comment = "创建表" + table_name;
sb.AppendLine("create table " + table_name);
sb.AppendLine("(");
for (int i = 0; i < columns.Count; i++)
{
OracleTableColClass item = (OracleTableColClass)columns[i];
string dataType = Convert.ToString(item.data_type);
string columnName = Convert.ToString(item.column_name);
//避免Oracle关键字作为列名
if (OracleReserveKeys.Contains(columnName.ToUpper()))
{
columnName = "\"" + columnName + "\"";
}
string temp = " " + columnName + " ";
if (dataType != "DATE" &&
dataType != "CLOB" &&
dataType != "NCLOB" &&
dataType != "BLOB" &&
dataType != "BFILE" &&
dataType != "ROWID" &&
dataType != "BINARY_DOUBLE" &&
dataType != "BINARY_FLOAT" &&
!dataType.StartsWith("INTERVAL DAY") &&
!dataType.StartsWith("INTERVAL YEAR") &&
!dataType.StartsWith("TIMESTAMP"))
{
string data_precision = Convert.ToString(item.data_precision);
string data_scale = Convert.ToString(item.data_scale);
string data_length = Convert.ToString(item.data_length);
int precision = -1, scale = -1, length = -1;
int.TryParse(data_precision, out precision);
int.TryParse(data_scale, out scale);
int.TryParse(data_length, out length);
string tnum = "";
if (precision > 0)
{
tnum += precision;
if (scale > 0)
{
tnum += "," + scale;
}
}
else if (length > 0)
{
if (dataType == "NUMBER")
{
dataType = "INTEGER";
}
else if (dataType == "NVARCHAR2")
{
tnum += (length / 2);
}
else
{
tnum += length;
}
}
if (tnum != "")
{
tnum = "(" + tnum + ")";
}
temp += dataType + tnum;
}
else
{
temp += dataType;
}
if (!string.IsNullOrWhiteSpace(GetObjectString(item.data_default)))
{
temp += " default " + item.data_default;
}
if (Convert.ToString(item.nullable) == "N")
{
temp += " not null";
}
if (i != columns.Count - 1)
{
temp += ",";
}
sb.AppendLine(temp);
}
sb.AppendLine(")");
sb.AppendLine("tablespace " + tbsp);
if (!string.IsNullOrWhiteSpace(GetObjectString(pct_free)))
{
sb.AppendLine(" pctfree " + pct_free);
}
if (!string.IsNullOrWhiteSpace(GetObjectString(ini_trans)))
{
sb.AppendLine(" initrans " + ini_trans);
}
if (!string.IsNullOrWhiteSpace(GetObjectString(max_trans)))
{
sb.AppendLine(" maxtrans " + max_trans);
}
sb.AppendLine(" storage");
sb.AppendLine(" (");
if (!string.IsNullOrWhiteSpace(GetObjectString(initial_extent)))
{
sb.AppendLine(" initial " + initial_extent);
}
if (!string.IsNullOrWhiteSpace(GetObjectString(min_extents)))
{
sb.AppendLine(" minextents " + min_extents);
}
int maxE = 0;
int.TryParse(Convert.ToString(max_extents), out maxE);
if (maxE == 0 || maxE == 2147483645)
{
sb.AppendLine(" maxextents unlimited");
}
else
{
sb.AppendLine(" maxextents " + max_extents);
}
sb.AppendLine(" )");
table.sql = sb.ToString();
list.Add(table);
sb.Clear();
if (!string.IsNullOrWhiteSpace(GetObjectString(comments)))
{
CreateSqlObject comontable = new CreateSqlObject(null, "创建表" + table_name + "的描述");
sb.AppendLine("comment on table " + table_name + " is '" + comments + "'");
comontable.sql = sb.ToString();
list.Add(comontable);
}
foreach (OracleTableColClass item in columns)
{
if (string.IsNullOrWhiteSpace(GetObjectString(item.comments)))
{
continue;
}
sb.Clear();
CreateSqlObject comonclum = new CreateSqlObject(null, "添加表" + table_name + ".列" + item.column_name + "的描述");
string columnName = Convert.ToString(item.column_name);
//避免Oracle关键字作为列名
if (OracleReserveKeys.Contains(columnName.ToUpper()))
{
columnName = "\"" + columnName + "\"";
}
sb.AppendLine("comment on column " + table_name + "." + columnName + " is '" + item.comments + "'");
comonclum.sql = sb.ToString();
list.Add(comonclum);
}
return(list);
}
public void LoadCols(DataTable columns)
{
Type type = typeof(OracleTableColClass);
foreach (DataRow item in columns.Rows)
{
OracleTableColClass occ = new OracleTableColClass();
foreach (FieldInfo fi in type.GetFields())
{
try
{
fi.SetValue(occ, item[fi.Name.ToUpper()]);
}
catch (System.Exception ex) { }
}
this.columns.Add(occ);
}
}