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