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