operator.sql

在。NET中利用VB6.0的控件来现实对串口的操作来完成短信发送的功能

/*+++++++++++++++++++++参数信息+++++++++++++++++++++++*/
if exists (select * from sysobjects where id = object_id(N'[Sms].[SmsInform]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Sms].[SmsInform]
GO
CREATE TABLE [Sms].[SmsInform]
(
	[SmsInform_Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[Number] [varchar] (20) NOT NULL ,
	[SmsText] [varchar] (140) NOT NULL ,
	[UserName] [varchar] (10) NULL ,
	[UpdateTime] [datetime]  NULL ,
	[SendTime] [datetime]  NULL,
        [State] [int] NOT NULL default(0)     
) 
GO

/*+++++++++++++++++++++参数信息+++++++++++++++++++++++*/
if exists (select * from sysobjects where id = object_id(N'[Sms].[Parameter]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Sms].[Parameter]
GO
CREATE TABLE [Sms].[Parameter]
(
	[Parameter_Id] [int] NOT NULL PRIMARY KEY,
	[CommPort] [varchar](5) NOT NULL default('COM1') ,
	[BitRatio] [varchar] (6) NOT NULL default('9600') ,
	[ParityCheck] [varchar] (10) NOT NULL  default('无'),
	[DataBit] [varchar] (5) NOT NULL default('8'),
	[Digit] [varchar] (3) NOT NULL default('1'),
        [Interval] [varchar] (5) Not NULL default('500'),
        [State] [int] NOT NULL  default(0)      
) 
GO

/************GetParameter************/
if exists (select * from dbo.sysobjects where id = object_id(N'[Sms].[GetParameter]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Sms].[GetParameter]
GO
CREATE PROCEDURE GetParameter

AS  
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0
SET NOCOUNT ON
   select Parameter_Id,CommPort,BitRatio,ParityCheck,DataBit,Digit,Interval,State from Parameter
IF (@@ERROR <> 0)
    SET @ErrorSave = @@ERROR
RETURN @ErrorSave
GO
exec GetSmsInform

/************GetSmsInform************/
if exists (select * from dbo.sysobjects where id = object_id(N'[Sms].[GetSmsInform]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Sms].[GetSmsInform]
GO
CREATE PROCEDURE GetSmsInform

AS  
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0
SET NOCOUNT ON
   select SmsInform_Id,Number,SmsText,SendTime,State from SmsInform where SmsInform_Id in ( select min(SmsInform_Id) from SmsInform where State = 0)
IF (@@ERROR <> 0)
    SET @ErrorSave = @@ERROR
RETURN @ErrorSave
GO

/************UpdateSmsInform************/
if exists (select * from dbo.sysobjects where id = object_id(N'[Sms].[UpdateSmsInform]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Sms].[UpdateSmsInform]
GO
CREATE PROCEDURE UpdateSmsInform
           @SmsInform_Id int,
           @SendTime datetime,
           @State int
AS  
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0
SET NOCOUNT ON
   UPDATE SmsInform
         set
            SendTime = @SendTime,
            State = @State
         where SmsInform_Id = @SmsInform_Id
IF (@@ERROR <> 0)
    SET @ErrorSave = @@ERROR
RETURN @ErrorSave
GO

/************UpdateParameter************/
if exists (select * from dbo.sysobjects where id = object_id(N'[Sms].[UpdateParameter]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Sms].[UpdateParameter]
GO
CREATE PROCEDURE UpdateParameter
           @Parameter_Id int,
           @CommPort varchar(5),
           @BitRatio varchar(6),
           @ParityCheck varchar(10),
           @DataBit varchar(5),
           @Digit varchar(3),
           @Interval varchar(5),
           @State int
AS  
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @IdCount int
select @IdCount = count(Parameter_Id) from Parameter 
DECLARE @ErrorSave INT
SET @ErrorSave = 0
SET NOCOUNT ON
    IF(@IdCount <> 0 )
       BEGIN
          UPDATE Parameter 
             SET
               Parameter_Id = @Parameter_Id,
               CommPort = @CommPort,
               BitRatio = @BitRatio ,
               ParityCheck = @ParityCheck,
               DataBit = @DataBit,
               Digit = @Digit ,
               Interval = @Interval,
               State = @State 
              WHERE 0 = Parameter_Id
        END
     ELSE
        BEGIN
           INSERT INTO Parameter
           (
               Parameter_Id,
               CommPort,
               BitRatio,
               ParityCheck,
               DataBit,
               Digit ,
               Interval,
               State
            )
            VALUES
           (
               @Parameter_Id,
               @CommPort,
               @BitRatio,
               @ParityCheck,
               @DataBit,
               @Digit ,
               @Interval,
               @State
             ) 
         END        
IF (@@ERROR <> 0)
    SET @ErrorSave = @@ERROR
RETURN @ErrorSave

GO


exec GetParameter