📄 concurrency-continue.aspx
字号:
<%@Page Language="VB" debug="true"%>
<%@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>Locating Concurrency Errors After Updating the Source Data</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Locating Concurrency Errors After Updating the Source Data</span><hr />
<!--------------------------------------------------------------------------->
<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>
<div id="outError" runat="server"> </div>
<b>Initial contents of the Books table:</b>
<asp:datagrid id="dgrResult1" runat="server" /><p />
<b>Contents of the Books table after editing:</b>
<asp:datagrid id="dgrResult2" runat="server" /><p />
<div id="outConcurrent" runat="server"></div><p />
<div id="outResult" runat="server"></div><p />
<b>Note:</b> while this page does execute the Update method of the DataSet, it does not<br />
actually change the original data. It uses a transaction that is rolled back afterwards.</br />
Otherwise, the update would prevent the code from running the next time.
<script language="vb" runat="server">
Sub Page_Load()
Dim strResult As String = "" 'to hold the result messages
Dim strConnect As String 'to hold connection string
Dim objDataSet As DataSet 'to hold rows from database
'get connection string from ..\global\connect-strings.ascx user control
strConnect = ctlConnectStrings.OLEDBConnectionString
'specify the SELECT statement to extract the data
Dim strSelect As String
strSelect = "SELECT * FROM BookList WHERE ISBN LIKE '18610022%'"
'create a new DataSet object
objDataSet = New DataSet()
'create a new Connection object using the connection string
Dim objConnect As New OleDbConnection(strConnect)
'create a new DataAdapter using the connection object and select statement
Dim objDataAdapter As New OleDbDataAdapter(strSelect, objConnect)
Try
'fill the dataset with data using the DataAdapter object
objDataAdapter.Fill(objDataSet, "Books")
Catch objError As Exception
'display error details
outError.innerHTML = "<b>* Error while accessing data</b>.<br />" _
& objError.Message & "<br />" & objError.Source
Exit Sub ' and stop execution
End Try
'accept the changes to "fix" the current state of the DataSet contents
objDataSet.AcceptChanges()
'declare a variable to reference the Books table
Dim objTable As DataTable = objDataSet.Tables("Books")
'display the contents of the Books table before changing data
dgrResult1.DataSource = objTable.DefaultView
dgrResult1.DataBind() 'and bind (display) the data
'now change some records in the Books table
objTable.Rows(0).Delete()
objTable.Rows(1)("Title") = "Amateur Theatricals for Windows 2000"
objTable.Rows(2).Delete()
objTable.Rows(3).Delete()
objTable.Rows(4)("PublicationDate") = "01-01-2002"
objTable.Rows.RemoveAt(5)
'notice that using the Remove method on row 5 (rather than marking
'it as deleted) means that the next row then becomes row 5
objTable.Rows(5)("ISBN") = "200000000"
'add a new row using an array of values
Dim objValsArray(2) As Object
objValsArray(0) = "200000001"
objValsArray(1) = "Impressionist Guide to Painting Computers"
objValsArray(2) = "05-02-2002"
objTable.Rows.Add(objValsArray)
'display the contents of the Books table after changing the data
dgrResult2.DataSource = objTable.DefaultView
dgrResult2.DataBind() 'and bind (display) the data
'-------------------------------------------------------------------
'change some values in the original table while the DataSet is holding
'a disconnected copy of the data to force a concurrency error
Dim strConcurrent, strUpdate As String
Dim intRowsAffected As Integer
'need a new (separate) Connection and Command object
Dim objNewConnect As New OleDbConnection(strConnect)
Dim objNewCommand As New OleDbCommand()
objNewCommand.Connection = objNewConnect
Try
objNewConnect.Open()
'modify two of the book titles to force concurrency errors
strConcurrent += "<b>Concurrently executed</b>:<br />"
Dim datNow As DateTime = Now()
Dim strNow As String = datNow.ToString("dd-M-yy \a\t hh:mm:ss")
strUpdate = "UPDATE BookList SET Title = 'New Book Written on " _
& strNow & "' WHERE ISBN = '186100222X'"
objNewCommand.CommandText = strUpdate
intRowsAffected = objNewCommand.ExecuteNonQuery()
strConcurrent += strUpdate & "<br /> ... <b>" _
& CStr(intRowsAffected) & "</b> row(s) affected<br />"
'edit the next line to force a concurrency error if none occurs
strUpdate = "UPDATE BookList SET Title = 'Another Book Written on " _
& strNow & "' WHERE ISBN = '1861002262'"
objNewCommand.CommandText = strUpdate
intRowsAffected = objNewCommand.ExecuteNonQuery()
strConcurrent += strUpdate & "<br /> ... <b>" _
& CStr(intRowsAffected) & "</b> row(s) affected<br />"
objNewConnect.Close()
Catch objError As Exception
'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
outConcurrent.InnerHtml = strConcurrent
'----------------------------------------------------------------
'now ready to do the update from the DataSet to the database
'declare a variable to hold a Transaction object
Dim objTransaction As OleDbTransaction
Try
'create an auto-generated command builder to create the commands
'to update, insert and delete the data
Dim objCommandBuilder As New OleDbCommandBuilder(objDataAdapter)
'set the update, insert and delete commands for the DataAdapter
objDataAdapter.DeleteCommand = objCommandBuilder.GetDeleteCommand()
objDataAdapter.InsertCommand = objCommandBuilder.GetInsertCommand()
objDataAdapter.UpdateCommand = objCommandBuilder.GetUpdateCommand()
'start a transaction so that we can roll back the changes
'must do this on an open Connection object
objConnect.Open()
objTransaction = objConnect.BeginTransaction()
'attach the current transaction to all the Command objects
'must be done after setting Connection property
objDataAdapter.DeleteCommand.Transaction = objTransaction
objDataAdapter.InsertCommand.Transaction = objTransaction
objDataAdapter.UpdateCommand.Transaction = objTransaction
'prevent exceptions being thrown due to concurrency errors
'the error details can then be obtained from the RowError property
'of the row that generated the error if required
objDataAdapter.ContinueUpdateOnError = True
'perform the update on the original data
objDataAdapter.Update(objDataSet, "Books")
Catch objError As Exception
'rollback the transaction undoing any 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
'to actually update the source tables just change the next line
'to CommitTransaction() or remove the transaction altogether.
'note that you will then have to change the SELECT statement and
'code to be able to run the example again, or rebuild the
'original data from the supplied SQL scripts
objTransaction.Rollback()
'----------------------------------------------------------------
'now see if there are any update errors anywhere in the DataSet
If objDataSet.HasErrors Then
Dim objThisRow As DataRow
Dim intIndex As Integer
'check each table for errors in that table
Dim objThisTable As DataTable
For Each objThisTable In objDataSet.Tables
If objThisTable.HasErrors Then
strResult += "One or more errors found in table '<b>" _
& objThisTable.TableName & "</b>:'<p />"
'get collection containing only rows with errors
'using the GetErrors method of the DataTable object
'check each row in this table for errors
For Each objThisRow In objThisTable.GetErrors()
'display the error details and column values
strResult += "* Row with ISBN=<b>" _
& objThisRow("ISBN") _
& "</b> has error <b>" _
& objThisRow.RowError & "</b><br />" _
& "Original Values: "
'iterate through row collecting original and current values
For intIndex = 0 To objThisTable.Columns.Count - 1
strResult += objThisRow(intIndex, DataRowVersion.Original) & ", "
Next
strResult = Left(strResult, Len(strResult) - 2)
strResult += "<br />Current Values: "
For intIndex = 0 To objThisTable.Columns.Count - 1
strResult += objThisRow(intIndex, DataRowVersion.Current) & ", "
Next
strResult = Left(strResult, Len(strResult) - 2)
'use function declared later in page to get underlying values
strResult += "<br />Underlying (database) Values: " _
& GetUnderlyingValues(strConnect, objThisRow("ISBN")) & "<p />"
Next
End If
Next 'table
End If
'display the results of the Update
outResult.InnerHtml = strResult
End Sub
'----------------------------------------------------------------
Function GetUnderlyingValues(strConnect As String, strRowKey As String) As String
'select the existing column values from the underlying table in the database
Dim strSQL = "SELECT * FROM BookList WHERE ISBN='" & strRowKey & "'"
'create connection and command to access database
Dim objConnect As New OleDbConnection(strConnect)
Dim objCommand As New OleDbCommand(strSQL, objConnect)
'declare the variables we'll need
Dim objReader As OleDbDataReader
Dim strValues As String = ""
Dim intIndex As Integer
Try
'get a DataReader containing the specified row data
objConnect.Open()
objReader = objCommand.ExecuteReader()
'put values from row into a string to return
If objReader.Read() Then
For intIndex = 0 To objReader.FieldCount - 1
strValues += objReader.GetValue(intIndex) & ", "
Next
End If
'close connection and return result
objConnect.Close()
GetUnderlyingValues = Left(strValues, Len(strValues) - 2)
Catch objError As Exception
GetUnderlyingValues = "*Error*"
End Try
End Function
</script>
<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -