Back to: ADO.NET Core Tutorial For Beginners and Professionals

CRUD Operations using ADO.NET Core SqlDataAdapter and Stored Procedures

In this article, I will discuss how to perform CRUD Operations using ADO.NET Core SqlDataAdapter and Stored Procedures with examples. Please read our previous article discussing how to perform CRUD Operations using ADO.NET Core with Stored Procedures.

What is a Stored Procedure?

A Stored Procedure is a precompiled collection of one or more SQL statements stored on the database server. It allows us to encapsulate logic such as Insert, Update, Delete, and Select operations and reuse them multiple times. Instead of sending raw SQL queries from the application to the database, you call a stored procedure by name and pass any required parameters. This approach offers several advantages:

• Increases Performance: Since stored procedures are precompiled, the SQL Server can execute them more quickly than sending individual SQL statements.
• Security: You can grant users access to stored procedures without giving them direct access to the underlying tables.
• Easier to Maintain: Business logic is maintained on the server side. A change in logic requires an update only in the stored procedure rather than in multiple application layers consuming the stored procedure.
• Reusability: They can be called from multiple applications or parts of the same application consistently.

What is ADO.NET Core SqlDataAdapter?

The SqlDataAdapter in ADO.NET Core acts as a bridge between a DataSet or DataTable (an in-memory representation of data) and the SQL Server database. It allows us to fetch data from the database into an in-memory DataTable/DataSet, make changes to the data in memory, and then push those changes back to the database. The Key features of ADO.NET Core SqlDataAdapter include:

• Fill a DataTable/DataSet: Retrieve data from the database using a SELECT Query or stored procedure.
• Update Changes: Use the same adapter to push any modifications made to the DataTable back to the database.
• Command Mapping: You can map specific commands (SelectCommand, InsertCommand, UpdateCommand, DeleteCommand) to stored procedures for CRUD operations.
• Disconnected Data Access: Once the data is fetched into a DataSet, the connection to the database can be closed and later reconnected to save changes, improving scalability and performance.

How Do We Implement CRUD Operations using ADO.NET Core SqlDataAdapter with Stored Procedures?

• Define Database Table: In SQL Server, create the necessary tables to store the data.
• Create Stored Procedures: Write SQL scripts to handle Create, Read, Update, and Delete operations.
• Use SqlDataAdapter in .NET Core:
  1. Establish a connection to your database.
  2. Create a SqlDataAdapter and set the appropriate stored procedure commands (e.g., SelectCommand, InsertCommand, UpdateCommand, DeleteCommand).
  3. Fill a DataTable or DataSet for the Read operations, or execute the commands directly for Insert, Update, and Delete operations.

Note: If this is not clear at the moment, don’t worry; we will see one example to understand this concept using a simple .NET Core Console application.

Database Setup in SQL Server

Please execute the following SQL Script to create a sample database named EmployeeDB with a single table (Employees), insert dummy data, and create stored procedures to perform CRUD operations. Open SQL Server Management Studio (SSMS) or use your preferred SQL environment and run the following commands:

-- Create a new database EmployeesDB
CREATE DATABASE EmployeesDB;
GO

-- Switch to the new EmployeesDB database
USE EmployeesDB;
GO

-- Create Employees table
CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Designation NVARCHAR(100)
);
GO

-- Insert some dummy data into Employees table
INSERT INTO Employees (FirstName, LastName, Email, Designation)
VALUES	('John', 'Doe', 'john.doe@example.com', 'Manager'),
  ('Jane', 'Smith', 'jane.smith@example.com', 'Developer'),
  ('Bob', 'Smith', 'bob.smith@example.com', 'Tester');
GO

-- Stored Procedure for READ operation: Get all employees
CREATE PROCEDURE sp_GetEmployees
AS
BEGIN
    SET NOCOUNT ON;
    SELECT EmployeeID, FirstName, LastName, Email, Designation 
    FROM Employees;
END;
GO

