Last post Jan 12, 2016 06:33 AM by mds2907
Jan 11, 2016 10:20 AM|mds2907|LINK
I have a scenario where I am struggling a bit. I have table Users in database, with a stored procedure for registering new user. I have used MVC4 with entity framework5.
I have used stored procedure because I have User ID generated in my SP at the time of user insert.
The stored proc uses output param, and hence I am using "Database.ExecuteSqlCommand in my DBContext class. Below is the code
SqlParameter sqlParams =
new SqlParameter("@Email", objUser.Email),
new SqlParameter("@chvOutput", "",128, System.Data.ParameterDirection.Output)
string strProcInputParams = "@chvEmail, @chvPassword";
Database.ExecuteSqlCommand("[dbo].[RealtyReviews_SPUserAccount]" +' '+strProcInputParams, sqlParams);
But I am getting error for the third parameter..
Can anyone let me know if the way m doing is correct.
Also most of the database operations i would like to use SP's for operations
By considering above, would it be good to drop EF and go ahead? Since I find it difficult to get things worked in Codefirst.
Is there any optimal way where I can use my stored procedures, return output from the same?
Please guide on the same.
Jan 12, 2016 05:53 AM|Zhi Lv - MSFT|LINK
I suggest you could refer to the following code:
var descriptionParameter = new SqlParameter();
descriptionParameter.ParameterName = "@Description";
descriptionParameter.Direction = System.Data.ParameterDirection.Output;
descriptionParameter.SqlDbType = System.Data.SqlDbType.NVarChar;
descriptionParameter.Size = 255; //If the data type is varchar or nvarchar, we need to set the size.
var des = context.Database.ExecuteSqlCommand("GetTestTableValueByID @ID, @Description OUTPUT", new SqlParameter("@ID", "1001"), descriptionParameter);
More details, you could refer to the following articles:
Jan 12, 2016 06:33 AM|mds2907|LINK
Thanks for the reply. I have started to adapt the same after learning from a similar article as you have provided. One thing I want to know is , Is this the correct approach to follow?
If I am executing the stored procedure, then I feel that using EF will not be of much helpful and we will not be able to take full advantage of it. Instead I can use repository/unit of work pattern with just plain ADO.Net.
Please let me know your thoughts on the same.