C# Reusing an Adapter to populate other controls

Question

Hello All,

I'm using the following code to populate a DataGridView (sqliteAdapter derives from DbDataAdapter):

sqliteAdapter.SelectCommand.CommandText = SQLCommand;
sqliteConn.Open();
using (DataTable dt = new DataTable())
{
    sqLiteAdapter.Fill(dt);
    dataGridRes.DataSource = dt;
}

The Actual SQLCommand is:

SELECT Email NOTNULL AS Sel, Regiao, Distrito, Grupo, MG, ID, Nome, Morada, Email
FROM assessores

Now, I would like to reuse this DataTable to populate other controls in the form. Namely 4 CheckedListBox controls. I was hoping to avoid any further connections to the database by "filtering" the current DataTable to something like (Invalid code. Illustrative only)

SELECT Distinct Regiao FROM DataTable
SELECT Distinct Distrito FROM DataTable
SELECT Distinct Grupo FROM DataTable
SELECT Distinct MG FROM DataTable

My forays into adapter and DataTable properties and methods have been fruitless.

Answer 1

Controls can be bound to any collection, not just a DataTable.
You can therefore use LINQ, like this:

myControl.DataSource = dt.AsEnumerable().Select(dr => dr.Field<string>("Regiao")).Distinct().ToArray();

Answer 2

You can filter a DataTable but it returns an array of DataRow objects rather than a filtered DataTable, which can't be directly bound to a DataGrid or other data bound control. Instead use a DataView:

DataView dv = new DataView(dt);
dv.RowFilter = "Filter Expresion";

dg.DataSource = dv;

See Sorting and Filtering Data Using a DataView