VB.NET & MySql.Data.MySqlClient - Transaction()

Author: admin | Posted: 13-06-2010 | Category: VB.NET & MySql.Data.MySQLClient

VB.NET & MySql.Data.MySQLClient - Transaction() - How to learn Connector/NET ADO.NET component MySql.Data.MySqlClient namespace is the .NET Framework Data Provider for MySql data source, Using the MySqlTransaction class for makes sure that changes that were made to the store are treated as a group that can be committed or rolled back (MySQL Server Database)

ShotDev Focus:
- VB.NET & MySql.Data.MySQLClient - Transaction()

Example

Transaction.aspx

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="MySql.Data.MySqlClient"%>
<%@ Page Language="VB" %>
<script runat="server">

Sub Page_Load(sender As Object, e As EventArgs)
SampleTransaction()
End Sub

Sub SampleTransaction()

Dim objConn As MySql.Data.MySqlClient.MySqlConnection
Dim objCmd As MySql.Data.MySqlClient.MySqlCommand
Dim strConnString,strSQL As String
Dim Trans As MySql.Data.MySqlClient.MySqlTransaction

strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false"
objConn = New MySql.Data.MySqlClient.MySqlConnection(strConnString)
objConn.Open()

'*** Start Transaction ***'
Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted)

Try

'*** Query 1 ***'
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES ('C005','Weerachai Nukitram','webmaster@shotdev.com','TH','2000000','1000000')"
objCmd = New MySql.Data.MySqlClient.MySqlCommand()
With objCmd
.Connection = objConn
.Transaction = Trans
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()

'*** Query 2 ***'
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES ('C005','Weerachai Nukitram','webmaster@shotdev.com','TH','2000000','1000000')"
objCmd = New MySql.Data.MySqlClient.MySqlCommand()
With objCmd
.Connection = objConn
.Transaction = Trans
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()

Trans.Commit()  '*** Commit Transaction ***'
Me.lblText.Text = "Record is commit"

Catch ex As Exception
Trans.Rollback() '*** RollBack Transaction ***'

Me.lblText.Text = "Record is rollback ("& ex.Message &")"
End Try

objCmd = Nothing
objConn.Close()
objConn = Nothing

End Sub

</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label id="lblText" runat="Server"></asp:Label>
</form>
</body>
</html>

Error Case
Record is rollback (Duplicate entry ‘C005′ for key 1)

Screenshot

.
.
.
Download this script.