Hello,
The following code sample uses SQL-Server yet is irrelevant as how the code is setup the data can come from any database e.g. SQlite, Oracle, SQL-Server, MS-Access etc.
- As Shown in the image there are two DataGridViewComboBox columns.
- To get current row values, see this code which if needed you can send back to the database.
- Data operations are in the following class.
Now one might ask, is the easier way, well that depends, yes and no while I wrote this to be optimal rather than think about simple as simple may work but usually is missing something or is not wise e.g. access cells of the DataGridView for values rather than from a BindingSouce (and a BindingSource has many benefits to check out).
Data class
using System.Data;
using System.Data.SqlClient;
namespace DataGridViewCombo1.Classes
{
public class Operations
{
/*
* Make sure the databasescript file is executed and that the
* Data Source points to the correct server if not SQL-Server Express.
*/
private string ConnectionString = "Data Source=.\\sqlexpress;Initial " +
"Catalog=DataGridViewCodeSample;Integrated Security=True";
public DataTable ColorDataTable { get; set; }
public DataTable VendorDataTable { get; set; }
private DataTable _customerTable = new DataTable();
public DataTable CustomerDataTable
{
get => _customerTable;
set => _customerTable = value;
}
/// <summary>
/// Load main table for DataGridView
/// </summary>
public void LoadCustomerDataTable()
{
CustomerDataTable = new DataTable();
using (var cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand { Connection = cn })
{
cn.Open();
cmd.CommandText = "SELECT id,Item,ColorId,CustomerId, qty, InCart, VendorId FROM Product";
CustomerDataTable.Load(cmd.ExecuteReader());
}
}
}
/// <summary>
/// Load color reference table
/// </summary>
public void LoadColorsReferenceDataTable()
{
ColorDataTable = new DataTable();
using (var cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand { Connection = cn })
{
cn.Open();
cmd.CommandText = "SELECT ColorId,ColorText FROM Colors";
ColorDataTable.Load(cmd.ExecuteReader());
}
}
}
/// <summary>
/// Load vendor reference table
/// </summary>
public void LoadVendorsReferenceDataTable()
{
VendorDataTable = new DataTable();
using (var cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand { Connection = cn })
{
cn.Open();
cmd.CommandText = "SELECT VendorId,VendorName FROM dbo.Vendors";
VendorDataTable.Load(cmd.ExecuteReader());
}
}
}
}
}
Frontend code
using System;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using DataGridViewCombo1.Classes;
namespace DataGridViewCombo1
{
public partial class Form1 : Form
{
readonly Operations _operations = new Operations();
readonly BindingSource _customerBindingSource = new BindingSource();
readonly BindingSource _vendorBindingSource = new BindingSource();
readonly BindingSource _colorBindingSource = new BindingSource();
public Form1()
{
InitializeComponent();
CustomersDataGridView.CurrentCellDirtyStateChanged += _CurrentCellDirtyStateChanged;
}
private void Form1_Load(object sender, EventArgs e)
{
Setup();
CustomersDataGridView.AllowUserToAddRows = false ;
_customerBindingSource.PositionChanged += _customerBindingSource_PositionChanged;
LoadData();
CurrentValuesView();
ActiveControl = CustomersDataGridView;
}
private void _customerBindingSource_PositionChanged(object sender, EventArgs e)
{
CurrentValuesView();
}
private void Setup()
{
_operations.LoadColorsReferenceDataTable();
_operations.LoadVendorsReferenceDataTable();
_vendorBindingSource.DataSource = _operations.VendorDataTable;
_colorBindingSource.DataSource = _operations.ColorDataTable;
ColorComboBoxColumn.DisplayMember = "ColorText";
ColorComboBoxColumn.ValueMember = "ColorId";
ColorComboBoxColumn.DataPropertyName = "ColorId";
ColorComboBoxColumn.DataSource = _colorBindingSource;
ColorComboBoxColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
VendorComboBoxColumn.SortMode = DataGridViewColumnSortMode.Automatic;
VendorComboBoxColumn.DisplayMember = "VendorName";
VendorComboBoxColumn.ValueMember = "VendorId";
VendorComboBoxColumn.DataPropertyName = "VendorId";
VendorComboBoxColumn.DataSource = _vendorBindingSource;
VendorComboBoxColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
VendorComboBoxColumn.SortMode = DataGridViewColumnSortMode.Automatic;
QtyNumericUpDownColumn.DataPropertyName = "qty";
InCartCheckBoxColumn.DataPropertyName = "InCart";
}
private void LoadData()
{
_operations.LoadCustomerDataTable();
CustomersDataGridView.AutoGenerateColumns = false;
ItemTextBoxColumn.DataPropertyName = "Item";
_customerBindingSource.DataSource = _operations.CustomerDataTable;
CustomersDataGridView.DataSource = _customerBindingSource;
//see comments in event code.
//CustomersDataGridView.CellFormatting += CustomersDataGridView_CellFormatting;
}
/// <summary>
/// To be moved out of here, placed here to reply to a forum question.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void CustomersDataGridView_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
if (e.ColumnIndex == CustomersDataGridView.Columns["ColorComboBoxColumn"].Index && CustomersDataGridView.Rows[e.RowIndex].DataBoundItem != null)
{
var colorKeyValue = ((DataRowView)CustomersDataGridView.Rows[e.RowIndex].DataBoundItem).Row.Field<int>("ColorId");
if (colorKeyValue == 2)
{
//CustomersDataGridView.Rows[e.RowIndex].Cells[CustomersDataGridView.Columns["ColorComboBoxColumn"].Index].Style = new DataGridViewCellStyle { ForeColor = Color.White, BackColor = Color.Tomato };
CustomersDataGridView.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Yellow;
}
else
{
//CustomersDataGridView.Rows[e.RowIndex].Cells[CustomersDataGridView.Columns["ColorComboBoxColumn"].Index].Style = null;
CustomersDataGridView.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Empty;
}
}
}
private void _CurrentCellDirtyStateChanged(object sender, EventArgs e)
{
CustomersDataGridView.CurrentCellDirtyStateChanged -= _CurrentCellDirtyStateChanged;
CustomersDataGridView.CommitEdit(DataGridViewDataErrorContexts.Commit);
CustomersDataGridView.CurrentCellDirtyStateChanged += _CurrentCellDirtyStateChanged;
}
private void CurrentValuesView()
{
if (_customerBindingSource.Current == null)
{
return;
}
#region Get primary table information
var customerRow = ((DataRowView)_customerBindingSource.Current).Row;
var customerPrimaryKey = customerRow.Field<int>("Id");
var colorKey = customerRow.Field<int>("ColorId");
var vendorKey = customerRow.Field<int>("VendorId");
#endregion
#region Get child table information
var vendorName = ((DataTable)_vendorBindingSource.DataSource)
.AsEnumerable()
.FirstOrDefault(row => row.Field<int>("VendorId") == vendorKey)
.Field<string>("VendorName");
var colorName = ((DataTable)_colorBindingSource.DataSource)
.AsEnumerable()
.FirstOrDefault(row => row.Field<int>("ColorId") == colorKey)
.Field<string>("ColorText");
#endregion
DisplayInformationTextBox.Text =
$"PK: {customerPrimaryKey} Vendor key {vendorKey} vendor: {vendorName} color id: {colorKey} - {colorName}";
}
}
}