为什么此oracle批量插入不起作用? [英] Why does this oracle bulk insert not work?

问题描述

我正在尝试将一些数据批量插入到oracle数据库中.我按照文档中的示例进行操作.

I am trying to bulk insert some data into an oracle db. I followed the example in the documentation.

this.DataBaseAccess = new OracleConnection(connString);
var dataAdapter = new OracleDataAdapter();
var insertCmd = DataBaseAccess.CreateCommand();
insertCmd.CommandType = CommandType.Text;
insertCmd.BindByName = true;

var names = new List<string>();

foreach (DataTable table in product.Contracts.Tables)
{
    foreach (DataRow row in table.Rows)
    {
        names.Add(row["Contract"].ToString());
    }

    const string InsertContracts = "merge into CONTRACT t " +
                                   "using " +
                                   "(select :name NAME from dual) s " +
                                   "on (t.NAME = s.NAME) " +
                                   "when not matched then " +
                                   "insert (t.NAME) " +
                                   "values (s.NAME)";

    insertCmd.CommandText = InsertContracts;
    insertCmd.ArrayBindCount = table.Rows.Count;
    insertCmd.Parameters.Add(":name", OracleDbType.Varchar2, names, ParameterDirection.Input);

    dataAdapter.InsertCommand = insertCmd;

    this.DataBaseAccess.Open();
    insertCmd.ExecuteNonQuery();
    this.DataBaseAccess.Close();
}

嗯，它不起作用.什么都没有插入数据库，我没有收到任何错误消息.

Well, it does not work. Nothing is inserted into the database, I don't get any error messages.

当我不使用批量插入时，一切都很好(相反，我通过DataTables中的每一行进行foreach循环，并在每次迭代时将DataRow插入数据库中.)

Everything works fine when i do not use bulk insert (instead i foreach-loop through each row from my DataTables and insert the DataRow into the Database on each iteration).

更新:我已遵循建议，并对参数进行了以下更改.

UPDATE: I have followed the suggestions and made the following changes to my parameter.

var nameParam = new OracleParameter
    {
        ParameterName = ":name",
        OracleDbType = OracleDbType.Varchar2,
        Value = names,
        Size = table.Rows.Count,
        CollectionType = OracleCollectionType.PLSQLAssociativeArray,
        Direction = ParameterDirection.Input
    }; 

我收到此错误:

System.InvalidCastException:无法将类型为"System.String"的对象转换为类型为"System.Array".
在Oracle.DataAccess.Client.OracleParameter.SetStatus(Int32 arraySize)处
在Oracle.DataAccess.Client.OracleParameter.ResetCtx(Int32 arraySize)处
在Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn，IntPtr errCtx，Int32 arraySize)
在Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
在\ path \ share $ \ Visual Studio 2010 \ Projects \ ImportData-trunk \ Gateway \ DataGateway.Sql.cs:line 196中的Gateway.DataGateway.Import(字符串connString，产品)处

System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Array'.
at Oracle.DataAccess.Client.OracleParameter.SetStatus(Int32 arraySize)
at Oracle.DataAccess.Client.OracleParameter.ResetCtx(Int32 arraySize)
at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at Gateway.DataGateway.Import(String connString, Product product) in \path\share$\Visual Studio 2010\Projects\ImportData-trunk\Gateway\DataGateway.Sql.cs:line 196

UPDATE2:ODP.NET驱动程序是愚蠢的(只是无法正常工作；)

UPDATE2: The ODP.NET driver is stupid (just does not work as i exprected ;)

这不起作用

var names = new List<string>();

必须是这个

var names = new string[table.Rows.Count];

推荐答案

我必须使用列表上的ToArray()方法.

I had to us the ToArray() method on the List.

insertCmd.Parameters.Add(":name", OracleDbType.Varchar2, names.ToArray(), ParameterDirection.Input);

这篇关于为什么此oracle批量插入不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！