SQL VS ADO.NET With Stored Procedures

Stored Procedures have been used as a solution for so many problems, performance issues and not being able to separate the business Logic and transaction management. Stored Procedure tries to solve this major problem while developing a website. In .NET when we want to create a website or desktop application and when we have to query the database we mainly use Stored Procedure. But, in .NET we majorly work with programming languages C#, F#, and all and thus creating and executing the procedure like SQL is not the same.

The Ultimate Comparison of ADO.NET and Entity Framework

.NET comes with 3 widely used features to work and communicate with database:

1. ADO.NET
2. LINQ (Language Integrated Query)
3. Entity Framework

Let’s Create a table to work on it

CREATE TABLE Employee (
    EmployeeId INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50),
    Salary DECIMAL(10, 2)
);

Insert data into it

INSERT INTO Employee (EmployeeId, FirstName, LastName, Department, Salary)
VALUES
    (1, 'John', 'Doe', 'IT', 50000.00),
    (2, 'Jane', 'Smith', 'HR', 60000.00),
    (3, 'Alice', 'Johnson', 'Finance', 70000.00),
    (4, 'Bob', 'Williams', 'Marketing', 55000.00);
    (5, 'Michael', 'Brown', 'IT', 48000.00),
    (6, 'Jennifer', 'Davis', 'HR', 52000.00),
    (7, 'David', 'Miller', 'Finance', 75000.00),
    (8, 'Emily', 'Wilson', 'Marketing', 58000.00),
    (9, 'Daniel', 'Moore', 'IT', 51000.00),
    (10, 'Linda', 'Taylor', 'HR', 62000.00),
    (11, 'Richard', 'Anderson', 'Finance', 73000.00),
    (12, 'Susan', 'Thomas', 'Marketing', 57000.00),
    (13, 'Matthew', 'Jackson', 'IT', 49000.00),
    (14, 'Amanda', 'White', 'HR', 61000.00),
    (15, 'Christopher', 'Harris', 'Finance', 72000.00),
    (16, 'Sarah', 'Martinez', 'Marketing', 59000.00),
    (17, 'Joshua', 'Lee', 'IT', 52000.00),
    (18, 'Jessica', 'Garcia', 'HR', 63000.00),
    (19, 'Andrew', 'Rodriguez', 'Finance', 71000.00),
    (20, 'Lauren', 'Lopez', 'Marketing', 60000.00)

Also, we will create a Stored Procedure in a database to get an idea about how we can execute the procedure using this framework.

CREATE PROCEDURE GetEmployeeById
    @EmployeeId INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeId = @EmployeeId
END

This will get the employee based on the id provided into the stored procedure.

ADO.NET

ADO.NET is the oldest of all these three technologies and it provides us with a way to connect directly with the database and access it with the SQL commands. Generally, we use ADO.NET when we have to work with legacy software. Also, it can be used when the performance optimization is a priority. As mentioned it is old technology and thus we have to write so much boilerplate code to compared to other ORMs like Entity Framework.

So, in ADO.NET we can directly execute the procedure by using the sql commands and we don’t require any context file.

C#

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

namespace C__Application
{
    internal class ADO
    {
        static void Main(string[] args)
        {
            const string CONNECTION_STRING = "YourConnectionString";
            using (SqlConnection connection = new SqlConnection(CONNECTION_STRING))
            {
                // Create command
                using (SqlCommand command = new SqlCommand("GetEmployeeById", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    // Add parameters
                    command.Parameters.AddWithValue("@EmployeeId", 1);

                    // Open connection
                    connection.Open();

                    // Execute command
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        // Process results
                        while (reader.Read())
                        {
                            // Accessing data using numbered Index
                            Console.WriteLine(reader[0] + " " + reader[1] + " " + reader[2] + " " + reader[3] + " " + reader[4] + " ");

                            // Accessing data using string Key
                            Console.WriteLine(reader["EmployeeId"] + " " + reader["FirstName"] + " " + reader["LastName"] + " " + reader["Department"] + " " + reader["Salary"] + " ");
                        }
                    }
                }
            }

            Console.ReadKey();
        }
    }

}

