Adhoc stored procs on silverlight app

Question

I have a simple requirement to be implemented on a silverlight app. I want to list down all the stored procs in a drop down and view its output on a grid. I am currently having a linq to sql class where I have drag dropped all the stored procs I want to view and generated the classes on a .dbml file. This works for me at the moment. However, if I have a new stored proc, I am forced to generate the class and update the service again.

Is there a way to view the results of the stored proc without updating the service everytime? I was thinking of using DataContext.ExecuteCommand Method but not sure if there is a much better way to do this.

Answer 1

You will first need to write a stored procedure that returns the list of store procedures in your database, using a statement like (add filtering as required):

SELECT * FROM sys.procedures

That will let you display the list in the UI without recompiling.

The second step will be to call that stored proc. DataContext.ExecuteCommand() won't do because it executes a command, not a query. That means that ExecuteCommand() won't give you a resultset, only an int.

DataContext.ExecuteQuery() might do but that depends whether all of your stored procs are going to return the same set of columns. If this is not the case then you might be better off using straight ADO.NET and get the result in a DataSet.