救命！使用C＃批量将数据插入Oracle的问题 [英] Help! A problem in Bulk Inserting data into Oracle Using C#

问题描述

我使用以下代码批量插入Access并且它成功了！但是..（参见下一部分代码）

 DataTable dt =  new  DataTable（）; 
 
 OleDbConnection read_con =  new  OleDbConnection（ConnectionString）; 
 OleDbDataAdapter read_adp =  new  OleDbDataAdapter（  select来自OPMSIntStorage的GID，IntInfo，其中GID =''，read_con）; 
 
 read_adp.Fill（dt）; 
 
  for （ int  i =  0 ; i <   5 ; i ++）
 {
 DataRow r = dt.NewRow（）; 
 r [  GID] = i.ToString（）;  //  字符串类型OPMSIntStorage表中的列 
r [  IntInfo] = i;  //   OPMSIntStorage表中的Int Type列 
 dt.Rows.Add（r）; 
} 
 
 OleDbConnection save_con =  new  OleDbConnection（ConnectionString）; 
 OleDbDataAdapter save_adp =  new  OleDbDataAdapter（  select来自OPMSIntStorage的GID，IntInfo，其中GID =''，save_con）; 
 
 save_adp.InsertCommand =  new  OleDbCommand（ 插入OPMSIntStorage（GID，IntInfo）值（P_GID，P_IntInfo））; 
 save_adp.InsertCommand.Parameters.Add（  P_GID，OleDbType.BSTR， 255 ，  GID）; 
 save_adp.InsertCommand.Parameters.Add（  P_IntInfo，OleDbType.Integer， 4 ，  IntInfo）; 
 save_adp.InsertCommand.Connection = save_con; 
 
 save_adp.InsertCommand.Connection.Open（）; 
 save_adp.Update（dt）; 
 save_adp.InsertCommand.Connection.Close（）; 
 

我想更改代码，然后可以批量插入Oracle。我只是将OleDb替换为Oracle，将OleDbType.BSTR替换为OracleType.VarChar，将OleDbType.Integer替换为OracleType.Int32。但是发生了错误！（ORA-01036：非法变量名称/号码

）。我已经多次审查过，var名称，数量和类型都是正确的！请帮我！谢谢！

 DataTable dt =  new  DataTable（）; 
 
 OracleConnection read_con =  new  OracleConnection（ConnectionString）; 
 OracleDataAdapter read_adp =  new  OracleDataAdapter（  select来自OPMSIntStorage的GID，IntInfo，其中GID =''，read_con）; 
 
 read_adp.Fill（dt）; 
 
  for （ int  i =  0 ; i <   5 ; i ++）
 {
 DataRow r = dt.NewRow（）; 
 r [  GID] = i.ToString（）;  //  字符串类型OPMSIntStorage表中的列 
r [  IntInfo] = i;  //   OPMSIntStorage表中的Int Type列 
 dt.Rows.Add（r）; 
} 
 
 OracleConnection save_con =  new  OracleConnection（ConnectionString）; 
 OracleDataAdapter save_adp =  new  OracleDataAdapter（  select来自OPMSIntStorage的GID，IntInfo，其中GID =''，save_con）; 
 
 save_adp.InsertCommand =  new  OracleCommand（ 插入OPMSIntStorage（GID，IntInfo）值（P_GID，P_IntInfo））; 
 save_adp.InsertCommand.Parameters.Add（  P_GID，OracleType.VarChar， 255 ，  GID）; 
 save_adp.InsertCommand.Parameters.Add（  P_IntInfo，OracleType。 Int32 ， 4 ，  IntInfo）; 
 save_adp.InsertCommand.Connection = save_con; 
 
 save_adp.InsertCommand.Connection.Open（）; 
 save_adp.Update（dt）; 
 save_adp.InsertCommand.Connection.Close（）; 
 

解决方案

检查生成的语句。在Oracle中正确传递参数使用:(冒号）前缀而不是@（at-sign），如sql server。



https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleparametercollection.aspx [ ^

I use the following code to bulk insert to Access and it's successful! But..(see the next part of code)

DataTable dt = new DataTable();

OleDbConnection read_con = new OleDbConnection(ConnectionString);
OleDbDataAdapter read_adp = new OleDbDataAdapter("select GID, IntInfo From OPMSIntStorage Where GID = ''", read_con);

read_adp.Fill(dt);

for (int i = 0; i < 5; i++)
{
    DataRow r = dt.NewRow();
    r["GID"] = i.ToString();    // String Type Column in OPMSIntStorage Table
    r["IntInfo"] = i;           // Int Type Column in OPMSIntStorage Table
    dt.Rows.Add(r);
}

OleDbConnection save_con = new OleDbConnection(ConnectionString);
OleDbDataAdapter save_adp = new OleDbDataAdapter("select GID, IntInfo From OPMSIntStorage Where GID = ''", save_con);

save_adp.InsertCommand = new OleDbCommand("Insert into OPMSIntStorage (GID, IntInfo) Values (P_GID, P_IntInfo)");
save_adp.InsertCommand.Parameters.Add("P_GID", OleDbType.BSTR, 255, "GID");
save_adp.InsertCommand.Parameters.Add("P_IntInfo", OleDbType.Integer, 4, "IntInfo");
save_adp.InsertCommand.Connection = save_con;

save_adp.InsertCommand.Connection.Open();
save_adp.Update(dt);
save_adp.InsertCommand.Connection.Close();

I want to change the code and then it can bulk insert into Oracle. I simply replaced "OleDb" to "Oracle" and "OleDbType.BSTR" to "OracleType.VarChar" and "OleDbType.Integer" to "OracleType.Int32". But there was a error occur!(ORA-01036: illegal variable name/number
). I have reviewed many times, the var name and number and type are both right! Please help me! Thanks!

DataTable dt = new DataTable();

OracleConnection read_con = new OracleConnection(ConnectionString);
OracleDataAdapter read_adp = new OracleDataAdapter("select GID, IntInfo From OPMSIntStorage Where GID = ''", read_con);

read_adp.Fill(dt);

for (int i = 0; i < 5; i++)
{
    DataRow r = dt.NewRow();
    r["GID"] = i.ToString();    // String Type Column in OPMSIntStorage Table
    r["IntInfo"] = i;           // Int Type Column in OPMSIntStorage Table
    dt.Rows.Add(r);
}

OracleConnection save_con = new OracleConnection(ConnectionString);
OracleDataAdapter save_adp = new OracleDataAdapter("select GID, IntInfo From OPMSIntStorage Where GID = ''", save_con);

save_adp.InsertCommand = new OracleCommand("Insert into OPMSIntStorage (GID, IntInfo) Values (P_GID, P_IntInfo)");
save_adp.InsertCommand.Parameters.Add("P_GID", OracleType.VarChar, 255, "GID");
save_adp.InsertCommand.Parameters.Add("P_IntInfo", OracleType.Int32, 4, "IntInfo");
save_adp.InsertCommand.Connection = save_con;

save_adp.InsertCommand.Connection.Open();
save_adp.Update(dt);
save_adp.InsertCommand.Connection.Close();

解决方案

Check the generated statements. In Oracle to pass correctly the parameter use a : (Colon) prefix and not @ ( at-sign) like sql server.

https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleparametercollection.aspx[^]

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