如何从存储过程中返回和使用数值字段 [英] how do return and use From Stored Procedure Numeric Filed Value

问题描述

大家好
我想在sql server中返回一个数字字段，并在c#
中使用它
我写了这段代码，但不正确

请帮助我

存储过程代码

hi all
i want return one numeric field in sql server and use from it in c#

i wrote this code but not correct

please help me

Stored Procedure Code

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[Calc_Product_Price]
(@product_id int,
 @Price int output)
as

if not exists (select * from Products where [product_id]=@product_id)
return -1
else
begin
select @Price=[product_sell_price] from Products where [product_id]=@product_id

print @Price
return @Price
end

C#代码:

C# Code :

public int  Calc_Product_price(int Product_id)
{
    SqlConnection MySqlConnection = new SqlConnection(ConnectionMain.strconnection);
    SqlCommand MySqlCommand = MySqlConnection.CreateCommand();
    MySqlCommand.CommandText = "Calc_Product_Price";
    MySqlCommand.CommandType = CommandType.StoredProcedure;

    MySqlCommand.Parameters.Add("@product_id", SqlDbType.BigInt, 8).Value = Product_id;
    MySqlCommand.Parameters.Add("@Price", SqlDbType.BigInt, 8).Value = 0;
    SqlParameter retval = MySqlCommand.Parameters.Add("@Price", SqlDbType.BigInt );
    retval.Direction = ParameterDirection.ReturnValue;


    if (MySqlConnection.State != ConnectionState.Open)
    {
        MySqlConnection.Open();
    }

    MySqlCommand.ExecuteNonQuery();
    int result =(int) MySqlCommand.Parameters["@Price"].Value;

    if (MySqlConnection.State != ConnectionState.Closed)
    {
        MySqlConnection.Close();
    }
    if (result == 1)
    {
        return result;
    }
    else
    {
        return -1;
    }
}

推荐答案

我认为您的这一行应该像这样更改

I think your this line should be change like this

retval.Direction = ParameterDirection.ReturnValue;
retval.Direction=ParameterDirection.Output; // This is important!

了解更多详情
http://stackoverflow.com/Problems/3433694/如何运行存储过程-具有-c的输出参数 [

for more details
http://stackoverflow.com/questions/3433694/how-to-run-the-stored-procedure-that-has-output-parameter-from-c[^]

I hope this will help you.

这篇关于如何从存储过程中返回和使用数值字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！