ADO.NET Core Using Stored Procedure

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. As part of this article, we will discuss the following pointers in detail.

  1. What is a Stored Procedure in SQL?
  2. Advantages of using Stored Procedures
  3. Example to Understand ADO.NET Core using Stored Procedure
  4. How Do We Call a Stored Procedure with ADO.NET Core?
  5. How Do We Call a Stored Procedure Using ADO.NET Core without Parameters?
  6. How Do We Call a Stored Procedure Using ADO.NET Core with Input Parameter?
  7. How Do We Call a Stored Procedure with Both Input and Output Parameters?
  8. Calling Stored Procedures Asynchronously Using ADO.NET Core
  9. Advantages of Using Stored Procedure over T-SQL statement using ADO.NET Core

What is a Stored Procedure in SQL?

A Stored Procedure in SQL is a precompiled collection of SQL statements stored under a name and processed as a unit. They are used to encapsulate and manage database operations such as data validation, data insertion, data updating, or complex business logic that can be executed with a single call from a client application. Stored Procedures are stored within the database itself and can be invoked by applications, triggers, or other procedures.

Advantages of using Stored Procedures:

The main benefits of using Stored Procedures are as follows:

  • Performance Improvement: Since Stored Procedures are precompiled, the database engine can execute them more efficiently compared to running multiple SQL statements individually. This is particularly beneficial for complex operations or queries that are executed frequently.
  • Reduced Network Traffic: By bundling several SQL commands into a single Stored Procedure, network traffic between the client application and the database server can be significantly reduced. This is because only the call to the Stored Procedure, rather than each individual SQL statement, needs to be transmitted over the network.
  • Security Enhancement: Stored Procedures can be used to implement business rules and data validation logic on the database server side, reducing the exposure of underlying database structures to the client side. Additionally, permissions can be set on Stored Procedures, allowing fine-grained control over who can execute specific database operations.
  • Maintainability: Encapsulating database operations within Stored Procedures allows for centralized management of the logic that interacts with the database. This makes it easier to update and maintain the code since changes can be made in one place without requiring modifications to the client applications.
  • Reusability: Stored Procedures can be called from multiple applications or within other Stored Procedures, promoting code reuse.

Example to Understand ADO.NET Core using Stored Procedure:

Let us look at some examples to understand how to use the Stored Procedures with ADO.NET Core. We are going to use the following Product table in this demo to understand the concept of ADO.NET Core using Stored Procedure.

Example to Understand ADO.NET Core using Stored Procedure

Please use the below SQL Script to Create and Populate the database ProductDB and table Product with the required sample data.

CREATE DATABASE ProductDB;
GO

USE ProductDB;
GO

CREATE TABLE Product (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    Quantity INT DEFAULT 0
);
GO

INSERT INTO Product (ProductName, Price, Quantity) VALUES
('Laptop', 1200.00, 10),
('Smartphone', 800.00, 15),
('Tablet', 450.00, 20),
('Smartwatch', 199.99, 30),
('Headphones', 150.00, 50),
('Keyboard', 70.00, 60),
('Mouse', 40.00, 70),
('Webcam', 100.00, 25),
('Monitor', 300.00, 20),
('Printer', 250.00, 15);
GO

Stored Procedure without Parameters:

Next, create a stored procedure that retrieves all records from the Product table. This procedure will not take any parameters. Please use the below SQL Script to create the stored procedure in the SQL server ProductDB database.

CREATE PROCEDURE GetAllProducts
AS
BEGIN
    SELECT * FROM Product;
END
Explanation:
  • CREATE PROCEDURE GetAllProducts: Creates a new stored procedure named GetAllProducts.
  • SELECT: Retrieves all rows and all columns from the Product table.

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 for the stored procedure.
  • Execute the SqlCommand: Execute the command using ExecuteReader, ExecuteScalar, or ExecuteNonQuery, depending on the nature of your stored procedure and what it returns.
  • Process the Results: If your stored procedure returns results (e.g., a SELECT statement), process the results using a SqlDataReader.

How Do We Call a Stored Procedure using ADO.NET Core without Parameters?

