xile001
asked on
Using BeginTran/Commit Tran for MS SQL in c#
Hi All,
I need to know how to use the BeginTran and Commit Tran in C#.
Here's the basic background on the problem:
I have a webpage where the user can add new record. The record is inserted into a MS SQL DB. the table has a primary key.
What I need is to get the ID (primary key) of the record that was inserted.
I can get the last record that was added using the select Ident_current. So if I tie the Insert Into and the select ident_current into one transaction, I can get an accurate ID of the record.
So, how do I do this in .Net using C#?????
Please be kind and elobrate your comments
Thanks.
I need to know how to use the BeginTran and Commit Tran in C#.
Here's the basic background on the problem:
I have a webpage where the user can add new record. The record is inserted into a MS SQL DB. the table has a primary key.
What I need is to get the ID (primary key) of the record that was inserted.
I can get the last record that was added using the select Ident_current. So if I tie the Insert Into and the select ident_current into one transaction, I can get an accurate ID of the record.
So, how do I do this in .Net using C#?????
Please be kind and elobrate your comments
Thanks.
Except select statement how many insert/update statements do you have?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just want to mention, if you have just a single insert/update statement you don't need transaction to handle that.
If you want it in VB let me know.
Regards,
Aeros
Regards,
Aeros
ASKER
boulder_bum:
That's interesting. I didn't know that method return the value. That would works perfectly without me having to use the transaction.
though, I did find the information for using transaction in c#. So incase anyone needs it, here's the link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconperformingtransactionusingadonet.asp
That's interesting. I didn't know that method return the value. That would works perfectly without me having to use the transaction.
though, I did find the information for using transaction in c#. So incase anyone needs it, here's the link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconperformingtransactionusingadonet.asp
"I didn't know that method return the value."
Yeah. The method simply returns the first value returned, so if you have a statement like:
INSERT INTO --etc etc
SELECT @@IDENTITY AS MyNewIdentity
then ExecuteScalar will snag the value of "MyNewIdentity".
Yeah. The method simply returns the first value returned, so if you have a statement like:
INSERT INTO --etc etc
SELECT @@IDENTITY AS MyNewIdentity
then ExecuteScalar will snag the value of "MyNewIdentity".
ASKER
buolder_bum:
If I use the Insert into (val.....) values (x1,....), will it return the primary key of the inserted row using the execute scalar?
If I use the Insert into (val.....) values (x1,....), will it return the primary key of the inserted row using the execute scalar?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"will it return the primary key of the inserted row using the execute scalar"
It all depends on the SQL. The trick is not so much in ExecuteScalar (which simply selects a single value) as it is in the statement you use. After the INSERT, you want to have a little bit of SQL that returns the primary key like AerosSaga's "SELECT LAST_INSERT_ID()" or the identity selection I mentioned, "SELECT @@IDENTITY".
It all depends on the SQL. The trick is not so much in ExecuteScalar (which simply selects a single value) as it is in the statement you use. After the INSERT, you want to have a little bit of SQL that returns the primary key like AerosSaga's "SELECT LAST_INSERT_ID()" or the identity selection I mentioned, "SELECT @@IDENTITY".