When you switch to ADO. NET, you will need to know how to deal with the scenarios that previously known to be handled with ADO. NET, but now must be solved with ADO. NET. Just as the N-layer solution developed using Visual Basic, C ++, and ASP often relies on ADO to meet data access needs, Windows? Forms, Web forms, and Web services must also depend on ADO. NET. I have discussed how to use ADO. NET to handle some data access scenarios from the perspective of traditional ADO development. Some of the topics include retaining the row set as XML, processing the flow-only cursor, and executing the Command object. In this article, I will continue to discuss the use of ADO. NET development scenarios and how to deal with the use of traditional ADO technology. I will discuss the situation from the conventional ADO-only, static, keyset and dynamic cursors. I also want to discuss how to deal with concurrency and how the disconnected row set evolves from ADO to ADO. NET. Then, I will explain how to convert the code that uses traditional ADO to process batch updates to the code that uses ADO. NET (using DataAdapter and its four command objects.
Scattered Recordset Functions
In ADO. NET, most functions of ADO Recordset are divided into three main objects: DataReader, DataSet, and DataAdapter (see figure 1 ).
The ADO. NET DataReader object is designed as a server-side, read-only cursor. ADO. NET DataSet object is a storage tool for disconnected row sets. It stores records but does not connect to the data source. In fact, it does not care about the data source from which the row set is derived. DataSet is a binary object stored in the memory, but it can be easily serialized from XML to XML. This is similar to setting CursorType to adOpenStatic when the ADO Recordset object is disconnected from its associated Connection object (through the ActiveConnection attribute of Recordset) and setting CursorLocation to adUseClient. The ADO. NET DataAdapter object is a bridge between the connection and the DataSet object. It can load a DataSet from the data source through a connection and update the data source with the changed content stored in the DataSet. The behavior of ADO Recordset depends on the setting of its attributes (including the CursorType and CursorLocation attributes ). Different objects are built in ADO. NET to handle these specific situations, instead of using an object to deal with all situations.
Flow cursor
The traditional ADO exposes four different types of cursors, which can change the operation mode of the ADO Recordset object. The behavior of An ADO Recordset object varies greatly because of its CursorType attribute settings. For example, by setting CursorType to adOpenForwardOnly, Recordset can maintain a connection with its data source and must be traversed in only the forward direction. However, when you set the CursorType attribute to adOpenDynamic, the Recordset can traverse forward or backward, or even redirect the cursor to a specific row. Through its CursorType and CursorLocation attributes, ADO Recordset objects use a method to package many solutions into a single object. The methods used by ADO. NET are different, and different objects and methods are designed to handle various specific situations. In traditional ADO, read-only and read-only cursors are implemented by setting CursorType to adOpenForwardOnly and CursorLocation to adUseServer (which is also the default setting, this will make the Recordset object take the server-side cursor form only. The MoveNext method moves the Recordset to the next row, while the MovePrevious method is not allowed at all, but the MoveFirst method of the Recordset can be called. This is easy to misunderstand, because this method does not relocate the Recordset to the beginning of the current row set; instead, it calls the original SQL statement and refills the Recordset from the beginning, so it will be moved to the first record again. Every time you execute the MoveFirst method of the traditional ADO Recordset (set CursorType to adOpenForwardOnly), you can easily see this by opening the SQL event probe tool and observing the SQL Execution. When you need to traverse each row of thousands (or even more) of rows one by one or when you need a smaller row set but only need to traverse it once (maybe to load it to a selected list, this type of cursor is very common:
-- Forward-only Firehose Cursor in ASP and ADO
Set oRs. ActiveConnection = oCn
ORs. LockType = adLockReadOnly
ORs. CursorType = adOpenForwardOnly
ORs. CursorLocation = adUseServer
ORs. Open sSQL |
The equivalent of the traditional ADO flow cursor is the ADO. NET DataReader object. Just like traditional ADO Recordset operations, DataReader maintains a connection with its data source while enabling it, and can only traverse data in the forward direction. However, they are different. One of the differences is that a data provider (such as SQL Server? (SqlDataReader class) or ODBC Data Source (OdbcDataReader class) Write a separate DataReader type. The ADO. NET DataReader object is very efficient because it is built specifically for the purpose of only-in and read-only cursors. The traditional ADO only-in cursor is implemented through the same Recordset object (as a disconnected Recordset or even a data source-sensitive Recordset. DataReader is designed only for lightweight flow cursor.
// -- ASP. NET and ADO. NET in C #
SqlDataReader oDr = oCmd. ExecuteReader (); |
Sensitive data cursor
The traditional ADO forward-only cursor is now processed by the DataReader object. However, what about the keyset and dynamic server-side CursorType (CursorType = adOpenKeySet and CursorType = adOpenDynamic) that are very sensitive to changes in the basic database? The current version of ADO. NET does not publish multi-direction, rolling, and updatable server-side cursors. However, ADO. NET does provide many ways to avoid using these types of server-side cursors, because other recommended technologies can be considered. In addition to using DataSet and DataAdapter to retrieve and update data, there are also some good alternatives to use a scroll server-side cursor on the client side. For example, you can use a stored procedure, which is very efficient when running SQL processing on the server. You can also use the server side to retrieve data with DataReader only in the cursor, and then use the Command object to update the data separately. However, there are often more efficient ways to modify data than to use updatable server-side cursors.
In an N-tier environment, one of the problems with server-side scroll cursors is that they need to be kept on the server. Therefore, if an application uses a server-side scroll cursor in the middle layer, the client layer needs to maintain the connection with the business layer where the scroll cursor is located. In this way, the business objects must be retained when the client application screen that uses the same scroll cursor exists. These scalability problems have been well documented in various documents, but in some cases, server-side cursors are still very valuable, such as the case where only the flow cursor is required. For example, server-side cursors are required when applications need to focus on data concurrency. With traditional ADO, Recordset can be opened with a dynamic cursor, which enables the Recordset to be informed of the insert, change, and delete operations performed by all other users. This type of server-side cursor is very sensitive to changes made by other users and can be used to take proactive measures when applications encounter concurrency issues. For example, dynamic cursors in traditional ADO are often used in such scenarios: The application is prepared to save changes in the database, but it is necessary to first know whether other people have changed the same record. When you change a record value in a dynamic Recordset, the value is automatically updated in the dynamic Recordset on the server:
-- Dynamic Cursor in ASP and ADO
Set oRs. ActiveConnection = oCn
ORs. LockType = adLockOptimistic
ORs. CursorType = adOpenDynamic
ORs. CursorLocation = adUseServer
ORs. Open sSQL |
Server-side cursors and concurrency
Concurrency is a reasonable and common problem in many enterprise applications, but the cost of using server-side cursors to solve this problem may be very high. So what alternative methods does ADO. NET address the concurrency issue? A common technique is to allow an application to submit changes to the database and then cause special exceptions when a concurrency problem occurs. There is a special type of Exception named DBConcurrencyException, which is derived from the Exception base class. Because DataSet stores the original value and the value to be changed in each column in a row, it knows how to compare the value with the database to automatically find the concurrency issue. For example, assume that you change the value of the firstname field from Lloyd to Lamar and submit the changes to the database. The changes stored in DataSet are transmitted to the database through the Update method of DataAdapter. Before the data is actually saved, if the database name is no longer Lloyd, And now it becomes Lorenz, A DBConcurrencyException will be thrown. This exception can be captured and handled in any way that best suits the needs of the application. For example, the "last priority" rule can be adopted, this allows you to cancel changes, rewrite, force updates, or other technologies.
Public DataSet SaveData (DataSet oDs)
{
String sMethodName = "[public void SaveData (DataSet oDs)]";
// ================================================ ================================
// -- Establish local variables
// ================================================ ================================
String sProcName;
String sConnString = "Server = (local); Database = Northwind; Integrated
Security = SSPI ";
SqlDataAdapter oDa = new SqlDataAdapter ();
SqlTransaction oTrn = null;
SqlConnection oCn = null;
SqlCommand oInsCmd = null;
SqlCommand oUpdCmd = null;
SqlCommand oDelCmd = null;
Try
{
// ================================================ ================================
// -- Set up the Connection
// ================================================ ================================
OCn = new SqlConnection (sConnString );
// ====================================== |
