예제 #1
0
        /// <inheritdoc />
        public IEnumerable <IDbTableFieldEntity> GetFields(IDbTableEntity table)
        {
            //create SQL statement
            IConstantSqlExpressionsFactory constantFactory = new SqlServerConstantExpressionFactory();
            ISqlObject     owner                      = new SqlServerSource("INFORMATION_SCHEMA");
            ISqlExpression tableName                  = constantFactory.Create(table.Name);
            ISqlExpression tableSchema                = constantFactory.Create(table.Schema);
            ISqlFunction   tableID                    = new SqlFunction("OBJECT_ID").AddArgument(constantFactory.Create(table.SchemaQualifiedName));
            ISqlObject     columnsView                = new SqlServerSource(owner, "COLUMNS");
            ISqlObject     extendedPropertiesView     = new SqlServerSource(new SqlServerSource("sys"), "extended_properties");
            ISqlObject     keyColumnUsageView         = new SqlServerSource(owner, "KEY_COLUMN_USAGE");
            ISqlObject     tableConstraintsView       = new SqlServerSource(owner, "TABLE_CONSTRAINTS");
            ISqlObject     referentialConstraintsView = new SqlServerSource(owner, "REFERENTIAL_CONSTRAINTS");
            ISqlObject     keyColumnUsageView2        = new SqlServerSource(owner, "KEY_COLUMN_USAGE", "kcu");

            ISqlObject nameField           = new SqlServerField(columnsView, "COLUMN_NAME", "Name");
            ISqlObject indexField          = new SqlServerField(columnsView, "ORDINAL_POSITION", "Index");
            ISqlObject typeField           = new SqlServerField(columnsView, "DATA_TYPE", "TypeName");
            ISqlObject descriptionField    = new SqlServerField(extendedPropertiesView, "value", "Description");
            ISqlObject maxLengthField      = new SqlServerField(columnsView, "CHARACTER_MAXIMUM_LENGTH", "MaxLength");
            ISqlObject defaultField        = new SqlServerField(columnsView, "COLUMN_DEFAULT", "DefaultExpression");
            ISqlObject isNullableField     = new SqlServerField(columnsView, "IS_NULLABLE", "IsNullable");
            ISqlObject isIdentityField     = new SqlServerField(new SqlFunction("COLUMNPROPERTY").AddArgument(tableID).AddArgument(nameField, false).AddArgument(constantFactory.Create("IsIdentity")), "IsIdentity");
            ISqlObject constraintNameField = new SqlServerField(tableConstraintsView, "CONSTRAINT_NAME", "ConstraintName");
            ISqlObject constraintTypeField = new SqlServerField(tableConstraintsView, "CONSTRAINT_TYPE", "ConstraintType");
            ISqlObject referenceTableField = new SqlServerField(keyColumnUsageView2, "TABLE_NAME", "ReferenceTable");
            ISqlObject referenceFieldField = new SqlServerField(keyColumnUsageView2, "COLUMN_NAME", "ReferenceField");

            ISqlSelectStatement statement = new SqlServerSelectStatement();

            statement.SelectClause.AddExpressions(nameField, indexField, typeField, descriptionField, maxLengthField, defaultField, isNullableField, isIdentityField, constraintNameField, constraintTypeField, referenceTableField, referenceFieldField);
            statement.FromClause.SetSource(columnsView);
            statement.FromClause.LeftOuterJoin(extendedPropertiesView, new SqlServerField(extendedPropertiesView, "class", null).Equal(constantFactory.Create(1)).And(new SqlServerField(extendedPropertiesView, "major_id", null).Equal(tableID)).And(new SqlServerField(extendedPropertiesView, "minor_id", null).Equal(new SqlFunction("COLUMNPROPERTY").AddArgument(tableID).AddArgument(nameField, false).AddArgument(constantFactory.Create("ColumnId")))));
            statement.FromClause.LeftOuterJoin(keyColumnUsageView, new SqlServerField(columnsView, "COLUMN_NAME", null).Equal(new SqlServerField(keyColumnUsageView, "COLUMN_NAME", null)).And(new SqlServerField(keyColumnUsageView, "TABLE_NAME", null).Equal(tableName)).And(new SqlServerField(keyColumnUsageView, "TABLE_SCHEMA", null).Equal(tableSchema)));
            statement.FromClause.LeftOuterJoin(tableConstraintsView, new SqlServerField(keyColumnUsageView, "CONSTRAINT_NAME", null).Equal(new SqlServerField(tableConstraintsView, "CONSTRAINT_NAME", null)).And(new SqlServerField(tableConstraintsView, "TABLE_NAME", null).Equal(tableName)).And(new SqlServerField(tableConstraintsView, "TABLE_SCHEMA", null).Equal(tableSchema)));
            statement.FromClause.LeftOuterJoin(referentialConstraintsView, new SqlServerField(tableConstraintsView, "CONSTRAINT_NAME", null).Equal(new SqlServerField(referentialConstraintsView, "CONSTRAINT_NAME", null)));
            statement.FromClause.LeftOuterJoin(keyColumnUsageView2, new SqlServerField(referentialConstraintsView, "UNIQUE_CONSTRAINT_NAME", null).Equal(new SqlServerField(keyColumnUsageView2, "CONSTRAINT_NAME", null)).And(new SqlServerField(keyColumnUsageView, "ORDINAL_POSITION", null).Equal(new SqlServerField(keyColumnUsageView2, "ORDINAL_POSITION", null))));
            statement.WhereClause.Condition = new SqlServerField(columnsView, "TABLE_NAME", null).Equal(tableName).And(new SqlServerField(columnsView, "TABLE_SCHEMA", null).Equal(tableSchema));
            statement.OrderClause.AddExpression(indexField, SqlOrder.Asc);

            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

            builder.DataSource     = table.Database.Source.Name;
            builder.InitialCatalog = table.Database.Name;
            if (!(builder.IntegratedSecurity = table.Database.Source.DbCredential.IntegratedSecurity))
            {
                builder.UserID   = table.Database.Source.DbCredential.UserName;
                builder.Password = table.Database.Source.DbCredential.Password;
            }

            //read all fields data
            ICollection <FieldItem> items        = new List <FieldItem>();
            IList <ISqlExpression>  selectFields = new List <ISqlExpression>(statement.SelectClause.Expressions);

            using (IDbConnection connection = new SqlConnection(builder.ConnectionString)) {
                using (IDataReader reader = SqlHelper.ExecuteSelect(connection, statement)) {
                    int nameIndex           = selectFields.IndexOf(nameField);
                    int indexIndex          = selectFields.IndexOf(indexField);
                    int typeIndex           = selectFields.IndexOf(typeField);
                    int descriptionIndex    = selectFields.IndexOf(descriptionField);
                    int maxLengthIndex      = selectFields.IndexOf(maxLengthField);
                    int defaultIndex        = selectFields.IndexOf(defaultField);
                    int isNullableIndex     = selectFields.IndexOf(isNullableField);
                    int isIdentityIndex     = selectFields.IndexOf(isIdentityField);
                    int constraintNameIndex = selectFields.IndexOf(constraintNameField);
                    int constraintTypeIndex = selectFields.IndexOf(constraintTypeField);
                    int referenceTableIndex = selectFields.IndexOf(referenceTableField);
                    int referenceFieldIndex = selectFields.IndexOf(referenceFieldField);

                    while (reader.Read())
                    {
                        items.Add(new FieldItem {
                            Name               = reader.GetString(nameIndex),
                            Index              = reader.GetInt32(indexIndex),
                            Type               = reader.GetString(typeIndex),
                            Description        = reader.IsDBNull(descriptionIndex) ? null : reader.GetString(descriptionIndex),
                            MaxLength          = reader.IsDBNull(maxLengthIndex) ? 0 : reader.GetInt32(maxLengthIndex),
                            HasDefault         = !reader.IsDBNull(defaultIndex),
                            IsNullable         = reader.GetString(isNullableIndex).Equals("YES", StringComparison.OrdinalIgnoreCase),
                            IsIdentity         = reader.GetInt32(isIdentityIndex) == 1,
                            ConstraintName     = reader.IsDBNull(constraintNameIndex) ? null : reader.GetString(constraintNameIndex),
                            ConstraintTypeName = reader.IsDBNull(constraintTypeIndex) ? null : reader.GetString(constraintTypeIndex),
                            ReferenceTable     = reader.IsDBNull(referenceTableIndex) ? null : reader.GetString(referenceTableIndex),
                            ReferenceField     = reader.IsDBNull(referenceFieldIndex) ? null : reader.GetString(referenceFieldIndex),
                        });
                    }
                }
            }

            //inject unique index data
            FieldItem field = null;

            foreach (UniqueIndexColumnItem item in this.GetUniqueIndexColumns(table))
            {
                field = items.Where((obj) => string.Equals(obj.Name, item.ColumnName, StringComparison.OrdinalIgnoreCase)).First();
                if (field.ConstraintName == null)
                {
                    field.ConstraintName     = item.IndexName;
                    field.ConstraintTypeName = "UNIQUE";
                }
                else
                {
                    field = items.AddItem((FieldItem)field.Clone());
                    field.ConstraintName     = item.IndexName;
                    field.ConstraintTypeName = "UNIQUE";
                }
            }

            //get read-only data
            ISqlObject tableSource = new SqlServerSource(new SqlServerSource(table.Schema), table.Name);

            statement = new SqlServerSelectStatement();
            statement.SelectClause.AddExpressions(new SqlAllField(tableSource));
            statement.FromClause.Source = tableSource;
            using (IDbConnection connection = new SqlConnection(builder.ConnectionString)) {
                using (DataTable schema = SqlHelper.ExecuteSchema(connection, statement)) {
                    using (DataColumn columnNameColumn = schema.Columns["ColumnName"]) {
                        using (DataColumn isReadOnlyColumn = schema.Columns["IsReadOnly"]) {
                            foreach (DataRow row in schema.Rows)
                            {
                                if (row.IsNull(columnNameColumn))
                                {
                                    continue;
                                }

                                this.m_isReadOnlyCache[((string)row[columnNameColumn]).ToLower()] = (bool)row[isReadOnlyColumn];
                            }
                        }
                    }
                }
            }

            //analyze fields
            IDictionary <string, DbConstraintEntity> constraints = new Dictionary <string, DbConstraintEntity>();

            this.CreateFileds(table, constraints, items.Where((obj) => string.IsNullOrWhiteSpace(obj.ReferenceField)));
            this.CreateFileds(table, constraints, items.Where((obj) => !string.IsNullOrWhiteSpace(obj.ReferenceField)));

            return(new List <IDbTableFieldEntity>(g_fieldsCache[table].Values));
        }