Shulou(Shulou.com)06/02 Report--

This article will give you a detailed explanation on how to use the ADO.NET database. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

In the past, when accessing SQL Server 2000, if there are a large number of data records that need to be added to the database, such as downloading a large number of data records from the host system or NCR Teradata, Oracle and other database systems, we want to quickly add them to SQL Server 2000. We can choose to call T-SQL 's Bulk Insert syntax, execute SELECT INTO syntax through Linked Server, or execute bcp.exe tools. And the quick load (Use Fast Load) setting through the Bulk Insert Task of DTS or the launch Transform Data Task.

However, if you want to complete the batch loading through the program written by yourself, you can only call the Bulk API of OLEDB or ODBC with C _ Cure +, and you can't execute it through objects such as ADO.NET or ADO. SqlClient of ADO.NET 2.0 provides a new class called SqlBulkCopy, which allows a large amount of data in DataSet or DataReader to read a large number of records directly through Stream, which can quickly add these records to the data table of the destination ADO.NET database. However, it should be noted that it is not the bcp.exe tool program we usually use, which can read a large amount of data from a symbol-delimited file, optionally load records into an ADO.NET database with a format file (Format File), or export the data in the database as a file. However, because DataSet can integrate XML data, it is still possible to use the SqlBulkCopy type and easily transfer a large amount of XML file data to the ADO.NET database through DataSet.

You can use the SqlBulkCopy class to quickly write large quantities of data. For SQL Server optimization, you can write DataRow data, DataTable,DataReader, and map different data column names.

◆ WriteToServer (DataTable) writes to the data table

◆ WriteToServer (DataRow []) batches write data rows

◆ WriteToServer (IDataReader) writes to the DataReader object

Here 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 columns bcp.ColumnMappings.Add ("changedname4", "name4"); bcp.WriteToServer (dt); sqlcon.Close ();}}

However, you should pay attention to the following points when using SqlBulkCopy:

Confirm that mass updates are really needed to perform this operation (dozens of rows of data, please try to use other channels).

(2) confirm data consistency and check mechanism to avoid accidents such as primary key conflict, data inconsistent format and so on.

The 3 SqlBulkCopy operation may result in changes to the target table metadata (for example, when constraint checking is disabled). If this occurs, the concurrent snapshot isolation transaction accessing the bulk insert table will fail.

4 SqlBulkCopy will bulk update locks to the database, please pay attention to concurrency so as not to time out other connections due to a long wait.

This is the end of the article on "how to use ADO.NET database". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.