Ich lese eine Tabellenkalkulationsdatei über C # ... und meine Tabelle hat mehr als 1000 Zeilen. Ich muss jede Datenzeile an die gespeicherte Prozedur senden, um einige datenbankseitige Logik durchzuführen und Datensätze zu aktualisieren. Ich brauche etwas Hilfe, um all diese 1000 Datenzeilen auf einmal zu senden, um Zeit zu sparen. Welche Technik ist erforderlich, um alle diese 1000 Datenzeilen zu gruppieren?
Wenn Sie davon ausgehen, dass Sie SQL Server 2008 oder besser verwenden, haben Sie einige Optionen. Alle diese Optionen wurden auf der Tech-Ed 2010 in New Orleans ausführlich behandelt, und das Video der Sitzung steht zur Verfügung. online Im Folgenden finden Sie eine Zusammenfassung der angebotenen Optionen.
Option 1 - Masseneinfügung (nicht wirklich in Video )
Dies ist eine großartige Option, wenn Sie nur die Daten in eine Tabelle "ablegen" müssen und Sie nicht viel mit den Daten tun müssen, außer es in die Datenbank zu bekommen. Dies wird auch in ADO.NET mithilfe des Objekts SqlBulkCopy unterstützt. Ich habe auch einen Lightweight-Wrapper geschrieben, den Sie auf CodePlex finden , um das Arbeiten mit SQL Server und ADO.NET zu erleichtern
Option # 2 - Übergeben Sie eine Liste mit Trennzeichen
Nehmen Sie alle Ihre Daten und bauen Sie eine große Zeichenfolge und übergeben Sie die gesamte Zeichenfolge in eine gespeicherte Prozedur. Das ist überraschend schnell, bringt aber viel Gepäck mit sich. Sie müssen eine Split-Funktion haben, um die Daten herauszuholen und die beste Leistung zu erhalten, die Sie für das Teilen mit SQL-CLR benötigen. Dies kann ein Showstopper sein, wenn Sie die Datenbank nicht besitzen.
Option # 3 - Als XML übergeben
Dies ist fast identisch mit Option # 2, da Sie erneut eine riesige Zeichenfolge in einen Parameter eingeben. Dies hat auch eine angemessene Leistung, kommt aber auch mit dem gleichen Gepäck wie Option 2, aber ohne die Split-Funktion, weil Sql Server weiß, wie man XML analysiert.
Option # 4 - Verwenden Sie eine Tabellenwertfunktion (SQL Server 2008)
Diese Option ist sehr cool und bietet die beste Leistung. Sie erstellen zunächst einen Werttyp in SQL Server vom Typ "Tabelle" und erstellen dann eine gespeicherte Prozedur, die diesen Werttyp als Parameter eingibt. In C # können Sie nun einen SqlCommand erstellen und einen Parameter mit dem Typ SqlDbType.Structured hinzufügen.
%Vor%Wenn die gespeicherte Prozedur ausgeführt wird, sind alle Daten in der Tabellenvariablen der gespeicherten Prozedur verfügbar. Es kann wie jede andere Tabellenvariable verwendet werden, sodass das Verschieben der Daten sehr einfach ist.
Option # 5 - Verwenden Sie eine Streaming Tabellenwertfunktion (SQL Server 2008)
Etwas mehr Arbeit als Option # 4, da Sie einen Iterator einrichten müssen, aber Sie bekommen eine verrückte Leistung daraus, weil Sie nicht alle Daten auf dem Client laden müssen, bevor Sie sie in die gespeicherte Prozedur übergeben. Die .NET Runtime streamt die Daten tatsächlich in die Datenbank und die Implementierung der gespeicherten Prozedur ist die gleiche.
%Vor%Alle diese Optionen sind im Video mit detaillierten Details und ein wenig Humor abgedeckt zu Beginn erwähnt. Es war eine meiner Lieblingssitzungen bei Tech-Ed dieses Jahr.
Ryans Antwort ist sehr gründlich und deckt die verschiedenen Optionen ab. Für eine relativ kleine Anzahl von Zeilen (1000-5000 ist ziemlich klein, alle Dinge in Betracht gezogen), würde ich verwenden, was als Option # 3 umrissen ist, übergeben Sie XML als gespeicherte Prozedur Parameter. Wir machen das oft in meinem Shop und der folgende Code ist damit verbunden:
Ich gehe davon aus, dass Ihre Tabellendaten einfach sind und Sie sie in Ihrem Code bereits als eine von Ihnen erstellte Liste oder als DataTable bereithalten. Für dieses einfache Beispiel gehe ich davon aus, dass Ihre Daten der Einfachheit halber eine DataTable sind.
Ich gehe wie Ryan auch von SQL 2008 aus.
1 - Bereiten Sie die Daten in C # vor, indem Sie die Daten in XML umwandeln, das an die gespeicherte Prozedur übergeben wird. Dies ist nur eine Zeichenfolge von XML. Wir verwenden eine Methode in unserer Basisdatenklasse. Sie übergeben Ihre DataTable und es wird es in eine einfache XML-Zeichenfolge konvertieren, die Sie als Parameter an Ihre gespeicherte Prozedur übergeben können.
%Vor%2 - Ich habe ein einfaches DataTable-Beispiel erstellt, das 1000 Datenzeilen enthält, alle Ganzzahlen, 10 Spalten
%Vor%Das Endergebnis der Übergabe der DataTable an ConvertToXMLDataString ist eine schön formatierte XML-Repräsentation der DataTable, die an die gespeicherte Prozedur übergeben und einfach ausgewählt werden kann aus:
%Vor%3 - Erstellen Sie jetzt eine gespeicherte Prozedur, die die übergebene XML-Datenzeichenfolge behandelt.
%Vor%4 - Die Prozedur akzeptiert die XML-Variable von @TableData und fügt sie in eine neu erstellte Tabellenvariable namens @TempTable ein.
Der letzte Schritt besteht nun darin, Ihren Datenbankaufruf mit dem richtigen XML-Parameter zu erstellen. Nennen Sie den SP wie gewohnt, verwenden Sie einfach diesen Parameter.
%Vor%Da hast du es. Sie sollten in der Lage sein, die Daten entsprechend anzupassen. Normalerweise hasse ich das Übergeben von DataTables, würde eher ein Objekt oder eine Liste herumreichen, aber in dieser Situation haben Sie wahrscheinlich Ihre Daten bereits in der DataTable.
Wenn es sich hierbei um ein einmaliges oder gar nicht häufiges Ding handelt, ist die Leistung, die Sie im Austausch für den Komfort der Verwendung von XML erhalten, minimal. Wenn dies häufig von vielen Benutzern geschieht, verwenden Sie den effizienteren Ansatz.