This article is a little experience in using the ADO.NET, including the comparison between the various objects provided by the Microsoft ADO.NET framework, how to choose a better solution. These will help optimize Microsoft ADO.NET applications.
Since the author is developing the SQL Server .NET data provider under the system.data.sqlclient namespace, this article is expanded under the above framework.
ADO.NET
ADO.NET is the data access model of the .NET application. It can be used to access relational database systems, such as SQL Server 2000, and many other data sources that have OLE DB supplies. To a certain extent, ADO.NET represents the latest version of ADO technology. However, ADO.NET introduces some major changes and innovations, which are specifically used to loose, essentially non-linking web applications.
An important change introduced by ADO.NET is that the ADO Recordset object is replaced with a combination of DataTable, DataSet, DataAdapter, and DataReader objects. DataTable represents a collection of rows from a table, which is similar to Recordset. DataSet represents the collection of DataTable objects and the relationships and restrictions that are bound to other tables. In fact, DataSet is an associated structure in memory supported by built-in extended marker language (XML).
One of the main features of DataSet is that it does not know anything about the underlying data source, and these data sources may be used to fill them. This is an isolated independent entity for indicating data sets, and it can be passed from one component to another by different layers of a multi-layer application. It can also be serialized as an XML data stream, so it is ideal for data transmission between different types of platforms. ADO.NET uses the DataAdapter object to create channels to send to data from DataSet and underlying data sources. The DataAdapter object also supports enhanced batch update features, before this is the Recorder's related functions.
2. When is Dataset and DataReader?
ADO.NET provides two objects to retrieve relational data and store it in memory: Dataset and DataReader. DataSet provides a relationship representation of data in memory, and a complete set includes some tables (these tables contain data, sorting and constrained data), and the relationship between tables. DataReader provides a fast, enter, read-only data stream from the database.
When designing an application, consider the level you need to function in your application to determine the use of DataSet or DataReader.
To do the following by the application, use DataSet:
u Navigate between the plurality of discrete sheets of the result.
u Operate data from multiple data sources (eg, mixed data from multiple databases, an XML file, and a spreadsheet).
u Exchange data between layers or use XML web services. Unlike DataReader, DataSet can be passed to the remote client.
u Reuse the same row in order to improve performance by cache (such as sort, search, or filtering data).
u Per line performs a lot of processing. The extension processing of each line returned to DataReader will extend the necessary time to serve the DataReader, which affects performance.
u Operate data using XML operations, such as scalable style sheet language conversion (XSLT conversion) or XPath query.
For the following cases, you must use DataReader in your application:
u does not need to cache data.
u The result of the hand is too big, and the memory can't be put. u Once you need to enter, you will quickly access the data.
3. Refresh the data in DataSet
Use DataAdapter.Fill if you want to refresh the value in the DataSet with the update value on the server. If there is a primary key defined on a DataTable, DataAdapter.Fill matches the primary key and makes the value on the application server when changing to an existing line. Even if they modified them before refreshing, the RowState that refreshed the row is still set to unchanged. Note that if the primary key is not defined for the DataTable, DataAPter.Fill adds a new line with the possible host key value.
If you want to refresh the table with the current value from the server, keep any changes made to the rows in the table, you must first use the DataAdapter.Fill to populate the table and populate a new DataTable, then use the preserveChanges value True to put DataTableMerge to DataSet in.
4. Pipe
ADO.NET can explicitly control what data returned from the data source and how much data is available locally in the DataSet. Subject to the results of the query does not have a unique answer, but there are some techniques that should be considered when designing applications.
Avoid using DataAdapter.Fill overload with StartRecord and maxRecords values. When filling DataSet in this way, only the maxRecords parameter (starting from the StartRecord parameter ID) is used to populate the DataSet, but always returns a complete query. This will cause unnecessary processing to read "unwanted" records; and to return additional records, unnecessary server resources will be exhausted.
It is used to create a SQL statement, combining the WHERE clause and the Order By clause and TOP predicate. This technique depends on the existence of a way to identify each row. When you browse the next page record, modify the WHERE clause to include all unique identifiers greater than the record of the last unique identifier of the current page. When you browse the previous page record, modify the WHERE clause to return all unique identifiers less than the record of the first unique identifier of the current page. Both queries returned to the recorded TOP page. When browsing the previous page, you need to sort in sequences. This will effectively return the last page of the query.
Another technology that only returns only a page record is to create a SQL statement, combine the use of TOP predicates and embedded SELECT statements. This technology does not rely on the existence of a way to uniquely identify each row. The first step in using this technology is to multiply the number of pages of the page with the page size. The result is then passed to the TOP predicate of SQL Query, which is arranged in ascending order. Then embed this query into another query, and the latter selects the TOP page size from the descended embedded query results. In essence, returning is the last page of embedded query. For example, to return the third page of the query result (page size 10), you should write the command shown below:
SELECT TOP 10 * from
(SELECT TOP 30 * from customs) AS TABLE1
ORDER BY ID DESC
Note that the result page returned from the query is displayed as sequence. You should be reordered if needed.
If the data is not changed, a record cache can be maintained locally in the DataSet to improve performance. For example, you can store 10 pages in the local DataSet, and you only query new data from the data source only when the user browsing exceeds the cache first page and the last page.
