Practical experience in ADO. NET

Practical experience in ADO. NET

As Microsoft's latest 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, two ADO. NET experts explain a lot of practical experience to readers without reservation.

Introduction

This article provides you. NET application to achieve and obtain the best performance, scalability and function of the best solution, but 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.

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 use the current value from the server to refresh the table and retain 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.

Search data in DataSet

When querying rows matching specific conditions in DataSet, you can use index-based search to improve search performance. When the PrimaryKey value is assigned to the able, an index is created. When DataView is created for the DataTable, an index is also created. The following are some indexing-based search techniques.

1) If you want to query the columns that make up the PrimaryKey of the DataTable, use able. Rows. Find instead of DataTable. Select.

2) For queries involving non-primary key columns, you can use DataView for multiple queries of data to improve performance. When the sorting order is applied to DataView, an index is created for search. DataView exposes the Find and FindRows methods to query data in the basic DataTable.

3) If you do not need a sort view for a table, you can still create a DataView for the DataTable to use index-based search. Note that this will benefit only when multiple query operations are performed on the data. If you only execute a single query, the processing required to create an index will reduce the performance improvement caused by using the index.

DataView Construction

If DataView is created and the attributes of Sort, RowFilter, or RowStateFilter are modified, DataView creates an index for the data in the basic DataTable. When creating a DataView object, you must use the DataView constructor. It uses the Sort, RowFilter, and RowStateFilter values as the constructor parameters (together with the basic able ). The result is an index created once. Create an "empty" DataView and then set the Sort, RowFilter, or RowStateFilter attributes. This will cause the index to be created at least twice.

ADO. NET can explicitly control what data is returned from the data source and how much data is locally cached in the DataSet. There is no unique answer to the paging of the query results, but below are some tips to consider when designing an application.

1) avoid using the DataAdapter. Fill overload with startRecord and maxRecords values. When DataSet is filled in this way, only the number of records specified by the maxRecords parameter (starting from the record identified by the startRecord parameter) is used to fill the DataSet, but the complete query is always returned in any case. This will lead to unnecessary processing, which is used to read "unwanted" records. In addition, in order to return additional records, unnecessary server resources will be exhausted.

2) the technology used to return only one page of records at a time is to create an SQL statement and combine the WHERE clause, ORDER BY clause, and TOP predicates. This technique depends on a method that uniquely identifies each row. When browsing the next page, modify the WHERE clause to include all records whose unique identifiers are greater than the last unique identifiers of the current page. When browsing a previous page record, modify the WHERE clause to make it return records with all unique identifiers smaller than the first unique identifiers on the current page. Both queries only return the TOP page of the record. When you browse a previous page, you need to sort the results in descending order. This will effectively return the last page of the query (if needed, you may have to re-sort the results before it is displayed ).

3) Another technique that returns only one page of records at a time is to create an SQL statement that combines the use of TOP predicates with embedded SELECT statements. This technique does not rely on a method that uniquely identifies each row. The first step to using this technology is to multiply the number of pages required by the page size. The results are then passed to the TOP predicates of the SQL Query, which are sorted in ascending order. Then, this query is embedded into another query. The latter selects the TOP page size from the embedded query results in descending order. Essentially, the last page of the embedded query is returned. For example, to return the third page of the query result (the page size is 10), you should write the following command:

Select top 10 * FROM

(Select top 30 * FROM MERs order by Id ASC) AS Table1

Order by Id DESC

Note: The returned results page is displayed in descending order. Sort again if needed.

1) if the data does not change frequently, You can locally maintain a record cache in DataSet to improve performance. For example, you can store 10 pages of useful data in the local DataSet, and query new data from the data source only when the user browses the first and last pages beyond the cache.

Fill DataSet with schema

When DataSet is filled with data, the DataAdapter. Fill method uses the existing architecture of DataSet and fills it with the data returned from SelectCommand. If no table name in DataSet matches the name of the table to be filled, the Fill method creates a table. By default, Fill only defines the column and column type.

