[dba-VB] c#: Get identity value back from SQL Server

Gustav Brock gustav at cactus.dk
Mon Aug 13 04:01:17 CDT 2012

Hi John

The DataTableAdapter of Visual Studio is an abstraction layer above the SqlCommand-stuff which it uses - as you - to communicate with the SQL Server. The graphic layout mimics the relations' GUI of Access. It is ready at hand (nothing to install) and so simple and fast to use that I at once decided never to go back.  You can move forward to the Entity Framework but that is another story.

The important feature of the DataTableAdapter is that it turns all your tables into strongly typed classes. It is, of course, much nicer to work with classes, but you may argue that it is a feel-good feature not intended for real programmers. 
However, the strongly typed properties is the real feature. They have saved me countless hours of boring debugging because they catch all sorts of errors before they reach the database. In fact, if the classes "run", your database never fails.

The DataTableAdapter works on its own. It is not limited to or mainly intended for Winforms, although Winforms work great with it. For example, I've used them to build a custom synchronising class to sync between one SQL Server database and two other SQL Server databases and between these and two Access mdb files. The only GUI is a progress meter and a small form listing the log entries.

I don't blame you building on the code base you have developed for years. Anything else would be stupid. However, for new modules or features it sometimes pays off to look for other methods or tools.


>>> jwcolby at colbyconsulting.com 12-08-12 13:49 >>>
I think you have never mentioned it.

I was under the impression that running tsql statements against SQL Server was the way SQL Server 
was manipulated.  I have been doing this stuff for ages, running update queries and getting back the 
row count, running BCP queries to import or export data etc.  In all cases it is done with a C# 
command object and TSQL.

It appears to me that perhaps what you are doing is designed to bind to a form or grid maybe?  The 
stuff I am doing isn't bound in the normal sense and so I don't ever get down to the tableadapter 

In this case I have about 10 databases.  I have to do joins between all 10 databases and "each other 
database", joined on one of three fields - HashPerson, HashFamily and HashAddress - and then do 
counts on those joines.  Thus I am getting about 50 counts on three different hash levels or about 
150 counts.

The program that does this was designed originally to simply write the results into a spreadsheet. 
Visualize a spreadsheet with three sections down the sheet, a HashPerson, HashFamily and HashAddress 
section. Each section has a line under that for each database, and columns for the counts for the 
joins against all other databases.  So if I have 8 databases there are 8 rows, one for each 
database, and 7 columns, one for each "other" database.  Each row/column junction cell holds the 
count of that pair of databases joined on that hash level.  Obviously I have to do counts (TSQL) 
against (in this example) of 8*7 row/columns * 3 (hash levels) and as each count completes it has to 
write into the spreadsheet.  BTW these tables are anywhere from 5 million records to 220 million 
records so these counts take literally all night to run.

I am now retrofitting the program to also write the results into a matching set of tables - 
Spreadsheet / HashLevel / LineLevel (within the hash level) / JunctionCount.  Doing that I will then 
be able to "read out" any count spreadsheet from the past, do analysis against counts over time etc.

But no, I do not use table adapters in the manner that you seem to be doing so.  I do everything I 
do with connection / command objects executing dynamically constructed TSQL statements.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 8/12/2012 4:44 AM, Gustav Brock wrote:
> Hi John
> I think I have asked this before, but I just don't get why you of all mess around with this low level stuff when class handling in several varietes are right at your hand - like the DataTableAdapters which really aren't very "advanced" but very handy:
> <C#>
> mediaCutRow = _mediaCutDataTable.NewMediaCutRow();
> mediaCutRow.MediaIssueSectionPageId = _mediaIssueSectionPageId;
> mediaCutRow.CutNumber = tempCutNumber;
> // Add the new cut.
> _mediaCutDataTable.AddMediaCutRow(mediaCutRow);
> _mediaCutTableAdapter.Update(_mediaCutDataTable);
> // Retrieve assigned new Id of mediaCutRow.
> _mediaCutId = mediaCutRow.Id;
> </C#>
> /gustav

More information about the dba-VB mailing list