1

I set the value for my SqlCommandbuilder.SetAllValues = false;

Some year ago when I implemented it I checked with profiler and it worked correct, only the changed fields are in the update command generated by the SqlCommandBuilder.
But now I discovered it is not working anymore.
When debugging I notice that just before calling adapter.Update(table) that in the DataTable the columns have the correct value in the DataRowVersion.Original and the DataRowVersion.Current

I have no clue where to begin looking for this so I am hoping someone can push me in the right direction.

I use binding on all my forms, every control is bound to a BindingSource that is bound to a DataTable.

Here is my complete code of my ApplyUpdate command :

public int ApplyUpdates(DataTable Table, string SelectTextForUpdate, string IdentityFieldName = "") Command, string SelectTextForUpdate = "")
{
    int Result = -1;

    if (_ConnectionString != null && _ConnectionString != "")
    {
        using (SqlConnection connection = new SqlConnection(_ConnectionString))
        {
            connection.Open();
            SqlTransaction trans = connection.BeginTransaction();

            using (SqlDataAdapter adapter = new SqlDataAdapter())
            {
                using (SqlCommand command = new SqlCommand())
                {
                    using (SqlCommandBuilder builder = new SqlCommandBuilder())
                    {
                        adapter.SelectCommand = command;
                        adapter.SelectCommand.Connection = connection;
                        builder.DataAdapter = adapter;

                        // Make only fields with changed values appear in the update command
                        // !!!!!!!!!!!!!!!!!!!!!!!!! Somewhere in 2016 this stopped working and it always updates all values whatever the value of this property !!!!!!!!!!!!!!!!!!!!!!!!!
                        builder.SetAllValues = false;

                        // Make the where clause of the update statement to have only the primary field in it.
                        builder.ConflictOption = ConflictOption.OverwriteChanges; 

                        adapter.SelectCommand.CommandText = SelectTextForUpdate;
                        adapter.SelectCommand.Transaction = trans;
                        adapter.UpdateCommand = builder.GetUpdateCommand(true).Clone();
                        adapter.DeleteCommand = builder.GetDeleteCommand(true).Clone();

                        // create new insertcommand with extra parameter for getting the stupid identity field
                        SqlCommand inserter = new SqlCommand();
                        inserter = builder.GetInsertCommand(true).Clone();
                        if (IdentityFieldName != "")
                        {
                            inserter.CommandText += " SET @ID = SCOPE_IDENTITY()";
                            SqlParameter param = new SqlParameter();
                            param.Direction = ParameterDirection.Output;
                            param.Size = 4;
                            param.DbType = DbType.Int32;
                            param.ParameterName = "@ID";
                            inserter.Parameters.Add(param);
                        }

                        //put custom insertcommand into our adapter
                        adapter.InsertCommand = inserter;

                        // now dispose the original CommandBuilder. The original builder is bound to our adapter and will overwrite the insertcommand
                        // just before the adapter.update command, and thus dump your new parameter...
                        // The only way to break this evil spell is to dispose the bad sorcerer
                        builder.Dispose();

                        // now create a temperary RowUpdated event, in this we can update the identity field of the table
                        adapter.RowUpdated += adapter_RowUpdated;
                        _Table = Table;
                        _IdentityFieldName = IdentityFieldName;

                        try
                        {
                            try
                            {
                                if (adapter.InsertCommand != null)
                                        adapter.InsertCommand.Transaction = trans;
                                if (adapter.UpdateCommand != null)
                                        adapter.UpdateCommand.Transaction = trans;
                                if (adapter.DeleteCommand != null)
                                        adapter.DeleteCommand.Transaction = trans;

                                Result = adapter.Update(Table);
                                trans.Commit();
                            }
                            catch (Exception ex)
                            {
                                trans.Rollback();
                                throw new Exception(ex.Message);
                            }
                        }
                        finally
                        {
                            // get rid of the temperay RowUpdated event
                            adapter.RowUpdated -= adapter_RowUpdated;
                            _Table = null;
                            _IdentityFieldName = "";
                            inserter.Dispose();
                        }

                        Table.AcceptChanges();
                    }
                }
            }
        }
    }
Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
GuidoG
  • 11,359
  • 6
  • 44
  • 79

0 Answers0