strongly typed dataset update with identity column

[Boblemar]

Hi all,

I've been struggeling for days on the following problem :
- I Have a table into SQL Server with an identity column (Auto increment)
- I created a strongly typed dataset using this table (Dataset is named
_DSTest and the datatable is called Test)

I want to populate the table and get back the identity values.

Here is my code :

Using ta As New _DSTestTableAdapters.TestTableAdapter
ta.Fill(ds.Test)

ds.Test.AddTestRow(ds.Test.NewTestRow())

Dim dtChanges As DataTable = ds.Test.GetChanges()
If dtChanges IsNot Nothing Then
Dim dt As _DSTest.TestDataTable = CType(dtChanges,
_DSTest.TestDataTable)

ta.Update(dt)
ds.Merge(dt)
ds.AcceptChanges()
Console.WriteLine(ds.Test.Rows(ds.Test.Count - 2))
Console.WriteLine(ds.Test.Rows(ds.Test.Count - 1))
End If
End Using

The problem is after merging the initial dataset, I have 2 rows in the
datatable :
- one with the value created by the database
- the other one with -1 (AutoIncrementSeed=-1 in my typed dataset).

I believe the problem is that when merging, the system doesn't see the 2
rows are the same...

How Can I solve the issue to have only One Row ?
I found 2 ways to do this :
- putting ds.RedjectChanges() before updating
- Or just don't deal with GetChanges, just update ds : ta.Update(ds)

But I believe there's a better way to do this. Can someone help me ?

Thanks

Bob

 

[Jim Rand]

By default, AcceptChanges() is called on the row. Take a look at the
diffgrams. Very interesting.

/* Skip current row for statement type insert. Necessary to prevent adding a
new row for
* each insert instead of changing the autoincrement primary key */
foreach (System.Data.SqlClient.SqlDataAdapter da in adapterList)
{
da.RowUpdated += new
System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated);
}

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

 

[Cowboy \(Gregory A. Beamer\)]

Right click on the table and create a new query. Choose insert query. This
automatically sets it up to return the entire row where id = SCOPE_IDENTITY.
You can also create a sproc for this.

If you have already invested in an architecture that uses the standard
insert, you can add a query that pulls the data back out. If you go this
route, it is hopeful you have a natural key in the data (some other unique
item). If so, just make a query to return based on that value. Example:

public UnitWarehouseDS.UnitRow GetByImei(string imei)
{
var table = new UnitWarehouseDS.UnitDataTable();
UnitTableAdapter adapter = GetAdapter(_connectionString);
adapter.FillByImei(table, imei);

if (table.Rows.Count == 0)
return null;

return (UnitWarehouseDS.UnitRow)table.Rows[0];
}

In the above, the IMEI is unique to units that communicate via cell phone
modems. The pattern is not perfected yet, but I have covered it here:
http://gregorybeamer.spaces.live.com/blog/cns!B036196EAF9B34A8!974.entry

It is a work in progress, but allows for code generation, which makes it
nice.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://feeds.feedburner.com/GregoryBeamer#

or just read it:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think outside the box! |
********************************************

 

[Boblemar]

Thank you Cowboy for your answer.

I am using Visual Studio 2005 .NET 2.0, and in fact, the insert command
that was generated by the designer pulls the data back in my dataset by
selecting using scope_identity after inserting.

My problem is that if I do an update using a tableadapter with
dataset.GetChanges() for parameter, the data are pulled back into
dataset.GetChanges() but not in dataset.
So I found on the web (msdn) that the dataset returned by
dataset.GetChanges() was designed to be able to be nicely merged into
the original dataset, and so the data would be pulled back to original
dataset. But in my sample, it doesn't work as assumed : for 1 row
inserted into the database, I get 2 rows in my dataset :
- The one with the auto-increment back from the database (the good one)
- The one the I put initially in the dataset that I wanted the
tableadapter to insert into the database (with an identity column set
arbitrary to -1)
But the 2 rows are the same. So I want to have only 1 !

