[RESOLVED] transaction and rollback

[vagelis]

hi!

i want to ask about the transaction
i want to rollback the changes if something goes wrong either in the clients site(internet connection failed in the time the sub is running) or in the servers side(if the server falls)

is the sqltrasnsaction the properly solution??

i found this example for the sqltransaction

 Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
          "integrated security=sspi;database=MyDatabase")

      ' SQL Delete Commands
      Dim sql As String = "DELETE FROM Employee " & _
         "WHERE ID = 10" 

      ' Create command
      Dim thisCommand As New SqlCommand(sql, thisConnection)

      ' Create Transaction
      Dim thisTransaction As SqlTransaction

      Try
         ' Open Connection
         thisConnection.Open()

         ' Begin transaction and attach it to command
         thisTransaction = thisConnection.BeginTransaction()
         thisCommand.Transaction = thisTransaction

         ' Run delete command
         thisCommand.ExecuteNonQuery()

         ' Commit transaction
         thisTransaction.Commit()

         ' Display success
         Console.WriteLine("Transaction Committed. Data Deleted")

      Catch ex As Exception
         ' Roll back transaction
         thisTransaction.Rollback()

         Console.WriteLine("Transaction rolled back : " & ex.Message)

      Finally
         ' Close Connection
         thisConnection.Close()

      End Try

[avrail]

this means, if the Delete statement worked as expected then commit the changes,
and if there is any exception, or any something prevent the command from being executed, then rollback,
if this what you are looking for ??

[gep13]

Assuming that it is Database operations that you are wanting to allow rollback for, then yes, the SqlTransaction class would be the correct choice.

Within this class, all operations have to complete successfully, otherwise, none of the operations will be committed to the database.

Gary

[vagelis]

and if i want to rollback something else like to give value to a session and at the same time make sql rollback?

[gep13]

Take a look at this example here:

http://msdn.microsoft.com/en-us/libr...ansaction.aspx

In the appropriate places, you could do some additional work to set Session variables.

Gary

[vagelis]

in the example that i posted before if i put the
thisTransaction = thisConnection.BeginTransaction()

inside the try i have an error "variable is used before assigned a value in the catch"
isit right to have it outside the catch??

[avrail]

can you post the code to take a look ,
and exactly where the error ?

[vagelis]

i see in the example that you posted Gary that it can be outside
another question is must the connection be openned before use the
"thisTransaction = thisConnection.BeginTransaction()"?? or it doesnt matter?

in my case i use many times con.open con.close??
is that a problem?

[vagelis]

for example

 Dim thisTransaction As SqlTransaction
       
        Try
 thisTransaction = con.BeginTransaction()
Dim paramar(3) As SqlParameter
            paramar(0) = New SqlParameter("@id", SqlDbType.Int, 4)
            paramar(0).Value = last_id + 1
            paramar(1) = New SqlParameter("@caption", SqlDbType.NVarChar)
            paramar(1).Value = tbox_text_caption2.Text
            paramar(2) = New SqlParameter("@image", SqlDbType.NVarChar)
            paramar(2).Value = "~/photos/allesomades" & fname
            paramar(3) = New SqlParameter("@imerominia", SqlDbType.DateTime, 512)
            paramar(3).Value = Date.UtcNow.AddHours(3)
            ' paramar(2) = New sqlParameter("@data", sqlType.VarChar, Session("maxtextsize"))
            ' paramar(2).Value = tbox_text_data.Text


            Dim sqlda As New SqlDataAdapter()
            sqlda.SelectCommand = New SqlCommand()
            sqlda.SelectCommand.Transaction = thisTransaction
            sqlda.SelectCommand.Connection = con
            sqlda.SelectCommand.Parameters.Add(paramar(0))
            sqlda.SelectCommand.Parameters.Add(paramar(1))
            sqlda.SelectCommand.Parameters.Add(paramar(2))
            sqlda.SelectCommand.Parameters.Add(paramar(3))
            sqlda.SelectCommand.CommandText = "insert into eidiseis_sxolia_allon_omadon (text_id,text_caption,image,imerominia) values (@id,@caption,@image,@imerominia)"
            con.Open()
            Dim ds As New DataSet()
            sqlda.Fill(ds)
            ds.Dispose()
            'tbox_text_data.Text = ""
            con.Close()








            Dim mycommandtab As New SqlCommand("create table alles_omades" & last_id + 1 & " (text_id int null,text_caption nvarchar(max) null,melos nvarchar(max) null,site nvarchar(max),titlos nvarchar(max) null,imerominia datetime null,quoted int null)", con)
            mycommandtab.Transaction = thisTransaction
            con.Open()
            dr = mycommandtab.ExecuteReader()
            dr.Close()
con.close()
....
.....
....
thisTransaction.Commit()
 Catch ex As Exception
            thisTransaction.Rollback()
        End Try

[avrail]

it is important when you open a connection with the database,
that you use try catch finally and in the finally check if the connection state is opened then close it.

[avrail]

the connection must be opened before you begin the transaction

[vagelis]

ok thanks for that it is an important information..

[avrail]

if this answered your Questions,
don't forget to mark the thread as resolved

[vagelis]

so a code like that

