[MSSQL] [WebService] Last inserted ID in transaction

11 Apr 2007 8:37 PM

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

11 Apr 2007 10:05 PM

macleod

I may be wrong, but doesn't the transaction have to commit to the table
before an actual identity is created? If that's the case, then all you
would ahve to do is move the select scope_identity below the
transaction.commit() statement.

Show quoteHide quote

"Robert Wachtel" <rwach***@gmx.de> wrote in message
news:uEuO8kHfHHA.284@TK2MSFTNGP05.phx.gbl...
> 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
>

11 Apr 2007 10:46 PM

Robert Wachtel

Hi macleod!

macleod wrote:

>I may be wrong, but doesn't the transaction have to commit to the table
>before an actual identity is created? If that's the case, then all you
>would ahve to do is move the select scope_identity below the
>transaction.commit() statement.

That makes sense. I'll give it a try... ;)

Thanks.

Robert

11 Apr 2007 10:27 PM

Norman Yuan

idcmd.ExecuteScalar() should return nothing else than null: in your
transaction you have at least two commands to execute, each has its own
scope: one or more command inside da.Update(), depending how many rows to be
update, and one command to try to retrieve an ID, which does not exists
because within the scope of this command's execution, no ID is generated.

In your case, you want get the row ID when a new row is updated to database
by dataadapter. So, you need to customize the dataadapter's InsertCommand
and/or handle DataAdapter's RowUpdated event to retrieve the ID. The easiest
way is to include a output parameter in the dataadapter's InsertCommand and
have the InsertCommand execute an inserting stored procedure, while the
stroed procedure fills an output parameter with SCOPE_IDENTITY(). This way,
since the inserting and calling SCOPE_IDENTITY() are done in the same stored
procedure (the same execution scope), you then can get correct ID returned.
If your inserting and retrieving ID execution are not in the same scope, the
ID returned may not be correct one!

Show quoteHide quote

"Robert Wachtel" <rwach***@gmx.de> wrote in message
news:uEuO8kHfHHA.284@TK2MSFTNGP05.phx.gbl...
> 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
>

11 Apr 2007 10:45 PM

Robert Wachtel

Hi Norman!

Norman Yuan wrote:

> idcmd.ExecuteScalar() should return nothing else than null: [...]
> because within the scope of this command's execution, no ID is generated.

Ahh yes, I see.

> [...] So, you need to customize the dataadapter's InsertCommand and/or
> handle DataAdapter's RowUpdated event to retrieve the ID. [...]

Do you have - by any chance - an example for retrieving the ID with the
RowUpdated event?

Is it possible to modify InsertCommands created by CommandBuilder? My
example was a cut down of a bigger in-house framework. The insert method
should work with many tables and so I try to avoid stored procedures.

Thanks again.

Robert

12 Apr 2007 8:58 AM

Robert Wachtel

Hi @all!

What about using SELECT IDENT_CURRENT() in a transaction? Is it safe?

[...]
     da.Update( ds.Tables[0] );
     // get last inserted id
     SqlCommand idcmd = new SqlCommand( "SELECT IDENT_CURRENT ('" +
ds.Tables[0].ToString() + "')", con );
     idcmd.Transaction = trans;
     int lastID = Convert.ToInt32( idcmd.ExecuteScalar() );
     trans.Commit();
     return lastID;
[...]

This seems to work. Any pitfals here?

tia

Robert