Date parameter using Entity Framework stored procedure

Question

I am calling a stored procedure on a SQL server DB that accepts Date parameters where d1, d2 are c# DateTime type. I am using the entity framework to do so:

context.Database.ExecuteSqlCommand("myprocedure @p1 @p2",dt1.Date,dt2.Date)

It didn't return results so I tried:

var p0 = new SqlParameter("p0",dt1);
p0.SqlDbType = SqlDbType.Date;
var p1= new SqlParameter("p1", dt2);
p1.SqlDbType = SqlDbType.Date;
context.Database.ExecuteSqlCommand("myprocedure @p1 @p2",p0,p1)

Yet, if I simply do:

context.Database.ExecuteSqlCommand("myprocedure @p1, @p2", '1/20/2014', '1/30/2014')

I get the correct result. Please help.. going out of my mind here.

Answer 1

Your dates also include times. To do exactly this:

context.Database.ExecuteSqlCommand("myprocedure @p1, @p2", '1/20/2014', '1/30/2014')

You need to do this:

context.Database.ExecuteSqlCommand("myprocedure @p1 @p2",dt1.Date.ToShortDateString() , dt2.Date.ToShortDateString())

This is because I assume your SP takes strings as opposed to DateTime types.

Note that the formatting of datestrings depends on culture. You may want to manually specify the MM/dd/YYYY format etc with the .ToString() method to ensure the SP gets the correct formatted date.

---

Also, it doesn't look like you're using entity framework correctly. There should be a method called MyProcedure on the entity framework object that you can call directly and pass the parameters.