locked
Insert multiple rows into Oracle from .Net RRS feed

  • Question

  • User1150900820 posted

    Hello:

    I've an ArrayList which contains bulk records and I want to insert all of those in a single statement (or Bulk insert). I know it can be done if I use Oracle.DataAccess (it supports bulk insert). But I'm not gonna use it rather use a single insert statement like below:

    StringBuilder strSQL = new StringBuilder();
                        conn.Open();
                        com.Connection = conn;
    
                        strSQL.Append("INSERT ALL");
                        strSQL.Append(" INTO TEST "
                                                + " VALUES('"
                                                + "ID"
                                                + "','" + "Person"
                                                + "','" + "Location"                                           
                                                + "') "
                                                );
                        strSQL.Append(" INTO TEST "
                                                + " VALUES('"
                                                + newItems[0].ToString()
                                                + "','" + newItems[1].ToString()
                                                + "','" + newItems[2].ToString()                                           
                                                + "') "
                                                );
    
    
                        com.CommandText = strSQL.ToString();
                        com.ExecuteNonQuery();

    The above is generating error. Can anyone help me to figure it out.

    Thanks/Razin

    Thursday, January 5, 2012 9:04 AM

Answers

  • User269602965 posted
    Also, INSERT ALL requires a SELECT Statement, but since you are inserting into one table SELECT * FROM DUAL should work.
    Imports System.Xml.Linq.XElement 
     
    Public Shared Sub InsertIntoTest(ByVal strID As String, ByVal strPerson As String, ByVal strLocation As String) 
      Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("{YourOraConnStrName}").ConnectionString 
      Try 
        Dim SQL = 
        <SQL> 
          INSERT ALL 
          INTO {YourSchemaName}.TEST
            VALUES ('ID','Person','Location')
          INTO {YourSchemaName}.TEST
            VALUES (:strID, :strPerson, :strLocation)
          SELECT * FROM DUAL
        </SQL> 
        Using conn As New OracleConnection(OraConnStr) 
          Using cmd As New OracleCommand(SQL.Value, conn) 
            cmd.Parameters.Clear() 
            cmd.Parameters.Add("strID", OracleDbType.Varchar2, strID, ParameterDirection.Input) 
            cmd.Parameters.Add("strPerson", OracleDbType.Varchar2, strPerson, ParameterDirection.Input) 
            cmd.Parameters.Add("strLocation", OracleDbType.Varchar2, strLocation, ParameterDirection.Input) 
            conn.Open() 
            cmd.ExecuteNonQuery() 
          End Using 
        End Using 
      Catch ex As Exception 
      End Try 
    End Sub
     
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 6, 2012 6:12 PM

All replies

  • User269602965 posted

    Single row insert

    1. get rid of all the punctuation in string builder, a easy source of errors, and replace with creating SQL inside XML tag and passing that value.

    2. Pass your variables as BIND VARIABLES and in ODP.NET you do that with named parameters

    VB example:

    Imports System.Xml.Linq.XElement
    
    Public Shared Sub InsertIntoTest(ByVal strID As String, ByVal strPerson As String, ByVal strLocation As String)
      Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("{YourOraConnStrName}").ConnectionString
      Try
        Dim SQL =
        <SQL>
          INSERT INTO {YourSchemaName}.TEST
           (ID, Person, Location)
          VALUES
           (:strID, :strPerson, :strLocation)
        </SQL>
        Using conn As New OracleConnection(OraConnStr)
          Using cmd As New OracleCommand(SQL.Value, conn)
            cmd.Parameters.Clear()
            cmd.Parameters.Add("strID", OracleDbType.Varchar2, strID, ParameterDirection.Input)
            cmd.Parameters.Add("strPerson", OracleDbType.Varchar2, strPerson, ParameterDirection.Input)
            cmd.Parameters.Add("strLocation", OracleDbType.Varchar2, strLocation, ParameterDirection.Input)
            conn.Open()
            cmd.ExecuteNonQuery()
          End Using
        End Using
      Catch ex As Exception
      End Try
    End Sub
    Thursday, January 5, 2012 9:33 PM
  • User1150900820 posted

    Hi Lannie:

    I said I'm not using ODP .NET (Oracle.DataAccess). And I want to insert multiple rows together to reduce network overhead. I think you've not read my post properly.

    Thanks/Tanvir 

    Friday, January 6, 2012 8:57 AM
  • User269602965 posted

    If you want the best performance and capabilities, use Oracle Data Access

    ODAC supports BINDING to ARRAYS for inserts as well.

     

    Friday, January 6, 2012 3:15 PM
  • User269602965 posted
    Also, INSERT ALL requires a SELECT Statement, but since you are inserting into one table SELECT * FROM DUAL should work.
    Imports System.Xml.Linq.XElement 
     
    Public Shared Sub InsertIntoTest(ByVal strID As String, ByVal strPerson As String, ByVal strLocation As String) 
      Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("{YourOraConnStrName}").ConnectionString 
      Try 
        Dim SQL = 
        <SQL> 
          INSERT ALL 
          INTO {YourSchemaName}.TEST
            VALUES ('ID','Person','Location')
          INTO {YourSchemaName}.TEST
            VALUES (:strID, :strPerson, :strLocation)
          SELECT * FROM DUAL
        </SQL> 
        Using conn As New OracleConnection(OraConnStr) 
          Using cmd As New OracleCommand(SQL.Value, conn) 
            cmd.Parameters.Clear() 
            cmd.Parameters.Add("strID", OracleDbType.Varchar2, strID, ParameterDirection.Input) 
            cmd.Parameters.Add("strPerson", OracleDbType.Varchar2, strPerson, ParameterDirection.Input) 
            cmd.Parameters.Add("strLocation", OracleDbType.Varchar2, strLocation, ParameterDirection.Input) 
            conn.Open() 
            cmd.ExecuteNonQuery() 
          End Using 
        End Using 
      Catch ex As Exception 
      End Try 
    End Sub
     
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 6, 2012 6:12 PM
  • User979523123 posted

    Hi,

      I'm using the Microsoft visual studio 2010.  I cannot see the Oracle.dataaccess.dll.  Is that some thing I have to download explicitly.

    Thanks

    dds

    Thursday, November 8, 2012 9:45 AM
  • User269602965 posted

    Install Oracle ODAC (ODP.NET).  You can use the latest version, and during install, select the CLIENT install option.

    then you can transition from system.data.oracle to oracle.dataaccess.dll

    http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html

    if you have oracle database on your system, you should install the client in a new Oracle Home directory

    Thursday, November 8, 2012 12:47 PM
  • User979523123 posted

    Thanks  this helps me.

    Monday, November 12, 2012 8:40 AM