Recently, I found myself with the task of exposing a catch-all reporting stored procedure through WCF. This single stored procedure accepts a standard set of parameters including a report name. Depending on the report name however, the rows and columns may vary significantly.
We're going to gloss over the fact that there are probably better ways of writing the reporting layer. There are some things you just can't change.
The heart of the problem is that both Entity Framework and WCF would be much happier with rigidly defined data contracts and models. A dynamically created resultset that's the result of an ad hoc query or varying list of queries throws a wrench into these tools.
The first issue is to execute the stored procedure or SQL command.
The first task is to get the database context into db. We use this database context to create the command object representing the SQL or Stored Procedure we're going to execute against the database.
If you're using a stored procedure, make sure that you update the command-type property - if you miss this step, you may get confusing warnings about missing parameters.
This command object is then executed, each row is converted to an Expando object, and the result is serialized to a string. Easy. Sure, but the command object doesn't have the AddParameterWithValue method, Expando objects are new to .NET 4, and WCF still wants a data contract to serialize to JSON. So maybe it's not that easy.
This method is courtesy of the MSDN Forum. Extension methods are really powerful bits of magic that I always forget about. Used effectively, they manage to make your code easier to write and easier to read. This particular version extends the dbCommand object to overcome the missing constructor on the parameter class.
Microsoft added the ExpandoObject class in .NET 4.0. It's simply a class of object that can have members added at run time. In this case, we iterate over the reader fields, creating an expando object.
WCF would prefer to know the object return type, so we have to fall back to a simple string data type. Don't feel bad. HTTP doesn't care about the data type on the wire, and Javascript will be more than happy to assemble the result into an object on the client. In order to get that string, we leverage the JSON.net library. JSON.net can be included with nuget and is pretty handy serializing and deserializing JSON data.