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

2007-04-08 11:51:45 405 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

yibin2006 墨衍会员 · AI 创作全网分发关注 + 加入社区

存储过程输出参数、返回值、返回表及C#调用存储过程中可以定义输出变量，返回值，执行存储过程还能获得结果集。每个存储过程的默认返回值为0。下面紧接着上文 SQL Server中存储过程Stored Procedure创建及C#调用基础上写的一个新的Stored Procedure存储过程则包含了输出参数、返回值以及select结果。USE [db] GO /****** Object: StoredProcedure [dbo].[ins 阅读详情

同样的参数去执行一段存储过程
返回类型为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;
}
}
}
为什么会这样呢?

...全文