WhereInUtilityLibrary 1.0.0

dotnet add package WhereInUtilityLibrary --version 1.0.0
NuGet\Install-Package WhereInUtilityLibrary -Version 1.0.0
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="WhereInUtilityLibrary" Version="1.0.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add WhereInUtilityLibrary --version 1.0.0
#r "nuget: WhereInUtilityLibrary, 1.0.0"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install WhereInUtilityLibrary as a Cake Addin
#addin nuget:?package=WhereInUtilityLibrary&version=1.0.0

// Install WhereInUtilityLibrary as a Cake Tool
#tool nuget:?package=WhereInUtilityLibrary&version=1.0.0

About

provides methods for writing SQL WHERE IN conditions in C# dynamically for SQL-Server tables using SqlClient data provider. WHERE IN condition are used to assist for an alternative to using OR conditions in a SELECT and DELETE statement are most common.

.NET Frameworks

.NET Core 5/6 and higher, for .NET Framework 4.x, see the non NuGet class project.

Simple example

In this example the goal is to get company names (kept to one column for simplicity) where one or more keys (for the primary key CompId) are passed in.

In the following SQL {0} is important which indicates to the SqlWhereInParamBuilder how to properly inject keys into the query.

SELECT CompanyName FROM dbo.Company WHERE id IN ({0})

Then cmd.AddParamsToCommand("CompId", pIdentifiers); adds parameters to, in this case cmd object.

Note that the following cmd.ActualCommandText() provides you to see the actual query with parameter values, it is not part of this library but is available in the source repository in the project DbLibrary which should only be used in development, never in production.

This code is in the sample project

public static (List<string> list, Exception exception) GetByPrimaryKeys(List<int> pIdentifiers)
{

    var customerList = new List<string>();

    using var cn = new SqlConnection() { ConnectionString = ... };
    using var cmd = new SqlCommand() { Connection = cn };

    // create one parameter for each key in pIdentifiers
    cmd.CommandText = SqlWhereInParamBuilder
        .BuildInClause("SELECT CompanyName FROM dbo.Company WHERE id IN ({0})", "CompId",
            pIdentifiers);

    // populate each parameter with values from pIdentifiers
    cmd.AddParamsToCommand("CompId", pIdentifiers);

    //GetCommandText?.Invoke(cmd.ActualCommandText());

    try
    {
        cn.Open();
        var reader = cmd.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                customerList.Add(reader.GetString(0));
            }
        }

        return (customerList, null);
    }
    catch (Exception ex)
    {
        return (null, ex);
    }
}

Using in your project

For novice developers, clone the source repository, create the database (script is in the root of the solution), build the projects.

Inspect code in SimpleExample then run the project and see the results.

Data providers

The majority of testing was done with SQL-Server with limited testing on Oracle and Microsoft Access. If needed for other databases follow what was done for SQL-Server and create one for your provider. The key is in types as in the class SqlTypeHelper.

See also

Microsoft TechNet

SQL-Server dynamic C#: Dynamic WHERE IN conditions in C#for SQL Server

Product Compatible and additional computed target framework versions.
.NET net5.0 is compatible.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.0.0 213 9/2/2022