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
Performing Basic CRUD Operations Using SQL Queries
CRUD stands for Create, Read, Update, and Delete — the four fundamental operations that allow applications to manage data in a database. Every database-driven application, from a student registration system to a shop inventory manager, relies on these four operations to store, retrieve, modify, and remove data. In event-driven programming with C# and SQLite, these operations are performed using SQL queries that are executed in response to user actions, such as clicking buttons.
Create (INSERT)
The Create operation adds new records to a database table. In SQL, this is done using the INSERT INTO statement. When a user fills in a form and clicks a Save button, the application executes an INSERT query to store the new data in the database.
Read (SELECT)
The Read operation retrieves data from the database. The SELECT statement is used to fetch records. Retrieved data is typically displayed in a ListView or DataGridView so users can see existing records.
Update (UPDATE)
The Update operation modifies existing records. The UPDATE statement changes values in specific fields of a record, identified by a unique ID.
Delete (DELETE)
The Delete operation removes records from the database permanently. The DELETE FROM statement removes a record based on its ID.
This example demonstrates how to perform CRUD operations in a C# WinForms application using SQLite. We will use a database handler class called SQLiteHelper to manage all database interactions.
Step 1: Setting Up the SQLiteHelper Class
The database handler manages connections and executes SQL queries. Here is how to create and insert data:
using System;
using System.Data.SQLite;
using System.IO;
public class SQLiteHelper
{
private string connectionString = "Data Source=school.db;Version=3;";
public SQLiteHelper()
{
CreateDatabaseAndTable();
}
private void CreateDatabaseAndTable()
{
if (!File.Exists("school.db"))
{
SQLiteConnection.CreateFile("school.db");
}
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
string sql = "CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Age INTEGER NOT NULL)";
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
}
}
}
Step 2: Create — Inserting Data
To add a new student record, use the INSERT INTO SQL statement:
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();
}
}
}
The @name and @age are parameters that prevent SQL injection attacks. When the user clicks the Submit button, the event handler calls this method to save the data.
Step 3: Read — Retrieving Data
To display all registered students, use the SELECT statement:
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 retrieved data is then displayed in a ListView control on the form.
Step 4: Update — Modifying Data
To update a student's name or age, use the UPDATE SQL statement:
public void UpdateUser(int studId, string newName, int newAge)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
string query = "UPDATE Users SET Name = @name, Age = @age WHERE Id = @studid";
using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
{
cmd.Parameters.AddWithValue("@name", newName);
cmd.Parameters.AddWithValue("@age", newAge);
cmd.Parameters.AddWithValue("@studid", studId);
cmd.ExecuteNonQuery();
}
}
}
The WHERE Id = @studid clause ensures that only the specific record is updated.
Step 5: Delete — Removing Data
To delete a student record, use the DELETE FROM SQL statement:
public void DeleteUser(int studId)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
string query = "DELETE FROM Users WHERE Id = @studid";
using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
{
cmd.Parameters.AddWithValue("@studid", studId);
cmd.ExecuteNonQuery();
}
}
}
Before deleting, the application should display a confirmation dialog to prevent accidental deletion.
| Operation | SQL Statement | Example |
|---|---|---|
| Create | INSERT INTO | INSERT INTO Users (Name, Age) VALUES ('John', 15) |
| Read | SELECT | SELECT * FROM Users |
| Update | UPDATE | UPDATE Users SET Age = 16 WHERE Id = 1 |
| Delete | DELETE FROM | DELETE FROM Users WHERE Id = 1 |
- Always use parameterized queries (
@parameter) instead of directly inserting user input into SQL strings to prevent SQL injection attacks. - The primary key (Id) is used to uniquely identify records for update and delete operations.
- After performing any CRUD operation, refresh the display to show the current state of the database.
- Validate user input (for example, ensure age is a valid number) before executing SQL queries.
In Tanzania, small businesses such as dukas (small shops) and schools can use CRUD-powered applications to manage inventory or student records. For example, a primary school in Dodoma could use a simple desktop application to register new students (Create), view the class list (Read), update a student's class when they progress to the next level (Update), or remove a student's record when they transfer to another school (Delete). This replaces paper-based record-keeping, making it faster to find information and reducing the risk of losing important data.
Jinsi utakavyotathminiwaTathmini ya mchakato · Tathmini ya matokeo · Tathmini ya maarifa
Tathmini ya mchakato
The student should be able to:
- Write and execute SQL queries for CRUD operations.
- Link CRUD operations toevent handlers in an event-driven program.
Tathmini ya matokeo
CRUD operations using SQL are performed accurately to meet user needs.
Tathmini ya maarifa
Methods used: The trainee should explain how to:
- Write SQL queries for basic CRUD operations.
- Use event-driven programming to integrate CRUD functionality.
Principles: The student should explain the principle of:
- Data manipulation and its importance in application functionality.
- SQL as a language for interacting with relational databases.
Theories: The trainee should explain: The trainee should explain:
- How CRUD operations enable dynamic and interactive application behaviour.
Circumstantial knowledge:
Detailed knowledge about:
- Various data types used in database tables and their application.
- Designing user-friendly forms to trigger CRUD operations.
Swali
What does the acronym CRUD stand for in database operations?
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