public static StiSqlDataConnector Get(StiConnectionIdent ident, string connectionString) { switch (ident) { case StiConnectionIdent.Db2DataSource: return(StiDb2Connector.Get(connectionString)); case StiConnectionIdent.FirebirdDataSource: return(StiFirebirdConnector.Get(connectionString)); case StiConnectionIdent.InformixDataSource: return(StiInformixConnector.Get(connectionString)); case StiConnectionIdent.MsAccessDataSource: return(StiMsAccessConnector.Get(connectionString)); case StiConnectionIdent.MsSqlDataSource: return(StiMsSqlConnector.Get(connectionString)); case StiConnectionIdent.MySqlDataSource: return(StiMySqlConnector.Get(connectionString)); case StiConnectionIdent.OdbcDataSource: return(StiOdbcConnector.Get(connectionString)); case StiConnectionIdent.OleDbDataSource: return(StiOleDbConnector.Get(connectionString)); case StiConnectionIdent.OracleDataSource: return(StiOracleConnector.Get(connectionString)); case StiConnectionIdent.PostgreSqlDataSource: return(StiPostgreSqlConnector.Get(connectionString)); case StiConnectionIdent.SqlCeDataSource: return(StiSqlCeConnector.Get(connectionString)); case StiConnectionIdent.SqLiteDataSource: return(StiSqLiteConnector.Get(connectionString)); case StiConnectionIdent.SybaseDataSource: return(StiSybaseConnector.Get(connectionString)); case StiConnectionIdent.TeradataDataSource: return(StiTeradataConnector.Get(connectionString)); case StiConnectionIdent.VistaDbDataSource: return(StiVistaDbConnector.Get(connectionString)); case StiConnectionIdent.UniversalDevartDataSource: return(StiUniversalDevartConnector.Get(connectionString)); case StiConnectionIdent.ODataDataSource: return(StiODataConnector.Get(connectionString)); default: 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); } }