I have created a simple stored procedure in SQL Server 2008 as:
CREATE PROCEDURE viewPosts
AS
SELECT * FROM dbo.Post
Now, I have no idea how to use it in controller's action, I have a database object which is:
entities db = new entities();
Kindly tell me how to use stored procedure with this database object in Entity Framework.
For Details check this link: http://www.entityframeworktutorial.net/data-read-using-stored-procedure.aspx
Hope this will help you.
See article about 30% in:
- In the designer, right click on the entity and select Stored Procedure mapping.
- Click and then click the drop down arrow that appears. This exposes the list of all Functions found in the DB metadata.
- Select Procedure from the list. The designer will do its best job of matching the stored procedure’s parameters with the entity properties using the names. In this case, since all of the property names match the parameter names, it maps every one correctly so you don’t need to make any changes. Note: The designer is not able to automatically detect the name of the field being returned.
- Under the Result Column Bindings section, click and enter variable name. The designer should automatically select the entity key property for this final mapping.
The following code is what I use to initialize the stored procedure, then obtain the result into variable returnedResult, which in this case is the record id of a newly created record.
SqlParameter paramResult = new SqlParameter("@Result", -1);
paramResult.Direction = System.Data.ParameterDirection.Output;
var addParameters = new List<SqlParameter>
{
new SqlParameter("@JobID", EvalModel.JobID),
new SqlParameter("@SafetyEvaluator", EvalModel.SafetyEvaluator),
new SqlParameter("@EvaluationGuid", EvalModel.EvaluationGuid),
new SqlParameter("@EvalType", EvalModel.EvalType),
new SqlParameter("@Completion", EvalModel.Completion),
new SqlParameter("@ManPower", EvalModel.ManPower),
new SqlParameter("@EDate", EvalModel.EDate),
new SqlParameter("@CreateDate", EvalModel.CreateDate),
new SqlParameter("@Deficiency", EvalModel.Deficiency.HasValue ? EvalModel.Deficiency.Value : 0),
new SqlParameter("@DeficiencyComment", EvalModel.DeficiencyComment != null ? EvalModel.DeficiencyComment : ""),
new SqlParameter("@Traffic", EvalModel.Traffic.HasValue ? EvalModel.Traffic.Value : 0),
paramResult
};
// Stored procedure name is AddEval
context.Database.ExecuteSqlCommand("AddEval @JobID, @SafetyEvaluator, @EvaluationGuid, @EvalType, @Completion, @ManPower, @EDate, @CreateDate, @Deficiency, @DeficiencyComment, @Traffic, @Result OUTPUT", addParameters.ToArray());
var returnedResult = paramResult.Value;
NewEvaluationID = Convert.ToInt32(returnedResult);
