0

For performance reasons I'm trying to use Entity Frameworks ExecuteSqlCommand to bulk update data. ctx.Database.ExecuteSqlCommand(updateQuery, new object[] {newValue} );

However I'm having trouble setting the Value-field of my table. It should multiply the existing value by a factor, but I'm receiving the following error:

Additional information: Error converting data type nvarchar to numeric.

newValue = "Value * 56,25";    
String updateQuery = @"UPDATE dbo.BudgetLine_type1_unpivoted set Value = {0} where valueTypeID='Value2' AND Dim2 in ('712','718','722') AND Dim4 = '110' AND Period = 1"
int noRowsAffected = db.Database.ExecuteSqlCommand(updateQuery, new object[] {newValue} );

When setting newValue to "1" or simply "Value" (not changing anything) it works fine

How should I got about doing the multiplication? The value-field is of type decimal(28,6)

JohanLarsson
  • 475
  • 1
  • 8
  • 23

1 Answers1

0

Passing parameters to ExecuteSqlCommand does not behave the same as string.Format, so your {0} placeholder is incorrect - you need to set a SQL parameter here instead.

If you want to always multiply Value by a certain amount, that amount is the thing which changes, so that is the parameter you need to pass. You just need to tweak the query depending on if it is a multiplication or not, i.e. either set Value = Value * @p0 or set Value = @p0. I assume your code must know what the value to set is and whether to multiply or not.

I would write it like this, similar to the example in the description on MSDN:

decimal newValue = 56.25;
bool multiply = true;

String newValueSql = multiply ? "Value * @p0" : "@p0";
String updateQuery = @"UPDATE dbo.BudgetLine_type1_unpivoted set Value = " + newValueSql
    + " where valueTypeID='Value2' AND Dim2 in ('712','718','722') AND Dim4 = '110' AND Period = 1"

int noRowsAffected = db.Database.ExecuteSqlCommand(updateQuery, newValue);

@p0 will get replaced with the value of newValue when the query is run and will be a decimal value so the calculation works.

Rhumborl
  • 16,349
  • 4
  • 39
  • 45
  • sometimes it will be Value = Value * factor and other times it will be simply Value = X so your example will not work in this case I'm afraid. I tried the replace {0} with @p0 but still no luck – JohanLarsson Oct 07 '14 at 09:05
  • Thanks a lot, that did the trick. I think my problem was that I stored "Value * 56.25" in a string rather than a decimal when trying to run the update. Cheers! :) – JohanLarsson Oct 07 '14 at 09:54