Пример #1
0
        public virtual DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null)
        {
            if (options == null)
                options = new GetSchemaOptions();

            IncludedSchemas       = options.IncludedSchemas ?? new string[0];
            ExcludedSchemas       = options.ExcludedSchemas ?? new string[0];
            GenerateChar1AsString = options.GenerateChar1AsString;

            var dbConnection = (DbConnection)dataConnection.Connection;

            DataTypes = GetDataTypes(dataConnection);

            List<TableSchema>     tables;
            List<ProcedureSchema> procedures;

            if (options.GetTables)
            {
                tables =
                (
                    from t in GetTables(dataConnection)
                    where
                        (IncludedSchemas.Length == 0 ||  IncludedSchemas.Contains(t.SchemaName)) &&
                        (ExcludedSchemas.Length == 0 || !ExcludedSchemas.Contains(t.SchemaName))
                    select new TableSchema
                    {
                        ID              = t.TableID,
                        CatalogName     = t.CatalogName,
                        SchemaName      = t.SchemaName,
                        TableName       = t.TableName,
                        Description     = t.Description,
                        IsDefaultSchema = t.IsDefaultSchema,
                        IsView          = t.IsView,
                        TypeName        = ToValidName(t.TableName),
                        Columns         = new List<ColumnSchema>(),
                        ForeignKeys     = new List<ForeignKeySchema>()
                    }
                ).ToList();

                var pks = GetPrimaryKeys(dataConnection);

                #region Columns

                var columns =
                    from c  in GetColumns(dataConnection)

                    join dt in DataTypes
                        on c.DataType equals dt.TypeName into g1
                    from dt in g1.DefaultIfEmpty()

                    join pk in pks
                        on c.TableID + "." + c.Name equals pk.TableID + "." + pk.ColumnName into g2
                    from pk in g2.DefaultIfEmpty()

                    join t  in tables on c.TableID equals t.ID

                    orderby c.Ordinal
                    select new { t, c, dt, pk };

                foreach (var column in columns)
                {
                    var columnType = column.c.DataType;
                    var systemType = GetSystemType(columnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale);
                    var isNullable = column.c.IsNullable;

                    column.t.Columns.Add(new ColumnSchema
                    {
                        Table           = column.t,
                        ColumnName      = column.c.Name,
                        ColumnType      = column.c.ColumnType ?? GetDbType(columnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale),
                        IsNullable      = isNullable,
                        MemberName      = ToValidName(column.c.Name),
                        MemberType      = ToTypeName(systemType, isNullable),
                        SystemType      = systemType ?? typeof(object),
                        DataType        = GetDataType(columnType, column.c.ColumnType),
                        SkipOnInsert    = column.c.SkipOnInsert || column.c.IsIdentity,
                        SkipOnUpdate    = column.c.SkipOnUpdate || column.c.IsIdentity,
                        IsPrimaryKey    = column.pk != null,
                        PrimaryKeyOrder = column.pk != null ? column.pk.Ordinal : -1,
                        IsIdentity      = column.c.IsIdentity,
                        Description     = column.c.Description,
                    });
                }

                #endregion

                #region FK

                var fks = GetForeignKeys(dataConnection);

                foreach (var fk in fks.OrderBy(f => f.Ordinal))
                {
                    var thisTable  = (from t in tables where t.ID == fk.ThisTableID  select t).FirstOrDefault();
                    var otherTable = (from t in tables where t.ID == fk.OtherTableID select t).FirstOrDefault();

                    if (thisTable == null || otherTable == null)
                        continue;

                    var thisColumn  = (from c in thisTable. Columns where c.ColumnName == fk.ThisColumn   select c).Single();
                    var otherColumn = (from c in otherTable.Columns where c.ColumnName == fk.OtherColumn  select c).Single();

                    var key = thisTable.ForeignKeys.FirstOrDefault(f => f.KeyName == fk.Name);

                    if (key == null)
                    {
                        key = new ForeignKeySchema
                        {
                            KeyName      = fk.Name,
                            MemberName   = ToValidName(fk.Name),
                            ThisTable    = thisTable,
                            OtherTable   = otherTable,
                            ThisColumns  = new List<ColumnSchema>(),
                            OtherColumns = new List<ColumnSchema>(),
                            CanBeNull    = true,
                        };

                        thisTable.ForeignKeys.Add(key);
                    }

                    key.ThisColumns. Add(thisColumn);
                    key.OtherColumns.Add(otherColumn);
                }

                #endregion
            }
            else
                tables = new List<TableSchema>();

            if (options.GetProcedures)
            {
                #region Procedures

                var sqlProvider = dataConnection.DataProvider.CreateSqlProvider();
                var procs       = GetProcedures(dataConnection);
                var procPparams = GetProcedureParameters(dataConnection);

                if (procs != null)
                {
                    procedures =
                    (
                        from sp in procs
                        where
                            (IncludedSchemas.Length == 0 ||  IncludedSchemas.Contains(sp.SchemaName)) &&
                            (ExcludedSchemas.Length == 0 || !ExcludedSchemas.Contains(sp.SchemaName))
                        join p  in procPparams on sp.ProcedureID equals p.ProcedureID
                        into gr
                        select new ProcedureSchema
                        {
                            CatalogName     = sp.CatalogName,
                            SchemaName      = sp.SchemaName,
                            ProcedureName   = sp.ProcedureName,
                            MemberName      = ToValidName(sp.ProcedureName),
                            IsFunction      = sp.IsFunction,
                            IsTableFunction = sp.IsTableFunction,
                            IsDefaultSchema = sp.IsDefaultSchema,
                            Parameters      =
                            (
                                from pr in gr

                                join dt in DataTypes
                                    on pr.DataType equals dt.TypeName into g1
                                from dt in g1.DefaultIfEmpty()

                                let systemType = GetSystemType(pr.DataType, dt, pr.Length ?? 0, pr.Precision, pr.Scale)

                                orderby pr.Ordinal
                                select new ParameterSchema
                                {
                                    SchemaName    = pr.ParameterName,
                                    SchemaType    = GetDbType(pr.DataType, dt, pr.Length ?? 0, pr.Precision, pr.Scale),
                                    IsIn          = pr.IsIn,
                                    IsOut         = pr.IsOut,
                                    IsResult      = pr.IsResult,
                                    Size          = pr.Length,
                                    ParameterName = ToValidName(pr.ParameterName),
                                    ParameterType = ToTypeName(systemType, true),
                                    SystemType    = systemType ?? typeof(object),
                                    DataType      = GetDataType(pr.DataType, null)
                                }
                            ).ToList()
                        } into ps
                        where ps.Parameters.All(p => p.SchemaType != "table type")
                        select ps
                    ).ToList();

                    var current = 1;

                    foreach (var procedure in procedures)
                    {
                        if ((!procedure.IsFunction || procedure.IsTableFunction) && options.LoadProcedure(procedure))
                        {
                            var catalog     = procedure.CatalogName   == null ? null : sqlProvider.Convert(procedure.CatalogName,   ConvertType.NameToDatabase).  ToString();
                            var schema      = procedure.SchemaName    == null ? null : sqlProvider.Convert(procedure.SchemaName,    ConvertType.NameToOwner).     ToString();
                            var procName    = procedure.ProcedureName == null ? null : sqlProvider.Convert(procedure.ProcedureName, ConvertType.NameToQueryTable).ToString();
                            var commandText = sqlProvider.BuildTableName(new StringBuilder(), catalog, schema, procName).ToString();

                            CommandType     commandType;
                            DataParameter[] parameters;

                            if (procedure.IsTableFunction)
                            {
                                commandText = "SELECT * FROM " + commandText + "(";

                                for (var i = 0; i < procedure.Parameters.Count; i++)
                                {
                                    if (i != 0)
                                        commandText += ",";
                                    commandText += "NULL";
                                }

                                commandText += ")";
                                commandType = CommandType.Text;
                                parameters  = new DataParameter[0];
                            }
                            else
                            {
                                commandType = CommandType.StoredProcedure;
                                parameters  = procedure.Parameters.Select(p =>
                                    new DataParameter
                                    {
                                        Name      = p.ParameterName,
                                        Value     =
                                            p.SystemType == typeof(string)   ? "" :
                                            p.SystemType == typeof(DateTime) ? DateTime.Now :
                                                DefaultValue.GetValue(p.SystemType),
                                        DataType  = p.DataType,
                                        Size      = p.Size,
                                        Direction =
                                            p.IsIn ?
                                                p.IsOut ?
                                                    ParameterDirection.InputOutput :
                                                    ParameterDirection.Input :
                                                ParameterDirection.Output
                                    }).ToArray();
                            }

                            {
                                try
                                {
                                    var st = GetProcedureSchema(dataConnection, commandText, commandType, parameters);

                                    if (st != null)
                                    {
                                        procedure.ResultTable = new TableSchema
                                        {
                                            IsProcedureResult = true,
                                            TypeName          = ToValidName(procedure.ProcedureName + "Result"),
                                            ForeignKeys       = new List<ForeignKeySchema>(),
                                            Columns           = GetProcedureResultColumns(st)
                                        };

                                        foreach (var column in procedure.ResultTable.Columns)
                                            column.Table = procedure.ResultTable;

                                        procedure.SimilarTables =
                                        (
                                            from  t in tables
                                            where t.Columns.Count == procedure.ResultTable.Columns.Count
                                            let zip = t.Columns.Zip(procedure.ResultTable.Columns, (c1, c2) => new { c1, c2 })
                                            where zip.All(z => z.c1.ColumnName == z.c2.ColumnName && z.c1.SystemType == z.c2.SystemType)
                                            select t
                                        ).ToList();
                                    }
                                }
                                catch (Exception ex)
                                {
                                    procedure.ResultException = ex;
                                }
                            }
                        }

                        options.ProcedureLoadingProgress(procedures.Count, current++);
                    }
                }
                else
                    procedures = new List<ProcedureSchema>();

                #endregion
            }
            else
                procedures = new List<ProcedureSchema>();

            return ProcessSchema(new DatabaseSchema
            {
                DataSource    = GetDataSourceName(dbConnection),
                Database      = GetDatabaseName  (dbConnection),
                ServerVersion = dbConnection.ServerVersion,
                Tables        = tables,
                Procedures    = procedures,
            });
        }
