Multithreading in C# and update tables

Question

I am new to multithreading in C#. This is what I am trying to do. I have a sql server table

create table dbo.temp(ID smallint not null, textfilename varchar(100) not null,isDone bit not null )

INSERT INTO dbo.temp(1,'textfile1.txt',0)
INSERT INTO dbo.temp(2,'textfile2.txt',0)
INSERT INTO dbo.temp(3,'textfile3.txt',0)
INSERT INTO dbo.temp(4,'textfile4.txt',0)

I have a c# program that Uses the contents of this textfile and updates other database tables and sets isdone=1. I know how to do this singlethread.

public class Program
{
    public static void Main()
    {
       public string filename = "";
        try
        {
         string query = "SELECT ID,textfilename FROM [somedatabase].[dbo].[temp] WITH(NOLOCK) WHERE isdone = 0 ORDER BY ID;";
         using (SqlConnection Conn= new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConnStr"].ConnectionString))
         {
            Conn.Open();
            using (SqlCommand cmd = new SqlCommand(query, Conn))
            {
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        fileid = reader.GetInt16(0).ToString();
                        filename = reader.GetString(1);
                        StartProcessingFiles(filename);

                    }
                }
           }
         }
     }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            throw e;

        }
}
public static void StartProcessingFiles(string filename)
{
  //some business rules here

  //after processing them
  //Update [somedatabase].[dbo].[temp]  SET isdone=1 where filename=filename
}
}

How can I convert this to multithread say 3 files at a time

How do you propose to stop the threads from processing the same entries? — spodger, Jun 04 '19 at 16:52
In which case you'd need to lock each row in the table as you process it until you've set `isdone = 1`. — spodger, Jun 05 '19 at 07:52

score 3 · Accepted Answer · answered Jun 04 '19 at 17:20

Multithreading is for logic which is CPU-bound. Your database updates are not likely to be bound by the application server's CPU. So instead you might want to consider using async.

To do this, first break down your logic. Define a separate method to retrieve the file names:

public IEnumerable<string> GetFileNames()
{
     string query = "SELECT ID,textfilename FROM [somedatabase].[dbo].[temp] WITH(NOLOCK) WHERE isdone = 0 ORDER BY ID;";
     using (SqlConnection Conn= new SqlConnection(connectionString))
     {
        Conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, Conn))
        {
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var filename = reader.GetString(1);
                    yield return filename;
                }
            }
       }
    }
}

Then write code to process the list at the same time, asynchronously:

public async Task ProcessFiles()
{
    var files = GetFileNames();
    var tasks = files.Select( fileName => ProcessFile(fileName) );
    await Task.WhenAll(tasks);
}

For this to work, ProcessFile must be async too:

public async Task ProcessFile(string fileName)
{
     string query = "Update [somedatabase].[dbo].[temp]  SET isdone=1 where filename=@fileName";
     using (SqlConnection Conn= new SqlConnection(connectionString))
     {
        Conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, Conn))
        {
            cmd.Parameters.AddWithValue("@fileName", fileName);
            await cmd.ExecuteNonQueryAsync();
       }
    }
}

Since the method uses ExecuteNonQueryAsync, it can return control to the caller and allow the next task to proceed (see How do yield and await implement flow of control?). The tasks are all synchronized when you call WhenAll.

[There is no thread](https://blog.stephencleary.com/2013/11/there-is-no-thread.html). Async methods like `ExecuteNonQueryAsync` utilize O/S threads and callbacks to avoid creating application threads. An additional thread is not necessary when the operation is not CPU-bound. — John Wu, Jun 04 '19 at 18:26

score 0 · Answer 2 · edited Jun 04 '19 at 17:05

0

To process multiple files at the same time I think you could replace

StartProcessingFiles(filename);

with

Task.Run(() => StartProcessingFiles(filename));

But then you have no guarantees about order in with the files are processed and database is updated.

edited Jun 04 '19 at 17:05

kalehmann

4,821
6
26
36

answered Jun 04 '19 at 16:43

Marcin Gajda

11
3

Multithreading in C# and update tables

2 Answers2