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