Managing threads

[foamy]

Hi everyone,

I have an app that needs to get a chunky amount of data from an Access mdb file... This in itself is no problem, I've done it often... The problem is, that this Access database is horribly slow (as you might imagine) and during the looping of the data, my app appears to the user as "Not responding" ...

What I'd like is to have a thread handle the connection to the database and the handling of the data, while triggering events to increment the number of rows processed, which would then be displayed on my GUI...

How would a thread be implemented when dealing with a database connection?

The code below is what I'm working on... Obviously, the count variable currently doesn't show up until everything is done, which kind of defeats its purpose...

GUI class

public partial class Main : Form
    {
        private Driver driver = Driver.Instance;
        private ArrayList mainData = new ArrayList();
        private ArrayList dispoData = new ArrayList();
        private int count = 0;

        public Main()
        {
            InitializeComponent();
            Driver.CountChangedHandler myCountChangedHandler = Increment;
            driver.CountChanged += myCountChangedHandler;
            gridDispo.AutoGenerateColumns = false;
            gridMain.AutoGenerateColumns = false;
        }

        private void Increment(object sender)
        {
            count++;
            txtCount.Text = count.ToString();
        }

        private void btnExtract_Click(object sender, EventArgs e)
        {
            try
            {
                mainData = driver.GetMainData();
                gridMain.DataSource = mainData;
                dispoData = driver.GetDispoData();
                gridDispo.DataSource = dispoData;
            }
            catch
            {
            }
        }

Driver class (should be called DAO really, but you get the idea)

    class Driver
    {
        private static Driver instance;
        private String ConnectionString = "";

        //Set up delegates
        public delegate void CountChangedHandler(object sender);
        public event CountChangedHandler CountChanged;
        //Set up events


        private Driver()
        {
            ConnectionString = this.ReadSettings("\\settings.ini", Environment.CurrentDirectory, "Db");
        }

        public void OnCountChanged()
        {
            CountChanged(this);
        }

        /// <summary>
        /// Returns an instance of this class.
        /// Creates a new instance if none exists.
        /// </summary>
        public static Driver Instance
        {
            get
            {
                if (instance == null)
                    instance = new Driver();
                return instance;
            }
        }

        public ArrayList GetMainData()
        {
            using (OleDbConnection connection = new OleDbConnection(ConnectionString))
            {
                String QueryString = "SELECT * FROM Main";
                using (OleDbCommand cmd = new OleDbCommand(QueryString, connection))
                {
                    ArrayList result = new ArrayList();
                    try
                    {
                        cmd.Connection.Open();
                        OleDbDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            object name = reader["Driver"];
                            result.Add(new TruckDriver(name.ToString()));
                            this.OnCountChanged();
                        }
                        return result;
                    }
                    catch
                    {
                        return null;
                    }
                }
            }
        }
        public ArrayList GetDispoData()
        {
            using (OleDbConnection connection = new OleDbConnection(ConnectionString))
            {
                String QueryString = "SELECT * FROM [Disponering-SAL-Move]";
                using (OleDbCommand cmd = new OleDbCommand(QueryString, connection))
                {
                    ArrayList result = new ArrayList();
                    try
                    {
                        cmd.Connection.Open();
                        OleDbDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            object id = reader["ID"];
                            object chassis = reader["Chassisnr"];
                            object kørsel = reader["Kørselsdato"];
                            object model = reader["Model"];
                            object postnr = reader["HPostnummer"];
                            result.Add(new Disponering_SAL_Move(Convert.ToInt32(id), chassis.ToString(), kørsel.ToString(), model.ToString(), Convert.ToInt32(postnr)));
                            this.OnCountChanged();
                        }
                        return result;
                    }
                    catch
                    {
                        return null;
                    }
                }
            }
        }

[Arjay]

Have you started with the basics of working on how to create a thread?

[foamy]

Oh yeah, I know that... I should have put that in the original post...

Apparently, getting away from the code for an hour has helped... I've come up with this:

private void btnExtract_Click(object sender, EventArgs e)
        {
            try
            {
                Thread work = new Thread(new ThreadStart(GetData));
                work.Start();
            }
            catch
            {
            }
        }

        private void GetData()
        {
             try
            {
                mainData = driver.GetMainData();
                gridMain.DataSource = mainData;
                dispoData = driver.GetDispoData();
                gridDispo.DataSource = dispoData;
            }
            catch
            {
            }
        }

However, the CountChanged event doesn't seem to fire as the Increment() method is never called...

