Mada za sehemu hiiCreating database connectivity options and reportingMada 4
Showing Database Records on Forms
When building event-driven applications, you often need to display records stored in a database directly on your form so users can view and interact with the data. This is done by fetching records from the database using SQL SELECT queries and binding the results to visual controls like ListView or DataGridView.
Before you can display database records on a form, you need a method that retrieves data from the database. This is the Read operation in CRUD (Create, Read, Update, Delete). The retrieval is done using the SQL SELECT statement, which fetches rows from a table.
In the SQLiteHelper class, the GetUsers method retrieves all records from the Users table. It opens a connection to the database, executes a SELECT query, and stores each row as a tuple containing the user's ID, name, and age in a list.
public List<(int id, string name, int age)> GetUsers()
{
List<(int id, string name, int age)> users = new List<(int id, string name, int age)>();
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
string sql = "SELECT Id, Name, Age FROM Users";
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
int age = reader.GetInt32(2);
users.Add((id, name, age));
}
}
}
}
return users;
}
The method uses a SQLiteDataReader to read through each row returned by the query. The reader.GetInt32(0) gets the integer value from the first column (ID), reader.GetString(1) gets the string from the second column (Name), and so on.
To display records on a form, you use a ListView control. This control shows data in columns, similar to a table. Before displaying data, you must configure the ListView by adding columns for each field you want to show.
The code below configures a ListView with three columns: ID, Name, and Age. The View = View.Details setting enables column display, FullRowSelect allows selecting entire rows, and GridLines adds lines between rows for better readability.
listViewUsers.View = View.Details;
listViewUsers.FullRowSelect = true;
listViewUsers.GridLines = true;
listViewUsers.Columns.Add("ID", listViewUsers.Width / 3, HorizontalAlignment.Left);
listViewUsers.Columns.Add("Name", listViewUsers.Width / 3, HorizontalAlignment.Left);
listViewUsers.Columns.Add("Age", listViewUsers.Width / 3, HorizontalAlignment.Left);
The LoadUsers method connects the database to the ListView. It first clears any existing data in the ListView, then calls GetUsers to fetch the records from the database. For each user record retrieved, it creates a ListViewItem and adds it to the ListView.
private void LoadUsers()
{
listViewUsers.Items.Clear();
List<(int id, string name, int age)> users = dbHelper.GetUsers();
foreach (var user in users)
{
ListViewItem item = new ListViewItem(user.id.ToString()); // ID is first column
item.SubItems.Add(user.name); // Name is second column
item.SubItems.Add(user.age.ToString()); // Age is third column
listViewUsers.Items.Add(item);
}
if (users.Count == 0)
{
MessageBox.Show("No users found in the database.", "Information",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
The first column value is set using the ListViewItem constructor, while subsequent columns are added using the SubItems.Add() method. This method is called when the form loads (in the Form_Load event) or after a new record is inserted to refresh the display.
Sometimes you want to show records in a separate window rather than on the main form. To do this, you create a second form (Form2) with its own ListView and LoadUsers method. When a "View" button is clicked on the main form, Form2 opens as a dialog.
private void button3_Click(object sender, EventArgs e)
{
Form2 form2 = new Form2();
form2.Show(); // Opens Form2
}
This approach is useful when you have a registration form on the main window but want to display the list of registered students in a dedicated window.
Another common control for displaying database records is the DataGridView. It automatically handles column generation and provides built-in features like sorting and column resizing. To load data into a DataGridView, you bind a DataTable or use a data source directly.
private void btnLoadStudents_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
string sql = "SELECT StudentID, FullName, Class FROM Students";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn);
adapter.Fill(dt);
}
dataGridView1.DataSource = dt;
}
The DataGridView automatically creates columns based on the DataTable structure, making it quicker to set up than ListView for simple data display.
Consider a school where you need to display registered students on a form. Suppose the Students table has columns: StudentID, FullName, and Class.
- Create the form interface: Add a ListView control named listViewStudents and a button named btnRefresh.
- Configure the ListView: Add three columns matching the table fields.
- Write the LoadStudents method: Fetch records from the Students table and populate the ListView.
- Call LoadStudents on form load: This ensures the list displays immediately when the form opens.
When the user clicks the Refresh button, the LoadStudents method runs again to show any new records added since the form opened.
In Tanzania, this skill is directly applicable when developing simple desktop applications for small businesses such as pharmacies, shops, or schools. For example, a shop owner in Arusha can use a form that displays all products from their inventory database, showing fields like ProductName, Quantity, and Price in TSh, allowing them to quickly check stock levels without manually counting items.
Jinsi utakavyotathminiwaTathmini ya mchakato · Tathmini ya matokeo · Tathmini ya maarifa
Tathmini ya mchakato
The student should be able to:
- Write SQL SELECT queries to retrieve data from databases.
- Bind retrieved data to form controls dynamically.
- Format and organize displayed data for clarity and usability.
Tathmini ya matokeo
Records are displayed accurately and in real time on forms.
Tathmini ya maarifa
Methods used: The trainee should explain how to:
- Write SQL queries to fetch records based on user input or predefined conditions.
- Bind data dynamically to various form controls like grids, lists, or labels.
Principles: The student should explain the principle of:
- Data representation for readability and userfriendliness.
- Error handling during database queries and data binding.
Theories: The trainee should explain: The trainee should explain:
- How event-driven programming synchronizes user actions with database interactions.
- The role of SQL in filtering and organizing data for display.
Circumstantial knowledge:
Detailed knowledge about:
- Techniques for designing user-friendly data displays using form controls.
- Configuring and using data-binding tools or libraries in IDEs.
Swali
What property must be set to true to allow a user to select an entire row in a ListView control?
Ingia ili kuwasilisha jibu lako na lihesabiwe katika umahiri wako.
Ingia ili kufanya mazoeziMwalimu
Umekwama? Niulize chochote kuhusu mada hii.
Ingia ili kumuuliza Mwalimu wa AI wa Sonza kuhusu swali hili.
Ingia ili kuulizaMajadiliano
Hakuna maswali bado