SqlBulkCopyColumnMappingCollection.RemoveAt メソッド

.NET Framework クラス ライブラリ リファレンス

SqlBulkCopyColumnMappingCollection.RemoveAt メソッド

メモ : このメソッドは、.NET Framework version 2.0 で新しく追加されたものです。

指定したインデックス位置にあるマップをコレクションから削除します。

名前空間: System.Data.SqlClient
アセンブリ: System.Data (system.data.dll 内)
構文

Visual Basic (宣言)

Public Sub RemoveAt ( _
    index As Integer _
)

Visual Basic (使用法)

Dim instance As SqlBulkCopyColumnMappingCollection
Dim index As Integer

instance.RemoveAt(index)

C#

public void RemoveAt (
    int index
)

C++

public:
void RemoveAt (
    int index
)

J#

public void RemoveAt (
    int index
)

JScript

public function RemoveAt (
    index : int
)

パラメータ

index

コレクションから削除する SqlBulkCopyColumnMapping オブジェクトの 0 から始まるインデックス番号。

解説

RemoveAt は、1 つの SqlBulkCopy インスタンスで複数の一括コピー操作を処理する場合に最もよく使用されるメソッドです。ある一括コピー操作のために列マップを作成した場合は、次の一括コピーのためのマップを定義する前に、WriteToServer メソッドを呼び出してから、もう適用しないマップを削除する必要があります。Clear メソッドを使用してコレクション全体を消去することも、Remove メソッドまたは RemoveAt メソッドを使用してマップを個別に削除することもできます。

パフォーマンス上、繰り返し一括コピー操作を実行する場合、同じ SqlBulkCopy のインスタンスを使用した方が、別々の SqlBulkCopy を使用するよりも効率的です。

使用例

次の例では、2 回の一括コピー操作を実行します。1 回目の操作で注文のヘッダー情報をコピーし、2 回目の操作で注文の詳細情報をコピーしています。ここでは、一括コピー操作ごとに列マップを作成していますが、コピー元とコピー先の序数位置が一致しているため、必ずしもマップを作成する必要はありません。この例では、いずれの一括コピーにも、SalesOrderID のマップが使用されるため、1 回目の一括コピー操作の後でコレクション全体を消去するのではなく、SalesOrderID のマップを除くすべてのマップを削除してから、2 回目の一括コピー操作に必要なマップを追加しています。

重要 :
このサンプルを実行するには、あらかじめ、「バルク コピーの例のためのテーブルの作成」の説明に従って作業テーブルを作成しておく必要があります。このコードは、SqlBulkCopy を使用する構文を示すためだけに提供されています。同じ SQL Server インスタンスにコピー元テーブルとコピー先テーブルが存在する場合、Transact-SQL の INSERT … SELECT ステートメントを使用した方が容易かつ迅速にデータをコピーできます。

Visual Basic

Imports System.Data.SqlClient

