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 :/
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 SqlParameter
s - 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);
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 SqlParameter
s.