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
- 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:
- Add procedure to DBML file
- Add web service method
- Rebuild & redeploy web service
- Update service reference in client
- Add code to client to call new procedure
- 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)
{
string classHeaderCode = "using System;" + newLine + newLine + "public class " + ClassName + newLine + "{" + newLine;
string classFooterCode = "}";
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));
}
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:
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:
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
.
const string RecordCollectionIdentifier = "Records";
const string RecordIdentifier = "Record";
foreach (var record in xDoc.Descendants(RecordCollectionIdentifier).First().Descendants(RecordIdentifier))
{
List<object> listRecordArguments = new List<object>();
var recordFields = record.Descendants();
for (int i = 0; i < columns.Count; i++)
{
var recordField = recordFields.Where(q => q.Name == fields[i].Name.LocalName).Single();
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);
}
object instance = Activator.CreateInstance(dynamicType, null);
object[] args = listRecordArguments.ToArray();
MethodInfo initMethodInfo = dynamicType.GetMethod(InitMethodName);
initMethodInfo.Invoke(instance, new object[] { args });
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.
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
- Set up a SQL Server 2008 database using the SQL script
01 - Create database.sql
- Load the Dynamic Reports project in Visual Studio
- Modify the web config as needed to allow the web service to access the newly created database
- Execute the WPF application and run a couple of reports
- Whilst leaving the application running, execute the second SQL script
02 - Add new report.sql
- Return to your running application and click "Fetch"—the new report should now appear
- Select it and populate the parameters
- 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)