By setting the MissingSchemaAction attribute of DataAdapter, You can override the default behavior of Fill. For example, to create a table schema for Fill, it also includes primary key information, unique constraints, column attributes, whether to allow null, maximum column length, read-only columns, and automatically incremental columns, the DataAdapter. missingSchemaAction is specified as MissingSchemaAction. addWithKey. Alternatively, before calling DataAdapter. Fill, you can call DataAdapter. FillSchema to ensure that the schema is in place when the DataSet is filled.

The call to FillSchema generates an additional route to the server for retrieving additional architecture information. To achieve optimal performance, you must specify the DataSet architecture before calling Fill, or set the MissingSchemaAction of DataAdapter.

Best practices for using CommandBuilder

Assuming that SelectCommand executes a single SELECT table, CommandBuilder automatically generates the InsertCommand, UpdateCommand, and DeleteCommand attributes of DataAdapter Based on the SelectCommand attribute of DataAdapter. The following are some tips for using CommandBuilder for optimal performance.

1) The use of CommandBuilder should be limited to design or ad hoc solutions. The processing required to generate the DataAdapter command attribute will affect the performance. If you know the content of the INSERT, UPDATE, and DELETE statements in advance, you can set them explicitly. A good design technique is to create a stored procedure for the INSERT/UPDATE/DELETE command and explicitly configure the properties of the DataAdapter command to use them.

2) CommandBuilder uses the SelectCommand attribute of DataAdapter to determine the values of other command attributes. If the SelectCommand of DataAdapter has been changed, make sure to call RefreshSchema to update the command attributes.

3) if the DataAdapter command property is empty (the command property is empty by default), CommandBuilder generates only one command for it. CommandBuilder does not overwrite the command attributes explicitly. If you want CommandBuilder to generate a command for the previously set command attribute, set the command attribute to null.

Batch Processing SQL statement

Many databases support combining or batch processing multiple commands into one single command for execution. For example, SQL Server allows you to use semicolons (;) to separate commands. Combining Multiple commands into a single command can reduce the number of server trips and improve application performance. For example, you can store all the predefined deletions locally in the application, and then issue a batch processing command call to delete them from the data source.

Although this can indeed improve the performance, managing data updates in DataSet may increase the complexity of applications. To keep it simple, you may need to create a DataAdapter for each able in DataSet.

Fill DataSet with multiple tables

If you use a batch processing SQL statement to retrieve multiple tables and Fill in DataSet, the first table is named by the table name specified to the Fill method. The following table is named by adding a number starting from 1 and increasing to 1 to the table name specified for the Fill method. For example, if you run the following code:

'Visual Basic

Dim da As SqlDataAdapter = New SqlDataAdapter ("SELECT * FROM MERs MERS; SELECT * FROM Orders;", myConnection)

Dim ds As DataSet = New DataSet ()

Da. Fill (ds, "MERs ")

// C #

SqlDataAdapter da = new SqlDataAdapter ("SELECT * FROM MERs MERS; SELECT * FROM Orders;", myConnection );

DataSet ds = new DataSet ();

Da. Fill (ds, "MERs ");

Data from the MERs table is placed in a able named "Customers. Data from the Orders table is placed in the able mers1 DataTable.

After filling in the DataSet, you can easily change the TableName attribute of the Customers1 table to "Orders ". However, subsequent filling will cause the "MERs" table to be refilled, while the "Orders" table will be ignored and another "Customers1" table will be created. To remedy this situation, create a ableablemapping, map "Customers1" to "Orders", and create other table mappings for other tables. For example:

'Visual Basic

Dim da As SqlDataAdapter = New SqlDataAdapter ("SELECT * FROM MERs MERS; SELECT * FROM Orders;", myConnection)

Da. TableMappings. Add ("Customers1", "Orders ")

Dim ds As DataSet = New DataSet ()

Da. Fill (ds, "MERs ")

// C #

SqlDataAdapter da = new SqlDataAdapter ("SELECT * FROM MERs MERS; SELECT * FROM Orders;", myConnection );

Da. TableMappings. Add ("Customers1", "Orders ");

DataSet ds = new DataSet ();

Da. Fill (ds, "MERs ");

Use DataReader

The following are some tips for using DataReader to get the best performance, and I also answered some frequently asked questions about using DataReader.

1) DataReader must be disabled before accessing any output parameters of related commands.

2) DataReader is always disabled after Data Reading is completed. If you only use Connection to return DataReader, Immediately disable DataReader.

