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
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
To use classes such as SqlConnection, SqlCommand, and SqlDataReader you can
use Microsoft.Data.SqlClient namespace in your code.
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
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT EmployeeID, FirstName,
LastName FROM Employees";
SqlDataReader reader = cmd.ExecuteReader();
List<Employee> employees = new List<Employee>();
Employee item = new Employee();
item.EmployeeID = reader.GetInt32(0);
item.FirstName = reader.GetString(1);
item.LastName = reader.GetString(2);
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
That's it for now! Keep coding!!