标记法定义和创建数据库

博客园 - 水如烟(LzmTW)

.NET的变量在代码集中是不安全的 HOW TO:获取硬盘物理序列号(VB.NET) 一个创建快捷方式类一个简单的CodeAccessPermission生成器认识一下Attribute HOW TO:端口打印(比较粗糙) 标记法定义和创建数据库(四) Sql2005数据类型与Framework类型的对应关系 HOW TO:重启程序(WinForm) HOW TO:设置默认打印机 HOW TO:避免“将COM对象与其基础RCW分开后不能再使用该对象”错误 HOW TO:控制是否允许用户退出ExcelApplication的Workbook 树和自联表(五) 树和自联表(六) 树和自联表(四) 树和自联表(一) 行政区划数据数据库的设计(脚本) 行政区划数据数据库的设计(七) 行政区划数据数据库的设计(六)

标记法定义和创建数据库

水如烟(LzmTW) · 2007-01-24 · via 博客园 - 水如烟(LzmTW)

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)> _
    SexEnd Enum<Sql9Table("Base", "部门")> _
Public Enum Depart
    <Sql9int("部门ID", IsIdentity:=True, IsPK:=True)> _
    ID<Sql9nvarchar("部门", length:=50, Isnullable:=False)> _
    NameEnd Enum

建库示例代码:

Imports LzmTW
Imports LzmTW.Sql2005Builder
Imports System.ReflectionPublic Class CreateSql9Works
    Private gLoginInformation As New LzmTW.uSystem.uData.uSql.LoginInformationPrivate Function CreateConnection() As SqlClient.SqlConnection
        Return New SqlClient.SqlConnection(gLoginInformation.ConnectionStringBuilder.ConnectionString)
    End FunctionPrivate Function CreateCommand(ByVal sql As String) As SqlClient.SqlCommand
        Return New SqlClient.SqlCommand(sql, Me.CreateConnection)
    End FunctionPrivate Function GetCreateDatabaseString(ByVal catalog As DatabaseBuilder.SqlCatalogInfo) As String
        Return catalog.StringCreateDatabaseOverrides
    End FunctionPrivate Function GetCreateTablesString(ByVal catalog As DatabaseBuilder.SqlCatalogInfo) As String
        Return catalog.StringCreateTablesOverrides
    End FunctionPrivate 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 UsingReturn True
    End FunctionPrivate 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 FunctionPublic 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,1) NOT NULL,
      [姓名] [nvarchar] (50) NOT 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,1) NOT NULL,
      [部门] [nvarchar] (50) NOT NULL

)
;

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

)
;

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

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

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

取数据库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

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - 水如烟(LzmTW)