How to get value from stored procedure which returns dynamic table in Asp mvc EDMX

Question

I am using stored procedure in edmx. I am using following SP.

Create getreportDAta(@Reportname varchar(50),@startDate datetime,@enddate datetime)
begin
    IF OBJECT_ID('tempdb..#ouputtable ') IS NOT NULL DROP TABLE #ouputtable ;
  create #ouputtable (usdate datetime)
  if(@reportname="abc")
   begin
   alter #ouputtable add(Some columns) 
     end
      else begin 
         alter #ouputtable add(Some columns) 
      End 
      so oonn...
     Select * from #ouputtable ;

Now i want to read the value selected by Select * from #ouputtable in edmx . ouputtable is not contain fixed number of columns.

How can i do this in ASP mvc EDMX.

score 0 · Answer 1 · answered Feb 12 '13 at 14:59

Basically I wanted to do the same thing, but in the end couldn't figure it out... so i decided on creating a class that i could call instead when wanting to call sp's like this. This link gives the basics on how to call and bind the sp's results to your Gridview: http://www.aspnettutorials.com/tutorials/database/db-storedprocs-aspnet2-csharp.aspx

(see the "The flow for the code behind page is as follows." section. Also, they have a link to a VB.NET version on that page if you're not into C#)

Here's a modified version of my code-behind button click event:

try {
System.Configuration.Configuration rootWebConfig =  
    System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/MyWebsite");    //create connection string object
System.Configuration.ConnectionStringSettings connString;
if (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0)    {
    connString = rootWebConfig.ConnectionStrings.ConnectionStrings["MyConnectionString"];
    if (connString != null) {
        System.Data.SqlClient.SqlCommand cmd = 
            new System.Data.SqlClient.SqlCommand("MyStoredProcedure", new System.Data.SqlClient.SqlConnection(connString.ConnectionString));
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Connection.Open();
        GridViewResults.DataSource = cmd.ExecuteReader();
        GridViewResults.DataBind();
        cmd.Connection.Close();
        cmd.Connection.Dispose();
    }
    else {
        throw new Exception("No MyConnectionString Connection String found in web.config.");
    }
}
else {
    throw new Exception("No Connection Strings found in web.config.");
}
}
catch (Exception) {
    throw;
}

Simply incorporate that into a class of your own in your datalayer project where your edmx file is and use it to call such stored procedures. :).

How to get value from stored procedure which returns dynamic table in Asp mvc EDMX

1 Answers1