VB.NET - clear the parameters passed to an Oracle stored procedure

Question

I have a sub which inserts the content of a file into Oracle DB. For only one file, everything goes just fine but for more then one file, the procedure's parameters keeps the old values but also the new ones; so instead of 4 parameters, at 2nd file I have 8 parameters.

Here is a sample of code:

For Each oFile As String In Directory.GetFiles("D:\files")

Dim header() As String = IO.File.ReadAllLines(oFile)
If header(0) = "<delivery_note>" Then

    XML_File = XmlReader.Create(oFile, New XmlReaderSettings())
    DataSet.ReadXml(XML_File)

    For i As Integer = 0 To DataSet.Tables(0).Rows.Count - 1

        cmd.Parameters.Add("p_delnote_id", OracleDbType.Int64).Direction = ParameterDirection.Input
        cmd.Parameters("p_delnote_id").Value = Convert.ToInt32(DataSet.Tables(0).Rows(i).Item(0))

        cmd.Parameters.Add("p_order_id", OracleDbType.Int64).Direction = ParameterDirection.Input
        cmd.Parameters("p_order_id").Value = Convert.ToInt32(DataSet.Tables(0).Rows(i).Item(1))

        cmd.Parameters.Add("p_product_id", OracleDbType.Int64).Direction = ParameterDirection.Input
        cmd.Parameters("p_product_id").Value = Convert.ToInt32(DataSet.Tables(0).Rows(i).Item(2))

        cmd.Parameters.Add("p_quantity", OracleDbType.Int64).Direction = ParameterDirection.Input
        cmd.Parameters("p_quantity").Value = Convert.ToInt32(DataSet.Tables(0).Rows(i).Item(3))

        cmd.Parameters.Add("p_confirm", OracleDbType.Int16).Direction = ParameterDirection.Output

        cmd.CommandText = "pkg_erp.insert_delnote"
        cmd.CommandType = CommandType.StoredProcedure

        temp = cmd.ExecuteNonQuery()

        result = cmd.Parameters("p_confirm").Value.ToString()

        ...

    Next

End If

Next

At the 1st iteration through "files" folder, it generates a list of 4 parameters for procedure "insert_delnote". At the 2nd iteration, it add another 4 parameters instead only overwriting the values for initial 4 params.

Can anyone tell me how can I have only 4 parameters at every iteration through the "file" directory?

Thank you,

Answer 1

You only need to create the parameters once, before the loops, and then you can set the values inside the inner loop:

cmd.CommandText = "pkg_erp.insert_delnote"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("p_delnote_id", OracleDbType.Int64).Direction = ParameterDirection.Input
cmd.Parameters.Add("p_order_id", OracleDbType.Int64).Direction = ParameterDirection.Input
cmd.Parameters.Add("p_product_id", OracleDbType.Int64).Direction = ParameterDirection.Input
cmd.Parameters.Add("p_quantity", OracleDbType.Int64).Direction = ParameterDirection.Input
cmd.Parameters.Add("p_confirm", OracleDbType.Int16).Direction = ParameterDirection.Output

For Each oFile As String In Directory.GetFiles("D:\files")

    Dim header() As String = IO.File.ReadAllLines(oFile)
    If header(0) = "<delivery_note>" Then

        XML_File = XmlReader.Create(oFile, New XmlReaderSettings())
        DataSet.ReadXml(XML_File)

        For i As Integer = 0 To DataSet.Tables(0).Rows.Count - 1
            cmd.Parameters("p_delnote_id").Value = Convert.ToInt32(DataSet.Tables(0).Rows(i).Item(0))
            cmd.Parameters("p_order_id").Value = Convert.ToInt32(DataSet.Tables(0).Rows(i).Item(1))
            cmd.Parameters("p_product_id").Value = Convert.ToInt32(DataSet.Tables(0).Rows(i).Item(2))
            cmd.Parameters("p_quantity").Value = Convert.ToInt32(DataSet.Tables(0).Rows(i).Item(3))
            temp = cmd.ExecuteNonQuery()
            result = cmd.Parameters("p_confirm").Value.ToString()

            ...

        Next
    End If
Next