Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

Bulk update fails if the column name is SQL server keyword #142

Closed
habibwali opened this Issue · 3 comments

3 participants

@habibwali

var items = tableInDatabase.All("WHERE [Order] > " + order).ToArray();
foreach (var i in items)
{
i.Order = 7;
}
tableInDatabase.Save(items)

this would fail because Order is a keyword in SQL server, It works with other columns, Also the example for bulk update doesn't update the data because of ToArray usage in foreach loop

@frankhale

This looks easy enough to fix.

In CreateInsertCommand, CreateUpdateCommand and CreateDeleteCommand put [ ] around the places it's building up the column names.

Here are the relevant lines and methods. Extraneous code has been replaced with 3 dots.

public virtual DbCommand CreateInsertCommand(dynamic expando)
{
  ...

  foreach (var item in settings)
  {
    sbKeys.AppendFormat("[{0}],", item.Key);
    ...
  }

  ...
}

public virtual DbCommand CreateUpdateCommand(dynamic expando, object key)
{
  ...
  foreach (var item in settings)
  {
        ...
    if (!item.Key.Equals(PrimaryKeyField, StringComparison.OrdinalIgnoreCase) && item.Value != null)
    {
      result.AddParam(val);
      sbKeys.AppendFormat("[{0}] = @{1}, \r\n", item.Key, counter.ToString());
      counter++;
    }
  }
  ...
}

public virtual DbCommand CreateDeleteCommand(string where = "", object key = null, params object[] args)
{
  ...
  if (key != null)
  {
    sql += string.Format("WHERE [{0}]=@0", PrimaryKeyField);
    args = new object[] { key };
  }
  ...
}
@robconery
Collaborator

You can delimit the columns as needed in the source, but it's probably a better idea to not use reserved words.

@robconery robconery closed this
@habibwali

Thank you for the response

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.