0

The following code reads data from a SQL database, does some calculations then enters those results in a different SQL databse. I'm trying to get it to work as fast as possible with millions of data points and the main bottleneck seems to be actually saving the database.

    Pipe_Context dbContext = new Pipe_Context(Configuration["ConnectionString"], calculationType);

    PipeProbabilityModel eng = new PipeProbabilityModel(CB_Mit_Factor, calculationType);

    int count = 0;

    List<OutputTable> lst = new List<OutputTable>();


    try
    {
        foreach (var pipe in dbContext.AssetServices)
        {

            double risk = eng.calculate(pipe.Diameter,pipe.Material,pipe.InstallationType);


            OutputTable newItem = new OutputTable();
            newItem.AssetId = pipe.Id;
            newItem.PCb = risk;
            newItem.CoR = (risk * pipe.LocationType);

            lst.Add(newItem);

            if(count % 1000000 == 0)
            {
                Console.WriteLine("Here");
                dbContext.BulkInsertOrUpdate(lst);
                lst.Clear();
            }
            ++count;
    }catch (System.InvalidOperationException)
    {
        Console.WriteLine("Error: The database contains duplicate keys, Abort");
        return;
    }

    Console.WriteLine("info: Total " + count.ToString() + " entries has been added");

    dbContext.BulkInsertOrUpdate(lst);

The problem is when I have above a million pipes it throws a System.InvalidOperationException and doesn't continue to execute. Without the catch block it throws Unhandled Exception: System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first. Is there a way to run bulkinsertorupdate in batches or a better way to accomplish my goal?

Ian
  • 27
  • 7
  • Move the try to the beginning of the code. I do not think that exception is occurring in the for loop. Because you are getting the exception before the for loop and you do not have a exception handler at the beginning of the code it is reporting the exception in the only exception handler in the routine. – jdweng Jan 24 '19 at 19:04
  • @jdweng thanks for the advice but it's happening the for loop – Ian Jan 25 '19 at 13:48
  • See following posting : https://stackoverflow.com/questions/6062192/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c – jdweng Jan 25 '19 at 13:59

0 Answers0