TableAdapter Preserve Original Values on Update (no Accept Changes  
Author Message
JohnMason





PostPosted: Thu Oct 26 23:06:39 CDT 2006 Top

ADO >> TableAdapter Preserve Original Values on Update (no Accept Changes

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

DotNet80  
 
 
Cor





PostPosted: Thu Oct 26 23:06:39 CDT 2006 Top

ADO >> TableAdapter Preserve Original Values on Update (no Accept Changes 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



> 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


 
 
twahl





PostPosted: Fri Oct 27 07:09:01 CDT 2006 Top

ADO >> TableAdapter Preserve Original Values on Update (no Accept Changes 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





> 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
>


> > 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





PostPosted: Fri Oct 27 12:27:55 CDT 2006 Top

ADO >> TableAdapter Preserve Original Values on Update (no Accept Changes 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



> 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
>
>
>

>
>> 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
>>


>> > 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
>>
>>
>>


 
 
twahl





PostPosted: Fri Oct 27 14:47:01 CDT 2006 Top

ADO >> TableAdapter Preserve Original Values on Update (no Accept Changes 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
>


> > 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
> >
> >
> >

> >
> >> 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
> >>


> >> > 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
> >>
> >>
> >>
>
>
>
 
 
Jim





PostPosted: Fri Oct 27 15:09:58 CDT 2006 Top

ADO >> TableAdapter Preserve Original Values on Update (no Accept Changes 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





PostPosted: Sat Oct 28 00:48:15 CDT 2006 Top

ADO >> TableAdapter Preserve Original Values on Update (no Accept Changes 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
>>


>> > 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
>> >
>> >
>> >

>> >
>> >> 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
>> >>


>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>


 
 
Jim





PostPosted: Sat Oct 28 08:18:50 CDT 2006 Top

ADO >> TableAdapter Preserve Original Values on Update (no Accept Changes 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.