使用C＃.NET将批量数据从SQL插入Oracle

本文介绍了使用C＃.NET将批量数据从SQL插入Oracle的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

HI，

以下代码片段用于将数据从SQL DB批量插入Oracle数据库。

The following code snippet for for bulk insert of data from SQL DB into Oracle database.

I在oracle数据库中有用户存储过程有2个参数（int和string）

I have user storedprocedure in oracle database with 2 parameters (int and string)

我在执行查询时遇到以下错误。请帮我解决这个问题。或建议批量数据插入的任何好的解决方案。

I am getting the below error while executing the query. Please help me to resolve this. or suggest any good solution for bulk data insert.

我的查询：

&NBSP; &NBSP; &NBSP;列表与LT; INT> arrPersonId = new List< int>（）;

&NBSP; &NBSP; &NBSP; foreach（数据行在ds.Tables [0]。行中行）

&NBSP; &NBSP; &NBSP; {

&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; arrPersonId.Add（Convert.ToInt32（row [" USER_ID"]））;
$

&NBSP; &NBSP; &NBSP; }

&NBSP; &NBSP; &NBSP;列表与LT;串GT; arrPersonName = new List< string>（）;

&NBSP; &NBSP; &NBSP; foreach（数据行在ds.Tables [0]。行中行）

&NBSP; &NBSP; &NBSP; {

&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; arrPersonName.Add（row [" USERNAME"]。ToString（））;

&NBSP; &NBSP; &NBSP; }

&NBSP; &NBSP; &NBSP; OracleConnection连接=新的OracleConnection（）;

&NBSP; &NBSP; &NBSP; connection.ConnectionString = DAKObj.GetOraConnectionString（）;

&NBSP; &NBSP; &NBSP; OracleCommand command = new OracleCommand（）;

&NBSP; &NBSP; &NBSP; command.Connection = connection;

List<int> arrPersonId = new List<int>();

foreach (DataRow row in ds.Tables[0].Rows)
{
arrPersonId.Add(Convert.ToInt32(row["USER_ID"]));

}

List<string> arrPersonName = new List<string>();

foreach (DataRow row in ds.Tables[0].Rows)
{
arrPersonName.Add(row["USERNAME"].ToString());
}

OracleConnection connection = new OracleConnection();
connection.ConnectionString = DAKObj.GetOraConnectionString();

OracleCommand command = new OracleCommand();
command.Connection = connection;

&NBSP; &NBSP; &NBSP; command.CommandType = CommandType.StoredProcedure;

&NBSP; &NBSP; &NBSP; command.CommandText =" sp_InsertByODPNET";

command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_InsertByODPNET";

&NBSP; &NBSP; command.ArrayBindCount = arrPersonId.Count;

command.ArrayBindCount = arrPersonId.Count;

谢谢，

Salman

推荐答案

根据您的描述，您似乎希望将列表传递给oracle存储过程。请尝试像这样修改你的代码：

Based on your description, it seems that you want to pass a list to oracle store procedure. please try to modify your code like this:

command.CommandType = CommandType.StoredProcedure;
         command.CommandText = "sp_InsertByODPNET";
        command.Parameters.Add("@PersonId", OracleDbType.Int32);
         command.Parameters[0].Value = arrPersonId;
         command.Parameters.Add("@PersonName", OracleDbType.Varchar2, 100);
         command.Parameters[1].Value = arrPersonName.ToArray();
       command.ArrayBindCount = arrPersonId.Count;

欲了解更多信息，请参阅：

For more information, please refer to:

http://stackoverflow.com/questions/9700173 / odp-net-how-to-pass-array-of-strings-to-an-oracle-stored-procedure

祝你好运，

Cole Wu

这篇关于使用C＃.NET将批量数据从SQL插入Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！