Parameterized SQL WHERE IN clause c#

If you are using some legacy SQL in C# you’ll occasionally need to write a WHERE IN.
Yon don’t want to end up writing something like the below, either directly or through some sort of loop.

string cmdText = "SELECT * FROM Members WHERE MemberId IN (100, 200, 300, 400);
SqlCommand cmd = new SqlCommand(cmdText);

Instead you should parameterize the sql. This involves two steps.

  1. Add parameters to the sql string.
  2. Add the parameters to SqlCommand.

Add a class to your project like SqlWhereInParamBuilder, I chose to use a static method and an extension method, but you can do it in any number of other ways.

using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace SqlWhereInParameters
{
    public static class SqlWhereInParamBuilder
    {
        public static string BuildWhereInClause<T>(string partialClause, string paramPrefix, IEnumerable<T> parameters)
        {
            string[] parameterNames = parameters.Select(
                (paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString())
                .ToArray();

            string inClause = string.Join(",", parameterNames);
            string whereInClause = string.Format(partialClause.Trim(), inClause);
            
            return whereInClause;
        }

        public static void AddParamsToCommand<T>(this SqlCommand cmd, string paramPrefix, IEnumerable<T> parameters)
        {
            string[] parameterValues = parameters.Select((paramText) => paramText.ToString()).ToArray();

            string[] parameterNames = parameterValues.Select(
                (paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString()
                ).ToArray();

            for (int i = 0; i < parameterNames.Length; i++)
            {
                cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
            }
        }
    }
}

This is the usage

using System.Collections.Generic;
using System.Data.SqlClient;

namespace SqlWhereInParameters
{
    class Program
    {
        static void Main(string[] args)
        {
            Program p = new Program();
            p.RunCommandBulderForFullSelectExample();
        }

        private void RunCommandBulderForFullSelectExample()
        {
            List<long> memberIds = new List<long> { 100, 200, 300, 400 };
            string parameterPrefix = "MemberId";
            string cmdText = "SELECT * FROM Member WHERE Name IN ({0})";

            cmdText = SqlWhereInParamBuilder.BuildWhereInClause(cmdText, parameterPrefix, memberIds);

            SqlCommand cmd = new SqlCommand(cmdText);
            cmd.AddParamsToCommand(parameterPrefix, memberIds);
        }
    }
}

How to fix ‘No database providers are configured’ when scaffolding a controller in ASP.NET 5

If got this error when trying to scaffold a new controller (MVC and Web Api) for an ASP.NET 5 web app using Visual Studio 2015.

There was an error running the selected code generator:
'No database providers are configured. Configure a database provider by overriding OnConfiguring in your DbContext class or in the AddDbContext method when setting up services.'

Scaffolding

MemeberContext was not part of the web application project, instead it was in a referenced class library. This was causing the problem.

To resolve it go to the Startup.cs file.

Inside the ConfigureServices(..) method add something like –

            services.AddEntityFramework()
            .AddSqlServer()
            .AddDbContext<MemberContext>(options =>
                options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));

Of course you need to make sure your Data:DefaultConnection:ConnectionString is pointing to the right place.

The type ‘xxxx’ is defined in an assembly that is not referenced. System.Runtime.

If you recognize the error from the title of this post, you can jump to the solution.

The problem

I have a ASP.NET 5 solution with two projects, a web application project and a class library project.
After adding the class library I was very surprised to get this error –

The type 'IEnumerable<>' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Runtime, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.

All I was doing was querying an Entity Framework context for some simple data and returning the result as an IEnumerable.

This is not the code but is close enough.

var members = _context.Members.Where(m => m.FirstName.Contains(firstName));

To verify that I wasn’t messing up something very simple I tried the same code from the web api controller in the web application, it compiled and worked fine. I looked in the references for both projects for an indication that I had left something out of my class library but could find nothing.

I checked the project.json for both and messed around in nuget for a while but all looked fine.

The project.lock.json files

Finally, I had a look in the project.lock.json files and noticed this section in the web application file.

{
  "locked": false,
  "version": 2,
  "targets": {
    "DNX,Version=v4.5.1": {
      "EntityFramework.Core/7.0.0-rc1-final": {
        "type": "package",
        "dependencies": {
          "Ix-Async": "1.2.5",
          "Microsoft.Extensions.Caching.Abstractions": "1.0.0-rc1-final",
          "Microsoft.Extensions.Caching.Memory": "1.0.0-rc1-final",
          "Microsoft.Extensions.DependencyInjection": "1.0.0-rc1-final",
          "Microsoft.Extensions.DependencyInjection.Abstractions": "1.0.0-rc1-final",
          "Microsoft.Extensions.Logging": "1.0.0-rc1-final",
          "Microsoft.Extensions.Logging.Abstractions": "1.0.0-rc1-final",
          "Microsoft.Extensions.OptionsModel": "1.0.0-rc1-final",
          "Remotion.Linq": "2.0.1",
          "System.Collections.Immutable": "1.1.36"
        },
        "frameworkAssemblies": [
          "Microsoft.CSharp",
          "mscorlib",
          "System",
          "System.Collections",
          "System.ComponentModel.DataAnnotations",
          "System.Core",
          "System.Diagnostics.Debug",
          "System.Diagnostics.Tools",
          "System.Globalization",
          "System.Linq",
          "System.Linq.Expressions",
          "System.Linq.Queryable",
          "System.ObjectModel",
          "System.Reflection",
          "System.Reflection.Extensions",
          "System.Resources.ResourceManager",
          "System.Runtime",
          "System.Runtime.Extensions",
          "System.Threading"
        ],

 

There in the project.lock.json for the web application project is "System.Runtime" in the "frameworkAssemblies" section.

But the same section in the class library’s file did NOT have a "System.Runtime".

{
  "locked": false,
  "version": 2,
  "targets": {
    ".NETFramework,Version=v4.5.1": {
      "EntityFramework.Core/7.0.0-rc1-final": {
        "type": "package",
        "dependencies": {
          "Ix-Async": "1.2.5",
          "Microsoft.Extensions.Caching.Abstractions": "1.0.0-rc1-final",
          "Microsoft.Extensions.Caching.Memory": "1.0.0-rc1-final",
          "Microsoft.Extensions.DependencyInjection": "1.0.0-rc1-final",
          "Microsoft.Extensions.DependencyInjection.Abstractions": "1.0.0-rc1-final",
          "Microsoft.Extensions.Logging": "1.0.0-rc1-final",
          "Microsoft.Extensions.Logging.Abstractions": "1.0.0-rc1-final",
          "Microsoft.Extensions.OptionsModel": "1.0.0-rc1-final",
          "Remotion.Linq": "2.0.1",
          "System.Collections.Immutable": "1.1.36"
        },
        "frameworkAssemblies": [
          "Microsoft.CSharp",
          "mscorlib",
          "System",
          "System.ComponentModel.DataAnnotations",
          "System.Core"
        ],

I added "System.Runtime" into the project.lock.json of class library project and everything compiled and worked.

But, and this is a big but, the project.lock.json file is generated from the project.json file and any changes to nuget or to the porject.json will lead to my project.lock.json being overwritten.

At least I know the culprit and now I had to figure out how to get that entry  for "System.Runtime" to be generated and put into "frameworkAssemblies".

Note also that line 5 for the two files also differs, this I think is more telling problem.

 "DNX,Version=v4.5.1": { 

vs

 ".NETFramework,Version=v4.5.1": { 

Time to compare the project.json files.

The project.json files

This is project.json in the web application project

{
  "version": "1.0.0-*",
  "compilationOptions": {
    "emitEntryPoint": true
  },

    "dependencies": {
        "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
        "Member.Business": "1.0.0-*",
        "Member.DataLayer": "1.0.0-*",
        "Member.Domain": "1.0.0-*",
        "Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
        "Microsoft.AspNet.Mvc": "6.0.0-rc1-final",
        "Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final",
        "Microsoft.AspNet.StaticFiles": "1.0.0-rc1-final",
        "Microsoft.CSharp": "4.0.0",
        "Microsoft.Extensions.Configuration.FileProviderExtensions": "1.0.0-rc1-final",
        "Microsoft.Extensions.Configuration.Json": "1.0.0-rc1-final",
        "Microsoft.Extensions.Logging": "1.0.0-rc1-final",
        "Microsoft.Extensions.Logging.Console": "1.0.0-rc1-final",
        "Microsoft.Extensions.Logging.Debug": "1.0.0-rc1-final"
    },

  "commands": {
    "web": "Microsoft.AspNet.Server.Kestrel"
  },

  "frameworks": {
    "dnx451": { },
    "dnxcore50": { }
  },

  "exclude": [
    "wwwroot",
    "node_modules"
  ],
  "publishExclude": [
    "**.user",
    "**.vspscc"
  ]
}

And this is in the class library project.

{
  "version": "1.0.0-*",
  "description": "Member.Business Class Library",
  "authors": [ "bryan" ],
  "tags": [ "" ],
  "projectUrl": "",
  "licenseUrl": "",
    "frameworks": {
        "net451": { },
        "dotnet5.4": {
            "dependencies": {
                "Microsoft.CSharp": "4.0.1-beta-23516",
                "System.Collections": "4.0.11-beta-23516",
                "System.Linq": "4.0.1-beta-23516",
                "System.Runtime": "4.0.21-beta-23516",
                "System.Threading": "4.0.11-beta-23516"
            }
        }
    },
    "dependencies": {
        "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
        "Member.DataLayer": "1.0.0-*",
        "Member.Domain": "1.0.0-*"
    }
}

There are some serious differences between the two, most notably around the “frameworks” and “dependencies”. I have no idea why the two project.json files are so different.

Solution A and B

I changed my project.json in the class library project to the below adding the "frameworkAssemblies" node.

{
  "version": "1.0.0-*",
  "description": "Fund.FundEntitlement.Business Class Library",
  "authors": [ "bryan" ],
  "tags": [ "" ],
  "projectUrl": "",
  "licenseUrl": "",
    "frameworks": {
        "net451": {
            "frameworkAssemblies": {
                "System.Runtime": "4.0.10.0"
            }
        },
        
        "dotnet5.4": {
            "dependencies": {
                "Microsoft.CSharp": "4.0.1-beta-23516",
                "System.Collections": "4.0.11-beta-23516",
                "System.Linq": "4.0.1-beta-23516",
                "System.Runtime": "4.0.21-beta-23516",
                "System.Threading": "4.0.11-beta-23516"
            }
        }
    },
    "dependencies": {
        "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
        "Fund.FundEntitlement.DataLayer": "1.0.0-*",
        "Fund.FundEntitlement.Domain": "1.0.0-*"
    }
}

An alternative that also works is changing the project.json in the class library to look more like the on from the web application project.

{
    "version": "1.0.0-*",
    "description": "Fund.FundEntitlement.Business Class Library",
    "authors": [ "bryan" ],
    "tags": [ "" ],
    "projectUrl": "",
    "licenseUrl": "",
    "frameworks": {
        "dnx451": { },
        "dnxcore50": {
            "dependencies": {
                "Microsoft.CSharp": "4.0.1-beta-23516"
            }
        }
    },
    "dependencies": {
        "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
        "Fund.FundEntitlement.DataLayer": "1.0.0-*",
        "Fund.FundEntitlement.Domain": "1.0.0-*",
        "System.Collections": "4.0.11-beta-23516",
        "System.Linq": "4.0.1-beta-23516",
        "System.Runtime": "4.0.21-beta-23516",
        "System.Threading": "4.0.11-beta-23516"
    }
}

Now everything compiles and all is good. I’m sadly sure it won’t be the last time I have screw around with project.lock.json and project.json. For more info about those files see http://davidfowl.com/diagnosing-dependency-issues-with-asp-net-5/

ASP.NET 5 Web Api Controller with multiple get methods

I was recently working on a Asp.Net 5 Web.Api application and needed a controller with multiple get methods.
I wanted to have something like GetByAdminId(int adminId) and GetByMemberId(int memberId) (yes I know people will say that you should have two controllers and maybe even two webservices, but that is the scenario I was faced with).

Of course this should not be the most difficult problem, but it was not obvious either.

Here is the solution.

using Microsoft.AspNet.Mvc;

namespace ControllerWithMultipleGetMethods.Controllers
{
    [Route("api/[controller]")] /* this is the default prefix for all routes, see line 20 for overriding it */ 
    public class ValuesController : Controller
    {
        [HttpGet] // this api/Values
        public string Get()
        {
            return string.Format("Get: simple get");
        }

        [Route("GetByAdminId")] /* this route becomes api/[controller]/GetByAdminId */
        public string GetByAdminId([FromQuery] int adminId)
        {
            return $"GetByAdminId: You passed in {adminId}";
        }

        [Route("/someotherapi/[controller]/GetByMemberId")] /* note the / at the start, you need this to override the route at the controller level */
        public string GetByMemberId([FromQuery] int memberId)
        {
            return $"GetByMemberId: You passed in {memberId}";
        }

        [HttpGet]
        [Route("IsFirstNumberBigger")] /* this route becomes api/[controller]/IsFirstNumberBigger */
        public string IsFirstNumberBigger([FromQuery] int firstNum, int secondNum)
        {
            if (firstNum > secondNum)
            {
                return $"{firstNum} is bigger than {secondNum}";
            }
            return $"{firstNum} is NOT bigger than {secondNum}";
        }
    }
}

Adding class libraries to an ASP.NET 5 web application

When adding a class library to an ASP.NET 5 application you might get an error like

“The following projects are not supported as references: – The project ClassLibrary1 has a target framework that is incompatible or has a version higher than the current project and cannot be referenced”

Project reference error

The cause is most likely that when you created the project ClassLibrary1 you selected “Class Library” as the type of project as shown here.

Class Library

Instead, you should have selected “Class Library (Package)”.
Class Library (Package)