eaglass.sql

文件传输源码pb9－－高级winsokt编程

SET NOCOUNT ON
USE master
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name='eaglass1')
    DROP DATABASE eaglass1
GO
DECLARE @DEVICE_DIRECTORY NVARCHAR(520)
SELECT @DEVICE_DIRECTORY = SUBSTRING(phyname, 1, CHARINDEX(N'master.mdf', LOWER(phyname)) - 1)
FROM master.dbo.sysdevices WHERE (name = N'master')
-- 创建数据库
EXECUTE (N'CREATE DATABASE eaglass1
  ON PRIMARY (NAME = N''eaglass1'', FILENAME = N''' + @device_directory + N'eaglass1.mdf'')
  LOG ON (NAME = N''eaglass1_log'',  FILENAME = N''' + @device_directory + N'eaglass1_log.ldf'')')
GO
EXEC sp_dboption 'eaglass1','trunc. log on chkpt.','true'
EXEC sp_dboption 'eaglass1','select into/bulkcopy','true'
GO
SET QUOTED_IDENTIFIER ON
GO
USE eaglass1
GO

CREATE TABLE [c_cangku] (
	[id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[cangku] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[memo] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[bz] [int] NULL CONSTRAINT [DF_c_cangku_bz] DEFAULT (1),
	CONSTRAINT [PK_c_cangku] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO




DELETE FROM [c_cangku]
GO


INSERT INTO [c_cangku] ( id, cangku, memo, bz) VALUES ('01','总店',Null,0)
INSERT INTO [c_cangku] ( id, cangku, memo, bz) VALUES ('02','桥西店',Null,1)
CREATE TABLE [c_diqu] (
	[diqu] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	CONSTRAINT [PK_c_diqu] PRIMARY KEY  CLUSTERED 
	(
		[diqu]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO




DELETE FROM [c_diqu]
GO


INSERT INTO [c_diqu] ( diqu) VALUES ('北京')
INSERT INTO [c_diqu] ( diqu) VALUES ('上海')
INSERT INTO [c_diqu] ( diqu) VALUES ('石家庄')
CREATE TABLE [c_gangwei] (
	[gangwei] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	CONSTRAINT [PK_c_gangwei] PRIMARY KEY  CLUSTERED 
	(
		[gangwei]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO




DELETE FROM [c_gangwei]
GO


INSERT INTO [c_gangwei] ( gangwei) VALUES ('验光师')
INSERT INTO [c_gangwei] ( gangwei) VALUES ('营业员')
CREATE TABLE [c_guige] (
	[guige] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	CONSTRAINT [PK_c_guige] PRIMARY KEY  CLUSTERED 
	(
		[guige]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO




DELETE FROM [c_guige]
GO


CREATE TABLE [c_mima] (
	[id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[mima] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[zhuangtai] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_c_mima_zhuangtai] DEFAULT ('正常'),
	[jibie] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_c_mima_jibie] DEFAULT (1),
	[jhdj] [bit] NULL CONSTRAINT [DF_c_mima_jhdj] DEFAULT (0),
	CONSTRAINT [PK_c_mima] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO




DELETE FROM [c_mima]
GO


INSERT INTO [c_mima] ( id, mima, name, zhuangtai, jibie, jhdj) VALUES ('MjIy','MjIy','222','正常','01',1)
INSERT INTO [c_mima] ( id, mima, name, zhuangtai, jibie, jhdj) VALUES ('MTEx','MTEx','111','正常','999',1)
INSERT INTO [c_mima] ( id, mima, name, zhuangtai, jibie, jhdj) VALUES ('MzMz','MzMz','333','正常','02',1)
CREATE TABLE [c_pinming] (
	[xid] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[pinming] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	CONSTRAINT [PK_c_pinming] PRIMARY KEY  CLUSTERED 
	(
		[xid]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO




DELETE FROM [c_pinming]
GO

SET IDENTITY_INSERT [c_pinming] ON
GO


INSERT INTO [c_pinming] ( xid, pinming) VALUES (5,'镜架')
INSERT INTO [c_pinming] ( xid, pinming) VALUES (6,'镜片')
INSERT INTO [c_pinming] ( xid, pinming) VALUES (9,'隐形')
INSERT INTO [c_pinming] ( xid, pinming) VALUES (21,'成镜')
INSERT INTO [c_pinming] ( xid, pinming) VALUES (22,'其他')
SET IDENTITY_INSERT [c_pinming] OFF

CREATE TABLE [c_pinpai] (
	[xid] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[pinpai] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	CONSTRAINT [PK_c_pinpai] PRIMARY KEY  CLUSTERED 
	(
		[xid]
	)  ON [PRIMARY]