Pass unknown number of values to select statement

Question

I have a department table and I want to select multiple checkboxes under department, then show it in a datagrid. When I select one option it works, but how to pass multi values? As shown in queryBuilder image WHERE userGroup=?.

Here I want to pass multi values depending on the checkboxes:

Answer 1

Try the in clause: https://www.w3schools.com/sql/sql_in.asp

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

In your case

WHERE userGroup in (?,?, ... ,?)

It's a bit more tricky as you'll need to create the sql statement every time by code, depending on the items in user group.

Don't forget to keep on using parameters to keep all that sweet sanitanization.

Check here on how to do it with code: Building SQL "where in" statement from list of strings in one line?

Answer 2

Using STRING_SPLIT

@values = 'v1,v2,...'

SELECT column_name(s)
FROM table_name
WHERE userGroup IN (SELECT value FROM STRING_SPLIT(@values, ','))

Answer 3

An option is to build the WHERE IN condition on the fly with actual parameters rather than passing text. The code presented below works with SQL-Server, there is also code available in the repository for working with OleDb providers.

Note Coded with C#9, .NET Core 5. If using a lower framework e.g. 4.8 then you need to modify the using declarations in DataOperations class.

Full code can be found in the following GitHub repository.

Base code located in a class project to create the WHERE IN with parameters

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

namespace SqlCoreUtilityLibrary.Classes
{
    public static class SqlWhereInParamBuilder
    {
        /// <summary>
        /// Creates parameters for IN of the WHERE clause
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="partialClause">SELECT partly built up to WHERE IN ({0})</param>
        /// <param name="pPrefix">Prefix for parameter names</param>
        /// <param name="parameters">Parameter values</param>
        /// <returns></returns>
        public static string BuildInClause<T>(string partialClause, string pPrefix, IEnumerable<T> parameters)
        {
            string[] parameterNames = parameters.Select((paramText, paramNumber) 
                => $"@{pPrefix}{paramNumber}").ToArray();

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

            return whereInClause;

        }
        /// <summary>
        /// Populate parameter values
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd">Valid command object</param>
        /// <param name="pPrefix">Prefix for parameter names</param>
        /// <param name="parameters">Parameter values</param>
        public static void AddParamsToCommand<T>(this SqlCommand cmd, string pPrefix, IEnumerable<T> parameters)
        {
            var parameterValues = parameters.Select((paramText) => 
                paramText.ToString()).ToArray();

            var parameterNames = parameterValues.
                Select((paramText, paramNumber) 
                    => $"@{pPrefix}{paramNumber}").ToArray();

             
            for (int index = 0; index < parameterNames.Length; index++)
            {
                cmd.Parameters.Add(new SqlParameter()
                {
                    ParameterName = parameterNames[index],
                    SqlDbType = SqlTypeHelper.GetDatabaseType(typeof(T)),
                    Value = parameterValues[index]
                });
            }
        }
    }
}

Form code which reads, in this case all companies from a SQL-Server database table into a CheckedListBox. Using a button, get keys for each checked company.

The SELECT statement is shown in a label and the results are shown in a listbox.

Form code

using System;
using System.Linq;
using System.Windows.Forms;
using SimpleExamples.Classes;
using SimpleExamples.Extensions;

namespace SimpleExamples
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            Shown += OnShown;

            DataOperations.GetCommandText += ReceiveQuery;
        }

        /// <summary>
        /// Show decoded SQL SELECT
        /// </summary>
        /// <param name="sender"></param>
        private void ReceiveQuery(string sender)
        {
            label1.Text = sender;
        }

        private void OnShown(object sender, EventArgs e)
        {
            CompanyCheckedListBox.DataSource = DataOperations.GetCompanies();
        }

        private void GetSelectedButton_Click(object sender, EventArgs e)
        {
            CompanyListBox.DataSource = null;

            var list = CompanyCheckedListBox.CheckedList();

            if (list.Count <= 0) return;
            var indices = list.Select(company => company.Id).ToList();

            var (companies, exception) = DataOperations.GetByPrimaryKeys(indices);
            if (exception is null)
            {
                CompanyListBox.DataSource = companies;
            }
            else
            {
                MessageBox.Show(exception.Message);
            }
        }
    }
}

Language extension to get checked companies from the CheckedListBox

public static class GenericExtensions
{
    public static List<Company> CheckedList(this CheckedListBox source)
        => source.Items.Cast<Company>()
            .Where((item, index) => source.GetItemChecked(index)).Select(item => item)
            .ToList();

}

Company class

public class Company
{
    public int Id { get; set; }
    public string Name { get; set; }
    public override string ToString() => Name;

}