SqlBulkCopyColumnMapping Costruttori
Definizione
Importante
Alcune informazioni sono relative alla release non definitiva del prodotto, che potrebbe subire modifiche significative prima della release definitiva. Microsoft non riconosce alcuna garanzia, espressa o implicita, in merito alle informazioni qui fornite.
Inizializza una nuova istanza della classe SqlBulkCopyColumnMapping.
Overload
| SqlBulkCopyColumnMapping() |
Costruttore senza parametri che inizializza un nuovo oggetto SqlBulkCopyColumnMapping. |
| SqlBulkCopyColumnMapping(Int32, Int32) |
Crea un nuovo mapping di colonna, utilizzando gli ordinali di colonna per fare riferimento alle colonne di origine e destinazione. |
| SqlBulkCopyColumnMapping(Int32, String) |
Crea un nuovo mapping di colonna, utilizzando un ordinale di colonna per fare riferimento alla colonna di origine e un nome di colonna per la colonna di destinazione. |
| SqlBulkCopyColumnMapping(String, Int32) |
Crea un nuovo mapping di colonna, utilizzando un nome di colonna per fare riferimento alla colonna di origine e un ordinale di colonna per la colonna di destinazione. |
| SqlBulkCopyColumnMapping(String, String) |
Crea un nuovo mapping di colonna, utilizzando i nomi di colonna per fare riferimento alle colonne di origine e destinazione. |
SqlBulkCopyColumnMapping()
Costruttore senza parametri che inizializza un nuovo oggetto SqlBulkCopyColumnMapping.
public:
SqlBulkCopyColumnMapping();public SqlBulkCopyColumnMapping ();Public Sub New ()Esempio
L'esempio seguente esegue la copia bulk di dati da una tabella di origine nel database di esempio AdventureWorks a una tabella di destinazione nello stesso database. Anche se il numero di colonne nella destinazione corrisponde al numero di colonne nell'origine, i nomi delle colonne e le posizioni ordinali non corrispondono. SqlBulkCopyColumnMapping gli oggetti vengono usati per creare una mappa di colonne per la copia bulk.
Importante
Questo esempio non verrà eseguito a meno che non siano state create le tabelle di lavoro, come descritto in Installazione di esempio di copia bulk. Il codice viene fornito solo per illustrare la sintassi relativa all'uso di SqlBulkCopy. Se le tabelle di origine e di destinazione si trovano nella stessa istanza di SQL Server, è più semplice e veloce usare un'istruzione Transact-SQL INSERT … SELECT per copiare i dati.
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.BulkCopyDemoDifferentColumns;",
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.BulkCopyDemoDifferentColumns";
// Set up the column mappings by name.
SqlBulkCopyColumnMapping mapID =
new SqlBulkCopyColumnMapping("ProductID", "ProdID");
bulkCopy.ColumnMappings.Add(mapID);
SqlBulkCopyColumnMapping mapName =
new SqlBulkCopyColumnMapping("Name", "ProdName");
bulkCopy.ColumnMappings.Add(mapName);
SqlBulkCopyColumnMapping mapMumber =
new SqlBulkCopyColumnMapping("ProductNumber", "ProdNum");
bulkCopy.ColumnMappings.Add(mapMumber);
// 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;";
}
}
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a connection to the AdventureWorks database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoDifferentColumns;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT ProductID, Name, ProductNumber " & _
"FROM Production.Product;", sourceConnection)
Dim reader As SqlDataReader = commandSourceData.ExecuteReader
' Set up the bulk copy object.
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = _
"dbo.BulkCopyDemoDifferentColumns"
' Set up the column mappings by name.
Dim mapID As New _
SqlBulkCopyColumnMapping("ProductID", "ProdID")
bulkCopy.ColumnMappings.Add(mapID)
Dim mapName As New _
SqlBulkCopyColumnMapping("Name", "ProdName")
bulkCopy.ColumnMappings.Add(mapName)
Dim mapMumber As New _
SqlBulkCopyColumnMapping("ProductNumber", "ProdNum")
bulkCopy.ColumnMappings.Add(mapMumber)
' Write from the source to the destination.
Try
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
' Close the SqlDataReader. The SqlBulkCopy
' object is automatically closed at the end
' of the Using block.
reader.Close()
End Try
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
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()
End Using
End Sub
Private Function GetConnectionString() As String
' 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;"
End Function
End Module
Commenti
Se si usa questo costruttore, è necessario definire l'origine per il mapping usando la SourceColumn proprietà o la proprietà e definire la destinazione per il mapping usando DestinationColumn la proprietà o SourceOrdinal la DestinationOrdinal proprietà.
Vedi anche
Si applica a
SqlBulkCopyColumnMapping(Int32, Int32)
Crea un nuovo mapping di colonna, utilizzando gli ordinali di colonna per fare riferimento alle colonne di origine e destinazione.
public:
SqlBulkCopyColumnMapping(int sourceColumnOrdinal, int destinationOrdinal);public SqlBulkCopyColumnMapping (int sourceColumnOrdinal, int destinationOrdinal);new System.Data.SqlClient.SqlBulkCopyColumnMapping : int * int -> System.Data.SqlClient.SqlBulkCopyColumnMappingPublic Sub New (sourceColumnOrdinal As Integer, destinationOrdinal As Integer)Parametri
- sourceColumnOrdinal
- Int32
Posizione ordinale della colonna di origine all'interno dell'origine dati.
- destinationOrdinal
- Int32
Posizione ordinale della colonna di destinazione all'interno della tabella di destinazione.
Esempio
L'esempio seguente esegue la copia bulk di dati da una tabella di origine nel database di esempio AdventureWorks a una tabella di destinazione nello stesso database. Anche se il numero di colonne nella destinazione corrisponde al numero di colonne nell'origine, i nomi delle colonne e le posizioni ordinali non corrispondono. SqlBulkCopyColumnMapping gli oggetti vengono usati per creare una mappa di colonne per la copia bulk in base alle posizioni ordinali delle colonne.
Importante
Questo esempio non verrà eseguito a meno che non siano state create le tabelle di lavoro, come descritto in Installazione di esempio di copia bulk. Il codice viene fornito solo per illustrare la sintassi relativa all'uso di SqlBulkCopy. Se le tabelle di origine e di destinazione si trovano nella stessa istanza di SQL Server, è più semplice e veloce usare un'istruzione Transact-SQL INSERT … SELECT per copiare i dati.
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.BulkCopyDemoDifferentColumns;",
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.BulkCopyDemoDifferentColumns";
// Set up the column mappings by ordinal.
SqlBulkCopyColumnMapping columnMapID =
new SqlBulkCopyColumnMapping(0, 0);
bulkCopy.ColumnMappings.Add(columnMapID);
SqlBulkCopyColumnMapping columnMapName =
new SqlBulkCopyColumnMapping(1, 2);
bulkCopy.ColumnMappings.Add(columnMapName);
SqlBulkCopyColumnMapping columnMapNumber =
new SqlBulkCopyColumnMapping(2, 1);
bulkCopy.ColumnMappings.Add(columnMapNumber);
// 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;";
}
}
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a connection to the AdventureWorks database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoDifferentColumns;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT ProductID, Name, ProductNumber " & _
"FROM Production.Product;", sourceConnection)
Dim reader As SqlDataReader = commandSourceData.ExecuteReader
' Set up the bulk copy object.
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = _
"dbo.BulkCopyDemoDifferentColumns"
' Set up the column mappings by ordinal.
Dim columnMapID As New _
SqlBulkCopyColumnMapping(0, 0)
bulkCopy.ColumnMappings.Add(columnMapID)
Dim columnMapName As New _
SqlBulkCopyColumnMapping(1, 2)
bulkCopy.ColumnMappings.Add(columnMapName)
Dim columnMapNumber As New _
SqlBulkCopyColumnMapping(2, 1)
bulkCopy.ColumnMappings.Add(columnMapNumber)
' Write from the source to the destination.
Try
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
' Close the SqlDataReader. The SqlBulkCopy
' object is automatically closed at the end
' of the Using block.
reader.Close()
End Try
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
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()
End Using
End Sub
Private Function GetConnectionString() As String
' 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;"
End Function
End Module
Vedi anche
Si applica a
SqlBulkCopyColumnMapping(Int32, String)
Crea un nuovo mapping di colonna, utilizzando un ordinale di colonna per fare riferimento alla colonna di origine e un nome di colonna per la colonna di destinazione.
public:
SqlBulkCopyColumnMapping(int sourceColumnOrdinal, System::String ^ destinationColumn);public SqlBulkCopyColumnMapping (int sourceColumnOrdinal, string destinationColumn);new System.Data.SqlClient.SqlBulkCopyColumnMapping : int * string -> System.Data.SqlClient.SqlBulkCopyColumnMappingPublic Sub New (sourceColumnOrdinal As Integer, destinationColumn As String)Parametri
- sourceColumnOrdinal
- Int32
Posizione ordinale della colonna di origine all'interno dell'origine dati.
- destinationColumn
- String
Nome della colonna di destinazione all'interno della tabella di destinazione.
Esempio
L'esempio seguente esegue la copia bulk di dati da una tabella di origine nel database di esempio AdventureWorks a una tabella di destinazione nello stesso database. Anche se il numero di colonne nella destinazione corrisponde al numero di colonne nell'origine, i nomi delle colonne e le posizioni ordinali non corrispondono. SqlBulkCopyColumnMapping gli oggetti vengono usati per creare una mappa di colonne per la copia bulk.
Importante
Questo esempio non verrà eseguito a meno che non siano state create le tabelle di lavoro, come descritto in Installazione di esempio di copia bulk. Il codice viene fornito solo per illustrare la sintassi relativa all'uso di SqlBulkCopy. Se le tabelle di origine e di destinazione si trovano nella stessa istanza di SQL Server, è più semplice e veloce usare un'istruzione Transact-SQL INSERT … SELECT per copiare i dati.
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.BulkCopyDemoDifferentColumns;",
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.BulkCopyDemoDifferentColumns";
// Set up the column mappings by ordinal and name.
SqlBulkCopyColumnMapping columnMapID =
new SqlBulkCopyColumnMapping(0, "ProdID");
bulkCopy.ColumnMappings.Add(columnMapID);
SqlBulkCopyColumnMapping columnMapName =
new SqlBulkCopyColumnMapping(1, "ProdName");
bulkCopy.ColumnMappings.Add(columnMapName);
SqlBulkCopyColumnMapping columnMapNumber =
new SqlBulkCopyColumnMapping(2, "ProdNum");
bulkCopy.ColumnMappings.Add(columnMapNumber);
// 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;";
}
}
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a connection to the AdventureWorks database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoDifferentColumns;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT ProductID, Name, ProductNumber " & _
"FROM Production.Product;", sourceConnection)
Dim reader As SqlDataReader = commandSourceData.ExecuteReader
' Set up the bulk copy object.
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = _
"dbo.BulkCopyDemoDifferentColumns"
' Set up the column mappings by ordinal and name.
Dim columnMapID As New _
SqlBulkCopyColumnMapping(0, "ProdID")
bulkCopy.ColumnMappings.Add(columnMapID)
Dim columnMapName As New _
SqlBulkCopyColumnMapping(1, "ProdName")
bulkCopy.ColumnMappings.Add(columnMapName)
Dim columnMapNumber As New _
SqlBulkCopyColumnMapping(2, "ProdNum")
bulkCopy.ColumnMappings.Add(columnMapNumber)
' Write from the source to the destination.
Try
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
' Close the SqlDataReader. The SqlBulkCopy
' object is automatically closed at the end
' of the Using block.
reader.Close()
End Try
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
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()
End Using
End Sub
Private Function GetConnectionString() As String
' 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;"
End Function
End Module
Vedi anche
Si applica a
SqlBulkCopyColumnMapping(String, Int32)
Crea un nuovo mapping di colonna, utilizzando un nome di colonna per fare riferimento alla colonna di origine e un ordinale di colonna per la colonna di destinazione.
public:
SqlBulkCopyColumnMapping(System::String ^ sourceColumn, int destinationOrdinal);public SqlBulkCopyColumnMapping (string sourceColumn, int destinationOrdinal);new System.Data.SqlClient.SqlBulkCopyColumnMapping : string * int -> System.Data.SqlClient.SqlBulkCopyColumnMappingPublic Sub New (sourceColumn As String, destinationOrdinal As Integer)Parametri
- sourceColumn
- String
Nome della colonna di origine all'interno dell'origine dati.
- destinationOrdinal
- Int32
Posizione ordinale della colonna di destinazione all'interno della tabella di destinazione.
Esempio
L'esempio seguente esegue la copia bulk di dati da una tabella di origine nel database di esempio AdventureWorks a una tabella di destinazione nello stesso database. Anche se il numero di colonne nella destinazione corrisponde al numero di colonne nell'origine, i nomi delle colonne e le posizioni ordinali non corrispondono. SqlBulkCopyColumnMapping gli oggetti vengono usati per creare una mappa di colonne per la copia bulk.
Importante
Questo esempio non verrà eseguito a meno che non siano state create le tabelle di lavoro, come descritto in Installazione di esempio di copia bulk. Il codice viene fornito solo per illustrare la sintassi relativa all'uso di SqlBulkCopy. Se le tabelle di origine e di destinazione si trovano nella stessa istanza di SQL Server, è più semplice e veloce usare un'istruzione Transact-SQL INSERT … SELECT per copiare i dati.
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.BulkCopyDemoDifferentColumns;",
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.BulkCopyDemoDifferentColumns";
// Set up the column mappings by name and ordinal.
SqlBulkCopyColumnMapping columnMapID =
new SqlBulkCopyColumnMapping("ProductID", 0);
bulkCopy.ColumnMappings.Add(columnMapID);
SqlBulkCopyColumnMapping columnMapName =
new SqlBulkCopyColumnMapping("Name", 2);
bulkCopy.ColumnMappings.Add(columnMapName);
SqlBulkCopyColumnMapping columnMapNumber =
new SqlBulkCopyColumnMapping("ProductNumber", 1);
bulkCopy.ColumnMappings.Add(columnMapNumber);
// 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;";
}
}
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a connection to the AdventureWorks database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoDifferentColumns;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT ProductID, Name, ProductNumber " & _
"FROM Production.Product;", sourceConnection)
Dim reader As SqlDataReader = commandSourceData.ExecuteReader
' Set up the bulk copy object.
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = _
"dbo.BulkCopyDemoDifferentColumns"
' Set up the column mappings by name and ordinal.
Dim columnMapID As New _
SqlBulkCopyColumnMapping("ProductID", 0)
bulkCopy.ColumnMappings.Add(columnMapID)
Dim columnMapName As New _
SqlBulkCopyColumnMapping("Name", 2)
bulkCopy.ColumnMappings.Add(columnMapName)
Dim columnMapNumber As New _
SqlBulkCopyColumnMapping("ProductNumber", 1)
bulkCopy.ColumnMappings.Add(columnMapNumber)
' Write from the source to the destination.
Try
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
' Close the SqlDataReader. The SqlBulkCopy
' object is automatically closed at the end
' of the Using block.
reader.Close()
End Try
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
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()
End Using
End Sub
Private Function GetConnectionString() As String
' 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;"
End Function
End Module
Vedi anche
Si applica a
SqlBulkCopyColumnMapping(String, String)
Crea un nuovo mapping di colonna, utilizzando i nomi di colonna per fare riferimento alle colonne di origine e destinazione.
public:
SqlBulkCopyColumnMapping(System::String ^ sourceColumn, System::String ^ destinationColumn);public SqlBulkCopyColumnMapping (string sourceColumn, string destinationColumn);new System.Data.SqlClient.SqlBulkCopyColumnMapping : string * string -> System.Data.SqlClient.SqlBulkCopyColumnMappingPublic Sub New (sourceColumn As String, destinationColumn As String)Parametri
- sourceColumn
- String
Nome della colonna di origine all'interno dell'origine dati.
- destinationColumn
- String
Nome della colonna di destinazione all'interno della tabella di destinazione.
Esempio
L'esempio seguente esegue la copia bulk di dati da una tabella di origine nel database di esempio AdventureWorks a una tabella di destinazione nello stesso database. Anche se il numero di colonne nella destinazione corrisponde al numero di colonne nell'origine, i nomi delle colonne e le posizioni ordinali non corrispondono. SqlBulkCopyColumnMapping gli oggetti vengono usati per creare una mappa di colonne per la copia bulk.
Importante
Questo esempio non verrà eseguito a meno che non siano state create le tabelle di lavoro, come descritto in Installazione di esempio di copia bulk. Il codice viene fornito solo per illustrare la sintassi relativa all'uso di SqlBulkCopy. Se le tabelle di origine e di destinazione si trovano nella stessa istanza di SQL Server, è più semplice e veloce usare un'istruzione Transact-SQL INSERT … SELECT per copiare i dati.
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.BulkCopyDemoDifferentColumns;",
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.BulkCopyDemoDifferentColumns";
// Set up the column mappings by name.
SqlBulkCopyColumnMapping mapID =
new SqlBulkCopyColumnMapping("ProductID", "ProdID");
bulkCopy.ColumnMappings.Add(mapID);
SqlBulkCopyColumnMapping mapName =
new SqlBulkCopyColumnMapping("Name", "ProdName");
bulkCopy.ColumnMappings.Add(mapName);
SqlBulkCopyColumnMapping mapMumber =
new SqlBulkCopyColumnMapping("ProductNumber", "ProdNum");
bulkCopy.ColumnMappings.Add(mapMumber);
// 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;";
}
}
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a connection to the AdventureWorks database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoDifferentColumns;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT ProductID, Name, ProductNumber " & _
"FROM Production.Product;", sourceConnection)
Dim reader As SqlDataReader = commandSourceData.ExecuteReader
' Set up the bulk copy object.
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = _
"dbo.BulkCopyDemoDifferentColumns"
' Set up the column mappings by name.
Dim mapID As New _
SqlBulkCopyColumnMapping("ProductID", "ProdID")
bulkCopy.ColumnMappings.Add(mapID)
Dim mapName As New _
SqlBulkCopyColumnMapping("Name", "ProdName")
bulkCopy.ColumnMappings.Add(mapName)
Dim mapMumber As New _
SqlBulkCopyColumnMapping("ProductNumber", "ProdNum")
bulkCopy.ColumnMappings.Add(mapMumber)
' Write from the source to the destination.
Try
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
' Close the SqlDataReader. The SqlBulkCopy
' object is automatically closed at the end
' of the Using block.
reader.Close()
End Try
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
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()
End Using
End Sub
Private Function GetConnectionString() As String
' 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;"
End Function
End Module
Vedi anche
Si applica a
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per