Let us see an example of how to call a stored procedure using ADO.NET Core without parameters. Please modify the Program class as follows. In the example below, I show how to call a stored procedure without parameters using ADO.NET Core.

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Connection String
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ProductDB;Trusted_Connection=True;TrustServerCertificate=True;";

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    try
                    {
                        connection.Open();
                        SqlCommand command = new SqlCommand("GetAllProducts", connection);
                        command.CommandType = CommandType.StoredProcedure;

                        SqlDataReader reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            Console.WriteLine($"ID: {reader["ProductID"]}, Name: {reader["ProductName"]}, Price: {reader["Price"]}, Quantity: {reader["Quantity"]}");
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"An error occurred: {ex.Message}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }
    }
}
Explanation:
  • SqlConnection: Establishes a connection to the SQL Server database using the provided connection string.
  • SqlCommand: Represents a SQL command or stored procedure to execute against the database.
  • CommandType.StoredProcedure: Specifies that the command text is the name of a stored procedure.
  • ExecuteReader: Executes the command and returns data in a SqlDataReader.
  • SqlDataReader: Provides a way to read a forward-only stream of rows from a SQL Server database.

Now, run the application, and you should get the following output:

How Do We Call a Stored Procedure using ADO.NET Core without Parameters?

Stored Procedure with Input Parameters:

Now, we will see how to call a stored procedure with an input parameter using ADO.NET Core. First, we create the stored procedure inside the ProductDB database. The following stored procedure takes ProductId as an input parameter and retrieves the corresponding product’s details.

So, please execute the following SQL Script to create the stored procedure, which will return the product details using ProductId. Here, ProductId is the input parameter, and we need to pass that parameter value while calling this stored procedure from our .NET Application using ADO.NET Core.

CREATE PROCEDURE GetProductById
    @ProductId INT
AS
BEGIN
    SELECT * FROM Product WHERE ProductID = @ProductId;
END
Explanation:
  • CREATE PROCEDURE GetProductById: Creates a new stored procedure named GetProductById.
  • @ProductId INT: Defines an input parameter named @ProductId of type INT.
  • SELECT: Retrieves rows from the Product table where the ProductID column matches the specified @ProductId.
How Do We Call a Stored Procedure using ADO.NET Core with a Parameter?

Let us see an example of how to call a stored procedure using ADO.NET Core with a parameter. Please modify the Program class as follows. In the below example, I am showing how to call the GetProductById stored procedure from a .NET Core application using ADO.NET Core.

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Connection String
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ProductDB;Trusted_Connection=True;TrustServerCertificate=True;";

                int productId = 1; // Example ProductId to fetch

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("GetProductById", connection)
                    {
                        CommandType = CommandType.StoredProcedure
                    };
                    command.Parameters.Add(new SqlParameter("@ProductId", productId));

                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"ID: {reader["ProductID"]}, Name: {reader["ProductName"]}, Price: {reader["Price"]}, Quantity: {reader["Quantity"]}");
                        }
                    }
                    else
                    {
                        Console.WriteLine("No rows found.");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }
    }
}
Explanation:
  • SqlConnection: Opens a connection to the database using the specified connection string.
  • SqlCommand: Represents the GetProductById stored procedure to be executed against the database.
  • CommandType.StoredProcedure: Indicates that the command text is the name of a stored procedure, not SQL commands.
  • Parameters.Add: Adds a parameter to the SqlCommand object. This parameter corresponds to the @ProductId parameter expected by the stored procedure.
  • ExecuteReader: Executes the SqlCommand and returns data via a SqlDataReader.
  • SqlDataReader: Reads the data returned by the query. The HasRows property checks if the query returned any rows, and the Read method iterates through the result set.

Now, run the application, and you should get the following output:

How Do We Call a Stored Procedure using ADO.NET Core with a Parameter?

Stored Procedure with Both Input and Output Parameters:

In our previous example, we understand how to call a stored procedure with an input parameter. Now, let us see how to call a Stored Procedure with both input and output parameters. First, use the below SQL Script to create the SQL Server Stored Procedure with both input and output parameters. The following stored procedure adds a new product to the Product table and returns the newly created ProductId using an output parameter.

CREATE PROCEDURE CreateProduct
    @ProductName VARCHAR(255),
    @Price DECIMAL(10, 2),
    @Quantity INT,
    @NewProductId INT OUTPUT
AS
BEGIN
    INSERT INTO Product (ProductName, Price, Quantity)
    VALUES (@ProductName, @Price, @Quantity);
    
    SET @NewProductId = SCOPE_IDENTITY();
