Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 978
  • Last Modified:

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.
0
xile001
Asked:
xile001
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
ihenryCommented:
Except select statement how many insert/update statements do you have?
0
 
boulder_bumCommented:
You'd get the value returned from the final SELECT of your SQL/Sproc by using SqlCommand.ExecuteScalar() which returns a single value. The INSERT will still execute, you'll just get a value back in addition.
0
 
ihenryCommented:
Just want to mention, if you have just a single insert/update statement you don't need transaction to handle that.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
AerosSagaCommented:
If you want it in VB let me know.

Regards,

Aeros
0
 
xile001Author Commented:
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
0
 
boulder_bumCommented:
"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".
0
 
xile001Author Commented:
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?

0
 
AerosSagaCommented:
Yes it can be done like so:

cmd.CommandText = "INSERT INTO ShippingAddresses (FirstName, LastName, Address1, Address2, City, State, Zip, Country)" & _
                    " Values ('" & CStr(Session("BillFirstName")) & "','" & _
            CStr(Session("BillLastName")) & "','" & CStr(Session("BillAddress1")) & "','" & CStr(Session("BillAddress2")) & "','" & _
            CStr(Session("BillCity")) & "','" & CStr(Session("BillState")) & "','" & CStr(Session("BillZip")) & "','" & _
            CStr(Session("BillCountry")) & "')"
            End If
            cmd.ExecuteNonQuery()
            cmd.CommandText = "SELECT LAST_INSERT_ID() AS ShippingAddressID"
            cmd.ExecuteScalar()
            strShippingAddressID = CStr(cmd.ExecuteScalar())
            Me.lblShippingAddressID.Text = strShippingAddressID
            cmd.CommandText = "INSERT INTO Orders (CustomerID, OrderID, Total, Shipping, Tax, OrderDate, AuthNetAuthCode, AuthNetTransID, CCType, ShipAddressID)" & _
            " Values ('" & Me.lblCustomerID.Text.ToString & "','" & Me.lblTrackingID.Text.ToString & "','" & Me.lblOrderSubTotal.Text.ToString & "','" & _
            Me.lblTotalTax.Text & "','" & Me.lblShippingTotal.Text & "','" & ThisDay & "','" & "" & "','" & "" & "','" & Me.ddlCreditType.SelectedValue & "','" & Me.lblShippingAddressID.Text & "')"
            'Insert Null AuthNet Codes to create OrderTrackingID, will update after transaction
            cmd.ExecuteNonQuery()
            cmd.CommandText = "SELECT LAST_INSERT_ID() As OrderTrackingID"
            cmd.ExecuteScalar()
            strOrderTrackingID = CStr(cmd.ExecuteScalar())
            Me.lblOrderTrackingID.Text = strOrderTrackingID

Regards,

Aeros
0
 
boulder_bumCommented:
"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".
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now