none
Is there a way to generate Update command with only a few SET columns which actually changed? RRS feed

  • Question

  • Hi everybody,

    This is my current code

      protected Boolean ProcessDataTable(SqlCommand toSqlCommand)
            {
                Boolean results = false;
    
                try
                {
                    this.table = new DataTable();
                    this.adapter = new SqlDataAdapter(toSqlCommand);
                    this.adapter.SelectCommand = toSqlCommand;
                    this.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; // Generate PK information
                    this.adapter.Fill(this.table);
                    this.adapter.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating);
                    
                    results = true;
                }
                catch (Exception ex)
                {
                    results = false;
                    Logging.LogFormat(2, ex.Message);  // at least log the problem
                }
    
                return results;
            }
            // handler for RowUpdating event 
            private static void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e)
            {
                PrintEventArgs(e);
            }
    
            private static void PrintEventArgs(SqlRowUpdatingEventArgs args)
            {
                Console.WriteLine("OnRowUpdating");
                Console.WriteLine("  event args: (" +
                    " command=" + args.Command +
                    " commandType=" + args.StatementType +
                    " status=" + args.Status + ")");
            }
            /// <summary>
            /// Update information
            /// </summary>
            /// <returns></returns>
            public Boolean UpdateRows(Boolean generateCommands = false)
            {
                Boolean results = false;
                try
                {
                    if (generateCommands)
                    {
                       
                        this.adapter.UpdateBatchSize = 1; //Disable batch processing
                        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                        builder.ConflictOption = ConflictOption.OverwriteChanges;
                        builder.SetAllValues = false; // Set only changed values
                        adapter.UpdateCommand = builder.GetUpdateCommand(true);
                    }
                    adapter.Update(this.table);
                    results = true;
                }
    
                catch (Exception ex)
                {
                    results = false;
                    Logging.LogFormat(2, ex.Message);  // at least log the problem
                }
    
                return results;
            }

    When I examine the UpdateCommand I see it includes every single column from the table (my select command is 'SELECT * from Table where PK = @PK") even including updating the PK column (which is not identity).

    My question is - is there a way to automatically create the update command that will only have changed columns in the SET portion of the command?

    The SetAllValues property doesn't seem to matter.

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, April 14, 2014 5:52 PM

Answers

All replies

  • Hello Naomi,

    >>My question is - is there a way to automatically create the update command that will only have changed columns in the SET portion of the command?

    In actual, the generated TSQL in with SqlCommandBuilder is same whether we set its value to be false or true. However, the exact executed  TSQL will be affected, you can use the SQLProfile to check the executed TSQL, you can find if it is false, it will only update the changed column, and if true, it will update all column including the primary key. You can have a try.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 15, 2014 2:04 AM
    Moderator
  • With the help of Rob Jasinski from another forum I was able to sort this problem out.



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Naomi N Thursday, April 17, 2014 3:14 AM
    Thursday, April 17, 2014 3:14 AM