GZFramwork数据库层《前言》Demo简介

时间:2022-09-12 07:33:53

本系列旨在熟悉GZFramwork数据库层操作,对数据库表进行增删改查,单据编号生成等:

详细见图:

GZFramwork数据库层《前言》Demo简介

普通单表操作:

GZFramwork数据库层《前言》Demo简介

 

GZFramwork数据库层《前言》Demo简介

 

 

GZFramwork数据库层《前言》Demo简介

GZFramwork数据库层《前言》Demo简介

 

GZFramwork数据库层《前言》Demo简介

 

数据库建模:

GZFramwork数据库层《前言》Demo简介

 

创建表脚本:

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('tb_CustomerDetail') and o.name = 'FK_TB_CUSTO_REFERENCE_TB_CUSTO')
alter table tb_CustomerDetail
   drop constraint FK_TB_CUSTO_REFERENCE_TB_CUSTO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('tb_DictionaryDetail') and o.name = 'FK_TB_DICTI_REFERENCE_TB_DICTI')
alter table tb_DictionaryDetail
   drop constraint FK_TB_DICTI_REFERENCE_TB_DICTI
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('tb_EmployeeCon') and o.name = 'FK_TB_EMPLO_REFERENCE_TB_EMPLO')
alter table tb_EmployeeCon
   drop constraint FK_TB_EMPLO_REFERENCE_TB_EMPLO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('_tmp')
            and   type = 'U')
   drop table _tmp
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_Customer')
            and   type = 'U')
   drop table tb_Customer
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_CustomerDetail')
            and   type = 'U')
   drop table tb_CustomerDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_Dictionary')
            and   type = 'U')
   drop table tb_Dictionary
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_DictionaryDetail')
            and   type = 'U')
   drop table tb_DictionaryDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_EmpLeave')
            and   type = 'U')
   drop table tb_EmpLeave
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_Employee')
            and   type = 'U')
   drop table tb_Employee
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_EmployeeCon')
            and   type = 'U')
   drop table tb_EmployeeCon
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_MyUser')
            and   type = 'U')
   drop table tb_MyUser
go

