C＃ - 使用OleDB加速从Excel读取

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.

我试图在C#中读取带有OleDB的Excelfile并将其放入列表框中。我跟着很多指南,它的确有效!但是,它会持续显示数据,延迟时间为50毫秒。这是非常小的,但我正在为我的一些同事做这个,我希望它能更好地工作,甚至更顺畅,即减少延迟。 excel文件不是很大,它包含~840项,所以我可以想象它可以更快。

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.

因此,当列表框更改其索引时,它会尝试使用数据库中请求的数据表填充另一个列表框。它还有一个额外的if / else语句,以阻止它在最后一个请求相同时请求数据。来自Excel的数据请求显示在下面的代码中。

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.

注意:connectionstring中的“databasePathFile”是在程序的前面定义的。

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.

我觉得我做的很奇怪,它一直让我拖延。有没有人有什么建议? (类似于:“你需要完全改变你的方法”也没问题:P我还在学习!但是我想保留这种方法。

1 个解决方案

#1

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

只是抛出我的两分钱,但尝试在一个单独的线程中运行你的getData函数。我指的是 - > 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#.

如果您不熟悉在C#中使用线程,我将提供示例代码。

Hope the above helps.

希望以上有所帮助。

#1