📄 concurrency-columns.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 to Individual Columns</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Managing Concurrent Updates to Individual Columns</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>SELECT command: <b><span id="outSelect" runat="server"></span></b></div>
<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 />
<b>The Modified records in the new DataSet:</b>
<asp:datagrid id="dgrResult3" runat="server" /><p />
<div id="outUpdates" runat="server"></div><p />
<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 SELECT statement to extract the data
Dim strSelect As String
strSelect = "SELECT * FROM BookList WHERE ISBN LIKE '18610016%'"
outSelect.innerText = strSelect 'and display it
'create a new DataSet object
Dim objDataSet As 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 from 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
'got the data so we're finished with the DataAdapter
objDataAdapter = Nothing
'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 modify the DataSet by changing some records in the Books table
objTable.Rows(0)("Title") = "Amateur Double Glazing for Windows 2000"
objTable.Rows(2)("Title") = "Amateur Topfix Hinges for Windows 2000"
objTable.Rows(2)("PublicationDate") = "2001-09-03"
objTable.Rows(3)("PublicationDate") = "2001-02-08"
'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 = '1861001622'"
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 = '1861001681'"
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 start the process of collecting the changed values and
'updating the original data source
Dim strSQL, strWhere, strRowValue, strResults, strColName As String
Dim datRowDateValue As Date
'create a new Command object to use for the updates
Dim objCommand As New OleDbCommand()
'specify the Connection object and command type for the Command
objCommand.Connection = objConnect
objCommand.CommandType = CommandType.Text
'declare a variable to hold a new DataSet object
Dim objChangeDS As DataSet
'get *changed* records into the new DataSet
'copies only rows with a RowState property of "Modified"
objChangeDS = objDataSet.GetChanges(DataRowState.Modified)
'display the modified records in the table in the new DataSet
dgrResult3.DataSource = objChangeDS.Tables(0).DefaultView
dgrResult3.DataBind() 'and bind (display) the data
'open connection and apply the updates to the data source
objConnect.Open()
'declare a variable to hold a Transaction object
Dim objTransaction As OleDbTransaction
'start a transaction and assign it to the current Command object
objTransaction = objConnect.BeginTransaction()
objCommand.Transaction = objTransaction
strResults = "<b>Executing Update Commands</b> ...<br />"
'iterate through all the modified rows in the table
Dim objRow As DataRow, objColumn As DataColumn
For Each objRow in objChangeDS.Tables(0).Rows
'create the two root parts of the SQL statement
strSQL = "UPDATE BookList SET "
strWhere = " WHERE ISBN='" _
& objRow("ISBN", DataRowVersion.Original) & "'"
'iterate through all the columns in this row
For Each objColumn In objChangeDS.Tables(0).Columns
strColName = objColumn.ColumnName
'see if this column has been changed since the DataSet was
'originally created by comparing Original and Current values
If objRow(strColName, DataRowVersion.Current) <> _
objRow(strColName, DataRowVersion.Original) Then
'this column's data has been modified in the disconnected DataSet
'note: this does not detect concurrent changes to the source data
'have to get format of DateTime exactly right for a comparison
If objColumn.DataType.ToString() = "System.DateTime" Then
datRowDateValue = objRow(strColName, DataRowVersion.Original)
strRowValue = datRowDateValue.ToString("yyyy-MM-dd\ HH:mm:ss")
Else
strRowValue = objRow(strColName, DataRowVersion.Original)
End If
strSQL += strColName & "='" & objRow(strColName, DataRowVersion.Current) & "', "
strWhere += " AND " & strColName & "='" & strRowValue & "'"
End If
Next
'strip off extra comma and space from end of string
strSQL = Left(strSQL, Len(strSQL) - 2) & strWhere
'and assemble SQL statement
strResults += "* " & strSQL & " ... "
objCommand.CommandText = strSQL
Try
intRowsAffected = objCommand.ExecuteNonQuery()
If intRowsAffected > 0 Then
strResults += "updated <b>" & intRowsAffected & "</b> row(s).<p />"
Else
strResults += "<b>Error</b>: Row has been changed by another user<p />"
End If
Catch objError As Exception
'display error details
strResults += "<b>Error</b>: " & objError.Message & " - " & objError.Source & "<p />"
End Try
Next
outUpdates.InnerHtml = strResults
objTransaction.Rollback()
objConnect.Close()
End Sub
</script>
<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -