question

Stesvis-5434 avatar image
0 Votes"
Stesvis-5434 asked Stesvis-5434 commented

How to retrieve the data from a stored proc?

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!

dotnet-aspnet-mvc
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

rijwanansari avatar image
1 Vote"
rijwanansari answered Stesvis-5434 edited

HI @Stesvis-5434

You can use in this way.


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


This is quite simple and easy to use.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @rijwanansari that was also the first one I tried but it gave me an error that I could not figure out so I went for the other route.
The error was:

"exceptionMessage": "The data reader is incompatible with the specified Models.Customer'. A member of the type, '_geolocation', does not have a corresponding column in the data reader with the same name."


My Customer model has a column where I store the geolocation as a JSON object:

internal string _geolocation { get; set; }
        [NotMapped]
        public GeolocationJSON Geolocation
        {
            get { return _geolocation == null ? null : JsonConvert.DeserializeObject<GeolocationJSON>(_geolocation); }
            set { _geolocation = JsonConvert.SerializeObject(value); }
        }


and

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Customer>().Property(b => b._geolocation).HasColumnName("Geolocation");
        }


I am not sure how to get that to work, but if there is a solution, for sure one-liner is better!

For now I guess I could modify the stored proc to return an extra column...even tho it's not my favorite solution: SELECT *, Geolocation AS _geolocation

0 Votes 0 ·
Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YijingSun-MSFT avatar image
0 Votes"
YijingSun-MSFT answered Stesvis-5434 commented

Hi @Stesvis-5434 ,
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.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @YijingSun-MSFT please see my answer to rijwanansari above..
I am probably missing something.

0 Votes 0 ·

Hi @Stesvis-5434 ,

For now I guess I could modify the stored proc to return an extra column...even tho it's not my favorite solution: SELECT *, Geolocation AS _geolocation

So,do you have solved your problems now? Or you still have problems,you could tell the current problem clearly to us.

Best regards,
Yijing Sun

0 Votes 0 ·

Hey @YijingSun-MSFT I believe that I am ok now.
The one-line code (`ApplicationContext.Customers.SqlQuery`) is working now that I added an extra column in the SELECT of the stored proc.

I am not sure if it's due to the extra computing (in the other solution I was only selecting the columns that I needed), but the one-line solution is slower. However it works so thank you for the answer!

0 Votes 0 ·