水如烟

                 顺其自然,水到渠成 LzmTW

文或代码皆是面向初学者.我是爱好者,也是初学者.那些"文章",只按自己理解写,我是不知术语名词的.所以只供参考,也仅供参考.

导航

标记法定义和创建数据库

Posted on 2007-01-24 17:02  水如烟(LzmTW)  阅读(1192)  评论(2编辑  收藏  举报

Author:水如烟  20070124

这种建库方法我称之为标记法.

现在建立数据库的轮廓做出来了:

建一个类项目,生成后是Sql2005BuilderDemo.dll文件,
这个项目的Assembly标记上

<Assembly: AssemblySql9CatalogAttribute("Sql9Works""(local)")> 

定义两个表:

<Sql9Table("Base""职员")> _
Public Enum Employee
    
<Sql9int("职员ID", IsIdentity:=True, IsPK:=True)> _
    ID

    
<Sql9nvarchar("姓名", length:=50, Isnullable:=False)> _
    Name

    
<Sql9bit("性别", DefaultOrBinding:=1)> _
    Sex

End Enum


<Sql9Table("Base""部门")> _
Public Enum Depart
    
<Sql9int("部门ID", IsIdentity:=True, IsPK:=True)> _
    ID

    
<Sql9nvarchar("部门", length:=50, Isnullable:=False)> _
    Name

End Enum

建库示例代码:

Imports LzmTW
Imports LzmTW.Sql2005Builder
Imports System.Reflection

Public Class CreateSql9Works
    
Private gLoginInformation As New LzmTW.uSystem.uData.uSql.LoginInformation

    
Private Function CreateConnection() As SqlClient.SqlConnection
        
Return New SqlClient.SqlConnection(gLoginInformation.ConnectionStringBuilder.ConnectionString)
    
End Function

    
Private Function CreateCommand(ByVal sql As StringAs SqlClient.SqlCommand
        
Return New SqlClient.SqlCommand(sql, Me.CreateConnection)
    
End Function

    
Private Function GetCreateDatabaseString(ByVal catalog As DatabaseBuilder.SqlCatalogInfo) As String
        
Return catalog.StringCreateDatabaseOverrides
    
End Function

    
Private Function GetCreateTablesString(ByVal catalog As DatabaseBuilder.SqlCatalogInfo) As String
        
Return catalog.StringCreateTablesOverrides
    
End Function

    
Private Function CreateDatabase(ByVal catalog As DatabaseBuilder.SqlCatalogInfo) As Boolean
        
Me.gLoginInformation.Database = "master" '即Use master
        Using cm As SqlClient.SqlCommand = Me.CreateCommand(GetCreateDatabaseString(catalog))
            cm.Connection.Open()
            
Try
                Console.WriteLine(cm.CommandText)
                cm.ExecuteNonQuery()
            
Catch ex As Exception
                Console.WriteLine(ex.Message)
                
Return False
            
Finally
                cm.Connection.Close()
            
End Try
        
End Using

        Console.WriteLine()

        
Me.gLoginInformation.Database = catalog.CatalogName '即Use Sql9Works
        Using cm As SqlClient.SqlCommand = Me.CreateCommand(GetCreateTablesString(catalog))
            cm.Connection.Open()
            
Try
                Console.WriteLine(cm.CommandText)
                cm.ExecuteNonQuery()
            
Catch ex As Exception
                Console.WriteLine(ex.Message)
                
Return False
            
Finally
                cm.Connection.Close()
            
End Try
        
End Using

        
Return True
    
End Function

    
Private Function Sql9WorksCatalog() As DatabaseBuilder.SqlCatalogInfo
        
Dim ass As Reflection.Assembly = Reflection.Assembly.LoadFrom("Sql2005BuilderDemo.dll")
        
Dim info As New DatabaseBuilder.SqlDbaseInfo
        info.LoadFrom(ass)
        
Return info.Catalog
    
End Function

    
Public Function ReCreateDatabase() As Boolean
        
Return Me.CreateDatabase(Me.Sql9WorksCatalog)
    
End Function
End Class

调用:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button1.Click
    
Dim demo As New CreateSql9Works
    demo.ReCreateDatabase()
End Sub

就可以生成数据库Sql9Works了.
Console出来的字串如下:

 

      USE master
      ;
      
IF  EXISTS
      (
      
SELECT *
      
FROM sys.databases
      
WHERE name = N'Sql9Works'
      )
      
DROP DATABASE Sql9Works
      ;
      
CREATE DATABASE Sql9Works
      ;
    


      
IF NOT EXISTS
      (
      
SELECT *
      
FROM sys.schemas
      
WHERE name = N'Base'
      )
      
EXEC sys.sp_executesql N'CREATE SCHEMA [Base] AUTHORIZATION [dbo]'
      ;
    

      
IF  EXISTS
      (
      
SELECT *
      
FROM sys.objects
      
WHERE object_id = OBJECT_ID(N'[Base].[职员]')
      
AND type in (N'U')
      )
      
DROP TABLE [Base].[职员]
      ;
      
CREATE TABLE [Base].[职员]
      (
      
[职员ID] [int] IDENTITY(1,1NOT NULL,
      
[姓名] [nvarchar] (50NOT NULL ,
      
[性别] [bit] 

      )
      ;
    

      
ALTER TABLE [Base].[职员]
      
ADD CONSTRAINT [PK_职员] PRIMARY KEY
      (
      职员ID

      )
      ;
    

      
IF NOT EXISTS
      (
      
SELECT *
      
FROM sys.schemas
      
WHERE name = N'Base'
      )
      
EXEC sys.sp_executesql N'CREATE SCHEMA [Base] AUTHORIZATION [dbo]'
      ;
    

      
IF  EXISTS
      (
      
SELECT *
      
FROM sys.objects
      
WHERE object_id = OBJECT_ID(N'[Base].[部门]')
      
AND type in (N'U')
      )
      
DROP TABLE [Base].[部门]
      ;
      
CREATE TABLE [Base].[部门]
      (
      
[部门ID] [int] IDENTITY(1,1NOT NULL,
      
[部门] [nvarchar] (50NOT NULL 

      )
      ;
    

      
ALTER TABLE [Base].[部门]
      
ADD CONSTRAINT [PK_部门] PRIMARY KEY
      (
      部门ID

      )
      ;

这样做好处是,数据库是由类(虽说是枚举)

 

<Sql9Table("Base""职员")> _
Public Enum Employee
    
<Sql9int("职员ID", IsIdentity:=True, IsPK:=True)> _
    ID

    
<Sql9nvarchar("姓名", length:=50, Isnullable:=False)> _
    Name

    
<Sql9bit("性别", DefaultOrBinding:=1)> _
    Sex

End Enum

定义的.它包含了表的基本信息(外键约束现在还没考虑).
据此可以生成实体类等等.

因为它信息丰富,甚至可以据此信息直接来读写数据库了.




取数据库Northwind信息,结果为:

<Assembly: AssemblySql9Catalog(Name:="Northwind",Server:=".\SQLExpress")>

<Sql9Table(Name:="Categories",Schema:="dbo")> _
Public Enum Categories
    
<Sql9int(Name:="CategoryID",IsPrimary:=True,IsNullable:=False,IsIdentity:=True)> _
    [CategoryID]

    
<Sql9nvarchar(Name:="CategoryName",IsNullable:=False,Length:=15)> _
    [CategoryName]

    
<Sql9ntext(Name:="Description")> _
    [Description]

    
<Sql9image(Name:="Picture")> _
    [Picture]

End Enum



<Sql9Table(Name:="CustomerCustomerDemo",Schema:="dbo")> _
Public Enum CustomerCustomerDemo
    
<Sql9nchar(Name:="CustomerID",IsPrimary:=True,IsNullable:=False,Length:=5)> _
    [CustomerID]

    
<Sql9nchar(Name:="CustomerTypeID",IsPrimary:=True,IsNullable:=False,Length:=10)> _
    [CustomerTypeID]

End Enum



<Sql9Table(Name:="CustomerDemographics",Schema:="dbo")> _
Public Enum CustomerDemographics
    
<Sql9nchar(Name:="CustomerTypeID",IsPrimary:=True,IsNullable:=False,Length:=10)> _
    [CustomerTypeID]

    
<Sql9ntext(Name:="CustomerDesc")> _
    [CustomerDesc]

End Enum



<Sql9Table(Name:="Customers",Schema:="dbo")> _
Public Enum Customers
    
<Sql9nchar(Name:="CustomerID",IsPrimary:=True,IsNullable:=False,Length:=5)> _
    [CustomerID]

    
<Sql9nvarchar(Name:="CompanyName",IsNullable:=False,Length:=40)> _
    [CompanyName]

    
<Sql9nvarchar(Name:="ContactName",Length:=30)> _
    [ContactName]

    
<Sql9nvarchar(Name:="ContactTitle",Length:=30)> _
    [ContactTitle]

    
<Sql9nvarchar(Name:="Address",Length:=60)> _
    [Address]

    
<Sql9nvarchar(Name:="City",Length:=15)> _
    [City]

    
<Sql9nvarchar(Name:="Region",Length:=15)> _
    [Region]

    
<Sql9nvarchar(Name:="PostalCode",Length:=10)> _
    [PostalCode]

    
<Sql9nvarchar(Name:="Country",Length:=15)> _
    [Country]

    
<Sql9nvarchar(Name:="Phone",Length:=24)> _
    [Phone]

    
<Sql9nvarchar(Name:="Fax",Length:=24)> _
    [Fax]

End Enum



<Sql9Table(Name:="Employees",Schema:="dbo")> _
Public Enum Employees
    
<Sql9int(Name:="EmployeeID",IsPrimary:=True,IsNullable:=False,IsIdentity:=True)> _
    [EmployeeID]

    
<Sql9nvarchar(Name:="LastName",IsNullable:=False,Length:=20)> _
    [LastName]

    
<Sql9nvarchar(Name:="FirstName",IsNullable:=False,Length:=10)> _
    [FirstName]

    
<Sql9nvarchar(Name:="Title",Length:=30)> _
    [Title]

    
<Sql9nvarchar(Name:="TitleOfCourtesy",Length:=25)> _
    [TitleOfCourtesy]

    
<Sql9datetime(Name:="BirthDate")> _
    [BirthDate]

    
<Sql9datetime(Name:="HireDate")> _
    [HireDate]

    
<Sql9nvarchar(Name:="Address",Length:=60)> _
    [Address]

    
<Sql9nvarchar(Name:="City",Length:=15)> _
    [City]

    
<Sql9nvarchar(Name:="Region",Length:=15)> _
    [Region]

    
<Sql9nvarchar(Name:="PostalCode",Length:=10)> _
    [PostalCode]

    
<Sql9nvarchar(Name:="Country",Length:=15)> _
    [Country]

    
<Sql9nvarchar(Name:="HomePhone",Length:=24)> _
    [HomePhone]

    
<Sql9nvarchar(Name:="Extension",Length:=4)> _
    [Extension]

    
<Sql9image(Name:="Photo")> _
    [Photo]

    
<Sql9ntext(Name:="Notes")> _
    [Notes]

    
<Sql9int(Name:="ReportsTo")> _
    [ReportsTo]

    
<Sql9nvarchar(Name:="PhotoPath",Length:=255)> _
    [PhotoPath]

End Enum



<Sql9Table(Name:="EmployeeTerritories",Schema:="dbo")> _
Public Enum EmployeeTerritories
    
<Sql9int(Name:="EmployeeID",IsPrimary:=True,IsNullable:=False)> _
    [EmployeeID]

    
<Sql9nvarchar(Name:="TerritoryID",IsPrimary:=True,IsNullable:=False,Length:=20)> _
    [TerritoryID]

End Enum



<Sql9Table(Name:="Order Details",Schema:="dbo")> _
Public Enum Order Details
    
<Sql9int(Name:="OrderID",IsPrimary:=True,IsNullable:=False)> _
    [OrderID]

    
<Sql9int(Name:="ProductID",IsPrimary:=True,IsNullable:=False)> _
    [ProductID]

    
<Sql9money(Name:="UnitPrice",IsNullable:=False,DefaultOrBinding:="((0))")> _
    [UnitPrice]

    
<Sql9smallint(Name:="Quantity",IsNullable:=False,DefaultOrBinding:="((1))")> _
    [Quantity]

    
<Sql9real(Name:="Discount",IsNullable:=False,DefaultOrBinding:="((0))")> _
    [Discount]

End Enum



<Sql9Table(Name:="Orders",Schema:="dbo")> _
Public Enum Orders
    
<Sql9int(Name:="OrderID",IsPrimary:=True,IsNullable:=False,IsIdentity:=True)> _
    [OrderID]

    
<Sql9nchar(Name:="CustomerID",Length:=5)> _
    [CustomerID]

    
<Sql9int(Name:="EmployeeID")> _
    [EmployeeID]

    
<Sql9datetime(Name:="OrderDate")> _
    [OrderDate]

    
<Sql9datetime(Name:="RequiredDate")> _
    [RequiredDate]

    
<Sql9datetime(Name:="ShippedDate")> _
    [ShippedDate]

    
<Sql9int(Name:="ShipVia")> _
    [ShipVia]

    
<Sql9money(Name:="Freight",DefaultOrBinding:="((0))")> _
    [Freight]

    
<Sql9nvarchar(Name:="ShipName",Length:=40)> _
    [ShipName]

    
<Sql9nvarchar(Name:="ShipAddress",Length:=60)> _
    [ShipAddress]

    
<Sql9nvarchar(Name:="ShipCity",Length:=15)> _
    [ShipCity]

    
<Sql9nvarchar(Name:="ShipRegion",Length:=15)> _
    [ShipRegion]

    
<Sql9nvarchar(Name:="ShipPostalCode",Length:=10)> _
    [ShipPostalCode]

    
<Sql9nvarchar(Name:="ShipCountry",Length:=15)> _
    [ShipCountry]

End Enum



<Sql9Table(Name:="Products",Schema:="dbo")> _
Public Enum Products
    
<Sql9int(Name:="ProductID",IsPrimary:=True,IsNullable:=False,IsIdentity:=True)> _
    [ProductID]

    
<Sql9nvarchar(Name:="ProductName",IsNullable:=False,Length:=40)> _
    [ProductName]

    
<Sql9int(Name:="SupplierID")> _
    [SupplierID]

    
<Sql9int(Name:="CategoryID")> _
    [CategoryID]

    
<Sql9nvarchar(Name:="QuantityPerUnit",Length:=20)> _
    [QuantityPerUnit]

    
<Sql9money(Name:="UnitPrice",DefaultOrBinding:="((0))")> _
    [UnitPrice]

    
<Sql9smallint(Name:="UnitsInStock",DefaultOrBinding:="((0))")> _
    [UnitsInStock]

    
<Sql9smallint(Name:="UnitsOnOrder",DefaultOrBinding:="((0))")> _
    [UnitsOnOrder]

    
<Sql9smallint(Name:="ReorderLevel",DefaultOrBinding:="((0))")> _
    [ReorderLevel]

    
<Sql9bit(Name:="Discontinued",IsNullable:=False,DefaultOrBinding:="((0))")> _
    [Discontinued]

End Enum



<Sql9Table(Name:="Region",Schema:="dbo")> _
Public Enum Region
    
<Sql9int(Name:="RegionID",IsPrimary:=True,IsNullable:=False)> _
    [RegionID]

    
<Sql9nchar(Name:="RegionDescription",IsNullable:=False,Length:=50)> _
    [RegionDescription]

End Enum



<Sql9Table(Name:="Shippers",Schema:="dbo")> _
Public Enum Shippers
    
<Sql9int(Name:="ShipperID",IsPrimary:=True,IsNullable:=False,IsIdentity:=True)> _
    [ShipperID]

    
<Sql9nvarchar(Name:="CompanyName",IsNullable:=False,Length:=40)> _
    [CompanyName]

    
<Sql9nvarchar(Name:="Phone",Length:=24)> _
    [Phone]

End Enum



<Sql9Table(Name:="Suppliers",Schema:="dbo")> _
Public Enum Suppliers
    
<Sql9int(Name:="SupplierID",IsPrimary:=True,IsNullable:=False,IsIdentity:=True)> _
    [SupplierID]

    
<Sql9nvarchar(Name:="CompanyName",IsNullable:=False,Length:=40)> _
    [CompanyName]

    
<Sql9nvarchar(Name:="ContactName",Length:=30)> _
    [ContactName]

    
<Sql9nvarchar(Name:="ContactTitle",Length:=30)> _
    [ContactTitle]

    
<Sql9nvarchar(Name:="Address",Length:=60)> _
    [Address]

    
<Sql9nvarchar(Name:="City",Length:=15)> _
    [City]

    
<Sql9nvarchar(Name:="Region",Length:=15)> _
    [Region]

    
<Sql9nvarchar(Name:="PostalCode",Length:=10)> _
    [PostalCode]

    
<Sql9nvarchar(Name:="Country",Length:=15)> _
    [Country]

    
<Sql9nvarchar(Name:="Phone",Length:=24)> _
    [Phone]

    
<Sql9nvarchar(Name:="Fax",Length:=24)> _
    [Fax]

    
<Sql9ntext(Name:="HomePage")> _
    [HomePage]

End Enum



<Sql9Table(Name:="Territories",Schema:="dbo")> _
Public Enum Territories
    
<Sql9nvarchar(Name:="TerritoryID",IsPrimary:=True,IsNullable:=False,Length:=20)> _
    [TerritoryID]

    
<Sql9nchar(Name:="TerritoryDescription",IsNullable:=False,Length:=50)> _
    [TerritoryDescription]

    
<Sql9int(Name:="RegionID",IsNullable:=False)> _
    [RegionID]

End Enum