How To Use Output Parameter In Stored Procedure In C#

In this article, we will learn how to execute stored procedures with output parameters in C# code.

Introduction

In this code example, we will learn how to create a stored procedure with output parameters executed in a C# code and return back the values to the caller function.

First, we create a table in the database and also create a stored procedure with an output parameter.

Now create a table in the database.

CREATE TABLE [dbo].[tblClients](
    [fldGuid] [uniqueidentifier] NULL,
    [fldClientID] [int] NULL,
    [fldClientName] [nvarchar](50) NULL,
    [fldDateCreated] [datetime] NULL,
    [fldCellPhone] [nvarchar](30) NULL,
    [fldEMail] [nvarchar](60) NULL,
    [fldPK] [int] NOT NULL,
    CONSTRAINT [tblClients_pk] PRIMARY KEY NONCLUSTERED
    (
        [fldPK] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

Now create a stored procedure with three output parameters.

CREATE PROCEDURE GetClientDetails
(
    @ClientID INT,
    @Guid VARCHAR(100) OUTPUT,
    @ClientName VARCHAR(100) OUTPUT,
    @DateCreated DATETIME OUTPUT
)
AS
BEGIN
    SELECT @Guid = fldGuid, @ClientName = fldClientName, @DateCreated = fldDateCreated
    FROM tblClients WHERE fldClientID = @ClientID
END

In the above-stored procedure, @ClientID is the input parameter, and others are the output parameters.

In this stored procedure, we get client details using input and output parameters.

Now let's call a stored procedure from C# code.

In app.config/web.config add the following code in <configuration></configuration> section.

<appSettings>
    <add key="CS" value="server=.;database=myDb;Integrated Security=SSPI;" />
</appSettings>

In the code behind, write the following code on a button click event handler or where you see fit.

private void btnShow_Click(object sender, EventArgs e)
{
    // Here we pass 1001 as clientid.
    // Now show all three values
    var getData = StoredProcWithOutPutParameter(1001);
    MessageBox.Show(string.Format("Client guid: {0}, Client Name: {1}, Date: {2}", clientGuid, clientName, dateCreated));
}

string clientGuid = "";
string clientName = "";
DateTime dateCreated = DateTime.MinValue;

private Tuple<string, string, DateTime> StoredProcWithOutPutParameter(int clientId)
{
    SqlConnection conn = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
    cmd.Connection = conn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "GetClientDetails";
    cmd.Parameters.AddWithValue("@ClientID", clientId);

    cmd.Parameters.Add("@Guid", SqlDbType.VarChar, 100);
    cmd.Parameters["@Guid"].Direction = ParameterDirection.Output;
    cmd.Parameters.Add("@ClientName", SqlDbType.VarChar, 100);
    cmd.Parameters["@ClientName"].Direction = ParameterDirection.Output;
    cmd.Parameters.Add("@DateCreated", SqlDbType.DateTime);
    cmd.Parameters["@DateCreated"].Direction = ParameterDirection.Output;

    try
    {
        conn.Open();
        int i = cmd.ExecuteNonQuery();
        // Storing the output parameters' values in three different variables.
        clientGuid = Convert.ToString(cmd.Parameters["@Guid"].Value);
        clientName = Convert.ToString(cmd.Parameters["@ClientName"].Value);
        dateCreated = Convert.ToDateTime(cmd.Parameters["@DateCreated"].Value);
        // Here we get all three values from the database in the above three variables.
    }
    catch (Exception ex)
    {
        // Handle the exception
    }
    finally
    {
        conn.Close();
    }
    return new Tuple<string, string, DateTime>(clientGuid, clientName, dateCreated);
}