Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

Datenexport mittels einer CLR Assembly

13. Januar 2010 , Geschrieben von Olaf Helper Veröffentlicht in #.NET

Für den aufbereiteten Export von Daten aus dem Microsoft Sql Server gibt es diverse Möglichkeiten und Tools, so bietet bereits nahezu jede Report Engine die Möglichkeit, die Berichtsdaten als Excel oder PDF-Datei zu speichern. Serverbasiert ist der SSIS (MS Sql Server Integration Services) prädestiniert für diese Aufgabe, ebenso der SSRS (MS Sql Server Reporting Service).

Es gibt aber auch regelmäßig mal die Anforderung, einen Export per T-SQL Befehl ausführen zu können. Das Problem ist nur, dass man das mit T-SQL nicht wirklich gut abbilden kann. Zwar kann man mit ActiveX Objekten arbeiten (siehe Stored Procedures sp_OA…), aber das ist alles andere als komfortable.

Danke der .NET Integration und der Verwendbarkeit von CLR Assemblies im MS Sql Server ab Version 2005 geht es nun auch bequemer. Eine solche Lösung habe ich einmal für ein paar Dateiformate umgesetzt. Für alle Exportformate gilt, dass diese erste Version alle Werte einfach per .ToString() ohne weitere Formatierungsangaben oder CultureInfo umwandelt, es werden deswegen die Ländereinstellungen des Servers verwendet.

 

Entwickelt habe ich es mit Microsoft Visual Basic 2008 Express Edition, damit kann auch jeder kostenfrei an die IDE zum Öffnen und Bearbeiten des Projektes kommen. Das Projekt ist mit einer SNK (Strong Name Key) signiert (Batch zum Erstellen liegt bei), ebenso gibt es wie immer ein MS FxCop Datei dazu. Die SQL Scripte zur Anlage der Stored Procedures sowie ein Testscript liegen im Unterordner „Sql“.

Die erste Version 0.0.0.1 des Assemblies ist für mich erst mal nur die grobe Umsetzung (Proof Of Concept), die ich bisher auch nur minimal getestet habe. Zudem will ich noch andere Funktionen hinzufügen wie das Exportieren von BLOB‘s oder FileStream Dateien. Wer will, kann sich das Projekt aber schon ansehen.

Download: OlafHelper.SqlServer.Export (104 KB)

 

Hier noch ein paar Detail-Informationen dazu:

 

Parameter „multiQuery“

Alle Export Prozeduren bieten den Parameter „multiQuery“ an. Wenn der Wert True übergeben wird, werden alle Statements des Batches aus dem Parameter „sqlStatement“ ausgewertet und die Daten exportiert. Bei CSV und HTML stehen die Tabellen in einer Datei untereinander, getrennt durch eine Leerzeile; bei CSV zugegeben nicht wirklich sinnvoll. Bei ExcelXml wird je Ergebnismenge ein weiteres ExcelWorksheet erstellt.

Der wichtigere Unterschied ist aber die interne Ausführung. Wenn multiQuery = True gesetzt wird, wird ein DataSet zum Abrufen der Daten verwendet. Für Client-Anwendungen ist ein DataSet optimal, man baut eine Verbindung zum Server auf, lässt per SqlCommand das DataSet füllen und trennt die Verbindung wieder. Anschließend kann man „verbindungslos“ weiter arbeiten, bis man wieder die Änderungen übertragen will. Zudem unterstützt das DataSet mehrere Resultsets, man kann also ein Sql Batch aus mehreren Statements ausführen und erhält für jedes ein eigenes Table im DataSet. Das bedeutet aber auch, dass die Daten die ganze Zeit im Speicher gehalten werden müssen. Wie gesagt, für einen Client optimal, aber nicht „innerhalb“ des MS Sql Servers, den dann hat man de facto die Daten auf der Maschine doppelt im Speicher und das wo man doch so nahe an der Quelle ist.

