>Rob,
>
>Setting all these properties didn't change a thing :( My UpdateCommand is still very long:
>
>
>UPDATE [dbo].[gst_pass] SET [pass_no] = @pass_no, -- BTW, this is a PK in that table
>
>
> [guest_no] = @guest_no, [masterpass] = @masterpass, [swipe_no] = @swipe_no, [addit_no] = @addit_no,
> [wtp_no] = @wtp_no, [rfserial] = @rfserial, [additchar1] = @additchar1, [additchar2] = @additchar2,
> [addit_no2] = @addit_no2, [addit_no3] = @addit_no3, [val_parent] = @val_parent,
>[valprnttyp] = @valprnttyp, [last_use] = @last_use, [printcount] = @printcount,
> [start_date] = @start_date, [expires] = @expires,
>[validcount] = @validcount, [dis_count] = @dis_count, [total_uses] = @total_uses,
> [usesw_left] = @usesw_left, [week_refr] = @week_refr, [usest_left] = @usest_left,
>[day_refr] = @day_refr, [points1] = @points1, [points2] = @points2,
>[money1] = @money1, [money2] = @money2, [blackout_s] = @blackout_s,
> [blackout_e] = @blackout_e, [warnings] = @warnings, [voided_for] = @voided_for,
>[voided_by] = @voided_by, [shift_ends] = @shift_ends, [trans_no] = @trans_no,
> [mastertran] = @mastertran, [department] = @department,
>[category] = @category, [item] = @item, [amt_paid] = @amt_paid,
> [account] = @account, [operator] = @operator, [salespoint] = @salespoint, [date_time] = @date_time,
>[totalcomp] = @totalcomp, [invoice_no] = @invoice_no, [cc_tracks] = @cc_tracks, [crlimit] = @crlimit,
>[crlimit_dy] = @crlimit_dy, [dw_active] = @dw_active, [splimit] = @splimit, [splimit_dy] = @splimit_dy,
> [last_mod] = @last_mod, [card_id] = @card_id, [bl_reason] = @bl_reason, [importpass] = @importpass,
> [level_chg] = @level_chg, [purch_chg] = @purch_chg, [a_autodep1] = @a_autodep1, [a_autocat1] = @a_autocat1, [a_autoitm1] = @a_autoitm1, [a_maxqty1] = @a_maxqty1, [a_trklqty1] = @a_trklqty1, [a_autodep2] = @a_autodep2, [a_autocat2] = @a_autocat2, [a_autoitm2] = @a_autoitm2, [a_maxqty2] = @a_maxqty2, [a_trklqty2] = @a_trklqty2, [a_autodep3] = @a_autodep3, [a_autocat3] = @a_autocat3, [a_autoitm3] = @a_autoitm3, [a_maxqty3] = @a_maxqty3, [a_trklqty3] = @a_trklqty3, [a_autodep4] = @a_autodep4, [a_autocat4] = @a_autocat4, [a_autoitm4] = @a_autoitm4, [a_maxqty4] = @a_maxqty4, [a_trklqty4] = @a_trklqty4, [a_autodep5] = @a_autodep5, [a_autocat5] = @a_autocat5, [a_autoitm5] = @a_autoitm5, [a_maxqty5] = @a_maxqty5, [a_trklqty5] = @a_trklqty5, [a_autodep6] = @a_autodep6, [a_autocat6] = @a_autocat6, [a_autoitm6] = @a_autoitm6, [a_maxqty6] = @a_maxqty6, [a_trklqty6] = @a_trklqty6, [a_names] = @a_names, [mod_op] = @mod_op, [mod_sp] = @mod_sp, [cc_swipe] = @cc_swipe, [pin] = @pin WHERE (([pass_no] = @Original_pass_no))
>
>Even though I changed the UpdateRows method to be
>
>
> public Boolean UpdateRows(Boolean generateCommands = false)
> {
> Boolean results = false;
> try
> {
> if (generateCommands)
> {
> 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;
> }
>>
>and I added
>
>
> 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);
>
> results = true;
> }
> catch (Exception ex)
> {
> results = false;
> Logging.LogFormat(2, ex.Message); // at least log the problem
> }
>
> return results;
> }>
>Do you see what is missing and why it didn't work for me?
>
>Thanks again.
>
>-------------
>Although it did shorten the WHERE clause as I see. But it included all columns in the update, not only the changed values.
Add an event handler for the data adapter's OnRowUpdating event (
http://msdn.microsoft.com/en-us/library/system.data.common.dbdataadapter.onrowupdating%28v=vs.110%29.aspx). Check the command used there and verify whether or not the values in the columns changed. Also make sure the data adapter's UpdateBatchSize is set to 1.
