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