Wednesday, October 15, 2008

How to create update command from a DataTable

protected override DbCommand GetUpdateCommand(DataTable changedRecords)
{
    StringBuilder query = new StringBuilder(string.Format("UPDATE {0} SET ",changedRecords.TableName));
    List primary = new List();
    primary.AddRange(changedRecords.PrimaryKey);

    for (int i = 0; i < changedRecords.Columns.Count; i++ )
    {
    DataColumn column = changedRecords.Columns[i];

    if (!primary.Contains(column))
    {
        query.Append(column.ColumnName + "=@" + column.ColumnName);

        if (i < changedRecords.Columns.Count - changedRecords.PrimaryKey.Length - 1)
        query.Append(" ,");
    }
       
    }
   
    query.Append(" WHERE ");

    for (int i = 0; i < changedRecords.PrimaryKey.Length; i++)
    {
    DataColumn column = changedRecords.PrimaryKey[i];               
    query.Append(column.ColumnName + "=@" + column.ColumnName);

    if (i < changedRecords.PrimaryKey.Length - 1)
        query.Append(" ,");

    }
    SqlCommand command = new SqlCommand(query.ToString());
    command.UpdatedRowSource = UpdateRowSource.None;
    return command;
}

No comments:

Post a Comment