Another way to explicitly close the Connection is to pass CommandBehavior. CloseConnection to the ExecuteReader method to ensure that the related Connection is closed when DataReader is disabled. This is especially useful if you return a DataReader from a method and cannot control the closure of DataReader or related connections.

1) DataReader cannot be remotely accessed between layers. DataReader is designed for connected data access.

2) When accessing column data, use a typed accesser, such as GetString and GetInt32. This eliminates the need to forcibly convert the Object returned by GetValue to a specific type.

3) Only one DataReader can be opened for a single connection at a time. In ADO, if you open a single connection and request two record sets that use a forward-only and read-only cursor, ADO will implicitly open the second, unpooled connection to the data storage area during the cursor lifetime, and then implicitly close the connection. For ADO. NET, there are very few "secret" actions. To enable two DataReaders in the same data storage area at the same time, you must create two connections explicitly, one for each DataReader. This is a method that ADO. NET provides more control for the use of pooled connections.

4) by default, DataReader loads the entire row into the memory each time it reads data. This allows Random Access to columns in the current row. If such random access is not required, CommandBehavior. Sequentialaccess is passed to ExecuteReader for better performance. This changes the default behavior of DataReader to load data to the memory only during the request. Note that CommandBehavior. SequentialAccess requires sequential access to the returned columns. That is to say, once you read the returned column, you cannot read its value.

5) If you have read data from DataReader but there are still a large number of pending unread results, you can call the Cancel Command before calling Close of DataReader. Calling Close of DataReader will result in retrieving suspended results and clearing the stream before closing the cursor. The Cancel that calls the Command will discard the results on the server, so that the DataReader does not need to read these results when it is disabled. If you want to return output parameters from the Command, you also need to call Cancel to discard them. To read any output parameter, do not call the Cancel Command. You only need to call the Close of DataReader.

Binary Large Object (BLOB)

When you use DataReader to retrieve Binary large objects (BLOB), you should pass CommandBehavior. SequentialAccess to the ExecuteReader method call. The default behavior of DataReader is to load the entire row to the memory every Read, and because the BLOB value may be very large, a large amount of memory may be used up due to a single BLOB. SequentialAccess sets the DataReader behavior to load only the requested data. You can also use GetBytes or GetChars to control how much data is loaded each time.

Remember, when SequentialAccess is used, different fields returned by DataReader cannot be accessed in sequence. That is to say, if the query returns three columns, where the third column is BLOB and you want to access the data in the first two columns, you must first access the value of the first column before accessing BLOB data, then access the value of the second column. This is because the data is returned sequentially, and the data is no longer available once the DataReader reads the data.

Use commands

ADO. NET provides different methods for command execution and different options for optimizing command execution. The following are some tips about selecting the best command for execution and how to improve the performance of command execution.

Best practices for using OleDbCommand

Command Execution between different. NET Framework data providers is standardized as much as possible. However, there are still differences between data providers. The following are some tips for fine-tuning the command execution of the. NET Framework data provider for ole db.

1) Use CommandType. Text to CALL the stored procedure according to odbc call syntax. Using CommandType. StoredProcedure only secretly generates odbc call syntax.

2) Be sure to set the OleDbParameter type, size (if applicable), and precision and range (if the parameter type is numeric or decimal ). NOTE: If parameter information is not explicitly provided, OleDbCommand re-creates the ole db parameter accesser for each command.

Best practices for using SqlCommand

A prompt for using SqlCommand to execute a stored procedure: If a stored procedure is called, specify the CommandType attribute of SqlCommand as the CommandType of StoredProcedure. By explicitly marking this command as a stored procedure, you do not need to analyze the command before execution.

Use the Prepare method

The Command. Prepare method can improve the performance of parameterized commands that repeatedly act on the data source. Prepare indicates that the data source calls the specified optimization command for multiple times. To effectively use Prepare, you must thoroughly understand how the data source responds to the Prepare call. For some data sources (such as SQL Server 2000), commands are implicitly optimized without calling Prepare. Prepare is valid for other data sources (such as SQL Server 7.0.

Explicitly specify the architecture and metadata

As long as the user does not specify metadata information, many objects in ADO. NET will infer metadata information. The following are some examples:

1) DataAdapter. Fill method. If there are no tables and columns in DataSet, the DataAdapter. Fill method creates tables and columns in DataSet.

