批量插入/更新Oracle ODP.NET：高效数据操作指南

/// <summary>
///  批量插入数据
/// </summary>
/// <param name="tableName">表名称</param>
/// <param name="columnRowData">键-值存储的批量数据：键是列名称，值是该列对应的数据集合</param>
/// <returns></returns>
public int BatchInsert(string tableName, Dictionary<string,object[]> columnRowData)
{
    if (string.IsNullOrEmpty(tableName))
    {
        throw new ArgumentNullException("tableName", "必须指定批量插入的表名称");
    }
 
    if (columnRowData == null || columnRowData.Count < 1)
    {
        throw new ArgumentException("必须指定批量插入的字段名称", "columnRowData");
    }
 
    int iResult = 0;
    string[] dbColumns = columnRowData.Keys.ToArray();
    StringBuilder sbCmdText = new StringBuilder();
    if (columnRowData.Count > 0)
    {
        // 准备插入SQL
        sbCmdText.AppendFormat("INSERT INTO {1} (", tableName);
        sbCmdText.Append(string.Join(",", dbColumns));
        sbCmdText.Append(") VALUES (");
        sbCmdText.Append(":" + string.Join(", :", dbColumns));
        sbCmdText.Append(") ");
 

        using (OracleConnection conn = new OracleConnection("数据库连接字符信息"))
        {
            using (OracleCommand cmd = _oraDbConn.CreateCommand())
            {
                // 绑定批处理的行数
                cmd.ArrayBindCount = columnRowData.Values.First().Length; // 很重要
                cmd.BindByName = true;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sbCmdText.ToString();
                cmd.CommandTimeout = 600; // 10分钟
                // 创建参数
                OracleParameter oraParam;
                List<IDbDataParameter> cacher = new List<IDbDataParameter>();
                OracleDbType dbType = OracleDbType.Object;
                foreach (string colName in dbColumns)
                {
                    dbType = this.GetOracleDbType(columnRowData[colName][0]);
                    oraParam = new OracleParameter(colName, dbType);
                    oraParam.Direction = ParameterDirection.Input;
                    oraParam.OracleDbTypeEx = dbType;
 
                    oraParam.Value = columnRowData[colName];
                    cmd.Parameters.Add(oraParam);
                }
 
                // 执行批处理
                var trans  = conn.BeginTransaction();
                try
                {
                    cmd.Transaction = trans;