ASP.NET C# SqlBulkCopy class 대량 자료 저장 사용하는 방법

ASP.NET C# SqlBulkCopy class 사용하는 방법

 

Bulk copies 데이터베이스 테이블에 많은 양의 데이터를 채워야 할 때 일반적입니다. 일반적으로 열어야 하는 실제 파일이 있고 그 내용을 데이터베이스에 저장해야 하는 경우에 사용됩니다.

 

.NET Framework는 대량 작업을 사용하여 SQL Server 테이블에 데이터를 쉽게 로드할 수 있는 방법을 제공합니다. System.Data.SqlClient 네임스페이스 에서 사용할 수 있는 SqlBulkCopy 클래스 에는 이러한 종류의 작업에 도움이 됩니다.

 

 

생성자 오버로드

 

 

SqlBulkCopy 클래스에 대해 네 가지 생성자 오버로드를 사용할 수 있습니다 .

• public SqlBulkCopy(SqlConnection connection);
• public SqlBulkCopy(string connectionString);
• public SqlBulkCopy(string connectionString, SqlBulkCopyOptions copyOptions);
• public SqlBulkCopy(SqlConnection connection, SqlBulkCopyOptions copyOptions, SqlTransaction externalTransaction);

첫 번째 및 두 번째 오버로드는 각각 SqlConnection 또는 연결 문자열을 허용하므로 이해하기가 매우 간단하지만 세 번째 오버로드에서 매개변수로 SqlBulkCopyOptions 열거자를 사용하는 것을 보셨을 것입니다. 이것은 copy process 의 behavior 을 정의 하는 bit flag enumerator (bit flag enumerators 는 팁 #97 에 설명되어 있음 )입니다.  

• Default: 모든 옵션에 기본값을 사용합니다
• KeepIdentity: 소스 ID 값을 유지합니다. 지정하지 않으면 대상에서 ID 값을 할당합니다.;
• CheckConstraints: 데이터가 삽입되는 동안 제약 조건을 확인합니다. 기본적으로 제약 조건은 확인되지 않습니다.
• TableLock: 대량 복사 작업 기간 동안 대량 업데이트 잠금을 얻습니다. 지정하지 않으면 행 잠금이 사용됩니다.
• KeepNulls: 기본값 설정에 관계없이 대상 테이블의 null 값을 유지합니다. 지정하지 않으면 해당하는 경우 null 값이 기본값으로 바뀝니다.
• FireTriggers: 지정되면 서버가 데이터베이스에 삽입되는 행에 대한 삽입 트리거를 실행하도록 합니다.
• UseInternalTransaction: 지정하면 대량 복사 작업의 각 일괄 처리가 트랜잭션 내에서 발생합니다. 이 옵션을 지정하고 생성자에 SqlTransaction 개체도 제공하면 ArgumentException이 발생합니다.

 

마지막 overload  는  copy process. 에 대한 transaction 모델을 정의하는 SqlTransaction 개체도 허용합니다.

 

Properties

• int BatchSize: 각 배치의 행 수를 가져오거나 설정합니다. 각 일괄 처리가 끝나면 일괄 처리의 행이 서버로 전송됩니다.
• int BulkCopyTimeout: 작업이 시간 초과되기 전에 완료되는 데 걸리는 시간(초)을 가져오거나 설정합니다.
• SqlBulkCopyColumnMappingCollection ColumnMappings: 원본 테이블과 대상 테이블 간의 관계를 나타내는 SqlBulkCopyColumnMapping 개체 목록을 가져옵니다.
• string DestinationTableName: 데이터베이스의 대상 테이블 이름을 가져오거나 설정합니다.
• int NotifyAfter: 알림 이벤트를 생성하기 전에 처리할 행 수를 가져오거나 설정합니다.
  
  

Event

• SqlRowsCopiedEventHandler SqlRowsCopied: T데이터베이스 테이블에 복사된 행 수가 NotifyAfter 속성에 설정된 행 수에 도달할 때마다 이벤트가 발생합니다. 이벤트 인수 개체에는 두 가지 중요한 속성이 있습니다.
  • long RowsCopied: 현재 대량 복사 작업 중에 복사된 행 수를 반환합니다.
  • bool Abort: true로 설정하면 대량 복사가 중지되고 OperationAbortedException이 발생합니다.

Methods

• void Close(): SqlBulkCopy 인스턴스를 닫습니다.
• void WriteToServer(DataRow[] rows): 데이터 행 배열의 모든 복사본을 데이터베이스 테이블로 복사합니다.
• void WriteToServer(DataTable table): DataTable의 모든 복사본을 데이터베이스 테이블로 복사합니다.
• void WriteToServer(IDataReader reader):  IDataReader 개체의 모든 복사본을 데이터베이스 테이블로 복사합니다.
• void WriteToServer(DataTable table, DataRowState rowState): DataTable의 모든 복사본을 DataRowState와 일치하는 데이터베이스 테이블로 복사합니다.

 

 

How to…

 

만들려는 예제는 DataTable 개체를 기반으로 아래 표시된 테이블에 대량 복사를 수행하는 콘솔 응용 프로그램입니다.

 

Picture 1 - Destination table

• Visual Studio 2008을 엽니다.
• BulkCopyConsole이라는 새 콘솔 응용 프로그램을 만듭니다.
• 아직 열려 있지 않은 경우 솔루션 탐색기 창에서 Program.cs 파일을 엽니다.
• DataTable을 채우고 검색하는 GenerateBulkData라는 메서드를 만들어 보겠습니다.
• dt라는 DataTable 클래스의 인스턴스를 만듭니다.
• DataTable에 4개의 열을 추가합니다:
  • source_id: integer
  • source_name: string;
  • source_active: boolean;
  • source_date: datetime.
• 1에서 5000까지 가는 루프를 만들고 데이터 테이블에 행을 추가합니다. 코드는 다음과 유사합니다

 

/// <summary>
/// Generates a DataTable with the content
/// to be copied into the table.
/// </summary>
/// <returns>DataTable with the content to be copied to the database.</returns>
private static DataTable GenerateBulkData()
{
    DataTable dt = new DataTable();
    dt.Columns.Add(“source_id”, typeof(int));
    dt.Columns.Add(“source_name”, typeof(string));
    dt.Columns.Add(“source_active”, typeof(bool));
    dt.Columns.Add(“source_date”, typeof(DateTime));

    for (int i = 1; i <= 5000; i++)
    {
        dt.Rows.Add(i, “Item “ + i, (i % 2 == 0), DateTime.Now.AddDays(i));
    }

    return dt;
}

 

 

이 방법으로 생성된 DataTable에는 그림 1에 표시된 데이터베이스 테이블에 존재하지 않는 추가 열이 있다는 것을 눈치채셨을 것입니다. 원본 테이블과 대상 테이블 간에 열을 매핑하는 방법을 보여주기 위해 일부러 그렇게 한 것입니다. 

 

• DataTable을 매개변수로 받아들이는 InsertBulk라는 메서드를 만듭니다.
• SqlConnection 클래스의 인스턴스를 만듭니다.
• 사용 가능한 생성자 오버로드 중 하나를 사용하여 SqlBulkCopy 클래스의 인스턴스를 만듭니다. 이 예에서는 이전 단계에서 만든 SqlConnection 개체를 매개 변수로 전달합니다.
• DestinationTableName을 dbo.bulk_table로 설정하여 대상 테이블 이름을 정의합니다.
• BatchSize 속성을 100으로 설정하여 버퍼 크기를 지정합니다. 이 값을 변경하면 성능에 영향을 미칩니다. 귀하의 작업에 가장 적합한 가치를 찾기 위해 몇 가지 테스트를 하십시오.
• 이 예에서 우리는 대량 프로세스 동안 때때로 알림을 받을 것입니다. 이를 수행하려면 두 가지 작업을 수행해야 합니다.
  • 알림 이벤트가 발생하는 빈도를 나타내는 행 수를 기반으로 간격을 정의합니다. NotifyAfter 속성을 설정하여 이 간격을 정의합니다. 이 예에서는 10으로 설정하고 있습니다.
  • 복사가 NotifyAfter 속성에 정의된 수에 도달할 때마다 이벤트가 발생하도록 SqlRowsCopied 이벤트에 대한 이벤트 처리기를 만듭니다.
• 대상 테이블과 대상 테이블이 이름과 순서가 같은 동일한 열 구조를 가지고 있는 경우 이 단계를 건너뛸 수 있지만 항상 두 테이블의 열 간에 맵을 생성하는 것이 좋습니다. SqlBulkCopy 개체에는 대상 테이블과 대상 테이블의 열 간의 매핑 컬렉션인 매핑이라는 속성이 있습니다. Add 메서드에 사용할 수 있는 5가지 오버로드 중 하나를 사용하여 컬렉션에 새 항목을 추가할 수 있습니다.
  • SqlBulkCopyColumnMapping Add(SqlBulkCopyColumnMapping bulkCopyColumnMapping);
  • SqlBulkCopyColumnMapping Add(int sourceColumnIndex, int destinationColumnIndex);
  • SqlBulkCopyColumnMapping Add(int sourceColumnIndex, string destinationColumn);
  • SqlBulkCopyColumnMapping Add(string sourceColumn, int destinationColumnIndex);
  • SqlBulkCopyColumnMapping Add(string sourceColumn, string destinationColumn);
• 이 예제에서는 열 이름을 포함하는 두 개의 문자열을 허용하는 마지막 오버로드를 사용하겠습니다. ID, 이름 및 활성 열을 나타내는 세 가지 매핑 개체를 컬렉션에 추가해야 합니다. Date 속성을 무시하도록 정의합니다.
• 연결을 엽니다.
• WriteToServer 메서드 오버로드 중 하나를 호출하여 복사를 시작합니다. 이 예에서는 메서드가 매개 변수로 받는 DataTable 개체를 전달합니다.
• SqlBulkCopy 인스턴스를 닫습니다.
• 연결을 닫습니다.

 

 

/// <summary>
/// Copies all rows from the
/// DataTable to the database table.
/// </summary>
/// <param name=”dt”>Source table.</param>
private static void InsertBulk(DataTable dt)
{
    /// Creates an instance of the SqlConnection object.
    using (SqlConnection conn = new SqlConnection(@”Data Source=SERVERNAME;Initial Catalog=DBNAME;Integrated Security=true;”))
    {
        /// Creates an instance of the SqlBulkCopy object.
        using (SqlBulkCopy bulk = new SqlBulkCopy(conn))
        {
            /// Specifies the destination table.
            bulk.DestinationTableName = “dbo.bulk_table”;

            /// Defines the number of rows to send to the server each time.
            /// Main property to define performance.
            bulk.BatchSize = 100;

            /// Defines the number of rows to be processed
            /// before raising a notification event.
            bulk.NotifyAfter = 10;

            /// Adds an event handler that will be raised when the
            /// number of rows processed reach the number specified
            /// by the NotifyAfter property above.
            bulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulk_SqlRowsCopied);

            /// Generates the mapping between the
            /// source destination tables.
            bulk.ColumnMappings.Add(“source_id”, “id”);
            bulk.ColumnMappings.Add(“source_name”, “name”);
            bulk.ColumnMappings.Add(“source_active”, “active”);

            /// Opens connection.
            conn.Open();

            /// Starts the bulk copy process.
            bulk.WriteToServer(dt);

            /// Closes the bulk copy instance.
            bulk.Close();

            /// Closes the database connection.
            conn.Close();
        }
    }
}

/// <summary>
/// SqlRowsCopied event handler.
/// </summary>
/// <param name=”sender”>Sender object.</param>
/// <param name=”e”>Event arguments.</param>
private static void bulk_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
    Console.WriteLine(“[{0}] {1} have been copied to the database table.”, 
    	DateTime.Now, e.RowsCopied);
}

발췌 자료

 

https://ittecture.wordpress.com/2009/04/06/tip-of-the-day-174-sqlbulkcopy-class/

Tip of the day #174 – SqlBulkCopy class