2) CommandBuilder, which generates the DataAdapter command attribute for the SELECT command of a single table.

3) CommandBuilder. DeriveParameters, which fills the Parameters set of the Command object.

However, each time these features are used, performance loss will occur. We recommend that you use these features primarily in design and ad hoc applications. Explicitly specify the architecture and metadata when possible. This includes defining tables and columns in DataSet, defining the Command attribute of DataAdapter, and defining Parameter information for Command.

ExecuteScalar and ExecuteNonQuery

To return a single value like Count (*), Sum (Price), or Avg (Quantity), use Command. ExecuteScalar. ExecuteScalar returns the value of the first column of the First row and returns the result set as a scalar value. ExecuteScalar not only simplifies the Code but also improves the performance. If DataReader is used, it takes two steps (that is, ExecuteReader + value ).

When you use an SQL statement that does not return rows, such as modifying data (such as INSERT, UPDATE, or DELETE) or returning only output parameters or return values, use ExecuteNonQuery. This avoids any unnecessary processing for creating an empty DataReader.

Test Null

If columns in a table (in a database) can be empty, you cannot test whether the parameter value is "equal to" null. On the contrary, you need to write a WHERE clause to test whether the columns and parameters are empty. The following SQL statement returns some rows whose LastName column is equal to the value assigned to the @ LastName parameter, or the LastName column and @ LastName parameters are empty.

SELECT * FROM MERs

WHERE (LastName = @ LastName) OR (LastName is null and @ LastName is null ))

Pass Null as the parameter value

In database commands, when a null value is sent as a parameter value, null cannot be used (Nothing in Visual Basic. NET ). DBNull. Value is required. For example:

'Visual Basic

Dim param As SqlParameter = New SqlParameter ("@ Name", SqlDbType. NVarChar, 20)

Param. Value = DBNull. Value

// C #

SqlParameter param = new SqlParameter ("@ Name", SqlDbType. NVarChar, 20 );

Param. Value = DBNull. Value;

Execute transactions

The transaction model of ADO. NET has been changed. In ADO, when StartTransaction is called, any update operations after the call are considered as part of the transaction. However, in ADO. NET, when Connection. BeginTransaction is called, a Transaction object is returned, which must be associated with the Transaction attribute of the Command. This design can execute multiple root transactions on a single connection. If the Command. Transaction attribute is not set to a Transaction initiated for the related Connection, the Command fails and an exception is thrown.

The forthcoming. NET framework allows you to manually register in existing distributed transactions. This is ideal for the object pool solution. In this solution, a pool object opens a connection, but this object is involved in multiple independent transactions .. This feature is not available in NET Framework 1.0 Release.

Use connection

High-performance applications connect to data sources in use in the shortest time, and use performance enhancement technologies such as connection pools. The following topic provides tips to help you achieve better performance when connecting to a data source using ADO. NET.

Connection Pool

SQL Server, OLE DB, And. NET Framework data providers for ODBC implicitly buffer connections. You can control the action of the Connection Pool by specifying different attribute values in the connection string.

Use DataAdapter to optimize connections

When the connection between the Fill and Update Methods of DataAdapter is closed, the connection specified for the relevant command attribute is automatically opened. If the Fill or Update method opens the connection, Fill or Update will close the connection when the operation is complete. To achieve optimal performance, you only need to enable the connection to the database as needed. At the same time, reduce the number of times the multi-operation connection is enabled and disabled.

If you only call a single Fill or Update method, we recommend that you allow the Fill or Update method to open or close the connection implicitly. If there are many calls to Fill and Update, it is recommended to explicitly open the connection, call Fill and Update, and then explicitly close the connection.

In addition, when a transaction is executed, the connection is opened explicitly before the transaction starts, and the connection is closed after the transaction is committed. For example:

'Visual Basic

Public Sub RunSqlTransaction (da As SqlDataAdapter, myConnection As SqlConnection, ds As DataSet)

MyConnection. Open ()

Dim myTrans As SqlTransaction = myConnection. BeginTransaction ()

