/// <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 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 StiDataSchema RetrieveSchema()
{
if (string.IsNullOrEmpty(ConnectionString))
{
return(null);
}
var schema = new StiDataSchema(StiConnectionIdent.ODataDataSource);
try
{
using (var client = GetDefaultWebClient())
{
var metadata = client.DownloadString(StiUrl.Combine(Address, "$metadata"));
using (var reader = new StringReader(metadata))
using (var xmlReader = XmlReader.Create(reader))
{
var root = XElement.Load(xmlReader);
var edmx = root.GetNamespaceOfPrefix("edmx");
var edm = root.Element(edmx + "DataServices").Elements().First().GetDefaultNamespace();
var elementSchema = root.Element(edmx + "DataServices").Elements().FirstOrDefault();
var namespaceStr = elementSchema.Attribute("Namespace") != null ? (string)elementSchema.Attribute("Namespace") : null;
var types = new Hashtable();
var hash = new Hashtable();
#region Parse Types
foreach (var entityType in elementSchema.Elements().Where(e => e.Name.LocalName == "EntityType" || e.Name.LocalName == "ComplexType"))
{
try
{
var name = (string)entityType.Attribute("Name");
var baseType = entityType.Attribute("BaseType") != null ? (string)entityType.Attribute("BaseType") : null;
if (string.IsNullOrWhiteSpace(name))
{
continue;
}
var properties = entityType.Elements(edm + "Property");
var table = new StiDataTableSchema(name, name);
if (baseType != null)
{
var str = baseType.Replace(namespaceStr + ".", "");
hash[str] = table;
}
foreach (var property in properties)
{
try
{
var propertyName = (string)property.Attribute("Name");
if (string.IsNullOrWhiteSpace(propertyName))
{
continue;
}
var propertyNullable = property.Attribute("Nullable") != null && property.Attribute("Nullable").Value == "true";
var propertyType = (string)property.Attribute("Type");
var columnType = GetNetType(propertyType);
if (propertyNullable)
{
columnType = typeof(Nullable <>).MakeGenericType(columnType);
}
var column = new StiDataColumnSchema(propertyName, columnType);
table.Columns.Add(column);
}
catch
{
}
}
types[namespaceStr + "." + table.Name] = table;
}
catch
{
}
foreach (string tableName in hash.Keys)
{
var table = hash[tableName] as StiDataTableSchema;
var baseTable = schema.Tables.FirstOrDefault(t => t.Name == tableName);
if (baseTable == null)
{
continue;
}
foreach (var column in baseTable.Columns)
{
if (table.Columns.Any(c => c.Name == column.Name))
{
continue;
}
table.Columns.Add(column);
}
}
}
#endregion
#region Parse Containers
foreach (var entityCont in elementSchema.Elements().Where(e => e.Name.LocalName == "EntityContainer"))
{
foreach (var entitySet in entityCont.Elements().Where(e => e.Name.LocalName == "EntitySet"))
{
try
{
var name = (string)entitySet.Attribute("Name");
var type = (string)entitySet.Attribute("EntityType");
if (string.IsNullOrWhiteSpace(name))
{
continue;
}
var table = new StiDataTableSchema(name, name);
var columnsTable = types[type] as StiDataTableSchema;
if (columnsTable != null)
{
table.Columns.AddRange(columnsTable.Columns);
}
schema.Tables.Add(table);
}
catch
{
}
foreach (string tableName in hash.Keys)
{
var table = hash[tableName] as StiDataTableSchema;
var baseTable = schema.Tables.FirstOrDefault(t => t.Name == tableName);
if (baseTable == null)
{
continue;
}
foreach (var column in baseTable.Columns)
{
if (table.Columns.Any(c => c.Name == column.Name))
{
continue;
}
table.Columns.Add(column);
}
}
}
}
#endregion
}
}
return(schema);
}
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
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 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
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();
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();
#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();
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();
#region Tables
var tableHash = new Hashtable();
try
{
var tables = connection.GetSchema("Tables", new[] { null, null, null, "TABLE" });
foreach (var row in StiSchemaRow.All(tables))
{
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))
{
var view = StiDataTableSchema.NewView(row.VIEW_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))
{
var column = new StiDataColumnSchema(row.COLUMN_NAME, GetNetType(row.COLUMN_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 procedure = StiDataTableSchema.NewProcedure(row.PROCEDURE_NAME);
procedureHash[procedure.Name] = procedure;
schema.StoredProcedures.Add(procedure);
}
}
catch
{
}
#endregion
#region Procedures Parameters and Columns
foreach (var procedure in schema.StoredProcedures)
{
try
{
var parameters = connection.GetSchema("procedureparameters", new[] { null, null, procedure.Name });
foreach (var row in StiSchemaRow.All(parameters))
{
if (row.PARAMETER_DIRECTION != "1")
{
continue;
}
procedure.Parameters.Add(new StiDataParameterSchema(row.PARAMETER_NAME, GetNetType(row.PARAMETER_DATA_TYPE)));
}
}
catch
{
}
}
#endregion
#region Relations
try
{
const string commandText = @"
SELECT rc.RDB$CONSTRAINT_NAME AS constraint_name,
i.RDB$RELATION_NAME AS table_name,
s.RDB$FIELD_NAME AS field_name,
i2.RDB$RELATION_NAME AS references_table,
s2.RDB$FIELD_NAME AS references_field,
(s.RDB$FIELD_POSITION + 1) AS field_position
FROM RDB$INDEX_SEGMENTS s
LEFT JOIN RDB$INDICES i ON i.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc2 ON rc2.RDB$CONSTRAINT_NAME = refc.RDB$CONST_NAME_UQ
LEFT JOIN RDB$INDICES i2 ON i2.RDB$INDEX_NAME = rc2.RDB$INDEX_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s2 ON i2.RDB$INDEX_NAME = s2.RDB$INDEX_NAME
WHERE rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
ORDER BY i.RDB$RELATION_NAME";
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.Trim(),
ParentSourceName = row.TABLE_NAME.Trim(),
ChildSourceName = row.REFERENCES_TABLE.Trim(),
ParentColumns = new List <string> {
row.FIELD_NAME.Trim()
},
ChildColumns = new List <string> {
row.REFERENCES_FIELD.Trim()
}
});
}
}
}
}
catch
{
}
#endregion
connection.Close();
}
return(schema);
}
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);
}
}