コード例 #1
0
ファイル: SchemaUtils.cs プロジェクト: gunivan/gensqldata
        public static List <ColumnBase> ReadSchemaColumn(String tableName)
        {
            LogUtils.Logs.Log("Begin reading schema column for table:{0}", tableName);
            var query = String.Format(@"SELECT C.*,RK.CNST_NAME, RK.PARENT_COL, RK.PARENT, ISPK, ISUK, UKNAME  
                           FROM  
                                       (SELECT COLUMN_NAME ,IS_NULLABLE ,  DATA_TYPE, 
                                               CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX 
                                       FROM INFORMATION_SCHEMA.COLUMNS   
                                       WHERE TABLE_NAME= '{0}') AS C 
                           LEFT OUTER JOIN 
                                       (SELECT DISTINCT OBJECT_NAME(constraint_object_id) CNST_NAME,COL_NAME(parent_object_id, parent_column_id ) AS CHILD_COL, 
                                                         COL_NAME(referenced_object_id, referenced_column_id ) AS  PARENT_COL, 
                                                         OBJECT_NAME(referenced_object_id) AS PARENT 
                                       FROM sys.foreign_key_columns 
                                       WHERE OBJECT_NAME(parent_object_id) = '{0}')	AS RK 
                           ON C.COLUMN_NAME = RK.CHILD_COL 
                           LEFT OUTER JOIN 
                                       (SELECT  COLUMN_NAME, 
                                       OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsPrimaryKey') ISPK  
                                       FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
                                       WHERE TABLE_NAME='{0}' AND  OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsPrimaryKey') = 1) AS PK  
                           ON C.COLUMN_NAME = PK.COLUMN_NAME  
                           LEFT OUTER JOIN  
                                       (SELECT  COLUMN_NAME,  CONSTRAINT_NAME AS UKNAME , 
                                       OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsUniqueCnst') ISUK 
                                       FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
                                       WHERE TABLE_NAME='{0}' AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsUniqueCnst') = 1) AS UK 
                           ON C.COLUMN_NAME = UK.COLUMN_NAME", tableName);
            var table = new DataTable();

            Sql.Fill(query, table);
            var columnList = new List <ColumnBase>();

            foreach (DataRow row in table.Rows)
            {
                var        isPk   = FieldUtils.AsInt(row, "ISPK") == 1;
                var        isUk   = FieldUtils.AsInt(row, "ISUK") == 1;
                var        isRef  = !String.IsNullOrEmpty(FieldUtils.AsText(row, "PARENT_COL"));
                ColumnBase column = null;
                if ((isPk || isUk) && !isRef)
                {
                    column = new PrimaryColumn();
                }
                else if (isRef)
                {
                    column = new ReferenceColumn();
                }
                else
                {
                    column = new NormalColumn();
                }

                column.Name              = FieldUtils.AsText(row, "COLUMN_NAME");
                column.UniqueName        = FieldUtils.AsText(row, "UKNAME");
                column.DataTypeName      = FieldUtils.AsText(row, "DATA_TYPE");
                column.MaxLength         = FieldUtils.AsInt(row, "CHARACTER_MAXIMUM_LENGTH");
                column.NumPrecision      = FieldUtils.AsByte(row, "NUMERIC_PRECISION");
                column.NumPrecisionRadix = FieldUtils.AsInt16(row, "NUMERIC_PRECISION_RADIX");
                column.GenType           = GetGenerateType(column.DataTypeName);
                var setting = default(GenSetting);
                switch (column.GenType)
                {
                case GenerateDataType.String:
                    setting = new GenStringSetting();
                    break;

                case GenerateDataType.DateTime:
                    setting = new GenDateTimeSetting();
                    break;

                case GenerateDataType.Time:
                    setting = new GenDateTimeSetting();
                    break;

                case GenerateDataType.Int:
                    setting = new GenNumSetting();
                    break;

                case GenerateDataType.Long:
                    setting = new GenNumSetting();
                    break;

                case GenerateDataType.Bit:
                    setting = new GenBitSetting();
                    break;

                case GenerateDataType.Uid:
                    setting = new GenSetting();
                    break;

                default:
                    setting = new GenSetting();
                    break;
                }
                column.Setting = setting;
                if (column.ColType.GetType() == typeof(NormalColumn))
                {
                    column.CanNull = FieldUtils.AsBool(row, "IS_NULLABLE");
                }
                else
                {
                    var refCol = column as ReferenceColumn;
                    if (null != refCol)
                    {
                        refCol.CnstName      = FieldUtils.AsText(row, "CNST_NAME");
                        refCol.RefTableName  = FieldUtils.AsText(row, "PARENT");
                        refCol.RefColumnName = FieldUtils.AsText(row, "PARENT_COL");
                    }
                }
                columnList.Add(column);
            }

            LogUtils.Logs.Log("End reading schema column, table:{0}, {1} columns", tableName, columnList.Count);
            return(columnList);
        }