C# WPF - DataTable to ObservableCollection-CodePudding

I am trying to Fetch 80K users from SQL. Is there a way to directly convert DataTable object to ObservableCollection because it takes too much time on the foreach loop?

private void FetchRelay(object obj)
{

    MySqlCommand cmd = new MySqlCommand(query, conn);
    dt = new DataTable();

    conn.Open();
    dt.Load(cmd.ExecuteReader());
    conn.Close();

    UserList = new ObservableCollection<Person>();
    foreach (DataRow row in dt.Rows)
    {
        UserList.Add(new Person((int)row[0], (string)row[1], (string)row[2]));

    }
}

CodePudding user response：

Here's what I would do:

Create a "constructor" of type Task<Person> called "TaskOfNewPerson";
Create a List<Task<Person>>() right after you load your datatable called "lstTaskOfNewPerson";

Then, for each row in datatable,

`lstTaskOfNewPerson.add(Person.TaskOfNewPerson((int)row[0], (string)row[1], (string)row[2])))`;

Finally, when everything was processed, get the result:
```
 `await Task.WhenAll(lstTaskOfNewPerson);`
```

And you can also get the results in the ObservableCollection using a loop for each Task in lstTaskOfNewPerson like this:

                foreach (Task<Person> personin lstTaskOfNewPerson)
                {
                    if (person.Result != null)
                    {
                        var resPerson = person.Result;
                        (...)
                    }
                }

CodePudding user response：

Thanks for all replies, here is my solution. Hope it will help someone. Firstly, loading to the DataTable object and re-reading from it takes too much time. Instead, I use the reader.

using (var reader = cmd.ExecuteReader())
            {
                 while (reader.Read())
                    {}
}

Secondly, I created a thread, that works on background, so that it won't freeze the GUI.

public void newMethod(object obj)
    {

        Thread th = new Thread(() =>
        {
            Thread.CurrentThread.IsBackground = true;
            //read SQL, place into ObservableColl
        });
        th.start();

Lastly, when I add to ObservableCollection directly, GUI (Devexpress Grid Control) couldn't catch the ObsCol. Elements were entering one by one. To solve the issue, I created a second ObservableCollection and added to it. Then, assigned NewCollection to the main one.

public void newMethod(object obj)
    {Thread.CurrentThread.IsBackground = true;
            MySqlCommand cmd = new MySqlCommand(query, conn);
            conn.Open();
            using (var reader = cmd.ExecuteReader())
            {
                NewList.Clear();
                while (reader.Read())
                {
                    NewList.Add(new Person((int)reader[0], reader[1].ToString(), reader[2].ToString()));
                }
            }
            conn.Close();
            UserListNotCollection = new ObservableCollection<Person>(NewList);
        });
        newThread.Start();

Further readings Manage Multi-Thread Data Updates