Issue in ODP.NET inserting Multiple Rows within single round trip

[Boinka]

I have recently started using Oracle�s ODP.NET Data Provider to insert Multiple Rows within a single round trip in my ASP.Net web applications. The class used is OracleCommand, which is part of the Oracle.DataAccess.Client namespace. Moreover, the ArrayBindCount property allows me to achieve a multiple row insert. In the database, I have used a stored procedure for DML.

 

The issue that I am facing is that sometimes, the parameter value that reaches the Oracle procedure for DML is malformed in the sense that I find more than one row values reaching the procedure in a single record. This happens with parameter values that are coming from ASP.Net dropdownlists. Here is an example with 3 rows/records to explain my point:

EmpID

(label inside DataGrid cell)

E001

E002

E003

 

Designation

(DDL inside DataGrid cell which has 4 listitems - Sr. Manager, Manager, Executive, Trainee)

Manager

Executive

Manager

 

Most of the times the values that reach database for DML are correct as above, but sometimes the values reaching the proc are as follows:

E001 Manager,Executive,Trainee

E002 Sr.Manager,Manager,Trainee

E003 Executive,Trainee,Sr.Manager

 

Further, I also want to diplay errors that are encoutered by RDBMS during DML on the User-Interface. How can I achieve that in this style of data updation of multiple rows?

[Nate Bross]

Could you post the relevant portions of your code?

[Boinka]

Sample code being used

 

Here is the C# code...

      OracleConnection dbConn = new OracleConnection(CONN_STRING);
     dbConn.Open();

     OracleCommand cmd = new OracleCommand("InsertBulkEmployeeRecord",dbConn);
     cmd.CommandType = CommandType.StoredProcedure;

     int[] arrEmployeeId={0};
     string[] arrName= {""};
     string[] arrDesig= {""};

     /*Grab EmployeeID(from textbox), EmployeeName(from textbox) and Designation (from dropdownlist) 
     from webpage controls present in DataGrid in a loop that iterates as many times as the count of 
     rows in the DataGrid and store them in the 3 arrays*/

     // Setting the ArrayCount for command to the number of rows in Grid
     cmd.ArrayBindCount = DataGrid.RowCount;

     OracleParameter aEmpIDParam = new OracleParameter("Employee ID", OracleDbType.Integer);
     aEmpIDParam.Direction = ParameterDirection.Input;
     aEmpIDParam.Value = arrEmployeeId;
     cmd.Parameters.Add(aEmpIDParam);
     /*Similarly for arrName and arrDesig*/

     try
     {
           cmd.ExecuteNonQuery();
           LabelStatus.Text = "There was a total of " + cmd.ArrayBindCount + " Employees added.";
           LabelStatus.Visible = true;
     }
     catch (Exception ex)
     {
           LabelStatus.Text = ex.Message.ToString();
           LabelStatus.Visible = true;
     }
     finally
     {
           dbConn.Dispose();
     }

 

 

Here is the procedure that inserts the records:

 

PROCEDURE InsertBulkEmployeeRecord(pEmpId NUMBER, pName varchar2, pDesignation VARCHAR2)
IS
BEGIN 
INSERT INTO EMPLOYEE (EMPLOYEE_ID , NAME, DESIGNATION)
VALUES (pEmpId, pName, pDesignation); 	

COMMIT;	

EXCEPTION WHEN OTHERS THEN	
INSERT INTO GTT_INSERTFAILED_EMPID (EMPLOYEE_ID) VALUES (pEmpId);
COMMIT;	 
END InsertBulkEmployeeRecord;

 

The issue in this code is that sometimes the values that reach the procedure in the database for insert are as follows:

First record: 100 Jack "Manager,Executive,Trainee"

Second record: 101 Gordon "Sr.Manager,Manager,Trainee"

Third record: 102 James "Executive,Trainee,Sr.Manager"

 

When the values input by the user were:

First record: 100 Jack "Manager"

Second record: 101 Gordon "Sr.Manager"

Third record: 102 James "Executive"

 

Further, I also want to diplay errors that are encoutered by RDBMS during DML on the User-Interface. How can I achieve that in this style of data updation of multiple rows? As you can see in the sample, I am using a workaround to achieve this by storing the failed employee ids in a Temporary Table.

[PlausiblyDamp]

Re: Sample code being used

 

What is the code you are using to assign arrName and arrDesign to parameters?

[Boinka]

Here is the C# code for arrName and arrDesig:

 

OracleParameter aNameParam = new OracleParameter("Name", OracleDbType.Varchar2);
     aNameParam.Direction = ParameterDirection.Input;
     aNameParam.Value = arrName;
     cmd.Parameters.Add(aNameParam);
     
OracleParameter aDesigParam = new OracleParameter("Designation", OracleDbType.Varchar2);
     aDesigParam.Direction = ParameterDirection.Input;
     aDesigParam.Value = arrDesig;
     cmd.Parameters.Add(aDesigParam);

 

Any lead/solution would be greatly appreciated.