SqlBulkCopy 极速插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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(); //关闭
}
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(); //关闭
}