How to populate a generated Column in a DataGridView efficiently?

Question

I have a DataGridView setup in my Winforms program with information populated from a database.

I've populated the DataGridView using Select * FROM [Data] with the following code:

private void GetData(string selectCommand)
{
    try
    {
        // Create a new data adapter based on the specified query.
        dataAdapter = new OleDbDataAdapter(selectCommand, strConn);

        // Create a command builder to generate SQL update, insert, and
        // delete commands based on selectCommand.
        OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);

        // Populate a new data table and bind it to the BindingSource.
        DataTable table = new DataTable
        {
            Locale = CultureInfo.InvariantCulture
        };
        dataAdapter.Fill(table);
        bindingSource1.DataSource = table;

        // Resize the DataGridView columns to fit the newly loaded content.
        LogDataGridView.AutoResizeColumns(
            DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
    }
    catch (OleDbException) { }
}

Answer 1

Option 1:

If the database where the Status information is stored is not constantly updated by some other process, you can simply specify that the column to join is part of a table in an external database.

If it instead receives updates from other sources, you'll have to poll it with a Timer (or use the FileSystemWatcher class to receive notifications when the database file changes). The query is the same, though.

Add [;database=Second database Path].[Source Table] AS alias after the JOIN clause, then proceed as usual.

For example, set a DataGridView.Datasource specifying the second database path.

LogDataGridView.DataSource = GetCrossJoinedTable([Second database path]);

The connection string's Data Source= contains the path to the first database.
Then, cross-join two Tables in the two databases using a common key and return the column which contains the Status information from the Table in the second database (the [Order Status] column, if I read correctly the updated code).

private DataTable GetCrossJoinedTable(string secondDataBasePath)
{
    var dt = new DataTable("JoinedTable");
    string sql = "SELECT [Data].*, status.[Order Status] " +
                $"FROM [Data] LEFT JOIN [;database={secondDataBasePath}].[Output] AS status " +
                 "ON [Data].[SO nr] = status.[Source No]";
    using (var conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=firstDatabase.accdb;Persist Security Info=false;"))
    using (var cmd = new OleDbCommand(sql, conn)) {
        conn.Open();
        using (var reader = cmd.ExecuteReader()) {
            dt.Load(reader);
            return dt;
        }
    }
}

Option 2:
Query the first database, add a Status Column, update the Status info from the second database then poll new Status information with a Timer.

You can call LogDataGridView.DataSource = GetMainTableData() to set the datasource. It will also load the Status information from the second database.

The Timer will then poll only the second database, to check whether the [Order Status] of a matching [SO nr] Column in the DataTable has changed.
When the DataTable (here, a field named mainDT) is updated, your DataGraidView will reflect immediately the change.

• If the data can be edited, the Timer should skip the update if IsCurrentCellInEditMode returns true (read the notes there, about CheckBoxColumns), or paused while a Cell is in edit mode (CellBeginEdit event) and reumed when the Cell exits edit mode (CellEndEdit event).
• Remember to stop and dispose of the Timer when the Form closes (Form.FormClosing event).

If you don't need a Timer to poll the Status info, just call UpdateStatusInfo() whenever you need to update the DGV.

System.Windows.Forms.Timer sqlTimer = null;
DataTable mainDT = new DataTable("MainTable");

private DataTable GetMainTableData()
{
    string sql = "SELECT * FROM Data";
    using (var conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=firstDatabase.accdb;Persist Security Info=false;"))
    using (var cmd = new OleDbCommand(sql, conn)) {
        conn.Open();
        var reader = cmd.ExecuteReader();
        mainDT.Load(reader);
        mainDT.Columns.Add(new DataColumn() {
            Caption = "Status", ColumnName = "fStatus", DataType = typeof(string), ReadOnly = true
        });
    }
    sqlTimer = new System.Windows.Forms.Timer() { Interval = 5000 };
    sqlTimer.Tick += (s, ev) => { UpdateStatusInfo(mainDT, false); };
    sqlTimer.Start();
    return UpdateStatusInfo(mainDT, true);
}

private DataTable UpdateStatusInfo(DataTable dt, bool returnTable)
{
    string sql = "SELECT Output.[Order Status], Output.[Source No] FROM Output";
    using (var conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=secondDatabase.accdb;Persist Security Info=false;"))
    using (var cmd = new OleDbCommand(sql, conn)) {
        conn.Open();
        var reader = cmd.ExecuteReader();
        dt.Columns["fStatus"].ReadOnly = false;
        while (reader.Read()) {
            dt.Select($"fNumber = {reader["Source No"]}").FirstOrDefault()?
              .SetField("fStatus", reader["Order Status"].ToString());
        }
        dt.Columns["fStatus"].ReadOnly = true;
        return returnTable ? dt : null;
    }
}