调用在 C# 中返回值的参数化存储过程

本文关键字：参数 存储过程 返回值 调用 | 更新日期: 2023-09-27 18:31:55

C# --> 我有一个参数化存储过程 （SP），它返回一个我要调用的 int 并读取我要分配给隐藏字段的返回值。 我相信我已经写了大部分内容，但卡在最后一部分（实际上是从 SP 中提取返回的值。 帮助将不胜感激。 这是我到目前为止所拥有的...

public int CheckIP()
{
    string conn = "";
    conn = ConfigurationManager.ConnectionStrings["Connection"].ToString();
    SqlConnection sqlconn = new SqlConnection(conn);
    try
    {
        sqlconn.Open();
        DataSet ds = new DataSet();
        SqlCommand objcmd = new SqlCommand("sp_CheckIP", sqlconn);
        objcmd.CommandType = CommandType.StoredProcedure;
        SqlParameter IPAddress = objcmd.Parameters.Add("@IpAddress",
            SqlDbType.VarChar);
        IPAddress.Value = 0;
        SqlDataAdapter objAdp = new SqlDataAdapter(objcmd);
        objAdp.Fill(ds);
        ????
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message.ToString());
    }
    finally
    {
        sqlconn.Close();
    }
}

我的存储过程如下：

ALTER Procedure [dbo].[sp_CheckIP]
@IpAddress varchar(15),
@AllowedLogInAttempts int Output
AS
BEGIN
IF (Select Count(IPAddressID) From Emerald.dbo.IPManager 
    Where IPAddress = @IPAddress) = 1
BEGIN
Select @AllowedLogInAttempts = 
     CASE
        When Trusted = 0 and Block = 0 then '5'
        When Trusted = 1 and Block = 0 then '7'
        When Trusted = 0 and Block = 1 then '0'
        When Trusted = 1 and Block = 1 then '0'
        End 
FROM Emerald.dbo.IPManager
Where IPAddress = @IPAddress
END
ELSE 
Select 3
END

调用在 C# 中返回值的参数化存储过程

您需要按如下方式修改代码 我所做的更改是我为 SP 添加了返回变量，这将返回输出值。

public int CheckIP()
{
 string conn = "";
 conn = ConfigurationManager.ConnectionStrings["Connection"].ToString();
 SqlConnection sqlconn = new SqlConnection(conn);
 try
 {
     sqlconn.Open();
     DataSet ds = new DataSet();
     SqlCommand objcmd = new SqlCommand("sp_CheckIP", sqlconn);
     objcmd.CommandType = CommandType.StoredProcedure;
     SqlParameter IPAddress = objcmd.Parameters.Add("@IpAddress", SqlDbType.VarChar);
     IPAddress.Value = 0;

     SqlParameter returnParameter = new SqlParameter("@AllowedLogInAttempts", SqlDbType.Int);
     returnParameter.Direction = ParameterDirection.Output;
     objcmd.Parameters.Add(returnParameter);
      objcmd.ExecuteNonQuery();
     int id = (int) returnParameter.Value;    
     //Now here write your logic to assign value hidden field
 }
 catch (Exception ex)
 {
     Response.Write(ex.Message.ToString());
 }
 finally
 {
     sqlconn.Close();
 }
}

您可以使用;

retval = objcmd.执行标量（）;

Select @AllowedLogInAttempts = 
     CASE
        When Trusted = 0 and Block = 0 then '5'
        When Trusted = 1 and Block = 0 then '7'
        When Trusted = 0 and Block = 1 then '0'
        When Trusted = 1 and Block = 1 then '0'
     End 
FROM Emerald.dbo.IPManager
Where IPAddress = @IPAddress

可以缩短为：

Select @AllowedLogInAttempts = (Trusted * 2 + 5) * (Block ^ 1)
相关文章：