SQL SERVER中的存储过程如下:
alter PROCEDURE [dbo].[CheckUserValid]
( @UserIP nvarchar(50), @UserName nvarchar(50) )
AS
declare @IsValid int
declare @IP BigInt
set @Ip = dbo.IP2BigInt( @UserIP )
-- 先判断IP地址
if exists( select id from [IP] where StartIP <= @Ip and EndIP >= @Ip )
SELECT @IsValid = 1
else
begin
-- IP验证不通过,验证用户名
if len(@UserName) >= 15
begin
if exists( select id from [Teacher] where ID = @UserName )
SELECT @IsValid = 2
else
SELECT @IsValid = 0
end
end
return @IsValid
go
C#程序如下:SqlParameter para1 = new SqlParameter("@UserIP", SqlDbType.NVarChar, 50);
para1.Direction = ParameterDirection.Input;
para1.Value = user.sUserPublicIP;
SqlCommand cmd = new SqlCommand("CheckUserValid", dataBaseConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(para1);
try
{
cmd.ExecuteNonQuery(); // 若返回值有异常
if (cmd.Parameters["@IsValid"].Value == null)
return CERTIFY_RETURN_VALUE_ERROR;
return int.Parse(cmd.Parameters["@IsValid"].Value.ToString());
}一般情况下输入参数都正常得到结果,但是偶尔总会出现错误,返回了 CERTIFY_RETURN_VALUE_ERROR值,当有一定量用户并发时出现几率更多。请教给位哪儿出现问题了? 谢谢
alter PROCEDURE [dbo].[CheckUserValid]
( @UserIP nvarchar(50), @UserName nvarchar(50) )
AS
declare @IsValid int
declare @IP BigInt
set @Ip = dbo.IP2BigInt( @UserIP )
-- 先判断IP地址
if exists( select id from [IP] where StartIP <= @Ip and EndIP >= @Ip )
SELECT @IsValid = 1
else
begin
-- IP验证不通过,验证用户名
if len(@UserName) >= 15
begin
if exists( select id from [Teacher] where ID = @UserName )
SELECT @IsValid = 2
else
SELECT @IsValid = 0
end
end
return @IsValid
go
C#程序如下:SqlParameter para1 = new SqlParameter("@UserIP", SqlDbType.NVarChar, 50);
para1.Direction = ParameterDirection.Input;
para1.Value = user.sUserPublicIP;
SqlCommand cmd = new SqlCommand("CheckUserValid", dataBaseConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(para1);
try
{
cmd.ExecuteNonQuery(); // 若返回值有异常
if (cmd.Parameters["@IsValid"].Value == null)
return CERTIFY_RETURN_VALUE_ERROR;
return int.Parse(cmd.Parameters["@IsValid"].Value.ToString());
}一般情况下输入参数都正常得到结果,但是偶尔总会出现错误,返回了 CERTIFY_RETURN_VALUE_ERROR值,当有一定量用户并发时出现几率更多。请教给位哪儿出现问题了? 谢谢
( @UserIP nvarchar(50), @UserName nvarchar(50), @IsValid int output )
AS
set @IsValid = 0
declare @IP BigInt
set @Ip = dbo.IP2BigInt( @UserIP )
-- 先判断IP地址
if exists( select id from [IP] where StartIP <= @Ip and EndIP >= @Ip )
SELECT @IsValid = 1
else
begin
-- IP验证不通过,验证用户名
if len(@UserName) >= 15
begin
if exists( select id from [Teacher] where ID = @UserName )
SELECT @IsValid = 2
else
SELECT @IsValid = 0
end
end
return @IsValid
go
SqlCommand cmd = new SqlCommand("CheckUserValid", dataBaseConn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter para1 = new SqlParameter("@UserIP", SqlDbType.NVarChar, 50);
para1.Value = user.sUserPublicIP;
cmd.Parameters.Add(para1);
SqlParameter para2 = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
para2.Value = user.sUserPublicName;
cmd.Parameters.Add(para2);
SqlParameter para3 = new SqlParameter("@return", SqlDbType.Int);
para3.Direction = ParameterDirection.Output;
cmd.Parameters.Add(para3);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter para1 = new SqlParameter("@UserIP", SqlDbType.NVarChar, 50);
para1.Value = user.sUserPublicIP;
cmd.Parameters.Add(para1);
SqlParameter para2 = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
para2.Value = user.sUserPublicName;
cmd.Parameters.Add(para2);
SqlParameter para3 = new SqlParameter("@IsValid", SqlDbType.Int);
para3.Direction = ParameterDirection.Output;
cmd.Parameters.Add(para3);