关系数据库元数据处理类(一) 创建MSSQL元数据具体处理类

             1
            public
            class
             SqlServer : BaseMetadata

             2
                {

             3
             4
            public SqlServer(string connectionString)
 5             : base(new DbUtility(connectionString, DbProviderType.SqlServer))
 6        {
 7 8        }
 910protectedoverridestring GetDataBaseSting()
11        {
12return"Select name FROM Master..SysDatabases order by Name";
13        }
1415protectedoverridestring GetDataTableString(string database = "")
16        {
17return"SELECT " +
18" Name = case when a.colorder = 1 then d.name else ‘‘ end," + //表名称19" Explain = case when a.colorder = 1 then isnull(f.value, ‘‘) else ‘‘ end " + //表说明20" FROM syscolumns a " +
21" inner join sysobjects d on a.id = d.id and d.xtype = ‘U‘ and d.name <> ‘sys.extended_properties‘ " +
22" left join sys.extended_properties f on a.id = f.major_id and f.minor_id = 0 " +
23" Where (case when a.colorder = 1 then d.name else ‘‘ end) <>‘‘";
24        }
2526protectedoverridestring GetDataColumnString()
27        {
28return"SELECT  " +
29"TableName= d.name," +
30"Name = a.name, " +
31"IsMarke =(case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1 then ‘1‘else ‘0‘ end)," +
32"IsPrimarykey= (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = ‘PK‘))>0 then ‘1‘ else ‘0‘ end)," +
33"Type =b.name," +
34"Bytes=a.length, " +
35"Length=COLUMNPROPERTY(a.id,a.name,‘PRECISION‘)," +
36"DecimalPlaces = isnull(COLUMNPROPERTY(a.id,a.name,‘Scale‘),0)," +
37"IsNull=(case when a.isnullable=1 then ‘1‘else ‘0‘ end), " +
38"DefaultValue=isnull(e.text,‘‘)," +
39"Explain=isnull(g.[value], ‘ ‘)" +
40" FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype=‘U‘ and d.name<>‘dtproperties‘ left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id left join sys.extended_properties f on d.id=f.class and f.minor_id=0 where b.name is not null --WHERE d.name=‘要查询的表‘ --如果只查询指定表,加上此条件 order by a.id,a.colorder";
41        }
4243protectedoverridestring GetDataRelationString()
44        {
45return46"SELECT 外键表ID = b.fkeyid , ForeignTableName = object_name(b.fkeyid) , 外键列ID = b.fkey , ForeignKeyName = (SELECT name FROM syscolumns WHERE colid = b.fkey AND id = b.fkeyid) , 主键表ID = b.rkeyid , PrimaryTableName = object_name(b.rkeyid) , 主键列ID = b.rkey , PrimaryKeyName = (SELECT name FROM syscolumns WHERE colid = b.rkey AND id = b.rkeyid) , 级联更新 = ObjectProperty(a.id,‘CnstIsUpdateCascade‘) , 级联删除 = ObjectProperty(a.id,‘CnstIsDeleteCascade‘) FROM sysobjects a JOIN sysforeignkeys b ON a.id = b.constid JOIN sysobjects c ON a.parent_obj = c.id WHERE a.xtype = ‘F‘ AND c.xtype = ‘U‘ ";
47        }
4849protectedoverride List<Database> ConversionDatabase(DataTable table)
50        {
51return Mapper.DynamicMap<IDataReader, List<Database>>(table.CreateDataReader());
52        }
5354protectedoverride List<Table> ConversionDataTable(DataTable table)
55        {
56return Mapper.DynamicMap<IDataReader, List<Table>>(table.CreateDataReader());
57        }
5859protectedoverride List<Column> ConversionColumn(DataTable table)
60        {
61return Mapper.DynamicMap<IDataReader, List<Column>>(table.CreateDataReader());
62        }
6364protectedoverride List<Relation> ConversionDataRelation(DataTable table)
65        {
66return Mapper.DynamicMap<IDataReader, List<Relation>>(table.CreateDataReader());
67        }
68     }