Adding New Database Reports to .NET Client Apps Without Re-Deployment - An Odyssey Through Dynamic LINQ and Reflection Hackery

• Download demo - 63.2 KB

Introduction: What am I trying to achieve?

The challenge is to develop an extensible reporting architecture with a WPF, Silverlight or similar client technology and a web service, in such a way as to be able to add new database report (e.g. SQL Server stored procedure) to the system without needing to redeploy the client or web service.

Disclaimer: this was tested with WPF & .NET 3.5. I'm hoping the principle will carry into other frameworks. I'd be interested to hear if anyone has any ideas for alternative approaches—if so, please leave a comment! This article & example is intended to demonstrate a proof of concept rather than provide a fully defensible and production-ready codebase. Adopt at your own risk.

Contents

• Background
  • Why do we normally have to re-deploy?
  • The ideal web service API
  • The problems
• Solution(s)
  • A general return type
  • Calling a stored procedure dynamically with LINQ
  • Including report meta data
  • Our general web service operation
  • How the client calls the web service
  • What comes back from the web service
  • The Dynamic Report Interpreter
  • Generating the source code
  • Compiling the dynamic class and getting the type
  • When is a list not a list?
  • Instantiating and populating instances of our dynamic type
  • Un-squashing the flat object list into an array
  • Afterthoughts
• Instructions
  • How to try the demo
• History

Background

Why do we normally have to re-deploy?

Your basic architecture may look something like this:

Database Stored Procedures -> LINQ DBML -> Web Service -> Client

When a new stored procedure is created the following steps need to occur in order for the client to be able to call it:

1. Add procedure to DBML file
2. Add web service method
3. Rebuild & redeploy web service
4. Update service reference in client
5. Add code to client to call new procedure
6. Rebuild & redeploy client

We can't just use the same web service method for all the stored procedures because they have:

• Different signatures (parameter types and count)
• Different return types (field types and count)

The Ideal Web Service API

Ideally we'd have a single web service method for a report, and maybe one to tell us what reports are available. Something like:

GetListOfAvailableReports();
GetReport(int reportId, object[] parameters);

Then if new reports were added later, we'd discover them and simply call them via an ID number.

The Problems

When you consider what the return type of GetReport should be then you can begin to see problems:

• How to make the return types indentical and serialisable (procedure types and web service operation types)
• How to call a procedure that's not declared in your DBML file without recompiling it
• What about type mistmatches between SQL Server & C#
• Using Analysing & instantiating the returned data

We'll tackle them one at a time...

Solution(s)

A general return type

XML is a natural candidate for both data transmission and definition. Rather than using a normal 'MyProcedureResult' style type usually defined by the LINQ DBML, we adjust the stored procedures' SQL with a simple wrapper which turns the result into XML with a common top top two levels.

Instead of a normal query:

SELECT Name,
	   Email,
	   Age
  FROM Person

We write wrap it in a general wrapper like this:

Header

DECLARE @XmlData XML    
    SET @XmlData = (

Query

SELECT Name,
	   Email,
	   Age
  FROM Person

Footer

FOR XML
  PATH ('Record'), ROOT('Records')
	   );

The parts labelled Header and Footer are always the same for every stored procedure, and we just change the original query in the middle to suit our needs. Simple!

This gives us XML in the following form:

<Records>
	<Record>
		<SomeSortOfColumn1>DataValue</SomeSortOfColumn1>
		<SomeSortOfColumn2>DataValue</SomeSortOfColumn2>
		<SomeSortOfColumn3>DataValue</SomeSortOfColumn3>
		<!--...etc. (this inner bit is specific to the procedure)-->
	</Record>
	<Record>
		<SomeSortOfColumn1>DataValue</SomeSortOfColumn1>
		<SomeSortOfColumn2>DataValue</SomeSortOfColumn2>
		<SomeSortOfColumn3>DataValue</SomeSortOfColumn3>
		<!--...etc. (this inner bit is specific to the procedure)-->
	</Record>
	<!--...etc. (repeats for each row)-->
</Records>

That's our report data wrapped up into a single object with an identical return type (XML). We can now move this around easily, and return several different reports from the same method.

