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

Executing Stored Procedure and returning row count back to code via output and sql parameters

提问by Asynchronous

I have the following code that matches user input via session variables. The Stored Procedure returns the Row Count if the data in the Session Variable matches the data in the database.

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

Everything works except I want to return the Row Count which will always be a single row: In a nutshell, you visit a form, add info and hit submit. The data is stored in session and the stored procedure returns the data when matched.

一切正常，除了我想返回 Row Count 这将始终是一行：简而言之，您访问一个表单，添加信息并点击提交。数据存储在会话中，存储过程在匹配时返回数据。

Even though the program works the intRecCountvariable is always zero rather than the row count.

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

Stored Procedure:

存储过程：

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

Method/Code:

方法/代码：

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;

... then a variable to hold the row count via an output variable ...

...然后是一个通过输出变量保存行数的变量 ...

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;
}

采纳答案by Habib

You need to access the value of output parameter after executing the query not before. So move this line after execution of query, and before clearing parameters, like:

您需要在执行查询之后访问输出参数的值而不是之前。所以在执行查询之后和清除参数之前移动这一行，例如：

//VARIABLE TO HOLD ROW COUNT VIA OUTPUT VIARABLE 
Int32 intRecCount = Convert.ToInt32(cmdSQL.Parameters["@RecordCount"].Value);

So your code for method would be:

所以你的方法代码是：

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;
}