This article reprinted: http://zhoufoxcn.blog.51cto.com/792419/166052
Reference http://www.cnblogs.com/scottckt/archive/2011/02/16/1955862.html
Description:
A few days ago, the company required a data import.Program, Requires that Excel data be imported into the database in large batches, with minimal access to the database and high-performance storage of the database. So I searched online and found a better solution, that is, using sqlbulkcopy to process the stored data. Sqlbulkcopy is very efficient in storing large volumes of data. Just like the name of this method, data tables in memory can be directly stored in the database at one time, you do not need to insert data to the database once. In the first experiment, millions of data tables can be fully stored into the database in just a few seconds. The speed is much faster than the traditional insert method.
Msdn Abstract: Microsoft SQL Server provides a popular command prompt utility called bcp to move data from one table to another (the table can be on the same server, it can also be on different servers ). The sqlbulkcopy class allows you to write a host that provides similar functions.CodeSolution. There are other ways to load data to SQL Server tables (such as insert statements), but sqlbulkcopy provides obvious performance advantages.
The sqlbulkcopy class can only be used to write data to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to the able instance or the idatareader instance can be used to read data.
/// <Summary>
///
/// </Summary>
/// <Param name = "connectionstring"> Target connection character </Param>
/// <Param name = "tablename"> Target table </Param>
/// <Param name = "DT"> Source data </Param>
Private Void Sqlbulkcopybydatatable ( String Connectionstring, String Tablename, datatable DT)
{
Using (Sqlconnection conn = New Sqlconnection (connectionstring ))
{
Using (Sqlbulkcopy = New Sqlbulkcopy (connectionstring, sqlbulkcopyoptions. useinternaltransaction ))
{
Try
{
Sqlbulkcopy. destinationtablename = tablename;
For ( Int I = 0 ; I <DT. Columns. Count; I ++)
{
Sqlbulkcopy. columnmappings. Add (Dt. Columns [I]. columnname, DT. Columns [I]. columnname );
}
Sqlbulkcopy. writetoserver (DT );
}
Catch (System. Exception ex)
{
Throw Ex;
}
}
}
}
Sqlbulkcopy uses custom table types as stored procedure parameters to write data in batches.
If you use SQL Server 2008 It provides a new menu variable (Table parameters) that can aggregate the entire table data into a parameter and pass it to the stored procedure or SQL statement. Its performance overhead is to integrate the data aggregation parameters (O (data volume )).
Now, we modify the previous Code to define our table variables in SQL Server. The specific definitions are as follows:
-- ===================================================== ======
-- Author: jkhuang
-- Create Date: 08 / 16 / 2012
-- Description: declares a user table paramter.
-- ===================================================== ======
Create type jk_users_bulk_insert as table (
User_login varchar ( 60 ),
User_pass varchar ( 64 ),
User_nicename varchar ( 50 ),
User_email varchar ( 100 ),
User_url varchar ( 100 ),
User_activation_key varchar ( 60 ),
User_status Int ,
Display_name varchar ( 250 )
)
We have defined a table parameter jk_users_bulk_insert. Then we define a stored procedure to accept the table parameter jk_users_bulk_insert. The specific definition is as follows:
-- ===================================================== ======
-- Author: jkhuang
-- Create Date: 08 / 16 / 2012
-- Description: Creates a stored procedure, receive
-- A jk_users_bulk_insert argument.
-- ===================================================== ======
Create procedure sp_insert_jk_users
@ Userstable jk_users_bulk_insert readonly
As
Insert into jk_users (user_login, user_pass, user_nicename, user_email, user_url,
User_activation_key, user_status, display_name, user_registered)
Select user_login, user_pass, user_nicename, user_email, user_url,
User_activation_key, user_status, display_name, getdate ()
From @ userstable
Next, we call the stored procedure in the client code and pass the table as a parameter to the stored procedure.
VaR Sw = stopwatch. startnew ();
Using ( VaR Conn = New Sqlconnection (configurationmanager. connectionstrings [ " Sqlconn2 " ]. Tostring ()))
{
Conn. open ();
/// /Invokes the stored procedure.
Using ( VaR Cmd = New Sqlcommand ( " Sp_insert_jk_users " , Conn ))
{
Cmd. commandtype = commandtype. storedprocedure;
/// /Adding a "structured" parameter allows you to insert tons of data with low overhead
VaR Param = New Sqlparameter ( " @ Usertable " , Sqldbtype. Structured) {value = DT };
Cmd. Parameters. Add (PARAM );
Cmd. executenonquery ();
}
}
Sw. Stop ();
Now, we re-execute the write operation and find that the write efficiency is the same as that of sqlbulkcopy.
