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

Sample code being used

Here is the C# code...
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:

Code: 
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.
 
Here is the C# code for arrName and arrDesig:

Code: 
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.
 
Back
Top