How To Use Output Parameter In Stored Procedure In C#

Question

First of all I did a lot of research on this topic but unfortunately I did not find what works for me. This is my stored procedure

USE [SIM]
GO
Create Proc [dbo].[AddPaymentTracking]

       @NPaymentRequest nvarchar(50),
       @DatePayment date,
       @id int out
AS
INSERT INTO [dbo].[PaymentTracking]
       ([NPaymentRequest]
       ,[DatePayment])

 VALUES
       (@NPaymentRequest, 
       @DatePayment)

Set @id=@@IDENTITY

and this is my c# class code

public async Task AddPaymentTracking(string NPaymentRequest, DateTime DatePayment)
    {
        DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
        await Task.Run(() => DAL.Open()).ConfigureAwait(false);
        SqlParameter[] param = new SqlParameter[3];

        param[0] = new SqlParameter("@NPaymentRequest", SqlDbType.NVarChar, 50)
        {
            Value = NPaymentRequest
        };

        param[1] = new SqlParameter("@DatePayment", SqlDbType.Date)
        {
            Value = DatePayment
        };

        param[2] = new SqlParameter("@id", SqlDbType.Int);
        param[2].Direction = ParameterDirection.Output;



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

    }

and this is my c# form code

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

How can I do it ?
Thanks in advance

You can get the out parameter value in your AddPaymentTracking method bu not in btnSave_Click, unless you also expose it from AddPaymentTracking method as an output parameter. Try after DAL.ExcuteCommande: var outVal = param[2].Value.. Add an out (or ref) parameter to your AddPaymentTracking method and assign this value to it before returning. — Oguz Ozgul
– Oguz Ozgul, Commented Mar 20, 2020 at 18:29
You said, "I did a lot of research on this topic but unfortunately I did not find what works for me", yet when I googled your topic title, one of the first results is: Example on how to use output parameter in stored procedure in C#. Did that not work for you? I don't see where your code is trying to access the out parameter value. — littlerufe
– littlerufe, Commented Mar 20, 2020 at 18:31
@RufusL it's not. There is a comment in the event handler btnSave_Click: //int output = Get output Parameter — Oguz Ozgul
– Oguz Ozgul, Commented Mar 20, 2020 at 18:32
@OguzOzgul Thank you very much can you please write it in answer section so I can make it as an answer for my question — M.Bouabdallah
– M.Bouabdallah, Commented Mar 20, 2020 at 19:01

Oguz Ozgul · Accepted Answer · 2020-03-20 20:49:02Z

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);

        . . .
        . . .
    }

Collectives™ on Stack Overflow

How To Use Output Parameter In Stored Procedure In C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related