Um was gehts heute? Ich stand vor kurzem vor der Herausforderung dynamisch, generische SQL Queries für unterschiedliche Such-Abfragen abzusetzen. Was mein ich damit? Ich hab in einer Datenbank SQL-Select-Statements hinterlegt, die ich nun dynamisch ausführen will und die Ergebnismenge in einem WPF ListView anzeigen will.

Wie lösen wir das?
Der Großteil stellt uns vor keine allzugroßen Problemen. Wenn wir das Problem mal in kleine Teilprobleme aufstückeln, kommen wir auf folgende Bereich:

• Dynamische SQL-Select-Statements ausführen;
• Generische Ergebnismenge auslesen und im Speicher sinnvoll ablegen;
• Generische Ergebnismenge anzeigen
• [Das Ganze drum herum, das notwendig ist, um die vorherigen drei Punkte zu lösen]

Dynamische SQL-Select-Statements ausführen
Gehen wir mal davon aus, das wir bereits über ein Repository die notwendigen Daten für unsere SQL Statements aus einer Datenbank gelesen haben und folgendes Model daraus erzeugt haben:

public class SearchScript
{
   public int Id { get; set; }
   public string Name { get; set; }
   public string Description { get; set; }
   public string Sql { get; set; }
   public DateTime CreatedAt { get; set; }
}

Logischerweise liegt in der Sql Property unser SQL-Select-Statement, das wir später ausführen wollen. Um das Skript jetzt auf der Datenbank jetzt abzusezten, haben wir mehrere Möglichkeiten: Entweder setzen wir das SQL Statement direkt über ADO.NET ab, rufen eine Stored Procedure auf, etc. Ich möchte für diese Beispiel den Weg der Stored Procedure gehen, weil ich denke, das es am wartungsfreundlichsten ist, da, möchte man zusätzliche Task vor oder nach dem SQL Statemant durchführen, man nur die Stored Procedure abändern muss und den .NET nicht neu kompilieren braucht.

Die Stored Procedure sieht relative einfach aus:

ALTER PROCEDURE dbo.ExecuteSqlScript
                @script AS nvarchar(4000)
AS

BEGIN
    SET NOCOUNT ON;

    EXECUTE sp_executesql @script
END