Calling a Stored Procedure Dynamically with LINQ

LINQ offers a method called ExecuteQuery from a data context which allows you to construct a query as a string and execute it. It does require that you know the return type, but as this is just going to be some XML we can create a simple class containing the XML results to be this type, like so:

[DataContract]
public class DynamicReport
{
    [DataMember]
    public string XmlData { get; set; }
}

Note that the field name XmlData matches the SQL header from earlier:

DECLARE @XmlData XML

So we can now call this procedure dynamically with:

DynamicReport report = db.ExecuteQuery<DynamicReport>(queryString, providedParameters).FirstOrDefault();

The FirstOrDefault() is used because in our scenario we're only ever using procedures returning a single result. If you're doing something more complex you'll need to take that into account.

Including Report Meta Data

We could optionally add to our DynamicReport class some more information about the parameters, types, UI-friendly column names (including spaces, etc.) and even column formatting strings. On the UI side this will allow you to dynamically configure components such as data grids with appropriately presented headings and values, all defined in the database rather than hard-coded into the UI. Here, I add some metadata to the DynamicReport class, described in a database table (note I specify the C# type as a TypeCode, which is described in a related table):

[DataContract]
public class DynamicReport
{
    [DataMember]
    public string XmlData { get; set; }

    [DataMember]
    public List<ReportColumn> Columns { get; set; }
}

 

CREATE TABLE [Reporting].[ReportColumn]
(
	[ReportColumnId] [int] IDENTITY(1,1) NOT NULL,
	[ReportId] [int] NOT NULL,
	[ColumnOrder] [int] NOT NULL,
	[TypeCodeId] [int] NOT NULL DEFAULT ((1)),
	[ProcedureColumnName] [varchar](250) NOT NULL,
	[ColumnFullName] [varchar](max) NULL,
	[ColumnFormatString] [varchar](max) NULL ,
	[IsChartDataPointX] [bit] NOT NULL DEFAULT ((0)),
	[IsChartDataPointY] [bit] NOT NULL DEFAULT ((0)),
	[IsChartDataPointZ] [bit] NOT NULL DEFAULT ((0)),
	[Created] [datetime] NOT NULL DEFAULT (getdate()),
	[Changed] [datetime] NOT NULL DEFAULT (getdate()),
	[Creator] [varchar](250) NOT NULL DEFAULT ('SYS'),
	[Changer] [varchar](250) NOT NULL DEFAULT ('SYS'),
	PRIMARY KEY [ReportColumnId]
 )

Here are the related tables as found in the demo:

Our general web service operation

Thanks to the identical types of every report, we can call them all using the same web service method:

[OperationContract]
public DynamicReport DynamicReport(int reportId, object[] parameters)
{
    return DynamicReportLogic.DynamicReport(reportId, parameters);
}

How the Client Calls the Web Service

Here I throw some arbitrary parameters at the report with an ID of 2. Note their different types, string and int, which are then covaried into a List of Objects. We can mix and match parameter types according to the needs of the report. I've hardcoded them below for clarity:

void Button_Click(object sender, RoutedEventArgs e)
{
    client.DynamicReportCompleted += new EventHandler<DynamicReportCompletedEventArgs>(client_DynamicReportCompleted);
    client.DynamicReportAsync(2, new List<object> { "1", "x2", 3 });
}

What Comes Back from the Web Service

Here we hit yet another problem, although perhaps one you've seen coming all along. Let's say we have successfully received that big bundle of XML data. How do we actually use it, if we don't know its true structure?

We packaged up the some metadata into our DynamicReport object. In particular, the stored procedure column (field) names and their types. We'll write a class to handle interpreting this data and turning it into a List of Objects, which we can then "throw" straight into a DataGrid or similar user control. Here's how we want the code in our client to look:

void client_DynamicReportCompleted(object sender, DynamicReportCompletedEventArgs e)
{
    if (e.Error != null)
    {
        MessageBox.Show(e.Error.Message.ToString());
        return;
    }
    dataGrid.ItemsSource = DynamicReportInterpreter.InterpretRecords(e.Result);
}

Of course we could put the data into a Property for MVVM goodness.

The Dynamic Report Interpreter

This class will take the dynamic report, "do magic here", and then return an array of instantiated report records. The type of these records is only known at runtime, therefore the "do magic here" means we have to generate some source code for this class, instantiate it and populate the values.

Generating the Source Code

Although the code is pretty ugly, this simply creates a big string of the class similar to how I might write it if I looked at the fields it contained. It has public properties, which have corresponding private instance variables. Although I don't normally like the convention, I deliberately chose to prefix private instance variables with an underscore, as this gave an easier means of distinguishing it in string from parameters than coding "this." or some sort of case-changing function. Perhaps most importantly, it has an "Init" method which takes in each of the fields as a value and assigns them. This was because I found it was easier to dynamically create a class and set the data afterwards than doing it in one go. 

const string ClassName = "DynamicRecord";
const string InitMethodName = "Init";

//...

static string GenerateClassSourceCode(
    DynamicReport dynamicReport, 
    List<ReportColumn> columns, 
    List<XElement> fields)
{
    // Generate code for class wrapper
    string classHeaderCode = "using System;" + newLine + newLine + "public class " + ClassName + newLine + "{" + newLine;
    string classFooterCode = "}";

    // Generate code for the variables, properties & Init method by looking at descendants of first record
    string variableTemplate = "\tprivate {0} _{1};{2}" + newLine;
    string propertyTemplate = "\tpublic {0} {1} {{ get {{return _{1};}} set {{ _{1} = value;}} }}" + newLine + newLine;
    string initTemplate = "\tpublic void {0} ({1})" + newLine + "\t{{" + newLine + "{2}" + newLine + "\t}}" + newLine;
    StringBuilder variableLines = new StringBuilder();
    StringBuilder propertyLines = new StringBuilder();
    StringBuilder initSetterLines = new StringBuilder();
    for (int i = 0; i < columns.Count; i++)
    {
        bool isLastColumn = i == columns.Count - 1;
        string initDelimiter = isLastColumn ? string.Empty : newLine;
        string fieldDelimiter = isLastColumn ? newLine : string.Empty;
        string fieldName = fields[i].Name.LocalName;
        string typeName = TypeUtil.ToSourceCodeName((TypeCode)columns[i].TypeCodeId);
        variableLines.Append(String.Format(variableTemplate, typeName, fieldName, fieldDelimiter));
        propertyLines.Append(String.Format(propertyTemplate, typeName, fieldName));
        initSetterLines.Append(String.Format("\t\tthis._{0} = ({1})args[{2}];{3}", fieldName, typeName, i, initDelimiter));
    }

    // Assemble complete source code
    string variableCode = variableLines.ToString();
    string propertyCode = propertyLines.ToString();
    string initMethodCode = String.Format(initTemplate, InitMethodName, "object[] args", initSetterLines.ToString());
    return classHeaderCode + variableCode + propertyCode + initMethodCode + classFooterCode;
}

Compiling the Dynamic Class and Getting the Type

With the source code generated, the next step is to turn it into a class at run time and read its type, something like this:

// Compile & read dynamic type
Assembly assembly = CompilationUtil.Compile(classSourceCode);
Type dynamicType = assembly.GetType(ClassName);

With credit going to Matthew Watson's comment for the basis of this, here's how we can compile the source code:

public static class CompilationUtil
{
    public static Assembly Compile(string sourceCode)
    {
        CompilerResults compilerResults = CompileScript(sourceCode);
        if (compilerResults.Errors.HasErrors)
        {
            throw new InvalidOperationException("Expression has a syntax error.");
        }
        return compilerResults.CompiledAssembly;
    }

    public static CompilerResults CompileScript(string source)
    {
        CompilerParameters parameters = new CompilerParameters
        {
            GenerateExecutable = false,
            GenerateInMemory = true,
            IncludeDebugInformation = false
        };
        CodeDomProvider compiler = CSharpCodeProvider.CreateProvider("CSharp");
        return compiler.CompileAssemblyFromSource(parameters, source);
    }
}

When is a list not a list?

When it's an object. No, wait—

Having come this far, some painful subtleties of objects, types, covariance and generics started beating me around the head. How could I create a List<T> if I didn't know what T was? How could I create an array of ???[]? Even if I create it, how do I refer to it? How do I call it's Add method?

Thankfully Anoop Madhusudanan's post had the answer. For now, just taking the idea of creating the generic list of a dynamic type problem, I create it as a flat object like this:

// Create a list, stored/formed as a flat object so we can keep the element type dynamic
object listDynamicRecords = CovarianceUtil.CreateGenericList(dynamicType);

//...

public static class CovarianceUtil
{
    public static object CreateGenericList(Type typeX)
    {
        Type listType = typeof(List<>);
        Type[] typeArgs = { typeX };
        Type genericType = listType.MakeGenericType(typeArgs);
        return Activator.CreateInstance(genericType);
    }
}

Instantiating and Populating Instances of our Dynamic Type

We have a list to put things into, so now we want to scan our XML data and create records with it. Note the use of MethodInfo.

// Note: these match the values names in the SQL footer
const string RecordCollectionIdentifier = "Records";
const string RecordIdentifier = "Record";

//...

// Read records into list
foreach (var record in xDoc.Descendants(RecordCollectionIdentifier).First().Descendants(RecordIdentifier))
{
    // Read this record
    List<object> listRecordArguments = new List<object>();
    var recordFields = record.Descendants();
    for (int i = 0; i < columns.Count; i++)
    {
        // Get field
        var recordField = recordFields.Where(q => q.Name == fields[i].Name.LocalName).Single();

        // Change value from raw string to real type, then covary into an object
        TypeCode typeCode = (TypeCode)columns[i].TypeCodeId;
        object argument;
        if (typeCode == TypeCode.Boolean)
        {
            argument = Convert.ChangeType(recordField.Value == "0" ? "False" : "True", typeCode);
        }
        else
        {
            argument = Convert.ChangeType(recordField.Value, typeCode);
        }
        listRecordArguments.Add(argument);
    }

    // Instantiate the dynamic class & populate its data with the Init Method
    object instance = Activator.CreateInstance(dynamicType, null);
    object[] args = listRecordArguments.ToArray();
    MethodInfo initMethodInfo = dynamicType.GetMethod(InitMethodName);
    initMethodInfo.Invoke(instance, new object[] { args });

    // Add the instance to the list of objects (instead of listDynamicRecords.Add(instance))
    MethodInfo addMethodInfo = listDynamicRecords.GetType().GetMethod("Add");
    addMethodInfo.Invoke(listDynamicRecords, new object[] { instance });
}

Un-Squashing the Flat Object List into an Array

Finally we use the same MethodInfo approach to .ToArray the plain object list of records. This creates an Array we can return and use in the client.

// Invoke the ToArray mothod of our generic List
MethodInfo toArrayMethodInfo = listDynamicRecords.GetType().GetMethod("ToArray");
Array itemSourceArray = (Array)toArrayMethodInfo.Invoke(listDynamicRecords, null);

Afterthoughts

There we have it. An end-to end demonstration of turning an unknown stored procedure the other side of a web service into a usable array. 

To take this further we could also interpret the list of ReportColumn data in order to construct a datagrid with custom headings, apply a formatting string or even automatically determine that it's a chart and map the appropriate columns, but that gets a bit more specific to a UI framework. Hopefully you won't have too much trouble using this for your own needs.

The complete source code is include, including scripts for the database. 

Instructions 

How to Try the Demo

1. Set up a SQL Server 2008 database using the SQL script 01 - Create database.sql
2. Load the Dynamic Reports project in Visual Studio
3. Modify the web config as needed to allow the web service to access the newly created database
4. Execute the WPF application and run a couple of reports
5. Whilst leaving the application running, execute the second SQL script 02 - Add new report.sql
6. Return to your running application and click "Fetch"—the new report should now appear
7. Select it and populate the parameters
8. Run the new report

Here's the demo application discovering & executing a new stored procedure at runtime:

History

• 2012/05/31: First draft
• 2012/05/31: Fixed typo in article description & incorrect image link
• 2012/06/01: Minor body text changes 
• 2012/06/08: Updated source code, fixed bug with parameterless reports (see comments)