/// <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); } }
public static StiDataTableSchema NewView(string name, StiSqlDataConnector connector = null, string query = null) { return(new StiDataTableSchema(name, query ?? StiTableQuery.Get(connector).GetSelectQuery(name))); }
public static StiDataTableSchema NewProcedure(string name, StiSqlDataConnector connector = null, string query = null) { return(new StiDataTableSchema(name, query ?? StiTableQuery.Get(connector).GetExecuteQuery(name))); }
/// <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 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(); #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(); 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 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); } }