[MSSQL] [WebService] Last inserted ID in transaction

R

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
 
M

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

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!


Robert Wachtel said:
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
Click to expand...
 
R

Robert Wachtel

Hi Norman!

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

Ahh yes, I see.
[...] So, you need to customize the dataadapter's InsertCommand and/or
handle DataAdapter's RowUpdated event to retrieve the ID. [...]
Click to expand...

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
 
R

Robert Wachtel

Hi 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.
Click to expand...

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

Thanks.

Robert
 
R

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
 
L

Levent Alpsal

This works fine with MsSql 2005 and seems to be very reliable

SqlCommand cmd = new SqlCommand("INSERT INTO surveys (survey_name, survey_active) OUTPUT INSERTED.survey_id VALUES (@survey_name, 0) ", con);
cmd.Parameters.AddWithValue("@survey_name", txtSurveyName.Text);
con.Open();
string insertedSurveyId = cmd.ExecuteScalar().ToString();
con.Close();



Robert Wachtel wrote:

[MSSQL] [WebService] Last inserted ID in transaction
11-Apr-07

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 connectio
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 i
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 greeting

Robert

EggHeadCafe - Software Developer Portal of Choice
Crystal Report And Parameter Passing Using Stored Procedure
http://www.eggheadcafe.com/tutorial...c-569d6ea46488/crystal-report-and-parame.aspx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SqlDataAdapter Fill Dataset is slow 6
ADO.Net transaction and ExecuteDataset 1
DAL technique - which is better 4
The root transaction wanted to commit, but transaction aborted 1
System.OutOfMemoryException in DataAdapter 4
ListView DataView, it shows me this letters "System.Data.Row.View" 1
Error creating connection class 6
Why is this not this cmd.Parameters.AddWithValue working 3

Top