How To Use Output Parameter In Stored Procedure In C#

 In this code example, we will learn how to create a stored procedure with output parameters and execute it 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.

1. CREATE TABLE [dbo].[tblClients](  
2.     [fldGuid] [uniqueidentifier] NULL,  
3.     [fldClientID] [int] NULL,  
4.     [fldClientName] [nvarchar](50) NULL,  
5.     [fldDateCreated] [datetime] NULL,  
6.     [fldCellPhone] [nvarchar](30) NULL,  
7.     [fldEMail] [nvarchar](60) NULL,  
8.     [fldPK] [int] NOT NULL,  
9.  CONSTRAINT [tblClients_pk] PRIMARY KEY NONCLUSTERED   
10. (  
11.     [fldPK] ASC  
12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
13. )  

Now create a stored procedure with three output parameters.

1. CREATE PROCEDURE GetClientDetails  
2. (  
3.     @ClientID  INT,  
4.     @Guid VARCHAR(100)   OUTPUT,  
5.     @ClientName VARCHAR(100)    OUTPUT,  
6.     @DateCreated DATETIME    OUTPUT  
7. )  
8. AS  
9. BEGIN  
10.     SELECT @Guid = fldGuid, @ClientName = fldClientName, @DateCreated = fldDateCreated   
11.     FROM tblClients WHERE fldClientID = @ClientID  
12. 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 stored procedure from C# code.

 

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

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

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

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

1. string clientGuid = "";
   
2. string clientName = "";
   
3. DateTime dateCreated = null;
   
4. private Tuple<string, string, DateTime> StoredProcWithOutPutParameter(int clientId)  
5. {  
6.     SqlConnection conn = new SqlConnection();  
7.     SqlCommand cmd = new SqlCommand();  
8.     conn.ConnectionString = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;  
9.     cmd.Connection = conn;  
10.     cmd.CommandType = CommandType.StoredProcedure;  
11.     cmd.CommandText = "GetClientDetails";              
12.     cmd.Parameters.AddWithValue("@ClientID", clientId);  
13.               
14.     cmd.Parameters.Add("@Guid", SqlDbType.VarChar, 100);  
15.     cmd.Parameters["@Guid"].Direction = ParameterDirection.Output;  
16.     cmd.Parameters.Add("@ClientName", SqlDbType.VarChar, 100);  
17.     cmd.Parameters["@ClientName"].Direction = ParameterDirection.Output;  
18.     cmd.Parameters.Add("@DateCreated", SqlDbType.DateTime, 20);  
19.     cmd.Parameters["@DateCreated"].Direction = ParameterDirection.Output;             
20.     try  
21.     {  
22.         conn.Open();  
23.         int i = cmd.ExecuteNonQuery();  
24.         //Storing the output parameters value in 3 different variables.  
25.         clientGuid = Convert.ToString(cmd.Parameters["@Guid"].Value);  
26.         clientName = Convert.ToString(cmd.Parameters["@ClientName"].Value);  
27.         dateCreated = Convert.ToDateTime(cmd.Parameters["@DateCreated"].Value);  
28.         // Here we get all three values from database in above three variables.  
29.     }  
30.     catch (Exception ex)  
31.     {  
32.         // throw the exception  
33.     }  
34.     finally  
35.     {  
36.         conn.Close();  
37.     }
38.     return new Tuple<string, string, DateTime>(clientGuid, clientName, dateCreated); 
    
39. }