SQL query on ADO.net limitation with 2100+ parameters

Question

I am trying to implement an ADO.NET code which executes the SQL query with multiple parameters. Looks like SQL parameter limit is 2100 and does not accept more than this limit. How do I achieve with my below code to have this accept more than the limitation.

I am finding it difficult to understand the implementations when validating online articles related how to send the queries in subsets or chunks to fulfill my request.

This is my code:

using (Connection = new SqlConnection(CS))
{
    Connection.Open();

    string query = "SELECT FamilyID, FullName, Alias FROM TABLE (nolock) WHERE FamilyID IN ({0})";

    var stringBuiler = new StringBuilder();
    var familyIds = new List<string>();

    string line;

    while ((line = TextFileReader.ReadLine()) != null)
    {
        line = line.Trim();

        if (!familyIds.Contains(line) & !string.IsNullOrEmpty(line))
        {
            familyIds.Add(line);
        }
    }

    var sqlCommand = new SqlCommand
    {
        Connection = Connection,
        CommandType = CommandType.Text
    };

    var index = 0; // Reset the index
    var idParameterList = new List<string>();

    foreach (var familyId in familyIds)
    {
        var paramName = "@familyId" + index;
        sqlCommand.Parameters.AddWithValue(paramName, familyId);
        idParameterList.Add(paramName);
        index++;
    }

    sqlCommand.CommandText = String.Format(query, string.Join(",", idParameterList));

    var dt = new DataTable();

    using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
    {
        dt.Load(sqlReader);
    }

    try
    {
        if (dt.Rows.Count > 0)
        {
            OutputdataGridView.DataSource = lstDownloadOwnerOutput;
            OutputdataGridView.ColumnHeadersDefaultCellStyle.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
            OutputdataGridView.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            Gridviewdisplaylabel.Text = "Total no of rows: " + this.OutputdataGridView.Rows.Count.ToString();
        }
        else if (dt.Rows.Count == 0)
        {
            MessageBox.Show("Data returned blank!!!");
        }
    }
    catch (Exception Ex)
    {
        if (Connection != null)
        {
            Connection.Close();
        }
        MessageBox.Show(Ex.Message);
    }
}

Answer 1

Having a WHERE IN clause with 2100, or even 100, parameters is generally not good coding practice. You might want to consider putting those values into a separate bona fide table, e.g.

families (ID int PK, ...)

Then, you may rewrite your query as:

SELECT FamilyID, FullName, Alias
FROM TABLE (nolock)
WHERE FamilyID IN (SELECT ID FROM families);

You could also express the above using an EXISTS clause or a join, but all three approaches might just optimize to a very similar query plan anyway.

Answer 2

You can just add a table load call every 2000 parameters in your code:

var index = 0; // Reset the index
var idParameterList = new List<string>();
var dt = new DataTable();

foreach (var familyId in familyIds) {
    var paramName = "@familyId" + index;
    sqlCommand.Parameters.AddWithValue(paramName, familyId);
    idParameterList.Add(paramName);
    index++;
    if (index > 2000) {
        sqlCommand.CommandText = String.Format(query, string.Join(",", idParameterList));

        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
            dt.Load(sqlReader);

        sqlCommand.Parameters.Clear();
        idParameterList.Clear();
        index = 0;
    }
}
if (index > 0) {
    sqlCommand.CommandText = String.Format(query, string.Join(",", idParameterList));

    using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
        dt.Load(sqlReader);
}