1
votes

Requête SQL sur la limitation ADO.net avec plus de 2100 paramètres

J'essaie d'implémenter un code ADO.NET qui exécute la requête SQL avec plusieurs paramètres. Il semble que la limite du paramètre SQL est de 2100 et n'accepte pas plus de cette limite. Comment puis-je obtenir avec mon code ci-dessous pour que cela accepte plus que la limitation.

J'ai du mal à comprendre les implémentations lors de la validation d'articles en ligne sur la façon d'envoyer les requêtes en sous-ensembles ou en blocs pour répondre à ma demande.

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

c# sql ado.net

2 commentaires

Nolock signifie que vous ne vous souciez pas de savoir si le résultat est correct ou non. Vous pouvez obtenir des lignes dupliquées ou manquantes dans le résultat par exemple. En êtes-vous conscient?

@ AntonínLejsek: Non, désolé, je ne suis pas au courant. L'utilisation de (nolock) dans la requête entraîne-t-elle des données manquantes?

3 Réponses :

5
votes

Avoir une clause WHERE IN avec 2100, voire 100, paramètres n'est généralement pas une bonne pratique de codage. Vous pouvez envisager de placer ces valeurs dans un tableau distinct de bonne foi, par exemple

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

Ensuite, vous pouvez réécrire votre requête comme suit:

families (ID int PK, ...)

Vous pouvez également exprimer ce qui précède en utilisant une clause EXISTS ou une jointure, mais les trois approches pourraient de toute façon être optimisées pour un plan de requête très similaire.

4 commentaires

Merci pour votre suggestion Tim! Je garderai cela à l'esprit pour suivre ceci.

Vous devez déplacer ces ID du client au serveur d'une manière ou d'une autre, c'est le problème.

@ AntonínLejsek C'est vrai, mais le faire (potentiellement) une fois sur une table temporaire est beaucoup plus efficace qu'une fois par requête, selon le fonctionnement de votre requête. Et l'insertion d'enregistrements dans une table devrait être quelque chose que la plupart des applications de base de données font de toute façon.

@NetMage comme je le vois, les ID seraient différents pour chaque requête dans une utilisation typique, vous devrez donc créer et remplir la table temporaire pour chaque requête. Oui, c'est quelque chose que vous faites de toute façon. Mais ... si vous allez remplir la table avec RBAR, alors vous feriez mieux de tout jeter et de remplir les données en boucle, un ID à la fois. Ce serait plus rapide et beaucoup plus simple.

1
votes

Vous pouvez simplement ajouter un appel de chargement de table tous les 2000 paramètres dans votre 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);
}

1 commentaires

Impressionnant! J'ai dû faire un changement de code minimum pour cela et merci beaucoup pour votre solution

2
votes

Pour un SQL dynamique comme celui-ci, je recommande généralement d'utiliser un Paramètre table .

Cela nécessite un peu de configuration: vous devez créer un type défini par l'utilisateur dans la base de données pour contenir les valeurs, mais c'est une opération assez triviale:

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

Nous les utilisons généralement en conjonction avec des procédures stockées:

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

Cependant, vous pouvez également utiliser le SQL en ligne si vous préférez.

L'attribution des valeurs au paramètre proc stocké ou au paramètre en ligne est assez simple, mais il y a une erreur (plus tard):

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

La chose à surveiller ici est la dénomination du paramètre. Consultez le code de la méthode ci-dessus qui supprime le nom de la base de données pour résoudre ce problème.

Si vous disposez d'un SQL dynamique, vous pouvez générer un paramètre correct à l'aide de la méthode suivante:

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

2 commentaires

Malheureusement, je ne suis pas censé créer un sproc car il sera exécuté dans l'environnement de production.

Vous n'avez pas besoin de sproc pour utiliser tvp. Ce n'est qu'un paramètre et vous pouvez l'utiliser comme n'importe quel autre paramètre. La seule configuration requise sur le serveur est le type défini par l'utilisateur.