ObjectContext.ExecuteStoreCommand doesn't recognise parameter

Question

What am I doing wrong here?

...
using (var ctx = ObjectContextManager<MyDataContext>.GetManager("MyDataContext"))
{
    var idsToUpdate = "2,3";

    var parameters = new[]
    {
         new SqlParameter("DesiredEndDate", SqlDbType.DateTime).Value = newUpperLimit,
         new SqlParameter("TasksToUpdate", SqlDbType.NVarChar).Value = idsToUpdate
    };

    ctx.ObjectContext.ExecuteStoreCommand("UPDATE dbo.Tasks SET DesiredEndDate = @DesiredEndDate WHERE Id IN (SELECT Id FROM dbo.fn_Split(@TasksToUpdate, N','))", parameters);

    ctx.ObjectContext.SaveChanges();
}
...

I get the error

Must declare the scalar variable "@DesiredEndDate".

Must declare the scalar variable "@TasksToUpdate".

But I cannot see what is wrong with my code :/

Not sure about the error you are getting with `@DesiredEndDate` but that approach with `IN (@TasksToUpdate)` isn't going to work anyway. — Martin Smith, Aug 08 '13 at 09:38
I've already tested it by manually executing the query with hardcoded values. Can you explain why you think it's wrong? — Kev, Aug 08 '13 at 09:39
It won't work. It will be the same as doing `Id IN ('1,2')` not the same as `Id IN (1,2)` — Martin Smith, Aug 08 '13 at 09:41
You're right. Thanks for pointing it out. Updated. Still getting the same error though :( — Kev, Aug 08 '13 at 10:14

score 5 · Answer 1 · edited Jun 19 '14 at 18:09

5

I still don't see why the @ParamName syntax doesn't work. But there is an alternative to this, which does work:

 ctx.ObjectContext
    .ExecuteStoreCommand(
          "UPDATE dbo.Tasks SET DesiredEndDate = {0} WHERE Id IN (SELECT Id FROM dbo.fn_Split({1}, N','))",
          newUpperLimit, 
          idsToUpdate);

This has the added benefit that I don't need to create SqlParameters.

edited Jun 19 '14 at 18:09

ΩmegaMan

29,542
12
100
122

answered Aug 08 '13 at 10:24

Kev

2,656
3
39
63

You have got a possible SQL injection issue here though if `newUpperLimit` and `idsToUpdate` aren't guaranteed to be in a safe format. – Martin Smith Aug 08 '13 at 11:00
2

There is no SQL injection possible here. Internally, ExecuteStoreCommand uses DbCommand. CreateParameter to pass the parameters. See Josh Gallagher's comment in http://stackoverflow.com/questions/5474264/how-to-pass-parameters-to-the-dbcontext-database-executesqlcommand-method. – Kev Aug 08 '13 at 13:13
Ah interesting. I didn't look that closely TBH. I saw the `{0}` and just thought you were doing string concatenation. – Martin Smith Aug 08 '13 at 13:16

score 3 · Accepted Answer · edited Aug 08 '13 at 13:23

The problem is that your code:

var parameters = new[]
{
     new SqlParameter("DesiredEndDate", SqlDbType.DateTime).Value = newUpperLimit,
     new SqlParameter("TasksToUpdate", SqlDbType.NVarChar).Value = idsToUpdate
};

is not creating an array of SqlParameters - it's actually create an array of the 2 Value properties. You can confirm this by changing your code to this (which will not compile):

var parameters = new SqlParameter[]
{
     new SqlParameter("DesiredEndDate", SqlDbType.DateTime).Value = newUpperLimit,
     new SqlParameter("TasksToUpdate", SqlDbType.NVarChar).Value = idsToUpdate
};

and this (which will compile)

var parameters = new SqlParameter[]
{
     new SqlParameter("DesiredEndDate", SqlDbType.DateTime),
     new SqlParameter("TasksToUpdate", SqlDbType.NVarChar)
};

parameters[0].Value = newUpperLimit;
parameters[1].Value = idsToUpdate;

You can also do this:

var param1 = new SqlParameter("DesiredEndDate", SqlDbType.DateTime);
param1.Value = newUpperLimit;
var param2 = new SqlParameter("TasksToUpdate", SqlDbType.NVarChar);
param2.Value = idsToUpdate;

ctx.ObjectContext.ExecuteStoreCommand("...", param1, param2);

Cool. I was sure I had done it the way I showed in the question before and it had worked. Thanks for the explanation. Turns out that the SqlParameters variables are unnecessary. — Kev, Aug 08 '13 at 13:22

ObjectContext.ExecuteStoreCommand doesn't recognise parameter

2 Answers2