Module Module1
    Sub Main()
        Dim connectionString As String
 = GetConnectionString()

        ' Open a connection to the AdventureWorks database.
        Using connection As SqlConnection = New
 SqlConnection(connectionString)
            connection.Open()

            ' Empty the destination tables.
            Dim deleteHeader As New
 SqlCommand( _
              "DELETE FROM dbo.BulkCopyDemoOrderHeader;",
 connection)
            deleteHeader.ExecuteNonQuery()
            deleteHeader.Dispose()
            Dim deleteDetail As New
 SqlCommand( _
              "DELETE FROM dbo.BulkCopyDemoOrderDetail;",
 connection)
            deleteDetail.ExecuteNonQuery()

            ' Perform an initial count on the destination table 
            ' with matching columns.
            Dim countRowHeader As New
 SqlCommand( _
               "SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderHeader;",
 _
                connection)
            Dim countStartHeader As Long
 = System.Convert.ToInt32( _
             countRowHeader.ExecuteScalar())
            Console.WriteLine("Starting row count for Header table
 = {0}", _
             countStartHeader)

            ' Perform an initial count on the destination table 
            ' with different column positions.
            Dim countRowDetail As New
 SqlCommand( _
                "SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderDetail;",
 _
                connection)
            Dim countStartDetail As Long
 = System.Convert.ToInt32( _
                countRowDetail.ExecuteScalar())
            Console.WriteLine("Starting row count for Detail table
 = " & _
               countStartDetail)

            ' Get data from the source table as a SqlDataReader.
            ' The Sales.SalesOrderHeader and Sales.SalesOrderDetail
            ' tables are quite large and could easily cause a timeout
            ' if all data from the tables is added to the destination.
            ' To keep the example simple and quick, a parameter is 
            ' used to select only orders for a particular account as
            ' the source for the bulk insert.
            Dim headerData As SqlCommand =
 New SqlCommand( _
             "SELECT [SalesOrderID], [OrderDate], "
 & _
             "[AccountNumber] FROM [Sales].[SalesOrderHeader]
 " & _
             "WHERE [AccountNumber] = @accountNumber;",
 _
             connection)

            Dim parameterAccount As SqlParameter
 = New SqlParameter()
            parameterAccount.ParameterName = "@accountNumber"
            parameterAccount.SqlDbType = SqlDbType.NVarChar
            parameterAccount.Direction = ParameterDirection.Input
            parameterAccount.Value = "10-4020-000034"
            headerData.Parameters.Add(parameterAccount)

            Dim readerHeader As SqlDataReader
 = _
             headerData.ExecuteReader()

            ' Get the Detail data in a separate connection.
            Using connection2 As SqlConnection = New
 SqlConnection(connectionString)
                connection2.Open()

                Dim sourceDetailData As SqlCommand
 = New SqlCommand( _
                 "SELECT [Sales].[SalesOrderDetail].[SalesOrderID],
 " & _
                 "[SalesOrderDetailID], [OrderQty], [ProductID],
 [UnitPrice] " & _
                 "FROM [Sales].[SalesOrderDetail] INNER JOIN "
 & _
                 "[Sales].[SalesOrderHeader] " &
 _
                 "ON [Sales].[SalesOrderDetail].[SalesOrderID]
 = " & _
                 "[Sales].[SalesOrderHeader].[SalesOrderID] "
 & _
                 "WHERE [AccountNumber] = @accountNumber;",
 connection2)

                Dim accountDetail As SqlParameter
 = New SqlParameter()
                accountDetail.ParameterName = "@accountNumber"
                accountDetail.SqlDbType = SqlDbType.NVarChar
                accountDetail.Direction = ParameterDirection.Input
                accountDetail.Value = "10-4020-000034"
                sourceDetailData.Parameters.Add( _
                 accountDetail)

                Dim readerDetail As SqlDataReader
 = _
                 sourceDetailData.ExecuteReader()

                ' Create the SqlBulkCopy object.
                Using bulkCopy As SqlBulkCopy = _
                  New SqlBulkCopy(connectionString)
                    bulkCopy.DestinationTableName = "dbo.BulkCopyDemoOrderHeader"

                    ' Guarantee that columns are mapped correctly by
                    ' defining the column mappings for the order.
                    bulkCopy.ColumnMappings.Add("SalesOrderID",
 "SalesOrderID")
                    bulkCopy.ColumnMappings.Add("OrderDate",
 "OrderDate")
                    bulkCopy.ColumnMappings.Add("AccountNumber",
 "AccountNumber")
 
                   ' Write readerHeader to the destination.
                    Try
                        bulkCopy.WriteToServer(readerHeader)
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    Finally
                        readerHeader.Close()
                    End Try

                    ' Set up the order details destination.
                    bulkCopy.DestinationTableName = "dbo.BulkCopyDemoOrderDetail"

                    ' Rather than clearing mappings that are not necessary
                    ' for the next bulk copy operation, the unneeded
         
                    ' mappings are removed with the RemoveAt method.
                    bulkCopy.ColumnMappings.RemoveAt(2)
                    bulkCopy.ColumnMappings.RemoveAt(1)

                    ' Add order detail column mappings.
                    bulkCopy.ColumnMappings.Add("SalesOrderDetailID",
 "SalesOrderDetailID")
                    bulkCopy.ColumnMappings.Add("OrderQty",
 "OrderQty")
                    bulkCopy.ColumnMappings.Add("ProductID",
 "ProductID")
                    bulkCopy.ColumnMappings.Add("UnitPrice",
 "UnitPrice")

                    ' Write readerDetail to the destination.
                    Try
                        bulkCopy.WriteToServer(readerDetail)
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    Finally
                        readerDetail.Close()
                    End Try
                End Using

                ' Perform a final count on the destination tables
                ' to see how many rows were added.
                Dim countEndHeader As Long
 = System.Convert.ToInt32( _
                  countRowHeader.ExecuteScalar())
                Console.WriteLine("{0} rows were added to the
 Header table.", _
                  countEndHeader - countStartHeader)
                Dim countEndDetail As Long
 = System.Convert.ToInt32( _
                   countRowDetail.ExecuteScalar())
                Console.WriteLine("{0} rows were added to the
 Detail table.", _
                    countEndDetail - countStartDetail)

                Console.WriteLine("Press Enter to finish.")
                Console.ReadLine()
            End Using
        End Using
    End Sub

    Private Function GetConnectionString()
 As String
        ' To avoid storing the connection 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

