oracle 为什么这个oracle批量插入不起作用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4431208/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does this oracle bulk insert not work?
提问by mrt181
I am trying to bulk insert some data into an oracle db. I followed the example in the documentation.
我正在尝试将一些数据批量插入到 oracle 数据库中。我按照文档中的示例进行操作。
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.
好吧，它不起作用。没有在数据库中插入任何内容，我没有收到任何错误消息。
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).
当我不使用批量插入时，一切正常（相反，我对 DataTable 中的每一行进行 foreach 循环，并在每次迭代时将 DataRow 插入到数据库中）。
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
};
I get this error:
我收到此错误：
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
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()
at Gateway.DataGateway.Import(String connString, Product product) in \path\share$\Visual Studio 2010\Projects\ImportData-trunk\Gateway\DataGateway.Sql.cs:line 196
UPDATE2: The ODP.NET driver is stupid (just does not work as i exprected ;)
UPDATE2：ODP.NET 驱动程序很愚蠢（只是不像我预期的那样工作；）
this does not work
这不起作用
var names = new List<string>();
it has to be this
必须是这个
var names = new string[table.Rows.Count];
采纳答案by mrt181
I had to us the ToArray() method on the List.
我不得不在列表中使用 ToArray() 方法。
insertCmd.Parameters.Add(":name", OracleDbType.Varchar2, names.ToArray(), ParameterDirection.Input);
回答by LBushkin
You need to set the parameter CollectionTypeproperty to OracleCollectionType.PLSQLAssociativeArrayto get bulk operations to work.
您需要将参数CollectionType属性设置OracleCollectionType.PLSQLAssociativeArray为使批量操作生效。
Since there's no Add()method that allows you to specificy this, you would have to add the following line after the call to Parameters.Add():
由于没有Add()方法可以让您具体说明这一点，因此您必须在调用后添加以下行Parameters.Add()：
insertCmd.Parameters[0].CollectionType = OracleCollectionType.PLSQLAssociativeArray
回答by R Muruganandhan
private void BulkCopy(List<test_bulk> lsttest_bulk)
{
try
{
//ConnectionString = String.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2})));User Id={3};Password={4};", "ServerAddress", "PortAddress", "DatabaseName", "Username", "Password");
ConnectionString = String.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2})));User Id={3};Password={4};", "ServerAddress", "PortAddress", "DatabaseName", "Username", "Password");
OracleConnection oraConn = new OracleConnection(ConnectionString);
oraConn.Open();
OracleCommand oraCMD = new OracleCommand();
oraCMD.Connection = oraConn;
var oracleBulkCopy = new OracleBulkCopy(oraConn)
{
DestinationTableName = "test_bulk",
BulkCopyOptions = OracleBulkCopyOptions.UseInternalTransaction
};
DataTable oDataTable = GetDataTableFromObjects<test_bulk>(lsttest_bulk);
oracleBulkCopy.WriteToServer(oDataTable);
oracleBulkCopy.Dispose();
}
catch(Exception ex)
{
Console.WriteLine("failed to write:\t{0}", ex.Message);
}
}
public static DataTable GetDataTableFromObjects<TDataClass>(List<TDataClass> dataList)
where TDataClass : class
{
Type t = typeof(TDataClass);
DataTable dt = new DataTable(t.Name);
foreach (PropertyInfo pi in t.GetProperties())
{
dt.Columns.Add(new DataColumn(pi.Name));
}
if (dataList != null)
{
foreach (TDataClass item in dataList)
{
DataRow dr = dt.NewRow();
foreach (DataColumn dc in dt.Columns)
{
dr[dc.ColumnName] =
item.GetType().GetProperty(dc.ColumnName).GetValue(item, null);
}
dt.Rows.Add(dr);
}
}
return dt;
}
