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

 1 public class SqlServer : BaseMetadata
 2     {
 3 
 4         public SqlServer(string connectionString)
 5             : base(new DbUtility(connectionString, DbProviderType.SqlServer))
 6         {
 7 
 8         }
 9 
10         protected override string GetDataBaseSting()
11         {
12             return "Select name FROM Master..SysDatabases order by Name";
13         }
14 
15         protected override string GetDataTableString(string database = "")
16         {
17             return "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         }
25 
26         protected override string GetDataColumnString()
27         {
28             return "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         }
42 
43         protected override string GetDataRelationString()
44         {
45             return
46                 "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         }
48 
49         protected override List<Database> ConversionDatabase(DataTable table)
50         {
51             return Mapper.DynamicMap<IDataReader, List<Database>>(table.CreateDataReader());
52         }
53 
54         protected override List<Table> ConversionDataTable(DataTable table)
55         {
56             return Mapper.DynamicMap<IDataReader, List<Table>>(table.CreateDataReader());
57         }
58 
59         protected override List<Column> ConversionColumn(DataTable table)
60         {
61             return Mapper.DynamicMap<IDataReader, List<Column>>(table.CreateDataReader());
62         }
63 
64         protected override List<Relation> ConversionDataRelation(DataTable table)
65         {
66             return Mapper.DynamicMap<IDataReader, List<Relation>>(table.CreateDataReader());
67         }
68     }