In this blog post I am not going to jump and start talking about Task Parallel Library (TPL), before starting that I am going to talk a little bit about threading and how to perform multiple operations using Threading via Multithreading. Later we will discuss on TPL and their relation operations.

Threading

As we all know, thread is an independent process which has its own memory and they will perform certain operation (if they are assigned to do so). In this whole blog post, we are going to discuss on executing some query with multiple thread, so that we execute them in parallel, rather sequential. Here is my function which will execute a query in my SQL server.

        public static void ConnectDBAndExecuteQuery()
        {
            try
            {
                string sqlQuery = "insert into tblMasterLookup Values (" + Thread.CurrentThread.Name + ",'Test','2.0','Myapplication',GetDate())";

                string connectionString = @"Server=.\SQLEXPRESS;Database=AUTODATA;Password=abc@123;User ID=sa";
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                    {
                        command.CommandTimeout = 80;
                        command.ExecuteNonQuery();
                        Console.WriteLine("Executed Thread.. " + Thread.CurrentThread.Name);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

Here is the code to Create Threads. I am going to use ThreadStart delegate to perform execution of method

        public static void CreateThread(int ThreadCount)
        {
            Thread[] workerThread = new Thread[ThreadCount];

            for (int i = 0; i < ThreadCount; i++)
            {
                workerThread[i] = new Thread(new ThreadStart(ConnectDBAndExecuteQueryWithLock));
                workerThread[i].Name = i.ToString();
                workerThread[i].Start();
            }
        }

As you can see here if I execute the code, I will get the output as shown But since the ExecuteNonQuery() method of SqlCommand is not Thread safe, we need to maintain thread safety. Also, you can note that we have opened the connection of SQL server each and everytime within the method itself (ConnectDBAndExecuteQuery).

Thread Safety

There are chances that your thread may be interrupted by some other thread, hence we need to use the lock as shown, and here is the updated method.

       public static void ConnectDBAndExecuteQueryWithLock()
        {
            lock (threadLock)
            {
                try
                {
                    string sqlQuery = "insert into tblMasterLookup Values (" + Thread.CurrentThread.Name + ",'Test','2.0','Myapplication',GetDate())";

                    string connectionString = @"Server=.\SQLEXPRESS;Database=AUTODATA;Password=abc@123;User ID=sa";
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        connection.Open();
                        using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                        {
                            command.CommandTimeout = 80;
                            command.ExecuteNonQuery();
                            Console.WriteLine("Executed Thread.. " + Thread.CurrentThread.Name);
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }
        }

We need to create a threadlock variable of type object 

private static object threadLock = new object();

Now we can ensure that our thread is safe from other threads. Here is the data executed (inserted) time for both with and without thread lock result in table As you could see for code without Thread Lock, there happened as many as 14 operation in single millisecond, whereas in code with thread lock we happened to have sporadically 11 operations in single millisecond.

Parallel Execution

I am going to do the same stuff executing them in Parallel using Task Parallel Library (TPL), but here creating and managing thread is not done by me, rather .Net and CLR will take care of it. Here is the code

        public static void WithTPL(int ThreadCount)
        {
            //Create Parallel.For to execute the task
            Parallel.For(0, ThreadCount, i =>
            {
                ConnectDBAndExecuteQuery(i);
            });
        }

As you can see in the code I am using Parallel.For to execute the ConnectDBAndExecuteQuery() method, the for loop will be iterated for the threadcount which I am passing as parameter, but here I am not creating the thread rather CLR will do instead. Here is the output, here I have added Thread.CurrentThread.ManagedThreadId to show the ManagedThreadID As you can see the output, most of the threads have been reused here instead of one creating newly. Here mostly thread ID with 10,11,12,13 are the threads which executed the whole operation 20 times, now the database table has these many records as shown AS you can see, the record inserted time frame is very close to the Thread without lock we saw previous in our discussion. But the important point to note here is, the whole operation is performed by JUST 4 THREADS. Using Parallel Programming we don’t have to care about managing threads and reusing them, since everything is taken care by .Net for us.

Limiting Parallel Threads with ParallelOptions

We can also limit the parallel threads from 4 to 2 or we can increase the same from 2 to 10 using ParallelOptions class Here is the self-explanatory code

            
ParallelOptions options = new ParallelOptions();
options.MaxDegreeOfParallelism = 2;

Now let’s change our code to have ParallelOptions in our Parallel.For method

        public static void WithTPLAndParallelOptions(int ThreadCount)
        {
            ParallelOptions options = new ParallelOptions();
            options.MaxDegreeOfParallelism = 2;
            //Create Parallel.For to execute the task
            Parallel.For(0, ThreadCount, options, i =>
            {
                ConnectDBAndExecuteQuery(i);
            });
        }

Now the output looks like this As you can see, only 2 threads are executing the method in parallel, which is 9 and 10. I hope this post has given an idea on how to call multiple methods in parallel using TPL and Threads. Thanks, Karthik KK