Utilize new data provider for SQL Server in ASP.NET Core apps

If you are developing database centric web applications for quite some time you have probably used SQL Server data provider a.k.a. System.Data.SqlClient. There are two code bases of SQL Server data provider - one that comes with .NET Framework and another that comes with .NET Core. Now Microsoft has developed a new data provider for SQL Server that is available in Microsoft.Data.SqlClient package.

The new SQL Server data provider is quite similar to System.Data.SqlClient in that it has similar set of classes - SqlConnection, SqlCommand, SqlParameter, and so on. However, going forward Microsoft recommends to use this new data provider in your applications. Going forward new features will be added to this new data provider.

Now a days Entity Framework Core is quite popular and common way of accessing data from SQL Server. And Microsoft.EntityFrameworkCore.SqlServer package provides the EF Core provider for SQL Server. If you add this package to your ASP.NET Core 3 applications you will find that it internally uses the new Microsoft.Data.SqlClient data provider.

If you want to use the new data provider directly rather than using EF Core, you can add NuGet package for Microsoft.Data.SqlClient in your project.

Notice that EF Core preview version uses preview version of Microsoft.Data.SqlClient but when you explicitly add it you can use the stable version.

To use classes such as SqlConnection, SqlCommand, and SqlDataReader you can use Microsoft.Data.SqlClient namespace in your code.

using Microsoft.Data.SqlClient;

You can then use the required classes. As an example, consider a repository that performs CRUD operations on Employees table of Northwind database. The GetAll() method of the repository can be written as follows:

public List<Employee> GetAll()
{
    using (SqlConnection cnn = new 
SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cnn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT EmployeeID, FirstName, 
LastName FROM Employees";

        cnn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        List<Employee> employees = new List<Employee>();
        while (reader.Read())
        {
            Employee item = new Employee();
            item.EmployeeID = reader.GetInt32(0);
            item.FirstName = reader.GetString(1);
            item.LastName = reader.GetString(2);
            employees.Add(item);
        }
        reader.Close();
        cnn.Close();
        return employees;
    }
}

The above code should look quite familiar to you because it uses the same classes - SqlConnection, SqlCommand, and SqlDataReader. Note that some common types such as CommandType enumeration still come from System.Data namespace.

So, if you want to migrate older System.Data.SqlClient code to the new Microsoft.Data.SqlClient data provider, that should be fairly simple task in most of the cases. 

You may read more about the new SQL Server data provider here. The GitHub repository for this new data provider can be found here.

That's it for now! Keep coding!!