Learn ASP.NET Core 3.0 : MVC, Razor Pages, Web API, Entity Framework Core, and Blazor.
Microsoft's official documentation can be found here. Looking for professional online training courses? Next weekend batches are starting in November 2019. More details here.

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!!


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

Bipin Joshi is an independent software consultant, trainer, author, yoga mentor, and meditation teacher. He has been programming, meditating, and teaching for 24+ 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 article updates : Facebook  Twitter  LinkedIn

Posted On : 16 September 2019


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


Subscribe to our newsletter

Get monthly email updates about new articles, tutorials, code samples, and how-tos getting added to our knowledge base.

  

Receive Weekly Updates