📄 update-with-transaction.aspx
字号:
<%@Page Language="VB"%>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>
<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Transactional Data Updates with a Command Object</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Transactional Data Updates with a Command Object</span><hr />
<!--------------------------------------------------------------------------->
<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>
<div>Connection string: <b><span id="outConnect" runat="server"></span></b></div>
<div>SQL statement 1: <b><span id="outSQL1" runat="server"></span></b></div>
<div>SQL statement 2: <b><span id="outSQL2" runat="server"></span></b></div>
<div>SQL statement 3: <b><span id="outSQL3" runat="server"></span></b></div>
<div id="outError" runat="server"> </div>
<div id="outResult" runat="server"></div>
<script language="vb" runat="server">
Sub Page_Load()
'get connection string from ..\global\connect-strings.ascx user control
Dim strConnect = ctlConnectStrings.OLEDBConnectionString
outConnect.InnerText = strConnect 'and display it
'specify the SQL statements to update the data
Dim strNow, strSQL1, strSQL2, strSQL3 As String
Dim datNow As DateTime = Now()
strNow = datNow.ToString("dd-M-yy \a\t hh:mm:ss")
strSQL1 = "UPDATE BookList SET Title = 'Book One Written on " _
& strNow & "' WHERE ISBN='1861000375'"
outSQL1.InnerText = strSQL1 'and display it
strSQL2 = "UPDATE BookList SET Title = 'Book Two Written on " _
& strNow & "' WHERE ISBN='1861000383'"
outSQL2.InnerText = strSQL2 'and display it
strSQL3 = "UPDATE BookList SET Title = 'Book Three Written on " _
& strNow & "' WHERE ISBN='1861000391'"
outSQL3.InnerText = strSQL3 'and display it
'create a new Connection object using the connection string
Dim objConnect As New OleDbConnection(strConnect)
'create a new Command object to use to access the data
Dim objCommand As New OleDbCommand()
'declare an Integer variable to hold the number of records affected
Dim intRowsAffected As Integer = 0
'declare a variable to hold a Transaction object
Dim objTransaction As OleDbTransaction
Try
'open the connection to the database
objConnect.Open()
'start a transaction for this connection
objTransaction = objConnect.BeginTransaction()
'specify the Connection object and command type for the Command
objCommand.Connection = objConnect
objCommand.CommandType = CommandType.Text
'attach the current transaction to the Command object
'must be done after setting Connection property
objCommand.Transaction = objTransaction
'specify the select statement to use for the first update
objCommand.CommandText = strSQL1
'execute the SQL statement against the command to fill the DataReader
'keep track of number of records originally updated
intRowsAffected += objCommand.ExecuteNonQuery()
'repeat using the select statement to use for the second update
objCommand.CommandText = strSQL2
intRowsAffected += objCommand.ExecuteNonQuery()
'repeat using the select statement to use for the third update
objCommand.CommandText = strSQL3
intRowsAffected += objCommand.ExecuteNonQuery()
Catch objError As Exception
'error encountered so roll back all the updates
objTransaction.Rollback()
'display error details
outError.InnerHtml = "<b>* Error while updating original data</b>.<br />" _
& objError.Message & "<br />" & objError.Source
Exit Sub ' and stop execution
End Try
'all seems to be OK so can commit all the updates
'however, only do so if current time has 'odd' seconds
Dim strCommit As String
If Second(datNow) Mod 2 = 0
objTransaction.Rollback()
strCommit = "rolled back"
Else
objTransaction.Commit()
strCommit = "committed"
End If
'now read the data back again. Specify a SQL statement to
'extract just the changes records in the Command object
'release the transaction object so we can use the Command again
objCommand.Transaction = Nothing
objCommand.CommandText = "SELECT * FROM BookList WHERE ISBN LIKE '18610003%'"
'declare a string to hold the results as an HTML table
Dim strResult As String
'show the number of records affected
strResult += "Executed SQL statement, <b>" & intRowsAffected.ToString() _
& "</b> record(s) affected<br />Transaction was <b>" & strCommit _
& "</b><br />Reading back from the database...</br>"
Try
'declare a variable to hold a DataReader object
Dim objDataReader As OleDbDataReader
'execute the SQL statement against the command to fill the DataReader
objDataReader = objCommand.ExecuteReader()
'iterate through the records in the DataReader getting field values
'the Read method returns False when there are no more records
Do While objDataReader.Read()
strResult += "ISBN=""<b>" & objDataReader("ISBN") & "</b>"" Title=""<b>" _
& objDataReader("Title") & """</b><br />"
Loop
'close the DataReader and Connection
objDataReader.Close()
objConnect.Close()
Catch objError As Exception
'display error details
outError.InnerHtml = "<b>* Error while accessing updated data</b>.<br />" _
& objError.Message & "<br />" & objError.Source
Exit Sub ' and stop execution
End Try
'add closing table tag and display the results
outResult.InnerHtml = strResult
End Sub
</script>
<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -