关于批量导入数据类SqlBulkCopy 导入数据时增加额外的列

TianMaiCheng 2012-06-05 11:05:10

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Diagnostics;

using System.Data.SqlClient;

using System.Data;



namespace 样本采购订单

{

    class Program

    {

        static void Main(string[] args)

        {

            Stopwatch st = new Stopwatch();

            st.Start();



            string connectionString = @"Data Source=192.168.0.20;Initial Catalog=misdb;User ID=sa;Password=123456;Replication=True";



            string SqlConnectionStringTarget = "Data Source=192.168.0.20;Initial Catalog=Basedb;User ID=sa;Password=123456;Replication=True";

            //DataTable sourceData = new DataTable();



            // 源 

            using (SqlConnection sourceConnection = new SqlConnection(connectionString))

            {



                //源表查询数据

                SqlCommand myCommand = new SqlCommand(" select * from ReceDetail ", sourceConnection);

                sourceConnection.Open();

                SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

                //sourceData.Load(reader);

                //sourceConnection.Close();

                // 目的





                /*

                 * 这里用的是同一个连接，

                 * 

                 * 如果不是同一个数据库，则用不同的连接

                 * 

                 * 注意：一定要按照我的连接字符串的格式来写，不然是不行的

                 * 

                 * */

                using (SqlConnection Target = new SqlConnection(SqlConnectionStringTarget))

                {

                    // 打开连接

                    //Target.Open();



                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Target.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction))

                    {



                        //源列字段：A，目标数据源字段：B

                        SqlBulkCopyColumnMapping m1 = new SqlBulkCopyColumnMapping("ShopCode", "ShopName");

                        SqlBulkCopyColumnMapping m2 = new SqlBulkCopyColumnMapping("ReceNo", "ReceNo");

                        SqlBulkCopyColumnMapping m4 = new SqlBulkCopyColumnMapping("TranDate", "TranDate");

                        SqlBulkCopyColumnMapping m6 = new SqlBulkCopyColumnMapping("RealAmount", "RealAmount");

                        SqlBulkCopyColumnMapping m7 = new SqlBulkCopyColumnMapping("DiscountType", "DiscountType");

                        SqlBulkCopyColumnMapping m8 = new SqlBulkCopyColumnMapping("Discount", "Discount");

                        SqlBulkCopyColumnMapping m9 = new SqlBulkCopyColumnMapping("ReceType", "ReceType");

                        SqlBulkCopyColumnMapping m10 = new SqlBulkCopyColumnMapping("AccountDiff", "AccountName");

                        SqlBulkCopyColumnMapping m12 = new SqlBulkCopyColumnMapping("CheckNo", "CheckNo");

                        SqlBulkCopyColumnMapping m13 = new SqlBulkCopyColumnMapping("Comment", "Comment");

                        SqlBulkCopyColumnMapping m16 = new SqlBulkCopyColumnMapping("Operator", "CreatePerson");

                        SqlBulkCopyColumnMapping m17 = new SqlBulkCopyColumnMapping("EnableFlag", "DeleteFlag");

                        SqlBulkCopyColumnMapping m18 = new SqlBulkCopyColumnMapping("CreateDate", "CreateTime");





                        bulkCopy.ColumnMappings.Add(m1);

                        bulkCopy.ColumnMappings.Add(m2);

                        bulkCopy.ColumnMappings.Add(m4);

                        bulkCopy.ColumnMappings.Add(m6);

                        bulkCopy.ColumnMappings.Add(m7);

                        bulkCopy.ColumnMappings.Add(m8);

                        bulkCopy.ColumnMappings.Add(m9);

                        bulkCopy.ColumnMappings.Add(m10);

                        bulkCopy.ColumnMappings.Add(m12);

                        bulkCopy.ColumnMappings.Add(m13);

                        bulkCopy.ColumnMappings.Add(m16);

                        bulkCopy.ColumnMappings.Add(m17);

                        bulkCopy.ColumnMappings.Add(m18);

                        //有多列时类推：



                        /**

                         *  SqlBulkCopyColumnMapping m1 = new SqlBulkCopyColumnMapping("A","B");

                         *  bulkCopy.ColumnMappings.Add(m1);

                         * 

                         * */





                        //目标数据表

                        bulkCopy.DestinationTableName = "Fina_ReceDetail";





                        //每次更新五条

                        bulkCopy.BatchSize = 5;





                        //每更新十条的时候就发出通知

                        bulkCopy.NotifyAfter = 10;





                        //下面的函数随时都在监督着这里

                        bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);

                        bulkCopy.WriteToServer(reader);

                    }

                    Target.Close();

                }

                sourceConnection.Close();

                reader.Close();

            }



            Console.WriteLine("这次数据导入共用了：" + st.ElapsedMilliseconds);

            st.Stop();

        }



        static void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)

        {

            Console.WriteLine("已经有" + e.RowsCopied + "条数据被导入");

        }

    }

}