Resolved Populate datagridview from a class

[AlexJames]

Hi All

I'm busy building a C# WinForms app and have a method on my form that gets data from the database and populates a DataGridView. This is working nicely, but i want to move the code form my form to it's own method in another class so that i can reuse it.

 private void PopulateGrid()
        {
            dgvExchanges.Rows.Clear();

            DatabaseConnection myDbConn = new DatabaseConnection();
            SQLiteDataReader dr = myDbConn.ReadData("SELECT * " +
                                                    "FROM ExchangeSetup " +
                                                    "ORDER BY ExchangeName");
            int newRow = -1;

            while (dr.Read())
            {
                dgvExchanges.Rows.Add();
                if (newRow == -1)
                {
                    newRow = 0;
                }
                else
                {
                    newRow += 1;
                }
                dgvExchanges.Rows[newRow].Cells[0].Value = dr["ExchangeName"].ToString();
                dgvExchanges.Rows[newRow].Cells[1].Value = dr["ExchangeAPIKey"].ToString();
                dgvExchanges.Rows[newRow].Cells[2].Value = dr["ExchangeSecret"].ToString();
                dgvExchanges.Rows[newRow].Cells[3].Value = dr["ExchangeID"].ToString();
            }
            myDbConn.CloseConnection();

            var ExchangeCount = dgvExchanges.Rows.Count;
            lblExchangeCount.Text = string.Concat(ExchangeCount);
        }

The above code works perfectly on my form, but i'm struggling to figure out how I would call it if this method was in a class, do i load the results into a list and then bind my grid to that list ? Any suggestions or guidance on this would b greatly appreciated.
Many thanks
AJ

 

[jmcilhinney]

You're going about things the wrong way. Reading data and populating the grid directly is a bad option. What you should be doing is populating a DataTable and then binding that to the grid. You can do that like this:

var table = new DataTable();

table.Load(dr);
dgvExchanges.DataSource = table;

The grid will automatically create columns if they don't exist, or you can create the columns yourself and set their DataPropertyName properties to tell them which columns in the data source to bind to.

The answer to your question is then that you simply create a method that queries the database and returns a populated DataTable. Each form can then call that method and do whatever is appropriate with that table for that form. Maybe that's binding to a grid and maybe it's something else.

 

[AlexJames]

Hi JM

Thank you for your reply, that's pretty much what i'm looking to do. I guess i'm still stuck in the old VB6 way of doing things, working really hard to break that old coding methodology. I'm sure i'll have more questions on this topic.

 

[AlexJames]

Hi JM

Thanks to your suggestion i'm making some decent progress here.

Here is my method in me Exchanges class.

        public object LoadExchangesGrid()
        {
            DataTable table = new DataTable();

            DatabaseConnection myDbConn = new DatabaseConnection();
            SQLiteDataReader dr = myDbConn.ReadData("SELECT * " +
                                                    "FROM ExchangeSetup " +
                                                    "ORDER BY ExchangeName");
            table.Load(dr);
            myDbConn.CloseConnection();
            return table;
        }

Here is my call to the above method.

        private void PopulateGrid()
        {
            ExchangesModel eM = new ExchangesModel();
            dgvExchanges.DataSource = eM.LoadExchangesGrid();

            var ExchangeCount = dgvExchanges.Rows.Count;
            lblExchangeCount.Text = string.Concat(ExchangeCount);
        }

Is there a way that i can control what data is displayed in the grid ? so for instance i'm seeing all my data, but i only want the user to see the Exchange Name column. I've tried setting the other columns visible property to false but that doesn't work. I need all the columns populated with the data but only 1 column visible to the user. I would also appreciate any suggestions if there are any on improving the above code. I'm trying to learn the correct way from the get go.

Many thanks again
AJ.

 

[JohnH]

Add the column in designer and set its DataPropertyName to the field you want displayed, set the grids AutoGenerateColumns to False (in code).

suggestions if there are any on improving the above code

Since you're returning a DataTable change return type of method to DataTable.

 

[JohnH]

In PopulateGrid you should get the table asynchronously in order to not tie up the UI thread, you can do this simply with the use of async-await keywords and a Task.
Change the signature to:

private async void PopulateGrid()

and the loading call to:

dgvExchanges.DataSource = await Task.Run(eM.LoadExchangesGrid);

 

[AlexJames]

Hi JohnH

Thank you for your improvement suggestions, i'll implement that now. I'm still a bit fuzzy on what to do with the datagridview columns but will play around with it as per my interpretation of your suggestion. Do you know of a document or thread i can go through on when to use the async function ?

Thanks
AJ

 

[JohnH]

DataPropertyName:

Do you know of a document or thread i can go through on when to use the async function ?

Basically any code that could take a little while (seconds) to process, you'd want that executed on a secondary thread.

 

[AlexJames]

Ah, that makes sense. I see the data takes a second to load in the grid.

 

[AlexJames]

Basically any code that could take a little while (seconds) to process, you'd want that executed on a secondary thread.

Sorry, the quote didn't work the first time.

 

[AlexJames]

Hi JohnH

Setting the datapropertyname and the autogeneratecolumn to false gives me the desired look, but i still need the data populated in the other columns but hidden. When i click on a row in the dgv i populate text boxes with the data in the other columns, is there a way to hide those other columns but still have them populate with data ?

 

[JohnH]

The other data is in the table, not in the grid. Consider this:

var view = (DataRowView)dgvExchanges.CurrentRow.DataBoundItem;
textBox1.Text = (string)view["other field"];

Better would be to use data binding. Add a BindingSource to form and use this as DataSource for the grid (set in Designer). Assign the table to the BindingSource.DataSource when loaded, and add the textbox binding:

textBox2.DataBindings.Add("Text", bindingSource1, "other field");

Now you can also add a BindingNavigator to form and set its DataSource to the BindingSource as well (also done in Designer), and you have complete navigation system for the data records:

 

[AlexJames]

Thank you JohnH

That's a great idea, i'll give that a bash.

 

[AlexJames]

Hi JohnH

Thank you for all your help and advice on this thread, I've got things working like a charm.