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

MikeDub MikeDub · Accepted Answer · 2016-02-16T22:53:10

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.

How to use SqlClient in ASP.NET Core?

3 Answers