Page History

• Solution Name: Get Data From DB
• Software Version: v10
• Keywords: DataGrid. Database. Datasets.

Summary

This solution demonstrates how to populate a DataGrid with information stored in databases.

Image Removed

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.

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

Image Added

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:

The image above already represents the Mapping display. 

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();
}

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;
}

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;
}

Reference Information

→ See Datasets (SQL Queries) for more information.

In this section: