[MSSQL] [WebService] Last inserted ID in transaction

[Robert Wachtel]

Hi!

I'm in desperate need for help.

Given is a table on SQL Server 2005 (T_IDTest with two columns: ID autoid
and Text varchar).

I implemented a webservice with two methods. The first method gives an empty
dataset, so the client can append a row to this dataset.

[WebMethod]
public DataSet GetEmptyDataSet() {
string scon = "Data Source=(local);Initial Catalog=Test; Integrated
Security=SSPI;";
try {
// create MS SQL connection
using ( SqlConnection con = new SqlConnection( scon ) ) {
con.Open();
SqlDataAdapter da = new SqlDataAdapter( "SELECT ID, Text FROM T_IDTest
WHERE 1=0", con );
DataSet ds = new DataSet( "IDTest" );
da.Fill( ds, "T_IDTest" );
return ds;
}
}
catch {
return null;
}
}

The other method takes the modified dataset writes the changes to the
database and should return the last inserted autoid.

[WebMethod]
public int InsertDataSet( ref DataSet ds ) {
string scon = "Data Source=(local);Initial Catalog=Test; Integrated
Security=SSPI;";
try {
using ( SqlConnection con = new SqlConnection( scon ) ) {
con.Open();
SqlTransaction trans = con.BeginTransaction(
IsolationLevel.ReadCommitted );
try {
SqlDataAdapter da = new SqlDataAdapter( "SELECT ID, Text FROM
T_IDTest", con );
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.SelectCommand.Transaction = trans;
SqlCommandBuilder cb = new SqlCommandBuilder( da );
da.Update( ds.Tables[0] );
// get last inserted id
SqlCommand idcmd = new SqlCommand( "SELECT SCOPE_IDENTITY()", con );
idcmd.Transaction = trans;
int lastID = Convert.ToInt32( idcmd.ExecuteScalar() );
trans.Commit();
return lastID;
}
catch {
try {
trans.Rollback();
return -1;
}
catch {
return -2;
}
}
}
}
catch {
return -1;
}
}

But idcmd.ExecuteScalar() returns null.

If I skip idcms.ExecuteScalar() the data is written to the database as
desired.

So the problem I have is retrieving the last inserted ID.

Someone out here with an idea or a hint?

btw: This is a very cut-down sample. In the real application this should
work with variable tables - therefore the command builder can not be
replaced (at least I have no idea for another solution <g>).

Thanks in advance and greetings

Robert