How to Call a Stored Procedure in C#

In C#, a stored procedure is a precompiled collection of SQL statements stored in a database. Calling a stored procedure in C# can be done using ADO.NET, which provides a set of classes for interacting with a database. In this tutorial, we'll discuss how to call a stored procedure in C# using ADO.NET.

Syntax

The syntax for calling a stored procedure in C# is as follows:

using (SqlConnection conn = new SqlConnection(connectionString)) {
   using (SqlCommand cmd = new SqlCommand("StoredProcedureName", conn)) {
      cmd.CommandType = CommandType.StoredProcedure;

      // add parameters as needed
      cmd.Parameters.AddWithValue("@ParameterName", parameterValue);

      // execute the stored procedure
      conn.Open();
      cmd.ExecuteNonQuery();
   }
}

Replace "StoredProcedureName" with the name of the stored procedure you want to call, and add parameters as needed.

Example

Let's say we have a stored procedure called "AddEmployee" that takes three parameters: "EmployeeName", "EmployeeAge", and "EmployeeEmail". Here's how we can call the stored procedure using ADO.NET:

using (SqlConnection conn = new SqlConnection(connectionString)) {
    using (SqlCommand cmd = new SqlCommand("AddEmployee", conn)) {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@EmployeeName", "John Doe");
        cmd.Parameters.AddWithValue("@EmployeeAge", 30);
        cmd.Parameters.AddWithValue("@EmployeeEmail", "johndoe@example.com");

        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

This code creates a SqlConnection object and a SqlCommand object. The command text is set to the name of the stored procedure "AddEmployee". We then add the three input parameters to the SqlCommand object and call the ExecuteNonQuery() method to execute the stored procedure.

Output

When we execute the code above, the stored procedure "AddEmployee" will be executed and the data will be updated in the database. There is no console output for stored procedure execution.

Explanation

In the example above, we created a SqlConnection object and a SqlCommand object to call the stored procedure in the database. We set the CommandType property of the command object to "StoredProcedure" and added the input parameters to the SqlCommand object using the AddWithValue method. We then called the ExecuteNonQuery method of the command object to execute the stored procedure.

Use

Stored procedures are useful when you have complex database operations that involve multiple statements or complex business logic. They can also improve performance by reducing the number of database calls required to execute an operation.

Important Points

• Always sanitize user inputs to prevent SQL injection attacks.
• Close the connection and command objects using the "using" statement to release resources as soon as possible.
• Always handle exceptions appropriately when calling stored procedures.

Summary

In this tutorial, we discussed how to call a stored procedure in C# using ADO.NET. We covered the syntax, example, output, explanation, use, and important points of calling stored procedures. With this knowledge, you can now use stored procedures in your C# code to simplify database operations and improve performance.