public Model.Database GetSchema(string connectionString, Model.DatabaseTypes type)
{
Model.Database database = new Model.Database();
database.ConnString = connectionString;
database.Type = type;
//获取数据库结构
DBUtility.DbHelper dbHelper = DBUtility.DbHelper.Create("SqlServer");
dbHelper.ConnectionString = database.ConnString;
DataSet ds = dbHelper.ExecuteDataset(CommandType.Text,
GetSql(database.Type, SchemaHelper.SchemaType.Table), null);
List <Model.Table> tables = GetSQLTableList(ds);
foreach (Model.Table table in tables)
{
database.AddTable(table);
}
ds = dbHelper.ExecuteDataset(CommandType.Text,
GetSql(database.Type, SchemaHelper.SchemaType.View), null);
List <Model.Table> views = GetSQLTableList(ds);
foreach (Model.Table view in views)
{
database.AddView(view);
}
ds = dbHelper.ExecuteDataset(CommandType.Text, SqlForGetStoreProcedures, null);
database.StoreProcedures = GetSQLStoreProcedureList(ds);
return(database);
}
public Model.Database GetSchema(string connectionString, Model.DatabaseTypes type)
{
Model.Database database = new Model.Database();
database.ConnectionString = connectionString;
database.Type = type;
//得到获取MySql结构的语句
string connStr = database.ConnectionString;
Match mDatabase = Regex.Match(connStr, @"Database=(?<Database>[^\;]*);");
if (mDatabase.Success)
{
database.Name = mDatabase.Groups["Database"].Value; //已赋数据库名
connStr = connStr.Replace(string.Format("Database={0};", database.Name), "Database=information_schema;");
}
else
{
return(null);
}
dbHelper = new DBUtility.DBHelper(DBUtility.DBHelper.DatabaseTypes.MySql, connStr);
GetTables(database);
GetViews(database);
GetStoreProcedures(database);
return(database);
}
public Model.Database GetSchema(string connectionString, Model.DatabaseTypes type)
{
Model.Database db = new Model.Database();
db.ConnectionString = connectionString;
db.Type = type;
helper = new DBUtility.DBHelper(DBUtility.DBHelper.DatabaseTypes.Oracle, connectionString);
GetTables(db);
GetViews(db);
GetProcedures(db);
return(db);
}
public Model.Database GetSchema(string connectionString, Model.DatabaseTypes type)
{
Model.Database db = new Model.Database();
db.ConnString = connectionString;
db.Type = type;
helper = DBUtility.DbHelper.Create("Oracle");
helper.ConnectionString = connectionString;
GetTables(db);
GetViews(db);
GetProcedures(db);
return(db);
}
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);
}
/// <summary>
/// 取得表结构的SQL语句
/// </summary>
private string GetSql(Model.DatabaseTypes type, SchemaHelper.SchemaType schemaType)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT d.name N'TableName',a.colorder N'FieldNumber',a.name N'FieldName', ");
strSql.Append("(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end) N'IsId',");
strSql.Append("(case when (SELECT count(*) ");
strSql.Append(" FROM sysobjects WHERE (name in (SELECT name FROM sysindexes ");
strSql.Append(" WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys");
strSql.Append(" WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns");
strSql.Append(" WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 ");
strSql.Append(" then '1' else '0' end) N'IsKey', b.name N'FieldType',a.length N'FieldSize', ");
strSql.Append(" COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'FieldLength', ");
strSql.Append(" isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'DecimalDigits', ");
strSql.Append(" (case when a.isnullable=1 then '1'else '0' end) N'AllowNull', isnull(e.text,'') N'DefaultValue', ");
strSql.Append(" isnull(g.[value],'') AS N'FieldDescn' ");
strSql.Append(" FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id");
switch (schemaType)
{
case SchemaHelper.SchemaType.View:
strSql.Append(" and d.xtype='V'");
break;
case SchemaHelper.SchemaType.Table:
default:
strSql.Append(" and d.xtype='U'");
break;
}
strSql.Append("and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id");
if (type == Model.DatabaseTypes.Sql2005)
{
strSql.Append(" left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id order by object_name(a.id),a.colorder");
}
else
{
strSql.Append(" left join sysproperties g on a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorder ");
}
return(strSql.ToString());
}
public Schema(string connectionString, Model.DatabaseTypes type)
{
this.connectionString = connectionString;
Type = type;
}
