VB.NET & System.Data.SqlClient - Transaction() - How to learn Connector/NET ADO.NET component System.Data.SqlClient namespace is the .NET Framework Data Provider for SQL Server data source, Using the SqlTransaction class for makes sure that changes that were made to the store are treated as a group that can be committed or rolled back (SQL Server 2000,2005,2008 Database)
ShotDev Focus:
- VB.NET & System.Data.SqlClient - Transaction()
Example
Transaction.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
SampleTransaction()
End Sub
Sub SampleTransaction()
Dim objConn As System.Data.SqlClient.SqlConnection
Dim objCmd As System.Data.SqlClient.SqlCommand
Dim strConnString,strSQL As String
Dim Trans As SqlTransaction
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"
objConn = New System.Data.SqlClient.SqlConnection(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 System.Data.SqlClient.SqlCommand()
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 ('C006','Surachai Sirisart','surachai@shotdev.com','TH','1000000','500000')"
objCmd = New System.Data.SqlClient.SqlCommand()
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 (Violation of PRIMARY KEY constraint ‘PK_customer’. Cannot insert duplicate key in object ‘dbo.customer’. The statement has been terminated.)
Screenshot
.
.
.
Download this script.
Loading ...