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);
    }
}

Nolock means, that you do not care if the result is correct or not. You may get duplicate or missing rows in the result for example. Are you aware of that? — Antonín Lejsek, Jan 05 '19 at 06:30
@AntonínLejsek: No, sorry I am not aware of it. Does using the (nolock) in query result in missing data? — Dhillli4u, Jan 07 '19 at 20:20

score 6 · Answer 1 · answered Jan 04 '19 at 23:57

6

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.

answered Jan 04 '19 at 23:57

Tim Biegeleisen

502,043
27
286
360

Thanks for your suggestion Tim! I will keep this in my mind to follow this. – Dhillli4u Jan 05 '19 at 01:41
You need to move those IDs from client to server somehow, that is the problem. – Antonín Lejsek Jan 05 '19 at 06:10
@AntonínLejsek True, but doing it (potentially) once to a temporary table is much more efficient than once per query, depending on how your querying works. And inserting records into a table should be something most database applications do anyway. – NetMage Jan 07 '19 at 18:09
@NetMage as I see it, the IDs would be different for every query in typical usage, so you would have to build and fill the temp table for every query. Yes, it is something you do anyway. But... if you are going to fill the table with RBAR, then you better throw it all away and just fill the data in a loop, one ID a time. It would be faster and much more simple. – Antonín Lejsek Jan 08 '19 at 02:20

score 2 · Accepted Answer · answered Jan 04 '19 at 23:59

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);
}

Awesome! I had to do minimum code change for this and thank you so much for your solution — Dhillli4u, Jan 05 '19 at 01:38

competent_tech · Answer 3 · 2019-01-05T00:10:29.110

For dynamic sql like this, I generally recommend using a Table-Valued Parameter.

It does require a bit of setup: you have to create a user-defined Type in the DB to hold the values, but that is a fairly trivial operation:

CREATE TYPE PrimaryKeyType AS TABLE ( VALUE INT NOT NULL );

We generally use these in conjunction with stored procedures:

CREATE PROCEDURE dbo.getFamily(@PrimaryKeys PrimaryKeyType READONLY)
AS
SELECT FamilyID, FullName, Alias 
  FROM TABLE (nolock) INNER JOIN @PrimaryKeys ON TABLE.FamilyID = @PrimaryKeys.Value
GO

However, you can also use inline SQL if you prefer.

Assigning the values to the stored proc or inline parameter is fairly straightforward, but there is one gotcha (more later):

    public static void AssignValuesToPKTableTypeParameter(DbParameter parameter, ICollection<int> primaryKeys)
    {
        // Exceptions are handled by the caller

        var sqlParameter = parameter as SqlParameter;
        if (sqlParameter != null && sqlParameter.SqlDbType == SqlDbType.Structured)
        {
            // The type name may look like DatabaseName.dbo.PrimaryKeyType,
            // so remove the database name if it is present
            var parts = sqlParameter.TypeName.Split('.');
            if (parts.Length == 3)
            {
                sqlParameter.TypeName = parts[1] + "." + parts[2];
            }
        }

        if (primaryKeys == null)
        {
            primaryKeys = new List<int>();
        }

        var table = new DataTable();

        table.Columns.Add("Value", typeof(int));

        foreach (var wPrimaryKey in primaryKeys)
        {
            table.Rows.Add(wPrimaryKey);
        }

        parameter.Value = table;
    }

The thing to watch out for here is the naming of the parameter. See the code in the method above that removes the database name to resolve this issue.

If you have dynamic SQL, you can generate a correct parameter using the following method:

    public static SqlParameter CreateTableValuedParameter(string typeName, string parameterName)
    {
        // Exceptions are handled by the caller

        var oParameter = new SqlParameter();

        oParameter.ParameterName = parameterName;
        oParameter.SqlDbType = SqlDbType.Structured;
        oParameter.TypeName = typeName;

        return oParameter;
    }

Where typeName is the name of your type in the DB.

Unfortunately I am not supposed to create a sproc since it's going to be run in the production environment. — Dhillli4u, Jan 07 '19 at 20:25
You do not need sproc to use tvp. It is just a parameter and you can use it as any other parameter. The only setup needed on the server is the user-defined type. — Antonín Lejsek, Jan 08 '19 at 02:25

SQL query on ADO.net limitation with 2100+ parameters

3 Answers3