Paginate Mysql Results in WPF Scheduled Task

Question

I have a generic method that lets me run a query inside scheduled Tasks like this:

public static DataSet ExecuteQuery(string strQuery)
{
    DataSet ds = new DataSet();

    using (MySqlConnection mySqlConnection = new MySqlConnection("server=localhost;uid=root;database=sampleDB"))
    {
        mySqlConnection.Open();

        using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(strQuery, mySqlConnection))
        {
            dataAdapter.TableMappings.Add("Table", "data");
            dataAdapter.Fill(ds);
        }

        mySqlConnection.Close();
    }
    return ds;
}

To use this, I can call it from inside my task like this:

DataSet results = DataConnection.ExecuteQuery("SELECT * from appointments");

I have been looking for a way to paginate results, but every solution I could find uses a DataGrid and buttons to navigate from page to page.

I tried using the extension from this page: LINQ and pagination, but it seems to need to be further downstream after results have been added to a List, at which point the Mysql query would have already been executed and returned potentially hundreds of thousands of results to be stored in memory.

I would like to still have this end up with the same simple call, but change it up to something like:

DataSet results = DataConnection.ExecuteQuery("SELECT * from appointments", 1, 50);

Why is a DataGrid + buttons not how you want to do your pagination? Are you looking for a fully virtualized scrollable collection in your view model that automatically requeries the database when the user scrolls? Please give some detail as to what you actually want. — Adam Brown, Jan 12 '18 at 17:51
Even though it is a WPF application, the user never sees the data, it is done on a task that is then formatted as JSON and sent to a webserver. — Alan, Jan 12 '18 at 17:53
Your query will need to specify both the page number and page size. It won't be possible for the query to be as simple as `select *`. — Scott Hannen, Jan 12 '18 at 17:53
This page was very helpful: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/paging-through-a-query-result `adapter.Fill(dataSet, currentIndex, pageSize, "Orders"); ` — Alan, Jan 12 '18 at 18:11
I wouldn't recommend that for lots of records. It's going to return all of the records from the database and then paginate them. It's much better to handle that on the database side, although it tends to require multiple steps in your query. In the first step I'll often select just primary keys according to the criteria. Then from that result set, select the set of keys for one page of records. Now I've got a set of PKs representing the page of records I want, so I re-join to the other tables and select the all of the columns I want to return for those PKs. — Scott Hannen, Jan 12 '18 at 20:51

score 0 · Answer 1 · answered Jan 12 '18 at 18:28

I think you are looking at the wrong place for the pagination. You should not paginate this on the code level if you are actually talking about "hundreds of thousands of records". You should have a primary key, or an indexed field in your MYSQL table that you can simply paginate based on that field.

You should just query the necessary amount of records by filtering them which would be efficient on both the Database side (querying specific block of data, therefore less seeks, and searches), and the client side (Memory allocation for the returned object).

To implement:

1) Check if you have Id, if not you can add one by using Auto Increment feature. Or you could simply use LIMIT @Start, @End 2) Modify your C# query, and do the pagination based on the UI interactions.

Paginate Mysql Results in WPF Scheduled Task

1 Answers1