(二)关于定义-CSDN博客

本文链接：https://blog.csdn.net/lzmtw/article/details/1496907

为适用于SQLServer的旧版本(相对9.0即2005而言)和MSAccess,引用了接口和基类定义.

目前重点实现的是SqlServer9.0.

整个项目,核心是AttributeBase和InfoBase,基本上,两者都实现对应的接口,只不过前者侧重标志,后者侧重数据分析.

两者通过静态类InfoAttrConvert进行转换.

重点是定段的分类定义.我现在的做法不一定科学.

我将常用的定段属性定义为一个接口,其它的分别定义.

Namespace LzmTW.DatabaseBuilder
     Public Interface IField
         Property Name() As String
         Property DataType() As Object
         Property DefaultOrBinding() As String
         Property IsNullable() As Boolean
         Property IsPrimary() As Boolean
         Sub CopyFrom( ByVal obj As IField)
     End Interface
End Namespace

其它的,现在已定义的有:

Namespace LzmTW.DatabaseBuilder
     Public Interface IFieldCharLength
         Property Length() As Integer
     End Interface
End Namespace

Namespace LzmTW.DatabaseBuilder
     Public Interface IFieldCompute
         Property Compute() As String
     End Interface
End Namespace

Namespace LzmTW.DatabaseBuilder
     Public Interface IFieldIdentity
         Property IsIdentity() As Boolean
         Property Seed() As Integer
         Property Increment() As Integer
     End Interface
End Namespace

Namespace LzmTW.DatabaseBuilder
     Public Interface IFieldMax
         Property IsMax() As Boolean
     End Interface
End Namespace

Namespace LzmTW.DatabaseBuilder
     Public Interface IFieldScale
         Property Scale() As Integer
         Property Length() As Integer
     End Interface
End Namespace

表:

Namespace LzmTW.DatabaseBuilder
     Public Interface ITable
         Property Schema() As String
         Property Name() As String
         Sub CopyFrom( ByVal obj As ITable)
     End Interface
End Namespace

库:

Namespace LzmTW.DatabaseBuilder
     Public Interface ICatalog
         Property Server() As String
         Property Name() As String
         Sub CopyFrom( ByVal obj As ICatalog)
     End Interface
End Namespace

到于Builder的功能,现在定义的有:

Namespace LzmTW.DatabaseBuilder
     Public Interface IDatabaseBuilder

         ReadOnly Property DbConnection() As Data.Common.DbConnection
         ReadOnly Property Catalog() As CatalogInfoBase

         Sub ReadInfoFrom( Of C As CatalogInfoBase, T As TableInfoBase, F As FieldInfoBase) _
            ( ByVal assembly As Reflection.Assembly)
         Sub ReadInfoFrom( ByVal info As CatalogInfoBase)
         Sub ReadInfoFrom( ByVal database As String )

         Function DropDatabase() As Boolean
         Function CreateDatabaseIfNotExits() As Boolean
         Function CreateDatabaseOverrides() As Boolean

         Function DropSchema( ByVal schema As String ) As Boolean
         Function CreateSchemaIfNotExits( ByVal schema As String ) As Boolean
         Function CreateAllSchemaNotExists() As Boolean

         Function DropTable( ByVal tableFullName As String ) As Boolean
         Function CreateTableIfNotExits( ByVal tableFullName As String ) As Boolean
         Function CreateTableOverrides( ByVal tableFullName As String ) As Boolean
         Function CreateAllTablesNotExits() As Boolean

         Function DropPrimary( ByVal tableFullName As String ) As Boolean
         Function AddPrimary( ByVal tableFullName As String ) As Boolean

         Function CreateAll() As Boolean

         Function GetAllScript() As String

         Function GetAttrCatalogString( Of C As AssemblyCatalogAttributeBase)() As String
         Function GetEnumTableString( Of T As TableAttributeBase)( ByVal TDataType As Type, ByVal fullTableName As String ) As String

     End Interface
End Namespace

哎,贴上来真的会有想法.应该再引进FieldBuilder和TableBuilder更好.

在2005中,取用户表:

SELECT
     a. object_id      [ ObjectID ]
    ,b.name                 [ Schema ]
    ,a.name                 [ Name ]
FROM   sys.tables a
INNER JOIN sys.schemas b
ON a.schema_id = b.schema_id
WHERE a.name <> ' sysdiagrams '
ORDER BY b.name, a.name

取表Object_ID为@ObjectID的字段信息:

SELECT
     Name
    ,DataType
    ,Position
    ,IsNullable
    , CASE WHEN Length IS NULL THEN [ Precision ] ELSE Length END Length
    ,Scale
    ,IsMax
    ,IsIdentity
    ,Seed
    ,Increment
    ,DefaultOrBinding
    ,IsComputed
    , [ Compute ]
    ,IsPrimary
FROM
(
     SELECT
        c. object_id ObjectID,
        c.name,
         ColumnProperty (c. object_id , c.name, ' ordinal ' )  Position,
         convert ( nvarchar ( MAX ),object_definition(c.default_object_id)) DefaultOrBinding,
        c.is_nullable IsNullable,
        type_name(c.system_type_id) DataType,
         ColumnProperty (c. object_id , c.name, ' charmaxlen ' )  Length,
         convert ( tinyint , CASE -- int/decimal/numeric/real/float/money
             WHEN c.system_type_id
             IN ( 48 , 52 , 56 , 59 , 60 , 62 , 106 , 108 , 122 , 127 ) THEN c. precision
             END ) [ Precision ] ,
         convert ( int , CASE -- datetime/smalldatetime
             WHEN c.system_type_id IN ( 58 , 61 ) THEN NULL
             ELSE odbcscale(c.system_type_id, c.scale)
             END ) Scale,
        c.is_identity IsIdentity,
         convert ( bit , CASE WHEN c.default_object_id = 0 Then 0 ELSE 1 END ) HasDefault,
        c.Is_Computed IsComputed,
        i.Seed_Value Seed,
        i.Increment_value InCrement,
        p.definition [ Compute ] ,
         convert ( bit , CASE WHEN ColumnProperty (c. object_id , c.name, ' charmaxlen ' ) = - 1
             Then 1 ELSE 0 END ) IsMax,
         Convert ( bit , CASE WHEN c.Name IN
            (
                 SELECT
                     col_name (c. object_id , c.column_id) AS COLUMN_NAME
                 FROM
                sys.key_constraints k JOIN sys.index_columns c
                 ON c. object_id = k.parent_object_id
                 AND c.index_id = k.unique_index_id
                 JOIN sys.tables t ON t. object_id = k.parent_object_id
                 WHERE c. Object_id = o. object_id
            ) THEN 1 ELSE 0
             END ) ISPrimary
     FROM sys.objects o
     JOIN sys.columns c ON c. object_id = o. object_id
     LEFT JOIN sys.identity_columns i ON i. object_id = o. object_id AND i.Name = c.Name
     LEFT JOIN sys.computed_columns p ON p. object_id = o. object_id AND p.Name = c.Name
     WHERE o. object_id = @ObjectID
) a
;

比如Northwind.Orders信息,如下: