C# & SQL - Followed examples to write a Parallel.ForEach loop to create threads, but it is throwing exceptions about the connection state

Question

I have tried the code below and it throws exceptions saying that the query requires an open connection, and that the current state is open/opening/closed.

It is a Parallel.ForEach loop creating threads, and I want them to open the connection to the SQL Server database and insert something there. I followed the patterns to handle parallelism in other questions here, but they don't work for me.

Can someone throw some light on this this? How to make this happen?

Connection open error?

Connection closed error

private static void CreateResultTable()
    {
        Parallel.ForEach(_fakeTableFileFull, tableRow =>
        {
            var fileRow = Regex.Split(tableRow, PatternExFile);
            SearchForConfirm(fileRow[3], fileRow[1], fileRow[2]);
        });
    }

    private static void SearchForConfirm(string id, string number, string date)
    {
        if (tokenChecksum.ContainsKey(id))
        {
            WriteLineToResultFile(number, date, tokenChecksum[id], id);
        }
    }

    private static void WriteLineToResultFile(string number, string date, string confirm, string id)
    {
        using (Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"]
            .ConnectionString)) 
        {
            Conn.Open();
            SqlCommand cmd = new SqlCommand("INSERT INTO [dbo].[PanChecksums] " +
                                                     "([number], " +
                                                     "[Id]) " +
                                                     "VALUES " +
                                                     "(" + Int64.Parse(id) +
                                                     "," + "'" + confirm + "'" + ")", Conn);
            cmd.ExecuteNonQuery();

        }

        using (Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"]
            .ConnectionString))
        {
            Conn.Open();
            SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT IdNumber ON;", Conn);
            cmd.ExecuteNonQuery();

            cmd = new SqlCommand("INSERT INTO [dbo].[IdNumber] " +
                             "([Id], " +
                             "[number], " +
                             "[Month], " +
                             "[Year]) " +
                             "VALUES " +
                             "(" + "'" + id + "'" + "," +
                             Int64.Parse(number) + "," +
                             "'" + date.Substring(0, 2) + "'" + "," +
                             "'" + date.Substring(2, 2) + "'" + ")", Conn);

        cmd.ExecuteNonQuery();

            cmd = new SqlCommand("SET IDENTITY_INSERT IdNumber ON;", Conn);
            cmd.ExecuteNonQuery();
        }

Parallel.ForEach is designed to do heavy CPU work. In your case you need to simply wait for IO of SQL server, no CPU work. Better to use manualy created tasks and wait for them to finish. Or you can just specify parallelism option to say 4 or something. — eocron
– eocron, Commented Nov 17, 2017 at 9:59
as per the error on connection open. I suppose your application can't find any sql connection available. — ARr0w
– ARr0w, Commented Nov 17, 2017 at 10:03
don't forget to open the connection after creating it. and don't forget to only close it when whole foreach loop is done. — Joel Harkes
– Joel Harkes, Commented Nov 17, 2017 at 10:06
You've not shown us but I'm guessing that Conn is a field in this class and so all of your parallel threads are over-writing the variable with what should be something only held locally. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 17, 2017 at 10:23

eocron · Accepted Answer · 2017-11-17 10:15:05Z

2

Instead of:

Parallel.ForEach(list, x=> {...});

Use light weight tasks from ThreadPool:

var tasks = list.Select(x=> Task.Run(()=> {...})).ToArray();
Task.WaitAll(tasks);

Parallel.ForEach is used for heavy CPU load, not for IO load (which is cause of your error, you simply exhaust SQL connection pool). Another approach is to set DegreeOfParallelism:

Parallel.ForEach(list, new ParallelOptions(){ MaxDegreeOfParallelism = 4 }, x => {...});

It will limit connection creation to 4 at the time, but I don't recomend it because you will need manualy manage ALL of your Parallel.ForEach invokations, which can become quite a pain.

Also, your connection is opened twice in row, in one iteration, which is strange. Simply open one connection and use it across all iterations. It will require from you to pass this dependency in every function which need connection, but it is common practice.

edited Nov 17, 2017 at 10:15

answered Nov 17, 2017 at 10:09

eocron

7,5962 gold badges27 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Consuela Over a year ago

I would like to use tasks from ThreadPool but I am not very familiar with how that works, so I took the MaxDegreeOfParallelism suggestion on the loop instead. So it can only have a limited number of threads running at a time.I guess that should be fine!

eocron Over a year ago

ThreadPool manages count of its threads and share this fixed ammount of threads across entire application. So, in other words, every call to it will be performed in some of this threads. It is convinient to use them, instead of degree of parallelism, where you don't need CPU, and just want to, for example, send a ton of images to some host, or execute a bunch of SQL statements on some machine, in other words - async IO operations which not depend on CPU at all, but on some Input/Output operations.

Joel Harkes · Accepted Answer · 2017-11-17 10:09:35Z

0

Your connections should be open:

Conn.Open();

only close it when the Parralel.ForEach is done.

A better solution wouldbe to merge multiple insert together into 1 insert:

INSERT INTO table (column1, column2)
VALUES 
  ('',''), -- first insert row
  ('','')  -- second insert row

Just join multiple inserts together with a comma.

answered Nov 17, 2017 at 10:09

Joel Harkes

11.7k3 gold badges49 silver badges71 bronze badges

Collectives™ on Stack Overflow

C# & SQL - Followed examples to write a Parallel.ForEach loop to create threads, but it is throwing exceptions about the connection state

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related