📄 concurrency-rowupdated.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>Managing Concurrent Updates with the RowUpdated Event</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Managing Concurrent Updates with the RowUpdated Event</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>Contents of the Errors table in a new DataSet after updating:</b>
<asp:datagrid id="dgrResult3" runat="server" /><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">
Dim gstrResult As String 'to hold the result messages
Dim gstrConnect As String 'to hold connection string
Dim gobjDataSet As DataSet 'to hold rows from database
Dim gobjErrorTable As DataTable 'to hold a list of errors
Dim gobjErrorDS As DataSet 'to hold the Errors table
Sub Page_Load()
'get connection string from ..\global\connect-strings.ascx user control
gstrConnect = 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
gobjDataSet = New DataSet()
'create a new Connection object using the connection string
Dim objConnect As New OleDbConnection(gstrConnect)
'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(gobjDataSet, "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
gobjDataSet.AcceptChanges()
'declare a variable to reference the Books table
Dim objTable As DataTable = gobjDataSet.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(gstrConnect)
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 set up event handler to react to RowUpdated event
AddHandler objDataAdapter.RowUpdated, _
New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
'----------------------------------------------------------------
'create a new empty Table object to hold error rows
gobjErrorTable = New DataTable("Errors")
'define the columns for the Errors table
gobjErrorTable.Columns.Add("Action", System.Type.GetType("System.String"))
gobjErrorTable.Columns.Add("RowKey", System.Type.GetType("System.String"))
gobjErrorTable.Columns.Add("ColumnName", System.Type.GetType("System.String"))
gobjErrorTable.Columns.Add("OriginalValue", System.Type.GetType("System.String"))
gobjErrorTable.Columns.Add("CurrentValue", System.Type.GetType("System.String"))
gobjErrorTable.Columns.Add("DatabaseValue", System.Type.GetType("System.String"))
'create a new empty DataSet object to hold Errors table
gobjErrorDS = New DataSet()
gobjErrorDS.Tables.Add(gobjErrorTable)
'----------------------------------------------------------------
'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
'perform the update on the original data
objDataAdapter.Update(gobjDataSet, "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()
'display the results of the Update
outResult.InnerHtml = gstrResult
'display the contents of the Errors table
dgrResult3.DataSource = gobjErrorDS
dgrResult3.DataMember = "Errors"
dgrResult3.DataBind() 'and bind (display) the data
End Sub
'----------------------------------------------------------------
'event handler for the RowUpdated event
Sub OnRowUpdated(objSender As Object, objArgs As OleDbRowUpdatedEventArgs)
'see if the update was successful
If objArgs.RecordsAffected < 1 Then
'get the text description of the StatementType
Dim strType = System.Enum.GetName(objArgs.StatementType.GetType(), _
objArgs.StatementType)
'get the primary key of the row (the ISBN)
Dim strRowKey As String
strRowKey = objArgs.Row("ISBN", DataRowVersion.Original)
'get a reference to the original table in the DataSet
Dim objTable As DataTable = gobjDataSet.Tables(0)
Dim objColumn As DataColumn 'to hold a DataColumn object
Dim strColumnName As String 'to hold the column name
'iterate through the columns in the current row
For Each objColumn In objTable.Columns
'get the column name as a string
strColumnName = objColumn.ColumnName
'see if this column has been modified
If objArgs.Row(strColumnName, DataRowVersion.Current) _
<> objArgs.Row(strColumnName, DataRowVersion.Original) Then
'create a new DataRow object instance in this table
Dim objDataRow As DataRow = gobjErrorTable.NewRow()
'and fill in the values
objDataRow("Action") = strType
objDataRow("RowKey") = strRowKey
objDataRow("ColumnName") = strColumnName
objDataRow("OriginalValue") = objArgs.Row(strColumnName, DataRowVersion.Original)
objDataRow("CurrentValue") = objArgs.Row(strColumnName, DataRowVersion.Current)
objDataRow("DatabaseValue") = GetCurrentColumnValue(gstrConnect, strRowKey, strColumnName)
'add new row to the Errors table
gobjErrorTable.Rows.Add(objDataRow)
End If
Next
'set the Status property of the row to skip current row update if there is an error
'default is Continue which means an error will halt execution of following updates
objArgs.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
'----------------------------------------------------------------
Function GetCurrentColumnValue(strConnect As String, strISBN As String, strColumnName As String) As String
'select the existing column value from the underlying table in the database
Dim strSQL = "SELECT " & strColumnName _
& " FROM BookList WHERE ISBN='" & strISBN & "'"
Dim objConnect As New OleDbConnection(strConnect)
Dim objCommand As New OleDbCommand(strSQL, objConnect)
Try
objConnect.Open()
'use ExecuteScalar for efficiency, it returns only one item
'get the value direct from it and convert to a String
GetCurrentColumnValue = objCommand.ExecuteScalar().ToString()
objConnect.Close()
Catch objError As Exception
GetCurrentColumnValue = "*Error*"
End Try
End Function
</script>
<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -