Back to: ADO.NET Core Tutorial For Beginners and Professionals
ADO.NET Core Using Stored Procedure with Examples
In this article, I will discuss ADO.NET Core Using Stored Procedure with Examples. Please read our previous article discussing the ADO.NET Core SqlDataAdapter Class.
What is a Stored Procedure in SQL Server?
A Stored Procedure in SQL Server is a precompiled collection of T-SQL statements stored under a specific name and processed as a single unit. It can include logic like conditional statements, loops, and error handling, allowing us to perform complex operations efficiently. Once a stored procedure is created, we can execute it by name. Stored Procedures can accept input parameters and return results and/or output parameters. This approach reduces the need to send raw queries from the client side, improves performance, and enhances security by abstracting direct table access.
How Does a Stored Procedure Execute in SQL Server?
Understanding the execution flow of a stored procedure helps optimize its performance and helps you know how the SQL Server handles its execution. To understand how a stored procedure is executed in the SQL Server, please have a look at the following diagram:
Let us understand what happens when we execute for the first and what happens for the subsequent executions.
First Execution:
Step1: Syntax Check
- When the stored procedure is executed for the first time, the SQL Server checks the syntax of the SQL statements inside the procedure. If there are any syntax errors, they are reported immediately.
Step2: Query Plan Compilation
- SQL Server parses the SQL statements inside the stored procedure and creates a Query Plan.
- During this step, the SQL Server analyzes the queries, evaluates different execution strategies, and selects the most efficient one based on available indexes and statistics.
- A Query Execution Plan is generated, containing detailed instructions on how the queries will access the data (e.g., full table scan or index scan).
Step3: Caching the Query Plan
- After creating the Query Execution Plan, it is stored in the cache to avoid recompiling the SQL statements and regenerating the Query Plan every time the procedure is executed, saving time during subsequent executions.
Step4: Query Execution
- The stored procedure is executed using the cached Query Execution Plan, and the required data is retrieved and returned to the user.
Subsequent Executions:
- The next time the stored procedure is executed, the SQL Server first checks if an existing Query Execution Plan is already in the cache.
- If it finds the Query Execution Plan in the Cache, SQL Server skips the syntax checking and Query Generation plan steps.
- The stored procedure proceeds directly to execution using the cached query plan, which speeds up execution.
Why Use Stored Procedures with ADO.NET Core?
The following are the advantages of using a Stored Procedure with ADO.NET Core:
- Reusability: Once written, the stored procedure can be executed repeatedly by various applications.
- Performance: The execution plan is generated and cached, improving performance, especially for complex logic.
- Security: This allows tighter control over data access since only the procedure is exposed. It limits direct table access; permissions are granted on the procedure instead.
- Maintenance: Simplify maintenance since changes can be made to the stored procedure without altering client-side applications (only the procedure logic changes, not the code in the app).
- Centralized Business Logic: Business logic is centralized in the database; multiple applications can reuse the same logic.
- Separation of Concerns: This separates data access logic from application logic, resulting in cleaner, more maintainable code.
- Reduced Network Traffic: Stored Procedures can significantly reduce the network traffic between the client application and the database server. This is because only the call to the Stored Procedure, rather than each SQL statement, needs to be transmitted over the network.
SQL Script for Creating a Database with Multiple Tables and Stored Procedures
Let us proceed and create the following in our SQL Server Database.
- Creates a database called StoreDB.
- Creates two tables: Customers and Orders.
- Insert dummy data into these tables.
- Create a stored procedure without parameters.
- Create a stored procedure with input parameters.
- Create a stored procedure with both input and output parameters.
Please execute the following SQL script to create a sample database StoreDB with two tables (Customers and Orders), insert dummy data, and define three stored procedures as per our requirements:
-- Create a new database named StoreDB
CREATE DATABASE StoreDB;
GO
-- Switch to the new StoreDB database
USE StoreDB;
GO
-- Create the Customers table
CREATE TABLE Customers (
CustomerID INT IDENTITY(1,1) PRIMARY KEY, -- Auto-increment primary key
FirstName NVARCHAR(50), -- Customer first name
LastName NVARCHAR(50), -- Customer last name
Email NVARCHAR(100), -- Customer email address
CreatedDate DATETIME NOT NULL DEFAULT GETDATE() -- Customer registration date
);
GO
-- Create the Orders table with a foreign key reference to Customers
CREATE TABLE Orders (
OrderID INT IDENTITY(1,1) PRIMARY KEY, -- Auto-increment primary key
CustomerID INT, -- Reference to CustomerID from Customers table
OrderDate DATETIME, -- Date of the order
Amount DECIMAL(10,2), -- Order amount
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
GO
-- Insert dummy data into the Customers table
INSERT INTO Customers (FirstName, LastName, Email)
VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com'),
('Alice', 'Brown', 'alice.brown@example.com');
GO
-- Insert dummy data into the Orders table
INSERT INTO Orders (CustomerID, OrderDate, Amount)
VALUES
(1, GETDATE(), 100.50),
(1, GETDATE(), 50.00),
(2, GETDATE(), 75.75),
(3, GETDATE(), 200.00);
GO
-- =============================================
-- Create Stored Procedure without Parameters
-- This procedure returns all customers from the Customers table.
-- =============================================
CREATE PROCEDURE sp_GetAllCustomers
AS
BEGIN
SET NOCOUNT ON; -- Prevent extra result sets from interfering with SELECT statements.
SELECT * FROM Customers;
END;
GO
-- =============================================
-- Create Stored Procedure with Input Parameter
-- This procedure returns all orders for a given CustomerID.
-- =============================================
CREATE PROCEDURE sp_GetOrdersByCustomer
@CustomerID INT -- Input parameter to filter orders by customer
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Orders WHERE CustomerID = @CustomerID;
END;
GO
-- =============================================
-- Create Stored Procedure with Input and Output Parameters
-- This procedure returns the total order count for a given CustomerID.
-- =============================================
CREATE PROCEDURE sp_GetCustomerOrderCount
@CustomerID INT, -- Input parameter for the customer
@OrderCount INT OUTPUT -- Output parameter that will hold the count of orders
AS
BEGIN
SET NOCOUNT ON;
SELECT @OrderCount = COUNT(*) FROM Orders WHERE CustomerID = @CustomerID;
END;
GO
Once you have run the above script, you will have:
- A database, StoreDB.
- Two tables: Customers and Orders with some dummy data.
- Three stored procedures to demonstrate different parameter usage.
What is SET NOCOUNT ON?
In SQL Server, SET NOCOUNT ON disables the automatic reporting of the number of rows affected by Transact-SQL statements. Reducing network traffic can improve performance, especially in stored procedures with many statements or those using loops.
How Do We Call a Stored Procedure with ADO.NET Core?
We need to follow the steps below to call a stored procedure with ADO.NET Core.
- Establish a Database Connection: Create a connection to your database using the SqlConnection class.
- Create a SqlCommand Object: Use the SqlCommand class to create a command object, specifying the name of the stored procedure and the connection.
- Specify the Command Type: Set the CommandType of your command object to CommandType.StoredProcedure to indicate that you’re executing a stored procedure.
- Add Parameters to the SqlCommand: Use the Parameters property of the SqlCommand object to add any required parameters (both input and output) for the stored procedure.
- Execute the SqlCommand: Depending on the nature of the stored procedure and its return, execute the command using ExecuteReader, ExecuteScalar, or ExecuteNonQuery.
- Process the Results: If your stored procedure returns results (e.g., a SELECT statement), process the results using a SqlDataReader.
Calling Stored Procedures with ADO.NET Core
The following example demonstrates how to call the above stored procedures using ADO.NET Core in a .NET Core Console application. The following example code is self-explained, so please read the comment lines for a better understanding.
using System.Data;
using Microsoft.Data.SqlClient;
namespace StoredProceduresDemo
{
class Program
{
static void Main(string[] args)
{
// Connection String to the StoreDB database
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StoreDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
// Create and open a SQL connection within a using block to ensure disposal.
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
// 1. Call sp_GetAllCustomers (Stored Procedure without parameters)
using (SqlCommand cmd = new SqlCommand("sp_GetAllCustomers", conn))
{
// Specify that the command is a stored procedure.
cmd.CommandType = CommandType.StoredProcedure;
// Execute the command and read the results.
using (SqlDataReader reader = cmd.ExecuteReader())
{
Console.WriteLine("All Customers:");
while (reader.Read())
{
// Display customer details.
Console.WriteLine($"CustomerID: {reader["CustomerID"]}, Name: {reader["FirstName"]} {reader["LastName"]}, Created On: {(Convert.ToDateTime(reader["CreatedDate"])).ToShortTimeString()}");
}
}
}
// 2. Call sp_GetOrdersByCustomer (Stored Procedure with an input parameter)
using (SqlCommand cmd = new SqlCommand("sp_GetOrdersByCustomer", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Add the input parameter value (example: CustomerID = 1).
cmd.Parameters.AddWithValue("@CustomerID", 1);
using (SqlDataReader reader = cmd.ExecuteReader())
{
Console.WriteLine("\nOrders for CustomerID 1:");
while (reader.Read())
{
// Display order details.
Console.WriteLine($"OrderID: {reader["OrderID"]}, OrderDate: {reader["OrderDate"]}, Amount: {reader["Amount"]}");
}
}
}
// 3. Call sp_GetCustomerOrderCount (Stored Procedure with input and output parameters)
using (SqlCommand cmd = new SqlCommand("sp_GetCustomerOrderCount", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Add input parameter.
cmd.Parameters.AddWithValue("@CustomerID", 1);
// Create and add the output parameter.
SqlParameter outputParam = new SqlParameter("@OrderCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(outputParam);
// Execute the command.
cmd.ExecuteNonQuery();
// Retrieve the value from the output parameter.
Console.WriteLine($"\nCustomer 1 has {cmd.Parameters["@OrderCount"].Value} orders.");
}
}
}
catch (Exception ex)
{
// Log any exceptions that occur during database operations.
Console.WriteLine("An error occurred: " + ex.Message);
}
Console.ReadKey();
}
}
}
Code Explanations:
- Connection Setup: A SqlConnection is established using a connection string. The using block ensures proper closing/disposal.
- Calling sp_GetAllCustomers: A SQLCommand is created for the stored procedure (with CommandType.StoredProcedure), which is executed via a SqlDataReader to loop through customer records.
- Calling sp_GetOrdersByCustomer: This is Similar to the previous call but adds an input parameter (@CustomerID) to filter orders.
- Calling sp_GetCustomerOrderCount: Adds both an input parameter and an output parameter to fetch the order count, then prints the result.
Output:
Calling Stored Procedures Asynchronously with ADO.NET Core
The following is an asynchronous version of calling stored procedures. Asynchronous programming improves responsiveness, especially in UI apps or services handling multiple concurrent operations. The following example code is self-explained, so please read the comment lines for a better understanding.
using System.Data;
using Microsoft.Data.SqlClient;
namespace StoredProceduresDemo
{
class Program
{
static async Task Main(string[] args)
{
// Connection String to the StoreDB database
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StoreDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
// Create and open a SQL connection within a using block to ensure disposal.
using (SqlConnection conn = new SqlConnection(connectionString))
{
// Open connection asynchronously
await conn.OpenAsync();
// Asynchronously calls SP_GetAllCustomers and displays results.
using (SqlCommand cmd = new SqlCommand("sp_GetAllCustomers", conn))
{
// Specify that the command is a stored procedure.
cmd.CommandType = CommandType.StoredProcedure;
// Execute the stored procedure asynchronously
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
Console.WriteLine("All Customers:");
// Asynchronously read each row
while (await reader.ReadAsync())
{
// Display customer details.
Console.WriteLine($"CustomerID: {reader["CustomerID"]}, Name: {reader["FirstName"]} {reader["LastName"]}, Created On: {(Convert.ToDateTime(reader["CreatedDate"])).ToShortTimeString()}");
}
}
}
// Asynchronously calls SP_GetCustomerById with an input parameter.
using (SqlCommand cmd = new SqlCommand("sp_GetOrdersByCustomer", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Add input parameter for the stored procedure
cmd.Parameters.AddWithValue("@CustomerID", 1);
// Execute reader asynchronously
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
Console.WriteLine("\nOrders for CustomerID 1:");
// Asynchronously read each row
while (await reader.ReadAsync())
{
// Display order details.
Console.WriteLine($"OrderID: {reader["OrderID"]}, OrderDate: {reader["OrderDate"]}, Amount: {reader["Amount"]}");
}
}
}
// Asynchronously calls SP_GetOrderCountByCustomer with input and output parameters.
using (SqlCommand cmd = new SqlCommand("sp_GetCustomerOrderCount", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Add input parameter
cmd.Parameters.AddWithValue("@CustomerID", 1);
// Setup output parameter for order count.
SqlParameter orderCountParam = new SqlParameter("@OrderCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(orderCountParam);
// Execute stored procedure asynchronously.
await cmd.ExecuteNonQueryAsync();
// Retrieve the output value from the output parameter
int orderCount = (int)orderCountParam.Value;
Console.WriteLine($"\nCustomer 1 has {orderCount} orders.");
}
}
}
catch (Exception ex)
{
// Log any exceptions that occur during database operations.
Console.WriteLine("An error occurred: " + ex.Message);
}
Console.ReadKey();
}
}
}
Key Points in the Asynchronous Version:
- Async/Await: All database operations that support asynchronous execution are awaited. This improves scalability and responsiveness.
- OpenAsync(), ExecuteReaderAsync(), and ExecuteNonQueryAsync(): These asynchronous methods prevent blocking the calling thread.
Output:
Why Use a Stored Procedure Over T-SQL Statements in ADO.NET Core?
The following are the Advantages of Stored Procedures over T-SQL Statements in ADO.NET Core:
- Precompilation: The SQL Server engine precompiles and optimizes stored procedures, often leading to better performance than dynamic T‑SQL queries constructed at runtime.
- Security: They reduce the risk of SQL injection since parameters are strongly typed and separated from the T‑SQL code. For example, using a stored procedure to filter orders by customer ID avoids string concatenation vulnerabilities that can occur if T‑SQL commands are built manually.
- Maintainability: Changes to business logic can be implemented at the database level without recompiling or redeploying the application.
- Reusability and Abstraction: Common queries can be encapsulated in stored procedures and reused across different parts of an application or even multiple applications.
For a better understanding, please have a look at the following image.
Using a Stored Procedure:
The database maintains the SQL logic, and the application calls the procedure with parameters.
Using inline T‑SQL in the application:
The SQL statement must be constructed as a string, and care must be taken to sanitize inputs. Example inline approach (prone to SQL injection if not handled properly):
So, using Stored Procedures helps enforce a clear separation between data access and application logic and offers a safer, more optimized, and maintainable approach to interacting with your database.
In the next article, I will discuss how to perform CRUD Operations using Stored Procedures in ADO.NET Core with Examples. In this article, I explain ADO.NET Core Using Stored Procedures with Examples. I would like your feedback. Please post your feedback, questions, or comments about this article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.