関係データベースメタデータ処理クラス(一)MSSQLメタデータ具体処理クラスの作成
12851 ワード
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 }