-- Stored Procedure for CREATE operation: Insert new employee
CREATE PROCEDURE sp_InsertEmployee
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(100),
    @Designation NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Employees (FirstName, LastName, Email, Designation) 
    VALUES (@FirstName, @LastName, @Email, @Designation);

    -- Optionally, return the new identity value if needed
    SELECT SCOPE_IDENTITY() AS NewEmployeeID;
END;
GO

-- Stored Procedure for UPDATE operation: Update an existing employee
CREATE PROCEDURE sp_UpdateEmployee
    @EmployeeID INT,
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(100),
    @Designation NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Employees
    SET FirstName = @FirstName,
 LastName = @LastName,
 Email = @Email,
 Designation = @Designation
    WHERE EmployeeID = @EmployeeID;
END;
GO

-- Stored Procedure for DELETE operation: Delete an employee
CREATE PROCEDURE sp_DeleteEmployee
    @EmployeeID INT
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM Employees 
    WHERE EmployeeID = @EmployeeID;
END;
GO

.NET Core Console Application

The following .NET Core Console application demonstrates consuming the above stored procedures using a SqlDataAdapter. In the below code, the adapter is configured with separate commands for SELECT, INSERT, UPDATE, and DELETE, each tied to a stored procedure. This allows us to work with an in-memory DataSet (or DataTable) and then synchronize the changes back to the database. The code is self-explained, so please read the comment lines for a better understanding.

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

namespace AdoNetCrudExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connection String to the EmployeesDB database
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeesDB;Trusted_Connection=True;TrustServerCertificate=True;";

            try
            {
                // 1. Retrieve data using stored procedure: sp_GetEmployees
                DataTable employeeTable = new DataTable("Employees");

                // Using block ensures that the connection is closed/disposed properly.
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // Open the connection to SQL Server.
                    connection.Open();

                    // ----------- Read (SELECT) Example -----------
                    // Create a SqlDataAdapter for the SELECT stored procedure.
                    SqlDataAdapter adapter = new SqlDataAdapter("sp_GetEmployees", connection);
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure; // Specify that it's a stored procedure.

                    // Fill the DataTable with the result from sp_GetEmployees.
                    adapter.Fill(employeeTable);

                    // Display the current employees.
                    Console.WriteLine("Current Employees:");
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        Console.WriteLine($"ID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}, Email: {row["Email"]}, Designation: {row["Designation"]}");
                    }

                    // ----------- Insertion (CREATE) Example -----------
                    Console.WriteLine("\nInserting a new employee...");

                    // Create a new DataRow object.
                    DataRow newRow = employeeTable.NewRow();
                    newRow["FirstName"] = "David";                      // Set the First Name column.
                    newRow["LastName"] = "Thompson";                    // Set the Last Name column.
                    newRow["Email"] = "david.thompson@example.com";     // Set the Email column.
                    newRow["Designation"] = "Architect";                // Set the Designation column.

                    // Add the new row to the DataTable.
                    employeeTable.Rows.Add(newRow);

                    // Set up the InsertCommand using the sp_InsertEmployee stored procedure.
                    SqlCommand insertCommand = new SqlCommand("sp_InsertEmployee", connection);
                    insertCommand.CommandType = CommandType.StoredProcedure;

                    // Map the parameters from the DataRow to the stored procedure parameters.
                    insertCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, "FirstName");
                    insertCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, "LastName");
                    insertCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 100, "Email");
                    insertCommand.Parameters.Add("@Designation", SqlDbType.NVarChar, 100, "Designation");
                    adapter.InsertCommand = insertCommand;  // Associate with the adapter.

                    // Update the database with the new employee insertion.
                    adapter.Update(employeeTable);
                    Console.WriteLine("Insertion complete.");

                    // ----------- Update (UPDATE) Example -----------
                    Console.WriteLine("\nUpdating an employee's designation...");

                    // Suppose we want to update the employee with EmployeeID=2.
                    // Find the row in the DataTable where EmployeeID equals 2.
                    DataRow[] rowsToUpdate = employeeTable.Select("EmployeeID = 2");
                    if (rowsToUpdate.Length > 0)
                    {
                        DataRow rowToUpdate = rowsToUpdate[0];
                        rowToUpdate["Designation"] = "Senior Developer"; // Update the designation.
                    }

                    // Set up the UpdateCommand using the sp_UpdateEmployee stored procedure.
                    SqlCommand updateCommand = new SqlCommand("sp_UpdateEmployee", connection);
                    updateCommand.CommandType = CommandType.StoredProcedure;

                    // Map the source columns to the parameters expected by the stored procedure.
                    updateCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");
                    updateCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, "FirstName");
                    updateCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, "LastName");
                    updateCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 100, "Email");
                    updateCommand.Parameters.Add("@Designation", SqlDbType.NVarChar, 100, "Designation");
                    adapter.UpdateCommand = updateCommand;  // Associate with the adapter.

                    // Update the database with the changes made to the employee.
                    adapter.Update(employeeTable);
                    Console.WriteLine("Update complete.");

                    // ----------- Deletion (DELETE) Example -----------
                    Console.WriteLine("\nDeleting an employee...");

                    // Suppose we want to delete the employee with EmployeeID=1.
                    DataRow[] rowsToDelete = employeeTable.Select("EmployeeID = 1");
                    if (rowsToDelete.Length > 0)
                    {
                        // Mark the found row for deletion.
                        rowsToDelete[0].Delete();
                    }

                    // Set up the DeleteCommand using the sp_DeleteEmployee stored procedure.
                    SqlCommand deleteCommand = new SqlCommand("sp_DeleteEmployee", connection);
                    deleteCommand.CommandType = CommandType.StoredProcedure;

                    // Map the source columns to the parameters expected by the stored procedure.
                    deleteCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");
                    adapter.DeleteCommand = deleteCommand;  // Associate with the adapter.

                    // Update the database to process the deletion.
                    adapter.Update(employeeTable);
                    Console.WriteLine("Deletion complete.");

                    // ----------- Display Final Employee List -----------
                    // Clear and refill the DataSet to get updated data.
                    employeeTable.Clear();
                    adapter.Fill(employeeTable);

                    Console.WriteLine("\nUpdated Employees:");
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        Console.WriteLine($"ID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}, Email: {row["Email"]}, Designation: {row["Designation"]}");
                    }
                } // Connection is automatically closed here.
            }
            catch (SqlException sqlEx)
            {
                // Exception handling for SQL Server errors.
                Console.WriteLine("A SQL error occurred: " + sqlEx.Message);
            }
            catch (Exception ex)
            {
                // General exception handling.
                Console.WriteLine("An error occurred: " + ex.Message);
            }

            Console.ReadKey();
        }
    }
}

