Insert multiple rows into Oracle from .Net

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

Re: Insert multiple rows into Oracle from .Net

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

Re: Insert multiple rows into Oracle from .Net

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

Re: Insert multiple rows into Oracle from .Net

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

ODAC supports BINDING to ARRAYS for inserts as well.

Re: Insert multiple rows into Oracle from .Net

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

Re: Insert multiple rows into Oracle from .Net

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

Re: Insert multiple rows into Oracle from .Net

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

Re: Insert multiple rows into Oracle from .Net

Thanks this helps me.