CRUD Operations using ADO.NET Core with Stored Procedures

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

CRUD Operations using ADO.NET Core with Stored Procedures

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

What do you mean by Database CRUD Operations?

Database CRUD operations refer to the four fundamental actions used to interact with database systems, enabling the management and manipulation of data stored within the database. These operations are essential for creating dynamic and interactive applications that require data persistence. CRUD stands for:

• Create: This operation involves creating or adding new entries (rows) into a database table. It’s typically executed using the INSERT statement in SQL (Structured Query Language), allowing users to add new data to the database.
• Read: The read operation retrieves or reads data from a database. This can involve fetching specific rows from a table or getting all data that matches specific criteria. In SQL, the SELECT statement is used to read data, enabling users to query the database for information stored within it.
• Update: This operation allows modification of existing data within the database. The update can affect one or multiple rows of data, depending on the criteria specified. In SQL, the UPDATE statement is used to modify existing records, allowing for changes in the data without removing the existing record.
• Delete: The delete operation removes existing data from a database. Depending on the specified criteria, this can be deleting a single row, multiple rows, or even entire tables. In SQL, the DELETE statement is utilized for this purpose, enabling removing data that is no longer needed or relevant.

Together, these CRUD operations form the backbone of most database interactions, allowing for the creation, querying, modification, and deletion of data within relational databases and other types of data storage systems.

CRUD Operations using ADO.NET Core with Stored Procedures

Using ADO.NET Core to perform CRUD operations with stored procedures involves several steps, ensuring efficient communication between your .NET Core application and a database, such as SQL Server. ADO.NET Core provides a streamlined approach for data access in .NET Core applications, and when combined with stored procedures, it can enhance performance, security, and data integrity. Let us proceed and understand this step by step:

Database Table Creation with Dummy Data

Let’s start by creating the Employee and Address tables. We are going to use the following Employee and Address table in this demo:

Please use the SQL script below to create and populate the database EmployeeDB and tables Employee and Address with the required sample data. Here, we are using an SQL Server database.

CREATE DATABASE EmployeeDB;
GO

USE EmployeeDB;
GO

-- Creation of 'Address' Table
CREATE TABLE Address (
    AddressID INT PRIMARY KEY Identity,
    Street VARCHAR(255),
    City VARCHAR(100),
    State VARCHAR(100),
    PostalCode VARCHAR(20)
);

-- Inserting Dummy Data into 'Address' Table
INSERT INTO Address (Street, City, State, PostalCode) VALUES
('1234 Elm Street', 'Springfield', 'Illinois', '62704'),
('5678 Oak Street', 'Decatur', 'Alabama', '35601');

-- Creation of 'Employee' Table
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY Identity,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Email VARCHAR(100),
    AddressID INT,
    FOREIGN KEY (AddressID) REFERENCES Address(AddressID)
);

-- Inserting Dummy Data into 'Employee' Table
INSERT INTO Employee (FirstName, LastName, Email, AddressID) VALUES
('John', 'Doe', 'johndoe@example.com', 1),
('Jane', 'Doe', 'janedoe@example.com', 2);

Creating Stored Procedures for CRUD Operations:

Now, let us create Stored Procedures for CRUD Operations. So, please execute the following SQL Script on the EmployeeDB database.

-- Get All Employees
CREATE OR ALTER PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.Email, a.Street, a.City, a.State, a.PostalCode
    FROM Employee e
    INNER JOIN Address a ON e.AddressID = a.AddressID;
END;
GO

-- Get Employee By ID
CREATE OR ALTER PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.Email, a.Street, a.City, a.State, a.PostalCode
    FROM Employee e
    INNER JOIN Address a ON e.AddressID = a.AddressID
    WHERE e.EmployeeID = @EmployeeID;
END;
GO

-- Create Employee With Address
CREATE PROCEDURE CreateEmployeeWithAddress
    @FirstName VARCHAR(100),
    @LastName VARCHAR(100),
    @Email VARCHAR(100),
    @Street VARCHAR(255),
    @City VARCHAR(100),
    @State VARCHAR(100),
    @PostalCode VARCHAR(20)
AS
BEGIN
    DECLARE @AddressID INT;

    -- Insert into Address table and get the AddressID
    INSERT INTO Address (Street, City, State, PostalCode) 
    VALUES (@Street, @City, @State, @PostalCode);

    SET @AddressID = SCOPE_IDENTITY();

    -- Insert into Employee table with the new AddressID
    INSERT INTO Employee (FirstName, LastName, Email, AddressID) 
    VALUES (@FirstName, @LastName, @Email, @AddressID);
END;
GO

-- Update Employee With Address
CREATE PROCEDURE UpdateEmployeeWithAddress
    @EmployeeID INT,
    @FirstName VARCHAR(100),
    @LastName VARCHAR(100),
    @Email VARCHAR(100),
    @Street VARCHAR(255),
    @City VARCHAR(100),
    @State VARCHAR(100),
    @PostalCode VARCHAR(20),
    @AddressID INT
AS
BEGIN
    -- Update Address table
    UPDATE Address
    SET Street = @Street, City = @City, State = @State, PostalCode = @PostalCode
    WHERE AddressID = @AddressID;

    -- Update Employee table
    UPDATE Employee 
    SET FirstName = @FirstName, LastName = @LastName, Email = @Email, AddressID = @AddressID
    WHERE EmployeeID = @EmployeeID;
END;
GO

-- Delete Employee
CREATE OR ALTER PROCEDURE DeleteEmployee
    @EmployeeID INT