END
Explanation:
  • CREATE PROCEDURE CreateProduct: Defines a new stored procedure named CreateProduct.
  • @ProductName, @Price, @Quantity: Input parameters for the new product’s details.
  • @NewProductId INT OUTPUT: Defines an output parameter to return the ProductId of the newly created product.
  • INSERT INTO Product: Insert the new product details into the Product table.
  • SCOPE_IDENTITY(): Returns the last identity value inserted into an identity column in the same scope. This is used to get the ProductId of the newly inserted product.

How Do We Call a Stored Procedure with Both Input and Output Parameters?

Let us see an example of how to call a stored procedure using ADO.NET Core with both input and output parameters. Please modify the Program class as follows. In the example below, I show you how to call the CreateProduct stored procedure from a .NET Core application using ADO.NET Core with both input and output parameters.

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Connection String
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ProductDB;Trusted_Connection=True;TrustServerCertificate=True;";

                string productName = "New Product";
                decimal price = 99.99M;
                int quantity = 10;

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("CreateProduct", connection)
                    {
                        CommandType = CommandType.StoredProcedure
                    };

                    command.Parameters.Add(new SqlParameter("@ProductName", productName));
                    command.Parameters.Add(new SqlParameter("@Price", price));
                    command.Parameters.Add(new SqlParameter("@Quantity", quantity));

                    SqlParameter newProductIdParam = new SqlParameter("@NewProductId", SqlDbType.Int)
                    {
                        Direction = ParameterDirection.Output
                    };
                    command.Parameters.Add(newProductIdParam);

                    command.ExecuteNonQuery();

                    int newProductId = (int)newProductIdParam.Value;
                    //int newProductId = (int)command.Parameters["@NewProductID"].Value;
                    Console.WriteLine($"New Product ID: {newProductId}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }
    }
}
Explanation:
  • SqlConnection: Establishes a connection to the SQL Server database.
  • SqlCommand: Represents the CreateProduct stored procedure to be executed against the database.
  • CommandType.StoredProcedure: This indicates that the command is a stored procedure.
  • Parameters.Add: Adds input parameters for ProductName, Price, and Quantity.
  • SqlParameter for @NewProductId: Configures an output parameter to capture the returned ProductId. The Direction property is set to ParameterDirection.Output.
  • ExecuteNonQuery: Executes the command. Since this stored procedure performs an insert operation and returns a value through an output parameter, ExecuteNonQuery is used instead of ExecuteReader.
  • Output Parameter Value: After executing the command, the value of the output parameter is accessed and displayed.

Calling Stored Procedures Asynchronously using ADO.NET Core

Calling stored procedures asynchronously in ADO.NET Core enhances the responsiveness of applications, especially in scenarios involving I/O-bound operations such as database calls. In the below example, I am showing how to asynchronously call the three stored procedures GetAllProducts, GetProductById, and CreateProduct) using the asynchronous methods provided by the SqlCommand class.

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ProductDB;Trusted_Connection=True;TrustServerCertificate=True;";

                //Asynchronous Call to GetAllProducts
                Console.WriteLine("Asynchronous Call to GetAllProducts");
                await GetAllProductsAsync(connectionString);

                //Asynchronous Call to GetProductById
                Console.WriteLine("\nAsynchronous Call to GetProductById");
                int ProductId = 1;
                await GetProductByIdAsync(connectionString, ProductId);

                //Asynchronous Call to CreateProduct
                Console.WriteLine("\nAsynchronous Call to CreateProduct");
                string productName = "New Product";
                decimal price = 99.99M;
                int quantity = 10;
                await CreateProductAsync(connectionString, productName, price, quantity);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }

        static async Task GetAllProductsAsync(string connectionString)
        {
            //To call the GetAllProducts stored procedure asynchronously,
            //you can use the ExecuteReaderAsync method.
            using (var connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand("GetAllProducts", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            Console.WriteLine($"ID: {reader["ProductID"]}, Name: {reader["ProductName"]}, Price: {reader["Price"]}, Quantity: {reader["Quantity"]}");
                        }
                    }
                }
            }
        }

        static async Task GetProductByIdAsync(string connectionString, int productId)
        {
            //For the GetProductById stored procedure,
            //you use ExecuteReaderAsync to perform the operation asynchronously.
            using (var connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand("GetProductById", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@ProductId", productId);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            Console.WriteLine($"ID: {reader["ProductID"]}, Name: {reader["ProductName"]}, Price: {reader["Price"]}, Quantity: {reader["Quantity"]}");
                        }
                        else
                        {
                            Console.WriteLine("Product not found.");
                        }
                    }
                }
            }
        }

        static async Task CreateProductAsync(string connectionString, string productName, decimal price, int quantity)
        {
            //To asynchronously call the CreateProduct stored procedure, use the ExecuteNonQueryAsync method,
            //which is suitable for operations that do not return rows.
            using (var connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand("CreateProduct", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@ProductName", productName);
                    command.Parameters.AddWithValue("@Price", price);
                    command.Parameters.AddWithValue("@Quantity", quantity);

                    var newProductIdParam = new SqlParameter("@NewProductId", SqlDbType.Int)
                    {
                        Direction = ParameterDirection.Output
                    };
                    command.Parameters.Add(newProductIdParam);

                    await command.ExecuteNonQueryAsync();

                    int newProductId = (int)newProductIdParam.Value;
                    Console.WriteLine($"New Product ID: {newProductId}");
                }
            }
        }
    }
}
Explanation
  • OpenAsync: Asynchronously opens a database connection.
  • ExecuteReaderAsync: Asynchronously executes commands that return rows.
  • ReadAsync: Asynchronously reads the next row from the result set.
  • ExecuteNonQueryAsync: Asynchronously executes commands that do not return rows, suitable for insert, update, and delete operations.
  • AddWithValue: Adds parameters with values directly, simplifying the addition of input parameters.