C#

using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        // Open a connection to the AdventureWorks database.
        using (SqlConnection connection =
                   new SqlConnection(connectionString))
        {
            connection.Open();

            // Empty the destination tables. 
            SqlCommand deleteHeader = new SqlCommand(
                "DELETE FROM dbo.BulkCopyDemoOrderHeader;",
                connection);
            deleteHeader.ExecuteNonQuery();
            SqlCommand deleteDetail = new SqlCommand(
                "DELETE FROM dbo.BulkCopyDemoOrderDetail;",
                connection);
            deleteDetail.ExecuteNonQuery();

            // Perform an initial count on the destination
            //  table with matching columns. 
            SqlCommand countRowHeader = new SqlCommand(
                "SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderHeader;",
                connection);
            long countStartHeader = System.Convert.ToInt32(
                countRowHeader.ExecuteScalar());
            Console.WriteLine(
                "Starting row count for Header table = {0}"
,
                countStartHeader);

            // Perform an initial count on the destination
            // table with different column positions. 
            SqlCommand countRowDetail = new SqlCommand(
                "SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderDetail;",
                connection);
            long countStartDetail = System.Convert.ToInt32(
                countRowDetail.ExecuteScalar());
            Console.WriteLine(
                "Starting row count for Detail table = {0}"
,
                countStartDetail);

            // Get data from the source table as a SqlDataReader.
            // The Sales.SalesOrderHeader and Sales.SalesOrderDetail
            // tables are quite large and could easily cause a timeout
            // if all data from the tables is added to the destination.
 
            // To keep the example simple and quick, a parameter is
  
            // used to select only orders for a particular account 
            // as the source for the bulk insert. 
            SqlCommand headerData = new SqlCommand(
                "SELECT [SalesOrderID], [OrderDate], " +
                "[AccountNumber] FROM [Sales].[SalesOrderHeader] " +
                "WHERE [AccountNumber] = @accountNumber;",
                connection);
            SqlParameter parameterAccount = new SqlParameter();
            parameterAccount.ParameterName = "@accountNumber";
            parameterAccount.SqlDbType = SqlDbType.NVarChar;
            parameterAccount.Direction = ParameterDirection.Input;
            parameterAccount.Value = "10-4020-000034";
            headerData.Parameters.Add(parameterAccount);
            SqlDataReader readerHeader = headerData.ExecuteReader();

            // Get the Detail data in a separate connection.
            using (SqlConnection connection2 = new
 SqlConnection(connectionString))
            {
                connection2.Open();
                SqlCommand sourceDetailData = new SqlCommand(
                    "SELECT [Sales].[SalesOrderDetail].[SalesOrderID], [SalesOrderDetailID],
 " +
                    "[OrderQty], [ProductID], [UnitPrice] FROM [Sales].[SalesOrderDetail]
 " +
                    "INNER JOIN [Sales].[SalesOrderHeader] ON [Sales].[SalesOrderDetail]."
 +
                    "[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]
 " +
                    "WHERE [AccountNumber] = @accountNumber;", connection2);

                    SqlParameter accountDetail = new SqlParameter();
                    accountDetail.ParameterName = "@accountNumber";
                    accountDetail.SqlDbType = SqlDbType.NVarChar;
                    accountDetail.Direction = ParameterDirection.Input;
                    accountDetail.Value = "10-4020-000034";
                    sourceDetailData.Parameters.Add(accountDetail);
                    SqlDataReader readerDetail = sourceDetailData.ExecuteReader();

                // Create the SqlBulkCopy object. 
                using (SqlBulkCopy bulkCopy =
                           new SqlBulkCopy(connectionString))
                {
                    bulkCopy.DestinationTableName =
                        "dbo.BulkCopyDemoOrderHeader";

                    // Guarantee that columns are mapped correctly by
                    // defining the column mappings for the order.
                    bulkCopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID");
                    bulkCopy.ColumnMappings.Add("OrderDate", "OrderDate");
                    bulkCopy.ColumnMappings.Add("AccountNumber", "AccountNumber");

                    // Write readerHeader to the destination.
                    try
                    {
                        bulkCopy.WriteToServer(readerHeader);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        readerHeader.Close();
                    }

                    // Set up the order details destination. 
                    bulkCopy.DestinationTableName ="dbo.BulkCopyDemoOrderDetail";

                    // Rather than clearing mappings that are not necessary
                    // for the next bulk copyo peration, the unneeded
 mappings
                    //  are removed with the RemoveAt method.
                    bulkCopy.ColumnMappings.RemoveAt(2);
                    bulkCopy.ColumnMappings.RemoveAt(1);

                    // Add order detail column mappings.
                    bulkCopy.ColumnMappings.Add("SalesOrderDetailID", "SalesOrderDetailID");
                    bulkCopy.ColumnMappings.Add("OrderQty", "OrderQty");
                    bulkCopy.ColumnMappings.Add("ProductID", "ProductID");
                    bulkCopy.ColumnMappings.Add("UnitPrice", "UnitPrice");
                    bulkCopy.WriteToServer(readerDetail);

                    // Write readerDetail to the destination.
                    try
                    {
                        bulkCopy.WriteToServer(readerDetail);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        readerDetail.Close();
                    }
                }

                // Perform a final count on the destination
                // tables to see how many rows were added. 
                long countEndHeader = System.Convert.ToInt32(
                    countRowHeader.ExecuteScalar());
                Console.WriteLine("{0} rows were added to the Header table."
,
                    countEndHeader - countStartHeader);
                long countEndDetail = System.Convert.ToInt32(
                    countRowDetail.ExecuteScalar());
                Console.WriteLine("{0} rows were added to the Detail table."
,
                    countEndDetail - countStartDetail);
                Console.WriteLine("Press Enter to finish.");
                Console.ReadLine();
            }
        }
    }

    private static string
 GetConnectionString()
        // To avoid storing the connection string in your code, 
        // you can retrieve it from a configuration file. 
    {
        return "Data Source=(local); " +
            " Integrated Security=true;" +
            "Initial Catalog=AdventureWorks;";
    }
}

プラットフォーム

Windows 98, Windows 2000 SP4, Windows Millennium Edition, Windows Server 2003, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP SP2, Windows XP Starter Edition

開発プラットフォームの中には、.NET Framework によってサポートされていないバージョンがあります。サポートされているバージョンについては、「システム要件」を参照してください。

バージョン情報

.NET Framework
サポート対象 : 2.0

参照

関連項目
SqlBulkCopyColumnMappingCollection クラス
SqlBulkCopyColumnMappingCollection メンバ
System.Data.SqlClient 名前空間
その他の技術情報
バルク コピー操作の実行