SqlBulkCopy 클래스

데이터가 있는 SQL Server 테이블을 다른 소스에서 대량으로 로드하는 작업을 효율적으로 수행할 수 있습니다.

네임스페이스:  System.Data.SqlClient
어셈블리:  System.Data(System.Data.dll)

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


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

                    {

                        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;";

    }

}

SqlTransaction SqlTran = null;

 

 

try

{

SqlTran = conn.BeginTransaction();
SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, SqlTran);

               
foreach (DataTable DTable in DSet.Tables)
{
    sbc.DestinationTableName = DTable.TableName;
    sbc.WriteToServer(DTable);
}                               

 

SqlTran.Commit();   

}

catch

{

if (SqlTran != null)

SqlTran.Rollback();

}

 

여기서 conn은 디비 커넥션 입니다.