displaying an auto-incremented SQL identity column

[BobLewiston]

Most databases have multiple users. For these databases it is a good idea to auto-increment the identity column.

I understand that an SQL identity column is not incremented until the newly-created record is inserted.

I guess this means the identity column is incremented when you actually reconnect to the database and insert the new record there, rather than just add the record to the locally-resident DataSet, correct?

And I guess this also means that for auto-incremented identity columns it is not possible to accurately display the newly-incremented identity column in the data entry WinForm used to create the new record and add it to the database, right?

For what it's worth, the environment I'm working in is:

32-bit
SQL Server 2008 Express with Advanced Services
SQL2008 AdventureWorks
SQL Server 2008 Management Studio Express
Visual C# 2008 Express

 

[Daeva]

You can retreive the identity of the most recently inserted record by using the @@identity property (in SQL Server), but this can't be done until the database has actually had a record inserted into it.

 

[BobLewiston]

Daeva:

Even if I did that, it would probably take at least a minute to manually type in the data in the new record that's going to be inserted, and within that minute a number of other records may have been inserted into the database by other data entry people, so that the identity column displayed in any record that's in the process of being input could very easily be out of date by the time that record is ready to actually be inserted into the database.

So I guess what I'm saying is that it would most probably be unwise to even attempt to display an identity column value for a new record that's going to be inserted, right?

 

[Daeva]

I would say that would be very unwise (and maybe a little bit unnecessary?). As a "workaround" you could run a select statement on the current database and sort the records in descending order by the identity column, then grab the first row returned (which would be your newest record) and then add 1 to that and display this number.

But as you suggested, this information becomes inaccurate if somebody else is also modifying the database. Is there a big reason you chose to use a dataset as opposed to a custom library to communicate with the database?

 

[BobLewiston]

Daeva:

Is there a big reason you chose to use a dataset as opposed to a custom library to communicate with the database?

Because I'm a newbie and I'm just trying to follow an online tutorial, and that's what the tutorial teaches.

What can you tell me about applicable custom libraries and their advantages (and disadvantages) compared to datasets, and what options in general are available for accessing SQL databases? I understand that it is possible to communicate "directly" with databases (rather than initialize and use canned command strings like dataAdapter.UpdateCommand.CommandText and dataAdapter.InsertCommand.CommandText), and that this approach is said to be simpler. Can you suggest the simplest way to accessing SQL databases, with perhaps a few words about more sophisticated ways to do so, under what circumstances such more sophisticated methods might be necessary, and a little of the syntax I would need for these methods, so that I can research them after I master the simplest methods? Any help you could give would be greatly appreciated.