Wenn multiQuery = False übergeben wird, wird intern hingegen ein SqlDataReader verwendet, der wie ein Cursor die Datensätze arbeitet und immer nur die Daten für den aktuellen Datensatz abruft. Das spart Hauptspeicher-Platz.

Dieses sollte man bedenken, wenn man multiQuery verwenden will. Für kleine Datenmenge funktioniert es problemlos, bei größeren Datenmengen kann es Speicherplatz-Probleme verursachen => Verwendung auf eigene Gefahr.

 

Format CSV

Der Export erfolgt als einfache Coma Separated Values, die Feld- und Zeilentrennzeichen kann man als Parameter an die SP übergeben.

 

Format HTML

Es wird hier eine einfache HTML Datei erstellt, die je Tabelle ein <table/> verwendet. Es wird keine gesonderte Formatierung angegeben, alle Spalten der Tabelle haben die gleiche Breite.

 

Format ExcelXML

Das neue Excel XML Format (Office Open XML), das mit Office 2007 eingeführt wurde. ist erfreulich einfach aufgebaut im XML Format. Für ältere Office Version bis zurück zu 2000 gibt es das "Compatibility Pack for Open XML", um das Fomat öffnen zu können.

Zwar gibt es mittlerweile ein Open XML SDK 2.0 for Microsoft Office, das steht aber natürlich im MS Sql Server zur Verfügung, deshalb habe ich eine eigene Klasse zum Generieren erstellt.

Im Format gibt ein paar Header Tags, je Worksheet einen weiteren Tag, in dem man die Zeilen mit <Row/> und die Zellen mit <Cell/> angibt. Es wird dabei nicht die Klasse System.Xml.XmlDocument verwendet, da diese wieder komplett im Speicher gehalten wird, sondern System.Xml.XmlWriter. Das ist von der Handhabung her nicht ganz zu bequem, dafür werden die Daten gleich in die Ausgabedatei geschrieben. Um im Fehlerfall alle Resourcen frei zu geben, wird dazu eine eigene Klasse verwendet, die IDisposable implementiert. Schließlich soll auf keinen Fall der Sql Server Dienst in Mitleidenschaft gezogen werden.

 

Format XML

Es wird eine einfache XML Datei mit dem übergeben RootNode angelegt. Die Implementierung ist analog zu ExcelXML.

 

Diesen Post teilen

Repost 0

Kommentiere diesen Post

Erdal Özkan 10/22/2010 13:28


Hallo Olaf,

vielen Dank für Deine Antwort.
Jetzt hat es geklappt.
Wenn ich mit dem Firefox in WindowsLive auf das Zip Icon gehe, und mit der rechten Maustaste "Ziel speichern unter..." klicke, ist das Archiv korrupt. Ich habe es dann mit dem "Herunterladen" Link
erfolgreich entpacken können.
Ich bin mit meiner CLR SP schon relativ weit und bin jetzt gespannt, wie es in Deiner Implementierung ausschaut.

Viele Grüße
Erdal


Olaf Helper 10/23/2010 07:43



Hallo Erdal,


danke für die Info, dann werde ich besser zukünftig nicht direkte Links verwenden, sondern nur bzw. zusätzlich die zur Übersichtseite.


Über konstruktive Kritik und Verbesserungsvorschläge zur Implementierung würde ich mich freuen.



Erdal Özkan 10/22/2010 11:35


Hallo,

bin auch gerade dabei so eine CLR Procedure zu entwickeln.
Wollte mir den Code ansehen, bekomme leider die Meldung, dass das Zip Archiv korrupt sei.
Wäre es möglich mir das Archiv zuzusenden?

Viele Grüße
Erdal Özkan


Olaf Helper 10/22/2010 13:08



Hallo Erdal,


ich habe es gerade selbst ausprobiert, das ZIP konnte ich problemlos herunter laden und entpacken.


Versuch bitte es noch einmal herunter zu laden, Du kannst auch einfach über die Startseite gehen:


http://cid-d974fc8f54c88ba9.office.live.com/browse.aspx/.Public