Some applications use the "Last IN WINS) method when updating database data. Using the "Back" method to update the database when the update is compared to the original record, it may overwrite all the changes made by other users since the last refresh record. However, sometimes the application needs to determine whether the data has been changed since the initial read before performing the update. Data Access Logic components can manage code that is locked and concurrent. There are two ways to manage locking and concurrency: • Conservative concurrency. Users who read a row of data for updates can set a lock in the data source. Any other users cannot change the row before the user is unlocked. • Open concurrent. The user does not lock the line when reading a row data. Other users can freely access the line at the same time. When a user wants to update a row data, the application must determine if other users have changed since the row is read. Try updating records that have been changed will result in concurrent conflicts. Using conservative concurrent conservative concurrency mainly used for data contention and the cost of protecting data by locking to protect the cost of rollback transactions when concurrent; If the lock time is short (for example, in the programmed record), the conservative concurrency effect is achieved. Conservative concurrency requires a persistent connection to the database, and because the record may be locked for a long time, it is not possible to provide scalable performance when the user interacts with the data. Using open concurrent open-handed concurrently applicable to data bands low or require read-only access data. Open concurrency can reduce the number of locks required to reduce the load of the database server and improve the performance of the database. Open concurrency is widely used in .NET to meet the needs of mobile and offline applications. In this case, long-term locking data is not feasible. In addition, keep the recording lock also requires a persistent connection to the database server, which is impossible in offline applications. Test an open concurrent conflict test Open concurrent conflict has a variety of: • Use a distributed timestamp. Distributed timestamps are suitable for environments that do not require coordination. Add a timestamp column or version column in each table of the database. The timestamp column returns together with the query on the table content. When trying to update, the timestamp value in the database will be compared to the original timestamp value in the modified row. If the two values match, execute the update, and the timestamp column is updated to the current time to reflect the update. An open concurrent conflict occurs if the two values do not match. • Reserve a copy of the original data value. Retain a copy of the original data value when you query the data of the database. When updating the database, check if the current value of the database matches the original value. • The original value is saved in the DataSet, when updating the database, the data adapter can use the original value to perform an open concurrent check. • Use the concentrated timestamp. Define a set of timestamp tables in the database to record updates to any row in any table. For example, the timestamp table can display the following information: "John updated the line 1234 in Table XYZ at 2:56 pm March 26, 2002. Concentrated timestamps are suitable for check out programs and some offline client scenarios, which may need to be clearly locked owners and alternative management. In addition, the concentrated timestamp can also provide an audit as needed.
Manually implement the open-ended and send it to consider the following SQL query: select column1, column2, column3 from table1 To test open concurrent conflicts when updating Table1, you can issue the following Update Table1 Set Column1 = @ newValueColumn1, SET Column2 = @ NewValueColumn2, Set Column3 = @ newValueColumn3 where column1 = @ OldValueColumn1 and coloruMn2 = @ OldValueColumn2 and color = @ OldValueColumn3 If the original value matches the value in the database, the update is performed. If a value is modified, the WHERE clause will not find the corresponding match, so that the update will not modify the row. You can change this technology a slightly change, ie, only apply the WHERE clause for a particular column, so that the data is not overwritten if a specific field has been updated since the last query. Note: Always return a value of a row in a unique identifier, such as a primary keyword for the WHERE clause of the UPDATE statement. This ensures that the UPDATE statement updates the correct row. If the column in the data source allows null values, you may need to extend the WHERE clause to check the empty references that match the local table and the data source. For example, the following Update statement will verify that the empty reference (or value) in the local row is still matched with the empty reference (or value) in the data source. UPDATE Table1 Set Column1 = @ NewColumn1Value WHERE (@ OldColumn1Value IS NULL AND Column1 IS NULL) OR Column1 = @ OldColumn1Value DataSet implemented using the data adapter and the optimistic concurrency: DataAdapter.RowUpdated event can be used in conjunction with the techniques described earlier to notice your application Open concurrent conflicts occurred in the program. Whenever trying to update the modified row in the DataSet, RowUpdated events will be triggered. You can use the RowUpdated event to add special processing code, including processing, processing, add custom error information, and add retry logic. The RowUpdated event handler receives a RowUpdatedEventArgs object that has a RECORDSAFFECTED property that can display how much it affects the update command for a modified row in the table. If the update command is set to test open concurrency, the RecordSaffected property will be 0 when an open concurrent conflict occurs. Set the RowUpdatedEventArgs.status property to indicate the operations to take; for example, you can set this property to UpdateStatus.skipCurrentrow to skip updates to the current row, but continue to update other rows in the update command. Another way to test concurrent errors using a data adapter is to set the DataAPter.ContinueUpdateOnError property to True before calling the Update method. After completing the update, call the getRRORS method of the DataTable object to determine which rows have occurred. Then, use these rows of ROWERROR properties to find specific detailed error messages. The following code example shows how the Customer data access logic component checks concurrent conflicts.
This example assumes that the client retrieves a DataSet and modifies the data, and then passes the DataSet to the UpdateCustomer method in the data access logic component. The UpdateCustomer method will update the appropriate customer record by calling the following stored procedures; only when the customer ID is not modified, the store is not modified when the store is updated: create procedure customerupdate {@companyname varchar (30), @OLDCUSTOMERID VARCHAR (10 ), @oldCompanyName varchar (30)} aS UPDATE Customers set CompanyName = @CompanyName WHERE CustomerID = UpdateCommand property @oldCustomerID AND CompanyName = @oldCompanyName GO UpdateCustomer in the method, the following code sample for testing a data adapter optimistic concurrency, Then use the RowUpdated event to test open concurrent conflicts. If you encounter an open concurrent conflict, the application will indicate an open concurrent conflict by setting RowError to be updated. Note that the parameter values passing to the WHERE clause in the Update command are mapped to the original values of each corresponding column in the DataSet.
Method UpdateCustomer // CustomerDALC class public void UpdateCustomer (DataSet dsCustomer) {// connected to the Northwind database SqlConnection cnNorthwind = new SqlConnection ( "Data source = localhost; Integrated security = SSPI; Initial Catalog = northwind"); // Create a adapter to access data Northwind Customers table SqlDataAdapter da = new SqlDataAdapter (); // set the data adapter UPDATE command, call the stored procedure "UpdateCustomer" da.UpdateCommand = new SqlCommand ( "CustomerUpdate", cnNorthwind); da.UpdateCommand. CommandType = commandType.StoredProcedure; / / Add two parameters to the Update command of the data adapter, / / Specify information for the WHERE clause (for checking open concurrent conflicts) Da.UpdateCommand.Parameters.add ("@ companiesName", SqldbType .Nvarchar, 30, "companyName"); // Specifies the original value of Customerid as the first WHERE clause parameter SQLParameter myparm = da.UpdateCommand.Parameters.add ("@oldcustomerid", sqldbtype.nchar, 5, "CustomerID "); myparm.sourceversion = DATAROWVERSION.ORIGINAL; // Specifies the original value of CustomerName as the second WHERE clause parameter myparm = da.updatecommand.Parameters.add (" @oldcompanyname ", sqldbtype.nvarchar, 30," CompanyName "); myparm.sourceversion = DATAROWVE rsion.original; / / Add a handler DA.ROWUPDATED = New SQLROPDATEDEVENTHANDLER (ONROWUPDATED); // Update Database DA.UPDATE (DS, "Customers"); Foreach (DataRow Myrow In Ds.Tables ["Customers "].ROWS) {if (Myrow.haserRRORS) console.writeline (Myrow [0] " MyRow.RowError);}} // How to process the RowUpdated event. If you register this event but do not process it, // triggers an SQL exception.
protected static void OnRowUpdated (object sender, SqlRowUpdatedEventArgs args) {if (args.RecordsAffected == 0) {args.Row.RowError = "optimistic concurrency conflict encounter"; args.Status = UpdateStatus.SkipCurrentRow;}} When a When performing multiple SQL statements during the SQL Server store, the SET NOCOUNT ON option can be used for performance reasons. This option will prohibit SQL Server to return a message to the client each time you perform a statement, so that network traffic can be reduced. However, this will not be able to check the RecordsAffected attribute as the previous code example. The RecordSaffected property will always be 1. Another method is to return the @@ rowcount function (or specify it as an output parameter) during the stored procedure; @@ rowcount contains the number of records when the last statement is completed during the store, and even uses the set nocount on, This function will be updated. Therefore, if the previous SQL statement executed during the store is the actual UPDATE statement, and the @@ rowcount is specified as the return value, the application code can be modified as follows: // Add another parameter to the UPDATE command to the data adapter To receive the return value. // You can name this parameter arbitrarily. myparm = da.UpdateCommand.Parameters.add ("@ rowcount", sqldbtype.int); myparm.direction = parameterDirection.ReturnValue; // Change the onrowupdated method to check the value of this parameter // instead of the RecordSaffected property. protected static void OnRowUpdated (object sender, SqlRowUpdatedEventArgs args) {if {args.Row.RowError = "optimistic concurrency conflict encounter" (args.Command.Parameters [ "@ RowCount"] Value == 0.); args.Status = Updatestatus.skipcurrentrow;}} Avoid conclude: 1. When you use it in the WHERE sentence, you can return a row, such as a primary keyword, which makes sure the Update statement updates the correct row.
