Run Stored Proc and get dynamic results

[MonkeyManvin]

The scenario is that I want to be able to run reports on data in a database - these are in the form of Stored Procedures (SP). I want to be able to create new SP when the user requires without having to rebuild/publish the project. The date (report) return simply needs to output to .csv for the user.

So far I have created a table containing ID, Report Name, Report Description and name of Stored Proc. This will allow the users to select the report they wish to run and me to add new reports by simply adding details to the table.

So, I have 3 main question:

1) What's the best/easiest way to run the required SP. I am using Entity Framework, so I have the DB in the project but obviously not the SP - I have considered having a single SP that could be included in the dbml that runs the relevant SP:

IF @ReportID = 1
BEGIN
EXEC pReport1
END
ELSE IF @ReportID = 2

etc........

2) The data returned from the SP contain unknown number of columns. The data can all be treated as strings. How best do I handle the return?

3) How best do I output the csv results to the user (this step may well merge with step 2?).

Thanks in advance for any pointers or advice

Continue reading...