ASP. NET 3.5 core programming learning notes (14): dataadapter

Data adapter

The data adapter object acts as a two-way bridge between the data source and the DataSet object. Dataset is a non-connected data container. The adapter fills it and submits its data to a specific data source.

The biggest difference between a command and a data adapter is the return method after obtaining data. The query command returns a read-only cursor-data reader. The data adapter can perform data access to obtain all the data and package it in the memory container-dataset or datatable. In fact, the data adapter is an additional abstraction layer built on command/data reader pairs. The data adapter uses commands internally to query, uses a data reader to traverse all records, and fills in the dataset of the user.

Sqldataadapter class

The data adapter class inherits from the dbdataadapter class and implements the idbdataadapter and icloneable interfaces.

The attributes of the sqldataadapter class are listed in the following table:

 

The data adapter is a two-way channel that can read data from the data source to the memory table or write data in the memory back to the data source.

The xxxcommand Member of the idbadapter interface controls the way in which memory data is written to the database during the update operation. Selectcommand has some special characteristics. It not only plays a role in batch update, but also is an essential member for filling operations.

Once the data is loaded into the memory, the. NET program can perform non-Connection Client updates on it. Batch update is a data provider process triggered by a client application. It applies all pending changes in the memory to the data source. During the execution of this process, you need a set of commands for a specific database management system to execute three basic operations: insert, update, and delete. The sqlcommand objects stored in the insertcommand, updatecommand, and deletecommand attributes are commands for these operations respectively.

Ado. Net batch update includes a series of commands that are submitted to the database in sequence through the data adapter. As a developer, we only need one command to trigger batch update. It should be remembered from the concept that the batch update of ADO. NET is different from a series of queries submitted by a single command. It does not mean that all the Batch Commands and data are moved to the DBMS and executed there.

Batch update is powerful, but not applicable to ASP. NET applications. The problem is that Web applications work on stateless protocols. In this way, to make the entire framework run normally, we need to cache the memory table to the session, which is not acceptable to all applications. In addition, it should be noted that the use of batch update can reduce a lot of coding work, and can be configured to handle complex updates. But in ADO. net
In 1.x, because each operation uses its own command, batch update does not have much performance advantage. From ADO. net
Starting from 2.0, we can use the new updatebatchsize attribute to centralize multiple update operations in one command.

The sqldataadapter method is shown in the following table:

The data adapter object uses the selectcommand attribute to obtain the mode and data from the data source. The connection object associated with selectcommand does not need to be opened manually. If the connection is closed before it is read and executed, it is automatically opened when data is obtained and closed after reading. If the connection has been opened manually, it will remain in this status until it is closed manually.

The data adapter object uses the fill method to populate the data obtained through the query into the memory object. The memory structure is dataset or able object. Filling a DataSet object is the table in it. The data adapter can create a table for each result set generated by the program. The ing code of the table determines the method (if a table already exists, it will be updated ). The process of reflecting the result set to a column is divided into two stages: Table ing and column ing. In the first phase, the data adapter determines the name of the datatable, which contains the current result set record. The default name specified for each able can be changed by the programmer at will.

The default name of the datatable depends on the signature of the called fill method. For example:

       DataSet ds = new DataSet();
       adapter.Fill(ds);
       adapter.Fill(ds, “MyTable”);

In the first call, the nouns in the first result set generated by the query are table by default. If the query generates multiple result sets, for other tables, index numbers, such as Table1 and Table2, are appended to the default name. For the second call, the first result set is mytable, and other tables are mytable1 and mytable2 in sequence.

There are two ways to change the table name: one is after the dataset is filled, and the other is to create a table ing. For the latter, we can map other names to the table to be changed by setting. To define table ing on the data adapter, you must use the tablemappings attribute.

We can also use the fill method to fill the able. In this case, only the first result set is used and only one ing is executed, that is, column ing.

       DataTable dt = new DataTable();
       adapter.Fill(dt);

Load settings

In ADO. NET 2.0 and later versions, you can use the fillloadoption attribute to specify how data is loaded to various data tables during the fill operation. The fillloadoption attribute accepts the loadoption enumeration value. The following table lists the meanings of different values:

 

In each case, the specified behavior takes effect only when the primary key of the input row matches the primary key of the existing row.

Overwritechanges can be used to initialize a table with new data. Preservechanges is suitable for synchronizing existing memory data with the current database status. If you want to update the value of the original data read by the database in dataset, you can use upsert.

The row in the memory must maintain two types of values: the current value and the original value. The current value is the value obtained when the cell is read, and the original value is the value stored in the cell during the last commit. When assigning values to a new inserted row, the current value is set. The original value is null ). The assigned value can only take effect in the database after it is submitted to the database. To submit a row, you can call the acceptchanges method. After you call this method, the current value is copied and overwritten. The status of the row changes to unchanged and no pending changes are made.

Submit the changes of a row (datarow class) to the original data version:

Row. acceptchanges ();

Read the current value of a row:

Row ["firstname"]. tostring ();

