Insert multiple rows into Oracle from .Net : The Official Microsoft ASP.NET Forums

Reply

thk_razin

Member

210 Points

287 Posts

Insert multiple rows into Oracle from .Net

Jan 05, 2012 01:04 PM|LINK

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

mark as answer if my response is fruitful for u

Reply

Lannie

Contributor

3724 Points

726 Posts

Re: Insert multiple rows into Oracle from .Net

Jan 06, 2012 01:33 AM|LINK

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

Reply

thk_razin

Member

210 Points

287 Posts

Re: Insert multiple rows into Oracle from .Net

Jan 06, 2012 12:57 PM|LINK

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

mark as answer if my response is fruitful for u

Reply

Lannie

Contributor

3724 Points

726 Posts

Re: Insert multiple rows into Oracle from .Net

Jan 06, 2012 07:15 PM|LINK

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

ODAC supports BINDING to ARRAYS for inserts as well.

Reply

Lannie

Contributor

3724 Points

726 Posts

Re: Insert multiple rows into Oracle from .Net

Jan 06, 2012 10:12 PM|LINK

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