Learn to use a databound DataGridView ComboBox in Windows Forms

Karen Payne - Aug 8 '23 - - Dev Community

Preface

It’s the year 2023 and developers are still creating Windows Forms projects for many reasons from learning to code with no interest in web development or for write helper utilities.

No matter the developer expertise many struggle to implement a combo box in a DataGridView. The common approach is trial and error and usually involves unnecessarily accessing and changing combo box values through properties of a DataGridView.

Note
This is a .NET Core 7 project which was created from an old .NET Framework 4.8. This means Microsoft Visual Studio 2022 or later is needed.

Source code

Implementation data bound with a database

For tables, yes tables as novice developers tend to use one table with a bad design.

Schema used for displaying data

  • Product table is the main table to display in a DataGridView with ColorId references to Colors table to get the color text for, in this case the DataGridView combo box column
  • Colors table contains available colors for te Product table.

Here is what we get for the finished product.

two views of finished DataGridView

  • The BindingNavigator is not avialable in Visual Studio toolbox so a custom BindingNavigator has been provided.
  • Current button shows how to get information for the current row to Visual Studio's output window without touching the DataGridView rows and cells
  • Iterate rows button shows how to get all rows to Visual Studio's output window without touching the DataGridView rows and cells
  • Set none button shows how to set the current combo box to the first element.
  • Set color button shows how to set the current row combo box value to a pre-define color.

Setup database

  • Open SSMS (SQL-Server Management Studio)
  • Create the database and populate using under the script folder, script.sql which has instructions.
  • As provided the database is set for SQLEXPRESS, change the connection string in ProductOperations.cs is not using SQLEXPRESS.

How to setup a Combo box

🔸 First rule, forget about creating the DataGridView and setting up columns in code. Visual Studio provides a designer to create columns after dropping a DataGridView on a form.

🔸 Second rule, perform data operations in a class as done in ProductOperations class to separate form operations from data operations. In this case there is a read and update methods.

Reading data

Start with reading data from the database done in ProductOperations class using the method LoadData which returns to DataTable containers which are deconstructed in the form.

  • First read the Product table into a DataTable using the query in figure 1
  • Next read in the Colors table into a DataTable using the query in figure 2
  • The last section of code to add a Select option is optonal

Figure 1



SELECT id,Item,ColorId,CustomerId FROM Product
Enter fullscreen mode Exit fullscreen mode

Figure 2



SELECT ColorId,ColorText FROM Colors ORDER BY ColorText
Enter fullscreen mode Exit fullscreen mode

Form code

  • Setup columns in the DataGridView, in this case for Item column and ColorId column from the Product table
  • Create two private BindingSource components, one for each table.

Setup method

Indicate we have defined columns so do not auto generate columns in the DataGridView.



ProductsDataGridView.AutoGenerateColumns = false;
Enter fullscreen mode Exit fullscreen mode

Read data from ProductOperations



var (productsTable, colorTable) = ProductOperations.LoadData();
Enter fullscreen mode Exit fullscreen mode

Create a private scoped DataTable for use later on to get data not needed for column setup and populate from code above.



_colorsDataTable = colorTable;
Enter fullscreen mode Exit fullscreen mode

The following code sets up data for making the ComboBox work.

Important

Assign the color DataTable to the BindingSource _comboBoxBindingSource which becomes the DataSource for the ComboBox in the DataGridView



_comboBoxBindingSource.DataSource = colorTable;
Enter fullscreen mode Exit fullscreen mode

Assign the ComboBox column DisplayMember to the column from the DataTable for colors



ColorComboBoxColumn.DisplayMember = "ColorText";
Enter fullscreen mode Exit fullscreen mode

🛑 This is where I see many developers rig up ValueMember and DataPropertyName wrong.

The next two lines are used to link the two tables together by the column ColorId



ColorComboBoxColumn.ValueMember = "ColorId";
ColorComboBoxColumn.DataPropertyName = "ColorId";
Enter fullscreen mode Exit fullscreen mode

Assign the color table to the BindingSource



ColorComboBoxColumn.DataSource = _comboBoxBindingSource;
Enter fullscreen mode Exit fullscreen mode

