how to handle multiple update or insert to database

Question

0.00/5 (No votes)

See more:

Hello,

I'm new in dotnet and trying t develop internet based mysql-windows applicatin using vb.net. If in a button click event, I'm performing multiple updates and insert operation to database(for example 5 steps), then its taking some time to complete this. What happens if during this period, my internet does down or some power failure occurs, then if 2 steps have already executed but rest 3 are not executed and the first 2 need to get rollback....

If I'm using MysqlTransaction, then for rollBack() in the catch block, it needs the connection to be present, but i've no internet connection.

Any suggestion how to overcome this problem????

Thanks

Debasish

Posted 15-Oct-11 1:12am

DEB4u

Add a Solution

2 solutions

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)

Espen Harlinn · Accepted Answer · 2011-10-15T01:20:00

Solution 1

If the connection goes down during a transaction it will be rolled back automagically ...

Best regards
Espen Harlinn

Posted 15-Oct-11 1:20am

Espen Harlinn

Comments

DEB4u 15-Oct-11 7:53am

have a lok at this code...

Public Class Form1
Dim gc As New mysql_con
Dim que As String
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
gc.conn()
gc.con.Open()
MsgBox("connection opened")
Dim cmd As New MySqlCommand
cmd.Connection = gc.con
que = "insert into testing values ('" + TextBox1.Text + "','" + TextBox2.Text + "')"
cmd.CommandText = que
cmd.ExecuteNonQuery()
MsgBox("...")
que = "insert into testing2 values ('" + TextBox2.Text + "','" + TextBox1.Text + "')"
cmd.CommandText = que
cmd.ExecuteNonQuery()
gc.con_Close()
MsgBox("closed")
End Sub

I'm disconnecting the internet on this step:

MsgBox("...")

but when I check my database, the 1st statement is executed, it's not being rolled back..

Thanks for Reply..

Simon Bang Terkildsen 15-Oct-11 14:15pm

Yeah, you're not using any transaction.. So that is to be expected.

Simon Bang Terkildsen 15-Oct-11 14:15pm

Correct

Espen Harlinn 15-Oct-11 16:48pm

Thanks Simon - good call :)

DEB4u · Accepted Answer · 2011-10-15T12:26:00

ok,, I've used MySqlTransaction and in the try block transaction.Commit()
and I've omitted the transaction.RollBack() inside the catch block... as for rolling back it requires a valid connection but my connection is broken...

I got my result of undoing the 1st statement as till transaction.commit() happens, all the insert or update happens in some temporary space, so is not affecting the real database till transaction.commit() .