可以使用DataReader返回out参数
本文关键字:out 参数 返回 DataReader 可以使 | 更新日期: 2023-09-27 18:08:31
使用ExecuteReader我能够返回DataReader
,但是out
参数返回0。
使用ExecuteNonQuery我能够检索out
参数(具有正确的值),但ExecuteNonQuery
不返回DataReader
。
CREATE PROCEDURE [dbo].[SelectDays]
@dateStart datetime,
@dateEnd datetime,
@recordCount bigint out
AS
BEGIN
select @recordCount = count(*)
from dbo.[Days]
where [Date]>=@dateStart and [Date]<=@dateEnd;
select [ID],[Name]
from dbo.[Days]
where [Date]>=@dateStart and [Date]<=@dateEnd;
END
是否有一种方法,我可以返回DataReader
以及out
参数,或者我应该为每个创建两个单独的过程?
Int32 returnValue = 0;
Parameters parameters = new Parameters();
parameters.Add(new SqlParameter("@dateStart", dateStart != null ? (object)dateStart : DBNull.Value));
parameters.Add(new SqlParameter("@dateEnd", dateEnd != null ? (object)dateEnd : DBNull.Value));
SqlParameter out_recordCount = new SqlParameter("@recordCount", SqlDbType.BigInt);
out_recordCount.Direction = ParameterDirection.InputOutput;
out_recordCount.Value = recordCount;
parameters.Add(out_recordCount);
SqlParameter return_Value = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
return_Value.Direction = ParameterDirection.ReturnValue;
parameters.Add(return_Value);
dataReader = this.command.ExecuteReader("dbo.SelectDays", CommandType.StoredProcedure, parameters.ToArray());
if(out_recordCount.Value != DBNull.Value)
recordCount = Convert.ToInt64(out_recordCount.Value);
returnValue = Convert.ToInt32(return_Value.Value);
return returnValue;
标题>标题>
输出参数的值在SQLServer返回的任何结果集之后的流中(我相信返回值也是如此)。这意味着直到从DataReader读取所有行(或者关闭它)之后才会看到该值。因此,告诉您结果集中的行数的输出参数用处不大。
然而,下面的代码片段演示了应该使用的操作序列:using(SqlConnection connection = new SqlConnection("[your connection string here]"))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.SelectDays";
command.Parameters.AddWithValue("@dateStart", dateStart != null ? (object)dateStart : DBNull.Value);
command.Parameters.AddWithValue("@dateEnd", dateEnd != null ? (object)dateEnd : DBNull.Value);
SqlParameter out_recordCount = new SqlParameter("@recordCount", SqlDbType.BigInt);
out_recordCount.Direction = ParameterDirection.InputOutput;
out_recordCount.Value = recordCount;
command.Parameters.Add(out_recordCount);
SqlParameter return_Value = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
return_Value.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(return_Value);
using(SqlDataReader reader = command.ExecuteReader())
{
while(reader.Read()) { /* do whatever with result set data here */ }
}
/* Output and return values are not available until here */
if (out_recordCount.Value != DBNull.Value)
recordCount = Convert.ToInt64(out_recordCount.Value);
returnValue = Convert.ToInt32(return_Value.Value);
return returnValue;
}
}
要使用SqlDataReader从OUTPUT参数中获取值,必须在reader关闭后才能获取。
所以你需要在获取 值之前添加这段代码if(!dataReader.IsClosed)
dataReader.Close();
if(out_recordCount.Value != DBNull.Value)
recordCount = Convert.ToInt64(out_recordCount.Value);
returnValue = Convert.ToInt32(return_Value.Value);