/*==============================================================*/
/* Table: _tmp                                                  */
/*==============================================================*/
create table _tmp (
   isid                 int                  identity(1,1) not for replication,
   CreateUser           varchar(20)          null,
   CreateDate           datetime             null,
   LastUpdateUser       varchar(20)          null,
   LastUpdateDate       datetime             null
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '表公共字段',
   'user', @CurrentUser, 'table', '_tmp'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '自增列',
   'user', @CurrentUser, 'table', '_tmp', 'column', 'isid'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建人',
   'user', @CurrentUser, 'table', '_tmp', 'column', 'CreateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建日期',
   'user', @CurrentUser, 'table', '_tmp', 'column', 'CreateDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改人',
   'user', @CurrentUser, 'table', '_tmp', 'column', 'LastUpdateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改日期',
   'user', @CurrentUser, 'table', '_tmp', 'column', 'LastUpdateDate'
go

/*==============================================================*/
/* Table: tb_Customer                                           */
/*==============================================================*/
create table tb_Customer (
   isid                 int                  identity(1,1) not for replication,
   CustomerCode         varchar(20)          not null,
   CustomerName         varchar(50)          null,
   Adress               nvarchar(100)        null,
   CreateUser           varchar(20)          null,
   CreateDate           datetime             null,
   LastUpdateUser       varchar(20)          null,
   LastUpdateDate       datetime             null,
   constraint PK_TB_CUSTOMER primary key (CustomerCode)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '客户',
   'user', @CurrentUser, 'table', 'tb_Customer'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '自增列',
   'user', @CurrentUser, 'table', 'tb_Customer', 'column', 'isid'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '客户编号',
   'user', @CurrentUser, 'table', 'tb_Customer', 'column', 'CustomerCode'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '客户名称',
   'user', @CurrentUser, 'table', 'tb_Customer', 'column', 'CustomerName'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '地址',
   'user', @CurrentUser, 'table', 'tb_Customer', 'column', 'Adress'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建人',
   'user', @CurrentUser, 'table', 'tb_Customer', 'column', 'CreateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建日期',
   'user', @CurrentUser, 'table', 'tb_Customer', 'column', 'CreateDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改人',
   'user', @CurrentUser, 'table', 'tb_Customer', 'column', 'LastUpdateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改日期',
   'user', @CurrentUser, 'table', 'tb_Customer', 'column', 'LastUpdateDate'
go

/*==============================================================*/
/* Table: tb_CustomerDetail                                     */
/*==============================================================*/
create table tb_CustomerDetail (
   isid                 int                  identity(1,1) not for replication,
   CustomerCode         varchar(20)          null,
   CustomerAttribute    varchar(50)          null,
   CustomerATValue      nvarchar(100)        null,
   CreateUser           varchar(20)          null,
   CreateDate           datetime             null,
   LastUpdateUser       varchar(20)          null,
   LastUpdateDate       datetime             null,
   constraint PK_TB_CUSTOMERDETAIL primary key (isid)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '客户明细',
   'user', @CurrentUser, 'table', 'tb_CustomerDetail'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '自增列',
   'user', @CurrentUser, 'table', 'tb_CustomerDetail', 'column', 'isid'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '客户编号',
   'user', @CurrentUser, 'table', 'tb_CustomerDetail', 'column', 'CustomerCode'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '属性',
   'user', @CurrentUser, 'table', 'tb_CustomerDetail', 'column', 'CustomerAttribute'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '属性值',
   'user', @CurrentUser, 'table', 'tb_CustomerDetail', 'column', 'CustomerATValue'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建人',
   'user', @CurrentUser, 'table', 'tb_CustomerDetail', 'column', 'CreateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建日期',
   'user', @CurrentUser, 'table', 'tb_CustomerDetail', 'column', 'CreateDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改人',
   'user', @CurrentUser, 'table', 'tb_CustomerDetail', 'column', 'LastUpdateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改日期',
   'user', @CurrentUser, 'table', 'tb_CustomerDetail', 'column', 'LastUpdateDate'
go

/*==============================================================*/
/* Table: tb_Dictionary                                         */
/*==============================================================*/
create table tb_Dictionary (
   TypeID               varchar(10)          not null,
   TypeName             varchar(20)          null,
   Remark               varchar(200)         null,
   isid                 int                  identity(1,1) not for replication,
   CreateUser           varchar(20)          null,
   CreateDate           datetime             null,
   LastUpdateUser       varchar(20)          null,
   LastUpdateDate       datetime             null,
   constraint PK_TB_DICTIONARY primary key (TypeID)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '数据字典主表',
   'user', @CurrentUser, 'table', 'tb_Dictionary'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '类别编号',
   'user', @CurrentUser, 'table', 'tb_Dictionary', 'column', 'TypeID'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '类别名称',
   'user', @CurrentUser, 'table', 'tb_Dictionary', 'column', 'TypeName'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '备注',
   'user', @CurrentUser, 'table', 'tb_Dictionary', 'column', 'Remark'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '自增列',
   'user', @CurrentUser, 'table', 'tb_Dictionary', 'column', 'isid'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建人',
   'user', @CurrentUser, 'table', 'tb_Dictionary', 'column', 'CreateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建日期',
   'user', @CurrentUser, 'table', 'tb_Dictionary', 'column', 'CreateDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改人',
   'user', @CurrentUser, 'table', 'tb_Dictionary', 'column', 'LastUpdateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改日期',
   'user', @CurrentUser, 'table', 'tb_Dictionary', 'column', 'LastUpdateDate'
go

/*==============================================================*/
/* Table: tb_DictionaryDetail                                   */
/*==============================================================*/
create table tb_DictionaryDetail (
   isid                 int                  identity(1,1) not for replication,
   TypeID               varchar(10)          null,
   DataCode             varchar(10)          null,
   DataName             varchar(20)          null,
   CreateUser           varchar(20)          null,
   CreateDate           datetime             null,
   LastUpdateUser       varchar(20)          null,
   LastUpdateDate       datetime             null,
   constraint PK_TB_DICTIONARYDETAIL primary key (isid)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '数据字典明细',
   'user', @CurrentUser, 'table', 'tb_DictionaryDetail'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '自增列',
   'user', @CurrentUser, 'table', 'tb_DictionaryDetail', 'column', 'isid'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '类别编号',
   'user', @CurrentUser, 'table', 'tb_DictionaryDetail', 'column', 'TypeID'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '编号',
   'user', @CurrentUser, 'table', 'tb_DictionaryDetail', 'column', 'DataCode'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '字典名称',
   'user', @CurrentUser, 'table', 'tb_DictionaryDetail', 'column', 'DataName'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建人',
   'user', @CurrentUser, 'table', 'tb_DictionaryDetail', 'column', 'CreateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建日期',
   'user', @CurrentUser, 'table', 'tb_DictionaryDetail', 'column', 'CreateDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改人',
   'user', @CurrentUser, 'table', 'tb_DictionaryDetail', 'column', 'LastUpdateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改日期',
   'user', @CurrentUser, 'table', 'tb_DictionaryDetail', 'column', 'LastUpdateDate'
go

/*==============================================================*/
/* Table: tb_EmpLeave                                           */
/*==============================================================*/
create table tb_EmpLeave (
   isid                 int                  identity(1,1) not for replication,
   DocNo                varchar(20)          not null,
   EmpName              varchar(20)          null,
   BeginDate            datetime             null,
   EndDate              datetime             null,
   CreateUser           varchar(20)          null,
   CreateDate           datetime             null,
   LastUpdateUser       varchar(20)          null,
   LastUpdateDate       datetime             null,
   constraint PK_TB_EMPLEAVE primary key (DocNo)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '请假记录',
   'user', @CurrentUser, 'table', 'tb_EmpLeave'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '自增列',
   'user', @CurrentUser, 'table', 'tb_EmpLeave', 'column', 'isid'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '请假单号',
   'user', @CurrentUser, 'table', 'tb_EmpLeave', 'column', 'DocNo'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '请假人',
   'user', @CurrentUser, 'table', 'tb_EmpLeave', 'column', 'EmpName'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '请假开始日期',
   'user', @CurrentUser, 'table', 'tb_EmpLeave', 'column', 'BeginDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '请假结束日期',
   'user', @CurrentUser, 'table', 'tb_EmpLeave', 'column', 'EndDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建人',
   'user', @CurrentUser, 'table', 'tb_EmpLeave', 'column', 'CreateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建日期',
   'user', @CurrentUser, 'table', 'tb_EmpLeave', 'column', 'CreateDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改人',
   'user', @CurrentUser, 'table', 'tb_EmpLeave', 'column', 'LastUpdateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改日期',
   'user', @CurrentUser, 'table', 'tb_EmpLeave', 'column', 'LastUpdateDate'
go

/*==============================================================*/
/* Table: tb_Employee                                           */
/*==============================================================*/
create table tb_Employee (
   isid                 int                  identity(1,1) not for replication,
   InfoID               varchar(10)          not null,
   EmpName              varchar(20)          null,
   Sex                  varchar(2)           null,
   Age                  int                  null,
   CreateUser           varchar(20)          null,
   CreateDate           datetime             null,
   LastUpdateUser       varchar(20)          null,
   LastUpdateDate       datetime             null,
   constraint PK_TB_EMPLOYEE primary key (InfoID)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '员工表',
   'user', @CurrentUser, 'table', 'tb_Employee'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '自增列',
   'user', @CurrentUser, 'table', 'tb_Employee', 'column', 'isid'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '员工编号',
   'user', @CurrentUser, 'table', 'tb_Employee', 'column', 'InfoID'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '姓名',
   'user', @CurrentUser, 'table', 'tb_Employee', 'column', 'EmpName'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '性别',
   'user', @CurrentUser, 'table', 'tb_Employee', 'column', 'Sex'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '年龄',
   'user', @CurrentUser, 'table', 'tb_Employee', 'column', 'Age'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建人',
   'user', @CurrentUser, 'table', 'tb_Employee', 'column', 'CreateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建日期',
   'user', @CurrentUser, 'table', 'tb_Employee', 'column', 'CreateDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改人',
   'user', @CurrentUser, 'table', 'tb_Employee', 'column', 'LastUpdateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改日期',
   'user', @CurrentUser, 'table', 'tb_Employee', 'column', 'LastUpdateDate'
go

/*==============================================================*/
/* Table: tb_EmployeeCon                                        */
/*==============================================================*/
create table tb_EmployeeCon (
   isid                 int                  identity(1,1) not for replication,
   InfoID               varchar(10)          null,
   ConNo                varchar(10)          not null,
   BeginDate            datetime             null,
   EndDate              datetime             null,
   Salary               int                  null,
   CreateUser           varchar(20)          null,
   CreateDate           datetime             null,
   LastUpdateUser       varchar(20)          null,
   LastUpdateDate       datetime             null,
   constraint PK_TB_EMPLOYEECON primary key nonclustered (ConNo)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '员工合同表',
   'user', @CurrentUser, 'table', 'tb_EmployeeCon'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '自增列',
   'user', @CurrentUser, 'table', 'tb_EmployeeCon', 'column', 'isid'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '员工编号',
   'user', @CurrentUser, 'table', 'tb_EmployeeCon', 'column', 'InfoID'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '合同编号',
   'user', @CurrentUser, 'table', 'tb_EmployeeCon', 'column', 'ConNo'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '合同开始日期',
   'user', @CurrentUser, 'table', 'tb_EmployeeCon', 'column', 'BeginDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '合同结束日期',
   'user', @CurrentUser, 'table', 'tb_EmployeeCon', 'column', 'EndDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '最低工资',
   'user', @CurrentUser, 'table', 'tb_EmployeeCon', 'column', 'Salary'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建人',
   'user', @CurrentUser, 'table', 'tb_EmployeeCon', 'column', 'CreateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建日期',
   'user', @CurrentUser, 'table', 'tb_EmployeeCon', 'column', 'CreateDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改人',
   'user', @CurrentUser, 'table', 'tb_EmployeeCon', 'column', 'LastUpdateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改日期',
   'user', @CurrentUser, 'table', 'tb_EmployeeCon', 'column', 'LastUpdateDate'
go

/*==============================================================*/
/* Table: tb_MyUser                                             */
/*==============================================================*/
create table tb_MyUser (
   isid                 int                  identity(1,1) not for replication,
   Account              varchar(20)          not null,
   UserName             varchar(20)          null,
   PetName              varchar(20)          null,
   CreateUser           varchar(20)          null,
   CreateDate           datetime             null,
   LastUpdateUser       varchar(20)          null,
   LastUpdateDate       datetime             null,
   constraint PK_TB_MYUSER primary key (Account)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户表',
   'user', @CurrentUser, 'table', 'tb_MyUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '自增列',
   'user', @CurrentUser, 'table', 'tb_MyUser', 'column', 'isid'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户账号',
   'user', @CurrentUser, 'table', 'tb_MyUser', 'column', 'Account'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户名称',
   'user', @CurrentUser, 'table', 'tb_MyUser', 'column', 'UserName'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '昵称',
   'user', @CurrentUser, 'table', 'tb_MyUser', 'column', 'PetName'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建人',
   'user', @CurrentUser, 'table', 'tb_MyUser', 'column', 'CreateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建日期',
   'user', @CurrentUser, 'table', 'tb_MyUser', 'column', 'CreateDate'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改人',
   'user', @CurrentUser, 'table', 'tb_MyUser', 'column', 'LastUpdateUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '修改日期',
   'user', @CurrentUser, 'table', 'tb_MyUser', 'column', 'LastUpdateDate'
go

alter table tb_CustomerDetail
   add constraint FK_TB_CUSTO_REFERENCE_TB_CUSTO foreign key (CustomerCode)
      references tb_Customer (CustomerCode)
go

alter table tb_DictionaryDetail
   add constraint FK_TB_DICTI_REFERENCE_TB_DICTI foreign key (TypeID)
      references tb_Dictionary (TypeID)
go

alter table tb_EmployeeCon
   add constraint FK_TB_EMPLO_REFERENCE_TB_EMPLO foreign key (InfoID)
      references tb_Employee (InfoID)
go

 

本系列项目源码下载地址:https://github.com/GarsonZhang/GZFramework.Demo

 

系列文章

1. GZFramwork数据库层《前言》Demo简介

2. GZFramwork数据库层《前言》DLL项目引用

3. GZFramwork数据库层《一》普通表增删改查

4. GZFramwork数据库层《二》单据表增删改查(自动生成单据号码)

5. GZFramwork数据库层《三》普通主从表增删改查

6. GZFramwork数据库层《四》单据主从表增删改查(主键自动生成)

7. GZFramwork数据库层《五》高级主从表增删改查(主表明细表主键都自动生成)

8. GZFramwork数据库层《六》存储过程调用

9. GZFramwork数据库层《七》总结