微软封装了一个类,号称百万条数据秒插数据库,它就是SqlBulkCopy
现在让我们来看看怎么使用吧
///
/// DataTale整张表数据插入数据
///
/// 要插入的table数据
/// 目标数据表名
/// 必须提供所有的字段
///返回成功,或者失败 true or false
public static bool SqlBulkInsert(DataTable dt, string tableName,string[] fieldName)
{
using (SqlConnection conn = new SqlConnection(Conn.ConnString))
{
conn.Open();
using (SqlBulkCopy bulk = new SqlBulkCopy(Conn.ConnString))
{
try
{
//when the table data handle done
bulk.DestinationTableName = tableName;
for (int i = 0; i {
bulk.ColumnMappings.Add(fieldName[i], fieldName[i]);
}
bulk.WriteToServer(dt);
//去重
using (SqlCommand cmd = conn.CreateCommand())
{
StringBuilder temp = new StringBuilder();
temp.AppendFormat(" delete from {0} where IsDelete=1 and Name in( ", tableName);
temp.AppendFormat(" select distinct Name from {0} where IsDelete=1 group by Name having COUNT(Name)>1) and ", tableName);
temp.AppendFormat(" Id not in(select min(Id) from {0} where IsDelete=1 group by Name having COUNT(Name)>1) ", tableName);
cmd.CommandText = temp.ToString();
cmd.ExecuteNonQuery();
}
return true;
}
catch
{
return false;
}
finally
{
conn.Close();
bulk.Close();
}
}
}
}
超简单!!!
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.e1idc.net