执行存储过程并通过输出和sql参数将行数返回给代码

本文关键字：返回 代码 参数 sql 存储过程 输出 执行 | 更新日期: 2023-09-27 18:13:19

我有以下通过会话变量匹配用户输入的代码。如果会话变量中的数据与数据库中的数据匹配，则存储过程返回行计数。

一切工作，除了我想返回的行数，这将始终是一个单一的行:简单地说，就是访问一个表单，添加信息，然后点击提交。数据存储在会话中，存储过程在匹配时返回数据。

即使程序工作，intRecCount变量始终为零，而不是行数。

存储过程: 

CREATE PROCEDURE [dbo].[uspConfirmation]
    @RecordID CHAR(36),
    @LName VARCHAR(30),
    @FName VARCHAR(30),
    @MInit CHAR(1),
    @RecordCount INT OUTPUT
AS
SELECT * FROM Registration
WHERE RecordID = @RecordID AND
      LName = @LName AND
      FName = @FName AND
      MInit = @MInit
SET @RecordCount = @@ROWCOUNT
RETURN

方法/代码:

public static DataSet Confirmation()
{ 
SqlCommand cmdSQL = new SqlCommand("uspConfirmation", Connection);
cmdSQL.CommandType = CommandType.StoredProcedure;
cmdSQL.Parameters.Add(new SqlParameter("@RecordID", SqlDbType.VarChar, 36));
cmdSQL.Parameters["@RecordID"].Direction = ParameterDirection.Input;
cmdSQL.Parameters["@RecordID"].Value = RecordIDSession;
cmdSQL.Parameters.Add(new SqlParameter("@LName", SqlDbType.VarChar, 30));
cmdSQL.Parameters["@LName"].Direction = ParameterDirection.Input;
cmdSQL.Parameters["@LName"].Value = LNameSession;
cmdSQL.Parameters.Add(new SqlParameter("@FName", SqlDbType.VarChar, 30));
cmdSQL.Parameters["@FName"].Direction = ParameterDirection.Input;
cmdSQL.Parameters["@FName"].Value = FNameSession;
cmdSQL.Parameters.Add(new SqlParameter("@MInit", SqlDbType.Char, 1));
cmdSQL.Parameters["@MInit"].Direction = ParameterDirection.Input;
cmdSQL.Parameters["@MInit"].Value = MNameSession;
cmdSQL.Parameters.Add(new SqlParameter("@RecordCount", SqlDbType.Int));
cmdSQL.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

…然后通过输出变量保存行数的变量…

Int32 intRecCount = Convert.ToInt32(cmdSQL.Parameters["@RecordCount"].Value);
SqlDataAdapter da = new SqlDataAdapter(cmdSQL);
DataSet ds = new DataSet();
da.Fill(ds);
try {
    Connection.Open();
    cmdSQL.ExecuteNonQuery();
}
catch (Exception ex) {   
    dbMsg = ex.Message; 
}
finally {
    Connection.Close();
    cmdSQL.Dispose();
    cmdSQL.Parameters.Clear();
}
return ds;
}

执行存储过程并通过输出和sql参数将行数返回给代码

需要在执行查询后而不是之前访问输出参数的值。因此，在执行查询之后和清除参数之前移动这一行，如:

//VARIABLE TO HOLD ROW COUNT VIA OUTPUT VIARABLE 
Int32 intRecCount = Convert.ToInt32(cmdSQL.Parameters["@RecordCount"].Value);
所以你的方法代码应该是: 
public static DataSet Confirmation()
{ 
SqlCommand cmdSQL = new SqlCommand("uspConfirmation", Connection);
cmdSQL.CommandType = CommandType.StoredProcedure;
cmdSQL.Parameters.Add(new SqlParameter("@RecordID", SqlDbType.VarChar, 36));
cmdSQL.Parameters["@RecordID"].Direction = ParameterDirection.Input;
cmdSQL.Parameters["@RecordID"].Value = RecordIDSession;
cmdSQL.Parameters.Add(new SqlParameter("@LName", SqlDbType.VarChar, 30));
cmdSQL.Parameters["@LName"].Direction = ParameterDirection.Input;
cmdSQL.Parameters["@LName"].Value = LNameSession;
cmdSQL.Parameters.Add(new SqlParameter("@FName", SqlDbType.VarChar, 30));
cmdSQL.Parameters["@FName"].Direction = ParameterDirection.Input;
cmdSQL.Parameters["@FName"].Value = FNameSession;
cmdSQL.Parameters.Add(new SqlParameter("@MInit", SqlDbType.Char, 1));
cmdSQL.Parameters["@MInit"].Direction = ParameterDirection.Input;
cmdSQL.Parameters["@MInit"].Value = MNameSession;
cmdSQL.Parameters.Add(new SqlParameter("@RecordCount", SqlDbType.Int));
cmdSQL.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

SqlDataAdapter da = new SqlDataAdapter(cmdSQL);
DataSet ds = new DataSet();
da.Fill(ds);
Int32 intRecCount = 0;
try
{
    Connection.Open();
    cmdSQL.ExecuteNonQuery();
    //VARIABLE TO HOLD ROW COUNT VIA OUTPUT VIARABLE
    intRecCount = Convert.ToInt32(cmdSQL.Parameters["@RecordCount"].Value);
}
catch (Exception ex)
{
    dbMsg = ex.Message;
}
finally
{
    Connection.Close();
    cmdSQL.Dispose();
    cmdSQL.Parameters.Clear();
}

return ds;
}
相关文章：