【转】sqlbulkcopy 大批量数据插入神器

public static void SqlBulkCopyDemo()

        {

            String connStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();

            //从数据库中获得表结构和数据

            DataTable student = new DataTable();

            SqlDataAdapter adapter = new SqlDataAdapter("select * from student", connStr);

            adapter.Fill(student);

            //向表中添加数据            DataRow dr;

            Random r = new Random();

            Stopwatch st = new Stopwatch();

            for(int i = 0; i<1000000; i++)//一百万条数据            {//数据表中的列：name，no，age，sex

                dr = student.NewRow();

                dr[0] = "小花" + i;

                dr[1] = 108 + i;

                dr[2] = r.Next(9, 40);//这里是因为数据表中有约束，

                dr[3] = (i % 2 == 0 ? "男" : "女");//这个也是有约束

                student.Rows.Add(dr);

            }

            SqlBulkCopy bulk = new SqlBulkCopy(connStr);

            bulk.DestinationTableName = "student";//设置目标表，这里是数据库中的student表

            bulk.ColumnMappings.Add(0, 0);//建立映射关系

            bulk.ColumnMappings.Add(1, 1);

            bulk.ColumnMappings.Add(2, 2);

            bulk.ColumnMappings.Add(3, 3);

            st.Start();//开始计时

            bulk.WriteToServer(student.GetChanges());

            st.Stop();//结束计时

            Console.WriteLine("数据插入成功，耗时为：" + st.ElapsedMilliseconds + "毫秒");

        }