I get it work passing the entire dataset (and not anly GetChanges) to
the adapter, but I think it is better to use GetChanges...


Cowboy (Gregory A. Beamer) a écrit :

 

[Pierre T.]

Hi!

What you need to do is to delete the rows with the DataRowState.Added in the
original dataset before doing the merge.

Thank you Cowboy for your answer.

I am using Visual Studio 2005 .NET 2.0, and in fact, the insert command
that was generated by the designer pulls the data back in my dataset by
selecting using scope_identity after inserting.

My problem is that if I do an update using a tableadapter with
dataset.GetChanges() for parameter, the data are pulled back into
dataset.GetChanges() but not in dataset.
So I found on the web (msdn) that the dataset returned by
dataset.GetChanges() was designed to be able to be nicely merged into the
original dataset, and so the data would be pulled back to original
dataset. But in my sample, it doesn't work as assumed : for 1 row inserted
into the database, I get 2 rows in my dataset :
- The one with the auto-increment back from the database (the good one)
- The one the I put initially in the dataset that I wanted the
tableadapter to insert into the database (with an identity column set
arbitrary to -1)
But the 2 rows are the same. So I want to have only 1 !

I get it work passing the entire dataset (and not anly GetChanges) to the
adapter, but I think it is better to use GetChanges...


Cowboy (Gregory A. Beamer) a écrit :

Right click on the table and create a new query. Choose insert query.
This automatically sets it up to return the entire row where id =
SCOPE_IDENTITY. You can also create a sproc for this.

If you have already invested in an architecture that uses the standard
insert, you can add a query that pulls the data back out. If you go this
route, it is hopeful you have a natural key in the data (some other
unique item). If so, just make a query to return based on that value.
Example:

public UnitWarehouseDS.UnitRow GetByImei(string imei)
{
var table = new UnitWarehouseDS.UnitDataTable();
UnitTableAdapter adapter = GetAdapter(_connectionString);
adapter.FillByImei(table, imei);

if (table.Rows.Count == 0)
return null;

return (UnitWarehouseDS.UnitRow)table.Rows[0];
}

In the above, the IMEI is unique to units that communicate via cell phone
modems. The pattern is not perfected yet, but I have covered it here:
http://gregorybeamer.spaces.live.com/blog/cns!B036196EAF9B34A8!974.entry

It is a work in progress, but allows for code generation, which makes it
nice.

 

[Boblemar]

Thank you,

I can do ds.RedjectChanges() that will undo every changes on the initial
dataset and merge with the getchanges dataset... I just wondered
whether is is the good way or not.
The other solution I found was to update using the initial dataset, but
I think it may be heavier...

Pierre T. a écrit :

Hi!

What you need to do is to delete the rows with the DataRowState.Added in
the original dataset before doing the merge.

Thank you Cowboy for your answer.

I am using Visual Studio 2005 .NET 2.0, and in fact, the insert
command that was generated by the designer pulls the data back in my
dataset by selecting using scope_identity after inserting.

My problem is that if I do an update using a tableadapter with
dataset.GetChanges() for parameter, the data are pulled back into
dataset.GetChanges() but not in dataset.
So I found on the web (msdn) that the dataset returned by
dataset.GetChanges() was designed to be able to be nicely merged into
the original dataset, and so the data would be pulled back to original
dataset. But in my sample, it doesn't work as assumed : for 1 row
inserted into the database, I get 2 rows in my dataset :
- The one with the auto-increment back from the database (the good one)
- The one the I put initially in the dataset that I wanted the
tableadapter to insert into the database (with an identity column set
arbitrary to -1)
But the 2 rows are the same. So I want to have only 1 !

I get it work passing the entire dataset (and not anly GetChanges) to
the adapter, but I think it is better to use GetChanges...


Cowboy (Gregory A. Beamer) a écrit :

