MySQL的事务(提交和回滚) [英] mysql transaction (commit and rollback)
问题描述
下面是我使用的MySqlTransaction的代码,它完美地运行....
,但问题是我使用的MySqlCommand的单个实例我在它使用唯一的参数。这是毁了我的查询。有周围的任何其他方式做....
我试过每次查询后却没有用的处置CMD .. :(
con.Open()
昏暗sqlTran作为MySqlTransaction = con.BeginTransaction()
尺寸CMD作为的MySqlCommand = con.CreateCommand()
cmd.Transaction = sqlTran
昏暗str作为字符串=没有
尝试
cmd.CommandText =选择身份识别码FROM memaster哪里ADATE = @ ADATE和ANO = @ ANO和ASource = @ ASRC
cmd.Parameters.AddWithValue(@ ADATE,txt_bpass_adate.Text)
cmd.Parameters.AddWithValue(@ ANO,txt_bpass_af。文本)
cmd.Parameters.AddWithValue(@ ASRC,txt_bpass_asource.Text)
海峡= cmd.ExecuteScalar()
'cmd.Dispose()'
如果str为没什么然后
cmd.CommandText =INSERT INTO memaster(A日期,ANO,ASource)值(@ ADATE,@ ANO,@ ASRC)
cmd.Parameters.AddWithValue(@ ADATE txt_bpass_adate.Text)
cmd.Parameters.AddWithValue(@ ANO,txt_bpass_af.Text)
cmd.Parameters.AddWithValue(@ ASRC,txt_bpass_asource.Text)
cmd.ExecuteNonQuery()
端如果
sqlTran.Commit()
抓住EX作为例外
尝试
sqlTran.Rollback()
捕捉EX1作为例外
端尝试
端尝试
其实我是想在火单笔交易更多然后4的查询,这样,如果什么地方错了,我可以回滚它...
如果任何onebody有任何其他方法好心分享概念妇女参与发展我...
对于指数为整数= 1至5
cmd.CommandText =INSERT INTO细节(ID,BNOS,SNO)值(@ FID1,@ BNO,@ SeqN1)
CMD。 Parameters.AddWithValue(@ FID1,STR)
cmd.Parameters.AddWithValue(@ BNO,STR1)
cmd.Parameters.AddWithValue(@ SeqN1,txt_bpass_sqn1.Text)
cmd.ExecuteNonQuery()
下一
要在同一事务中执行多个命令,请确保您的交易对象分配给每个单独的命令:
昏暗设置SelectCmd作为的MySqlCommand = con.CreateCommand()
昏暗insertCmd作为的MySqlCommand = con.CreateCommand()
selectCmd.CommandText =SELECT ...
insertCmd .CommandText =INSERT ...
尺寸sqlTran作为MySqlTransaction = con.BeginTransaction()
尝试
selectCmd.Transaction = sqlTran
insertCmd.Transaction = sqlTran
... selectCmd.ExecuteScalar()...
... insertCmd.ExecuteNonQuery()...
sqlTran.Commit()
抓住
sqlTran.Rollback()
端尝试
正如其他人所说,它通常是一个好主意,的Dispose()
的对象(即是的IDisposable
),只要你大功告成与他们合作。处理对象后,就可以不再使用。
below is the code i am using for MySqlTransaction and it is running perfectly.... but problem is as i am using single instance of mysqlcommand i have to use unique PARAMETER in it. which is ruining my query. is there any other way around to do....
i tried to dispose cmd after each query but of no use.. :(
con.Open()
Dim sqlTran As MySqlTransaction = con.BeginTransaction()
Dim cmd As MySqlCommand = con.CreateCommand()
cmd.Transaction = sqlTran
Dim str As String = Nothing
Try
cmd.CommandText = "SELECT myid FROM memaster where Adate=@adate and ANo=@ano and ASource=@asrc"
cmd.Parameters.AddWithValue("@adate", txt_bpass_adate.Text)
cmd.Parameters.AddWithValue("@ano", txt_bpass_af.Text)
cmd.Parameters.AddWithValue("@asrc", txt_bpass_asource.Text)
str = cmd.ExecuteScalar()
'cmd.Dispose()'
If str Is Nothing Then
cmd.CommandText = "Insert into memaster (ADate,ANo,ASource) values (@aDate,@aNo,@aSRC)"
cmd.Parameters.AddWithValue("@aDate", txt_bpass_adate.Text)
cmd.Parameters.AddWithValue("@aNo", txt_bpass_af.Text)
cmd.Parameters.AddWithValue("@aSRC", txt_bpass_asource.Text)
cmd.ExecuteNonQuery()
End If
sqlTran.Commit()
Catch ex As Exception
Try
sqlTran.Rollback()
Catch ex1 As Exception
End Try
End Try
i actually want to fire more then 4 queries in single transaction so that if anything go wrong i can rollback it...
if any onebody have any other method of it kindly share the concept wid me...
For index As Integer = 1 To 5
cmd.CommandText = "Insert into detail (ID,BNos,SNo) values (@FID1,@BNo,@SeqN1)"
cmd.Parameters.AddWithValue("@FID1", str)
cmd.Parameters.AddWithValue("@BNo", str1)
cmd.Parameters.AddWithValue("@SeqN1", txt_bpass_sqn1.Text)
cmd.ExecuteNonQuery()
Next
To execute multiple commands within the same transaction, ensure that you assign the transaction object to each command individually:
Dim selectCmd As MySqlCommand = con.CreateCommand()
Dim insertCmd As MySqlCommand = con.CreateCommand()
selectCmd.CommandText = "SELECT ..."
insertCmd.CommandText = "INSERT ..."
Dim sqlTran As MySqlTransaction = con.BeginTransaction()
Try
selectCmd.Transaction = sqlTran
insertCmd.Transaction = sqlTran
...selectCmd.ExecuteScalar()...
...insertCmd.ExecuteNonQuery()...
sqlTran.Commit()
Catch
sqlTran.Rollback()
End Try
As others have mentioned, it is generally a good idea to Dispose()
objects (that are IDisposable
) as soon as you're done working with them. After disposing objects, they can no longer be used.
这篇关于MySQL的事务(提交和回滚)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!