private List <Model.Table> GetSQLTableList(DataSet ds)
{
DataTable dt = ds.Tables[0];
List <Model.Table> tables = new List <Model.Table>();
foreach (DataRow r in dt.Rows)
{
Model.Field field = GetField(r);
string parentTableName = SchemaHelper.GetString(r["TableName"]);
bool hasTable = false;
foreach (Model.Table modelTable in tables)
{
if (parentTableName == modelTable.Name)
{
modelTable.AddField(field);
hasTable = true;
break;
}
}
if (!hasTable)
{
Model.Table newTable = new Model.Table();
newTable.Name = parentTableName;
newTable.AddField(field);
tables.Add(newTable);
}
}
foreach (Model.Table table in tables)
{
table.Fields.Sort();
}
return(tables);
}
public Database GetSchema(string connectionString, Model.DatabaseTypes type)
{
Model.Database database = new Model.Database();
database.ConnString = connectionString;
database.Type = type;
//获取所有表
DataTable dtAllTable = GetDbSchema(database.ConnString, OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });
foreach (DataRow rt in dtAllTable.Rows)
{
Model.Table table = new Model.Table();
table.Name = rt["TABLE_NAME"].ToString();
DataTable dtColumns = GetDbSchema(database.ConnString, OleDbSchemaGuid.Columns, new object[] { null, null, table.Name });
foreach (DataRow rc in dtColumns.Rows)
{
Model.Field field = GetField(database.ConnString, table.Name, rc);
table.AddField(field);
}
table.Fields.Sort();
database.AddTable(table);
}
//获取所有视图
DataTable dtAllView = GetDbSchema(database.ConnString, OleDbSchemaGuid.Views, null);
foreach (DataRow rv in dtAllView.Rows)
{
Model.Table view = new Model.Table();
view.Name = rv["TABLE_NAME"].ToString();
DataTable dtColumns = GetDbSchema(database.ConnString, OleDbSchemaGuid.Columns, new object[] { null, null, view.Name });
foreach (DataRow rc in dtColumns.Rows)
{
Model.Field field = GetField(database.ConnString, view.Name, rc);
view.AddField(field);
}
view.Fields.Sort();
database.AddView(view);
}
//获取所有存储过程
DataTable dtAllStoreProcedure = GetDbSchema(database.ConnString, OleDbSchemaGuid.Procedures, null);
foreach (DataRow rsp in dtAllStoreProcedure.Rows)
{
database.StoreProcedures.Add(rsp["PROCEDURE_NAME"].ToString());
}
return(database);
}
private void GetColumns(Model.Table table)
{
DataSet dsTableColumns = helper.ExecuteQuery(CommandType.Text,
string.Format("select * from user_tab_cols where table_name = '{0}'", table.Name),
null);
//获取所有字段
foreach (DataRow r in dsTableColumns.Tables[0].Rows)
{
Model.Field field = new Model.Field();
field.AllowNull = SchemaHelper.GetString(r["NULLABLE"]).Equals("Y", StringComparison.CurrentCultureIgnoreCase);
field.DefaultValue = r["DATA_DEFAULT"].ToString();
//field.Descn 暂时获取不到
field.SetDbType(Model.DatabaseTypes.Oracle, SchemaHelper.GetString(r["DATA_TYPE"]));
//field.IsIdentifier 暂时获取不到
//field.IsKeyField
field.Length = SchemaHelper.GetInt(r["DATA_LENGTH"]);
field.Name = SchemaHelper.GetString(r["COLUMN_NAME"]);
field.Position = SchemaHelper.GetInt(r["COLUMN_ID"]);
field.Size = (int)field.Length;
table.AddField(field);
}
//获取主键
DataSet ds = helper.ExecuteQuery(CommandType.Text,
string.Format("select col.column_name from user_constraints con, user_cons_columns col where con.constraint_name = col.constraint_name and con.constraint_type='P' and col.table_name = '{0}'", table.Name),
null);
foreach (DataRow r in ds.Tables[0].Rows)
{
string key = SchemaHelper.GetString(r[0]);
foreach (Model.Field field in table.Fields)
{
if (field.Name.Equals(key, StringComparison.CurrentCultureIgnoreCase))
{
field.IsKeyField = true;
}
}
}
}
private void GetColumns(Model.Database db, Model.Table table)
{
// 对每个表取字段属性
DataSet dsColumns = dbHelper.ExecuteQuery(CommandType.Text,
string.Format("select * from COLUMNS where TABLE_SCHEMA='{0}' and TABLE_NAME='{1}'", db.Name, table.Name),
null);
foreach (DataRow rField in dsColumns.Tables[0].Rows)
{
Model.Field field = new Model.Field();
field.IsIdentifier = rField["EXTRA"].ToString().ToLower() == "auto_increment";
field.IsKeyField = rField["COLUMN_KEY"].ToString().ToLower() == "pri";
field.AllowNull = rField["IS_NULLABLE"].ToString().ToLower() == "yes";
field.SetDbType(Model.DatabaseTypes.MySql, SchemaHelper.GetString(rField["DATA_TYPE"]));
field.DefaultValue = SchemaHelper.GetString(rField["COLUMN_DEFAULT"]);
field.Descn = SchemaHelper.GetString(rField["COLUMN_COMMENT"]);
field.Length = SchemaHelper.GetLong(rField["CHARACTER_MAXIMUM_LENGTH"]);
field.Name = SchemaHelper.GetString(rField["COLUMN_NAME"]);
field.Position = SchemaHelper.GetInt(rField["ORDINAL_POSITION"]);
table.AddField(field);
}
}