Calling SQL Stored Procedures in C#

Learn how to efficiently call SQL Server stored procedures from your C# applications. This tutorial covers establishing database connections using `SqlConnection`, creating `SqlCommand` objects, adding parameters with `AddWithValue()`, and executing procedures using `ExecuteNonQuery()` and `ExecuteReader()`. Improve your application's performance, security, and code organization by mastering this essential skill. We'll cover connection strings, parameter handling, and efficient data retrieval techniques.

Calling Stored Procedures in C#

Stored procedures are pre-compiled SQL code stored on a database server. Calling them from your C# application offers several advantages, including improved performance, security, and code organization.

1. Establish a Database Connection

Before calling a stored procedure, you need a database connection. Use the `SqlConnection` class (in `System.Data.SqlClient`) and provide a valid connection string containing server details and credentials:

using System.Data.SqlClient;

string connectionString = "YourConnectionString"; // Replace with your actual connection string
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

2. Create a Command Object

Create a `SqlCommand` object specifying the stored procedure's name and the connection object:

SqlCommand command = new SqlCommand("YourStoredProcedureName", connection);
command.CommandType = CommandType.StoredProcedure;

3. Add Parameters (If Necessary)

If your stored procedure accepts parameters, add them to the `command` object using `AddWithValue()`:

command.Parameters.AddWithValue("@param1", value1); 

4. Execute the Stored Procedure

Execute the stored procedure using either `ExecuteNonQuery()` (for procedures that don't return data) or `ExecuteReader()` (for procedures that return a result set):

// For procedures without a result set:
command.ExecuteNonQuery();

// For procedures returning a result set:
SqlDataReader reader = command.ExecuteReader();
while (reader.Read()) {
    // Process the data from the reader
}
reader.Close();

5. Close the Connection

After execution, close the connection to release resources:

connection.Close();

Handling Output Parameters

Retrieve output parameters (values returned by the stored procedure) using the `Parameters` collection:

SqlParameter outputParam = new SqlParameter("@outputParam", SqlDbType.Int);
outputParam.Direction = ParameterDirection.Output;
command.Parameters.Add(outputParam);
command.ExecuteNonQuery();
int outputValue = (int)command.Parameters["@outputParam"].Value;

Transaction Management

Use transactions for multiple database operations that must succeed or fail together:

SqlTransaction transaction = connection.BeginTransaction();
try {
    // ... (execute stored procedures within the transaction) ...
    transaction.Commit();
} catch (Exception ex) {
    transaction.Rollback();
    //Handle Exception
}

Handling Return Values

Stored procedures can return integer values indicating success or failure. Access this using `ParameterDirection.ReturnValue`:

SqlParameter returnValue = new SqlParameter();
returnValue.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);
command.ExecuteNonQuery();
int returnVal = (int)command.Parameters["@ReturnValue"].Value;

Error Handling

Always include error handling (using `try-catch` blocks) to gracefully manage potential exceptions:

try {
    // ... (your stored procedure call) ...
} catch (SqlException ex) {
    // Handle database-specific errors
} catch (Exception ex) {
    //Handle other errors
}

Parameterized Queries

Use parameterized queries to prevent SQL injection vulnerabilities and improve performance. Avoid string concatenation.

string query = "SELECT * FROM Users WHERE Id = @userId";
command.Parameters.AddWithValue("@userId", userId);