none
array binding

    Question

  •  

    Hi

    I am new to VB.net windows app

    Do we have Array Binding in ADO.net like we have in ODP.net, to execute a database stored procedure, multiple times in a single database round trip?

    if there is way ,please send me a sample code

    eg..(used ODP.net)

    Imports System
    Imports System.Data
    Imports Oracle.DataAccess.Client


    Dim connectStr As String = "User Id=Scott; Password=tiger; Data Source=orcl9i"

    ' Initialize connection
    Dim connection As OracleConnection
    connection = New OracleConnection(connectStr)
    connection.Open()
     

    2. Initialize the OracleCommand object:

    Visual Basic .NET
    'STEP 2
    ' Set command to execute Test_Arraybind database stored procedure
    Dim cmd1 As OracleCommand = New OracleCommand("", connection)
    cmd1.CommandText = "Test_Arraybind"
    cmd1.CommandType = CommandType.StoredProcedure
     

    3. Initialize the array with multiple values of Deptno and Dname. The ArrayBindCount property determines the number of command executions as well as the number of array elements to bind as part of OracleCommand:

     
    Visual Basic .NET
    ' STEP 3
    ' Initialize array with data
    Dim myArrayDeptNo As Int16() = {1, 2, 3}
    Dim myArrayDeptName As String() = {"Dev", "QA", "Facility"}

    ' Set the ArrayCount for command to 3 i.e. max.' number of rows in the
    ' preceding arrays
    cmd1.ArrayBindCount = 3
     

    4. The value of deptNoParam and deptNameParam Oracle parameters are set to the array created:

     
    Visual Basic .NET
    ' STEP 4
    ' Instantiate Oracle parameter corresponding to DeptNo
    Dim deptNoParam As OracleParameter = New OracleParameter("deptno", OracleDbType.Int32)
    deptNoParam.Direction = ParameterDirection.Input

    ' Bind Array containing Department numbers "deptNoParam" Oracle Parameter
    deptNoParam.Value = myArrayDeptNo

    ' Add Oracle Parameter to Command
    cmd1.Parameters.Add(deptNoParam)


    ' Similarly bind Dept Name parameter
    Dim deptNameParam As OracleParameter = New OracleParameter("deptname",
                                                               OracleDbType.Varchar2)
    deptNameParam.Direction = ParameterDirection.Input
    deptNameParam.Value = myArrayDeptName
    cmd1.Parameters.Add(deptNameParam)
     

    5. On executing the command calling Stored Procedure, the stored procedure is called multiple times in a single database round-trip:


    Visual Basic .NET
    ' STEP 5
    ' Execute the command calling stored procedure
    Try
       cmd1.ExecuteNonQuery()
       Console.WriteLine("{0} Rows Inserted", cmd1.ArrayBindCount)

    Catch e As Exception
      Console.WriteLine("Execution Failed:" + e.Message)
    End Try
     

    6. Clean-up DeptTab table before exit from application:

     
    Visual Basic .NET
    ' Step 6
    ' Cleanup DeptTab table data
    Dim cmd2 As OracleCommand = New OracleCommand("", connection)


    ' Delete all the rows from the DeptTab table
    cmd2.CommandText = "DELETE depttab WHERE deptno = :1"

    ' Bind with an array of 3 items
    cmd2.ArrayBindCount = 3

    Dim param1 As OracleParameter = New OracleParameter()
    param1.OracleDbType = OracleDbType.Int32
    param1.Value = myArrayDeptNo
    cmd2.Parameters.Add(param1)

    ' Execute the delete statement through command
    Try

        cmd2.ExecuteNonQuery()
        Console.WriteLine("Cleaned DeptTab table data")

    Catch e As Exception
        Console.WriteLine("Cleanup Failed:{0}", e.Message)
    Finally
        ' Dispose the OracleCommand objects
        cmd1.Dispose()
        cmd2.Dispose()

        ' Close and Dispose the OracleConnection object
        connection.Close()
        connection.Dispose()

    End Try
     
    Thanks in advance

    Thursday, November 09, 2006 6:24 PM

Answers

  •  

    While array binding is supported by Oracle's .NET Data Provider (ODP.NET), it is not supported via the common provider interfaces or by most .NET Data Providers, like System.Data.SqlClient or System.Data.OracleClient.

    Monday, November 13, 2006 7:02 PM

All replies

  •  

    While array binding is supported by Oracle's .NET Data Provider (ODP.NET), it is not supported via the common provider interfaces or by most .NET Data Providers, like System.Data.SqlClient or System.Data.OracleClient.

    Monday, November 13, 2006 7:02 PM
  • Hi Dave

    Thanks for your reply

    is there any other way to pass array of value to storedprocedure(Oracle)?

    Thanks in advance

     

     

     

    Monday, November 13, 2006 7:48 PM
  • Given the behavior you're looking for, passing arrays of values into an Oracle stored procedure to execute the query on the server for each set of values, your best bet is to use ODP.NET.

    As noted earlier, System.Data.OracleClient does not support this functionality.  I'm not sure if it is available with other Oracle providers, such as the one from DataDirect.

    Wednesday, November 15, 2006 8:10 PM