- #region 使用DataAdapter
- public static bool ExecuteDataAdapterInsert(DataTable dt, int batchSize)
- {
- int count = dt.Rows.Count;
- bool flag = false;
- try
- {
- SqlConnection cn = new SqlConnection(connectionString);
- SqlCommand cmd = new SqlCommand("Insert into TestTable(Id, Name) Values(@Id, @Name)", cn);
- cmd.Parameters.Add("@Id", SqlDbType.Int, 4, "Id");
- cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 20, "Name");
- cmd.UpdatedRowSource = UpdateRowSource.None;
- SqlDataAdapter da = new SqlDataAdapter();
- da.InsertCommand = cmd;
- da.UpdateBatchSize = batchSize;
- cn.Open();
- da.Update(dt);
- cn.Close();
- flag = true;
- }
- catch (Exception ex)
- {
- LogHelper.Error(ex.Message);
- return false;
- }
- return flag;
- }
- #endregion
结果如下:
Use SqlServer DataAdapter Insert;RecordCount:40000;BatchSize:10;Time:21569;
Use SqlServer DataAdapter Insert;RecordCount:40000;BatchSize:20;Time:19607;
Use SqlServer DataAdapter Insert;RecordCount:40000;BatchSize:50;Time:19647;
Use SqlServer DataAdapter Insert;RecordCount:40000;BatchSize:100;Time:18649;
Use SqlServer DataAdapter Insert;RecordCount:40000;BatchSize:200;Time:21823;
测试的结果让我比较失望,SqlDataAdapter会为每个修改行发送一条T-SQL命令,所以效率并不高。
而且再其他的方式中,都可以进行异步处理,SqlDataAdapter的Update()方法并不支持异步的结果,所以此方法不推荐