ajax(Tibco) 与 SQL server 2005(4)

1.先建好数据库BBSDATABASE,再建好表,T_USER,脚本如下:

USE [BBSdataBase]
GO
/****** 对象:  Table [dbo].[T_User]    脚本日期: 05/11/2008 09:29:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_User](
 [UserID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [UserName] [varchar](50) NOT NULL,
 [Password] [nvarchar](50) NOT NULL,
 [Email] [nvarchar](50) NULL,
 [Sex] [nvarchar](2) NULL,
 [Birthday] [datetime] NULL,
 [Web] [nvarchar](50) NULL,
 [QQ] [nvarchar](12) NULL,
 [MSN] [nvarchar](50) NULL,
 [TaobaoID] [nvarchar](50) NULL,
 [ZhiFuBao] [nvarchar](50) NULL,
 [Face] [p_w_picpath] NULL,
 [Singer] [nvarchar](50) NULL,
 [ZhiWoJS] [nvarchar](50) NULL,
 [ZhiFen] [float] NULL CONSTRAINT [DF_T_User_ZhiFen]  DEFAULT ((0)),
 [RecDateTime] [datetime] NOT NULL CONSTRAINT [DF_T_User_RecDateTime]  DEFAULT (getdate()),
 [LoginCount] [int] NULL CONSTRAINT [DF_T_User_LoginCount]  DEFAULT ((0)),
 [Online] [real] NULL CONSTRAINT [DF_T_User_Online]  DEFAULT ((0)),
 CONSTRAINT [PK_T_User] PRIMARY KEY CLUSTERED
(
 [UserID] ASC,
 [UserName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用戶編號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_User', @level2type=N'COLUMN',@level2name=N'UserID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用戶姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_User', @level2type=N'COLUMN',@level2name=N'UserName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_User', @level2type=N'COLUMN',@level2name=N'RecDateTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登录次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_User', @level2type=N'COLUMN',@level2name=N'LoginCount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否在线' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_User', @level2type=N'COLUMN',@level2name=N'Online'

3.建好,存储过程.脚本如下.

USE [BBSdataBase]
GO
/****** 对象:  StoredProcedure [dbo].[CheckUserProc]    脚本日期: 05/11/2008 09:30:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[CheckUserProc]
@struser varchar(50),
@strpwd nvarchar(50),
@RecCnt int output
AS
  set @RecCnt=( SELECT
        count(UserID) as RecCount
    FROM
  
BBSDataBase.dbo.T_User where username=@struser and [password]=@strpwd)
if @RecCnt>=1

update dbo.T_User set LoginCount=LoginCount+1,Online=1 where UserName=@struser

return @RecCnt

5.建好.SOAP,这是重点中的重点.全靠这个东西才可以使SQL SERVER发布WEB SERVICES.也是MS的新技术之一.

/****** 对象:  Endpoint [SelectRegRec]    脚本日期: 05/11/2008 09:31:56 ******/
CREATE ENDPOINT [SelectRegRec]
 AUTHORIZATION [sa]
 STATE=STARTED
 AS HTTP (PATH=N'/Bbsdatabase', PORTS = (CLEAR), AUTHENTICATION = (NTLM), SITE=N'*', CLEAR_PORT = 80, COMPRESSION=ENABLED)
 FOR SOAP (

WEBMETHOD 'DataAccessList'( NAME=N'[bbsdatabase].[dbo].[CheckUserProc]'
, SCHEMA=DEFAULT
, FORMAT=ALL_RESULTS),
), BATCHES=ENABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]', SESSIONS=ENABLED, SESSION_TIMEOUT=60, DATABASE=N'BBSDatabase', NAMESPACE=N'http://ajax.dtlaizi.com/Bbsdatabase', SCHEMA=STANDARD, CHARACTER_SET=XML)