When you run the above code, you will get the following output:

Calling Stored Procedures Asynchronously using ADO.NET Core

General Pattern for Asynchronous ADO.NET Operations
  • Async/Await: The async keyword is used to define asynchronous methods, and await is used to call asynchronous operations, ensuring non-blocking execution.
  • Using Statement: Ensures proper disposal of SqlConnection and SqlCommand objects, even in asynchronous operations.
  • CommandType.StoredProcedure: Indicates that the command text is the name of a stored procedure.
  • Parameters: Parameters are added to the SqlCommand object, with input parameters being populated directly and output parameters requiring configuration for direction and type.
Advantages of Using Stored Procedure over T-SQL statement using ADO.NET Core

Using Stored Procedures over T-SQL statements directly in ADO.NET Core applications offers several advantages, primarily in terms of performance, security, maintainability, and application design. Below, these advantages are outlined with a focus on how they contribute to more robust and efficient database interactions:

Performance Optimization
  • Precompiled Execution: Stored procedures are compiled and stored in the database, which means that their execution plan is saved by the SQL Server. This can lead to performance improvements because the SQL Server does not need to compile the SQL statements every time they are executed, unlike ad-hoc T-SQL statements that are compiled at runtime.
  • Reduced Network Traffic: When using stored procedures, only the call to the procedure is sent over the network rather than the entire T-SQL command. This can significantly reduce the amount of data sent over the network, especially for complex queries.
Security Enhancements
  • Parameterized Execution: Stored procedures use parameters that can help prevent SQL injection attacks. By using parameters, the SQL execution context is clearly separated from the data, unlike dynamic SQL queries that might concatenate strings to create a query, which is a common vulnerability point.
  • Permission Management: Permissions can be assigned directly to the stored procedure instead of the underlying tables. This means an application can limit database access, allowing users to perform only specific operations through stored procedures and not directly on the data, enhancing data security.
Maintainability and Centralization
  • Centralized Logic: Business logic can be centralized within the database as stored procedures. This means changes to the logic can be made in one place without the need to redeploy applications that use these procedures, facilitating easier maintenance and updates.
  • Code Reusability: Stored procedures can be reused across multiple applications. This avoids duplicating SQL code, which can lead to discrepancies and maintenance challenges.
Reduced Client-Side Complexity
  • Simplified Application Code: By moving complex business logic into stored procedures, the application code can be simplified. This can make the application easier to understand and maintain, as the complex logic is abstracted away from the application layer.
  • Consistency in Data Handling: Stored procedures ensure that data manipulation and business logic are consistently applied, regardless of the application accessing the data. This uniformity is crucial for data integrity and reliability.
Transactional Management
  • Better Transaction Control: Stored procedures provide a convenient way to encapsulate complex transactions. This includes beginning a transaction, performing multiple operations, and then committing or rolling back based on success or failure, all within a single procedure. This can lead to more robust and reliable transaction management.

In the next article, I will discuss Performing CRUD Operations using Stored Procedures in ADO.NET Core with Examples. In this article, I explain ADO.NET Core Using Stored Procedure with Examples. I would like to have your feedback. Please post your feedback, questions, or comments about this ADO.NET Core Using Stored Procedure article.

Leave a Reply

Your email address will not be published. Required fields are marked *