C# BaseData Maintain (标准三层C/S架构 )

USE [SMT] GO /****** Object: Table [dbo].[SMT_LineRest] Script Date: 04/19/2010 12:08:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SMT_LineRest]( [Line] [varchar](50) NOT NULL, [Type] [varchar](50) NOT NULL, [StartDate] [varchar](8) NOT NULL, [StartTime] [varchar](6) NOT NULL, [EndDate] [varchar](8) NOT NULL, [EndTime] [varchar](6) NOT NULL, [UID] [varchar](50) NOT NULL, [TransDateTime] [varchar](14) NOT NULL, CONSTRAINT [PK_B_LineRest] PRIMARY KEY CLUSTERED ( [Line] ASC, [Type] ASC, [StartDate] ASC, [StartTime] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[QMS_LOG] Script Date: 04/19/2010 12:08:54 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[QMS_LOG]( [System_Name] [varchar](100) NOT NULL, [Event_No] [varchar](50) NOT NULL, [SN] [varchar](50) NULL, [User_Name] [varchar](20) NULL, [Desc1] [nvarchar](2000) NULL, [Trans_Date] [varchar](14) NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[QMS_LOG] ADD CONSTRAINT [DF_QMS_LOG_Trans_Date] DEFAULT ([DBO].[FormatDate](getdate(),'YYYYMMDDHHNNSS')) FOR [Trans_Date] GO /****** Object: UserDefinedFunction [dbo].[FormatDate] Script Date: 04/19/2010 12:09:46 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE FUNCTION [dbo].[FormatDate] (@date as datetime, @formatstring as varchar(100) ) RETURNS varchar(100) AS BEGIN declare @datestring as varchar(100) set @datestring=@formatstring --year set @datestring=replace(@datestring, 'yyyy', cast(year(@date) as char(4))) set @datestring=replace(@datestring, 'yy', right(cast(year(@date) as char(4)),2)) --millisecond set @datestring=replace(@datestring, 'ms', replicate('0',3-len(cast(datepart(ms,@date) as varchar(3)))) + cast(datepart(ms, @date) as varchar(3))) --month set @datestring=replace(@datestring, 'mm', replicate('0',2-len(cast(month(@date) as varchar(2)))) + cast(month(@date) as varchar(2))) set @datestring=replace(@datestring, 'm', cast(month(@date) as varchar(2))) --day set @datestring=replace(@datestring, 'dd', replicate('0',2-len(cast(day(@date) as varchar(2)))) + cast(day(@date) as varchar(2))) set @datestring=replace(@datestring, 'd', cast(day(@date) as varchar(2))) --hour set @datestring=replace(@datestring, 'hh', replicate('0',2-len(cast(datepart(hh,@date) as varchar(2)))) + cast(datepart(hh, @date) as varchar(2))) set @datestring=replace(@datestring, 'h', cast(datepart(hh, @date) as varchar(2))) --minute set @datestring=replace(@datestring, 'nn', replicate('0',2-len(cast(datepart(n,@date) as varchar(2)))) + cast(datepart(n, @date) as varchar(2))) set @datestring=replace(@datestring, 'n', cast(datepart(n, @date) as varchar(2))) --second set @datestring=replace(@datestring, 'ss', replicate('0',2-len(cast(datepart(ss,@date) as varchar(2)))) + cast(datepart(ss, @date) as varchar(2))) set @datestring=replace(@datestring, 's', cast(datepart(ss, @date) as varchar(2))) return @datestring END GO GO /****** Object: StoredProcedure [dbo].[MDL_SMT_LineRest] Script Date: 04/19/2010 12:10:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[MDL_SMT_LineRest] @DMLType varchar(50), @Line varchar(50), @Type varchar(50), @StartDate varchar(50), @StartTime varchar(50), @EndDate varchar(50), @EndTime varchar(50), @UID varchar(50) as set nocount on ----------------------------------------------------------------------------- --*Program*: <SMT_Maintain>------------ --*Programer*:<Archer> --*Date*:<2010/03/29> --*Unify*:<UA> --*Description*:<Maintain SMT_LineRest Table> --########## Parameter Description Begin ########## --########## Parameter Description End # ########## --##########Update Log Begin ################### --EQMS Date UpdateOwner Description /* */ --##########Update Log End # ################### ------------------------------------------------------------------------------- Declare @sql as varchar(max) If @DMLType='Delete' Begin Set @sql='Delete from SMT_LineRest where Line='''+@Line+''' and [Type]='''+@Type+''' and StartDate='''+@StartDate+''' and StartTime='''+@StartTime+''' and EndDate='''+@EndDate+''' and EndTime='''+@EndTime+'''' Exec(@sql) If @@ROWCOUNT>0 Begin Select 'PASS' as Result,'PASS' as Msg End Else Begin Select 'FAIL' as Result,'can not find data by <Line='+@Line+'><Type='+@Type+'><StartDate='+@StartDate+'><StartTime='+@StartTime+'><EndDate='+@EndDate+'><EndTime='+@EndTime+'>' as Msg return End End Else If @DMLType='Insert' Begin If @Type='Day' Begin If dbo.Convert2Date(@StartDate+@StartTime)>=dbo.Convert2Date(@EndDate+@EndTime) Begin Select 'FAIL' as Result,'The Rest Time is wrong:'+@StartDate+@StartTime+' late then '+@EndDate+@EndTime as Msg return End End else Begin if @StartTime>=@EndTime Begin Select 'FAIL' as Result,'The Rest Time is wrong:'+@StartTime+' late then '+@EndTime as Msg return End End If exists(Select 0 from SMT_LineRest where Line=@Line and [Type]=@Type and StartDate=@StartDate and StartTime=@StartTime) Begin Select 'FAIL' as Result,'The data is Exists for <Line='+@Line+'><Type='+@Type+'>'+ case when @Type='Day' then +'<StartDate='+@StartDate+'>' else '' end +'<StartTime='+@StartTime+'>' as Msg Return End Set @sql='Insert into SMT_LineRest(Line,Type,StartDate,StartTime,EndDate,EndTime,UID,TransDatetime)Select '''+@Line+''','''+@Type+''','''+@StartDate+''','''+@StartTime+''','''+@EndDate+''','''+@EndTime+''','''+@UID+''',dbo.FormatDate(GETDATE(),''YYYYMMDDHHNNSS'')' Exec(@sql) If @@ERROR=0 Begin Select 'PASS' as Result,'Insert Data is OK' as Msg End Else Begin Select 'FAIL' as Result,'Insert data is Fail' as Msg return End End Insert into QMS_LOG(System_Name,Event_No,SN,[User_Name],Desc1,Trans_Date) Select 'SMT_LineRest',@DMLType,HOST_NAME(),@UID,@sql,dbo.FormatDate(GETDATE(),'YYYYMMDDHHNNSS')