SQL存储过程的调用及写法

调用函数：

    public class SqlProcess
    {
        public int ReturnValue = 0;
        public DataSet ReturnSet = null;
        public SqlDataAdapter adapter = null;

        public SqlProcess(String proc,String uid,String pwd,String data,String ip) {
                uid = Regex.Replace(uid,@"[^\w]*","");
                pwd = Regex.Replace(pwd,@"[^\w]*","");

                SqlConnection conn = new SqlConnection(Config.DBConnString);
                conn.Open();
                SqlCommand sqlcmd = new SqlCommand(proc, conn);
                sqlcmd.CommandType = CommandType.StoredProcedure;
                sqlcmd.Parameters.Add(new SqlParameter("uid", uid));
                sqlcmd.Parameters.Add(new SqlParameter("pwd", pwd));
                sqlcmd.Parameters.Add(new SqlParameter("data", data));
                sqlcmd.Parameters.Add(new SqlParameter("IP", ip));
                SqlParameter returnParm = new SqlParameter("return", SqlDbType.Int);
                returnParm.Direction = ParameterDirection.ReturnValue;
                sqlcmd.Parameters.Add(returnParm);
                adapter = new SqlDataAdapter(sqlcmd);
                ReturnSet = new DataSet();
                adapter.Fill(ReturnSet);
                conn.Close();
                ReturnValue = Convert.ToInt32(returnParm.Value);
        }
    }

 

sql存储过程：

