11

Where can I get good tutorial on Entity framework with Stored Procedure in MVC framework?

Is it better to use Enterprise library in this case when I have almost everything written in the stored procedure.

Note: I am using stored procedure because they are really very complex and some of them is over 1000 lines.

Chris
  • 2,293
  • 11
  • 48
  • 86

3 Answers3

41

MVC is in this case absolutely not related. The way how you call stored procedure from EF will be still the same. I guess you want to use stored procedures without actually using entities and linq-to-entities (main EF features), don't you? Generally you need:

  • EDMX file (ado.net entity data model) where you run update from database and add all stored procedures you want to use. EDMX file also generates derived ObjectContext and all entities by default.
  • Next you must go to Model Browser and create Function import for each procedure. Function import will create method on the derived ObjectContext which will allow you call the stored procedure as any other .net method.
  • During function import you will have to create complex type (it can happen automatically) for result set returned from stored procedure.

You also don't have to use function imports at all and you can execute procedures directly by calling either:

  • objectContext.ExecuteSqlCommand("storedProcedureName", SqlParameters) for SPs not returning record set
  • objectContext.ExecuteStoreQuery<ResultType>("storedProcedureName", SqlParameters) for SPs returning record set. ResultType must have properties with same names as columns in result set. It can work only with flat types (no nested objects).

There are some limitations when using stored procedures:

  • Entity framework doesn't like stored procedures which returns dynamic result sets (based on some condition result set has different columns)
  • Entity framework doesn't support stored procedures returning multiple result sets - there are EFExtensions which does but it is more like doing ADO.NET directly.
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • 1
    +1 Awesome.. Why SO doesn't allow us to give +5. Thanks for the explanation. Just wanted to know that if I use Enterprise Library then will it be under good practice because my SP returns multiple result sets also few output parameter. – Chris Jun 02 '11 at 20:19
  • Can you use Stored Procedures without an EDMX file? – Tom Stickel Aug 29 '12 at 17:14
  • 1
    @TomStickel: You cannot map stored procedure to function import without EDMX but you can execute stored procedures through `dbContext.Database.SqlQuery` or `dbContext.Database.ExecuteSqlCommand` – Ladislav Mrnka Aug 29 '12 at 20:30
1

If you are using Entityframwork Code-first,This way you can Use your stored-Procedure, In this Example I have four Input parameters.

var startDateTY = masterSales.PolicyStartDate;
var endateTY = masterSales.PolicyEndDate;
var startDatePY = masterSales.PolicyStartDate.Value.AddYears(-1);
var endatePY = masterSales.PolicyEndDate.Value.AddYears(-1);

var spParameters = new object[4];
spParameters[0] = new SqlParameter()
{
     ParameterName = "startDateTY",
     Value = startDateTY
};
spParameters[1] = new SqlParameter()
{
     ParameterName = "endateTY",
     Value = endateTY
};
spParameters[2] = new SqlParameter()
{
     ParameterName = "startDatePY",
     Value = startDatePY
};
spParameters[3] = new SqlParameter()
{
     ParameterName = "endatePY",
     Value = endatePY
};
var datalist = objContext.Database.SqlQuery<vMasterSalesAgentReport>("dbo.usp_GetSalesAgentReport @startDateTY,@endateTY,@startDatePY,@endatePY", spParameters).ToList();
Smit Patel
  • 2,992
  • 1
  • 26
  • 44
0
store = "sp_selectmark @regid='" + id + "'";
var st = db.ExecuteStoreQuery<Sp>("exec " + store).ToList();
GridView1.DataSource = st;
GridView1.DataBind();

or

string store = "";
store = "sp_inserttbreg @name='" + regobj.name + "',@age='" + regobj.age + "',@place='" + regobj.place + "',@gender='" + regobj.gender + "',@email='" + regobj.email + "',@fon='" + regobj.fon + "'";
samir
  • 396
  • 2
  • 13