Use the sqlbulkcopy class to load other source data to the SQL table

Use the sqlbulkcopy class to load other source data to the SQL table

Msdn Abstract: microsoftsql 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 managed code solutions that provide similar functions. 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.

Protected void btnbulkcopy_click (Object sender, eventargs E)
{
String shajarconstring = configurationmanager. connectionstrings ["dsn_shajar"]. connectionstring;
String northwindconstring = configurationmanager. connectionstrings ["dsn_northwind"]. connectionstring;
Sqlconnection shajarcon = new sqlconnection (shajarconstring );
Sqlconnection northwindcon = new sqlconnection (northwindconstring );
String sql1 = "select ID, first_name, last_name," +
"'Shajar' as source from mailinglist_temp ";
Sqlcommand shajarcom = new sqlcommand (sql1, shajarcon );
Sqldatareader shajarreader;
Shajarcon. open ();

Sqlbulkcopy northwindbulkop = new sqlbulkcopy (northwindconstring, sqlbulkcopyoptions. useinternaltransaction );

Northwindbulkop. destinationtablename = "employees ";

Northwindbulkop. columnmappings. Add ("ID", "employeeid ");
Northwindbulkop. columnmappings. Add ("first_name", "firstname ");
Northwindbulkop. columnmappings. Add ("last_name", "lastname ");

Sqlbulkcopycolumnmapping jobtitlecolmap = new sqlbulkcopycolumnmapping ("source1", "title ");
Northwindbulkop. columnmappings. Add (jobtitlecolmap );
Northwindbulkop. bulkcopytimeout = 500000000;

Northwindbulkop. sqlrowscopied + =
New sqlrowscopiedeventhandler (onrowscopied );

Northwindbulkop. policyafter = 1000;

Shajarreader = shajarcom. executereader ();

Try
{
Northwindbulkop. writetoserver (shajarreader );
}
Catch (exception ex)
{
Lblresult. Text = ex. message;
}
Finally
{
Shajarreader. Close ();
}
}

Private void onrowscopied (Object sender, sqlrowscopiedeventargs ARGs)
{
Lblcounter. Text + = args. rowscopied. tostring () + "rows are copied <br> ";
}

 

View the code in professional ASP. NET 2.0

First define a sqldatareader to read data from the source data

Then define a sqlbulkcopy instance

The constructor parameter is

Connectionstring defines the connection string to be opened and used by the sqlbulkcopy instance.

The combination of values in the copyoptions sqlbulkcopyoptions enumeration. This enumeration determines which data source rows are copied to the target table.

The destinationtablename attribute defines the target name of the table to be copied.

Columnmappings returns a set of sqlbulkcopycolumnmapping items,

The sqlbulkcopycolumnmappingcollection. Add method parameter is the name of the data source class and the name of the target column in the target table.

You can also define a sqlbulkcopycolumnmapping, and then use the add method to add

Yyafter specifies the number of rows to be processed before a notification event is generated.

Northwindbulkop. sqlrowscopied + =
New sqlrowscopiedeventhandler (onrowscopied );

Specifies the event that occurs when the row processing is completed. For example, if the row 1000 is copied each time, the system prompts that the row 1000 has been copied.

Finally, the writetoserver method copies the sqldatareader to the specified table.