输出参数总是返回0

Question

任何人都可以请看看，看看他们是否能找出为什么我的输出参数总是返回0。

其proberbly简单的东西，我只是无法看到它。

----------------------- SP ---------------------- ------

ALTER PROCEDURE [dbo].[test] 
    -- Add the parameters for the stored procedure here 
    @SearchPhrase VarChar(100), 
    @RecordsFound INT OUTPUT 
AS 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from 
    -- interfering with SELECT statements. 
    SET NOCOUNT ON; 

    -- Insert statements for procedure here 
    SELECT 
      loads of stuff from table 

    set @RecordsFound = @@ROWCOUNT; 
END 

------------------ C＃code ----------------- --------

using (SqlConnection cn = new SqlConnection(dbConn)) 
       { 
       using (SqlCommand cmd = new SqlCommand(spName, cn)) 
        { 
        cmd.CommandType = CommandType.StoredProcedure; 
        cmd.Parameters.Add(new SqlParameter("@SearchPhrase", SqlDbType.VarChar, 100)); 
        cmd.Parameters["@SearchPhrase"].Value = id; 
        cmd.Parameters.Add(new SqlParameter("@RecordsFound", SqlDbType.Int)); 
        cmd.Parameters["@RecordsFound"].Direction = ParameterDirection.Output; 

        List<News> Data = new List<News>(); 
        try 
         { 
         cn.Open(); 
         using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.Default)) 
          { 
          if (rdr.HasRows) 
           { 
           while (rdr.Read()) 
            { 
            Data.Add(new News 
            { 
             strNewsTitle   = (string)rdr["NewsTitle"], 
             strNewsDatePosted  = (string)rdr["NewsDatePosted"], 
             strNewsDescription  = (string)rdr["NewsDescription"], 
             strLocationOfImageURL = (string)rdr["ImageURL"], 
             recordsReturned = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value) 
            }); 
            int recordsReturned1 = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value); 
            } 
           int recordsReturned2 = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value); 
           } 


          return Data; 

当测试存储过程，我可以看到，@RecordsFound确实返回一个值，所以我认为问题是我的代码。

感谢

乔治

Answer 1

On MSDN at this page

如果命令包含输出参数或返回值，直到DataReader关闭，他们将 不可用。

using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.Default)) 
{ 
    if (rdr.HasRows) 
    { 
     while (rdr.Read()) 
     { 
      Data.Add(new News 
      { 
       strNewsTitle = (string)rdr["NewsTitle"], 
       strNewsDatePosted = (string)rdr["NewsDatePosted"], 
       strNewsDescription = (string)rdr["NewsDescription"], 
       strLocationOfImageURL = (string)rdr["ImageURL"], 
       recordsReturned = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value) 
      }); 
      // NOT AVAILABLE HERE - READER IS OPEN 
      // int recordsReturned1 = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value); 
     } 
     // STILL OPEN 
     //int recordsReturned2 = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value); 
    } 
} 
// Data reader closed and disposed, get output parameter here or never 
int recordsReturned2 = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value); 

Answer 2

SET NOCOUNT ON; 

未启用行计数，因此0为默认值。