SqlBulkCopy Quick Insert Data

[Go] This article comes from Http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlbulkcopy (v=vs.80). aspx this code is used to demonstrate the use of only SqlBulkCopy's syntax. If both the source and target tables are in the same SQL Server instance, use the Transact-SQLINSERT ... SELECTStatement to copy data is more convenient and quick.

Using system.data.sqlclient;class program{static void Main () {String connectionString = GetConnectionString        ();        Open a sourceconnection to the AdventureWorks database. using (SqlConnection sourceconnection = new SqlConnection (connectionString)) {SOURCEC Onnection.            Open ();            Perform a initial count on the destination table. SqlCommand commandrowcount = new SqlCommand ("Select COUNT (*) from" + "dbo.            BulkCopyDemoMatchingColumns; ", sourceconnection);            Long Countstart = System.Convert.ToInt32 (Commandrowcount.executescalar ());            Console.WriteLine ("Starting Row count = {0}", Countstart);            Get data from the source table as a SqlDataReader.  SqlCommand commandsourcedata = new SqlCommand ("Select ProductID, Name," + "ProductNumber" + "from Production.Product;", sourceconnection);            SqlDataReader reader = Commandsourcedata.executereader (); Open the destination connection. In the "real world" you would//"use" SqlBulkCopy to move data from one table to the other/ The same database.            Demonstration purposes only.                using (SqlConnection destinationconnection = new SqlConnection (connectionString)) {                Destinationconnection.open ();                 Set up the bulk copy object.                 Note that the column positions in the source//data reader match the column positions in                The destination table so there are no need to//map columns.                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy (destinationconnection)) { Bulkcopy.destinationtablename = "dbo." BulkcopydemomatchingColumns ";                        try {//Write from the source to the destination.                    Bulkcopy.writetoserver (reader); } catch (Exception ex) {Console.WriteLine (ex.                    Message); } finally {//Close the SqlDataReader.  The SqlBulkCopy//object is automatically closed at the end//of the Using                        Block. Reader.                    Close (); }}//Perform a final count on the destination//table to see how many row                s were added.                Long countend = System.Convert.ToInt32 (Commandrowcount.executescalar ());                Console.WriteLine ("Ending row count = {0}", countend); Console.WriteLine ("{0} rows were added.", Countend -Countstart);                Console.WriteLine ("Press Enter to finish.");            Console.ReadLine (); }}} private static string GetConnectionString ()//To avoid storing the sourceconnection string in Y     Our code,//can retrieve it from a configuration file. {return "Data source= (local);" + "Integrated security=true;" + "Initial catalog=adventurew Orks, ";}}

　　

SqlBulkCopy Quick Insert Data