Understand ADO.NET Data Provider for SQL Server in ASP.NET Core

If you worked with ASP.NET Core projects before probably you are aware of Entity Framework Core and its capabilities. EF Core is a feature rich ORM and provides an easy to use approach most of for your data access needs. No wonder many modern applications built using ASP.NET Core tend to rely on EF Core for the sake of data access. However, at times you may want to skip using EF Core and delve into the underlying data provider yourself. It would be helpful for beginners to understand how EF Core might be working behind the curtains of easy to use ORM. To that end this article gives you a brief overview of the ADO.NET data provider for SQL Server.

An ADO.NET Data Provider is a component that allows to communicate with a database. To communicate with a SQL Server database you would resort to ADO.NET data provider for SQL Server. In .NET Framework and the older versions of .NET Core System.Data.SqlClient assembly / package represented this data provider. However, a few years ago Microsoft released an enhanced version of this provider in the form of Microsoft.Data.SqlClient NuGet package. I have already written a short article that explains why this new data provider was released and how you can use it in your ASP.NET Core apps. You may read it here.

Some of the reasons why you would like to use Microsoft.data.SqlClient directly rather than going to EF Core include:

1. You are migrating an ASP.NET web app that was written on .NET Framework and uses ADO.NET. You might want to reduce your code migration efforts by sticking with the new data provider that is compatible with the older provider.

2. EF Core provides a very rich set of functionality. Your application might not want much of these fancy features and you might realize that something simple would be more appropriate fit in a given situation.

3. You might want to use some third-party libraries such as Dapper for your data access that require you to deal with data provider objects in your code.

4. You might want to take the total control of your data access queries in your hand rather than relying on EF Core's auto-generated queries.

5. You might be heavily using stored procedures or such techniques for data access and feel that EF Core might not be the best fitment in the given situation.

If you take a look at the Microsoft.Data.SqlClient package page on NuGet website you will see this:

If you switch to the Used By tab you will notice that EF Core (and third-party libraries like Dapper) itself depends on Microsoft.data.SqlClient for its functioning.

Let's quickly confirm this by creating a new ASP.NET Core web application and adding this EF Core NuGet package to it.

Create a new ASP.NET Core web application based on the Empty project template and add Microsoft.EntityFrameworkCore.SqlServer NuGet package to it.

After adding the package expand the Dependencies folder and also this package entry to reveal its internal dependencies.

As you can see, Microsoft.EntityFrameworkCore.SqlServer internally uses Microsoft.Data.SqlClient data provider for its working.

The NuGet page of Microsoft.Data.SqlClient also lists the important classes of the data provider such as :

  • SqlConnection
  • SqlCommand
  • SqlParameter
  • SqlDataReader
  • SqlTransaction
  • SqlClientFactory
  • SqlException

If you haven't worked with ADO.NET before it would be worthwhile to quickly mention the purpose of these classes.

The SqlConnection class represents a connection with an SQL Server database and allows you to open and close that connection for performing data operations. It also helps you initiate a database transaction.

The SqlCommand class represents a fetch or action query (or stored procedure) that you would like to execute against the database. The SqlCommand object needs an open SqlConnection to execute the query.

The SqlParameter class represents a query or stored procedure parameter needed by the query specified in the SqlCommand object.

The SqlDataReader class represents a fast, forward-only and read-only cursor pointing to the records returned by the SqlCommand. A data reader requires an open connection with the underlying database in order to iterate through the records.

The SqlTransaction class represents a database transaction and allows you to commit or rollback it as required.

The SqlClientfactory class allows you to create instances of the other classes such as SqlConnection and SqlCommand classes.

The SqlException class represents an exception thrown by the underlying SQL Server during the working of the other objects of the provider.

Although not listed on the NuGet page the Microsoft.Data.SqlClient data provider also contains SqlDataAdapter in case you want to work with DataSet and DataTable objects. Although we won't discuss DataSet here in any details, it would be worthwhile to mention that System.Data assembly included in the core framework contains a set of classes that are independent of any data provider. Take a look at the following figure that shows System.Data after expanding the Microsoft.NETCore.App in the Solution Explorer.

Now that you know the basics of Microsoft.Data.SqlClient data provider, it's time to use various classes we discussed above. That's what we will do in the next article of this series.

That's it for now! Keep coding!!

Bipin Joshi is an independent software consultant and trainer by profession specializing in Microsoft web development technologies. Having embraced the Yoga way of life he is also a meditation teacher and spiritual guide to his students. He is a prolific author and writes regularly about software development and yoga on his websites. He is programming, meditating, writing, and teaching for over 27 years. To know more about his ASP.NET online courses go here. More details about his Kriya and Meditation online course are available here.

Posted On : 01 August 2022