Right click on the table and create a new query. Choose insert query.
This automatically sets it up to return the entire row where id =
SCOPE_IDENTITY. You can also create a sproc for this.

If you have already invested in an architecture that uses the
standard insert, you can add a query that pulls the data back out. If
you go this route, it is hopeful you have a natural key in the data
(some other unique item). If so, just make a query to return based on
that value. Example:

public UnitWarehouseDS.UnitRow GetByImei(string imei)
{
var table = new UnitWarehouseDS.UnitDataTable();
UnitTableAdapter adapter = GetAdapter(_connectionString);
adapter.FillByImei(table, imei);

if (table.Rows.Count == 0)
return null;

return (UnitWarehouseDS.UnitRow)table.Rows[0];
}

In the above, the IMEI is unique to units that communicate via cell
phone modems. The pattern is not perfected yet, but I have covered it
here:
http://gregorybeamer.spaces.live.com/blog/cns!B036196EAF9B34A8!974.entry

It is a work in progress, but allows for code generation, which makes
it nice.

 

[Jim Rand]

Try the following experiment:

1) Load a dataset with some data. Bind a grid to this.
2) Add some rows, modify some rows and delete some rows.
3) Create a new dataset (or 3) using the GetChanges() method.

System.Data.DataSet dsDeleted =
ds.GetChanges(System.Data.DataRowState.Deleted);
System.Data.DataSet dsAdded = ds.GetChanges(System.Data.DataRowState.Added);
System.Data.DataSet dsModified =
ds.GetChanges(System.Data.DataRowState.Modified);

Do your updates and yes, get the indentity back with the scope_indentity
select

4) Write out the diffgram with

dsAdded.WriteXml(@"C:\Tmp\add.xml", XmlWriteMode.DiffGram);

You will immediately see why you are getting a duplicate row for rows added.

5) Do as I last suggested and again write out the diffgram.

You will immediately see why you don't get the duplicate row.

Thank you Cowboy for your answer.

I am using Visual Studio 2005 .NET 2.0, and in fact, the insert command
that was generated by the designer pulls the data back in my dataset by
selecting using scope_identity after inserting.

My problem is that if I do an update using a tableadapter with
dataset.GetChanges() for parameter, the data are pulled back into
dataset.GetChanges() but not in dataset.
So I found on the web (msdn) that the dataset returned by
dataset.GetChanges() was designed to be able to be nicely merged into the
original dataset, and so the data would be pulled back to original
dataset. But in my sample, it doesn't work as assumed : for 1 row inserted
into the database, I get 2 rows in my dataset :
- The one with the auto-increment back from the database (the good one)
- The one the I put initially in the dataset that I wanted the
tableadapter to insert into the database (with an identity column set
arbitrary to -1)
But the 2 rows are the same. So I want to have only 1 !

I get it work passing the entire dataset (and not anly GetChanges) to the
adapter, but I think it is better to use GetChanges...


Cowboy (Gregory A. Beamer) a écrit :

Right click on the table and create a new query. Choose insert query.
This automatically sets it up to return the entire row where id =
SCOPE_IDENTITY. You can also create a sproc for this.

If you have already invested in an architecture that uses the standard
insert, you can add a query that pulls the data back out. If you go this
route, it is hopeful you have a natural key in the data (some other
unique item). If so, just make a query to return based on that value.
Example:

public UnitWarehouseDS.UnitRow GetByImei(string imei)
{
var table = new UnitWarehouseDS.UnitDataTable();
UnitTableAdapter adapter = GetAdapter(_connectionString);
adapter.FillByImei(table, imei);

if (table.Rows.Count == 0)
return null;

return (UnitWarehouseDS.UnitRow)table.Rows[0];
}

In the above, the IMEI is unique to units that communicate via cell phone
modems. The pattern is not perfected yet, but I have covered it here:
http://gregorybeamer.spaces.live.com/blog/cns!B036196EAF9B34A8!974.entry

It is a work in progress, but allows for code generation, which makes it
nice.