SqlDataReader无法获取Procedure的Output参数值?

SqlDataReader无法获取Procedure的Output参数值?

大 | 中 | 小

Post by 幻想曲 at 08 Apr 2007 11:22

<script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> <script language="javascript1.1" src="http://pagead2.googlesyndication.com/cpa/ads?client=ca-pub-3122628043669411&amp;cpa_choice=caaq9cm1_weacbkyusve5rxbkom84ycb&amp;oe=utf-8&amp;dt=1180689775718&amp;lmt=1180689775&amp;format=ref_text&amp;output=textlink&amp;correlator=1180689775703&amp;url=http%3a%2f%2fwww.lemongtree.com%2farchives%2fdatareader_procedure.aspx&amp;region=_google_cpa_region_&amp;ref=http%3a%2f%2fwww.google.cn%2fsearch%3fq%3dout%2bsqldatareader%2b%26complete%3d1%26hl%3dzh-cn%26newwindow%3d1&amp;cc=100&amp;flash=6&amp;u_h=768&amp;u_w=1024&amp;u_ah=738&amp;u_aw=1024&amp;u_cd=32&amp;u_tz=480&amp;u_java=true" type="text/javascript"></script>type="text/javascript"> 网上冲浪更快更爽，下载带有 Google 工具栏的 Firefox
同样的参数去执行一段存储过程
返回类型为DataTable时有值，而返回SqlDataReader时却为空.
public static DataTable ExecuteDataTable(string Text, CommandType commandType, SqlParameter[] para, out int pagecount, out int recordcount)
        {
            using (SqlConnection conn = new SqlConnection(SqlConnectionString))
            {
                SqlCommand comm = conn.CreateCommand();
               
                comm.CommandText = Text;
                comm.CommandType = commandType;
                foreach (SqlParameter p in para)
                {
                    comm.Parameters.Add(p);

                }
                comm.Parameters.Add("@pagecount", SqlDbType.Int);
                comm.Parameters.Add("@RecordCount", SqlDbType.Int);
                comm.Parameters["@pagecount"].Direction = ParameterDirection.Output;
                comm.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                SqlDataAdapter da = new SqlDataAdapter(comm);
                DataSet dst = new DataSet();
                da.Fill(dst);
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                pagecount = (int)comm.Parameters["@pagecount"].Value;  //这里返回正确
                recordcount = (int)comm.Parameters["@RecordCount"].Value;   //这里也正确
                return dst.Tables[0];
            }
        }
返回类型为SqlDataReader时
public static SqlDataReader ExecuteDataReader(string Text, CommandType commandType, SqlParameter[] para, out int pagecount, out int recordcount)
        {
            pagecount = 1;
            recordcount = 0;
            using (SqlConnection conn = new SqlConnection(SqlConnectionString))
            {
                SqlCommand comm = conn.CreateCommand();

                comm.CommandText = Text;
                comm.CommandType = commandType;
                foreach (SqlParameter p in para)
                {
                    comm.Parameters.Add(p);

                }
                comm.Parameters.Add("@PageCount", SqlDbType.Int);
                comm.Parameters.Add("@RecordCount", SqlDbType.Int);
                comm.Parameters["@PageCount"].Direction = ParameterDirection.Output;
                comm.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                conn.Open();
                using (SqlDataReader dr = comm.ExecuteReader())
                {
                    if (dr.Read())
                    {
                        pagecount = (int)comm.Parameters["@pagecount"].Value; //null
                        recordcount = (int)comm.Parameters["@recordcount"].Value;   //null
                    }
                    return dr;
                }
            }
        }
为什么会这样呢?

原因：

当您将 Command 对象用于存储过程时，可以将 Command 对象的 CommandType 属性设置为 StoredProcedure。当 CommandType 为 StoredProcedure 时，可以使用 Command 的 Parameters 属性来访问输入及输出参数和返回值。无论调用哪一个 Execute 方法，都可以访问 Parameters 属性。但是，当调用 ExecuteReader 时，在 DataReader 关闭之前，将无法访问返回值和输出参数。

ref： http://msdn2.microsoft.com/zh-CN/library/tyy0sz6b.aspx

修改了一下：

 SqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
                dr.NextResult();   //加了这一行
                pagecount = (int)comm.Parameters["@pagecount"].Value;
                recordcount = (int)comm.Parameters["@recordcount"].Value;
                
                    return dr;
                }

这样就行了