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();
}
}
}
}
}