Parallel ForEach with SQL inserts c#

Question

I have an object like the below but with a huge amount of data, we observed that it takes a long to be inserted into our SQL database as we were using normal foreach, the main idea is to insert each Department and get the generated identity number, then insert the nested employees assigned with that department ID.

<Sample>
    <Departments>
        <Department>
            <Name>HR</Name>
            <Employees>
                <Employee>
                    <Name>Marco</Name>
                </Employee>
                <Employee>
                    <Name>John</Name>
                </Employee>
                <Employee>
                    <Name>Sarah</Name>
                </Employee>
            </Employees>
        </Department>
        <Department>
            <Name>IT</Name>
            <Employees>
                <Employee>
                    <Name>Ali</Name>
                </Employee>
                <Employee>
                    <Name>Roberto</Name>
                </Employee>
                <Employee>
                    <Name>Franco</Name>
                </Employee>
            </Employees>
        </Department>
    </Departments>
</Sample>

We tried to use Parallel.ForEach to enhance the performance, and it does, but we got another issue with @@IDENTITY because there is an overlap between the running tasks in Parallel.ForEach as we found an employee is assigned to another department.

The need ... I need to speed the process up either I use Parallel.ForEach or foreach ... any ideas?

BTW ... we are calling a stored procedures which contains normal INSERT INTO command using classing ADO.NET

Answer 1

You could use guid as primary key into your table. It would help you to avoid problem with @@IDENTITY. At first you should generate new guid-identity, thank insert the generated value into a row