Using Stored Procedure Output Parameters in C#

how to get Stored Procedure output parameter in c#

If you want to get value of output parameter outside the method without changing the return type of the method, you may use out parameters in C#.

Change your method definition like below:

public DataSet GetOrderItemPictureByOrderId(int orderID, int PageIndex, out string outputValue)
{
    //code here
    outputValue = db.GetParameterValue(cmd, "@RecordCount");
    //code here

}

and then call this method

 string outvalue;
 DataSet _ds = _orderPicDAC.GetOrderItemPictureByOrderId(OrderID, PageIndex,out outvalue;);

You will have the value in outvalue variable.

When C# calls stored procedures with output parameters, no data is querying

You should retrieve the values of Output parameters after you have consumed all of the result sets from SqlDataAdapter (and disposed of it). Try restructuring your code such as the following:

DataTable dataTable = new DataTable();
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(command))
{
    sqlDataAdapter.Fill(dataTable);
}
object value = command.Parameters["@newPageIndex"].Value;
newPageIndex = value != null && value != DBNull.Value ? (int)value : -1;
return dataTable;

How to get output parameter and also a table when executing a stored procedure

The parameter value won't be available until after you consume the resultset, eg

var cmd0 = new SqlCommand("create or alter procedure pFoo @id int output as begin  select * from sys.objects; set @id = 12; end", con);
cmd0.ExecuteNonQuery();

var cmd = new SqlCommand("pFoo", con);
cmd.CommandType = CommandType.StoredProcedure;

var p1 = cmd.Parameters.Add("@id", SqlDbType.Int);
p1.Direction = ParameterDirection.Output;


var dt = new DataTable();
using (var rdr = cmd.ExecuteReader())
{                    
    dt.Load(rdr);
    var id = (int)p1.Value;
}

How to use OUTPUT parameter in Stored Procedure

The SQL in your SP is wrong. You probably want

Select @code = RecItemCode from Receipt where RecTransaction = @id

In your statement, you are not setting @code, you are trying to use it for the value of RecItemCode. This would explain your NullReferenceException when you try to use the output parameter, because a value is never assigned to it and you're getting a default null.

The other issue is that your SQL statement if rewritten as

Select @code = RecItemCode, RecUsername from Receipt where RecTransaction = @id

It is mixing variable assignment and data retrieval. This highlights a couple of points. If you need the data that is driving @code in addition to other parts of the data, forget the output parameter and just select the data.

Select RecItemCode, RecUsername from Receipt where RecTransaction = @id

If you just need the code, use the first SQL statement I showed you. On the offhand chance you actually need the output and the data, use two different statements

Select @code = RecItemCode from Receipt where RecTransaction = @id
Select RecItemCode, RecUsername from Receipt where RecTransaction = @id

This should assign your value to the output parameter as well as return two columns of data in a row. However, this strikes me as terribly redundant.

If you write your SP as I have shown at the very top, simply invoke cmd.ExecuteNonQuery(); and then read the output parameter value.

Another issue with your SP and code. In your SP, you have declared @code as varchar. In your code, you specify the parameter type as Int. Either change your SP or your code to make the types consistent.

Also note: If all you are doing is returning a single value, there's another way to do it that does not involve output parameters at all. You could write

 Select RecItemCode from Receipt where RecTransaction = @id

And then use object obj = cmd.ExecuteScalar(); to get the result, no need for an output parameter in the SP or in your code.

C# EnterpriseLibrary trouble obtaining an Output Parameter

AddOutParameter doesn't take a value as its last parameter, but it need a size (for a boolean I suppose its just one byte). Moreover output parameters contain a valid value only after you finished with the command. So given the fact that AddOutParameter is void, you need a way to get back that parameter and look at its value after the execution of the query.

I cannot test it but is seems logical to follow this path.

db.AddOutParameter(cmd, "@Bit", DbType.Boolean, 1);
db.ExecuteNonQuery(cmd);

// Get the parameter back after the completition of the command.
DbParameter par = cmd.Parameters.GetParameter("@Bit");

// The value property is of type object and can be null, but given the SP
// above it should never be null, so we can have
return Convert.ToInt32(par.Value);

How To Use Output Parameter In Stored Procedure In C#

You can get the out parameter value in your AddPaymentTracking method bu not in btnSave_Click unless you also expose it from your AddPaymentTracking method as an output (or ref) parameter.

    public async Task AddPaymentTracking(string NPaymentRequest, DateTime DatePayment, out int newRecordId)
    {
        . . .
        . . .

        await Task.Run(() => DAL.ExcuteCommande("AddPaymentTracking", param)).ConfigureAwait(false);

        newRecordId = (int)param[2].Value;
        DAL.Close();
    }

And this is how you would call the method with its new signature:

    private async void btnSave_Click(object sender, EventArgs e)
    {
        int output;
        await supply.AddPaymentTracking
        (
            txtNPaymentRequest.Text,
            Convert.ToDateTime(txtDatePayment.EditValue, CultureInfo.CurrentCulture), 
            out output
        ).ConfigureAwait(true);

        . . .
        . . .
    }