Practical experience of ADO. Net Without retained exposure (1) [reprinted]

As Microsoft's most practical data access technology, ADO. net has been widely used in enterprise development. For front-line developers, the most effective way to improve the application level and solve practical problems after mastering the basic concepts and technologies is to exchange the best time and experience of each other. In this article Article Two ADO. Net experts gave readers a lot of practical experience without reservation.

Introduction

This article provides you with an application in Microsoft ADO. netProgramTo achieve and obtain the best performance, scalability, and function solutions. It also describes the use of ADO. net and provides some useful methods to optimize ADO. NET application design suggestions.

. NET Framework data provider

The data provider in the. NET Framework serves as a bridge between applications and data sources .. The net framework data provider can return query results from the data source, execute commands on the data source, and disseminate changes in dataset to the data source. This article includes some tips about which. NET Framework data provider is the most suitable for your needs.

Which. NET Framework data provider is used?

To achieve optimal performance for your applications, use the. NET Framework data provider that best suits your data sources. There are many data providers available for your applications.

Connect to SQL Server 7.0 or later

To achieve optimal performance when connecting to Microsoft SQL Server 7.0 or later, use the SQL server. NET data provider. The SQL Server. NET data provider is designed to directly access SQL server without any additional technical layer.

Connect to the ODBC Data Source

The ODBC. NET data provider can be found in the Microsoft. Data. ODBC namespace. Its architecture is the same as the. NET data provider for SQL Server and OLE DB. The ODBC. NET data provider follows the naming convention-prefix "ODBC" (for example, odbcconnection) and uses a standard ODBC connection string.

Use datareader, dataset, dataadapter, and dataview

ADO. NET provides the following two objects for retrieving relational data and storing it in memory: DataSet and datareader. Dataset provides a relational representation of In-memory data, a set of data including some tables (these tables contain data, Sort data, and constrain data), and relationships between tables. Datareader provides a fast, forward-only, read-only data stream from the database.

When dataset is used, dataadapter (or commandbuilder) is often used to interact with the data source. When using dataset, you can also use dataview to sort and filter data in dataset. You can also inherit from dataset to create a strong-type dataset, which is used to expose tables, rows, and columns as strong-type object attributes.

The following topics involve the best time to use dataset or datareader, how to optimize access to the data they contain, and how to optimize the techniques for using dataadapter (including commandbuilder) and dataview.

Dataset and datareader

When designing an application, consider the level of functionality required by the application to determine whether to use dataset or datareader.

Dataset is required to perform the following operations through the application:

1) navigate between multiple discrete tables in the result.
[Transferred from: 51item.net]
2) operate on data from multiple data sources (for example, mixed data from multiple databases, an XML file, and a workbook.

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

4) Reuse the same set of records to improve the cache performance (such as sorting, searching, or filtering data ).

5) each record must be processed in large quantities. Extended processing of each row returned by datareader will prolong the necessary time to serve the datareader connection, which affects the performance.

6) perform operations on data using XML operations, such as extensible style sheet language conversion (XSLT conversion) or XPath query.

Use datareader in applications in the following cases:

1) Data does not need to be cached.

2) The result set to be processed is too large to be stored in the memory.

3) Once you need to quickly access data in the forward and read-only mode.

Note: When filling in dataset, dataadapter uses datareader. Therefore, replacing dataset with dataadapter improves performance by saving dataset memory usage and filling the cycles required by dataset. Generally, this performance improvement is symbolic. Therefore, design decisions should be based on the required functions.

Benefits of using strong Dataset

Another advantage of dataset is that it can be inherited to create a strongly Typed Dataset. The benefits of a strongly Typed Dataset include design-time type checks and the benefits of Microsoft Visual Studio. net for the end of a strongly Typed Dataset statement. After modifying the dataset architecture or relational structure, you can create a strong-type dataset, publish rows and columns as object attributes, rather than as items in the set. For example, the name attribute of the customer object is disclosed without disclosing the name column of the row in the customer table. The typed dataset is derived from the dataset class, so it does not sacrifice any functions of the dataset. That is to say, the typed dataset can still be remotely accessed and provided as a data source for data binding controls (such as DataGrid. If the architecture is unknown in advance, it can still benefit from the common dataset function, but it cannot benefit from the additional features of strong dataset.

Process null references in a strongly Typed Dataset

When using a strongly typed dataset, you can use the XML Schema Definition Language (XSD) schema of dataset to ensure that the strongly typed dataset can correctly process null references. The nullvalue identifier allows you to replace dbnull with a specified string. Empty value, retain a null reference, or cause an exception. The option to select depends on the context of the application. By default, if a null reference is encountered, an exception is thrown.

Refresh data in Dataset

If you want to use the updated value on the server to refresh the value in dataset, use dataadapter. fill. If a primary key is defined on the datatable, dataadapter. Fill matches a new row based on the primary key, and changes the value on the Application Server when it is changed to an existing row. Even if the data is modified before the refresh, The rowstate of the refresh row is set to unchanged. Note: If a primary key is not defined for the datatable, dataadapter. Fill adds a new row with a primary key value that may already exist.

if you want to refresh the table with the current value from the server and keep any changes made to the rows in the table, you must first use dataadapter. fill the table with fill, fill in a new able, and then combine the datatable into the dataset with the preservechanges value true.