Read the original value of a row:

Row ["firstname ",
Datarowversion. original]. tostring ();

Table ing mechanism

For the result set generated by the query, the. NET data provider will give it a default name. The adapter searches tablemappings for data items that match the default name of the result set being read. If a match is found, the data adapter reads the mapped name. Then, by specifying the name in the ing, it tries to determine the position of the able object in the dataset.

The tablemappings attribute represents a collection object of the ableablemappingcollection type. Each data item is a datatablemapping object, which represents a pair of names: source table name and memory table name.

Sample Code:

       DataSet ds = new DataSet();
       DataTableMapping dtm1, dtm2, dtm3;
       dtm1 = adapter.TableMappings.Add(“Table”, “Employees”);
       dtm2 = adapter.TableMappings.Add(“Table1”, “Products”);
       dtm3 = adapter.TableMappings.Add(“Table2”, “Orders”);
       adapter.Fill(ds);

There is no limit on the Data mapped to the table, and the total number of mappings is irrelevant to the expected number of result sets.

To assign a name that is easy to remember to a dataset table, use the following code:

       DataSet ds = new DataSet();
       adapter.Fill(ds);
       ds.Tables[“Table”].TableName = “Employees”;
       ds.Tables[“Table1”].TableName = “Products”;

Another point in the ing mechanism is column ing. Column ing is used to establish a link between the columns in the result set and the able object to be mapped. Column ing is stored in columnmappings set attributes and its type is ableablemapping.

Sample Code:

DataSet ds = new DataSet();
DataTableMapping dtm1;
dtm1 = adapter.TableMappings.Add(“Table”, “Employees”);
dtm1.ColumnMappings.Add(“employeeid”, “ID”);
dtm1.ColumnMappings.Add(“firstname”, “Name”);
dtm1.ColumnMappings.Add(“lastname”, “FamilyName”);
adapter.Fill(ds);

When the adapter cannot find the table or column ing, or cannot find the required able or datacolumn, a lightweight exception is thrown.

When the data adapter collects the data to be filled with dataset, the ing operation is executed in two cases: when the default name cannot be found in the tablemappings set; when a column name in the columnmappings set does not exist. To handle these two exceptions, you must use the missingmappingaction attribute to define the behavior of the data adapter. This attribute is of the missingmappingaction Enumeration type. Its definition is as follows:

 

In the table ing stage, if the table name in the dataset cannot be determined or the dataset table does not contain columns with the expected ing name, the missing mode operation is required. The missingschemaaction attribute is used to specify the operations to be performed when the table mode is missing. This attribute is of the missingschemaaction Enumeration type. Its definition is as follows:

 

Missingmappingaction and missingschemaaction do not have any real exception overhead, but they still affect the code efficiency. If you need to repeat an empty dataset in a fixed mode, you can use the DataSet object of the pre-fill mode information. The fillschema method ensures that all required objects are created in advance.

Datatable []
Fillschema (Dataset ds, schematype mappingmode );

This method accepts a dataset, and adds the table required to associate the query command with the adapter. This method returns all the created able objects (including only the mode but no data ). Schematype is an enumeration type. The values are as follows:

 

Batch update

Conflict Detection during the update process may remind you of a considerable amount of overhead, or even greatly reduce the advantages of the batch update solution. In an environment with low data competition, batch update is more effective.

To submit the client changes to the server, you can use the update method of the data adapter. Data can only be submitted in tables. If no table name is specified, the default name-table is used. For example:

Adapter. Update (DS,
"Mytable ");

The update method prepares and executes specially generated insert, update, and delete statements for the rows inserted, updated, and deleted in a specific table. This method returns an integer indicating the number of rows successfully updated. If the value of continueupdateonerror is set to true, the update continues after an error occurs during update of a row until all rows are processed. The row in dataset that is successfully updated is submitted and marked as unchanged.

Command Generator

In ADO. net, the insert, update, and delete commands can be automatically generated and exposed to the data adapter. The command generator object can do this, but it cannot apply to all situations. The automatic generation of commands can only be executed in a specific environment. Specifically, if the table is obtained through multi-table join or the table contains columns obtained through calculation (or total), the command generator cannot generate any commands. The command generator can be used only when the adapter executes the update method. How does the command generator generate an update command for a generalized table? This is the problem to be solved by the selectcommand attribute.

To generate various update commands, the command generator uses selectcommand to obtain the required metadata. We must set selectcommand to a query string containing the primary key column name and the column name to manipulate. Only these columns will be updated, and the rows to be updated or deleted will be identified by the primary key.

The data adapter must be associated with the builder through the constructor of the command generator. As follows:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = “Select employeeid, lastname From employees”;
cmd.Connection = conn;
adapter.SelectCommand = cmd;
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();

The generator requests metadata and generates and caches commands during the first request. Each command can be obtained through corresponding methods, including getinsertcommand, getupdatecommand, and getdeletecommand. Note: The command builder does not automatically set the command attributes of the data adapter. You must manually set the command attributes of the adapter.