CODEWITHSUNDEEP
c#sqlmultithreading
MoonKnight
MoonKnight

Reputation: 23833

Returning Process Information From SQL Server

All, I have some complex C# code (Windows Forms) that is heavily embedded with calls to SQL Server (2008 R2 or higher is the assumed server version). As it stands. the code is serial and I have been asked to multithread it. This process of multithreading is now specifically associated with the expensive parts of the code, i.e. where the code does "Heavy Lifting". The majority of work is done with SQL Server queries.

I am just about to embark upon multithreading the main processor that deals with calls to SQL Server. I want to provide information to the user by-way of progress from the "SQL Thread" as the SQL procedures can be very long. I want to know if my method sounds reasonable or if there is a better way of doing it. My approach is as follows: (Note: the code below is just a small example I have built before attempting this with the actual code)

A. Start a BackgroundWorker thread from a button click event on the main form.

Bgw = new BackgroundWorker { WorkerReportsProgress = true, WorkerSupportsCancellation = true };
Bgw.DoWork += new DoWorkEventHandler(Bgw_DoWork);
Bgw.ProgressChanged += new ProgressChangedEventHandler(Bgw_ProgressChanged);
Bgw.RunWorkerCompleted += new RunWorkerCompletedEventHandler(Bgw_RunWorkerCompleted);
Bgw.RunWorkerAsync();

B. From the Bgw_DoWork event, I launch my SQL method which is located in class HeavyWork.

void Bgw_DoWorkSQL(object sender, DoWorkEventArgs e)
{
    Hw = new HeavyWork(this, ref Bgw, ref e);
    Hw.SQLProc();
    return;
}

C. Start another background thread from the BackgroundWorker (this time not BackgroundWorker), from within Hw.SQLProc in order to capture progress of SQL queries and to facilitate the cancelation of the SQL Server query.

// Globals.
private bool bConnOpen = false;
private SqlConnection conn = null;
private Form _MainForm; 
private BackgroundWorker _Bgw;
private DoWorkEventArgs _e;    

public void SQLProc()
{
    bool bConnOpen = false;
    const string strSqlConnMaster = "Data Source = localhost; Initial Catalog = RMH1006DHFinal; Integrated Security " + "= True; MultipleActiveResultSets = True; Connection Timeout = 0";

    const string strSQL = "DBCC CHECKDB"; // Expensive SQL Non-Query.

    try
    {
        // Create new SQL connection.
        conn = new SqlConnection(strSqlConnMaster);

        // Execute the SQL Non-Query.
        conn.Open();
        bConnOpen = true;

        // Start another thread to get user information and for cancellation purposes.
        Thread SQLThread = new Thread(myMethod); // This is not working.
        SQLThread.IsBackground = true;
        SQLThread.Start();           

        // Now run big query.
        _Bgw.ReportProgress(0, String.Format("Processing SQL Command '{0}'...", strSQL));
        ExecNonQuery(conn, strSQL);
        conn.Close();
        bConnOpen = false;

        return;
    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        if (bConnOpen)
            conn.Close();
    }
}

In summary I want to launch the expensive SQL query on a seperate BackgroundWorker. Within the method called on the BackgroundWorker thread (some query sent using an SQL connection, call it SqlConnection conn), launch another Thread which goes into another mathod using a seperate SqlConnection connNew which retreves the information about the major process on conn.

I would like to know is this a valid way of doing this? Also, if it is possible to launch new Threads from a BackGroundWorker as the code above is not starting the myMethod method on the new thread?

Thank you all in advance.

Upvotes: 1

Views: 910

Answers (2)

Nick Butler
Nick Butler

Reputation: 24383

If I understand correctly, you want to indicate progress, but can't because ExecNonQuery blocks.

As a matter of good practice, I would not use a BackgroundWorker for this if your db call might take more than say a second, as BW uses a thread pool thread. Instead, I would use a new Thread or, if you're on .NET 4.0, a Task with TaskCreationOptions.LongRunning.

Back to your progress reporting: your db call blocks, so the only progress you can show is running or completed, so I would show some sort of marquee progress indicator. I would control this from the UI thread using a Timer - you don't really want to spin up a thread just to do an animation and you want to be on the UI thread anyway.

Upvotes: 1

George Johnston
George Johnston

Reputation: 32258

If you want to spin off a new Thread from your background thread, that's fine. However, as you've stated, how you have it set up won't work in it's current state.

Thread SQLThread = new Thread(myMethod); // This is not working.

...should become...

Thread SQLThread = new Thread(new ThreadStart(myMethod));
...

...or simply...

Thread SQLThread = new Thread(()=>myMethod());

Upvotes: 1

Related Questions