Improve data interoperability using ADO. net2.0 dataset Data Tables

 

To asynchronously execute the command, another setting that must be set is to add the async = true attribute to the database connection string. If this attribute is not added to the connection string and the asynchronous execution method is called through the command object instance, an exception is thrown ). If commands are executed in synchronous mode when they are executed through a connection, the default async = false method is used to save resources. If some commands require synchronous execution and others require asynchronous execution, you can consider using different connections.

In introducing sample applicationsProgramFirst, let's talk a little bit.. NET Framework provides a public asynchronous running application design pattern, not just ADO. net2.0 can also be used to write other time-consuming programs.

. Net Framework has built-in functions that allow applications to run asynchronously, so that when you write an application, the program will not stop responding because of some time-consuming operations, the stagnant operation interface makes the user feel like a dead end. This requirement is generally handled in multiple threads. However, if you are not familiar with the running of a thread or want to use the advantages of a thread pool, you can perform time-consuming operations, use.. NET Framework.

Generally, file I/O, network access, Web Services Access, and DB access discussed in this section are time-consuming ,. net Framework provides ~ /End ~ Non-synchronous execution methods at the beginning, and these methods all appear in pairs. Of course, it may also be that the business logic of the method you write is very complicated. As a result, after calling this method, you need to wait for a while to complete it. the delegate class provided by netframework to create an asynchronous operation.

However, in our applications, we recommend that you use delegation when we do not need to know the information returned by the DB server, especially in Web development.

When the page thread starts asynchronous database access, the asynchronous thread accessing the database cannot be opened after the page service is executed. This is what we don't want to see, but using delegation can avoid this problem (the same is true for WebService asynchronous applications ).

4. Use sqlbulkcopy to load data in batches (sqlclient only)

In the past, when accessing SQL Server 2000, if a large number of data records need to be added to the database, such as downloading a large number of data records from the host system, NCR teradata, Oracle, and other database systems, we want to add them quickly to SQL Server 2000, either by calling the BULK INSERT syntax for the T-SQL, by executing the select into syntax through the linked server, or by executing the bcp.exe tool, you can also use the bulk insert task of DTs or the quick loading setting of the transform data task.

However, to complete batch loading through a self-compiled program, you can only use C/C ++ to call the bulk API of oledb or ODBC and cannot execute it through objects such as ADO. Net or ADO.

ADO. NET 2.0 sqlclient provides a new class called sqlbulkcopy, which allows a large amount of data in the dataset or the datareader to directly read a large number of records through the stream, you can quickly add these records to the data table of the target database. However, we should note that it is not a bcp.exe tool that we generally use. It can read a large amount of data from a separator file and selectively combine it with a format file) load records to the database, or export the data in the database into a file. However, because dataset can integrate XML data, the sqlbulkcopy type can still be used to easily transfer XML file data to the database through dataset.

You can use the sqlbulkcopy class to quickly write large volumes of data. for SQL Server optimization, you can write datarow data, able, and datareader, and map different data column names.

Writetoserver (datatable) write data table
Writetoserver (datarow []) Batch write data rows
Writetoserver (datatable, datarowstate) writes data to the database table by row
Writetoserver (idatareader) writes a datareader object

The following is an example:

Using (sqlconnection sqlcon = New sqlconnection ("Data Source = 192.168.80.242; user id = OA; Password = oapassword; Initial catalog = test ")) { Sqlcon. open (); Using (sqlbulkcopy BCP = new sqlbulkcopy (sqlcon )) { BCP. bulkcopytimeout = 3000; BCP. destinationtablename = "DBO. test01 "; BCP. columnmappings. Add ("ID", "ID "); BCP. columnmappings. Add ("name1", "name1 "); BCP. columnmappings. Add ("name2", "name2 "); BCP. columnmappings. Add ("name3", "name3 "); // Map to different names BCP. columnmappings. Add ("changedname4", "name4 "); BCP. writetoserver (DT ); Sqlcon. Close (); } }

Note the following when using sqlbulkcopy:

1. confirm that a large-capacity update is required to perform this operation. (try to use other channels for dozens of rows of data ).

2. Confirm data consistency and check mechanism to avoid primary key conflicts and data inconsistency.

3. sqlbulkcopy may change the metadata of the target table (for example, when the constraint check is disabled ). In this case, concurrent Snapshot isolation transactions that access large-capacity inserted tables will fail.

4. sqlbulkcopy will update the lock for large capacity under the database. Pay attention to the concurrency to avoid timeout for other connections due to long wait.

V. dataset Performance Improvement 

For developers, the most exciting change in ADO. NET 2.0 is that the. NET development team has finally achieved what they have promised for years: it has indeed improved the performance of dataset.

Due to the unflattering performance of dataset 1.1, many aspects of dataset are covered by its performance problems.

Now, after significantly improving the performance of dataset. Dataset has finally become perfect.

There are many improvements, including the following:

1. Index engine greatly improved

After a considerable number of tests on the dataset of ADO. NET 2.0, Microsoft finally announced that the data access capability of dataset2.0 has been greatly improved, with a wide number increasing by 44 times !!! Unlike the steep curve of sorting in 1.1, sorting in 2.0 is as linear as much as possible!

I really don't know whether the. NET group was just a bad job, or was it invented with new tricks. Pai_^

2. Binary sequential Dataset

Dataset has a good attribute that supports serialization, but many people have criticized it. Not because these people do not like dataset to support serialization, but the serialized dataset is too fat. A large number of <Xs: element name = "", type... It takes up a lot of space to make people unbearable.

Now we only need to set the remotingformat attribute to serializationformat. Binary (the default value is serializationformat. XML), then the binary data format is used in serialization, so the data is small and therefore more efficient. (The Official point is to scale down to 1/4 of serializationformat. XML ).