C# - Speeding up reading from Excel using OleDB

Question

I am trying to read an Excelfile with OleDB in C# and put it into a listbox. I followed a lot of guides and it works! However, it keeps showing the data with a delay of 50 milliseconds. This is very small, but I am making this for some colleagues of mine and I want it to work better and even more smooth i.e. reduce the delay. The excel file is not very big, it contains ~840 items so I can imagine it could go faster.

First I will show you my code for filling the data into the listboxes:

    const int straatmeubilair = 0;
    const int boomproducten = 1;
    const int dekkenEnBruggen = 2;
    String queryTempCategorie;
private void lbCategorie_SelectedIndexChanged(object sender, EventArgs e)
    {
        switch (lbCategorie.SelectedIndex)
        {
            case straatmeubilair:
                queryCategorie = "A%";
                break;

            case boomproducten:
                queryCategorie = "B%";
                break;

            case dekkenEnBruggen:
                queryCategorie = "C%";
                break;
        }

        if (queryTempCategorie != queryCategorie)
        {
            queryTempCategorie = queryCategorie;
            updateTable(dbProductInfo.getData("SELECT DISTINCT [Productfamilie] FROM [Bestelinfo$] WHERE [Pagina] LIKE '" + queryCategorie + "' ORDER BY [Productfamilie] ASC"), lbFamilie, "Productfamilie");
        }
    }

private void updateTable(DataTable tempTable, ListBox tempListbox, String column)
    {
        tempListbox.DataSource = tempTable;
        tempListbox.DisplayMember = column;
    }

So when the listbox changes it's index it tries to fill another listbox with a datatable requested from the database. It also has an extra if/else statement to stop it from requesting data when his last request was the same. The request for the data from Excel is shown in the code below.

public DataTable getData(String query)
    {
        // Test features
        Stopwatch stopwatch = new Stopwatch();
        stopwatch.Start();
        Console.WriteLine(testAmount);
        testAmount += 1;
        // Clear the datatable
        Console.WriteLine("CLEARING DATATABLE");
        DataTable data = new DataTable();
        // Create the connectionstring
        strConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + databasePathFile + @";Extended Properties=""Excel 12.0 xml;HDR=Yes;IMEX=1""";
        // Create adapter
        Console.WriteLine("CREATING ADAPTER");
        adapter = new OleDbDataAdapter();
        // Create connection
        Console.WriteLine("CREATING CONNECTION");
        connection = new OleDbConnection(strConnection);
        // Create the command for the given connection
        Console.WriteLine("CREATING COMMAND");
        command = new OleDbCommand(query, connection);

        try
        {
            // Open the connection
            connection.Open();
            // Give the command to the adapter
            adapter.SelectCommand = command;
            // Fill the dateset with the adapter
            adapter.Fill(data);
        }
        catch (Exception e)
        {
            MessageBox.Show("Something went wrong, contact IT");
            Console.WriteLine(e.Message);
        }
        finally
        {
            // Close connection
            connection.Close();
            // Dispose of adapter
            adapter.Dispose();
            TimeSpan ts = stopwatch.Elapsed;
            string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10);
            Console.WriteLine("Runtime " + elapsedTime);
        }
        return data;
    }

Note: The "databasePathFile" at the connectionstring is defined earlier in the program.

I think I'm doing something weird that it keeps giving me a delay. Does anyone have any suggestions? (Something like: "You need to change your method completely" is also ok :P I'm still learning! However I would like to keep this method.

Dolhescu Stefan · Answer 1 · 2015-07-22T14:05:10.823

0

Just throwing my two cents, but try running your getData function in a separate thread. I'm refering to -> System.Threading.

Usually running your application on multiple threads will make processes run faster and user experience more seamless.

I'll provide a sample code if you are not familiar using threads in C#.

Hope the above helps.

edited Jul 22 '15 at 14:05

answered Jul 22 '15 at 13:58

Dolhescu Stefan

21
2

Thanks for answering this quick :) I have never worked with Threading before. Found the standard explanations of MSDN (https://msdn.microsoft.com/en-us/library/aa645740(v=vs.71).aspx). However, if you think your example is better, don't let it stop you! :P – user3008534 Jul 22 '15 at 14:03
Actually, I think this article might help you: http://stackoverflow.com/questions/363377/how-do-i-run-a-simple-bit-of-code-in-a-new-thread This way you can create your thread / threads according to your needs. You can figure out the best running time by constantly moving functionality in or out of your thread. I see that you already monitor your execution time, so that will give you the best indication when you will be getting most performance out of your code. – Dolhescu Stefan Jul 22 '15 at 14:15
Thanks, I will give it a look – user3008534 Jul 22 '15 at 14:23

C# - Speeding up reading from Excel using OleDB

1 Answers1