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
– thorkia, Commented Feb 16, 2016 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
– MethodMan, Commented Feb 16, 2016 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
– mason, Commented Feb 16, 2016 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
– Rob, Commented Feb 16, 2016 at 23:03

MikeDub · Accepted Answer · 2019-12-04 03:44:28Z

210

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 4, 2019 at 3:44

answered Feb 16, 2016 at 22:53

MikeDub

5,3234 gold badges33 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ttt Over a year ago

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

Shahram Over a year ago

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

ttt Over a year ago

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

Evgeny Nozdrev Over a year ago

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

user2809176 Over a year ago

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.

|

KyleMit · Accepted Answer · 2020-05-19 20:37:07Z

92

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

edited May 19, 2020 at 20:37

KyleMit♦

31.4k74 gold badges517 silver badges712 bronze badges

answered Oct 15, 2019 at 12:34

Mozart

2,3022 gold badges24 silver badges39 bronze badges

9 Comments

Admir Tuzović Over a year ago

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.

Daleman Over a year ago

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

Mozart Over a year ago

@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.

Daleman Over a year ago

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

Mozart Over a year ago

@SteveSmith you can check the article in the link.

|

jishan siddique · Accepted Answer · 2019-06-27 13:12:49Z

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, 2019 at 13:12

jishan siddique

1,8852 gold badges17 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to use SqlClient in ASP.NET Core?

3 Answers 3

7 Comments

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related