Wir übergeben der SP einfach das SQL Statement als String und führen dieses mit der bereits vorhandenen (out of the box) Stored Procedure sp_executesql (http://msdn.microsoft.com/de-de/library/ms188001.aspx)  aus. Ziemlich einfach und logisch. Wollen wir noch weitere Tasks in der SP ausführen, könnten wir diese ohne weiteres jetzt einfach und jederzeit hinzunehmen. Interessant ist auf alle Fälle, das uns die SP ein ResultSet des ausgeführeten SQL-Select-Statements zurückliefert.

Generische Ergebnismenge auslesen und im Speicher sinnvoll ablegen
Um nicht mit untypisierten Listen bzw. zu tiefen Listen- Hierachien arbeiten zu müssen, versuchen wir, aus der generischen Ergebnismenge typisierte Datentypen zu erzeugen. Da gibts einerseits mal das Konzept der anonymen Typen (http://msdn.microsoft.com/de-de/library/bb397696.aspx): Problem dabei ist, dass wir diese nicht dynamisch zur Laufzeit erstellen können. Wir gehen daher einen anderen Weg: Verwendung von System.Reflection.Emit.

Mit Hilfe des Namespaces ist es uns möglich, zur Laufzeit dynamisch Assemblies, Typen, etc. zu generieren. Als erstes müssen wir mal unsere Stored Procedure mithilfe von ADO.NET aufrufen, um auf das ResultSet, das uns die SP liefert, zugreifen zu können:

SqlConnection connection = null;
SqlDataReader reader = null;

connection = new SqlConnection("_ConnectionString_");
connection.Open();

SqlCommand cmd = new SqlCommand("Name_Of_Stored_Procedure", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@script", SqlDbType.NVarChar).Value = "select * from table_name";

reader = cmd.ExecuteReader();

Nachdem wir jetzt unser SqlReader haben, können wir anfangen aufgrund der Ergebnismenge den dynamischen Typ zu erzeugen. Zuerst erzeugen wir uns mal das Assembly und die notwendige Klasse:

AssemblyBuilder ab = AppDomain.CurrentDomain.DefineDynamicAssembly(new AssemblyName("AssemblyName"), AssemblyBuilderAccess.Run);
ModuleBuilder modBuilder = ab.DefineDynamicModule("ModuleName");
TypeBuilder tb = modBuilder.DefineType("ClassName", TypeAttributes.Class | TypeAttributes.Public);

Den zweiten Parameter der Methode DefineDynamicAssembly() setzen wir auf Run, weil wir das dynamische Assembly nicht lokal am Filesystem speichern wollen sondern nur zur Laufzeit benötigen. Als nächstes müssen wir der neuen Klasse die notwendigen Properties zuweisen indem wir alle Felder (Spaltennamen) des SqlReaders auslesen und für jede gelesene Spalte eine öffentliche Property erzeugen:

for (int i = 0; i < reader.FieldCount; i++)
{
   string propertyName = reader.GetName(i);
   Type propertyType = reader.GetFieldType(i);

   FieldBuilder propertyFieldBuilder = tb.DefineField(string.Format("_{0}", propertyName.ToLower()), typeof(string), FieldAttributes.Private);

   PropertyBuilder propertyPropertyBuilder = tb.DefineProperty(propertyName, System.Reflection.PropertyAttributes.None, propertyType, null);

   MethodBuilder propertyGetter = tb.DefineMethod(string.Format("get_{0}", propertyName),
                                                  MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig,
                                                  propertyType, Type.EmptyTypes);

   ILGenerator custNameGetIL = propertyGetter.GetILGenerator();
   custNameGetIL.Emit(OpCodes.Ldarg_0);
   custNameGetIL.Emit(OpCodes.Ldfld, propertyFieldBuilder);
   custNameGetIL.Emit(OpCodes.Ret);

   MethodBuilder propertySetter = tb.DefineMethod(string.Format("set_{0}", propertyName),
                                                  MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig,
                                                  null, new Type[] { propertyType });

   ILGenerator custNameSetIL = propertySetter.GetILGenerator();
   custNameSetIL.Emit(OpCodes.Ldarg_0);
   custNameSetIL.Emit(OpCodes.Ldarg_1);
   custNameSetIL.Emit(OpCodes.Stfld, propertyFieldBuilder);
   custNameSetIL.Emit(OpCodes.Ret);

   propertyPropertyBuilder.SetGetMethod(propertyGetter);
   propertyPropertyBuilder.SetSetMethod(propertySetter);
}

Der Code ist relativ einfach: Wir iterieren über alle Spalten des SqlReaders und erzeugen uns eine Property, die wir auf die Klasse setzen. Als erstes erzeugen wir uns ein privates Feld und eine öffentliche Property. Jetzt brauchen wir für die Property noch Getter und Setter Methoden, die wir ganz am Ende auch dementsprechend auf die Property setzen (SetGetMethod() und SetSetMethod()). Die Parameter der ILGenerator.Emit() Methode sind nicht ganz einfach zu verstehen und können unter http://msdn.microsoft.com/en-us/library/system.reflection.emit.opcodes_members.aspx nachgelesen werden. Haben wir die dynamische Klasse, können wir aus dem TypeBuilder einen Typ bilden, mit dem wir später Instanzen der soeben erzeugten Klasse erzeugen können:

   Type dynamicType = tb.CreateType();

Nachdem wir jetzt unsere dynamische Klasse aufgrund des ResultSets des SQL-Select-Statements erzeugt haben, müssen wir noch Instanzen der dynamischen Klasse erzeugen und mit dem Ergebnis des Select-Statements befüllen:

   List<object> result = new List<object>();

   while (reader.Read())
   {
      object item = Activator.CreateInstance(dynamicType);
      if (item == null) throw new InvalidProgramException("Cannot create instance of dynamic type.");

      for (int i = 0; i < reader.FieldCount; i++)
      {
         PropertyInfo property = scriptType.GetProperty(reader.GetName(i));
         if (property == null || reader.IsDBNull(i)) continue;

         property.SetValue(item, reader.GetValue(i), null);
   }

   result.Add(item);
 }

Der Code erzeugt zunächst eine Instanz der dynamischen Klasse. Anschließend iterieren wir über alle verfügbaren Spalten des SqlReaders und holen uns die Property der dynamischen Klasse, die dem aktuellen Spaltennamen entspricht (da wir zuvor die dynamische Klasse genauso erzeugt haben, sollten eigentlich alle Properties zur Verfügung stehen). Haben wir die Property, dann befüllen wir sie mit dem Wert des aktuellen Datensatzes des SqlReaders. Sobald wir über alle Felder des SqlReaders interiert haben, können wir die Instanz der dynamischen Klasse zu einer Liste hinzufügen.

Generische Ergebnismenge anzeigen
Nachdem wir die gelesenen Daten nun in einem mehr oder weniger typisierten Objekt gespeichert haben, wollen wir die Liste der Resultate auch über WPF visualisieren.  Als erstes verwenden wir eine Instanz von ListView und müssen leider feststellen, das WPF mit dem dynamischen Objekt nicht zurecht kommt (warum ist mir nach wie vor noch nicht ganz klar). Daher erzeugen wir uns unser eigenes CustomControl das wir von ListView ableiten. Als ersten interessieren wir uns für den Loaded-Event, bei dem wir per Code die notwendigen Spalten auf die ListView setzen:

private void _GenerateColumns()
{
   GridView view = new GridView();

   PropertyInfo[] properties = dynamicType.GetProperties();
   if (properties == null || properties.Count() <= 0) return;

   foreach (var property in properties)
   {
      GridViewColumn column = new GridViewColumn();

      column.Header = property.Name;
      column.DisplayMemberBinding = new Binding(property.Name);

      view.Columns.Add(column);
   }

   this.View = view;
}

public CustomListView()
{
   this.Loaded += (sender, e) => _GenerateColumns();
}

Wir lesen einfach alle Properties des dynamischen Typs aus und erzeugen uns für jede Property eine Spalte in unserer ListView, indem wir den Namen der Property als Header/Bezeichnung für die Spalte verwenden und die DisplayMemberBinding-Property setzen (damit die ListView ihr Datenbinding richtig darstellen kann).

Das wars im Großen und Ganzen. Die CustomListView kann jetzt in jeder XAML Datei verwendet werden und (verwendet man MVVM) auf eine Property eines ViewModels “datagebindet” werden. Demoprojekt gibts hier.

In diesem Sinne.