在 IBatisNet 没有 IBatis4Java 的 startBatch() 函数,这让我们批量插入大量数据时很受困扰。本文介绍了如何在 IBatisNet 和 ADO.Net 中批量插入数据。
说到批量插入,我们有以下解决方案:
1) 直接执行用 SqlCommand 执行 INSERT 语句,一条一条的插入。无疑,这样效率最低。
2) 把拼接INSERT 语句,一次插入多条;这样性能不好说,最大的问题就是 SqlCommand 有参数限制(2100个)。
3) 利用 SqlBulkCopy 或SqlDataAdapter 的 Update 方法,可以实现批量的更新/插入,但是数据是基于 DataRow 的,并且最终 DataRow 还是要转换为 SqlCommand,对于 IBatisNet Mapping,显然我们不想这样做。
最好能怎么样呢?
我的思路就是批量执行 SqlCommand,因为 IBatisNet 最终也需要转化 statement 为 DbCommand,因此只要得到把 statement 和 parameters 混合以后的 SqlCommand,就可以了。
首先,我们要能够批量执行 SqlCommand,这里是直接反射调用 SqlDataAdapter 的批处理函数实现的,这些方法受保护,所以只能反射调用。
代码如下:
/**/
/**
<code>
<revsion>$Rev: 34 $</revision>
<owner name="Zealic" mail="rszealic@gmail.com" />
</code>
**/
using
System;
using
System.Collections.Generic;
using
System.Data;
using
System.Data.SqlClient;
using
System.Reflection;
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
namespace
YourApplicationOrLibrary.Data
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 为 Sql Server 提供批量处理操作。
/// </summary>
public class SqlBatcher
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
private MethodInfo m_AddToBatch;
private MethodInfo m_ClearBatch;
private MethodInfo m_InitializeBatching;
private MethodInfo m_ExecuteBatch;
private SqlDataAdapter m_Adapter;
private bool _Started;
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 构造一个新的 SqlBatcher。
/// </summary>
public SqlBatcher()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Type type = typeof(SqlDataAdapter);
m_AddToBatch = type.GetMethod("AddToBatch", BindingFlags.NonPublic | BindingFlags.Instance);
m_ClearBatch = type.GetMethod("ClearBatch", BindingFlags.NonPublic | BindingFlags.Instance);
m_InitializeBatching = type.GetMethod("InitializeBatching", BindingFlags.NonPublic | BindingFlags.Instance);
m_ExecuteBatch = type.GetMethod("ExecuteBatch", BindingFlags.NonPublic | BindingFlags.Instance);
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 获得批处理是否正在批处理状态。
/// </summary>
public bool Started
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
get
{ return _Started; }
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 开始批处理。
/// </summary>
/// <param name="connection">连接。</param>
public void StartBatch(SqlConnection connection)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (_Started) return;
SqlCommand command = new SqlCommand();
command.Connection = connection;
m_Adapter = new SqlDataAdapter();
m_Adapter.InsertCommand = command;
m_InitializeBatching.Invoke(m_Adapter, null);
_Started = true;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 添加批命令。
/// </summary>
/// <param name="command">命令</param>
public void AddToBatch(IDbCommand command)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!_Started) throw new InvalidOperationException();
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
m_AddToBatch.Invoke(m_Adapter, new object[1]
{ command });
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 执行批处理。
/// </summary>
/// <returns>影响的数据行数。</returns>
public int ExecuteBatch()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!_Started) throw new InvalidOperationException();
return (int)m_ExecuteBatch.Invoke(m_Adapter, null);
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 结束批处理。
/// </summary>
public void EndBatch()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (_Started)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
ClearBatch();
m_Adapter.Dispose();
m_Adapter = null;
_Started = false;
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 清空保存的批命令。
/// </summary>
public void ClearBatch()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!_Started) throw new InvalidOperationException();
m_ClearBatch.Invoke(m_Adapter, null);
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
}
}
然后是我们需要一个批量插入的模板 statement
<
insert
id
="SaveArray"
parameterClass
="TelnetRecord"
>
INSERT INTO [TelnetRecord]([state],[file_name]) VALUES(#State#,#FileName#)
</
insert
>
最后是重点,转化 statement 和 parameterObject 为 SqlCommand 并加入批处理操作执行。
public
class
TelnetRecordDao
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//由于IBatisNet 用 DbCommandDecorator 包装了 DbCommand,因此必须使用反射取得该字段
private static readonly FieldInfo m_InnerCommandField = typeof(IBatisNet.DataMapper.Commands.DbCommandDecorator).GetField("_innerDbCommand", BindingFlags.Instance | BindingFlags.NonPublic);
private SqlBatcher m_Batcher = new SqlBatcher();
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
// 转化 IBatis 包装后的 DbCommand 为 原始的 DbCommand
private IDbCommand GetCommand(IDbCommand ibatCommand)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return (IDbCommand)m_InnerCommandField.GetValue(ibatCommand);
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
// 批量保存
public int Save(TelnetRecord[] recordArray)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (recordArray == null)
throw new ArgumentNullException("recordArray");
if (recordArray.Length < 1)
throw new ArgumentException("recordArray");
ISqlMapper mapper = Mapper.Instance();
ISqlMapSession session = mapper.LocalSession;
IMappedStatement mappedStatment = mapper.GetMappedStatement("SaveArray");
IStatement st = mappedStatment.Statement;
IPreparedCommand pc = mappedStatment.PreparedCommand;
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
// 执行批处理命令
m_Batcher.StartBatch(session.Connection as SqlConnection);
RequestScope request = st.Sql.GetRequestScope(mappedStatment, recordArray[0], session);
foreach (TelnetRecord record in recordArray)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
pc.Create(request, session, st, record);
m_Batcher.AddToBatch(GetCommand(request.IDbCommand));
}
session.OpenConnection();
int ret = m_Batcher.ExecuteBatch();
m_Batcher.EndBatch();
return ret;
}
}
经测试,在我的老牛车上速度良好,插入三万条14个字段的记录只需要几秒。
即使不需要使用 IBatisNet,你也也可以使用 SqlBatcher 完成常规的批量的命令执行。
搜了下 cnblogs 和 Google ,没有发现 IBatisNet 对于这个问题的解决方法,于是就自己解决并共享,希望对各位有用。