Geek Answers Handbook

Executing a stored procedure using an entity structure

You can execute a stored procedure using EF, which selects records from a database from two or more tables using an inner join and a left outer join.

my point is to avoid the method of doing joins in EF or LINQ, with which I have a lot of problems with it.

therefore, if I create this procedure, can I call it with parameters from user input, I can assign the result to the .ToList() method, and then add the result to asp: repeatater .DataSource .

I know this may be a strange question, but I want to do this for many reasons, firstly, use EF, because I feel more comfortable. secondly, get rid of using unions in EF. thirdly, I read somewhere that using a stored procedure will improve query performance when a query is called frequently.

if anyone can help me answer these questions with an example, I would appreciate it.

+5
source share
3 answers

You can invoke SqlQuery from an Entity Framework data context.

 context.Database.SqlQuery<YourType>("exec usp_StoredProcedure").ToList()

You will need a class to match the query results as an example:

 public class YourType { public string Property1 { get; set; } public string Property2 { get; set; } }

You can also specify parameters for the request, as shown below:

 SqlParameter parameter1 = new SqlParameter("@Parameter1", "Value"); context.Database.SqlQuery<YourType>("exec usp_StoredProcedure @Parameter1", parameter1).ToList()
+15
source

We will look at how to execute a stored procedure in the Entity Framework, in MVC we will see how to add EF.

Run the following script in the database to create the stored procedure.

 CREATE PROCEDURE FETCHEMPLOYEES AS BEGIN SELECT * FROM EMPTABLE END CREATE PROCEDURE FETCHEMPLOYEE(@ID INT) AS BEGIN SELECT * FROM EMPTABLE WHERE ID = @ID END public class EmpModel { EmployeeEntities empdb = new EmployeeEntities(); public List<EMPTABLE> GetEmployees() { return empdb.FETCHEMPLOYEES().ToList(); } public EMPTABLE GetEmployee(int? id) { return empdb.FETCHEMPLOYEE(id).ToList().Single(); } } public class EmployeeController : Controller { Models.EmpModel mod = new Models.EmpModel(); public ActionResult Index() { List<EMPTABLE> result = mod.GetEmployees(); return View(result); } public ActionResult Details(int id) { EMPTABLE result = mod.GetEmployee(id); return View(result); } }

For more information, please refer to the following link: http://dotnetvisio.blogspot.in/2014/01/execute-stored-procedure-using-entity.html

0
source

you can use the ExecuteFunction of the ObjectContext class:

  public virtual ObjectResult<CustomClassForStoreReturnedData> NameOfStoredProcedure(Nullable<int> tableID) { var IDParameter = tableID.HasValue ? new ObjectParameter("tableID", tableID) : new ObjectParameter("tableID", typeof(int)); return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<CustomClassForStoreReturnedData>("NameOfStoredProcedureInSQLDatabase", IDParameter); }
0
source

More articles:


All Articles