How to use SqlClient in ASP.NET Core?

Question

I am trying to use SQLClient library in the ASP.net Core but cant seem to get it working. I found this article online advising how to setup but its not working for me: http://blog.developers.ba/using-classic-ado-net-in-asp-net-vnext/

I have a simple console application package. My project.json looks like this:

{
  "version": "1.0.0-*",
  "description": "DBTest Console Application",
  "authors": [ "" ],
  "tags": [ "" ],
  "projectUrl": "",
  "licenseUrl": "",

  "compilationOptions": {
    "emitEntryPoint": true
  },

  "dependencies": {
    "System.Data.Common": "4.0.1-beta-23516",
    "System.Data.SqlClient" :  "4.0.0-beta-23516"
  },

  "commands": {
    "DBTest": "DBTest"
  },

  "frameworks": {
    "dnx451": { },
    "dnxcore50": {
      "dependencies": {
        "Microsoft.CSharp": "4.0.1-beta-23516",
        "System.Collections": "4.0.11-beta-23516",
        "System.Console": "4.0.0-beta-23516",
        "System.Linq": "4.0.1-beta-23516",
        "System.Threading": "4.0.11-beta-23516"
      }
    }
  }
}

And I try the following code:

using System;
using System.Data.SqlClient;

namespace DBTest
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (SqlConnection con = new SqlConnection(ConnStr)) {
                con.Open();
                try {
                    using (SqlCommand command = new SqlCommand("SELECT * FROM SAMPLETABLE", con)) {
                        command.ExecuteNonQuery();
                    }
                }
                catch {
                    Console.WriteLine("Something went wrong");
                }
            }

            Console.Read();
        }
    }
}

But get the following errors:

Anyone else got this working?

Laconic

I don't see a reference to System.Runtime in any of your dependencies. Have you tried adding one? – Eldwun

also you are not doing UPDATE, INSERT or DELETE command in your sql so why are you using command.ExecuteNonQuery(); look up using the Fill() method to return data from a database or the ExecuteScalar method if returning only a single row. you need to also add references not only to the using section in the .cs file class header but also manually add them to the reference node in the project – Vesting

Your errors indicate you haven't added the proper references for DNX 4.5.1. You're building for two project types at the same time. If you don't care about DNX.4.5.1, then remove that from your configuration and it should build. – Hyssop

Guys - thank you all so much! Removed the DNX 4.5.1 section and added the dependency of System.Runtime to the settings and it worked perfectly (all be it after a visual studio restart!). Thanks again!!! – Laconic

Answer 1

I think you may have missed this part in the tutorial:

Instead of referencing System.Data and System.Data.SqlClient you need to grab from Nuget:

System.Data.Common and System.Data.SqlClient.

Currently this creates dependency in project.json –> aspnetcore50 section to these two libraries.

"aspnetcore50": {
       "dependencies": {
           "System.Runtime": "4.0.20-beta-22523",
           "System.Data.Common": "4.0.0.0-beta-22605",
           "System.Data.SqlClient": "4.0.0.0-beta-22605"
       }
}

Try getting System.Data.Common and System.Data.SqlClient via Nuget and see if this adds the above dependencies for you, but in a nutshell you are missing System.Runtime.

Edit: As per Mozarts answer, if you are using .NET Core 3+, reference Microsoft.Data.SqlClient instead.

Tansey

I only had to add System.Data.SqlClient via Nuget and it works with Dapper in .NET Core 1.1. – Sentry

I have just added system.data.sqlclient via Nuget and it works – Coycoyle

This seems to be fixed in .NET Core 2.0. For me at least. – Sentry

I used interface instead of editing config file: right-clicked on Dependencies in Solution Explorer, NuGet... . – Jehol

I had problems adding this package with Nuget or dotnet add package. A solution was to put the dependency drectly to the project file and perform a restore - dotnet restore. Nuget.config file should contain the nuget.org in a packageSources section. – Haggai

Added SQLClient Nuget package in .Net Core with Dapper and worked fine, thank you. – Maines

Which one is right,? use either System.Data.SqlClient or Microsoft.Data.SqlClient in net core? – Stewpan

Answer 2

For Dot Net Core 3, Microsoft.Data.SqlClient should be used.

Latoria

This comment is life-saver for anyone migrating from .NET Core 2.2 to .NET Core 3.0. Replace all references from System.Data.SqlClient to Microsoft.Data.SqlClient. – Odette

@mozart, can .Net Core 2.2 use Microsoft.Data.SqlClient ? or Microsoft.Data.SqlClient just used only for .net Core 3 ? – Grained

@Grained I solved this issue while moving to .Net Core 3 so I haven't tried it with 2.2, I do not think it works "Each parameter has a Data type right ?", but you can give it a try. – Latoria

@Mozar, and your connection to database still using stringbuilder ? – Grained

@Grained If you meant SqlConnectionStringBuilder, yes that is available in this NuGget. – Latoria

Yes @AdmirTuzović it is a life saver answer. I am fetching this problem while migrating from core 2.0 to 3.1. I added Microsoft.Data.SqlClient using nuget package manager and replaced all System.Data.SqlClient to Microsoft.Data.SqlClient. Now it works as expected.. – Trefoil

Why did they change it from "System.Data.SqlClient" to "Microsoft.Data.SqlClient"? – Sulph

@SteveSmith you can check the article in the link. – Latoria

@SteveSmith "We couldn’t just ship a new package that replaces System.Data.SqlClient. That would conflict with what lives inside .NET Framework now. Which brings us to our chosen solution… Creating a new SqlClient in a new namespace allows both the old System.Data.SqlClient and new Microsoft.Data.SqlClient to live side-by-side... We have no intention of dropping support for System.Data.SqlClient any time soon.. If you have a typical application that doesn’t use any of the newest SQL features, then you will still be well served by a stable and reliable System.Data.SqlClient for many years." – Gossipmonger

Answer 3

Try this one Open your projectname.csproj file its work for me.

<PackageReference Include="System.Data.SqlClient" Version="4.6.0" />

You need to add this Reference "ItemGroup" tag inside.

Joseph