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?

I don't see a reference to System.Runtime in any of your dependencies. Have you tried adding one? — thorkia, Feb 16 '16 at 22:49
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 — MethodMan, Feb 16 '16 at 22:53
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. — mason, Feb 16 '16 at 23:00
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!!! — Rob, Feb 16 '16 at 23:03

MikeDub · Accepted Answer · 2019-12-04T03:44:28.667

208

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.

edited Dec 04 '19 at 03:44

answered Feb 16 '16 at 22:53

MikeDub

5,143
3
27
44

11

I only had to add `System.Data.SqlClient` via `Nuget` and it works with `Dapper` in `.NET Core 1.1`. – tedi Jul 26 '17 at 08:52
4

I have just added system.data.sqlclient via Nuget and it works – Shahram Aug 14 '17 at 06:42
1

This seems to be fixed in `.NET Core 2.0`. For me at least. – tedi Oct 05 '17 at 08:24
I used interface instead of editing config file: right-clicked on Dependencies in Solution Explorer, NuGet... . – Evgeny Nozdrev Aug 24 '18 at 14:00
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. – user2809176 Nov 27 '19 at 10:28
Added SQLClient Nuget package in .Net Core with Dapper and worked fine, thank you. – Avdhoota Apr 29 '20 at 20:34
Which one is right,? use either System.Data.SqlClient or Microsoft.Data.SqlClient in net core? – Karthic G Apr 12 '21 at 10:35

score 87 · Answer 2 · edited May 19 '20 at 20:37

87

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

edited May 19 '20 at 20:37

KyleMit

30,350
66
462
664

answered Oct 15 '19 at 12:34

Mozart

2,117
2
20
38

10

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. – Admir Tuzović Nov 03 '19 at 20:51
@mozart, can .Net Core 2.2 use Microsoft.Data.SqlClient ? or Microsoft.Data.SqlClient just used only for .net Core 3 ? – Daleman Dec 09 '19 at 10:14
@Daleman 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. – Mozart Dec 09 '19 at 13:47
@Mozar, and your connection to database still using stringbuilder ? – Daleman Dec 09 '19 at 14:21
@Daleman If you meant SqlConnectionStringBuilder, yes that is available in this NuGget. – Mozart Dec 10 '19 at 10:40
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.. – Ehasanul Hoque Jan 06 '20 at 11:18
Why did they change it from "System.Data.SqlClient" to "Microsoft.Data.SqlClient"? – Steve Smith Jan 26 '21 at 11:41
1

@SteveSmith you can check the article in the link. – Mozart Jan 26 '21 at 11:50
@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._" – ruffin Sep 28 '21 at 15:24

score 4 · Answer 3 · answered Jun 27 '19 at 13:12

4

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.

answered Jun 27 '19 at 13:12

jishan siddique

1,848
2
12
23

How to use SqlClient in ASP.NET Core?

3 Answers3

Linked