Set DisplayStyle to none do the ComboBox does not show until clicked.



ColorComboBoxColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
Enter fullscreen mode Exit fullscreen mode

Next assign product.Item to the DataGridView first column. You could set DataPropertyName in the designer if you desire,



ItemTextBoxColumn.DataPropertyName = "Item";
_mainBindingSource.DataSource = productsTable;
Enter fullscreen mode Exit fullscreen mode

Done, next let's move on to simple updating the current row.

Update current row in the DataGridView

As the last line in Setup, subscribe to RowChanged event for the Product table.



productsTable.RowChanged += ProductsTable_RowChanged;
Enter fullscreen mode Exit fullscreen mode

Implementation



private void ProductsTable_RowChanged(object sender, DataRowChangeEventArgs e)
{

    if (e.Action == DataRowAction.Change)
    {
        if (e.Row.Field<int>("ColorId") == -1)
        {
            // no color selected
            return;
        }
        var (success, exception) = ProductOperations.UpdateRow(e.Row);
        if (!success)
        {
            // should really write to a log file and not show the exception
            MessageBox.Show($"Failed to update\n{exception.Message}");
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Backend code

  • Recommend adding logging to the catch
  • Note how parameters are setup, never use AddWithValue.


public static (bool success, Exception exception) UpdateRow(DataRow row)
{
    using SqlConnection cn = new() { ConnectionString = _connectionString };
    using SqlCommand cmd = new() { Connection = cn };

    cmd.CommandText = 
        """
        UPDATE dbo.Product 
        SET Item = @Item,ColorId = @ColorId 
        WHERE id = @Id;
        """;

    cmd.Parameters.Add("@Item", SqlDbType.NVarChar).Value = 
        row.Field<string>("Item");
    cmd.Parameters.Add("@ColorId", SqlDbType.Int).Value = 
        row.Field<int>("ColorId");
    cmd.Parameters.Add("@Id", SqlDbType.Int).Value = 
        row.Field<int>("Id");

    try
    {
        cn.Open();
        var affected = cmd.ExecuteNonQuery();
        return (cmd.ExecuteNonQuery() == 1, null)!;
    }
    catch (Exception localException)
    {
        return (false,localException);
    }
}
Enter fullscreen mode Exit fullscreen mode

Add and delete data

Following what has been done with update. Also, check out the following for how to work with DataTable events.

Get current row information

To start, the following language extensions are used for obtaining information for the current row of the DataGridView.



public static class BindingSourceExtensions
{
    /// <summary>
    /// Return DataSource as DataTable
    /// </summary>
    public static DataTable DataTable(this BindingSource sender)
        => (DataTable)sender.DataSource;


    /// <summary>
    /// Given Current is not null return a DataRow
    /// </summary>
    public static DataRow DataRow(this BindingSource sender)
        => ((DataRowView)sender.Current).Row;

}
Enter fullscreen mode Exit fullscreen mode

From a button click

  • Get the current row as a DataRow
  • Query _comboBoxBindingSource DataTable to get the current color id
  • Get the color name
  • For this sample display the information in Visual Studio's output window.


private void CurrentButton_Click(object sender, EventArgs e)
{
    if (_mainBindingSource.Current is null)
    {
        return;
    }

    DataRow currentRow = _mainBindingSource.DataRow();

    int colorIdentifier = _comboBoxBindingSource.DataTable().AsEnumerable()
        .FirstOrDefault(row => row.Field<int>("ColorId") == currentRow.Field<int>("ColorId"))
        .Field<int>("ColorId");

    string? colorName =_colorsDataTable.AsEnumerable().FirstOrDefault(row => 
        row.Field<int>("ColorId") == colorIdentifier).Field<string>("ColorText");

    Debug.WriteLine($"Current color Id: {colorIdentifier, -4} Name: {colorName}");

}
Enter fullscreen mode Exit fullscreen mode

Changing current row data

Kept simple using a known color primary key



private void SetCurrentColorButton_Click(object sender, EventArgs e)
{
    DataRow currentRow = _mainBindingSource.DataRow();
    currentRow.SetField("ColorId", 4); // set to white
}
Enter fullscreen mode Exit fullscreen mode

Iterate all rows

Code is self describing.



private void IterateRowsButton_Click(object sender, EventArgs e)
{
    DataTable productTable = _mainBindingSource.DataTable();
    DataTable colorTable = _comboBoxBindingSource.DataTable(); ;

    for (int rowIndex = 0; rowIndex < productTable.Rows.Count; rowIndex++)
    {
        var productName = productTable.Rows[rowIndex].Field<string>("Item");
        var colorIdentifier = productTable.Rows[rowIndex].Field<int>("ColorId");

        var colorName = colorTable
            .AsEnumerable()
            .FirstOrDefault(row => row.Field<int>("ColorId") == colorIdentifier)
            .Field<string>("ColorText");


        Debug.WriteLine($"{rowIndex,-5}{productName,-15}{colorName}");
    }
}
Enter fullscreen mode Exit fullscreen mode

Important notes

Other than setting up the DataGridView there is zero need to touch the DataGridView to gain access to data as per all code presented. Many developers starting out tend to not think about (because they are new to coding) the approach presented here. The author’s mindset when working with desktop projects, in this case Windows forms is to avoid touching controls to access data. Next level is to move away from a DataTable in favor of classes. When working with classes INotifyPropertyChanged needs to be implemented for data binding meaning this interface provides change notification which is native to DataTable.

Besides implementing INotifyPropertyChanged consider adding a BindingList to the mix which means no casting of data but when using a BindingList sorting for the DataGridView is no available but when replaced with SortableBindingList found in source code sorting works.

Next steps

Once the source code has been cloned and the database setup, run the project and try changing data. Then go back and study the code carefully, at first dependent on your expertise it may take time.

Note
While running the project and making changes have SSMS open to see the changes or write a SELECT statement in a .sql file in Visual Studio to monitor changes.

SELECT statement



SELECT P.id,
       P.Item,
       P.ColorId,
       C.ColorText,
       P.CustomerId,
       P.Qty,
       P.InCart,
       P.VendorId
FROM dbo.Product AS P
    INNER JOIN dbo.Colors AS C
        ON P.ColorId = C.ColorId;
Enter fullscreen mode Exit fullscreen mode

Once the code has meaning it will be easy to adapt to any Windows Form project.

Bonus

The author never uses MessageBox to display information, instead uses TaskDialogPage. I included a basic example in Dialogs.cs and for more samples see the following GitHub repository and article.

Source code

Clone the following GitHub repository, open in Microsoft Visual Studio 2022 or higher with .NET Core 7 Framework installed.

Image
The Benefits of Choosing Grown Diamonds Over Mined Diamonds
Image
LLM GUI: Custom Python Gradio Interface

llm, gradio
Image
Stwórz Swoją Sypialnię Marzeń: Najlepsze Meble, Które Odmienią Twój Kącik Relaksu
Image
10 Tips For Getting The Most Value From All Terrain Self Propelled Wheelchair Uk
Image
How To Tell If You're Prepared For Asbestos Lawsuit Attorney
Image
This Week's Most Popular Stories About Self Propelled Wheelchair Self Propelled Wheelchair
Image
<h1>spouwmuurisolatie enschede</h1>
Image
teknik memastikan situs slots online terbaik
Image
Golang build a file DB

go
Image
Checking if a Type Satisfies an Interface in Go

webdev, go, programming, beginners
Image
Maximize ERP Success with OpenTeQ’s NetSuite Consultation Services
Image
Uncovering Unsecured Debt Settlement Loans
Image
keunggulan provider slots online habanero
Image
How to Craft an Impressive LinkedIn Summary That Reflects Your Career Goals
Image
Factor Pemasti Kesuksesan Taruhan Judi Online
Image
My Hacktoberfest 2024 Journey: Growth, Collaboration, and New Beginnings 🌱

devchallenge, hacktoberfestchallenge, hacktoberfest, opensource
Image
cara main slots online biar tidak rugi
Image
L1SLOAD: Reading Arrays, Structs and Nested Mappings
Image
Jak stworzyć idealne mieszkanie: poradnik dla każdego
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .