Topic 14530 min readStructured Tutorial

Stored Procedures in C# Database Programming

Quick Answer

Stored procedures are precompiled SQL statements stored in the database that can be executed from C# code to improve performance, security, and maintainability. Using stored procedures in C# involves creating them in the database and calling them via ADO.NET or ORM frameworks.

Learning Objectives

Explain the purpose of Stored Procedures in a practical learning context.
Identify the main ideas, terms, and decisions involved in Stored Procedures.
Apply Stored Procedures in a simple real-world scenario or practice task.

Introduction

Stored procedures are an essential part of database programming that help encapsulate SQL logic on the database server.

In C#, stored procedures can be called to execute complex queries or operations efficiently and securely.

Encapsulate your SQL logic in stored procedures for better performance and security.

What Are Stored Procedures?

A stored procedure is a set of SQL statements that are stored in the database and can be executed repeatedly by applications.

They help reduce network traffic by executing multiple SQL commands in a single call and improve security by controlling access to data.

Precompiled and stored in the database
Can accept parameters
Return results or affect data
Improve performance and security

Benefits of Using Stored Procedures

Using stored procedures offers several advantages over embedding SQL directly in application code.

Improved performance due to precompilation
Reduced network traffic by batching commands
Enhanced security by restricting direct table access
Easier maintenance and code reuse

Creating a Stored Procedure in SQL Server

Stored procedures are created using SQL syntax specific to the database system. Here is a simple example for SQL Server.

Example Stored Procedure

This stored procedure retrieves employee details by department ID.

Executing Stored Procedures from C#

In C#, stored procedures can be executed using ADO.NET by setting the command type to StoredProcedure.

Parameters can be passed to the stored procedure using SqlParameter objects.

Create a SqlConnection to the database
Create a SqlCommand with CommandType.StoredProcedure
Add parameters if needed
Execute the command and process results

Example: Calling a Stored Procedure in C#

The following example demonstrates how to call a stored procedure named GetEmployeesByDepartment.

Handling Results from Stored Procedures

Stored procedures can return data in various forms such as result sets, output parameters, or return values.

C# code can handle these results using SqlDataReader, output parameters, or return value parameters.

Use SqlDataReader to read result sets
Use SqlParameter with Direction.Output for output parameters
Use SqlParameter with Direction.ReturnValue for return values

Practical Example

This stored procedure selects employees filtered by the department ID parameter.

This C# code calls the stored procedure with a parameter and reads the returned employee data.

Examples

SQL Server Stored Procedure Example

CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentId INT
AS
BEGIN
    SELECT EmployeeId, Name, Position
    FROM Employees
    WHERE DepartmentId = @DepartmentId
END

This stored procedure selects employees filtered by the department ID parameter.

C# Example Calling Stored Procedure

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("GetEmployeesByDepartment", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@DepartmentId", 5);

    conn.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"ID: {reader["EmployeeId"]}, Name: {reader["Name"]}, Position: {reader["Position"]}");
        }
    }
}

This C# code calls the stored procedure with a parameter and reads the returned employee data.

Best Practices

Always use parameterized queries to prevent SQL injection.
Keep stored procedures focused on a single task for maintainability.
Handle exceptions and errors gracefully in your C# code.
Use output parameters and return values to communicate status.
Test stored procedures independently before integrating with C#.

Common Mistakes

Embedding raw SQL queries instead of using stored procedures.
Not using parameters, leading to SQL injection risks.
Ignoring database connection disposal causing resource leaks.
Assuming stored procedures always improve performance without testing.
Not handling null or unexpected results from stored procedures.

Hands-on Exercise

Create and Call a Stored Procedure

Write a stored procedure that inserts a new product into a Products table and call it from C# with parameters.

Expected output: The product is inserted into the database when the C# program runs.

Hint: Use SqlCommand with CommandType.StoredProcedure and add parameters for product details.

Retrieve Data Using Stored Procedure

Create a stored procedure that returns all orders for a given customer ID and call it from C# to display the orders.

Expected output: The program lists all orders for the specified customer.

Hint: Use SqlDataReader to read the results returned by the stored procedure.

Interview Questions

What is a stored procedure and why use it in C# database programming?

Interview

A stored procedure is a precompiled SQL code stored in the database. It is used in C# to improve performance, reduce network traffic, enhance security, and centralize SQL logic.

How do you execute a stored procedure using ADO.NET in C#?

Interview

You create a SqlCommand, set its CommandType to StoredProcedure, add any required parameters, open a SqlConnection, and call ExecuteReader, ExecuteNonQuery, or ExecuteScalar as needed.

What is Stored Procedures, and why is it useful?

Beginner

Stored procedures are precompiled SQL statements stored in the database that can be executed from C# code to improve performance, security, and maintainability.