SqlBulkCopy 有效率地將其他來源的資料大量載入 SQL Server 資料表

引用來源

string connectionString = GetConnectionString();

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());

// 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)
{

}
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());

}
}