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