读取输出参数，无需关闭的DataReader [英] Reading output parameter without closing the datareader

问题描述

我不知道这甚至有可能只是需要一个解决方案，以我目前的问题。 我在数据层返回SqlDataReader对象的方法。这就是所谓的由业务层后。

I am not sure if this is even possible but just needed a solution to my current issue. I have a method in Data layer which returns SqlDataReader object. this is called by Business layer later.

public SqlDataReader GetAll(out int count)
{
    using (SqlConnection conn = new SqlConnection())
{
    IDataReader reader = null;
    SqlCommand cmd = new SqlCommand("sproc", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    // add parameters
    SqlParameter outputParam = cmd.Parameters.Add("@Count", SqlDbType.Int);
    outputParam.Direction = ParameterDirection.Output;

    conn.Open();

    reader = cmd.ExecuteReader();
    {
        while(reader.Read())
        {
            //read in data
        }
        **// not possible as the reader is not closed.
        // need to set this out variable here
        count = outputParam.Value; //doesn't work/**

    }

}
return reader;
}

请让我知道如果这个问题不明确。

please let me know if the question is not clear.

推荐答案

请看看这片code：

using(SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            SqlCommand command = new SqlCommand("testproc", connection);
            command.CommandType = CommandType.StoredProcedure;

            var countParam = command.CreateParameter();
            countParam.ParameterName = "@TotalCount";
            countParam.Direction = ParameterDirection.Output;
            countParam.DbType = DbType.Int32;
            command.Parameters.Add(countParam);


            using (IDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.Write("exec");
                }

                totalCount = (int)countParam.Value; //totalCount has valid value 
            }
        }

这篇关于读取输出参数，无需关闭的DataReader的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！