コード例 #1
0
        public IList<Column> GetTableDetails(Table table, string owner)
        {
            var columns = new List<Column>();
            var conn = new SqlConnection(connectionStr);
            conn.Open();
            using (conn)
            {
                using (SqlCommand tableDetailsCommand = conn.CreateCommand())
                {
                    tableDetailsCommand.CommandText = string.Format(
                        @"
                            SELECT c.column_name, c.data_type, c.is_nullable, tc.constraint_type, c.numeric_precision, c.numeric_scale, c.character_maximum_length,
                                COLUMNPROPERTY(object_id('[' + '{1}' + '].[' + '{0}' + ']'), c.column_name, 'IsIdentity') as 'IsIdentity',t1.value as 'remark'
                            from information_schema.columns c
                                join syscolumns on (
                                    id = object_id('[' + '{1}' + '].[' + '{0}' + ']')
                                    and syscolumns.name = c.column_name
                                )
                                left outer join (
                                    information_schema.constraint_column_usage ccu
                                    join information_schema.table_constraints tc on (
                                        tc.table_schema = ccu.table_schema
                                        and tc.constraint_name = ccu.constraint_name
                                        and tc.constraint_type <> 'CHECK'
                                    )
                                ) on (
                                    c.table_schema = ccu.table_schema and ccu.table_schema =  '{1}'
                                    and c.table_name = ccu.table_name
                                    and c.column_name = ccu.column_name
                                )
                                left join  sys.extended_properties t1
                                    on (
                                        t1.major_id = object_id(c.table_name)
                                        and syscolumns.colid = t1.minor_id
                                    )
                            where c.table_name = '{0}'
                            order by c.table_name, c.ordinal_position",
                        table.Name, owner);
                    using (SqlDataReader sqlDataReader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
                    {
                        while (sqlDataReader.Read())
                        {
                            string columnName = sqlDataReader.GetString(0);
                            string dataType = sqlDataReader.GetString(1);
                            bool isNullable = sqlDataReader.GetString(2).Equals("YES",
                                                                                StringComparison.
                                                                                    CurrentCultureIgnoreCase);
                            var characterMaxLenth = sqlDataReader["character_maximum_length"] as int?;
                            var numericPrecision = sqlDataReader["numeric_precision"] as int?;
                            var numericScale = sqlDataReader["numeric_scale"] as int?;
                            var remark = sqlDataReader["remark"] as string;
                            bool isPrimaryKey =
                                (!sqlDataReader.IsDBNull(3)
                                     ? sqlDataReader.GetString(3).Equals(
                                         SqlServerConstraintType.PrimaryKey.ToString(),
                                         StringComparison.CurrentCultureIgnoreCase)
                                     : false);
                            bool isForeignKey =
                                (!sqlDataReader.IsDBNull(3)
                                     ? sqlDataReader.GetString(3).Equals(
                                         SqlServerConstraintType.ForeignKey.ToString(),
                                         StringComparison.CurrentCultureIgnoreCase)
                                     : false);
                            bool isIdentity = sqlDataReader["IsIdentity"].ToString() == "1" ? true : false;

                            columns.Add(new Column
                                            {
                                                TableName = table.Name,
                                                Name = columnName,
                                                DataType = dataType,
                                                IsNullable = isNullable,
                                                IsPrimaryKey = isPrimaryKey,
                                                //IsPrimaryKey(selectedTableName.Name, columnName)
                                                IsForeignKey = isForeignKey,
                                                // IsFK()
                                                MappedDataType =
                                                    DataTypeMapper.MapFromDBType(dataType).ToString(),
                                                DataLength = characterMaxLenth,
                                                IsIdentity = isIdentity,
                                                Remark = string.IsNullOrEmpty(remark) ? "" : remark
                                            });

                            table.Columns = columns;
                        }
                        table.PrimaryKey = DeterminePrimaryKeys(table);
                        //comment by ben 2011-09-04
                        //table.ForeignKeys = DetermineForeignKeyReferences(table);
                        //table.HasManyRelationships = DetermineHasManyRelationships(table);
                    }
                }
            }
            return columns;
        }
コード例 #2
0
        private IList<ForeignKey> DetermineForeignKeyReferences(Table table)
        {
            var foreignKeys = table.Columns.Where(x => x.IsForeignKey.Equals(true));
            var tempForeignKeys = new List<ForeignKey>();

            foreach (var foreignKey in foreignKeys)
            {
                tempForeignKeys.Add(new ForeignKey
                                        {
                                            Name = foreignKey.Name,
                                            References = GetForeignKeyReferenceTableName(table.Name, foreignKey.Name)
                                        });
            }

            return tempForeignKeys;
        }
コード例 #3
0
        // http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/
        private IList<HasMany> DetermineHasManyRelationships(Table table)
        {
            var hasManyRelationships = new List<HasMany>();
            var conn = new SqlConnection(connectionStr);
            conn.Open();
            using (conn)
            {
                using (var command = new SqlCommand())
                {
                    command.Connection = conn;
                    command.CommandText =
                        String.Format(
                            @"
                        SELECT DISTINCT
                            PK_TABLE = b.TABLE_NAME,
                            FK_TABLE = c.TABLE_NAME,
                            FK_COLUMN_NAME = d.COLUMN_NAME
                        FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
                          JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA AND a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME
                          JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ON a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA AND a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
                          JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE d on a.CONSTRAINT_NAME = d.CONSTRAINT_NAME
                        WHERE b.TABLE_NAME = '{0}'
                        ORDER BY 1,2",
                            table.Name);
                    SqlDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        hasManyRelationships.Add(new HasMany
                                                     {
                                                         Reference = reader.GetString(1),
                                                         ReferenceColumn = reader["FK_COLUMN_NAME"].ToString()
                                                     });
                    }

                    return hasManyRelationships;
                }
            }
        }
コード例 #4
0
        private static PrimaryKey DeterminePrimaryKeys(Table table)
        {
            IEnumerable<Column> primaryKeys = table.Columns.Where(x => x.IsPrimaryKey.Equals(true));

            if (primaryKeys.Count() == 1)
            {
                Column c = primaryKeys.First();
                var key = new PrimaryKey
                              {
                                  Type = PrimaryKeyType.PrimaryKey,
                                  Columns =
                                      {
                                          new Column
                                              {
                                                  DataType = c.DataType,
                                                  Name = c.Name,
                                                   IsIdentity=c.IsIdentity
                                              }
                                      }
                              };
                return key;
            }
            else
            {
                var key = new PrimaryKey
                              {
                                  Type = PrimaryKeyType.CompositeKey
                              };
                foreach (var primaryKey in primaryKeys)
                {
                    key.Columns.Add(new Column
                                        {
                                            DataType = primaryKey.DataType,
                                            Name = primaryKey.Name,
                                            IsIdentity = primaryKey.IsIdentity
                                        });
                }
                return key;
            }
        }