Datenzugriff mit ADO.NET und .NET Core

Tabellen als Parameter übergeben

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Mit TVPs, den Table Valued Parameters, können Sie komplette Tabellen an Prozeduren, Funktionen und Ad-hoc-Abfragen übergeben. In ADO.NET Core ist das wegen der fehlenden DataTables gegenüber ADO.NET Classic nurmehr über Umwege möglich.
13:01

Transkript

Eine weitere, extrem interessante Möglichkeit, Parameter zu übergeben, ist es, nicht nur einzelne Werte, sondern komplette Tabellen an Prozeduren oder an Abfragen zu übergeben, sogenannte Table Valued Parameters. Dafür muss ich allerdings ein bisschen weiter ausholen. Letztendlich aber kurz die Folie gezeigt. Das ist leidernicht mehr so einfach mit ADO.NET Core. Ich kann zwar nach wie vor die Technik benutzen, aber hier schmerzt es mich ein bisschen, dass es keine DataTables mehr gibt. Also Sie können eine Stored Procedure nach wie vor definieren. Die bekommt als ein Parameter eine komplette Tabelle respektive natürlich deren Inhalt und ich kann mit der ADO.NET Classic hingehen und sagen ok, ich habe hier im Speicher ein DataTable Objekt, was genau so aufgebaut ist wie entsprechend dieser Parameter, sprich in Anzahl und Datentyp der Spalten, befülle das im Speicher und übergebe das beim Aufruf. Das ist natürlich eine schöne, elegante Sache. Das geht leider nicht mehr. Es gibt aber andere Wege und ich zeige Ihnen jetzt aber erst mal so ein bisschen das Drumherum, was ich benötige, um TVPs zum Laufen zu bekommen. So, und damit sind wir im Visual Studio zurück. Ich zeige Ihnen erst mal, welche Bausteine Sie brauchen, um Table Valued Parameters benutzen zu können. Zunächst einmal brauchen wir einen Table Type. Dieser Table Type wird definiert mit CREATE TYPE, einem Namen AS TABLE und so einer Angabe von Spalten und deren Datentypen. Also hier sind es genau zwei Spalten, einmal Id und Name und einmal der Typ INT und VARCHAR(128). Dann brauche ich in dem Fall eine Stored Procedure. Ich möchte das ja möglicherweise einer Stored Procedure übergeben. Wenn ich natürlich eine Ad-hoc-Abfrage habe, dann brauche ich natürlich jetzt diese Stored Procedure nicht, aber bleiben wir mal kurz bei Stored Procedure. Der übergebe ich diesen Table Type hier an der Stelle, indem ich eigentlich einen ganz normalen Parameter angebe, ich gebe dem einen Namen, ich wähle als Datentyp jetzt allerdings nicht hier INT, VARCHA, BIT oder Ähnliches, sondern genau der Name meines Table Types. und muss entsprechend READONLY hinzufügen. Das bedeutet, innerhalb zur Laufzeit der Stored Procedure, kann der Wert dieses Parameters nicht verändert werden. Er ist Readonly, aber das reicht in den meisten Fällen. Ich kann diese Variabel dann benutzen. Das heißt, der Parameter wird hier bei der Ausführung zu einer Variabel und die kann ich benutzen, wie entsprechend eine Tabelle oder Sicht. Zum Beispiel in dem Fragment, SELECT * FROM @list; und damit kann ich zum Beispiel in den gesamten Ausdruck hingehen und entsprechend die Tabelle befüllen und hier dann entsprechend das als Datenquelle angeben. Die OUTPUT-Klausel an der Stelle hat mit dem Table Type selber nichts zu tun, sondern ermöglicht es mir, dass ich direkt Werte durch das Einfügen aus der Datenbank gleichzeitig wieder auslese, aus der Tabelle auslese. Zum Beispiel kann ich damit sicherstellen, wie die Daten dann wirklich dann heißen auf dem Server, respektive welchen Wert sie haben. Und damit kann ich sichergehen, wenn ich beispielsweise berechnete Spalten habe oder Autowertspalten habe, dass diese und deren Werte direkt auch zurückgeliefert werden. Ähnlich sieht es auch aus bei Funktionen. Auch denen kann ich einen Table Type übergeben. Auch hier nennt sich das dann TVP. Das funktioniert nach den gleichen Regeln. Der Parameter ist entsprechend hier benannt, das ist dann der Datentyp, das ist wieder de Table Type, wieder READONLY ist zwingend und innerhalb der Funktion kann ich das Ganze dann quasi auch dort benutzen, wo ich entsprechend eine Tabelle oder eine Sicht benutze. Im SQL Server Management Studio sieht das ein bisschen anders aus. Es sind zwar die gleichen Objekte, aber die verteilen sich ein bisschen anders. Ich kann hier unter Types, zum einen das Table Type oder den Table Type raussuchen. Wo haben wir den? Da, Table Type. Und kann dann zum Beispiel hingehen, das Ganze scripten lassen, damit ich auf was sehe. Also Sie sehen, es ist vom Inhalt her genau das Gleiche, was Sie auch gerade im Visual Studio gesehen haben. Dann habe ich die Stored Procedures. Die befinden sich hier an der Stelle und das ist exakt genau der gleiche Text. Schließlich ist die Datenbank nach dem Datenbankprojekt aufgebaut, und das Gleiche gilt dann natürlich auch für die Funktionen. So, das ist da eigentlich der Gedanke. Was ich interessanterweise aber machen kann mit Table Types, ich kann die auch wirklich benutzen. Ich könnte selber zum Beispiel Folgendes schreiben. Hier so ich könnte sagen, DECLARE @list So etwas und das funktioniert. Und dann könnte ich sagen, INSERT @list VALUES und ich weiß, ich habe zum Beispiel eine ID und einen Namen, also einen String. Und ich kann das definieren. Das funktioiert auch. Und ich kann da entsprechend dann drauf zugreifen. Oh, Moment, natürlich nicht so, sondern SELECT * from @list; So, das Ganze könnte ich sogar ein Stückchen weiter noch nutzen, um die Stored Procedure auszuführen. Ich könnte einfach sagen, exec dann den Namen der Stored Procedure, und mich jetzt hier noch mal vergewissern, wie auch der Parameter heißt. = @list; Das sieht ein bisschen seltsam aus, ist aber so höchstwahrscheinlich sogar richtig. Das ist der Name des Parameters für die Stored Procedure. Das ist der Name der Variabel. Die heißen jetzt beide gleich, das spielt aber keine Rolle. Und Sie sehen, ich kann das ausführen. Es sind 89 auf den letzten beiden Stellen. Und wenn ich natürlich mal prüfen möchte, ob sich auch wirklich was tut bei der Anzahl, nicht dass das Ganze sich quasi einfach nur verläuft, dann kann ich hingehen und so ein Statement ausführen. Schauen wir mal wieviele wir jetzt hier haben. Es sind 89 auf den letzten beiden Stellen. Und es sollten 91 sein, es sind auch 91. Dass hier übrigens zweimal die Ergebnismenge angezeigt wird, das liegt einmal an dem SELECT hier und zum anderen an der OUTPUT-Klausel in der Stored Procedure, die mir natürlich genau die Werte, so wie ich sie eingefügt habe, an der Stelle auch zurückgibt. Soweit so gut. Wie kann ich das Ganze jetzt benutzen? Ich kann dafür das Management Studio erst mal wieder schließen. Mache hier die unnötigen Fenster zu. Und kann in meinen Programmcode gehen und ich habe damit jetzt die Möglichkeit, genau diese Stored Procedure, so wie ich sie gerade mit TSQL aufgerufen habe, entsprechend auch von ADO.NET aus aufzurufen. Und zwar auf folgende Art und Weise. Ich füge das mal ein. So, und zwar der Kern, ich scrolle mal ein bisschen rein. Zu den Fehlern und dem Code davor kommen wir gleich. Ich muss hingehen und einen Parameter entsprechend definieren. Dieser Parameter bekommt, Sie sehen hier SqlParameter, bekommt erst einmal einen Namen und entsprechend auch gleich einen Inhalt. Den habe ich vorher zusamnengebaut. Wichtig ist, dass ich zusätzlich noch sagen muss, dass der Datenbanktyp dieses Parameters Structured ist. Das heißt, letztendlich sage ich ihm damit, pass auf, das ist kein scalarer Wert, sondern das ist nur ein einzelner Tabellenausdruck quasi, den du bekommst, also ein Table Type an der Stelle. Und dann muss ich ihm aber auch sagen, wie dieser Table Type heißt an der Stelle. Das heißt, das ist im Wesentlichen erst einmal das, was sich groß ändert, wenn ich TVPs verwende. Ich könnte so auch skalare Werte übergeben, das ist aber eher ein bisschen untypisch, vor allem auch ein bisschen unhandlich Und letztendlich würde das reichen. Das ist der Name des Parameter der Stored Procedure, das ist konstant, weil es sich um ein TVP handelt, und das ist der Name des Table Types, der dann dahinter steht. Die Frage ist nur, was ist jetzt mit dem Kandidaten hier? Mit der Variabel TVP? Da muss ich ein bisschen nach oben scrollen und dann sehen Sie unter anderem, dass es hier oben zusammengesetzt wird. Und da es keinen DataTable mehr gibt, das wie gesagt, hat funktioniert bei ADO.NET Classic, muss ich mich eines anderen Tricks bedienen. Und zwar erst einmal, dass ich die Fehler wegbekomme, die entsprechenden Referenzen einbauen, So, wenn ich das habe... Ich kann also ein sogenanntes SQL Data Record Objekt benutzen, und dieses Objekt hat im Wesentlichen eine Auflistung von SQL Meta Data Objekten, die dann wiederum die Spalte und den Datentyp haben, und die kann ich mit Werten befüllen. Quasi baue ich eine Art Tabelle nach an der Stelle, die ich dann als Ganzes letztendlich an den Server schicke. Das heißt, dieser Passus hier ist nur dafür da, um entsprechend das Objekt Zeile für Zeile aufzubauen, also Zeile für Zeile ein Record Objekt aufzubauen, und das dann entsprechend in diese Liste von SQL Data Records entsprechend mit aufzunehmen und dann zum Schluss diese Variabel hier unten als Parameter zu übergeben. Also diesen Weg muss ich machen. Also ich müsste mir hier dann überlegen, wie komme ich an die Werte, die ich entsprechend auf dem Server gerne sehen möchte? Und hier gehe ich jetzt relativ einfach vor, indem ich einfach den Schleifenzähler und den Namen plus den Schleifenzähler verwende. Ich rufe das Ganze mal auf hier oben. So , ich mache mal Dump IDs auf True, scrolle noch mal runter und dieser Parameter hier oben macht eigentlich nichts anderes als entweder die Stored Procedure aufzurufen mit einem Data Reader, was es mir erlaubt, die durch OUTPUT festgelegten Werte, die also zurück an den Client kommen, entsprechend auszugeben, das tut dann diese Schleife hier, oder im Fall, dass ich Dump IDs auf False festgelegt habe, schlicht einfach sage, cmd.ExecuteNonQuery(); und mit einem Aufruf entsprechend alles in die Datenbank einzufügen, ohne dass ich die Daten wirklich zurück auf dem Client sehe. So, ich lasse das Ganze mal laufen. Wohlgemerkt, hier mit einer Millionen Zeilen. Lassen wir ihn mal einen Moment rödeln an der Stelle. Und gucken, wie lange er braucht, bis er dann tatsächlich fertig ist. Sie sehen, oops, jetzt habe ich es weggeklickt. Das ist die Geschwindigkeit an der Stelle, mit der er die Daten dann einfügt in der Datenbank und wie Sie dann entsprechend auch ausgegeben werden. Wir sind schon relativ fix an der Stelle. Ich breche das hier trotzdem mal ab und gehe einfach mal hin und rufe das Ganze anders auf. Ich möchte das Dumpen diesmal unterdrücken. weil es so ein bisschen die Laufzeit natürlich verbessert, wenn ich so und so viele 100.000 Ausgaben in die Konsole schreibe, und... lassen wir ihm mal kurz einen Moment Zeit Fertig ist er schon! Gemessen haben wir die Laufzeit jetzt nicht. Das Projekt, was Sie bekommen, hat noch einen Stop Watch vorne und hinten, um die Laufzeiten zu zeigen. Aber Sie haben gesehen, es ging brutal schnell. Da sind jetzt eine Millionen Zeilen eingeführt worden. und allein gerade bei den Isert Statements, einzelnen Insert Statments, ist uns schon die Geduld bei wenigen 100 Zeilen ausgegangen. Das heißt, das geht richtig fix an der Stelle. Und noch mal zur Kontrolle und dass Sie dem auch Glauben schenken. Hier laufen in der Tat eine Millionen Zeilen in die Datenbank. Das heißt, das ist durchaus ein sehr performante Ansatz, abgesehen davon, dass er natürlch eine gewisse Eleganz hat. Und ich habe damit auch nicht die Notwendigkeit, dass ich möglicherweise irgendwelche Listen von Werten kompliziert mit irgendwelchen Kommata trennen muss und dann in der Stored Procedure wieder aufsplitten und Ähnliches. Ich kann wirklich sehr, sehr logisch arbeiten, indem ich halt hier diese Liste von SQL Data Record Objekten zusammenbaue und dann als Paramenterwert übergebe.

Datenzugriff mit ADO.NET und .NET Core

Lernen Sie, wie mit Ihrer .NET Core-Anwendung auf relationale Datenbanken wie z.B. SQL Server oder SQLite zugreifen.

2 Std. 49 min (28 Videos)
Derzeit sind keine Feedbacks vorhanden...
Software:
Exklusiv für Abo-Kunden
Erscheinungsdatum:12.04.2017

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!