我有一个接受输入并返回多列的存储过程。当我从SSMS和VS 2013内部执行存储过程时,该存储过程可以工作。但是,当我尝试使用SqlCommand.ExecuteReader执行该存储过程时,阅读器中没有任何行。如果我从proc和SqlCommand中删除输出参数,同时仍保留一个输入参数,则可以返回要查找的行。
这是存储的过程
create Proc sp_ReturnSingleGame
@GameName varchar(100) output,
@PlatformName varchar(50) output,
@ConditionShortDescription varchar(30) output,
@RetailCost decimal(6,2) output,
@InStock bit output,
@GameID int
AS
select @GameName = GameName, @PlatformName = PlatformName,
@ConditionShortDescription = ConditionShortDescription, @RetailCost = RetailCost
from Games inner join Condition
on Games.ConditionID = Condition.ConditionID
inner join ConsolePlatform
on Games.PlatformID = ConsolePlatform.PlatformID
Where Games.GameID = @GameID
if exists (select GameID
From SaleItemized
Where GameID = @GameID)
Begin
set @InStock = 1;
end
else
Begin
set @InStock = 0;
end
这是我的C#代码
public Game ReturnSingleGame(int gameId)
{
SqlConnection connection = new SqlConnection(@"server=mylaptop; integrated security=true; database=GameStoreDB;");
SqlCommand command = this.ReturnCommandForSp_ReturnSingleGame(connection, gameId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows == true)
{
reader.Read();
game.GameId = gameId;
game.GameName = reader["GameName"].ToString();
game.PlatformName = reader["PlatformName"].ToString();
game.RetailCost = (decimal) reader["RetailCost"];
}
else
{
var exception = new ApplicationException("Game was not found");
throw exception;
}
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
return game;
}
private SqlCommand CommandForSp_ReturnSingleGame(SqlConnection connection, int gameId)
{
string storedProc = @"dbo.sp_ReturnSingleGame";
SqlCommand command = new SqlCommand(storedProc, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@GameName", SqlDbType.VarChar, 100, "GameName");
command.Parameters["@GameName"].Direction = ParameterDirection.Output;
command.Parameters.Add("@PlatformName", SqlDbType.VarChar, 50, "PlatformName");
command.Parameters["@PlatformName"].Direction = ParameterDirection.Output;
command.Parameters.Add("@ConditionShortDescription", SqlDbType.VarChar, 30, "ConditionShortDescription");
command.Parameters["@ConditionShortDescription"].Direction = ParameterDirection.Output;
command.Parameters.Add("@RetailCost", SqlDbType.Decimal);
command.Parameters["@RetailCost"].SourceColumn = "RetailCost";
command.Parameters["@RetailCost"].Precision = 6;
command.Parameters["@RetailCost"].Scale = 2;
command.Parameters["@RetailCost"].Direction = ParameterDirection.Output;
command.Parameters.Add("@InStock", SqlDbType.Bit);
command.Parameters["@InStock"].SourceColumn = "InStock";
command.Parameters["@InStock"].Direction = ParameterDirection.Output;
command.Parameters.Add("@GameID", SqlDbType.Int).Value = gameId;
command.Parameters["@GameID"].SourceColumn = "GameID";
command.Parameters["@GameID"].Direction = ParameterDirection.Input;
command.Prepare();
return command;
}
您提供的存储过程实际上不返回任何数据行。
它所做的一切-只是设置输出参数。
因此,您无需SqlDataReader
检索任何参数。
只需调用command.ExecuteNonQuery()
,然后从中获取参数值command.Parameters["@GameName"].Value
,依此类推。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句