Output:

Output of Execution

Now let’s Create a Stored procedure using ADP.Net and then execute using. The idea behind this is to execute the query to generate a procedure from the application directly.

C#

using System.Data.SqlClient;

namespace C__Application
{
    internal class ADO
    {
        static void Main(string[] args)
        {
            const string CONNECTION_STRING = "ConnectionString";

            using (SqlConnection connection = new SqlConnection(CONNECTION_STRING))
            {
                // Open connection
                connection.Open();

                // Create command
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    command.CommandText = @"
                        CREATE PROCEDURE GetEmployees
                        AS
                        BEGIN
                            SELECT * FROM Employee;
                        END
        ";
                    command.ExecuteNonQuery();
                }
            }

            Console.ReadKey();
        }
    }

}

This will create a procedure in the database and then we can use it on both database and application level.

LINQ to SQL

Language Integrated Query is an object-relational mapping technology that reduces the boilerplate code for mapping the SQL with object in our code. This solves the bottleneck of ADO.NET. LINQ helps us to generate SQL automatically based on the LINQ expressions, which can lead to efficient database access.

For Execution of Procedure, we have to drag and drop procedures onto the LINQ to SQL designer surface in Visual Studio, which will generate methods for calling those stored procedures.

C#

using (YourDbContext context = new YourDbContext())
{
    var result = context.GetEmployeeById(employeeId);
    // Process the result
}

When we use LINQ to SQL we cannot create a stored procedure using query. And thus we generally create a sp in the database and then we will put it into the designer to use it.

Entity Framework

It is the most advanced technology of these three. It is a more feature rich Object relation mapper compared to LINQ. It provides its support with different databases. It comes with advanced mapping configurations and other advanced ORM features. It creates a model of the tables in the database and due to this we developers can interact with them more with an object-oriented approach. EF Core comes with databases tasks like CRUD operations and relationship management.

In EF Core we have to apply Scaffold Command because we are using DB first approach here. The Scaffold command will look like:

–Scaffold Command

Scaffold-DbContext “Server=(localDb); Database=Geeks; Integrated Security=True;TrustServerCertificate=True” Microsoft.EntityFrameworkCore.SqlServer -OutputDir “Models”

It will generate a DBContext class and Class representing tables in the Models folder as shown in the images.

Context File

Executing the SP we created above

C#

using GeeksMVC.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

namespace GeeksMVC.Controllers
{
    [ApiController]
    [Route("[controller]/[action]/{id?}")]
    public class EmployeeController : Controller
    {
        private readonly GeeksContext context;

        public EmployeeController(GeeksContext geeksContext)
        {
            this.context = geeksContext;
        }
        [HttpGet]
        public IActionResult Index()
        {

            var result = context.Database.SqlQueryRaw<Employee>("GetEmployeeById @EmployeeId", new SqlParameter("EmployeeId", 2));
            return Ok(result);

        }
    }
}

Output:

SP Result

Creating a stored Procedure using EF Core.

C#

using GeeksMVC.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace GeeksMVC.Controllers
{
    [ApiController]
    [Route("[controller]/[action]/{id?}")]
    public class EmployeeController : Controller
    {
        private readonly GeeksContext context;

        public EmployeeController(GeeksContext geeksContext)
        {
            this.context = geeksContext;
        }
        [HttpGet]
        public IActionResult Index()
        {

            context.Database.ExecuteSqlRaw(@"CREATE PROCEDURE GetEmployees AS BEGIN SELECT * FROM Employee; END");

            var result = context.Database.SqlQueryRaw<Employee>("GetEmployees");
            return Ok(result);

        }
    }
}

Output:

Result

The difference between above code and the code we have seen in the ADO.NET is that we don’t have to create an instance of SQLConnection and SQLCommand. We will just work without the DBContext Class.

Conclusion

Stored Procedures are widely used when it comes to working with databases. In .NET we have seen the three ways by which we can create and execute the procedure. In terms of functionality and execution speed, we mainly choose Entity framework because with new versions of .NET, EFCore is getting better in terms of efficiency and thus we mainly use EF core on top of all these other options.