Пример #2
0
        public virtual DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null)
        {
            if (options == null)
            {
                options = new GetSchemaOptions();
            }

            IncludedSchemas       = options.IncludedSchemas ?? new string[0];
            ExcludedSchemas       = options.ExcludedSchemas ?? new string[0];
            GenerateChar1AsString = options.GenerateChar1AsString;

            var dbConnection = (DbConnection)dataConnection.Connection;

            DataTypes = GetDataTypes(dataConnection);

            List <TableSchema>     tables;
            List <ProcedureSchema> procedures;

            if (options.GetTables)
            {
                tables =
                    (
                        from t in GetTables(dataConnection)
                        where
                        (IncludedSchemas.Length == 0 || IncludedSchemas.Contains(t.SchemaName)) &&
                        (ExcludedSchemas.Length == 0 || !ExcludedSchemas.Contains(t.SchemaName))
                        select new TableSchema
                {
                    ID = t.TableID,
                    CatalogName = t.CatalogName,
                    SchemaName = t.SchemaName,
                    TableName = t.TableName,
                    Description = t.Description,
                    IsDefaultSchema = t.IsDefaultSchema,
                    IsView = t.IsView,
                    TypeName = ToValidName(t.TableName),
                    Columns = new List <ColumnSchema>(),
                    ForeignKeys = new List <ForeignKeySchema>()
                }
                    ).ToList();

                var pks = GetPrimaryKeys(dataConnection);

                #region Columns

                var columns =
                    from c  in GetColumns(dataConnection)

                    join dt in DataTypes
                    on c.DataType equals dt.TypeName into g1
                    from dt in g1.DefaultIfEmpty()

                    join pk in pks
                    on c.TableID + "." + c.Name equals pk.TableID + "." + pk.ColumnName into g2
                    from pk in g2.DefaultIfEmpty()

                    join t  in tables on c.TableID equals t.ID

                    orderby c.Ordinal
                    select new { t, c, dt, pk };

                foreach (var column in columns)
                {
                    var columnType = column.c.DataType;
                    var systemType = GetSystemType(columnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale);
                    var isNullable = column.c.IsNullable;

                    column.t.Columns.Add(new ColumnSchema
                    {
                        Table           = column.t,
                        ColumnName      = column.c.Name,
                        ColumnType      = column.c.ColumnType ?? GetDbType(columnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale),
                        IsNullable      = isNullable,
                        MemberName      = ToValidName(column.c.Name),
                        MemberType      = ToTypeName(systemType, isNullable),
                        SystemType      = systemType ?? typeof(object),
                        DataType        = GetDataType(columnType, column.c.ColumnType),
                        SkipOnInsert    = column.c.SkipOnInsert || column.c.IsIdentity,
                        SkipOnUpdate    = column.c.SkipOnUpdate || column.c.IsIdentity,
                        IsPrimaryKey    = column.pk != null,
                        PrimaryKeyOrder = column.pk != null ? column.pk.Ordinal : -1,
                        IsIdentity      = column.c.IsIdentity,
                        Description     = column.c.Description,
                    });
                }

                #endregion

                #region FK

                var fks = GetForeignKeys(dataConnection);

                foreach (var fk in fks.OrderBy(f => f.Ordinal))
                {
                    var thisTable  = (from t in tables where t.ID == fk.ThisTableID select t).FirstOrDefault();
                    var otherTable = (from t in tables where t.ID == fk.OtherTableID select t).FirstOrDefault();

                    if (thisTable == null || otherTable == null)
                    {
                        continue;
                    }

                    var thisColumn  = (from c in thisTable.Columns where c.ColumnName == fk.ThisColumn select c).Single();
                    var otherColumn = (from c in otherTable.Columns where c.ColumnName == fk.OtherColumn select c).Single();

                    var key = thisTable.ForeignKeys.FirstOrDefault(f => f.KeyName == fk.Name);

                    if (key == null)
                    {
                        key = new ForeignKeySchema
                        {
                            KeyName      = fk.Name,
                            MemberName   = fk.Name,
                            ThisTable    = thisTable,
                            OtherTable   = otherTable,
                            ThisColumns  = new List <ColumnSchema>(),
                            OtherColumns = new List <ColumnSchema>(),
                            CanBeNull    = true,
                        };

                        thisTable.ForeignKeys.Add(key);
                    }

                    key.ThisColumns.Add(thisColumn);
                    key.OtherColumns.Add(otherColumn);
                }

                #endregion
            }
            else
            {
                tables = new List <TableSchema>();
            }

            if (options.GetProcedures)
            {
                #region Procedures

                var sqlProvider = dataConnection.DataProvider.CreateSqlProvider();
                var procs       = GetProcedures(dataConnection);
                var procPparams = GetProcedureParameters(dataConnection);

                if (procs != null)
                {
                    procedures =
                        (
                            from sp in procs
                            where
                            (IncludedSchemas.Length == 0 || IncludedSchemas.Contains(sp.SchemaName)) &&
                            (ExcludedSchemas.Length == 0 || !ExcludedSchemas.Contains(sp.SchemaName))
                            join p  in procPparams on sp.ProcedureID equals p.ProcedureID
                            into gr
                            select new ProcedureSchema
                    {
                        CatalogName = sp.CatalogName,
                        SchemaName = sp.SchemaName,
                        ProcedureName = sp.ProcedureName,
                        MemberName = ToValidName(sp.ProcedureName),
                        IsFunction = sp.IsFunction,
                        IsTableFunction = sp.IsTableFunction,
                        IsDefaultSchema = sp.IsDefaultSchema,
                        Parameters =
                            (
                                from pr in gr

                                join dt in DataTypes
                                on pr.DataType equals dt.TypeName into g1
                                from dt in g1.DefaultIfEmpty()

                                let systemType = GetSystemType(pr.DataType, dt, pr.Length ?? 0, pr.Precision, pr.Scale)

                                                 orderby pr.Ordinal
                                                 select new ParameterSchema
                        {
                            SchemaName = pr.ParameterName,
                            SchemaType = GetDbType(pr.DataType, dt, pr.Length ?? 0, pr.Precision, pr.Scale),
                            IsIn = pr.IsIn,
                            IsOut = pr.IsOut,
                            IsResult = pr.IsResult,
                            Size = pr.Length,
                            ParameterName = ToValidName(pr.ParameterName),
                            ParameterType = ToTypeName(systemType, true),
                            SystemType = systemType ?? typeof(object),
                            DataType = GetDataType(pr.DataType, null)
                        }
                            ).ToList()
                    } into ps
                            where ps.Parameters.All(p => p.SchemaType != "table type")
                            select ps
                        ).ToList();

                    var current = 1;

                    foreach (var procedure in procedures)
                    {
                        if ((!procedure.IsFunction || procedure.IsTableFunction) && options.LoadProcedure(procedure))
                        {
                            var commandText = sqlProvider.BuildTableName(
                                new StringBuilder(),
                                procedure.CatalogName, procedure.SchemaName, procedure.ProcedureName).ToString();

                            CommandType     commandType;
                            DataParameter[] parameters;

                            if (procedure.IsTableFunction)
                            {
                                commandText = "SELECT * FROM " + commandText + "(";

                                for (var i = 0; i < procedure.Parameters.Count; i++)
                                {
                                    if (i != 0)
                                    {
                                        commandText += ",";
                                    }
                                    commandText += "NULL";
                                }

                                commandText += ")";
                                commandType  = CommandType.Text;
                                parameters   = new DataParameter[0];
                            }
                            else
                            {
                                commandType = CommandType.StoredProcedure;
                                parameters  = procedure.Parameters.Select(p =>
                                                                          new DataParameter
                                {
                                    Name  = p.ParameterName,
                                    Value =
                                        p.SystemType == typeof(string)   ? "" :
                                        p.SystemType == typeof(DateTime) ? DateTime.Now :
                                        DefaultValue.GetValue(p.SystemType),
                                    DataType  = p.DataType,
                                    Size      = p.Size,
                                    Direction =
                                        p.IsIn ?
                                        p.IsOut ?
                                        ParameterDirection.InputOutput :
                                        ParameterDirection.Input :
                                        ParameterDirection.Output
                                }).ToArray();
                            }

                            {
                                try
                                {
                                    var st = GetProcedureSchema(dataConnection, commandText, commandType, parameters);

                                    if (st != null)
                                    {
                                        procedure.ResultTable = new TableSchema
                                        {
                                            IsProcedureResult = true,
                                            TypeName          = ToValidName(procedure.ProcedureName + "Result"),
                                            ForeignKeys       = new List <ForeignKeySchema>(),
                                            Columns           = GetProcedureResultColumns(st)
                                        };

                                        foreach (var column in procedure.ResultTable.Columns)
                                        {
                                            column.Table = procedure.ResultTable;
                                        }

                                        procedure.SimilarTables =
                                            (
                                                from t in tables
                                                where t.Columns.Count == procedure.ResultTable.Columns.Count
                                                let zip = t.Columns.Zip(procedure.ResultTable.Columns, (c1, c2) => new { c1, c2 })
                                                          where zip.All(z => z.c1.ColumnName == z.c2.ColumnName && z.c1.SystemType == z.c2.SystemType)
                                                          select t
                                            ).ToList();
                                    }
                                }
                                catch (Exception ex)
                                {
                                    procedure.ResultException = ex;
                                }
                            }
                        }

                        options.ProcedureLoadingProgress(procedures.Count, current++);
                    }
                }
                else
                {
                    procedures = new List <ProcedureSchema>();
                }

                #endregion
            }
            else
            {
                procedures = new List <ProcedureSchema>();
            }

            return(ProcessSchema(new DatabaseSchema
            {
                DataSource = GetDataSourceName(dbConnection),
                Database = GetDatabaseName(dbConnection),
                ServerVersion = dbConnection.ServerVersion,
                Tables = tables,
                Procedures = procedures,
            }));
        }