SqlBulkCopy class
link: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
remarks:
Microsoft SQL Server includes a popular command-prompt utility named bcp for moving data from one table to another, whether on a single server or between servers. The SqlBulkCopy class lets you write managed code solutions that provide similar functionality. There are other ways to load data into a SQL Server table (INSERT statements, for example), but SqlBulkCopy offers a significant performance advantage over them.
The SqlBulkCopy class can be used to write data only 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 a DataTable instance or read with a IDataReader instance.
C# exmaple:
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)) { sourceConnection.Open(); // Perform an 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 // not use SqlBulkCopy to move data from one table to the other // in the same database. This is for 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 is 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 rows 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 your code, // you can retrieve it from a configuration file. { return "Data Source=(local); " + " Integrated Security=true;" + "Initial Catalog=AdventureWorks;"; } }
It also can transfer data from Xml file to database table.
link:http://www.codeproject.com/KB/database/TransferUsingSQLBulkCopy.aspx
Transferring Data from XML File to Database Table
The data source is not only limited to database tables, but you can also use XML files. Here is a very simple XML file which is used as a source for the bulk copy operation. (Products.xml)
<?xml version="1.0" encoding="utf-8" ?> <Products> <Product productID="1" productName="Chai" /> <Product productID="2" productName="Football" /> <Product productID="3" productName="Soap" /> <Product productID="4" productName="Green Tea" /> </Products>
private static void PerformBulkCopyXMLDataSource() { string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true"; DataSet ds = new DataSet(); DataTable sourceData = new DataTable(); ds.ReadXml(@"C:/Products.xml"); sourceData = ds.Tables[0]; // open the destination data using (SqlConnection destinationConnection = new SqlConnection(connectionString)) { // open the connection destinationConnection.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString)) { // column mappings bulkCopy.ColumnMappings.Add("productID", "ProductID"); bulkCopy.ColumnMappings.Add("productName", "Name"); bulkCopy.DestinationTableName = "Products_TopSelling"; bulkCopy.WriteToServer(sourceData); } } }The file is first read into the
DataTable
and then fed to theWriteToServer
method of theSqlBulkCopy
class. Since, the destination table isProducts_TopSelling
, we had to perform the column mapping.