/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { var database = GetDatabase(); #region Collections try { //var collectionNames = database.GetCollectionNames(); var collectionNames = database.GetType().GetMethod("GetCollectionNames").Invoke(database, null) as IEnumerable <string>; foreach (var collectionName in collectionNames) { if (collectionName == "system.indexes") { continue; } if (collectionName == "system.users") { continue; } var tableSchema = StiDataTableSchema.NewTable(collectionName); try { var columns = GetColumns(collectionName); if (columns != null) { tableSchema.Columns = columns; } } catch { } schema.Tables.Add(tableSchema); } } catch { } #endregion return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); #region Tables var tableHash = new Hashtable(); try { var tables = connection.GetSchema("Tables"); foreach (var row in StiSchemaRow.All(tables)) { if (row.TABLE_SCHEMA == "sys") { continue; } var table = StiDataTableSchema.NewTableOrView(row.TABLE_NAME); tableHash[table.Name] = table; if (row.TABLE_TYPE == "VIEW") { schema.Views.Add(table); } if (row.TABLE_TYPE == "TABLE") { schema.Tables.Add(table); } } } catch { } #endregion #region Columns try { var columns = connection.GetSchema("Columns"); foreach (var row in StiSchemaRow.All(columns)) { if (row.TABLE_SCHEMA == "sys") { continue; } var column = new StiDataColumnSchema(row.COLUMN_NAME, GetNetType(row.DATA_TYPE_INT)); var table = tableHash[row.TABLE_NAME] as StiDataTableSchema; if (table != null) { table.Columns.Add(column); } } } catch { } #endregion #region Procedures var procedureHash = new Hashtable(); try { var procedures = connection.GetSchema("Procedures"); foreach (var row in StiSchemaRow.All(procedures)) { if (row.PROCEDURE_SCHEMA == "sys") { continue; } var procName = row.PROCEDURE_NAME; if (procName.IndexOf(";", StringComparison.InvariantCulture) != -1) { procName = procName.Substring(0, procName.IndexOf(";", StringComparison.InvariantCulture)); } var procedure = StiDataTableSchema.NewProcedure(procName); procedureHash[procedure.Name] = procedure; schema.StoredProcedures.Add(procedure); } } catch { } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); #region Tables var tables = new DataTable("Tables"); try { using (var adapter = this.CreateAdapter("SELECT id, name FROM sysobjects where type = 'U'", connection)) { adapter.Fill(tables); foreach (DataRow row in tables.Rows) { var table = StiDataTableSchema.NewTable(row["name"] as string, this); var columns = new DataTable("Columns"); using (var columnAdapter = this.CreateAdapter("SELECT name, type FROM syscolumns where id = " + row["id"], connection)) { columnAdapter.Fill(columns); foreach (DataRow rowColumns in columns.Rows) { var typeColumn = this.GetNetType((byte)rowColumns["type"]); table.Columns.Add(new StiDataColumnSchema(rowColumns["name"].ToString(), typeColumn)); } } schema.Tables.Add(table); } } } catch { } #endregion #region Views var views = new DataTable("Views"); try { using (var adapter = this.CreateAdapter("SELECT name FROM sysobjects where type = 'V'", connection)) { adapter.Fill(views); foreach (DataRow row in views.Rows) { var view = StiDataTableSchema.NewView(row[0] as string, this); var columns = new DataTable("Columns"); using (var columnAdapter = this.CreateAdapter("SELECT name, type FROM syscolumns where id = " + row["id"], connection)) { columnAdapter.Fill(columns); foreach (DataRow rowColumns in columns.Rows) { var typeColumn = this.GetNetType((byte)rowColumns["type"]); view.Columns.Add(new StiDataColumnSchema(rowColumns["name"].ToString(), typeColumn)); } } schema.Views.Add(view); } } } catch { } #endregion #region Procedures var procedures = new DataTable("Procedures"); try { using (var adapter = CreateAdapter("SELECT name FROM sysobjects where type = 'P'", connection)) { adapter.Fill(procedures); foreach (DataRow row in procedures.Rows) { var procedure = StiDataTableSchema.NewProcedure(row[0] as string, this); schema.StoredProcedures.Add(procedure); } } } catch { } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); #region Tables var tableHash = new Hashtable(); try { var tables = connection.GetSchema("Tables"); foreach (var row in StiSchemaRow.All(tables)) { if (row.TABLE_SCHEMA == "sys" || row.TABLE_SCHEMA == "INFORMATION_SCHEMA") { continue; } var tableName = StiTableName.GetName(row.TABLE_SCHEMA != "dbo" ? row.TABLE_SCHEMA : null, row.TABLE_NAME); var query = StiTableQuery.Get(this).GetSelectQuery(row.TABLE_SCHEMA != "dbo" ? row.TABLE_SCHEMA : null, row.TABLE_NAME); var table = StiDataTableSchema.NewTableOrView(tableName, this, query); tableHash[table.Name] = table; if (row.TABLE_TYPE == "VIEW") { schema.Views.Add(table); } if (row.TABLE_TYPE == "TABLE") { schema.Tables.Add(table); } } } catch { } #endregion #region Columns try { var columns = connection.GetSchema("Columns"); if (AdvancedRetrievalModeOfDatabaseSchema) { var tables = connection.GetSchema("Tables"); foreach (var row in StiSchemaRow.All(tables)) { if (row.TABLE_SCHEMA == "sys") { continue; } if (row.TABLE_TYPE == null || (row.TABLE_TYPE != "TABLE" && row.TABLE_TYPE != "VIEW")) { continue; } var query = StiTableQuery.Get(this).GetSelectQuery(row.TABLE_SCHEMA != "dbo" ? row.TABLE_SCHEMA : null, row.TABLE_NAME); using (var command = CreateCommand(query, connection)) using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly)) using (var table = reader.GetSchemaTable()) { foreach (DataRow rowTable in table.Rows) { var column = new StiDataColumnSchema(rowTable["COLUMNNAME"].ToString(), Type.GetType(rowTable["DATATYPE"].ToString())); var tableSchema = tableHash[row.TABLE_NAME] as StiDataTableSchema; if (table != null && !table.Columns.Contains(rowTable["ColumnName"].ToString())) { tableSchema.Columns.Add(column); } } } } } else { foreach (var row in StiSchemaRow.All(columns)) { if (row.TABLE_SCHEMA == "sys") { continue; } var columnType = GetNetType(row.DATA_TYPE_INT); var tableName = StiTableName.GetName(row.TABLE_SCHEMA != "dbo" ? row.TABLE_SCHEMA : null, row.TABLE_NAME); var column = new StiDataColumnSchema(row.COLUMN_NAME, columnType); var table = tableHash[tableName] as StiDataTableSchema; if (table != null && table.Columns.All(c => c.Name != row.COLUMN_NAME)) { table.Columns.Add(column); } } } } catch { } #endregion #region Procedures var procedureHash = new Hashtable(); try { var procedures = connection.GetSchema("Procedures"); foreach (var row in StiSchemaRow.All(procedures)) { if (row.PROCEDURE_SCHEMA == "sys") { continue; } var baseName = row.PROCEDURE_NAME; if (baseName.IndexOf(";", StringComparison.InvariantCulture) != -1) { baseName = baseName.Substring(0, baseName.IndexOf(";", StringComparison.InvariantCulture)); } var procName = StiTableName.GetName(row.PROCEDURE_SCHEMA != "dbo" ? row.PROCEDURE_SCHEMA : null, baseName); var query = StiTableQuery.Get(this).GetProcQuery(row.PROCEDURE_SCHEMA != "dbo" ? row.PROCEDURE_SCHEMA : null, baseName); var procedure = StiDataTableSchema.NewProcedure(procName, this, query); procedureHash[procedure.Name] = procedure; schema.StoredProcedures.Add(procedure); } } catch { } #endregion #region Procedures Parameters and Columns foreach (var procedure in schema.StoredProcedures) { try { using (var command = CreateCommand(procedure.Query, connection, CommandType.StoredProcedure)) { DeriveParameters(command); using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly)) using (var table = new DataTable(procedure.Name)) { table.Load(reader); foreach (DataColumn column in table.Columns) { procedure.Columns.Add(new StiDataColumnSchema { Name = column.ColumnName, Type = column.DataType }); } foreach (DbParameter param in command.Parameters) { if (param.Direction == ParameterDirection.Input) { procedure.Parameters.Add(new StiDataParameterSchema { Name = param.ParameterName, Type = StiDbTypeConversion.GetNetType(param.DbType) }); } } } } } catch { } } #endregion #region Relations try { foreach (var schemaTable in schema.Tables) { var relations = GetRelationsTable(connection, schemaTable.Name); if (relations == null) { continue; } foreach (var row in StiSchemaRow.All(relations)) { schema.Relations.Add(new StiDataRelationSchema { Name = row.FK_NAME, ParentSourceName = row.PK_TABLE_NAME, ChildSourceName = row.FK_TABLE_NAME, ParentColumns = new List <string> { row.PK_COLUMN_NAME }, ChildColumns = new List <string> { row.FK_COLUMN_NAME } }); } } } catch { } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); var restrictionsArray = new[] { connection.Database }; var tables = connection.GetSchema("Tables", restrictionsArray); var tableHash = new Hashtable(); try { foreach (var row in StiSchemaRow.All(tables)) { var tableName = string.Format("{0}.{1}", row.TABLE_SCHEMA, row.TABLE_NAME); var table = StiDataTableSchema.NewTableOrView(tableName); tableHash[tableName] = table; if (row.TABLE_TYPE == "TABLE") { schema.Tables.Add(table); } if (row.TABLE_TYPE == "VIEW") { schema.Views.Add(table); } } } catch { } #region Columns try { var colRestrictionsArray = new[] { connection.Database }; var columns = connection.GetSchema("Columns", colRestrictionsArray); foreach (var row in StiSchemaRow.All(columns)) { var tableName = string.Format("{0}.{1}", row.TABLE_SCHEMA, row.TABLE_NAME); if (tableHash[tableName] == null) { continue; } var column = new DataColumn(row.COLUMN_NAME, GetNetType(row.COLUMN_TYPE)); var table = tableHash[tableName] as DataTable; if (table != null) { table.Columns.Add(column); } } } catch { } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); #region Tables var tableList = new List <StiDataTableSchema>(); try { var tables = connection.GetSchema("Tables"); foreach (var row in StiSchemaRow.All(tables)) { var tableSchema = StiDataTableSchema.NewTable(row.TABLE_NAME); tableList.Add(tableSchema); schema.Tables.Add(tableSchema); } } catch { } #endregion #region Views try { var views = connection.GetSchema("Views"); foreach (var row in StiSchemaRow.All(views)) { var viewSchema = StiDataTableSchema.NewView(row.TABLE_NAME); tableList.Add(viewSchema); schema.Views.Add(viewSchema); } } catch { } #endregion #region Columns try { var columns = connection.GetSchema("Columns"); foreach (var table in tableList) { foreach (var row in StiSchemaRow.All(columns)) { if (row.TABLE_NAME == null || row.TABLE_NAME != table.Name) { continue; } var column = new StiDataColumnSchema(row.COLUMN_NAME, GetNetType(row.DATA_TYPE)); table.Columns.Add(column); } } } catch { } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); #region Tables var tables = connection.GetSchema("Tables"); var tableHash = new Hashtable(); try { foreach (var row in StiSchemaRow.All(tables)) { if (row.TYPE == "System" || IsSystemOwner(row.OWNER)) { continue; } var tableName = StiTableName.GetName(row.OWNER, row.TABLE_NAME); var query = StiTableQuery.Get(this).GetSelectQuery(row.OWNER, row.TABLE_NAME); var table = StiDataTableSchema.NewTableOrView(tableName, this, query); tableHash[table.Name] = table; schema.Tables.Add(table); } } catch { } #endregion #region Views var views = connection.GetSchema("Views"); try { foreach (var row in StiSchemaRow.All(views)) { if (IsSystemOwner(row.OWNER)) { continue; } var viewName = StiTableName.GetName(row.OWNER, row.TABLE_NAME); var query = StiTableQuery.Get(this).GetSelectQuery(row.OWNER, row.TABLE_NAME); var view = StiDataTableSchema.NewTableOrView(viewName, this, query); tableHash[view.Name] = view; schema.Views.Add(view); } } catch { } #endregion #region Columns try { var columns = connection.GetSchema("Columns"); foreach (var row in StiSchemaRow.All(columns)) { if (IsSystemOwner(row.OWNER)) { continue; } var tableName = StiTableName.GetName(row.OWNER, row.TABLE_NAME); var column = new StiDataColumnSchema(row.COLUMN_NAME, GetNetType(row.DATATYPE)); var table = tableHash[tableName] as StiDataTableSchema; if (table != null) { table.Columns.Add(column); } } } catch { } #endregion #region Procedures var procedures = connection.GetSchema("Procedures"); var procedureHash = new Hashtable(); try { foreach (var row in StiSchemaRow.All(procedures)) { if ((row.OWNER == "SYS") || IsSystemOwner(row.OWNER)) { continue; } var procName = StiTableName.GetName(row.OWNER, row.OBJECT_NAME); var query = StiTableQuery.Get(this).GetProcQuery(row.OWNER, row.OBJECT_NAME); var proc = StiDataTableSchema.NewProcedure(procName, this, query); procedureHash[proc.Name] = proc; schema.StoredProcedures.Add(proc); } } catch { } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); #region Tables var tableList = new List <StiDataTableSchema>(); try { var tables = connection.GetSchema("Tables", new[] { connection.Database, null }); foreach (var row in StiSchemaRow.All(tables)) { var tableSchema = StiDataTableSchema.NewTable(row.NAME, this); tableList.Add(tableSchema); schema.Tables.Add(tableSchema); } } catch { } #endregion #region Views try { var views = connection.GetSchema("Views", new[] { connection.Database, null }); foreach (var row in StiSchemaRow.All(views)) { var viewSchema = StiDataTableSchema.NewView(row.NAME, this); tableList.Add(viewSchema); schema.Views.Add(viewSchema); } } catch { } #endregion #region Columns try { foreach (var table in tableList) { var columns = connection.GetSchema("Columns", new[] { connection.Database, table.Name, null }); foreach (var row in StiSchemaRow.All(columns)) { var column = new StiDataColumnSchema(row.NAME, GetNetType(row.DATATYPE)); table.Columns.Add(column); } } } catch { } #endregion #region Procedures var procedureHash = new Hashtable(); try { var procedures = connection.GetSchema("Procedures", new[] { connection.Database, null }); foreach (var row in StiSchemaRow.All(procedures)) { var procedure = StiDataTableSchema.NewProcedure(row.NAME); procedureHash[procedure.Name] = procedure; schema.StoredProcedures.Add(procedure); } } catch { } foreach (var procedure in schema.StoredProcedures) { try { using (var command = CreateCommand(procedure.Name, connection, CommandType.StoredProcedure)) { DeriveParameters(command); using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly)) using (var table = new DataTable(procedure.Name)) { table.Load(reader); foreach (DataColumn column in table.Columns) { procedure.Columns.Add(new StiDataColumnSchema { Name = column.ColumnName, Type = column.DataType }); } if (command.Parameters.Count > 0) { var paramStr = new StringBuilder(); foreach (DbParameter param in command.Parameters) { if (param.Direction == ParameterDirection.Input) { procedure.Parameters.Add(new StiDataParameterSchema { Name = param.ParameterName, Type = StiDbTypeConversion.GetNetType(param.DbType) }); paramStr = paramStr.Length == 0 ? paramStr.Append(param.ParameterName) : paramStr.AppendFormat("{0},", param.ParameterName); } } if (paramStr.Length > 0) { procedure.Query = string.Format("{0} ({1})", procedure.Query, paramStr); } } } } } catch { } } #endregion #region Relations try { foreach (var schemaTable in schema.Tables) { using (var dataSet = new DataSet()) { var commandText = "SELECT * FROM information_schema.TABLE_CONSTRAINTS i " + "LEFT JOIN information_schema.KEY_COLUMN_USAGE k " + "ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME " + "WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' and i.TABLE_NAME = '{0}'"; using (var adapter = CreateAdapter(string.Format(commandText, schemaTable.Name), connection)) { adapter.Fill(dataSet, schemaTable.Name); var dataTable = (dataSet.Tables.Count > 0) ? dataSet.Tables[0] : null; if (dataTable != null) { foreach (var row in StiSchemaRow.All(dataTable)) { schema.Relations.Add(new StiDataRelationSchema { Name = row.CONSTRAINT_NAME, ParentSourceName = row.TABLE_NAME, ChildSourceName = row.REFERENCED_TABLE_NAME, ParentColumns = new List <string> { row.COLUMN_NAME }, ChildColumns = new List <string> { row.REFERENCED_COLUMN_NAME } }); } } } } } } catch { } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); #region Tables & Views var tableHash = new Hashtable(); try { var tables = connection.GetSchema("Tables"); foreach (var row in StiSchemaRow.All(tables)) { if (row.TABLE_SCHEMA == "sys") { continue; } var tableName = StiTableName.GetName(row.TABLE_SCHEMA != "dbo" ? row.TABLE_SCHEMA : null, row.TABLE_NAME); var query = StiTableQuery.Get(this).GetSelectQuery(row.TABLE_SCHEMA != "dbo" ? row.TABLE_SCHEMA : null, row.TABLE_NAME); var tableSchema = StiDataTableSchema.NewTableOrView(tableName, this, query); tableHash[tableName] = tableSchema; if (row.TABLE_TYPE == "BASE TABLE") { schema.Tables.Add(tableSchema); } if (row.TABLE_TYPE == "VIEW") { schema.Views.Add(tableSchema); } } } catch { } #endregion #region Columns try { var columns = connection.GetSchema("Columns"); foreach (var row in StiSchemaRow.All(columns)) { if (row.TABLE_SCHEMA == "sys") { continue; } var tableName = StiTableName.GetName(row.TABLE_SCHEMA != "dbo" ? row.TABLE_SCHEMA : null, row.TABLE_NAME); var column = new StiDataColumnSchema(row.COLUMN_NAME, GetNetType(row.DATA_TYPE)); var table = tableHash[tableName] as StiDataTableSchema; if (table != null) { table.Columns.Add(column); } } } catch { } #endregion #region Procedures try { var procedures = connection.GetSchema("Procedures"); var connectionDatabase = connection.Database != null?connection.Database.ToUpperInvariant() : null; foreach (var row in StiSchemaRow.All(procedures)) { var rowROUTINE_CATALOG = row.ROUTINE_CATALOG != null?row.ROUTINE_CATALOG.ToUpperInvariant() : null; if (row.SPECIFIC_SCHEMA == "sys" || row.ROUTINE_TYPE != "PROCEDURE" || rowROUTINE_CATALOG != connectionDatabase) { continue; } var procName = StiTableName.GetName(row.SPECIFIC_SCHEMA != "dbo" ? row.SPECIFIC_SCHEMA : null, row.SPECIFIC_NAME); var query = StiTableQuery.Get(this).GetProcQuery(row.SPECIFIC_SCHEMA != "dbo" ? row.SPECIFIC_SCHEMA : null, row.SPECIFIC_NAME); var procedure = StiDataTableSchema.NewProcedure(procName, this, query); schema.StoredProcedures.Add(procedure); } } catch { } #endregion #region Procedures Parameters and Columns try { var queryGetParams = "select obj.name as procName, params.*, type_name(system_type_id) as type_name from sys.parameters params, sys.objects obj" + " where params.object_id = obj.object_id"; using (var commandGetParams = CreateCommand(queryGetParams, connection, CommandType.Text)) { using (var reader = commandGetParams.ExecuteReader()) { using (var table = new DataTable("Parameters")) { table.Load(reader); foreach (var procedure in schema.StoredProcedures) { #region Fill Parameters foreach (DataRow row in table.Rows) { var typeConverter = new StiMsSqlConnector(); if (procedure.Name == row["procName"].ToString()) { procedure.Parameters.Add(new StiDataParameterSchema { Name = row["name"].ToString(), Type = GetNetType(row["type_name"].ToString()) }); } } #endregion } } } } #region Fill Columns if (StiDataOptions.WizardStoredProcRetriveMode == StiWizardStoredProcRetriveMode.All) { try { foreach (var procedure in schema.StoredProcedures) { using (var command = CreateCommand(procedure.Query, connection, CommandType.StoredProcedure)) { DeriveParameters(command); using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly)) using (var table = new DataTable(procedure.Name)) { table.Load(reader); foreach (DataColumn column in table.Columns) { procedure.Columns.Add(new StiDataColumnSchema { Name = column.ColumnName, Type = column.DataType }); } } } } } catch { } } #endregion } catch { } #endregion #region Relations try { const string commandText = @" SELECT KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME' , KCU1.TABLE_NAME AS 'FK_TABLE_NAME' , KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME' , KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION' , KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME' , KCU2.TABLE_NAME AS 'UQ_TABLE_NAME' , KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME' , KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION' FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION"; using (var dataSet = new DataSet()) using (var adapter = CreateAdapter(commandText, connection)) { adapter.Fill(dataSet); var dataTable = (dataSet.Tables.Count > 0) ? dataSet.Tables[0] : null; if (dataTable != null) { foreach (var row in StiSchemaRow.All(dataTable)) { schema.Relations.Add(new StiDataRelationSchema { Name = row.FK_CONSTRAINT_NAME, ParentSourceName = row.UQ_TABLE_NAME, ChildSourceName = row.FK_TABLE_NAME, ParentColumns = new List <string> { row.UQ_COLUMN_NAME }, ChildColumns = new List <string> { row.FK_COLUMN_NAME } }); } } } } catch { } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); #region Tables var tableHash = new Hashtable(); try { var tables = connection.GetSchema("Tables"); foreach (var row in StiSchemaRow.All(tables)) { if (row.TABLE_SCHEM == "sys" || row.TABLE_TYPE != "TABLE" || row.TABLE_SCHEM == "INFORMATION_SCHEMA") { continue; } var tableName = StiTableName.GetName(row.TABLE_SCHEM != "dbo" ? row.TABLE_SCHEM : null, row.TABLE_NAME); var query = StiTableQuery.Get(this).GetSelectQuery(row.TABLE_SCHEM != "dbo" ? row.TABLE_SCHEM : null, row.TABLE_NAME); var tableSchema = StiDataTableSchema.NewTableOrView(tableName, this, query); tableHash[tableSchema.Name] = tableSchema; schema.Tables.Add(tableSchema); } } catch { } #endregion #region Views try { var views = connection.GetSchema("Views"); foreach (var row in StiSchemaRow.All(views)) { if (row.TABLE_SCHEM == "sys" || row.TABLE_TYPE != "VIEW" || row.TABLE_SCHEM == "INFORMATION_SCHEMA") { continue; } var tableName = StiTableName.GetName(row.TABLE_SCHEM != "dbo" ? row.TABLE_SCHEM : null, row.TABLE_NAME); var query = StiTableQuery.Get(this).GetSelectQuery(row.TABLE_SCHEM != "dbo" ? row.TABLE_SCHEM : null, row.TABLE_NAME); var tableSchema = StiDataTableSchema.NewTableOrView(tableName, this, query); tableHash[tableSchema.Name] = tableSchema; schema.Views.Add(tableSchema); } } catch { } #endregion #region Columns try { if (AdvancedRetrievalModeOfDatabaseSchema) { var tables = connection.GetSchema("Tables"); foreach (var row in StiSchemaRow.All(tables)) { if (row.TABLE_SCHEM == "sys") { continue; } if (row.TABLE_TYPE != "TABLE") { continue; } var query = StiTableQuery.Get(this).GetSelectQuery(row.TABLE_SCHEM != "dbo" ? row.TABLE_SCHEM : null, row.TABLE_NAME); using (var command = CreateCommand(query, connection)) using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly)) using (var table = reader.GetSchemaTable()) { foreach (var rowTable in StiSchemaRow.All(table)) { var columnSchema = new StiDataColumnSchema(rowTable.COLUMNNAME, Type.GetType(rowTable.DATATYPE)); var tableName = StiTableName.GetName(row.TABLE_SCHEM != "dbo" ? row.TABLE_SCHEM : null, row.TABLE_NAME); var tableSchema = tableHash[tableName] as StiDataTableSchema; if (tableSchema != null && !tableSchema.Columns.Exists(t => t.Name == rowTable.COLUMNNAME)) { tableSchema.Columns.Add(columnSchema); } } } } } else { var columns = connection.GetSchema("Columns"); foreach (var row in StiSchemaRow.All(columns)) { if (row.TABLE_SCHEM == "sys") { continue; } var column = new StiDataColumnSchema(row.COLUMN_NAME, GetNetType(row.TYPE_NAME)); var tableName = StiTableName.GetName(row.TABLE_SCHEM != "dbo" ? row.TABLE_SCHEM : null, row.TABLE_NAME); var table = tableHash[tableName] as StiDataTableSchema; if (table != null) { table.Columns.Add(column); } } } } catch { } #endregion #region Procedures try { var procedures = connection.GetSchema("Procedures"); var procedureHash = new Hashtable(); foreach (var row in StiSchemaRow.All(procedures)) { if (row.PROCEDURE_SCHEM == "sys") { continue; } if (row.PROCEDURE_CAT == null || row.PROCEDURE_CAT != connection.Database) { continue; } var baseName = row.PROCEDURE_NAME; if (baseName.IndexOf(";", StringComparison.InvariantCulture) != -1) { baseName = baseName.Substring(0, baseName.IndexOf(";", StringComparison.InvariantCulture)); } var procName = StiTableName.GetName(row.PROCEDURE_SCHEM != "dbo" ? row.PROCEDURE_SCHEM : null, baseName); var query = StiTableQuery.Get(this).GetCallQuery(row.PROCEDURE_SCHEM != "dbo" ? row.PROCEDURE_SCHEM : null, baseName); var procedure = StiDataTableSchema.NewProcedure(procName, this, query); procedureHash[procedure.Name] = procedure; schema.StoredProcedures.Add(procedure); } } catch { } #endregion #region Procedures Parameters and Columns foreach (var procedure in schema.StoredProcedures) { try { using (var command = CreateCommand(procedure.Name, connection, CommandType.StoredProcedure)) { DeriveParameters(command); if (command.Parameters != null && command.Parameters.Count > 0) { if (command.Parameters[0].Direction == ParameterDirection.ReturnValue) { command.Parameters.RemoveAt(0); } var pars = new List <string>(); var count = command.Parameters.Count; while (count > 0) { pars.Add("?"); count--; } procedure.Query = string.Format("{{{0}({1})}}", procedure.Query, string.Join(",", pars)); } else { procedure.Query = string.Format("{{{0}()}}", procedure.Query); } command.CommandText = procedure.Query; using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly)) using (var table = new DataTable(procedure.Name)) { table.Load(reader); foreach (DataColumn column in table.Columns) { procedure.Columns.Add(new StiDataColumnSchema { Name = column.ColumnName, Type = column.DataType }); } foreach (DbParameter param in command.Parameters) { if (param.Direction == ParameterDirection.Input) { procedure.Parameters.Add(new StiDataParameterSchema { Name = param.ParameterName, Type = StiDbTypeConversion.GetNetType(param.DbType) }); } } } } } catch { } } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); var connectionDatabase = connection.Database != null?connection.Database.ToLowerInvariant() : null; #region Tables var tableHash = new Hashtable(); try { var tables = connection.GetSchema("Tables"); foreach (var row in StiSchemaRow.All(tables)) { if (row.TABLE_SCHEMA == null || row.TABLE_SCHEMA.ToLowerInvariant() != connectionDatabase || row.TABLE_TYPE != "BASE TABLE") { continue; } var table = StiDataTableSchema.NewTable(row.TABLE_NAME); tableHash[table.Name] = table; schema.Tables.Add(table); } } catch { } #endregion #region Views try { var views = connection.GetSchema("Views"); foreach (var row in StiSchemaRow.All(views)) { if (row.TABLE_SCHEMA == null || row.TABLE_SCHEMA.ToLowerInvariant() != connectionDatabase) { continue; } var view = StiDataTableSchema.NewView(row.TABLE_NAME); tableHash[view.Name] = view; schema.Views.Add(view); } } catch { } #endregion #region Columns try { var columns = connection.GetSchema("Columns"); foreach (var row in StiSchemaRow.All(columns)) { if (row.TABLE_SCHEMA == null || row.TABLE_SCHEMA.ToLowerInvariant() != connectionDatabase) { continue; } var column = new StiDataColumnSchema(row.COLUMN_NAME, GetNetType(row.DATA_TYPE)); var table = tableHash[row.TABLE_NAME] as StiDataTableSchema; if (table != null) { table.Columns.Add(column); } } } catch { } #endregion #region Procedures var procedureHash = new Hashtable(); try { var procedures = connection.GetSchema("Procedures"); foreach (var row in StiSchemaRow.All(procedures)) { var procName = row.ROUTINE_NAME; if (row.ROUTINE_SCHEMA == null || row.ROUTINE_SCHEMA.ToLowerInvariant() != connectionDatabase || procName == null) { continue; } if (procName.IndexOf(";", StringComparison.InvariantCulture) != -1) { procName = procName.Substring(0, procName.IndexOf(";", StringComparison.InvariantCulture)); } var procedure = StiDataTableSchema.NewProcedure(procName); procedure.Query = StiTableQuery.Get(this).GetProcQuery(procName); procedureHash[procedure.Name] = procedure; schema.StoredProcedures.Add(procedure); } } catch { } #endregion #region Procedures Parameters and Columns foreach (var procedure in schema.StoredProcedures) { try { using (var command = CreateCommand(procedure.Name, connection, CommandType.StoredProcedure)) { DeriveParameters(command); using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly)) using (var table = new DataTable(procedure.Name)) { table.Load(reader); foreach (DataColumn column in table.Columns) { procedure.Columns.Add(new StiDataColumnSchema { Name = column.ColumnName, Type = column.DataType }); } if (command.Parameters.Count > 0) { var paramStr = new StringBuilder(); foreach (DbParameter param in command.Parameters) { if (param.Direction == ParameterDirection.Input) { procedure.Parameters.Add(new StiDataParameterSchema { Name = param.ParameterName, Type = StiDbTypeConversion.GetNetType(param.DbType) }); paramStr = paramStr.Length == 0 ? paramStr.Append(param.ParameterName) : paramStr.AppendFormat("{0},", param.ParameterName); } } } } } } catch { } } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); string[] restrictions = null; var provider = connection.GetType().GetProperty("Provider").GetValue(connection, null) as string; if (provider == "MySQL") { restrictions = new[] { connection.Database }; } else if (provider == "Oracle" || provider == "OracleClient") { var userId = connection.GetType().GetProperty("UserId").GetValue(connection, null) as string; if (userId != null) { restrictions = new[] { userId.ToUpper() } } ; } else { restrictions = new string[] { } }; #region Tables var tableHash = new Hashtable(); try { var tables = connection.GetSchema("Tables", restrictions); var columnName = "TABLE_NAME"; foreach (DataRow row in tables.Rows) { var table = StiDataTableSchema.NewTable(row[columnName] as string, this); if (row["TABLE_SCHEMA"] != DBNull.Value && ((string)row["TABLE_SCHEMA"]) != "dbo") { table = StiDataTableSchema.NewTable(string.Format("{0} ({1})", row["TABLE_NAME"] as string, row["TABLE_SCHEMA"] as string), this); } tableHash[table.Name] = table; schema.Tables.Add(table); } } catch { } #endregion #region Columns try { var columns = connection.GetSchema("Columns"); foreach (DataRow row in columns.Rows) { var columnName = row["COLUMN_NAME"] as string; string tableName; if (row["TABLE_SCHEMA"] != DBNull.Value && ((string)row["TABLE_SCHEMA"]) != "sys" && ((string)row["TABLE_SCHEMA"]) != "dbo") { tableName = string.Format("{0} ({1})", row["TABLE_NAME"] as string, row["TABLE_SCHEMA"] as string); } else { tableName = row["TABLE_NAME"] as string; } var columnType = GetNetType(row["DATA_TYPE"] as string); var column = new StiDataColumnSchema(columnName, columnType); var table = tableHash[tableName] as StiDataTableSchema; if (table != null) { table.Columns.Add(column); } } } catch { } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); #region Tables var tables = connection.GetSchema("Tables"); var tableHash = new Hashtable(); try { foreach (var row in StiSchemaRow.All(tables)) { if (row.TABLE_TYPE == null || row.TABLE_TYPE == "System") { continue; } var table = StiDataTableSchema.NewTable(row.TABLE_NAME); tableHash[table.Name] = table; schema.Tables.Add(table); } } catch { } #endregion #region Views var views = connection.GetSchema("Views"); var viewHash = new Hashtable(); try { foreach (var row in StiSchemaRow.All(views)) { var table = StiDataTableSchema.NewView(row.TABLE_NAME); viewHash[table.Name] = table; schema.Views.Add(table); } } catch { } #endregion #region Columns try { var columns = connection.GetSchema("Columns"); foreach (var row in StiSchemaRow.All(columns)) { if (tableHash[row.TABLE_NAME] == null) { continue; } var column = new StiDataColumnSchema(row.COLUMN_NAME, GetNetType(row.DATA_TYPE)); var table = tableHash[row.TABLE_NAME] as StiDataTableSchema; if (table != null) { table.Columns.Add(column); } } } catch { } #endregion #region Procedures var procedures = connection.GetSchema("Procedures"); var procedureHash = new Hashtable(); try { foreach (var row in StiSchemaRow.All(procedures)) { var table = StiDataTableSchema.NewProcedure(row.SPECIFIC_NAME); procedureHash[table.Name] = table; schema.StoredProcedures.Add(table); } } catch { } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); string dbSchemaName = null; GetConvertedConnectionStringToDotConnect(this.ConnectionString, out dbSchemaName); #region Tables var tableList = new List <StiDataTableSchema>(); try { var tables = connection.GetSchema("Tables", new[] { dbSchemaName, null }); foreach (var row in StiSchemaRow.All(tables)) { var tableName = StiTableName.GetName(row.OWNER, row.TABLE_NAME); var query = StiTableQuery.Get(this).GetSelectQuery(row.OWNER, row.TABLE_NAME); var table = StiDataTableSchema.NewTableOrView(tableName, this, query); tableList.Add(table); schema.Tables.Add(table); } } catch { } #endregion #region Views try { var views = connection.GetSchema("Views", new[] { dbSchemaName, null }); foreach (var row in StiSchemaRow.All(views)) { var viewName = StiTableName.GetName(row.OWNER, row.TABLE_NAME); var query = StiTableQuery.Get(this).GetSelectQuery(row.OWNER, row.TABLE_NAME); var view = StiDataTableSchema.NewTableOrView(viewName, this, query); tableList.Add(view); schema.Views.Add(view); } } catch { } #endregion #region Columns try { foreach (var table in tableList) { var columns = connection.GetSchema("Columns", new[] { dbSchemaName, table.Name, null }); foreach (var row in StiSchemaRow.All(columns)) { var column = new StiDataColumnSchema(row.NAME, GetNetType(row.DATATYPE)); table.Columns.Add(column); } } } catch { } #endregion #region Procedures var procedureHash = new Hashtable(); try { var procedures = connection.GetSchema("Procedures", new[] { dbSchemaName, null }); foreach (var row in StiSchemaRow.All(procedures)) { var procName = StiTableName.GetName(row.OWNER, row.OBJECT_NAME); var query = StiTableQuery.Get(this).GetProcQuery(row.OWNER, row.OBJECT_NAME); var proc = StiDataTableSchema.NewProcedure(procName, this, query); procedureHash[procName] = proc; schema.StoredProcedures.Add(proc); } } catch { } #endregion #region Procedures Parameters and Columns foreach (var procedure in schema.StoredProcedures) { try { using (var command = CreateCommand(procedure.Query, connection, CommandType.StoredProcedure)) { DeriveParameters(command); using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly)) using (var table = new DataTable(procedure.Name)) { table.Load(reader); foreach (DataColumn column in table.Columns) { procedure.Columns.Add(new StiDataColumnSchema { Name = column.ColumnName, Type = column.DataType }); } foreach (DbParameter param in command.Parameters) { if (param.Direction == ParameterDirection.Input) { procedure.Parameters.Add(new StiDataParameterSchema { Name = param.ParameterName, Type = StiDbTypeConversion.GetNetType(param.DbType) }); } } } } } catch { } } #endregion #region Relations try { var commandText = @" SELECT a.table_name, a.column_name, a.constraint_name, c.owner, c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name WHERE c.constraint_type = 'R'"; using (var dataSet = new DataSet()) using (var adapter = CreateAdapter(commandText, connection)) { adapter.Fill(dataSet); var dataTable = (dataSet.Tables.Count > 0) ? dataSet.Tables[0] : null; if (dataTable != null) { foreach (var row in StiSchemaRow.All(dataTable)) { schema.Relations.Add(new StiDataRelationSchema { Name = row.CONSTRAINT_NAME, ParentSourceName = string.Format("{0}.{1}", row.OWNER, row.TABLE_NAME), ChildSourceName = string.Format("{0}.{1}", row.R_OWNER, row.R_TABLE_NAME), ParentColumns = new List <string> { row.COLUMN_NAME }, ChildColumns = new List <string> { row.R_PK } }); } } } } catch { } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }
/// <summary> /// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable). /// </summary> public override StiDataSchema RetrieveSchema() { if (string.IsNullOrEmpty(this.ConnectionString)) { return(null); } var schema = new StiDataSchema(this.ConnectionIdent); try { using (var connection = CreateConnection()) { connection.Open(); var dbName = ExtractDataBaseNameFromConnectionString(); #region Tables & Views var tableHash = new Hashtable(); try { var userTables = connection.GetSchema("Tables", new[] { dbName, "public", null, null }); foreach (var row in StiSchemaRow.All(userTables)) { var table = StiDataTableSchema.NewTableOrView(row.TABLE_NAME); tableHash[table.Name] = table; if (row.TABLE_TYPE == "BASE TABLE") { schema.Tables.Add(table); } if (row.TABLE_TYPE == "VIEW") { schema.Views.Add(table); } } } catch { } #endregion #region Columns try { var columns = connection.GetSchema("Columns", new string[] { dbName, "public", null }); foreach (var row in StiSchemaRow.All(columns)) { var column = new StiDataColumnSchema(row.COLUMN_NAME, GetNetType(row.DATA_TYPE)); var table = tableHash[row.TABLE_NAME] as StiDataTableSchema; if (table != null) { table.Columns.Add(column); } } } catch { } #endregion #region Procedures Parameters and Columns foreach (var procedure in schema.StoredProcedures) { try { using (var command = CreateCommand(procedure.Query, connection, CommandType.StoredProcedure)) { DeriveParameters(command); using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly)) using (var table = new DataTable(procedure.Name)) { table.Load(reader); foreach (DataColumn column in table.Columns) { procedure.Columns.Add(new StiDataColumnSchema(column.ColumnName, column.DataType)); } foreach (DbParameter param in command.Parameters) { if (param.Direction != ParameterDirection.Input) { continue; } procedure.Parameters.Add(new StiDataParameterSchema(param.ParameterName, StiDbTypeConversion.GetNetType(param.DbType))); } } } } catch { } } #endregion #region Relations try { var commandText = @" SELECT tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY'"; using (var dataSet = new DataSet()) using (var adapter = CreateAdapter(commandText, connection)) { adapter.Fill(dataSet); var dataTable = (dataSet.Tables.Count > 0) ? dataSet.Tables[0] : null; if (dataTable != null) { foreach (var row in StiSchemaRow.All(dataTable)) { schema.Relations.Add(new StiDataRelationSchema { Name = row.CONSTRAINT_NAME, ParentSourceName = row.TABLE_NAME, ChildSourceName = row.FOREIGN_TABLE_NAME, ParentColumns = new List <string> { row.COLUMN_NAME }, ChildColumns = new List <string> { row.FOREIGN_COLUMN_NAME } }); } } } } catch { } #endregion connection.Close(); } return(schema.Sort()); } catch { return(null); } }