TableAdapter Preserve Original Values on Update (no Accept Changes

[Guest]

Is there a way that I can prevent the SqlDataAdapter associated the with
TableAdapter from issuing an AcceptChages? The only way I can see how to do
this is to create a partial class for the TableAdapter. The associated
SqlDataAdapter.AcceptChangesDuringUpdate defaults is defaulted to true.

My issue is that I am calling a web method with the dataset containing the
newly inserted rows. I then use the TableAdapter to update the database and
then return the dataset back to the caller of the WebService. The caller
then merges the dataset back in (updating the identity columns correctly).
The problem is that I end up getting a duplicate row for each newly inserted
row. One row is valid while the original inserted row still exists with the
idenity column set to some negative value.

Thanks in advance for your help!
Terry

 

[Cor Ligthert [MVP]]

Terry,

A lot of people use before they do an update it like this.

MyAdapter.Update(MyDataTable.GotChanges)

In that case is a copy of the changed rows from MyDataTable used, it has in
my idea not so much benefits in normal situations where an acceptchanges has
to be done than extra.

But in my idea this fits perfectly to your problem.

I hope this helps,

Cor

 

[Guest]

Hi Cor,
Thanks for your reply! Your suggestion does not help though.

For my example consider that I have inserted 1 new row on the client. The
client side then passes MyDataTable.GetChanges() to a web service. The web
service then issues a
DataSet ds = TableAdapter.Update(dataSetContainingDataFromGetChanges);
return ds;

The issues is that the TableAdapter on update calls TableAdapter.Accept
changes. So now back on the client side when I merge back the dataset that
is return from the web service the merge succeeds but I end up with 2 rows.
One row is the originally inserted row with the temporary identity column
value (-1 in this case) while the other is the originally inserted row but
with it’s identity column updated with the real database value.

Thanks,
Terry

 

[Cor Ligthert [MVP]]

Twahl,

Than why are you not deleting the rows which are -1 or lower after the
update before the merge, just the idea that came into my mind reading your
reply.

Cor

 

[Guest]

Hi Cor,

That could possibly work as far as the dataset is concerned but would have
strange effect on the UI if the dataset was bound to say a grid and the
deleted row was selected.

My work around is to create a partial class for the dataset and expose the
private SqlDataAdapter AcceptChangesDuringUpdate as below. This works.

Thanks,
Terry

// Partial class for the TableAdapter. Simply double click on the dataset's
// xsd designer and enter code similiar to the following:
using System;

namespace Tcm.DataAccessLayer.ReportingDAL.ReportTypeDSTableAdapters
{
public partial class ReportTypeTableAdapter :
System.ComponentModel.Component
{
public bool AcceptChangesDuringUpdate
{
get
{
return Adapter.AcceptChangesDuringUpdate;
}
set
{
Adapter.AcceptChangesDuringUpdate = value;
}
}
}
}

Now in the Web Service I do the following:
[WebMethod]
public ReportTypeDS UpdateReportType(ReportTypeDS ds)
{
using (TransactionScope updateTransaction = new TransactionScope())
{
using (ReportTypeTableAdapter adapter = new
ReportTypeTableAdapter())
{
adapter.AcceptChangesDuringUpdate = false;
adapter.Update(ds);
}

updateTransaction.Complete();
return ds;
}
}

 

[Jim Rand]

The duplicate row issue, in my opinion, is one of .NET's biggest pain in the
neck issue. Microsoft says in one of their knowledge base articles, it's
"by design".

What you need to do is trap a row updated event.

foreach (System.Data.SqlClient.SqlDataAdapter da in adapterList)
{
da.RowUpdated += new
System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated);
}

and

static void da_RowUpdated(object sender,
System.Data.SqlClient.SqlRowUpdatedEventArgs e)
{
if (e.StatementType == System.Data.StatementType.Insert) e.Status =
System.Data.UpdateStatus.SkipCurrentRow;
}

You will also discover that the TableAdapter doesn't expose any events so
you have to dig into the TableAdapter to get access to the contained
(private) DataAdapter.

