首页 新闻 赞助 找找看

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

0
悬赏园豆:10 [已关闭问题] 关闭于 2012-07-29 11:32
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 + "条数据被导入");
        }
    }
}

我现在有个问题,第一个表导入第二个表的时候,第二个表有一些额外的数据字段,这些数据字段的数据都是按规则生成的!!(比如:现在时间的毫秒数+ReceiveOrder+自动增长列+现在登录的人的姓名)等这样的规则,那如何在导入数据的时候自动增加呢!!

需要格局的主页 需要格局 | 老鸟四级 | 园豆:2145
提问于:2012-06-05 10:00
< >
分享
所有回答(1)
0

最简便的办法  BulkCopy 数据到另一个临时数据表, 然后c# 调用 一个存储过程 把 临时表的数据加入到你的目标表里, 

存储过程中可以加入任意的 计算过的数据字段

gunsmoke | 园豆:3592 (老鸟四级) | 2012-07-12 07:46
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册