Output:

When Should We Use ADO.NET Core SqlDataAdapter with Stored Procedures in Real-time Applications?

Using the SqlDataAdapter along with stored procedures can be particularly beneficial when:

• Disconnected Data Manipulation: Applications that work with data in batches or require offline manipulation (e.g., desktop apps, reporting systems) benefit from the disconnected nature of SqlDataAdapter.
• Performance Optimization: Precompiling SQL logic in stored procedures decreases runtime parsing overhead and can speed up complex queries.
• Enhanced Security: Stored procedures help shield the underlying tables from direct access, reducing the risk of SQL injection and enforcing permissions at the database level.
• Consistency and Reusability: When your application has multiple layers accessing the same data, stored procedures maintain consistency across operations.

In real-world applications such as enterprise resource planning (ERP) systems, customer relationship management (CRM) systems, or any application with significant data processing needs, using SqlDataAdapter with stored procedures ensures a scalable, secure, and maintainable approach to database operations.

Using ADO.NET Core with SqlDataAdapter and Stored Procedures remains a robust approach for performing CRUD operations in .NET Core applications, especially when you need the benefits of precompiled SQL logic, modular structure, and a disconnected data access model.

In the next article, I will discuss ADO.NET Core Architecture. In this article, I explain how to perform CRUD Operations using ADO.NET Core with SqlDataAdapter and Stored Procedures. I would like to have your feedback. Please post feedback, questions, or comments about this article.