输出参数始终返回0
本文关键字:返回 参数 输出 | 更新日期: 2023-09-27 18:05:26
有人能看看他们是否能找出为什么我的输出参数总是返回0吗。
它可能很简单,我就是看不见。
-----------------------SP-------------------------
ALTER PROCEDURE [dbo].[test]
-- Add the parameters for the stored procedure here
@SearchPhrase VarChar(100),
@RecordsFound INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
loads of stuff from table
set @RecordsFound = @@ROWCOUNT;
END
------------------C#代码-----------------------
using (SqlConnection cn = new SqlConnection(dbConn))
{
using (SqlCommand cmd = new SqlCommand(spName, cn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@SearchPhrase", SqlDbType.VarChar, 100));
cmd.Parameters["@SearchPhrase"].Value = id;
cmd.Parameters.Add(new SqlParameter("@RecordsFound", SqlDbType.Int));
cmd.Parameters["@RecordsFound"].Direction = ParameterDirection.Output;
List<News> Data = new List<News>();
try
{
cn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.Default))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
Data.Add(new News
{
strNewsTitle = (string)rdr["NewsTitle"],
strNewsDatePosted = (string)rdr["NewsDatePosted"],
strNewsDescription = (string)rdr["NewsDescription"],
strLocationOfImageURL = (string)rdr["ImageURL"],
recordsReturned = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value)
});
int recordsReturned1 = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value);
}
int recordsReturned2 = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value);
}
return Data;
在测试存储过程时,我可以看到@RecordsFound确实返回了一个值,所以我认为问题出在我的代码上。
感谢
George
在MSDN上的页面
如果命令包含输出参数或返回值,它们将在关闭DataReader之前不可用。
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.Default))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
Data.Add(new News
{
strNewsTitle = (string)rdr["NewsTitle"],
strNewsDatePosted = (string)rdr["NewsDatePosted"],
strNewsDescription = (string)rdr["NewsDescription"],
strLocationOfImageURL = (string)rdr["ImageURL"],
recordsReturned = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value)
});
// NOT AVAILABLE HERE - READER IS OPEN
// int recordsReturned1 = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value);
}
// STILL OPEN
//int recordsReturned2 = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value);
}
}
// Data reader closed and disposed, get output parameter here or never
int recordsReturned2 = Convert.ToInt32(cmd.Parameters["@RecordsFound"].Value);
SET NOCOUNT ON;
未启用行计数,因此默认为0。