📄 rowupdated-event.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>Handling the RowUpdating and RowUpdated Events</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Handling the RowUpdating and RowUpdated Events</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>INSERT command: <b><span id="outInsert" runat="server"></span></b></div>
<div>DELETE command: <b><span id="outDelete" runat="server"></span></b></div>
<div>UPDATE command: <b><span id="outUpdate" 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="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">
Dim gstrResult As String 'to hold the result messages
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 '18610022%'"
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 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
'now set up event handlers to react to update events
AddHandler objDataAdapter.RowUpdating, _
New OleDbRowUpdatingEventHandler(AddressOf OnRowUpdating)
AddHandler objDataAdapter.RowUpdated, _
New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
'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(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
'display the SQL statements that the DataSet used
'these are created automatically when Update is called
outInsert.InnerText = objDataAdapter.InsertCommand.CommandText
outDelete.InnerText = objDataAdapter.DeleteCommand.CommandText
outUpdate.InnerText = objDataAdapter.UpdateCommand.CommandText
'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
End Sub
'event handler for the RowUpdating event
Sub OnRowUpdating(objSender As Object, _
objArgs As OleDbRowUpdatingEventArgs)
'get the text description of the StatementType
Dim strType = System.Enum.GetName(objArgs.StatementType.GetType(), _
objArgs.StatementType)
'get the value of the primary key column "ISBN"
Dim strISBNValue As String
Select Case strType
Case "Insert"
strISBNValue = objArgs.Row("ISBN", DataRowVersion.Current)
Case Else
strISBNValue = objArgs.Row("ISBN", DataRowVersion.Original)
End Select
'add result to display string
gstrResult += "<b>" & strType & "</b> action in <b>RowUpdating</b> event " _
& "for row with ISBN='<b>" & strISBNValue & "</b>'<br />"
End Sub
'event handler for the RowUpdated event
Sub OnRowUpdated(objSender As Object, objArgs As OleDbRowUpdatedEventArgs)
'get the text description of the StatementType
Dim strType = System.Enum.GetName(objArgs.StatementType.GetType(), _
objArgs.StatementType)
'get the value of the columns
Dim strISBNCurrent, strISBNOriginal,strTitleCurrent,strTitleOriginal As String
Dim strPubDateCurrent, strPubDateOriginal As String
Select Case strType
Case "Insert"
strISBNCurrent = objArgs.Row("ISBN", DataRowVersion.Current)
strTitleCurrent = objArgs.Row("Title", DataRowVersion.Current)
strPubDateCurrent = objArgs.Row("PublicationDate", DataRowVersion.Current)
Case "Delete"
strISBNOriginal = objArgs.Row("ISBN", DataRowVersion.Original)
strTitleOriginal = objArgs.Row("Title", DataRowVersion.Original)
strPubDateOriginal = objArgs.Row("PublicationDate", DataRowVersion.Original)
Case "Update"
strISBNCurrent = objArgs.Row("ISBN", DataRowVersion.Current)
strTitleCurrent = objArgs.Row("Title", DataRowVersion.Current)
strPubDateCurrent = objArgs.Row("PublicationDate", DataRowVersion.Current)
strISBNOriginal = objArgs.Row("ISBN", DataRowVersion.Original)
strTitleOriginal = objArgs.Row("Title", DataRowVersion.Original)
strPubDateOriginal = objArgs.Row("PublicationDate", DataRowVersion.Original)
End Select
'add result to display string
gstrResult += "<b>" & strType & "</b> action in <b>RowUpdated</b> event:<br />" _
& "*<b> Original</b> values: ISBN='<b>" & strISBNOriginal & "</b>' " _
& "Title='<b>" & strTitleOriginal & "</b>' " _
& "PublicationDate='<b>" & strPubDateOriginal & "</b>'<br />" _
& "*<b> Current</b> values: ISBN='<b>" & strISBNCurrent & "</b>' " _
& "Title='<b>" & strTitleCurrent & "</b>' " _
& "PublicationDate='<b>" & strPubDateCurrent & "</b>'<br />"
'see if the update was successful
Dim intRows = objArgs.RecordsAffected 'expect 1 in success, zero or -1 on failure
If intRows > 0 Then
gstrResult += "* Successfully updated <b>" & intRows.ToString() & "</b> row<p />"
Else
gstrResult += "* Failed to update row<br />" & objArgs.Errors.Message & "<p />"
End If
End Sub
</script>
<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -