Passer la liste <> à la procédure stockée SQL

Question

J'ai souvent eu à charge de plusieurs éléments à un enregistrement particulier dans la base de données. Par exemple: une page web affiche les éléments à inclure pour un seul rapport, qui sont tous des enregistrements dans la base de données (Rapport est un record dans le Rapport de tableau, les Éléments sont des enregistrements dans le tableau des éléments). Un utilisateur de sélectionner les éléments à inclure dans un rapport unique via une application web, et disons-ils choisir 3 éléments et de se soumettre. Le processus permettra d'ajouter ces 3 éléments de ce rapport par l'ajout d'enregistrements à une table appelée ReportItems (Lang,ItemId).

Actuellement, je voudrais faire quelque chose comme ceci dans le code:

public void AddItemsToReport(string connStr, int Id, List<int> itemList)
{
    Database db = DatabaseFactory.CreateDatabase(connStr);

    string sqlCommand = "AddItemsToReport"
    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

    string items = "";
    foreach (int i in itemList)
        items += string.Format("{0}~", i);

    if (items.Length > 0)
        items = items.Substring(0, items.Length - 1);

    // Add parameters
    db.AddInParameter(dbCommand, "ReportId", DbType.Int32, Id);
    db.AddInParameter(dbCommand, "Items", DbType.String, perms);
    db.ExecuteNonQuery(dbCommand);
}

et ce dans la procédure Stockée:

INSERT INTO ReportItem (ReportId,ItemId)
SELECT  @ReportId,
          Id
FROM     fn_GetIntTableFromList(@Items,'~')

Où la fonction renvoie un tableau de la colonne de nombres entiers.

Ma question est la suivante: est-il une meilleure façon de gérer quelque chose comme cela? Remarque, je ne suis pas de demander à propos de la base de données de normalisation ou quelque chose comme ça, ma question porte plus précisément avec le code.

Answer 1

Si vous allez à SQL Server 2008 est une option pour vous, il y a une nouvelle fonctionnalité appelée "les paramètres de la Table" pour résoudre ce problème exact.

Découvrez plus de détails sur la TVP ici et ici, ou tout simplement demander à Google pour "SQL Server 2008 les paramètres de la table", vous trouverez plein d'info et les échantillons.

Fortement recommandé - si vous pouvez vous déplacer vers SQL Server 2008...

Answer 2

Votre chaîne de rejoindre la logique peut probablement être simplifiée:

string items = 
    string.Join("~", itemList.Select(item=>item.ToString()).ToArray());

Cela vous fera économiser de concaténation de chaîne, ce qui est coûteux .Net.

Je ne pense pas que quelque chose est incorrect avec la façon dont vous enregistrez les éléments. Vous êtes à la limitation des voyages à la db, ce qui est une bonne chose. Si votre structure de données est plus complexe qu'une liste d'entiers, je dirais XML.

Remarque: on m'a demandé dans les commentaires si cela nous ferait gagner du tout de concaténation de chaîne (il ne indeeed). Je pense que c'est une excellente question et souhaitez suivre.

Si vous ouvrez l'emballage de la chaîne.Joindre avec Réflecteur , vous verrez que Microsoft est à l'aide d'un couple de dangereux (dans le .Net les sens du terme) les techniques, y compris à l'aide d'un pointeur de char et une structure appelée UnSafeCharBuffer. Ce qu'ils font, quand vous en avez vraiment bouillir vers le bas, en utilisant les pointeurs de marcher à travers une chaîne vide et de construire la rejoindre. Rappelez-vous que la principale raison de concaténation de chaîne est si cher .Net est un nouvel objet string est placé sur le tas pour chaque concaténation, parce que la chaîne est immuable. Ces opérations de mémoire sont chers. Chaîne de caractères.Join(..) est essentiellement de l'allocation de la mémoire une fois, puis de l'exploitation sur elle avec un pointeur. Très rapide.

Answer 3

Un problème potentiel avec votre technique est qu’elle ne gère pas de très grandes listes - vous pouvez dépasser la longueur de chaîne maximale pour votre base de données. J'utilise une méthode d'assistance qui concatène les valeurs entières en une énumération de chaînes, chacune étant inférieure à un maximum spécifié (l'implémentation suivante vérifie également et supprime éventuellement les identificateurs en double):

 public static IEnumerable<string> ConcatenateValues(IEnumerable<int> values, string separator, int maxLength, bool skipDuplicates)
{
    IDictionary<int, string> valueDictionary = null;
    StringBuilder sb = new StringBuilder();
    if (skipDuplicates)
    {
        valueDictionary = new Dictionary<int, string>();
    }
    foreach (int value in values)
    {
        if (skipDuplicates)
        {
            if (valueDictionary.ContainsKey(value)) continue;
            valueDictionary.Add(value, "");
        }
        string s = value.ToString(CultureInfo.InvariantCulture);
        if ((sb.Length + separator.Length + s.Length) > maxLength)
        {
            // Max length reached, yield the result and start again
            if (sb.Length > 0) yield return sb.ToString();
            sb.Length = 0;
        }
        if (sb.Length > 0) sb.Append(separator);
        sb.Append(s);
    }
    // Yield whatever's left over
    if (sb.Length > 0) yield return sb.ToString();
}
 

Ensuite, vous l'utilisez quelque chose comme:

 using(SqlCommand command = ...)
{
    command.Connection = ...;
    command.Transaction = ...; // if in a transaction
    SqlParameter parameter = command.Parameters.Add("@Items", ...);
    foreach(string itemList in ConcatenateValues(values, "~", 8000, false))
    {
        parameter.Value = itemList;
        command.ExecuteNonQuery();
    }
}
 

Answer 4

Soit vous faites ce que vous avez déjà, de passer dans une chaîne délimitée et ensuite d'analyser un tableau de la valeur, ou l'autre choix est de passage dans un wodge de XML et d'un peu près les mêmes:

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

Je n'ai pas eu la chance de regarder SQL 2008 encore pour voir si ils ont ajouté de nouvelles fonctionnalités pour gérer ce genre de chose.

Answer 5

Pourquoi ne pas utiliser un paramètre table? http://msdn.microsoft.com/en-us/library/bb675163.aspx