Performs a bulk copy operation Sqlbulkcopy,sqltransaction in a transaction.

Microsoft SQL Server provides a popular command prompt utility called bcp , which is used to move data from one table to another (the table can be either on the same server or on a different server). The SqlBulkCopy class allows you to write managed code solutions that provide similar functionality. There are other ways to load data into a SQL Server table, such as an INSERT statement, but in contrast SqlBulkCopy provides significant performance benefits.

Use the SqlBulkCopy class to write data to a SQL Server table only. However, the data source is not limited to SQL Server; You can use any data source, as long as the data can be loaded into a DataTable instance or you can use a IDataReader instance to read the data.

1          Public voidSqlBulkCopy ()2         {3SqlConnectionStringBuilder SQLSB =NewSqlConnectionStringBuilder ();4SQLSB. ConnectionString = configurationmanager.connectionstrings["ConnectionString"]. ConnectionString;5 6             using(SqlConnection conn=NewSqlConnection (sqlsb. ConnectionString))7             {8                 using(SqlCommand cmd =NewSqlCommand ("Select Corpid,corpno, Corpname, Corpphone, registeredaddress, Registertime, Certifygrade, Certifyno, CorporateName , Corporatephone, Licensseno, Regismoney, response, Responsephone, post, email, zip from Corp", conn))9                 {Ten Conn. Open (); OneSqlDataReader sqlreader= cmd. ExecuteReader ();//Get Source data A                    stringConnStr = configurationmanager.connectionstrings["Connectionstringhouseonline"]. ConnectionString; -                    using(SqlConnection Onehouse =NewSqlConnection (connstr)) -                    { the Onehouse. Open (); -                        using(SqlTransaction trans=Onehouse. BeginTransaction ()) -                        { -                           +                            using(SqlBulkCopy bulk=NewSqlBulkCopy (Onehouse,sqlbulkcopyoptions.keepidentity,trans)) -                            { +Bulk. BatchSize =Ten;//set the number of rows executed per batch ABulk. Bulkcopytimeout =500000000;//number of seconds to allow a program to operate before the operation times out atBulk. Sqlrowscopied + =NewSqlrowscopiedeventhandler (bulk_sqlrowscopied);//defines an event that occurs after the number of rows specified by the Notifyafter property is completed -Bulk. Notifyafter =Ten;//define the number of rows to be processed before the departure notification event -Bulk. DestinationTableName ="Corp";//copy data to target object -  -Bulk. Columnmappings.clear ();//clear the collection . -  in                                //to set the field correspondence between the source object and the target object -Sqlbulkcopycolumnmapping column17 =NewSqlbulkcopycolumnmapping ("Corpid","Corpid"); to Bulk. Columnmappings.add (column17); +sqlbulkcopycolumnmapping Column1 =NewSqlbulkcopycolumnmapping ("Corpno","Corpno"); - Bulk. Columnmappings.add (column1); theSqlbulkcopycolumnmapping Column2 =NewSqlbulkcopycolumnmapping ("Corpname","Corpname"); * Bulk. Columnmappings.add (column2); $Sqlbulkcopycolumnmapping Column3 =NewSqlbulkcopycolumnmapping ("Corpphone","Corpphone");Panax Notoginseng Bulk. Columnmappings.add (COLUMN3); -Sqlbulkcopycolumnmapping COLUMN4 =NewSqlbulkcopycolumnmapping ("registeredaddress","registeredaddress"); the Bulk. Columnmappings.add (COLUMN4); +Sqlbulkcopycolumnmapping Column5 =NewSqlbulkcopycolumnmapping ("Registertime","Registertime"); A Bulk. Columnmappings.add (COLUMN5); theSqlbulkcopycolumnmapping column6 =NewSqlbulkcopycolumnmapping ("Certifygrade","Certifygrade"); + Bulk. Columnmappings.add (COLUMN6); -Sqlbulkcopycolumnmapping column7 =NewSqlbulkcopycolumnmapping ("Certifyno","Certifyno"); $ Bulk. Columnmappings.add (COLUMN7); $Sqlbulkcopycolumnmapping COLUMN8 =NewSqlbulkcopycolumnmapping ("Corporatename","Corporatename"); - Bulk. Columnmappings.add (COLUMN8); -Sqlbulkcopycolumnmapping Column9 =NewSqlbulkcopycolumnmapping ("Corporatephone","Corporatephone"); the Bulk. Columnmappings.add (COLUMN9); -Sqlbulkcopycolumnmapping Column10 =NewSqlbulkcopycolumnmapping ("Licensseno","Licensseno");Wuyi Bulk. Columnmappings.add (COLUMN10); theSqlbulkcopycolumnmapping column11 =NewSqlbulkcopycolumnmapping ("Regismoney","Regismoney"); - Bulk. Columnmappings.add (column11); WuSqlbulkcopycolumnmapping Column12 =NewSqlbulkcopycolumnmapping ("Response","Response"); - Bulk. Columnmappings.add (COLUMN12); AboutSqlbulkcopycolumnmapping Column13 =NewSqlbulkcopycolumnmapping ("Responsephone","Responsephone"); $ Bulk. Columnmappings.add (COLUMN13); -Sqlbulkcopycolumnmapping column14 =NewSqlbulkcopycolumnmapping ("Post","Post"); - Bulk. Columnmappings.add (COLUMN14); -Sqlbulkcopycolumnmapping column15 =NewSqlbulkcopycolumnmapping ("Email","Email"); A Bulk. Columnmappings.add (column15); +Sqlbulkcopycolumnmapping Column16 =NewSqlbulkcopycolumnmapping ("Zip","Zip"); the Bulk. Columnmappings.add (Column16); -                               $                                the  the                                 the  the                                Try -                                { in Bulk. WriteToServer (sqlreader); the trans.commit (); the  About                                } the                                Catch(Exception Err) the                                { the Trans. Rollback (); +  -                                    Throw NewException (Err. ToString ()); the Bayi                                } the                                   the                            } -                        } -                    } the                 } the             } the            the  -             the         } the  the         voidBulk_sqlrowscopied (Objectsender, Sqlrowscopiedeventargs e)94         { theResponse.Write ("number of completed rows:"+e.rowscopied.tostring ()); the}

Results from calling the SqlBulkCopy method:

Number of rows completed: 10

Number of rows completed: 20

Number of rows completed: 30

SqlBulkCopy parameter and method description can refer to website: https://msdn.microsoft.com/zh-cn/library/System.Data.SqlClient.SqlBulkCopy (v=vs.80) . aspx

Performs a bulk copy operation Sqlbulkcopy,sqltransaction in a transaction.