Using transaction to execute multiple queries

Question

0.00/5 (No votes)

See more:

How can I use transaction to run the code below, I want the data to rowback if any of the queries is not executed successfully.
please save my day.

VB

Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ConnectionString)
                    con.Open()
                    Using copy As New SqlBulkCopy(con)
                        copy.DestinationTableName = "trycsv"
                        copy.WriteToServer(dt)
                    End Using
                    con.Close()
                    con.Open()
                    Dim query As String = "UPDATE trycsv  SET level = @level  where level =@updlevel"
                    Using cmd As New SqlCommand(query, con)
                        cmd.Parameters.AddWithValue("@level", "1")
                        cmd.Parameters.AddWithValue("@updlevel", "O")
                        cmd.ExecuteNonQuery()
                    End Using
                    con.Close()
                End Using

Posted 10-Dec-14 20:57pm

Member 10316149

Add a Solution

4 solutions

Solution 3

I would suggest to create a stored procedure and inside the stored procedure

you can easily you the transaction block, something like below

SQL

Create Procedure ProcName(@level int, @updlevel int)
As
BEGIN

BEGIN TRAN
 UPDATE trycsv  SET level = @level  where level =@updlevel

If @@Error=0
 Commit Tran
Else
 Rollback Tran

END

Posted 10-Dec-14 21:32pm

Shweta N Mishra

v2

Comments

Sinisa Hajnal 11-Dec-14 5:52am

Procedure would also make the call much clearer. And transaction can be either in code (single call) or in the procedure :)

Solution 1

I think the link below will solve help unless I misunderstand.

http://msdn.microsoft.com/en-us/library/ms181299.aspx[^]

Posted 10-Dec-14 21:23pm

Member 9992980

Solution 2

Hi,

kindly check the below link for SQL transactions.

http://msdn.microsoft.com/en-us/library/ms188929(v=sql.110).aspx[^]

Posted 10-Dec-14 21:23pm

Senthilnathan Vaithi

Comments

Member 10316149 11-Dec-14 7:42am

thanks to you guys

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Anisuzzaman Sumon · Accepted Answer · 2014-12-10T22:38:00

Hi here is your Complete solution using SqlTransaction .feel free to copy .happy coding

VB

     Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ConnectionString)
               con.Open()
               Dim cmd As SqlCommand = con.CreateCommand()
           Using transaction As SqlTransaction = _
             con.BeginTransaction()

               Using bulkCopy As SqlBulkCopy = New _

SqlBulkCopy(con, _
                    SqlBulkCopyOptions.KeepIdentity, transaction)
                   bulkCopy.BatchSize = 10
                   bulkCopy.DestinationTableName = _
                    "trycsv"
                   cmd.Connection = connection
                   cmd.Transaction = transaction

                   Try

                       bulkCopy.WriteToServer(reader)
                           cmd.CommandText = _"UPDATE trycsv  SET level = @level  where level =@updlevel"
                          cmd.Parameters.AddWithValue("@level", "1")
                         cmd.Parameters.AddWithValue("@updlevel", "O")
                         cmd.ExecuteNonQuery()

                       transaction.Commit()

                   Catch ex As Exception

                       transaction.Rollback()

                   Finally
                       con.Close()
                   End Try
               End Using
           End Using
       End Using