Mada za sehemu hiiCreating database connectivity options and reportingMada 4
- Establishing a connection of event-driven programs to a database
- Performing basic CRUD operations using SQL queries
- Showing database records on forms
- Generating reports from database data
Connecting Event-Driven Programs to a Database
In event-driven programs, the flow of the program is determined by user actions such as button clicks, mouse movements, or keyboard inputs. However, most real applications need to store and retrieve data persistently. This is where database connectivity comes in. By connecting an event-driven program to a database, you can save user inputs (like student names and ages) to a database file and retrieve them later, even after closing the application. This section teaches you how to connect a C# Windows Forms application to a SQLite database to perform basic data operations.
When building applications like a Student Registration System, you need to:
- Store data permanently: Data saved in variables is lost when the program closes. A database keeps data even after the application ends.
- Retrieve stored data: Load previously saved records to display in your application.
- Manage data efficiently: Update, delete, or search through records as needed.
When building a C# WinForms application that connects to SQLite, there are four main steps:
- Install and configure SQLite — Set up the database package in Visual Studio.
- Set up the user interface (UI) — Create the form with text boxes and buttons.
- Create a database handler class — Write a class that manages all database operations.
- Link GUI events with database operations — Connect button clicks to database actions.
Before writing code, you need to add SQLite support to your Visual Studio project using NuGet Package Manager:
- In Visual Studio, go to Project → Manage NuGet Packages.
- Search for Microsoft.Data.SQLite or System.Data.SQLite.
- Click Install to add the package to your project.
This package allows your C# program to communicate with SQLite databases.
Using WinForms, design a simple form with:
- Two TextBoxes: one for entering the student's name and another for age.
- Two Labels: to identify each text box.
- One Button: labeled "Submit" or "Save" to save the data.
- One ListView: to display the registered students.
The interface allows users to input data and view saved records. For example, a form might have a text box for "Name", a text box for "Age", and a "Save" button.
The database handler is a class that manages all interactions with the database. It handles creating the database file, connecting to it, and performing operations like inserting and retrieving records.
Creating the Database File
In the constructor of your helper class, check if the database file exists. If not, create it:
if (!File.Exists("school.db"))
{
SQLiteConnection.CreateFile("school.db");
}
Creating the Table
When the database file is created, also create the table to store your data:
string sql = "CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Age INTEGER NOT NULL)";
Inserting Data
To save a student's name and age to the database, use the INSERT SQL command with parameters to prevent errors:
public void InsertUser(string name, int age)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
string sql = "INSERT INTO Users (Name, Age) VALUES (@name, @age)";
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@age", age);
cmd.ExecuteNonQuery();
}
}
}
Retrieving Data
To display all saved students, use the SELECT command:
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;
}
After creating the form and the database handler, connect the Submit button to save data:
- Double-click the Submit button to generate the click event handler.
- In the event handler, get the values from the text boxes.
- Validate that the age is a valid number.
- Call the
InsertUsermethod from your database handler. - Display a confirmation message.
private void button1_Click(object sender, EventArgs e)
{
string name = textBox1.Text.Trim();
string ageText = textBox2.Text.Trim();
// Validate that age is a number
if (!int.TryParse(ageText, out int age))
{
MessageBox.Show("Please enter a valid numeric age!", "Input Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
// Insert into database
dbHelper.InsertUser(name, age);
MessageBox.Show("User inserted successfully!");
// Refresh the ListView to show new data
LoadUsers();
}
Consider a school where Ms. Mary, the headmistress, needs to register students and track their ages. The system captures the student's name and age through a form and stores it in a SQLite database.
Step 1: Design the form with text boxes for Name and Age, a Save button, and a ListView to display registered students.
Step 2: Create the SQLiteHelper class with methods to create the database, insert a student, and retrieve all students.
Step 3: When the Save button is clicked, the program:
- Takes the name from textBox1 and age from textBox2
- Validates the age input
- Calls
dbHelper.InsertUser(name, age)to save to the database - Calls
LoadUsers()to refresh the ListView with the updated list
Step 4: The ListView displays each student with their ID, Name, and Age in separate columns.
A small pharmacy shop in Dar es Salaam can use this concept to build a desktop application that stores medicine inventory. When a new medicine arrives, the shopkeeper enters the medicine name, price, and quantity using a form connected to a database. The system can later retrieve this information to check stock levels or update quantities, replacing handwritten record books with a reliable digital system.
Jinsi utakavyotathminiwaTathmini ya mchakato · Tathmini ya matokeo · Tathmini ya maarifa
Tathmini ya mchakato
The student should be able to:
- Configure database drivers and establish a connection in the programming environment.
Tathmini ya matokeo
A stable and secure connection to the database is created.
Tathmini ya maarifa
Methods used: The trainee should explain how to:
- Set up database drivers and integrate them into the development environment.
Principles: The student should explain the principle of:
- Database connectivity, including connection pooling and driver configuration.
- Secure access to databases, including authentication and permissions.
Theories: The trainee should explain: The trainee should explain:
- Concepts on handling exceptions during database connections and operations to avoid application crashes
- How database drivers (e.g., JDBC, ODBC) bridge the application and the database.
Circumstantial knowledge:
Detailed knowledge about:
- Managing connections in applications and handling multiple concurrent users.
- Configuring timeouts and retries to maintain connection stability.
Swali
What is the main reason for connecting an event-driven program to a database?
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