-1
\$\begingroup\$

I have data that is stored in a list of class Employee:

class Employee
{
    public int employeeId;
    public string employeeName;
}

So I have List<Employee> employeeData and I'm going to do bulk insert to database using Oracle. OracleCommand requires each class member to be specified in the command parameter, meaning I have to create

int[] employeeId = new int[employeeData.Count];
string[] employeeName = new string[employeeData.Count];

Then I will need to loop from my List<Employee> class and assign to each individual array.

int index = 0;
foreach (Employee e in employeeData)
{
    employeeId[index] = e.employeeId;
    employeeName[index] = e.employeeName;
    index++;
}

And then I will bind the parameters and invoke the insert command:

string query = @"INSERT INTO EMPLOYEE_TABLE (EMPLOYEE_ID, EMPLOYEE_NAME) 
            VALUES (:EMPLOYEE_ID, :EMPLOYEE_NAME)";

using (OracleCommand dbCommand = new OracleCommand(query, Connection))
{
    dbCommand.Parameters.Add(":EMPLOYEE_ID", OracleDbType.Varchar2).Value = employeeId;
    dbCommand.Parameters.Add(":EMPLOYEE_NAME", OracleDbType.Varchar2).Value = employeeName;
    dbCommand.ArrayBindCount = employeeData.Count;

    dbCommand.ExecuteNonQuery();
}

My question is that, if I have many class members, how to do this efficiently, in the transformation from class Employee to each individual array?

\$\endgroup\$

put on hold as off-topic by t3chb0t, Toby Speight, vnp, user673679, IEatBagels Feb 12 at 18:57

This question appears to be off-topic. The users who voted to close gave these specific reasons:

  • "Lacks concrete context: Code Review requires concrete code from a project, with sufficient context for reviewers to understand how that code is used. Pseudocode, stub code, hypothetical code, obfuscated code, and generic best practices are outside the scope of this site." – Toby Speight, vnp, IEatBagels
  • "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – t3chb0t, user673679
If this question can be reworded to fit the rules in the help center, please edit the question.

  • \$\begingroup\$ why doesn't it work? my final actual code uses array instead of list. But the concept is the same. The SQL will be called only once. \$\endgroup\$ – rcs Feb 12 at 0:36
  • \$\begingroup\$ my final actual code uses - this is another reason why the question is off-topic. We require the real code and not the concept be the same so please update the question and post exactly what you have right now. \$\endgroup\$ – t3chb0t Feb 12 at 7:04
  • \$\begingroup\$ I've updated the code. \$\endgroup\$ – rcs Feb 12 at 7:40
  • \$\begingroup\$ Huh, I didn't know you could pass arrays as parameters: blogs.oracle.com/oraclemagazine/put-your-arrays-in-a-bind . \$\endgroup\$ – BCdotWEB Feb 12 at 9:20
  • \$\begingroup\$ Yes, this is essential for bulk operations. If you need to insert thousands of records and you do it through looping of insert statements 1000 times, then the performance will suffer. But the drawback is that it seems I need to create separate array for each member, so my array declarations will continue to grow and the assignment from class to array will also grow too. \$\endgroup\$ – rcs Feb 12 at 9:30

Browse other questions tagged or ask your own question.