public partial class NetApplicationTableAdapter
{
public System.Data.SqlClient.SqlDataAdapter daNetApplication
{
get
{
this.Adapter.SelectCommand = this.CommandCollection[0];
return this.Adapter;
}
}
} /* public partial class NetApplicationTableAdapter */

 

[Cor Ligthert [MVP]]

Twahl,

Seems for me obvious, bus why than not unselect those rows first?

Cor

Hi Cor,

That could possibly work as far as the dataset is concerned but would have
strange effect on the UI if the dataset was bound to say a grid and the
deleted row was selected.

My work around is to create a partial class for the dataset and expose the
private SqlDataAdapter AcceptChangesDuringUpdate as below. This works.

Thanks,
Terry

// Partial class for the TableAdapter. Simply double click on the
dataset's
// xsd designer and enter code similiar to the following:
using System;

namespace Tcm.DataAccessLayer.ReportingDAL.ReportTypeDSTableAdapters
{
public partial class ReportTypeTableAdapter :
System.ComponentModel.Component
{
public bool AcceptChangesDuringUpdate
{
get
{
return Adapter.AcceptChangesDuringUpdate;
}
set
{
Adapter.AcceptChangesDuringUpdate = value;
}
}
}
}

Now in the Web Service I do the following:
[WebMethod]
public ReportTypeDS UpdateReportType(ReportTypeDS ds)
{
using (TransactionScope updateTransaction = new TransactionScope())
{
using (ReportTypeTableAdapter adapter = new
ReportTypeTableAdapter())
{
adapter.AcceptChangesDuringUpdate = false;
adapter.Update(ds);
}

updateTransaction.Complete();
return ds;
}
}

Twahl,

Than why are you not deleting the rows which are -1 or lower after the
update before the merge, just the idea that came into my mind reading
your
reply.

Cor

 

[Jim Rand]

If you don't catch the rowupdated event for the insert row, this is what the
dataset diffgram looks like on the web service:

<?xml version="1.0" standalone="yes"?>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<DSAuthrtyTmplt xmlns="http://tempuri.org/DSAuthrtyTmplt.xsd">
<PrimaryUserClass diffgr:id="PrimaryUserClass1" msdata:rowOrder="0">
<PrimaryUserClassID>35</PrimaryUserClassID>
<PrimaryUserClass>!X</PrimaryUserClass>
<Description>This is a test</Description>
<LastUpdatedBy>RANDWINXP001\Jim</LastUpdatedBy>
<TS>93105</TS>
</PrimaryUserClass>
</DSAuthrtyTmplt>
</diffgr:diffgram>
Notice it has knowledge of the primary key before the add.

If you do catch the rowupdated event, you get an entirely different
diffgram:

<?xml version="1.0" standalone="yes"?>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<DSAuthrtyTmplt xmlns="http://tempuri.org/DSAuthrtyTmplt.xsd">
<PrimaryUserClass diffgr:id="PrimaryUserClass1" msdata:rowOrder="0"
diffgr:hasChanges="modified">
<PrimaryUserClassID>34</PrimaryUserClassID>
<PrimaryUserClass>!X</PrimaryUserClass>
<Description>This is a test</Description>
<LastUpdatedBy>RANDWINXP001\Jim</LastUpdatedBy>
<TS>93104</TS>
</PrimaryUserClass>
</DSAuthrtyTmplt>
<diffgr:before>
<PrimaryUserClass diffgr:id="PrimaryUserClass1" msdata:rowOrder="0"
xmlns="http://tempuri.org/DSAuthrtyTmplt.xsd">
<PrimaryUserClassID>-1</PrimaryUserClassID>
<PrimaryUserClass>!X</PrimaryUserClass>
<Description>This is a test</Description>
<LastUpdatedBy>RANDWINXP001\Jim</LastUpdatedBy>
</PrimaryUserClass>
</diffgr:before>
</diffgr:diffgram>

Because you are not catching the rowupdated event, the client, on return
from your webservice, has no idea what the original row is.