MyCommand. Transaction = myTrans

Try

Da. Update (ds)

MyTrans. Commit ()

Console. WriteLine ("Update successful .")

Catch e As Exception

Try

MyTrans. Rollback ()

Catch ex As SqlException

If Not myTrans. Connection Is Nothing Then

Console. WriteLine ("An exception of type" & ex. GetType (). ToString () & "was encountered while attempting to roll back the transaction .")

End If

End Try

Console. WriteLine ("An exception of type" & e. GetType (). ToString () & "was encountered .")

Console. WriteLine ("Update failed .")

End Try

MyConnection. Close ()

End Sub

// C #

Public void RunSqlTransaction (SqlDataAdapter da, SqlConnection myConnection, DataSet ds)

{

MyConnection. Open ();

SqlTransaction myTrans = myConnection. BeginTransaction ();

MyCommand. Transaction = myTrans;

Try

{

Da. Update (ds );

MyCommand. Transaction. Commit ();

Console. WriteLine ("Update successful .");

}

Catch (Exception e)

{

Try

{

MyTrans. Rollback ();

}

Catch (SqlException ex)

{

If (myTrans. Connection! = Null)

{

Console. WriteLine ("An exception of type" + ex. GetType () + "was encountered while attempting to roll back the transaction .");

}

}

Console. WriteLine (e. ToString ());

Console. WriteLine ("Update failed .");

}

MyConnection. Close ();

}

Always close Connection and DataReader

After you use Connection or DataReader objects, they are always explicitly disabled. Although garbage collection eventually clears objects and releases connections and other managed resources, garbage collection is only executed as needed. Therefore, it is still your responsibility to ensure that any valuable resources are explicitly released. In addition, Connections without explicit shutdown may not be returned to the pool. For example, a connection that is out of the valid range but has not been explicitly closed will be returned to the connection pool only when the connection pool size reaches the maximum and the connection is still valid.

Note that do not call Close or Dispose for Connection, DataReader, or any other managed object in the Finalize method of the class. At the end of the process, only the unmanaged resources directly owned by the class will be released. If the class does not have any unmanaged resources, do not include the Finalize method in the class definition.

Use the "Using" statement in C #

For C # programmers, a convenient way to ensure that the Connection and DataReader objects are always closed is to use the using statement. When the using statement leaves its own scope, it will automatically call the Dispose of the "used" object. For example:

// C #

String connString = "Data Source = localhost; Integrated Security = SSPI; Initial Catalog = Northwind ;";

Using (SqlConnection conn = new SqlConnection (connString ))

{

SqlCommand cmd = conn. CreateCommand ();

Cmd. CommandText = "SELECT CustomerId, CompanyName FROM Customers ";

Conn. Open ();

Using (SqlDataReader dr = cmd. ExecuteReader ())

{

While (dr. Read ())

Console. WriteLine ("{0} {1}", dr. GetString (0), dr. GetString (1 ));

}

}

The Using statement cannot be used in Microsoft Visual Basic. NET.

Avoid accessing the OleDbConnection. State attribute

If the connection is enabled, the OleDbConnection. State attribute executes the local ole db call IDBProperties. GetProperties on the performanceinfo attribute set of the DBPROP_CONNECTIONSTATUS attribute, which may cause a round-trip to the data source. That is to say, it may be costly to check the State attribute. Therefore, check the State attribute only when necessary. If you need to check this attribute frequently, listening to the StateChange event of OleDbConnection may improve the application performance.

Integration with XML

ADO. NET provides extensive XML integration in DataSet and discloses some XML functions provided by SQL Server 2000 and later. You can also use SQLXML 3.0 to extensively access the XML Functions of SQL Server 2000 and later versions. The following are tips and information for using XML and ADO. NET.

DataSet and XML

DataSet is tightly integrated with XML and provides the following functions:

1) load the DataSet architecture or relational structure from the XSD architecture.

2) Load DataSet content from XML.

3) If no architecture is provided, the DataSet architecture can be inferred from the content of the XML document.

4) write the DataSet architecture into the XSD architecture.

5) write the DataSet content as XML.

6) synchronously access the relational representation of data using DataSet and the hierarchical representation of data using XmlDataDocument.

