SqlBulkCopy 极速插入数据

 

 SqlBulkCopy sqlBulkCopy =null; //声明SqlBulkCopy对象

            try

            {

                DbHelper db =new DbHelper();                                                                                   //微软DbHelper类

                using (SqlConnection sqlCon = (SqlConnection)DbHelper.CreateConnection())

                {

                    sqlBulkCopy =new SqlBulkCopy(sqlCon);                                                                 //实例化SqlBulkCopy对象

                    sqlBulkCopy.BulkCopyTimeout =180;                                                                     //180秒执行超时

 

 

                    string strSql ="select * from Invoice where 1 = 2";                                              //获取Invoice表结构 的SQL语句 不读取表中的数据

                    DbCommand cmd = db.GetSqlStringCommond(strSql);

                    DataTable dt = db.ExecuteDataTable(cmd);                                                         //得到只有表结构的数据表Datetable

 

                    for (long i = startSb; i <= endSn; i++)                                                                //向表中添加数据

                    {

                        DataRow drNew = dt.NewRow();

                        drNew["InvoiceID"] = i;

                        drNew["Price"] = price;

                        drNew["Manager"] = workerID;

                        drNew["IsHandwritten"] = IsHandwritten.ToString().ToLower();

                        dt.Rows.Add(drNew);

                    }

                    sqlCon.Open();                                                                                                //打开数据库连接

                    sqlBulkCopy.DestinationTableName ="Invoice";                                                  //指定要将数据填入的数据库中的物理表名

                    sqlBulkCopy.WriteToServer(dt);                                                                        //将数据表写入数据库指定的表中
                                            sqlCon.Close();                                                                       //关闭连接

                }

            }

            catch (SqlException ex)

            {

                Console.WriteLine(ex.Message);
　　　　　  //throrw

            }

            finally

            {

                if(sqlBulkCopy !=null)

                    sqlBulkCopy.Close();                                                                                //关闭

            }