5

Is there any way to call a stored procedure with a dynamic result set (no of columns) without knowing the exact class model and having to define it up front. Just want to get a dynamic list then loop the rows?

As far as I know I cannot find any examples and not sure this is even possible but would be neat if I could do this...

    var query = _db.Database.SqlQuery<????>("EXEC [dbo].[PivotOnMeterReadView]");
    var result = query.ToList();
John
  • 1,459
  • 4
  • 21
  • 45
  • See http://stackoverflow.com/questions/26749429/anonymous-type-result-from-sql-query-execution-entity-framework – Maria Ines Parnisari May 28 '15 at 04:23
  • thanks, i'm struggling as to where to put the code exactly,controller helper class? With it being public static – John May 28 '15 at 05:03
  • A helper class would be sufficient. Or an extension method. Or on your app context. I've worked on projects that grouped these type of methods into ___Repository classes or whatnot. – James Haug Oct 31 '16 at 16:53

1 Answers1

1

As far as I know, it is not possible to execute a Stored Procedure via LINQ. You'll have to use SqlConnection and SqlCommand. Below is a code snippet which I have used for successful execution of a SP. Hope I could help :)

private readonly string sqlConnectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(sqlConnectionString); //sqlConnectionString is the database connection.
sqlconn.Open();
using (SqlCommand cmd = new SqlCommand("InsertUsers", sqlconn)) // "InsertUsers" is the name of SP.
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@User", SqlDbType.Structured).Value = User; //Here I am passing the "User" variable as an argument to the "@User" paramter
    cmd.ExecuteNonQuery();
}
sqlconn.Close();
Abdul Samad
  • 441
  • 2
  • 11