How to retrieve the data from a stored proc?

Stesvis 1,041 Reputation points
2022-07-06T21:54:40.16+00:00

I want to call a stored procedure from my EF6 MVC 5 app.
The stored procedure is just a SELECT * FROM Customers WHERE <conditions>, but the problem is that in MVC 5 I don't know how to retrieve the records.

I have done it before with .NET Core, which is a breeze:

   IQueryable<Customer> customers = Context.Customers.FromSqlRaw("exec sp_get_nearest_customers", param1, param2);  

In MVC 5 the only way I found was this:

   var customers = new List<Customer>();  
     
               using var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString());  
               using var cmd = new SqlCommand("sp_get_nearest_customers", con);  
     
               cmd.CommandType = CommandType.StoredProcedure;  
               cmd.Parameters.AddWithValue("@ClientId", clientId);  
               cmd.Parameters.AddWithValue("@Latitude", lat);  
               cmd.Parameters.AddWithValue("@Longitude", lng);  
               cmd.Parameters.AddWithValue("@Range", range);  
     
               con.Open();  
     
               using var da = new SqlDataAdapter();  
               da.SelectCommand = cmd;  
     
               using var ds = new DataSet();  
               da.Fill(ds);  
     
               for (int i = 0; i < ds.Tables[0].Rows.Count; i++)  
               {  
                   Customer customer = new Customer();  
                   customer.Id = Convert.ToInt32(ds.Tables[0].Rows[i]["Id"].ToString());  
                   customer.Company = ds.Tables[0].Rows[i]["Company"].ToString();  
                   customer.Geolocation = Newtonsoft.Json.JsonConvert.DeserializeObject<GeolocationJSON>(ds.Tables[0].Rows[i]["Geolocation"].ToString());  
     
                   customers.Add(customer);  
               }  
     
               return customers;  

As you must agree it's a much longer way to obtain the same result.
I hope I am missing the obvious here and that someone can suggest a more proper and quick way to do this.
For the record, I use unitOfWork, repository pattern etc (if it helps).

Again, it's in MVC 5, not .net core.

Thanks!

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,161 questions
0 comments No comments
{count} votes

Accepted answer
  1. Rijwan Ansari 741 Reputation points MVP
    2022-07-07T00:59:09.593+00:00

    HI @Stesvis

    You can use in this way.

    dbContext.Database.SqlQuery<YourEntityType>("storedProcedureName",params);  
    

    This is quite simple and easy to use.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Yijing Sun-MSFT 7,061 Reputation points
    2022-07-07T04:22:02.743+00:00

    Hi @Stesvis ,
    I think you could add these codes to your generic repository.Just like this:

    218415-new-text-document.txt
    Note: T will come from ProductRepository : GenericRepository<Product>.

    Best regards,
    Yijing Sun


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.