Magic sqlbulkcopy high-performance batch import SQL Server

Because of the favorable development environment, I have the opportunity to take a look at the sqlbulkcopy function in ADO. I used to migrate the database from mssql2005 to Oracle. At that time, I used C # To write a migration tool. NET database operations are generally skillful and fail to take full advantage of its best performance and methods. The written tools show a little slow in data import, and then use BCP for data import, it is still very fast to import BCP data. After a few months, I want to optimize it myself. Algorithm And methods to make data migration faster and more convenient, so let's take a look at it today.. net. test environment: 1. Windows Server 2003 + mssql2005 (server) 2. Linux + Oracle 10g (server) 3. My Computer Configuration: pentium D 2.8 GB + 2 GB memory 4. My operating system: Windows XP + vs2005 +. net framework2.0 5. Export 0.82 million of data from the Oracle database to mssql2005 Program Code : View plaincopy to clipboardprint?
Using system;
Using system. Data. sqlclient;
Using system. Data. oracleclient;

Class Program

{
Static void main ()

{
String connectionstring = getoracleconnectionstring ();

Try

{

Using (oracleconnection sourceconnection = new oracleconnection (connectionstring ))

{

Sourceconnection. open ();

Oraclecommand commandrowcount = new oraclecommand ("select count (*) from table", sourceconnection );

Long countstart = system. Convert. toint32 (

Commandrowcount. executescalar ());

Console. writeline ("Starting row COUNT = {0}", countstart );

Datetime dt1 = datetime. now;

Console. writeline ("start reading data ....");

String sqltxt = "select * from table ";

Oraclecommand commandsourcedata = new oraclecommand (sqltxt, sourceconnection );

Oracledatareader reader =

Commandsourcedata. executereader ();

String desconnectionstring = getmsconnectionstring ();

Console. writeline ("START moveing data ....");

Using (sqlconnection destinationconnection = new sqlconnection (desconnectionstring ))

{

Destinationconnection. open ();

Using (sqlbulkcopy bulkcopy = new sqlbulkcopy (destinationconnection ))

{

Bulkcopy. bulkcopytimeout = 5000000;


Bulkcopy. destinationtablename = "DBO. Table ";

Try

{

Bulkcopy. writetoserver (Reader );

}

Catch (exception ex)

{

Console. writeline (ex. Message );

}

Finally

{

Reader. Close ();

}


}

Datetime dt2 = datetime. now;

Timespan counttime = dt2.subtract (dt1 );

Long countend = system. Convert. toint32 (

Commandrowcount. executescalar ());

Console. writeline ("ending row COUNT = {0}", countend );

Console. writeline ("{0} rows were added.", countend-countstart );

Console. writeline ("time used:" + counttime. minutes. tostring () + "Minute" + counttime. seconds. tostring () + "second" + counttime. milliseconds. tostring () + "millisecond ");

Console. writeline ("press enter to finish .");

Console. Readline ();

}

}

}

Catch (exception ex)

{

Console. Write (ex. tostring ());

}

}

Private Static string getmsconnectionstring ()

{
Return "uid = user name; Password = password; database = database; server = 192.168.x.x // sql2005 ";

}

Private Static string getoracleconnectionstring ()

{

Return "Data Source = database; user id = username; Password = password ";

}

}

Using system;
Using system. Data. sqlclient;
Using system. Data. oracleclient;

Class Program

{
Static void main ()

{
String connectionstring = getoracleconnectionstring ();

Try

{

Using (oracleconnection sourceconnection = new oracleconnection (connectionstring ))

{

Sourceconnection. open ();

Oraclecommand commandrowcount = new oraclecommand ("select count (*) from table", sourceconnection );

Long countstart = system. Convert. toint32 (

Commandrowcount. executescalar ());

Console. writeline ("Starting row COUNT = {0}", countstart );

Datetime dt1 = datetime. now;

Console. writeline ("start reading data ....");

String sqltxt = "select * from table ";

Oraclecommand commandsourcedata = new oraclecommand (sqltxt, sourceconnection );

Oracledatareader reader =

Commandsourcedata. executereader ();

String desconnectionstring = getmsconnectionstring ();

Console. writeline ("START moveing data ....");

Using (sqlconnection destinationconnection = new sqlconnection (desconnectionstring ))

{

Destinationconnection. open ();

Using (sqlbulkcopy bulkcopy = new sqlbulkcopy (destinationconnection ))

{

Bulkcopy. bulkcopytimeout = 5000000;

Bulkcopy. destinationtablename = "DBO. Table ";

Try

{

Bulkcopy. writetoserver (Reader );

}

Catch (exception ex)

{

Console. writeline (ex. Message );

}

Finally

{

Reader. Close ();

}

}

Datetime dt2 = datetime. now;

Timespan counttime = dt2.subtract (dt1 );

Long countend = system. Convert. toint32 (

Commandrowcount. executescalar ());

Console. writeline ("ending row COUNT = {0}", countend );

Console. writeline ("{0} rows were added.", countend-countstart );

Console. writeline ("time used:" + counttime. minutes. tostring () + "Minute" + counttime. seconds. tostring () + "second" + counttime. milliseconds. tostring () + "millisecond ");

Console. writeline ("press enter to finish .");

Console. Readline ();

}

}

}

Catch (exception ex)

{

Console. Write (ex. tostring ());

}

}

Private Static string getmsconnectionstring ()

{
Return "uid = user name; Password = password; database = database; server = 192.168.x.x // sql2005 ";

}

Private Static string getoracleconnectionstring ()

{

Return "Data Source = database; user id = username; Password = password ";

}

} It took only two minutes to export a 0.82 million-piece data table from Oracle to sql2005. At that time, I was surprised that even the BCP import was not so fast, I was amazed. The data source can be arbitrary, but the target database must be MSSQL, which makes me feel a little uncomfortable, because oracle. data. the oracleclient namespace does not have this class. That is to say, you can import data from any source to MSSQL. This is probably a Microsoft strategy. It is more convenient for others to switch from other databases to Ms databases!