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

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.

-- 
John W. Colby
Colby Consulting