Displaying data via user generated SQL at runtime

I have a Winforms app which provides the ability for users to create their own reports. At its simplest they design the query which gets encrypted and saved into a database.

The ability to view these reports via the web as now come upon us.

W开发者_Python百科hat is the best way to handle this? Ideally I want to use MVC but there will be no model or viewmodel because these queries can return data for any table across the application and so I cannot have hardcoded propeties etc. Obviously I have access to the SQL to execute it.

I wonder if tradtional ASP.Net may perform better in this scenario ie/execute the SQL and put it in a DataTable but was hoping to get some thoughts from the community.

In a similar situation I used ASP.NET MVC with DataTables.net at the client side. I have an extension method on DataSet that returns the data in the JSON format required by DataTables.net. You will need a table/thead stub in the html that describes each column, but that could probably be generated if you parse the column names out of the SQL query or even better if you store column metadata when the user designs the report

What version of .NET will you be using? If it's 4 then you can use the new dynamic keyword.

As a example of this is used in the new web matrix. It takes a sql string and executes the query and the resulting object has the sql table fields as properties.

As the web matrix is built onto of the .NET platform you can use the same libraries and methods in Visual Studio.

If you don't know the properties that will be on an object you can use refection to loop around the properties in the object.

Here's a link that shows some of the basics.

Update:

var db = Database.Open("bakery");
var p = db.Query("SELECT * FROM PRODUCTS"); // This returns a list of products

Or for a single result

var db = Database.Open("TDL");
var selectQueryString = "SELECT * FROM Articles WHERE slug = @0";
var show =  db.QuerySingle(selectQueryString, slug);

The first example was from the link I've already included and the second was from here.

Update 2:

The assemblies you need are WebMatrix.Data and WebMatrix.WebData, MSDN. As I said before you will need to use reflection to find the object properties, just Google c# reflection and there are many tutorials about using it.