USE [ServiceDB]
GO
/****** Object:  StoredProcedure [dbo].[CheckIFlightPrivate]    Script Date: 06/02/2013 10:32:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Batch submitted through debugger: SQLQuery15.sql|7|0|C:\Documents and Settings\Administrator\Local Settings\Temp\~vs1860.sql


ALTER    PROCEDURE [dbo].[CheckIFlightPrivate]
(
@userId varchar(32),
@pwdMD5 varchar(32),
@data varchar(1024),
@IP varchar(16)
--@result int output
)
AS
BEGIN
    DECLARE @accountPwd varchar(32),
    @returnValue int,
    @ID bigint;
    
    IF @userId is NULL or @userId = ''
    BEGIN
        EXEC @returnValue = ErrCode 10001;
        RETURN @returnValue;
    END
    IF @pwdMD5 is NULL or @pwdMD5 = ''
    BEGIN
        EXEC @returnValue = ErrCode 10002;
        RETURN @returnValue;
    END
    --登录
    SELECT Top 1 @ID=ID,@accountPwd=userPwd FROM TUserInfo WHERE UserID = @userId;
    IF @@ROWCOUNT = 0 
    BEGIN
        EXEC @returnValue = ErrCode 10003;
        RETURN @returnValue;
    END
    --有用户 验证是否是用独立MD5
    
    
    DECLARE 
    @userMD5 varchar(50),
    @BeginDateTime datetime,
    @EndDateTime datetime,
    @PerDayTimes bigint,
    @LastDayUseTimes bigint,
    @PerMonthTimes bigint,
    @LastMonthUseTimes bigint,
    @PerYearTimes bigint,
    @LastYearUseTimes bigint,
    @TotalTimes bigint,
    @TotalUseTimes bigint,
    @AllowedClientIP varchar(50),
    @LastDayUpdateTime datetime,
    @LastMonthUpdateTime datetime,
    @LastYearUpdateTime datetime;
    
    SELECT TOP 1
    @userMD5 = UserMD5,
    @BeginDateTime = BeginDateTime,
    @EndDateTime = EndDateTime,
    @PerDayTimes = PerDayTimes,
    @LastDayUseTimes = LastDayUseTimes,
    @PerMonthTimes = PerMonthTimes,
    @LastMonthUseTimes = LastMonthUseTimes,
    @PerYearTimes = PerYearTimes,
    @LastYearUseTimes = LastYearUseTimes,
    @TotalTimes = TotalTimes,
    @TotalUseTimes = TotalUseTimes,
    @LastDayUpdateTime = LastDayUpdateTime,
    @LastMonthUpdateTime = LastMonthUpdateTime,
    @LastYearUpdateTime = LastYearUpdateTime,
    @AllowedClientIP = AllowedClientIP FROM  TIFlightPrivate WHERE UserID=@ID;
    --登录成功--验证功能
    
    IF @@RowCount = 0
        BEGIN
            EXEC @returnValue = ErrCode 10010;
            RETURN @returnValue;
        END
    ELSE
    BEGIN
        IF @userMD5 is NULL OR @userMD5 = ''
        BEGIN
            --用帐号的MD5;
            IF @pwdMD5 <> @accountPwd
            BEGIN
                EXEC @returnValue = ErrCode 10004;
                RETURN @returnValue;
            END
            
        END
        ELSE IF @pwdMD5 <> @userMD5
        BEGIN
            EXEC @returnValue = ErrCode 10006;
            RETURN @returnValue;
        END
    END
    
    --验证 更新查询次数
    
    --  更新次数
        if datediff(dd,@LastDayUpdateTime,getdate()) > 0
            begin
            set @LastDayUseTimes = 0;
            set @LastDayUpdateTime = getdate();
            end
        if datediff(dd,dateadd(mm,1,@LastMonthUpdateTime),getdate()) > 0
            begin
            set @LastMonthUseTimes = 0;
            set @LastMonthUpdateTime = getdate();
            end
        if datediff(dd,dateadd(yy,1,@LastYearUpdateTime),getdate()) > 0
            begin
            set @LastYearUseTimes = 0;
            set @LastYearUpdateTime = getdate();
            end
            
        IF @EndDateTime is not null and getdate() > @EndDateTime
            begin
            SELECT '该帐号已于'+convert(varchar,@EndDateTime,20)+'过期!' AS ErrInfo;
            return 0;
            end
        else if @AllowedClientIP is not null and charindex(@IP,@AllowedClientIP) = 0
            begin
            SELECT '该帐号限制访问IP为:'+@AllowedClientIP AS ErrInfo;
            return 0;
            end
        else if @PerDayTimes is not null and @PerDayTimes>0 and @LastDayUseTimes >= @PerDayTimes
            begin
            SELECT '该帐号已经超过当天查询次数:'+ convert(varchar,@perDayTimes) +'次' AS ErrInfo;
            return 0;
            end
        else if @PerMonthTimes is not null and @PerMonthTimes>0 and @LastMonthUseTimes >= @PerMonthTimes
            begin
            SELECT '该帐号已经超过当月查询次数:'+ convert(varchar,@PerMonthTimes) +'次' AS ErrInfo;
            return 0;
            end
        else if @PerYearTimes is not null and @PerYearTimes>0 and @LastYearUseTimes >= @PerYearTimes
            begin
            SELECT '该帐号已经超过当年查询次数:'+ convert(varchar,@PerYearTimes) +'次' AS ErrInfo;
            return 0;
            end
        else if @TotalTimes is not null and @TotalTimes>0  and @TotalUseTimes >= @TotalTimes
            begin
            SELECT '该帐号已经超过总查询次数:'+ convert(varchar,@TotalTimes) + '次' AS ErrInfo;
            return 0;
            end
        else
            begin
            update TIFlightPrivate set LastDayUseTimes=@LastDayUseTimes+1,
            LastMonthUseTimes=@LastMonthUseTimes+1,
            LastYearUseTimes=@LastYearUseTimes+1,
            TotalUseTimes=@TotalUseTimes+1,
            LastDayUpdateTime=@LastDayUpdateTime,
            LastMonthUpdateTime=@LastMonthUpdateTime,
            LastYearUpdateTime=@LastYearUpdateTime Where UserID=@ID;
            --set @result = '该帐号有效日期至:'+ convert(varchar,@EndDateTime,120) + ',当天剩余次数,当月剩余次数,当年剩余次数,总剩余次数';
            end    
    
    --默认城市地方
    return 1
END