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 
        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>();
        while (reader.Read())
            Employee item = new Employee();
            item.EmployeeID = reader.GetInt32(0);
            item.FirstName = reader.GetString(1);
            item.LastName = reader.GetString(2);
        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!!

"A good set of software tools enhances your productivity; a good set of Pranayamas enhances your Meditation."

Bipin Joshi is an independent software consultant, trainer, author, and meditation teacher. He has been programming, meditating, and teaching for 25+ years. He conducts instructor-led online training courses in ASP.NET family of technologies for individuals and small groups. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches Ajapa Yoga to interested individuals. To know more about him click here.

Get connected : Facebook  Twitter  LinkedIn  YouTube

Posted On : 16 September 2019

Tags : ASP.NET ASP.NET Core Data Access SQL Server C# Visual Studio