Datareader and Dataset

Datareader and Dataset

Use datareader, dataset, dataadapter, and dataview

ADO. NET provides two objects for retrieving relational data and storing it in the memory: DataSet and datareader. Dataset provides a full set of relational data in the memory, including the relationships between tables, such as tables, order, and constraints. Datareader provides fast, forward-only, and read-only data streams from the database.

When using dataset, dataadapter (or commandbuilder) is generally used to interact with the data source. dataview is used to sort and filter data in dataset. Dataset can be inherited to create a dataset of the reinforcement type. It is used to expose tables, rows, and columns as reinforcement type object attributes.

The following content includes when to use dataset or datareader, how to optimize access to the data they contain, and how to optimize the use of dataadapter and dataview (including commandbuilder ).

Comparison between dataset and datareader

In Design ApplicationProgramTo use dataset or datareader, consider the functions required by the application.

Dataset is used to implement the following functions of the application:

L multiple separated tables in the operation result.

L operate on data from multiple sources (such as hybrid data from multiple databases, XML files, and workbooks.

Exchange data between layers or use XML Web Services. Unlike datareader, dataset can be passed to a remote client.

L duplicate use of the same row set by buffering to improve performance (such as sorting, searching or filtering data ).

L a large amount of processing is performed on each row. Extended processing on the rows returned by using datareader will lead to a longer connection time than necessary, thus reducing the efficiency.

L use XML operations (such as XSLT conversion and XPath query) to maintain data.

Use datareader when the application requires the following features:

L no data buffering is required.

L The result set being processed is too large to be fully stored in the memory.

L The data needs to be quickly accessed at one time, only forward read-only.

Note: When filling in dataset, dataadapter uses datareader. Therefore, the performance obtained by using dataadapter instead of dataset is to save the memory consumed by dataset and the cycle required to assemble dataset. Most of the improvements to this performance are not real-name, so you should make the design decisions based on the required functions.

Benefits of using strong Dataset

Another advantage of using dataset is that it can be inherited for creating a strongly Typed Dataset. Benefits of a strong dataset include design-time check and Visual Studio. NET statement filling of a strong dataset. When you fix the outline or link structure for the dataset, you can create a strong dataset and use rows and columns as the attributes of the object rather than the set of items. For example, you can expose the name attribute of the customer object instead of the column name of a row in the exposed customer table. Strong dataset is derived from the dataset class, so it does not sacrifice any functions of dataset. That is to say, strong dataset can also be remote and provided as a data source for data binding controls (such as DataGrid. If you do not know the outline, you can also benefit by using the common dataset, but you lose the additional features of the strong dataset.

Processing null values in a strongly Typed Dataset

When using a strongly typed dataset, You can annotate the XML outline Definition Language (XSD) of dataset to ensure that the strongly Typed Dataset correctly processes null references. The nullvalue annotation allows you to replace dbnull with a specific value of string. Empty, retain null reference, or generate an exception. Select the content that depends on the application. By default, an exception occurs when an empty reference is encountered.

Refresh data in Dataset

If you want to use the updated value to refresh the value in the dataset from the server, use dataadapter. fill. If the primary key is defined on the data table, dataadapter. Fill matches the new row based on the primary key and changes the server data to an existing row. The rowstate of the refreshed row is set to unchanged, even if it is modified before refreshing. Note that if a primary key is defined for the data table, the new row of dataadapter. Fill may duplicate the primary key value.

If you want to refresh a table with the current value of the server and keep the row in the table changing, you must first use dataadapter. fill combines it, fills in a new data table, merges the data table (merge) into a data set, and sets the value of preservechanges to true.

Search data in Dataset

When you query rows that meet specific conditions in a dataset, you can use index-based tables to improve performance. When you specify a primary key value for a data table, an index is created. An index is also created when a data view (dataview) is created for a data table. The following are some tips for using index-based viewing:

If the query is performed on the primary key column of the data table, use able. Rows. Find to replace able. Select.

To query non-primary key columns, you can use the data view to speed up multiple data queries. When sorting is added to the data view, indexes used for search are created. The data view exposes the find and findrows methods for querying lower-layer data tables.

If you are not querying the sort view of a table, you can also create a data view for the data table to obtain the benefits of viewing the table based on indexes. Note that this is the only benefit if you execute multiple queries on the data. If you only execute a single query, the index creation process will reduce the performance by using the index.

Data View (dataview) Structure

When a data view is created and sort, rowfilter, or rowstatefilter or its attributes are modified, the data view creates an index for the data in the lower-layer data table. When creating a data view object, use the values of sort, rowfilter, and rowstatefilter as the data view constructor of the parameter. The result is an index. Create a "null" data view, and set the sort, rowfilter, and rowstatefilter attributes to create an index at least twice.

Datareader is a one-time forward read-only data reader that accepts the ecexutereader method of xxxcommand to query database results. ecexutereader only returns data.
Dataadapter is similar to xxxcommand, which has an SQL statement and a connection object. The fill method is called to save the database query results to the datasable in dataset. The fill method retrieves data and the retrieval mode, that is, the field name and data type of each field, the maximum length and other attributes.