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

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 
level.

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
>
>
>>>> jwcolby at colbyconsulting.com 10-08-12 14:38 >>>
> I have written a function to perform an insert using a passed in SQL string.  I need it to return an
> integer PK autoincremented by the table:
>
> public int mExecNonQueryReturnIdentity(string strCnn, string strSQL, int timeOut)
> {
> 	int intIdentity = 0;       //reset the records affected property before we execute
> 	SqlConnection mCnn = null;      //setup a sql connection object
> 	SqlCommand myCommand = null;        //setup a sql command object
>           try
>           {
>           	mCnn = new SqlConnection(strCnn);       //create a new sql connection using the connection
> string passed in
>                   mCnn.Open();        //open the connection
>                   myCommand = new SqlCommand(strSQL, mCnn);       //create a new command using the
> sql connection and sql statment
>                   myCommand.CommandTimeout = 60;     //set the command time out
>                   SqlParameter param = new SqlParameter("@ID", SqlDbType.Int, 4);
>                   param.Direction = ParameterDirection.Output;
>                   myCommand.Parameters.Add(param);
>
>                   myCommand.ExecuteNonQuery();     //execute the sql statement and set the records
> affected property
>                   intIdentity = (int)param.Value;
>        	}
>           catch (SqlException ex)
>           {
>           	logger.Error(ex.ToString());
>           }
> 	return intIdentity ;
> }
>
> The SQL statement looks like:
>
> string strSQLInsert = "INSERT INTO " + dbName + ".dbo." + tblName + " " +
> 	"(" +
>           "CSS_Name," +
>           "CSS_DteStart" +
>           ") " +
>           "VALUES " +
>           "(" +
>           "'DatabaseCrossReferenceCount_" + strNow + "'," +
>           "'" + strNow + "' " +
>           "); " +
>           "SELECT @@IDENTITY AS 'ID'"
>           ;
>
> PK = mExecNonQueryReturnIdentity(cnn, strSQLInsert, 600);
>
> The record is inserted into the table but param in mExecNonQueryReturnIdentity is {} which is null I
> suppose.  As a result i fall into the catch saying that the (int) "specified cast is not valid".  I
> am trying to cast a null to an int.  But the question is why is the returned value a null instead of
> an integer ID.
>
> There are a ton of hits on google about how to do this but they always break down into quarrels
> about "This is not safe and that is not safe and ...".  All the safe issues should not apply in my
> specific case because I am controlling the context by doing this from my C# code.
>
> I think the best solution seems to be doing the output parameter and passing something back which is
> the ID but I can't seem to figure out which part - the sql statement or the c# function (or both?)
> is in error.
>
> Any help greatly appreciated.
>