Note that XML functions (such as XPath query and XSLT conversion) can be applied to data in DataSet through this synchronization, you can also provide a relational view for all or a subset of the data in the XML document while retaining the original XML fidelity.

Architecture Inference

When loading a DataSet from an XML file, you can load the DataSet schema from the XSD Schema, or pre-define tables and columns before loading data. If there is no available XSD architecture and you do not know which tables and columns are defined for the content of the XML file, you can deduce the architecture based on the XML document structure.

Architecture inference is useful as a migration tool, but it should be limited only to the design stage applications because of the following restrictions on inference processing.

1) the deduction of the architecture will introduce additional processing that affects application performance.

2) All types of inferred columns are strings.

3) inference processing is not deterministic. That is to say, it is based on the XML file content, rather than the predefined architecture. Therefore, for two XML files with the same predefined architecture, two completely different inference architectures are obtained due to their different content.

SQL Server used for XML query

If the query result is returned from SQL Server 2000 for xml, you can use SqlCommand. ExecuteXmlReader to directly create an XmlReader FOR the. NET Framework data provider for SQL Server.

SQLXML hosting class

There are some classes in the. NET Framework to publish the XML functions for SQL Server 2000. These classes can be found in the Microsoft. Data. SqlXml namespace. They add the ability to execute XPath queries and XML template files and apply XSLT transformations to Data.

The SQLXML hosting class is included in the XML (SQLXML 2000) Release for Microsoft SQL Server 2.0. You can link XML for Microsoft SQL Server 2000 Web Release 2 (SQLXML 2.0)

More useful techniques

Below are some general skills for compiling ADO. NET code.

Avoid conflicts with auto increment values

Just like most data sources, DataSet allows you to identify columns that automatically increment their values when new rows are added. When auto increment columns are used in DataSet, if the auto increment columns come from the data source, you can avoid conflicts between the rows added to DataSet and the rows added to the data source.

For example, if you consider a table, its primary key column CustomerID is automatically incremental. Add two new customer information rows to the table, and receive the automatically incremental CustomerID values 1 and 2. Then, only the second customer row is passed to the DataAdapter method Update. The newly added row receives an automatic incremental CustomerID value 1 from the data source, which does not match the value 2 in the DataSet. When DataAdapter fills the second row in the table with the return value, there is a constraint conflict, because the first customer row already uses the CustomerID value 1.

To avoid this problem, we recommend that you create columns in the DataSet as AutoIncrementStep values equal to-1 and AutoIncrementSeed values equal to 0 when using columns that are automatically incremental on the data source and columns that are automatically incremental on the DataSet, in addition, make sure that the automatic incremental id value generated by the data source starts from 1 and increments with a positive value. Therefore, DataSet generates a negative number for the auto-increment value, which does not conflict with the positive auto-increment value generated by the data source. Another option is to use a column of the GUID type, rather than an automatically incremental column. The algorithm that generates the GUID value should never make the GUID value generated in the data source the same as the GUID value generated in the DataSet.

If the auto-increment column is only used as a unique value and does not make any sense, use GUID instead of the auto-increment column. They are unique and avoid the extra work required to use auto increment columns.

Check for open concurrency conflicts

According to the design, because DataSet is disconnected from the data source, when multiple clients update data on the data source according to the open concurrency model, ensure that the application does not conflict.

There are several techniques for Testing Open concurrency conflicts. A technique involves adding a timestamp column to a table. Another technique is to verify that the original values of all columns in a row still match the values found in the database when the WHERE clause is used in the SQL statement for testing.

Multi-Thread Programming

ADO. NET optimizes performance, throughput, and scalability. Therefore, the ADO. NET object does not lock resources and must be used only in a single thread. One exception is DataSet, which is thread-safe for multiple readers. However, you need to lock the DataSet during writing.

Use COM Interop to access ADO only when necessary

ADO. NET is designed to be the best solution for many applications. However, some applications only need to use ADO objects, for example, ADO multi-dimensional (ADOMD ). In these cases, the application can use COM Interop to access ADO. Note that using COM Interop to access data with ADO results in lower performance. Before designing an application, first determine whether ADO. NET meets the design requirements before using COM Interop to access ADO.