返回使用SqlCommand的值 [英] Return Value Using SqlCommand

问题描述

我想要得到的结果集，并返回一个存储过程的价值SQL 2008服务器上。当我运行在SQL Management Studio中的PROC，我得到的结果集和返回值。

I'm trying to get the result set and return value of a stored proc on SQL 2008 server. When I run the proc in sql management studio, I get the result set and the return value.

然而，当我尝试在C＃4.0的值，参数的值是零。下面是我的C＃code：

However, when I try to get the value in C# 4.0, the value of the parameter is null. Here is my C# code:

using (ConnectionManager<SqlConnection> cn = ConnectionManager<SqlConnection>.GetManager(CultureInfo.CurrentCulture.Name))
{
    using (SqlCommand cm = cn.Connection.CreateCommand())
    {
        cm.CommandText = "Name of proc here";
        cm.CommandType = CommandType.StoredProcedure;

        cm.Parameters.AddWithValue("@ApplicationId", ApplicationId);                                        
        cm.Parameters.AddWithValue("@Index", Index);

        if (PageSize > 0)
            cm.Parameters.AddWithValue("@PageSize", PageSize);

        cm.Parameters.Add("@ReturnValue", SqlDbType.Int);
        cm.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;

        using (IDataReader dr = cm.ExecuteReader())
        {
            SafeDataReader sdr = new SafeDataReader(dr);
            while (sdr.Read())
            {
                UserApplicationEntity uae = new UserApplicationEntity();
                uae.UserId = sdr.GetGuid("UserId");
                uae.ExternalId = sdr.GetString("ExternalId");
                Result.Value.Collection.Add(uae);
            }

            Result.Value.TotalResults = (int)cm.Parameters["@ReturnValue"].Value;
        }
    }
}

在这里我呼吁Result.Value.TotalResults =（int）的cm.Parameters [@返回值]值的最后一行。正是值为NULL。每一个教程或交的，我发现，我似乎是正确无所不为。在这一点上我想我只是失去了一些东西小，需要另一套眼睛。我也尝试了所有作为一个职位，我发现在MS的网站上的其他人之前设置返回参数说我需要，但无论在哪里，它在，则返回null。

The last line where I call Result.Value.TotalResults = (int)cm.Parameters["@ReturnValue"].Value; is where Value is null. Every tutorial or post I've found, I appear to be doing everything correctly. At this point I think I'm just missing something small and need another set of eyes. I've also tried setting the return parameter before all the others as one post I found on MS site said I needed to, but regardless of where it's at, it returns null.

推荐答案

返回值是最后一个从数据库的响应中发送，所以你必须读结果集之前完全可以访问返回值。

The return value is sent last in the response from the database, so you have to read the result set completely before you can access the return value.

什么是 SafeDataReader 类呢？有没有可能，这prevents整个结果被读取设置？

What does the SafeDataReader class do? Is it possible that it prevents the entire result set from being read?

尝试移动code读取外返回值使用块的数据读取器。这可能有助于推进超出结果集响应，从而使返回值是可用的。

Try to move the code that reads the return value outside the using block for the data reader. That may help to advance the response beyond the result set so that the return value is available.

这篇关于返回使用SqlCommand的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！