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