Author：水如烟
为适用于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 NamespaceNamespace 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信息,如下:
