Invoking a Stored Procedure with Parameters in C#

Introduction

In database-driven applications, stored procedures play a crucial role by encapsulating complex business logic within the database itself. This not only enhances performance but also improves security and maintainability. In this tutorial, we’ll explore how to call a stored procedure that requires parameters using C#. We will cover best practices such as proper resource management with using statements and parameter handling techniques.

Prerequisites

Before proceeding, ensure you have the following setup:

A working C# environment (such as Visual Studio).
Basic knowledge of SQL Server databases.
A table named tblContacts in your database for demonstration purposes.

Creating a Stored Procedure

First, let’s create a stored procedure named sp_Add_contact. This procedure will insert a new contact into the tblContacts table:

CREATE PROCEDURE sp_Add_contact 
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50)
AS
BEGIN
    INSERT INTO tblContacts (FirstName, LastName) VALUES (@FirstName, @LastName);
END;

This stored procedure accepts two parameters (@FirstName and @LastName) and inserts a new row into the tblContacts table.

Setting Up Your C# Project

In your C# project, ensure you have added a reference to System.Data.SqlClient, which is necessary for database operations.

Calling a Stored Procedure with Parameters in C#

Below is a comprehensive example of how to call the sp_Add_contact stored procedure from a C# application:

using System;
using System.Data;
using System.Data.SqlClient;

class ContactManager
{
    private string connectionString; // Your connection string

    public ContactManager(string connString)
    {
        connectionString = connString;
    }

    public void AddContact(string firstName, string lastName)
    {
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                // Adding parameters to the command
                cmd.Parameters.AddWithValue("@FirstName", SqlDbType.VarChar).Value = firstName;
                cmd.Parameters.AddWithValue("@LastName", SqlDbType.VarChar).Value = lastName;

                try
                {
                    con.Open();
                    cmd.ExecuteNonQuery(); // Execute the stored procedure
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"An error occurred: {ex.Message}");
                }
            }
        } // SqlConnection and SqlCommand are automatically disposed here
    }
}

Explanation

Using Statements: We employ using statements to ensure that the SqlConnection and SqlCommand objects are properly disposed of, even if an exception occurs.
Connection String: A connection string is essential for establishing a connection to your SQL Server database.
Command Type: The command type is set to StoredProcedure, indicating that we’re executing a stored procedure rather than a regular query or text statement.
Parameters: Parameters are added using the AddWithValue method, which simplifies parameter management and makes the code cleaner.
Error Handling: A try-catch block ensures any exceptions during execution are caught and logged, preventing application crashes due to unhandled exceptions.

Best Practices

Parameterization: Always use parameters with stored procedures to prevent SQL injection attacks.
Resource Management: Use using statements for managing resources such as database connections and commands.
Error Handling: Implement proper error handling to manage any potential runtime errors gracefully.
Performance Considerations: Stored procedures are compiled, which generally leads to better performance compared to executing raw SQL queries.

Conclusion

By following this tutorial, you’ve learned how to call a stored procedure with parameters in C#. This technique is vital for building robust and efficient data-driven applications. Always ensure to follow best practices like using using statements and proper error handling to maintain clean and reliable code.