SqlBulkCopyColumnOrderHintCollection Class
Definition
Important
Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.
Collection of SqlBulkCopyColumnOrderHint objects that inherits from CollectionBase.
public ref class SqlBulkCopyColumnOrderHintCollection sealed : System::Collections::CollectionBasepublic sealed class SqlBulkCopyColumnOrderHintCollection : System.Collections.CollectionBasetype SqlBulkCopyColumnOrderHintCollection = class
inherit CollectionBasePublic NotInheritable Class SqlBulkCopyColumnOrderHintCollection
Inherits CollectionBase- Inheritance
-
SqlBulkCopyColumnOrderHintCollection
Examples
The following example bulk copies data from a source table in the AdventureWorks sample database to a destination table in the same database. SqlBulkCopyColumnOrderHints are added to the SqlBulkCopyColumnOrderHintCollection of the SqlBulkCopy object to specify order hints for the bulk copy operation.
Important
This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup.
This code is provided to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are in the same SQL Server instance, it is easier and faster to
use a Transact-SQL INSERT … SELECT statement to copy the data.
using System;
using System.Data;
using Microsoft.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();
// Set up the bulk copy object.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
// Specify the sort order for the ProductNumber column in
// the destination table.
bulkCopy.ColumnOrderHints.Add("ProductNumber", SortOrder.Ascending);
// Write from the source to the destination.
try
{
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;";
}
}
Remarks
Column order hints define the sort order of the column in the destination table.
SqlBulkCopy's performance is improved if the data being imported is sorted according to the clustered index on the table, if any. If the data is sorted in an order that differs from the order of a clustered index key or if there is no clustered index on the table, the order hint is ignored.
Order hints can be specified for any number of columns in the destination table. By default, the bulk insert operation assumes the data is unordered if no hints are provided.
The column names supplied must be valid column names in the destination table. The order in which hints can be specified is arbitrary. A single column name cannot be specified more than once.
If the ColumnMappings collection is not empty, order hints can only be provided for valid destination columns which have been mapped.
If a SortOrder of Unspecified is given, an ArgumentException will be thrown.
Constructors
| SqlBulkCopyColumnOrderHintCollection() |
Properties
| Item[Int32] |
Gets the SqlBulkCopyColumnOrderHint object at the specified index. |
Methods
| Add(SqlBulkCopyColumnOrderHint) |
Adds the specified order hint to the SqlBulkCopyColumnOrderHintCollection. |
| Add(String, SortOrder) |
Creates a new SqlBulkCopyColumnOrderHint and adds it to the collection. |
| Clear() | |
| Contains(SqlBulkCopyColumnOrderHint) |
Gets a value indicating whether a specified SqlBulkCopyColumnOrderHint object exists in the collection. |
| CopyTo(SqlBulkCopyColumnOrderHint[], Int32) |
Copies the elements of the SqlBulkCopyColumnOrderHintCollection to an array of SqlBulkCopyColumnOrderHint items, starting at a particular index. |
| IndexOf(SqlBulkCopyColumnOrderHint) |
Gets the index of the specified SqlBulkCopyColumnOrderHint object. |
| Insert(Int32, SqlBulkCopyColumnOrderHint) |
Insert a new SqlBulkCopyColumnOrderHint at the index specified. |
| Remove(SqlBulkCopyColumnOrderHint) |
Removes the specified SqlBulkCopyColumnOrderHint element from the SqlBulkCopyColumnOrderHintCollection. |
| RemoveAt(Int32) | |