Different ways to write a method that reads list of entities from SQL Server in C# language

There are different ways we can do same work. With evolution of .net technologies we have found different technologies to perform data operations. Here we will look at different ways of getting a list of employees from SQL Server database using different data access technologies.

Method-1: using ADO.NET

This method retrieves the employee list from the "Employee" table in the SQL Server database using a SELECT query, and maps the data to a list of Employee objects. Note that in this example, the Employee class has properties for the Id, Name, Department, and Salary fields in the database, and the SqlDataReader's Get methods are used to retrieve the values for each field from each record.

using System.Data.SqlClient;

using System.Collections.Generic;

public class Employee

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string Department { get; set; }

    public decimal Salary { get; set; }

}

public List<Employee> GetEmployeeListFromSqlServer()

{

    List<Employee> employees = new List<Employee>();

   

    string connectionString = "Data Source=serverName;Initial Catalog=databaseName;

User ID=userName;Password=password";

    string query = "SELECT Id, Name, Department, Salary FROM Employee";

   

    using (SqlConnection connection = new SqlConnection(connectionString))

    {

        SqlCommand command = new SqlCommand(query, connection);

        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

       

        while (reader.Read())

        {

            Employee employee = new Employee();

            employee.Id = reader.GetInt32(0);

            employee.Name = reader.GetString(1);

            employee.Department = reader.GetString(2);

            employee.Salary = reader.GetDecimal(3);

           

            employees.Add(employee);

        }

       

        reader.Close();

    }

   

    return employees;

}

Method-2: using ADO.NET async

In this async version of the method, the SqlConnection is opened asynchronously using await connection.OpenAsync(), and the SqlDataReader is created and executed asynchronously using await command.ExecuteReaderAsync(). The while loop that reads the data is also made asynchronous using await reader.ReadAsync(). Note that the using statements are still used to ensure proper disposal of resources.

using System.Data.SqlClient;

using System.Collections.Generic;

using System.Threading.Tasks;

public class Employee

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string Department { get; set; }

    public decimal Salary { get; set; }

}

public async Task<List<Employee>> GetEmployeeListFromSqlServerAsync()

{

    List<Employee> employees = new List<Employee>();

   

    string connectionString = "Data Source=serverName;Initial Catalog=databaseName;

User ID=userName;Password=password";

    string query = "SELECT Id, Name, Department, Salary FROM Employee";

   

    using (SqlConnection connection = new SqlConnection(connectionString))

    {

        await connection.OpenAsync();

       

        using (SqlCommand command = new SqlCommand(query, connection))

        using (SqlDataReader reader = await command.ExecuteReaderAsync())

        {

            while (await reader.ReadAsync())

            {

                Employee employee = new Employee();

                employee.Id = reader.GetInt32(0);

                employee.Name = reader.GetString(1);

                employee.Department = reader.GetString(2);

                employee.Salary = reader.GetDecimal(3);

               

                employees.Add(employee);

            }

        }

    }

   

    return employees;

}

Method-3: using Dapper

In this async version of the method, the SqlConnection is opened asynchronously using await connection.OpenAsync(). The query is then executed asynchronously using Dapper's QueryAsync method, which returns a list of Employee objects already mapped to the data returned by the SQL query. The using statement is still used to ensure proper disposal of resources.

using System.Data.SqlClient;

using System.Collections.Generic;

using System.Threading.Tasks;

using Dapper;

public class Employee

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string Department { get; set; }

    public decimal Salary { get; set; }

}

public async Task<List<Employee>> GetEmployeeListFromSqlServerAsync()

{

    List<Employee> employees;

   

    string connectionString = "Data Source=serverName;Initial Catalog=databaseName;

User ID=userName;Password=password";

    string query = "SELECT Id, Name, Department, Salary FROM Employee";

   

    using (SqlConnection connection = new SqlConnection(connectionString))

    {

        await connection.OpenAsync();

        employees = (await connection.QueryAsync<Employee>(query)).AsList();

    }

   

    return employees;

}

Method-4: using EF Core

In this async version of the method, a DbContextOptionsBuilder is used to configure the connection string and then used to create a new DbContext instance. The Set method is used to retrieve the DbSet<Employee>, and then the ToListAsync method is called to execute the query asynchronously and retrieve the Employee objects already mapped to the data returned by the SQL query. The using statement is still used to ensure proper disposal of resources. Note that you will need to add the appropriate NuGet packages and configure EF Core for your specific database provider.

 

using System.Collections.Generic;

using System.Threading.Tasks;

using Microsoft.EntityFrameworkCore;

public class Employee

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string Department { get; set; }

    public decimal Salary { get; set; }

}

public async Task<List<Employee>> GetEmployeeListFromSqlServerAsync()

{

    List<Employee> employees;

   

    string connectionString = "Data Source=serverName;Initial Catalog=databaseName;

User ID=userName;Password=password";

   

    DbContextOptionsBuilder dbContextOptionsBuilder = new DbContextOptionsBuilder();

    dbContextOptionsBuilder.UseSqlServer(connectionString);

    using (var dbContext = new DbContext(dbContextOptionsBuilder.Options))

    {

        employees = await dbContext.Set<Employee>().ToListAsync();

    }

   

    return employees;

}

 

Method-5: LINQ to SQL:

In this async version of the method, a DataContext is used to connect to the database and a Table<Employee> is used to retrieve the table of Employee objects. The ToListAsync method is called on the Table<Employee> to execute the query asynchronously and retrieve the Employee objects already mapped to the data returned by the SQL query. Note that you will need to add the appropriate references to System.Data.Linq and configure the DataContext for your specific database provider. The using statement is still used to ensure proper disposal of resources.

using System.Data.Linq;

using System.Collections.Generic;

using System.Threading.Tasks;

using System.Linq;

public class Employee

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string Department { get; set; }

    public decimal Salary { get; set; }

}

public async Task<List<Employee>> GetEmployeeListFromSqlServerAsync()

{

    List<Employee> employees;

   

    string connectionString = "Data Source=serverName;Initial Catalog=databaseName;

User ID=userName;Password=password";

   

    DataContext dataContext = new DataContext(connectionString);

    Table<Employee> employeeTable = dataContext.GetTable<Employee>();

   

    employees = await employeeTable.ToListAsync();

   

    return employees;

}