AS
BEGIN
    DECLARE @AddressID INT;

    -- Start transaction
    BEGIN TRANSACTION;

    -- Get the AddressID for rollback purposes
    SELECT @AddressID = AddressID FROM Employee WHERE EmployeeID = @EmployeeID;

    -- Delete the Employee record
    DELETE FROM Employee WHERE EmployeeID = @EmployeeID;

    -- Delete the Address record
    DELETE FROM Address WHERE AddressID = @AddressID;

    -- Commit transaction
    COMMIT TRANSACTION;
END;

Implement CRUD Operations using ADO.NET Core with Stored Procedures:

Here is how you can implement the CRUD operations using ADO.NET Core. This example shows operations for the Employee and Address tables:

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

                //Calling GetAllEmployees
                GetAllEmployees(connectionString);

                //Calling GetEmployeeByID
                int EmployeeId = 1;
                GetEmployeeByID(connectionString, EmployeeId);

                //Calling CreateEmployeeWithAddress
                string firstName = "Ramesh";
                string lastName = "Sharma";
                string email = "Ramesh@Example.com";
                string street = "123 Patia";
                string city = "BBSR";
                string state = "India";
                string postalCode = "755019";
                CreateEmployeeWithAddress(connectionString, firstName, lastName, email, street, city, state, postalCode);

                //Calling UpdateEmployeeWithAddress
                int employeeID = 3;
                firstName = "Rakesh";
                lastName = "Sharma";
                email = "Rakesh@Example.com";
                street = "3456 Patia";
                city = "CTC";
                state = "India";
                postalCode = "755024";
                int addressID = 3;
                UpdateEmployeeWithAddress(connectionString, employeeID, firstName, lastName, email, street, city, state, postalCode, addressID);

                //Calling
                employeeID = 3;
                DeleteEmployee(connectionString, employeeID);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }

        static void GetAllEmployees(string connectionString)
        {
            Console.WriteLine("GetAllEmployees Stored Procedure Called");
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand("GetAllEmployees", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine($"EmployeeID: {reader["EmployeeID"]}, FirstName: {reader["FirstName"]}, LastName: {reader["LastName"]}, Email: {reader["Email"]}");
                    Console.WriteLine($"Address: {reader["Street"]}, {reader["City"]}, {reader["State"]}, PostalCode: {reader["PostalCode"]}\n");
                }

                reader.Close();
                connection.Close();
            }
        }

        static void GetEmployeeByID(string connectionString, int employeeID)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                Console.WriteLine("GetEmployeeByID Stored Procedure Called");
                SqlCommand command = new SqlCommand("GetEmployeeByID", connection);
                command.CommandType = CommandType.StoredProcedure;

                // Add parameter for EmployeeID
                command.Parameters.AddWithValue("@EmployeeID", employeeID);

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine($"Employee: {reader["FirstName"]} {reader["LastName"]}, Email: {reader["Email"]}");
                    Console.WriteLine($"Address: {reader["Street"]}, {reader["City"]}, {reader["State"]}, {reader["PostalCode"]}");
                }

                reader.Close();
                connection.Close();
            }
        }

        static void CreateEmployeeWithAddress(string connectionString, string firstName, string lastName, string email, string street, string city, string state, string postalCode)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                Console.WriteLine("CreateEmployeeWithAddress Stored Procedure Called");

                SqlCommand command = new SqlCommand("CreateEmployeeWithAddress", connection);
                command.CommandType = CommandType.StoredProcedure;

                // Add parameters for Employee and Address
                command.Parameters.AddWithValue("@FirstName", firstName);
                command.Parameters.AddWithValue("@LastName", lastName);
                command.Parameters.AddWithValue("@Email", email);
                command.Parameters.AddWithValue("@Street", street);
                command.Parameters.AddWithValue("@City", city);
                command.Parameters.AddWithValue("@State", state);
                command.Parameters.AddWithValue("@PostalCode", postalCode);

                connection.Open();
                command.ExecuteNonQuery();
                Console.WriteLine("Employee and Address created successfully.");
                connection.Close();
            }
        }

        static void UpdateEmployeeWithAddress(string connectionString, int employeeID, string firstName, string lastName, string email, string street, string city, string state, string postalCode, int addressID)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                Console.WriteLine("UpdateEmployeeWithAddress Stored Procedure Called");
                SqlCommand command = new SqlCommand("UpdateEmployeeWithAddress", connection);
                command.CommandType = CommandType.StoredProcedure;

                // Add parameters for Employee and Address
                command.Parameters.AddWithValue("@EmployeeID", employeeID);
                command.Parameters.AddWithValue("@FirstName", firstName);
                command.Parameters.AddWithValue("@LastName", lastName);
                command.Parameters.AddWithValue("@Email", email);
                command.Parameters.AddWithValue("@Street", street);
                command.Parameters.AddWithValue("@City", city);
                command.Parameters.AddWithValue("@State", state);
                command.Parameters.AddWithValue("@PostalCode", postalCode);
                command.Parameters.AddWithValue("@AddressID", addressID);

                connection.Open();
                command.ExecuteNonQuery();
                Console.WriteLine("Employee and Addresses updated successfully.");
                connection.Close();
            }
        }

        static void DeleteEmployee(string connectionString, int employeeID)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                Console.WriteLine("DeleteEmployee Stored Procedure Called");
                SqlCommand command = new SqlCommand("DeleteEmployee", connection);
                command.CommandType = CommandType.StoredProcedure;

                // Add parameter for EmployeeID
                command.Parameters.AddWithValue("@EmployeeID", employeeID);

                connection.Open();
                int result = command.ExecuteNonQuery();

                if (result > 0)
                {
                    Console.WriteLine("Employee and their Address deleted successfully.");
                }
                else
                {
                    Console.WriteLine("Employee not found.");
                }

                connection.Close();
            }
        }
    }
}

Output:

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