con.Open()
        Dim thisTransaction As SqlTransaction
        thisTransaction = con.BeginTransaction()
        Try
            Dim paramar(3) As SqlParameter
            paramar(0) = New SqlParameter("@id", SqlDbType.Int, 4)
            paramar(0).Value = last_id + 1
            paramar(1) = New SqlParameter("@caption", SqlDbType.NVarChar)
            paramar(1).Value = tbox_text_caption2.Text
            paramar(2) = New SqlParameter("@image", SqlDbType.NVarChar)
            paramar(2).Value = "~/photos/allesomades" & fname
            paramar(3) = New SqlParameter("@imerominia", SqlDbType.DateTime, 512)
            paramar(3).Value = Date.UtcNow.AddHours(3)
            ' paramar(2) = New sqlParameter("@data", sqlType.VarChar, Session("maxtextsize"))
            ' paramar(2).Value = tbox_text_data.Text


            Dim sqlda As New SqlDataAdapter()
            sqlda.SelectCommand = New SqlCommand()
            sqlda.SelectCommand.Transaction = thisTransaction
            sqlda.SelectCommand.Connection = con
            sqlda.SelectCommand.Parameters.Add(paramar(0))
            sqlda.SelectCommand.Parameters.Add(paramar(1))
            sqlda.SelectCommand.Parameters.Add(paramar(2))
            sqlda.SelectCommand.Parameters.Add(paramar(3))
            sqlda.SelectCommand.CommandText = "insert into eidiseis_sxolia_allon_omadon (text_id,text_caption,image,imerominia) values (@id,@caption,@image,@imerominia)"
            Dim ds As New DataSet()
            sqlda.Fill(ds)
            ds.Dispose()
            'tbox_text_data.Text = ""


            Dim mycommandtab As New SqlCommand("create table alles_omades" & last_id + 1 & " (text_id int null,text_caption nvarchar(max) null,melos nvarchar(max) null,site nvarchar(max),titlos nvarchar(max) null,imerominia datetime null,quoted int null)", con)
            mycommandtab.Transaction = thisTransaction
            dr = mycommandtab.ExecuteReader()
            dr.Close()



            'Catch ex As SqlException

            'Response.Write("problima kai anairesi allagon:" & ex.Message.ToString)


            '   End Try

            Dim paramard(2) As SqlParameter
            paramard(0) = New SqlParameter("@id", SqlDbType.Int, 4)
            paramard(0).Value = 1
            paramard(1) = New SqlParameter("@caption", SqlDbType.NVarChar)
            paramard(1).Value = tbox_text_caption3.Text
            paramard(2) = New SqlParameter("@imerominia", SqlDbType.DateTime, 512)
            paramard(2).Value = Date.UtcNow.AddHours(3)
            ' paramar(2) = New sqlParameter("@data", sqlType.VarChar, Session("maxtextsize"))
            ' paramar(2).Value = tbox_text_data.Text


            Dim sqldad As New SqlDataAdapter()
            sqldad.SelectCommand = New SqlCommand()
            sqldad.SelectCommand.Transaction = thisTransaction
            sqldad.SelectCommand.Connection = con
            sqldad.SelectCommand.Parameters.Add(paramard(0))
            sqldad.SelectCommand.Parameters.Add(paramard(1))
            sqldad.SelectCommand.Parameters.Add(paramard(2))
            sqldad.SelectCommand.CommandText = "insert into alles_omades" & last_id + 1 & " (text_id,text_caption,imerominia,quoted) values (@id,@caption,@imerominia,0)"
            Dim dsd As New DataSet()
            sqldad.Fill(dsd)
            tbox_text_caption3.Text = ""
            dsd.Dispose()
 thisTransaction.Commit()
        Catch ex As Exception
            Session("lathos") = True
            Session("messagelathous") = "tis pire piso"
            thisTransaction.Rollback()
        Finally
            con.Close()
        End Try

do you think it will work??
another question is when will rollback be need in general??
i saw that if i close the browser in the time the sub is running it occurs no problem and all the sub runs until the end
i thought that if a browser will be closed i would have problem with some values that are token from the page like
tbox_text_caption2.Text
that is the text from a textbox of a site
i thought that if the connection would close before the sub takes this value i would have a problem and then i need rollback..
is that right as i am thinking?

[avrail]

i guess you working on a website not a web application project ?

[vagelis]

yes website...

[avrail]

if you it was a web application it will stop executing.
but the website not,

http://connect.microsoft.com/VisualS...stop-debugging

[avrail]

another question is when will rollback be need in general??
i saw that if i close the browser in the time the sub is running it occurs no problem and all the sub runs until the end

now let me understand to clear things out?
for Q1 rollback if there is something goes wrong, so there is an exception

Q2 tell me how did you do this?

[vagelis]

the sub has many code and ii the middle i have putted a
"for" that counts a big number to have time to close it

so do you think that the code that i posted before is right??
and if you think that fall the conection in the time that the sub is running before take the value of textbox.text will create a problem if i dont have a transaction
or the browser will keep the value with some way before the conection falls?

[avrail]

as i told you before this code will work fine, but i don't know what you mean by the last Question

[vagelis]

i mean that if in the time sub is running and not have yet the value of a textbox.text if the connection of a client falls then the sub will create an error of dont know this value??
or the browser keeps the values the first time it displays a page??

[avrail]

how can you do such a thing?
your text value is a parameter in the insert statement, if you didn't pass it in the first place, there will be exception raised about the missing parameter

[vagelis]

yes my question is about that
if the connection of the client falls before the sub reaches the parameter give value of the textbox what will happen?

[avrail]

how this connection of your client will be last ?
you mean the client close the browser?

[vagelis]

close the browser or have bad internet from wifi or something like that..

[avrail]

don't worry about this, if the request didn't reach to the server, then there is nothing will happen

[vagelis]

and when will something happen??
only if i have wrong in my code??

[vagelis]

sory i writed here something not important and i didnt know how to delete this post

[gep13]

sory i writed here something not important and i didnt know how to delete this post

Unless you are a PowerPoster, you can't delete your own threads. The only thing that you could do would be to make a request to a Moderator to delete your thread.

Gary