SQLServer极速批量插入数据的方式
public bool Inserts(ObservableCollection<CustomerModel> models)
{
DataTable dataTable = SQLHelper.ExecteAdapter("select top 0 * from Customer", CommandType.Text, null);
foreach (var m in models)
{
DataRow dataRow = dataTable.NewRow();
dataRow[1] = m.Name;
dataRow[2] = m.Add;
dataRow[3] = m.Email;
dataRow[4] = m.Phone;
dataTable.Rows.Add(dataRow);
}
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(SQLHelper.ConnString);
sqlBulkCopy.DestinationTableName = "Customer";
try
{
if (dataTable != null && dataTable.Rows.Count > 0)
{
sqlBulkCopy.WriteToServer(dataTable);
}
return true;
}
catch (Exception ex)
{
return false;
}
finally
{
sqlBulkCopy.Close();
}
}
{
DataTable dataTable = SQLHelper.ExecteAdapter("select top 0 * from Customer", CommandType.Text, null);
foreach (var m in models)
{
DataRow dataRow = dataTable.NewRow();
dataRow[1] = m.Name;
dataRow[2] = m.Add;
dataRow[3] = m.Email;
dataRow[4] = m.Phone;
dataTable.Rows.Add(dataRow);
}
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(SQLHelper.ConnString);
sqlBulkCopy.DestinationTableName = "Customer";
try
{
if (dataTable != null && dataTable.Rows.Count > 0)
{
sqlBulkCopy.WriteToServer(dataTable);
}
return true;
}
catch (Exception ex)
{
return false;
}
finally
{
sqlBulkCopy.Close();
}
}
public static DataTable ExecteAdapter(string sql, CommandType type, params SqlParameter[] param)
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, ConnString))
{
SqlCommand cmd = adapter.SelectCommand;
if (param != null)
{
cmd.Parameters.AddRange(param);
}
cmd.CommandType = type;
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, ConnString))
{
SqlCommand cmd = adapter.SelectCommand;
if (param != null)
{
cmd.Parameters.AddRange(param);
}
cmd.CommandType = type;
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
注明:插入5000条数据时,使用SQLHelper方式需要约35秒,使用Entity Framework方式需要约55秒,采用SqlBulkCopy方式需要约2秒,真是光速啊,爽。