Offline databases???

[Kurt]

General question:

 

To keep this simple, suppose I have a database with 1 table. The primary key in the table is autogenerated by the database for each new entry. If I now fill a datatable in a dataset with the entries of the table using a dataadapter and I want to add a row, does ADO.NET then generate the unique primary key itself?

More over, if 2 users do the same.... they both add a record in the datatable - then they probably get the same 'unique' key created in their off-line dataset. What happens when the underlying database table is updated? Does ADO.NET know that the new entries each should get a new unique number? And what if there is another table that has the primary key of the first table as a foreign key? Will ADO.NET add a new entry in the first table for the first user - and use the key assigned by that opperation in the depending table - while the second user will get another unique key in the first table and use that one as the foreign key in the second table?

[Roey]

The database still maintains the primary keys, and will assign one on the dataadapter.update

[Kurt]

So in their local dataset, two users could get/fill in the same value in the primary key field for newly created entries, one on one users machine and the other one on the others pc, but when the update methode is called by both users, the dataatapter will resolve the problem and make sure that the primary key is unique for each entry? Or will the database simply reject the update action?

[Roey]

The DataAdapter resolves the issue if two users have the same screen open and add items at the same time. Each item will get its own unique Primary key. Sorry, but not too sure how this works, I find disconnected DataSets a hard thing to figure out sometimes.....

[hog]

The unique value is created by the database at the time that the record is written back to it, not before.

 

Two records would not be created with the same value as the database controls this.

 

If you have two users open the same record, then the first makes changes then writes it back when the second attempts to write their changes back the optimistic concurrency which you should have in place will inform the second user that they are unable to save their changes.

[Kurt]

Ok, but what if I get some person data like

 

Name

Address

City

Country

Phone1

.....etc.

 

Suppose I keep Countries with their Cities in a separate table that has a CityID field (autogenerated by SQL) for each row. In the Persons table I simply have this CityID as a foreign key to save the city and country the person lives in.

 

If a user types in a combination of a city and a country that is not know in the database yet (assume that his input is correct), what is to be done then? Should I first take the data about Country and City in a dataTable in the dataset, then update the underlying database which will create a unique CityID for the new entry and then in some way (how???) retreive this unique value so it can be written to the 'CityID'-field of the new person's entry in my offline dataset (as foreign key)?

 

In that way I will need to connect every time a new City/Country combination is filled in by a user - so the offline dataset would pretty much lose its value. This goes by the way for all 'related' information in the database... I know that after a while (a matter of time) most Countries/Cities combinations will be known so the ID field can be collected from the offline dataTable, but as relational databases are build on the principle of relations I can imagion that these situation must occure quiet frequently.

[bri189a]

I highly recommend you spend the money on Microsoft Press ADO.NET book... it will explain how all of that works in great detail. It's worth every penny IMO. Basically when dealing with PrimaryKeys you let the database take care of that, you just set up in your DataTable that this particular column is the PrimaryKey, when you call the Update method it will create a Key for you in the database and if you are using Access as your database you will need to call Fill again which will update your dataset, if you are using SQL then you should use a parametized query that has a return which will give you the PrimaryKey created by the database. If you are using relational tables with cascation you shouldn't have much of a problem... your only issue would be that which Hog mentioned in which case you ask the user what to do or handle it via code. And yes, if your entering a lot of stuff and design it so, you will need to do a lot of connections back and forth - but you could always have the connection update when they're done entering data, but the great thing about ADO.NET and DataSets is that you don't have physically type myConn.Open() each time and myConn.Close, the DataAdapter does it for you, and since ADO.NET uses connection pooling its not as resource intensive as you would think (such as it would be with regular ADO 2.X).

[FartNocker]

'I highly recommend you spend the money on Microsoft Press ADO.NET book... it will explain how all of that works in great detail. 

 

bri189a, Could give the exact name of the book you are referring to here or maybe the ISBN. Or anyone else that knows of a good ADO. NET book that deals alot with OLE DB for desktop applications.

 

Mark