Synchronize the identity columns with SQL server

[dusty]

Hi, I'll try to simplify the problem:

I created a table "TestTable" in a database on the SQL server. The first column, 'id', is the primary key with a auto-increment identity. I want to work connection-less, so I made my dataset where I populated a table with the data from the TestTable. When I insert a new row with the .NewRow() method, the identity column value is the next value available. For instance: if the last row had the value of 105, the inserted rows identity value will be 106, and so on.
But if I deleted a few last rows in the table in the database (in example rows with identity values 106 and 107), after executing a SQL insert statement the new inserted rows identity value will 108, not 106 ! I hope I as clear. For the sake of simplicity I ommited why it is important to me to have the 'real' value of the identity before updating to the database.

How can I keep these values synchronized, without having to update the datasource after every inserted row in the dataset table ?

Thanks.

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

That's pretty much impossible, IF there are 2 clients using the same DB,
both will generate the same PKs in theirs dataset, at the update at least
one will have problems with the currently used values and those in the DB.

Other thing that will affect your described escenario are the deleted rows,
if you can delete them you will create "holes" in the PK, and this is pretty
much unavoidable I think.

If you don;t want to leave "holes" in the PK numbering don;t delete the
rows, just use a bool column and mark the rows as valid/invalid

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"dusty" <du***@discussions.microsoft.com> wrote in message
news:E1**********************************@microsof t.com...

Hi, I'll try to simplify the problem:

I created a table "TestTable" in a database on the SQL server. The first column, 'id', is the primary key with a auto-increment identity. I want to
work connection-less, so I made my dataset where I populated a table with
the data from the TestTable. When I insert a new row with the .NewRow()
method, the identity column value is the next value available. For instance:
if the last row had the value of 105, the inserted rows identity value will
be 106, and so on. But if I deleted a few last rows in the table in the database (in example rows with identity values 106 and 107), after executing a SQL insert
statement the new inserted rows identity value will 108, not 106 ! I hope I
as clear. For the sake of simplicity I ommited why it is important to me to
have the 'real' value of the identity before updating to the database.
How can I keep these values synchronized, without having to update the datasource after every inserted row in the dataset table ?
Thanks.

[Nick Malik]

I disagree... it is not impossible to coordinate this using connectionless
methods... but it *is* a design issue, not a code issue.

Design issue: Don't use autonumber fields in the database. Use the
UniqueIdentifier datatype.

Code implementation: Have the client code generate the GUID, and place it
into the correct column both in the primary and secondary tables.

Then, simply insert the data into the correct tables.

No need to maintain a connection.

This may not be feasable for the OP, if he or she has an existing database
that cannot be changed. However, if this is new development, this is a much
better way to coordinate adds due to lower costs of connection and more
scalable implementation.

--- Nick

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:el**************@TK2MSFTNGP09.phx.gbl...

Hi,

That's pretty much impossible, IF there are 2 clients using the same DB, both will generate the same PKs in theirs dataset, at the update at least
one will have problems with the currently used values and those in the DB.

Other thing that will affect your described escenario are the deleted rows, if you can delete them you will create "holes" in the PK, and this is pretty much unavoidable I think.

If you don;t want to leave "holes" in the PK numbering don;t delete the
rows, just use a bool column and mark the rows as valid/invalid

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"dusty" <du***@discussions.microsoft.com> wrote in message
news:E1**********************************@microsof t.com...

Hi, I'll try to simplify the problem:

I created a table "TestTable" in a database on the SQL server. The first column, 'id', is the primary key with a auto-increment identity. I want to
work connection-less, so I made my dataset where I populated a table with
the data from the TestTable. When I insert a new row with the .NewRow()
method, the identity column value is the next value available. For

instance: if the last row had the value of 105, the inserted rows identity value will be 106, and so on.

But if I deleted a few last rows in the table in the database (in

example rows with identity values 106 and 107), after executing a SQL insert
statement the new inserted rows identity value will 108, not 106 ! I hope I as clear. For the sake of simplicity I ommited why it is important to me to have the 'real' value of the identity before updating to the database.

How can I keep these values synchronized, without having to update the

datasource after every inserted row in the dataset table ?

Thanks.

[dusty]

> Code implementation: Have the client code generate the GUID, and place it

into the correct column both in the primary and secondary tables.

Thats it !!!! Thanks a million ! Thats what i've been looking for !!
Allthough I've kept the autoincrement, I use the GUID only temporary, to maintain the the releation in the dataset, untill its updated.

Thanks again !!!!