Passing List alla stored procedure SQL

Ho spesso dovuto caricare più elementi su un particolare record nel database. Ad esempio: una pagina Web visualizza elementi da includere per un singolo report, che sono tutti record nel database (Report è un record nella tabella Report, Articoli sono record nella tabella Oggetti). Un utente sta selezionando gli elementi da includere in un singolo report tramite un’app Web e supponiamo che selezionino 3 elementi e invii. Il processo aggiungerà questi 3 elementi a questo report aggiungendo record a una tabella denominata ReportItems (ReportId, ItemId).

Attualmente, vorrei fare qualcosa di simile nel codice:

public void AddItemsToReport(string connStr, int Id, List 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); } 

e questo nella stored procedure:

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

Dove la funzione restituisce una tabella di una colonna di numeri interi.

La mia domanda è questa: c’è un modo migliore per gestire qualcosa di simile? Nota, non sto chiedendo di normalizzare il database o qualcosa del genere, la mia domanda si riferisce specificamente al codice.

Se per SQL Server 2008 è un’opzione, esiste una nuova funzione denominata “Parametri con valori di tabella” per risolvere questo problema esatto.

Scopri ulteriori dettagli su TVP qui e qui o chiedi a Google “Parametri a valore di tabella di SQL Server 2008”: troverai molte informazioni e campioni.

Altamente raccomandato: se è ansible passare a SQL Server 2008 …

La tua logica di join di stringhe può probabilmente essere semplificata:

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

Ciò ti farà risparmiare una concatenazione di stringhe, che è costosa in .Net.

Non penso che ci sia qualcosa di sbagliato nel modo in cui stai salvando gli oggetti. Stai limitando i viaggi al db, che è una buona cosa. Se la struttura dei dati fosse più complessa di un elenco di ints, suggerirei XML.

Nota: nei commenti mi è stato chiesto se questo ci avrebbe salvato qualsiasi concatenazione di stringhe (lo fa indieed). Penso che sia una domanda eccellente e vorrei seguire su questo.

Se apri la stringa aperta. Accedi con Reflector vedrai che Microsoft sta usando un paio di tecniche non sicure (nel senso della parola .Net), incluso l’uso di un puntatore char e una struttura chiamata UnSafeCharBuffer. Quello che stanno facendo, quando lo si riduce veramente, sta usando i puntatori per attraversare una stringa vuota e build il join. Ricorda che il principale motivo per cui la concatenazione di stringhe è così costosa in .Net è che un nuovo object stringa viene posto sull’heap per ogni concatenazione, perché la stringa è immutabile. Quelle operazioni di memoria sono costose. String.Join (..) sta essenzialmente allocando la memoria una volta, quindi operando su di essa con un puntatore. Molto veloce.

Un potenziale problema con la tua tecnica è che non gestisce elenchi molto grandi: potresti superare la lunghezza massima della stringa per il tuo database. Uso un metodo di supporto che concatena i valori interi in un’enumerazione di stringhe, ognuna delle quali è inferiore a un massimo specificato (la seguente implementazione consente anche di verificare e rimuovere gli ID duplicati):

 public static IEnumerable ConcatenateValues(IEnumerable values, string separator, int maxLength, bool skipDuplicates) { IDictionary valueDictionary = null; StringBuilder sb = new StringBuilder(); if (skipDuplicates) { valueDictionary = new Dictionary(); } 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(); } 

Quindi lo usi qualcosa come:

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

Perché non utilizzare un parametro con valori di tabella? http://msdn.microsoft.com/en-us/library/bb675163.aspx

Puoi fare ciò che hai già, passare una stringa delimitata e quindi analizzare ad un valore di tabella, o l’altra scelta sta passando in un wodge di XML e un po ‘lo stesso:

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

Non ho ancora avuto la possibilità di guardare a SQL 2008 per vedere se hanno aggiunto nuove funzionalità per gestire questo tipo di cose.

Vedere http://www.sumrskog.se/arrays-in-sql-2005.html per una discussione dettagliata di questo problema e dei diversi approcci che è ansible utilizzare.

Ecco una spiegazione molto chiara dei parametri di tabella di sqlteam.com: parametri di tabella