Best practices for using Ado.net

Ado. NET, as Microsoft's latest data access technology, has been widely used in enterprise development. For front-line developers, the most effective way to improve the level of application and solve practical problems is to exchange the best time experience with each other when mastering basic concepts and techniques. In this article, two ado.net experts gave the reader a lot of practical experience without reservation or detail.

Brief introduction

This article is provided for you at Microsoft ADO. NET application for optimal performance, scalability, and functionality, as well as best practices for using the objects available in ado.net, and suggestions to help optimize the design of ado.net applications.

. NET Framework Data Provider

. NET Framework provides a bridge between application and data sources in the data provider (Provider). NET Framework Data provider can return query results from the data source, execute commands on the data source, and propagate changes in the dataset to the data source. This article contains information about which. NET Framework data providers are some of the techniques that best suit your needs.

Use which. NET Framework Data Provider?

For the best performance of your application, use the one that best suits your data source. NET Framework Data Provider. There are a number of data providers that you can use for your application.

Connect to SQL Server 7.0 or later

To achieve optimal performance when connecting to Microsoft SQL Server 7.0 or later, use SQL Server. NET Data provider. SQL Server. NET Data provider is designed to provide direct access to SQL Server without any additional technology layers.

Connecting to an ODBC data source

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

Using DataReader, Datasets, DataAdapter, and DataView

Ado. NET provides the following two objects for retrieving relational data and storing it in memory: Datasets and DataReader. The dataset provides a relational representation of the data in memory, a set of data that includes tables (which contain data, sort data and constrain data), and relationships between tables. DataReader provides a fast, forward-only, read-only stream of data from a database.

When using a DataSet, DataAdapter (or possibly CommandBuilder) is often used to interact with the data source. When using a dataset, you can also use DataView to apply sorting and filtering to the data in the dataset. You can also inherit from a dataset and create a strongly typed dataset that exposes tables, rows, and columns as strongly typed object properties.

The following topics include information about the best time to use a 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 a dataset or a DataReader.

To do the following through an application, use the dataset:

1 navigation between multiple discrete tables of results.

2 manipulate data from multiple data sources (for example, mixed data from multiple databases, an XML file, and a spreadsheet).

3 Exchange data between tiers or use XML Web services. Unlike DataReader, a dataset can be passed to a remote client.

4 reuse the same collection of records to gain performance improvements through caching (such as sorting, searching, or filtering data).

5 each record needs to perform a lot of processing. Extended processing of each row returned using DataReader will prolong the necessary time to service the DataReader connection, which affects performance.

6 use XML operations to manipulate data, such as Extensible Stylesheet Language Transformations (XSLT transformations) or XPath queries.