/// <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);
            }
        }
예제 #2
0
        /// <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);
            }
        }
예제 #5
0
        /// <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);
            }
        }
예제 #9
0
        /// <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);
            }
        }
예제 #10
0
        /// <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);
            }
        }
예제 #12
0
        /// <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);
            }
        }
예제 #13
0
        /// <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);
            }
        }
예제 #14
0
        /// <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);
            }
        }