Page History

Download the example database from here: DatasetGetDataFromDB-Data.db

Solution Name: GetDataFromDB

Software Version:  10.1

Summary

This solution showcases an example on how to fill DataGrid with information stored in databases.

Summary

When working with databases, there are often multiple ways to get the data you need. This guide walks through three different approaches to filling a DataGrid with information, each with its own strengths:

• Using the Mapping feature

• Filtering with SQL queries

• Filtering with LINQ

Technical Information

Since this solution explores three different ways to retrieve data from a database, we’ll go through each one step by step.

Getting data through Mapping:

First up, let's take a look at the Mapping feature:

Image Added

The way this display works is that it pulls the information from the DataGrid when you click on a row, displaying it in the TextBoxes on the right. To make this happen, each column needs to be mapped to a Tag. To do that, go to Dataset/Table and set up your tables there. Then, double-click on the mapping field and click on the “...”.

Image Added

Here you can link each tag to its corresponding column, and it will automatically retrieve the value for the selected row. Alternatively, you can use the InsertCommand method to populate the DataTable with values from those tags.

Image Added

Filtering table through SQL Queries:

For filtering with SQL Tables, the display works a bit differently since we’re now using Radio Buttons for filtering.

Image Added

This time, each Radio Button plays a role in building a query string by concatenating specific conditions based on the selected option. The method for setting up the string follows this approach:

string BuildSqlQuery(string yearFilter, string genreFilter, string availabilityFilter) {
	List<string> conditions = new List<string>();

	// Year filter
	if (!string.IsNullOrWhiteSpace(yearFilter) && yearFilter != "nf") {
		if (yearFilter.StartsWith(">") || yearFilter.StartsWith("<") || yearFilter.StartsWith("="))
			conditions.Add($"published_year {yearFilter}");
	}

	// Genre filter
	if (!string.IsNullOrWhiteSpace(genreFilter) && genreFilter != "nf") {
		conditions.Add($"genre = '{genreFilter}'");
	}

	// Availability filter
	if (!string.IsNullOrWhiteSpace(availabilityFilter) && availabilityFilter != "nf") {
		conditions.Add($"available = {availabilityFilter}");
	}

	// Base query
	string query = "SELECT * FROM Books";

	// Add WHERE clause if there are conditions
	if (conditions.Count > 0) {
		query += " WHERE " + string.Join(" AND ", conditions);
	}

	return query;
}

In simpler terms, it checks which filters are selected, adds them to a list of conditions, and then combines them into a single string that gets attached to the WHERE clause in the query.

public async Task MyFilterCall(object sender, System.Windows.Input.InputEventArgs e)
{
	@Dataset.Query.Query1.SqlStatement = BuildSqlQuery(@Tag.FilterYear, @Tag.FilterGenre, @Tag.FilterAvail);
	await @Dataset.Query.Query1.SelectCommandAsync();
}

Filtering table through LINQ statements:

Lastly, on our final page, we’ve added a ListBox with multi-selection enabled to filter the table.

Image Added

Through CodeBehind, we need to manage the ListBox to configure its settings, retrieve selected values, and set up a subscription to our EventHandler so filtering happens automatically, unlike the previous display, which used a button. For that, we use the following code snippet:

TListBox myLB = new TListBox();

public async Task DisplayOpening()
{
	// Retrieves data from datatable
	@Tag.MyDT = await @Dataset.Table.TB_Exercises.SelectCommandAsync();
	@Tag.MyFilteredDT.Value = @Tag.MyDT.Value;
	
	// Gets control of the ListBox in Drawing
	myLB = this.CurrentDisplay.GetControl("ListBox") as TListBox;
	myLB.SelectionMode = SelectionMode.Multiple;
	
	// Sets an event handler for SelectionChanged (as in, when a selected item changes state, triggers the event)
	myLB.SelectionChanged += MyFilterList;
}

We also use our DisplayOpening to initialize our DataTables, since we are now using Tags instead of querying the table directly. Also, note that we have set up the Filtered table the same way as the other one, so the full table will be displayed if no filter is applied.

Now, for our EventHandler, we are using a filter that accumulates selections instead of narrowing them down. This means that if you select “chest” and “legs,” you’ll see information for both categories combined, rather than just the entries that contain both. The code snippet for it can be seen below:

public void MyFilterList(object sender, SelectionChangedEventArgs e) {
	
	// Uses LINQ to properly filter the table
	var rows = @Tag.MyDT.Value.AsEnumerable()
    .Where(a => myLB.SelectedItems.Contains(a.Field<string>("muscle_group")) ||
                myLB.SelectedItems.Contains(a.Field<string>("equipment")) ||
                myLB.SelectedItems.Contains(a.Field<string>("difficulty_level")));
	
	// If table is empty, show the whole table, otherwise show the filtered table
	if (rows.Any())	
		@Tag.MyFilteredDT = rows.CopyToDataTable();
	else 
		@Tag.MyFilteredDT = @Tag.MyDT;
}

Technical Information

The Dataset enables connecting to an existing external Database. Different providers can be used and can be simply and quickly configured.  

To each external Database the Module Database will communicate, a connection needs to be created with certain parameters. Each connection, created on Datasets → DBs is called this as Dataset DB.

When using SQLite Databases, the Dataset can automatically create the Database if necessary; for other ones, the Database itself must already exist before you set your connection. Users with any Permission groups can create new connections in the solution, but only the Administrator can configure Databases password logins.

To fill the DataGrid, first load an SQLite database under Datasets → DBs. After that, map the data by navigating to Datasets → Tables, using pre-existing Tags along with the "Mapping Name," database, and table name.

Reference Information

→ See Datasets (SQL Queries) for more information.

In this section: