.net 使用SqlBulkCopy批量插入数据库

        //一次批量插入100W条数据到数据库
        static void Main(string[] args)
        {
            System.Random a = new Random(System.DateTime.Now.Millisecond);
            Stopwatch sw = new Stopwatch();
            DataTable dt = GetTableSchema();
            for (int count = 0; count < 1000000; count++)
            {
                DataRow r = dt.NewRow();
                r[0] = Guid.NewGuid().ToString();
                r[1] = 100;
                r[2] = "ABCD";
                r[3] = a.Next().ToString();
                r[4] = count;
                r[5] = DateTime.Now;
                r[6] = false;
                dt.Rows.Add(r);
            }
            sw.Start();
            BulkToDB(dt);
            sw.Stop();
            Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
            Console.ReadLine();
        }

        public static DataTable GetTableSchema()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[]{ 
                new DataColumn("UserCode",typeof(string)), 
                new DataColumn("key1",typeof(int)), 
                new DataColumn("key2",typeof(string)),
                new DataColumn("UserName",typeof(string)), 
                new DataColumn("Age",typeof(int)), 
                new DataColumn("CreateTime",typeof(DateTime)),
                new DataColumn("IsDeleted",typeof(bool))
            });
            return dt;
        }

        public static void BulkToDB(DataTable dt)
        {
            SqlConnection sqlConn = new SqlConnection("Server=localhost;database=TestDB;User ID=sa;Password=123456;");
            SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
            bulkCopy.DestinationTableName = "Users";
            bulkCopy.BatchSize = dt.Rows.Count;
            try
            {
                sqlConn.Open();
                if (dt != null && dt.Rows.Count != 0)
                    bulkCopy.WriteToServer(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConn.Close();
                if (bulkCopy != null)
                    bulkCopy.Close();
            }
        }

转载于:https://www.cnblogs.com/weiweithe/p/4292080.html