How to connect with SQL Server database using .NET Core Class Library (.NET Standard)?

Question

I have just started to create an ASP.NET Core Web API Project. I am not much aware of "ASP.NET Core .NET Standard Library".

I am creating this application using Visual Studio 2017 RC and in the application, I have taken a project of type Class Library (.NET Standard) at repository layer.

Following is the screenshot for the same:

Now from repository Layer I want to connect to the database. I have created a variable

IDbConnection con;

Now I am trying to add reference of System.Data but I am unable to add any reference because when I am opening the add reference window then I am getting the following message:

No Framework assemblies were found on the machine.

How can I connect to database using .NET Core Class Library(.NET Standard)?

Answer 1

.NET Standard Class libraries don't work by directly referencing a DLL, because with .NET Core there is no guarantee the framework will be installed on the system and .NET Core applications can also run as self-contained applications which ship the framework libraries with the application and do not require a runtime to be installed before.

You have to use the NuGet package manager (or project.json or *.csproj in VS2017) to add dependencies. For SQLServer you need to add the System.Data.SqlClient package (link) if you want to directly communicate with the Database (i.e. w/o an ORM).

Answer 2

Above answer (Tseng) may have been valid in 2016 and .NET Standard 1.4, but in the meantime, Microsoft did remove some showstoppers, allowing access to SQL Server from a .NET Standard 2.1 library. Mind the old System.Data.SqlClient will not link, so you (really!) have to refer EF6 via NuGet and change some using.

• Create a .NET Standard Class library and put version on 2.1

• Install Entity framework (this contains the lower level libs)

• In using, refer to System.Data and to Microsoft.Data

Using are:

using System.Data
using Microsoft.Data.SqlClient

Now, "legacy" classes like DataSet, SqlConnection and SqlClient will become available.

It is not completely compatible (yet) There are some things that are not available in .NET standard 2.1, such as enumerating available SQL servers on the LAN. This was done with SqlDataSourceEnumerator which is a class in System.Data I cannot locate in Microsoft.Data.

NOTE: I tested successfully with a .Net Core 3.1 console application. A Standard lib configured as above can be called from .NET Core and connect to the database without issues. You cannot use a .NET Framework caller.