Edit: scratch that, I'm getting a cross-thread exception... txtCount cannot be changed from a different thread that the one it was created in... How would you suggest I get around this?

[saktya]

Use the Dispatch.Invoke method...

I use recursive method like this:

public void AddText( string text )
        {
            if( Dispatcher.CurrentDispatcher != this.Dispatcher )
            {
                this.Dispatcher.Invoke( DispatcherPriority.Render, (System.Windows.Forms.MethodInvoker)delegate { AddText( text ); } );
                return;
            }

            //new item
           _textBox1.Text = text;
        }

[Thread1]

Also, you may tell us how you retrieve/access the data from database and where it is located (i.e., over the network).

[foamy]

Use the Dispatch.Invoke method...

I use recursive method like this:

public void AddText( string text )
        {
            if( Dispatcher.CurrentDispatcher != this.Dispatcher )
            {
                this.Dispatcher.Invoke( DispatcherPriority.Render, (System.Windows.Forms.MethodInvoker)delegate { AddText( text ); } );
                return;
            }

            //new item
           _textBox1.Text = text;
        }

Thanks! I'll try it out in about an hour

Also, you may tell us how you retrieve/access the data from database and where it is located (i.e., over the network).

So the original post wasn't enough for you? it has code that specifies two methods: GetMainData() and GetDispoData() ... what more do you want?





EDIT: I can't get the dispatcher thing to work... I get a compiler error saying that my windows form class doesn't contain a definition for one... What am I doing wrong here?

[Thread1]

So the original post wasn't enough for you? it has code that specifies two methods: GetMainData() and GetDispoData() ... what more do you want?

Oh I see, sorry I overlooked the last part of your post.

Okay have you tried DoEvents inside the loop? And how large is your database? I just don't consider threading in this scenario, have a look http://msdn.microsoft.com/en-us/libr...24(VS.80).aspx

[foamy]

Oh I see, sorry I overlooked the last part of your post.

Okay have you tried DoEvents inside the loop? And how large is your database? I just don't consider threading in this scenario, have a look http://msdn.microsoft.com/en-us/libr...24(VS.80).aspx

I need to gather around 200,000 rows from an access database on a mounted network drive...

I've figured out the dispatcher thing - since I'm using .NET 2.0 I can't use it ... should have specified that in my first post

I ended up creating a new delegate in my GUI class and then using the Control.Invoke method... works like a charm and my users will now be able to see the progress of the data flow from their horribly slow database

thanks everyone!

EDIT: A small bug, though ... when I extract the data using a thread, my Datagridviews don't render their scrollbars, but when I extract it from the main thread, they do ... Could anyone advise? :-S

[Thread1]

Glad it works for you

[foamy]

Aside from the scrollbar problem, I'm getting some exceptions... I'm currently using the code below, and I'm getting a NullReferenceException when setting the Datasource properties on the datagridviews... this is not caught, so it flashes the Unhandled Exception dialog...

it doesn't happen every time, it doesn't always happen on the same datagridview... however, it never happens if I run GetData() directly... (without using the thread)

Any advise?

private void btnExtract_Click(object sender, EventArgs e)
        {
            try
            {
                //GetData();
                work = new Thread(GetData);
                work.Start();
            }
            catch (Exception x)
            {
            }
        }

private void GetData()
        {
            try
            {
                count = 0;

                mainData.Clear();
                dispoData.Clear();

                gridMain.DataSource = null;
                gridDispo.DataSource = null;
                gridMain.Rows.Clear();
                gridDispo.Rows.Clear();

                mainData = driver.GetMainData();
                dispoData = driver.GetDispoData();

                //Exception is thrown after this point...
                gridMain.DataSource = mainData; 
                gridDispo.DataSource = dispoData;
            }
            catch (Exception x)
            {
            }
        }

For testing purposes, I've changed my GetData methods in the Driver class:

public List<TruckDriver> GetMainData()
        {
            List<TruckDriver> result = new List<TruckDriver>();
            OnStatusChanged("Henter data...");
            for (int i = 0; i < 1000; i++)
            {
                result.Add(new TruckDriver("String"));
                OnCountChanged();
            }
            return result;
         }

public List<Disponering_SAL_Move> GetDispoData()
        {
            List<Disponering_SAL_Move> result = new List<Disponering_SAL_Move>();
            OnStatusChanged("Henter data...");
            for (int i = 0; i < 1000; i++)
            {
                result.Add(new Disponering_SAL_Move(i, "String", "String", "String", 5));
                OnCountChanged();
            }
            return result;
}