Using Stored Procedures in .NET Applications

June 27, 2025

Using Stored Procedures in .NET Applications

Stored procedures are precompiled SQL queries stored in a database that can be executed by applications. They help improve performance, maintainability, and security of database operations. In .NET applications, stored procedures can be easily called and managed using ADO.NET or higher-level data access technologies like Entity Framework.

Why Use Stored Procedures?

Performance: Stored procedures are precompiled, reducing query execution time.

Security: Encapsulate SQL logic on the server side, preventing SQL injection when used properly.

Maintainability: Centralize SQL logic in the database, making updates easier without changing application code.

Reusability: Procedures can be reused by multiple applications or modules.

How to Use Stored Procedures in .NET Applications

1. Creating a Stored Procedure

Example SQL stored procedure to get a customer by ID:

sql

Copy

Edit

CREATE PROCEDURE GetCustomerById

@CustomerId INT

BEGIN

SELECT * FROM Customers WHERE CustomerId = @CustomerId;

END

2. Calling Stored Procedures Using ADO.NET

Here’s a basic example in C#:

csharp

Copy

Edit

using System;

using System.Data;

using System.Data.SqlClient;

class Program

{

static void Main()

{

string connectionString = "your_connection_string_here";

using (SqlConnection conn = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand("GetCustomerById", conn))

{

cmd.CommandType = CommandType.StoredProcedure;

// Add parameter value

cmd.Parameters.Add(new SqlParameter("@CustomerId", 1));

conn.Open();

using (SqlDataReader reader = cmd.ExecuteReader())

{

while (reader.Read())

{

Console.WriteLine("Customer Name: " + reader["Name"]);

}

Set CommandType to StoredProcedure.

Add parameters using SqlParameter.

Execute with ExecuteReader, ExecuteNonQuery, or ExecuteScalar depending on the operation.

3. Calling Stored Procedures Using Entity Framework

If you use Entity Framework, you can call stored procedures like this:

csharp

Copy

Edit

var customerIdParam = new SqlParameter("@CustomerId", 1);

var customer = context.Customers

.FromSqlRaw("EXEC GetCustomerById @CustomerId", customerIdParam)

.ToList();

Or with EF Core 7+:

csharp

Copy

Edit

var result = context.Customers

.FromSqlInterpolated($"EXEC GetCustomerById {1}")

.ToList();

Best Practices

Use parameters to prevent SQL injection.

Handle exceptions and close connections properly.

Keep stored procedures focused on a single responsibility.

Use appropriate return types and output parameters when needed.

Consider using ORM support for stored procedures if available.

Summary

Using stored procedures in .NET applications enhances security, performance, and maintainability of database interactions. Whether you use ADO.NET for direct control or Entity Framework for easier data access, integrating stored procedures allows you to leverage powerful database-side logic effectively.

Learn Full Stack Dot NET Training in Hyderabad

Database Migrations in Entity Framework Core

Entity Framework Core: The ORM for Full Stack .NET Developers

Working with NoSQL Databases in .NET (MongoDB, Redis)

Visit Our Quality Thought Training in Hyderabad

Get Directions