concurrency-continue.aspx
来自「Professional ASP.NET source code」· ASPX 代码 · 共 308 行
ASPX
308 行
<%@Page Language="C#" 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="C#" runat="server">
void Page_Load(Object sender, EventArgs e)
{
string strResult = ""; // to hold the result messages
string strConnect; // to hold connection string
DataSet objDataSet; // 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
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 = new OleDbConnection(strConnect);
// create a new DataAdapter using the connection object and select statement
Dim objDataAdapter = new OleDbDataAdapter(strSelect, objConnect);
try
{
// fill the dataset with data using the DataAdapter object
objDataAdapter.Fill(objDataSet, "Books");
}
catch (Exception objError)
{
// display error details
outError.InnerHtml = "<b>* Error while accessing data</b>.<br />"
+ objError.Message + "<br />" + objError.Source;
return; // and stop execution
}
// 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
Object[2] objValsArray;
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
string strConcurrent, strUpdate;
int intRowsAffected;
// need a new (separate) Connection and Command object
OleDbConnection objnewConnect = new OleDbConnection(strConnect);
OleDbCommand objnewCommand = new OleDbCommand();
objnewCommand.Connection = objnewConnect;
try
{
objnewConnect.Open()
// modify two of the book titles to force concurrency errors
strConcurrent += "<b>Concurrently executed</b>:<br />"
DateTime datNow = DateTime.Now();
string strNow = 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>"
+ intRowsAffected.ToString() + "</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>"
+ intRowsAffected.ToString() + "</b> row(s) affected<br />";
objnewConnect.Close();
}
catch (Exception objError)
{
// display error details
outError.InnerHtml = "<b>* Error while updating original data</b>.<br />"
+ objError.Message + "<br />" + objError.Source;
return; // and stop execution
}
outConcurrent.InnerHtml = strConcurrent;
//----------------------------------------------------------------
// now ready to do the update from the DataSet to the database
// declare a variable to hold a Transaction object
OleDbTransaction objTransaction;
try
{
// create an auto-generated command builder to create the commands
// to update, insert and delete the data
OleDbCommandBuilderobjCommandBuilder = 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 (Exception objError)
{
// 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;
return; // and stop execution
}
// 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)
{
DataRow objThisRow;
int intIndex ;
// check each table for errors in that table
foreach (DataTable objThisTable in objDataSet.Tables)
{
if (objThisTable.HasErrors)
{
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
foreach (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 ; intIndex <= objThisTable.Columns.Count - 1; intIndex++)
strResult += objThisRow(intIndex, DataRowVersion.Original) + ", ";
strResult = strResult.Substring(1, strResult.Length - 2);
strResult += "<br />Current Values: ";
for (intIndex = 0 ; intIndex <= objThisTable.Columns.Count - 1; intIndex++)
strResult += objThisRow(intIndex, DataRowVersion.Current) + ", ";
strResult = strResult.Substring(1, strResult.Length - 2);
// use function declared later in page to get underlying values
strResult += "<br />Underlying (database) Values: "
+ GetUnderlyingValues(strConnect, objThisRow["ISBN"]) + "<p />";
} // row
}
} // table
}
// display the results of the Update
outResult.InnerHtml = strResult;
}
//----------------------------------------------------------------
string GetUnderlyingValues(string strConnect, string strRowKey)
{
// select the existing column values from the underlying table in the database
string strSQL = "SELECT * FROM BookList WHERE ISBN='" + strRowKey + "'";
// create connection and command to access database
OleDbConnectionobjConnect = new OleDbConnection(strConnect);
OleDbCommandobjCommand = new OleDbCommand(strSQL, objConnect);
// declare the variables we// ll need
OleDbDataReader objReader;
string strValues = "";
int intIndex;
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())
{
for (intIndex = 0 l intIndex <= objReader.FieldCount - 1; intIndex++)
strValues += objReader.GetValue(intIndex) + ", ";
}
// close connection and return result
objConnect.Close();
GetUnderlyingValues = strValues.Substring(1, strValues.Length - 2);
catch (Exception objError)
{
GetUnderlyingValues = "*Error*";
}
}
</script>
<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?