Developing and Implementing a Desktop Solution Using an RDMS through an Event Driven Programming Environment

Developing and Implementing a Desktop Solution Using an RDBMS in an Event-Driven Environment

Welcome to one of the most exciting and practical topics in your A2 Software Systems Development course! In this chapter, you will learn how to bring desktop applications to life by connecting a graphical user interface (GUI) to a Relational Database Management System (RDBMS) such as Microsoft SQL Server. Whether you are building an inventory manager, a student booking system, or a point-of-sale terminal, mastering how code talks to a database using an event-driven language (like C#) is an essential developer skill.

Don't worry if this seems a bit daunting at first. We will break down every single concept step-by-step, from handling button clicks to executing secure SQL queries!

1. Understanding Event-Driven Programming

What is Event-Driven Programming?

In traditional, procedural programming, code runs in a strict top-to-bottom sequence determined entirely by the programmer. However, in an event-driven environment, the user is in control! The program sits in a loop, patiently waiting for something to happen—such as a mouse click, a key press, or a window opening. These occurrences are called events.

Think of an event-driven system like a restaurant waiter. The waiter does not force food onto your table immediately upon arrival. Instead, the waiter stands by, waits for you to signal (an event), and then takes your order or brings the bill (the response).

Core Components of the Event Model

Every event-driven interaction consists of three main parts:

• Event Source (Control/Widget): The visual UI element that receives the action, such as a Button, TextBox, or ComboBox.
• Event: The specific action that occurs, such as Click, TextChanged, or FormLoad.
• Event Handler: A dedicated method (block of code) in your program that automatically runs when that specific event triggers.

Quick Review: An event is the trigger (e.g., clicking btnSubmit), while the event handler is the code that responds (e.g., saving user data to the database).

2. The Bridge to Data: Connecting to an RDBMS

What is an RDBMS?

A Relational Database Management System (RDBMS) organizes data into structured tables made of rows (records) and columns (fields/attributes). Tables are linked together using Primary Keys and Foreign Keys to eliminate data duplication.

Connecting Your Application: Connection Strings

Before your desktop app can fetch or save records, it must establish a secure line of communication with the database server. This is achieved using a Connection String.

A connection string is a specialized text string that tells the application:

1. Data Source / Server: Where the database lives (e.g., localhost, .\SQLEXPRESS, or a network IP address).
2. Initial Catalog / Database: The specific name of the database.
3. Security Information: Authentication credentials (e.g., Integrated Security=True for Windows Authentication, or a specific User ID and Password).

Real-World Analogy: A connection string is like an envelope address and key code. It specifies the building address (server), the apartment number (database), and provides the door pass (credentials) to enter.

3. Data Access Architectures: Connected vs Disconnected

When working with database data in an event-driven desktop application, there are two primary approaches:

A. Connected Architecture (Data Readers)

In a connected model, the application keeps an open, active link to the database while retrieving data.

• Uses tools like DataReader (e.g., SqlDataReader).
• It is read-only and forward-only (you read records from top to bottom like a conveyor belt).
• Advantage: Extremely fast and uses minimal computer memory.
• Disadvantage: The connection must remain open during the entire read operation, which can tie up server resources if many users connect simultaneously.

B. Disconnected Architecture (DataSets and DataAdapters)

In a disconnected model, the application connects to the database just long enough to grab a snapshot of data, copies it into local memory, and immediately closes the connection.

• Uses a DataAdapter to pull records into a DataSet or DataTable.
• The user can view, filter, edit, and navigate through the data offline in memory.
• Any changes made can later be synchronized back to the real database in a single batch operation.
• Advantage: Reduces server load and network traffic significantly.
• Disadvantage: Can consume more client memory, and data conflicts can occur if another user modified the database in the meantime.

Key Takeaway: Use a DataReader for fast, lightweight, read-only displays (like populating a dropdown list). Use a DataSet / DataAdapter when you need to manipulate, edit, or hold complex relational data offline.

4. Executing CRUD Operations

The term CRUD represents the four fundamental operations you will perform on a database:

• C - Create: Adding new records using SQL INSERT.
• R - Read: Fetching existing records using SQL SELECT.
• U - Update: Modifying existing records using SQL UPDATE.
• D - Delete: Removing records using SQL DELETE.

Command Execution Methods

When you send a SQL statement to the database via a Command object (such as SqlCommand), you choose a method based on what you expect back:

• ExecuteNonQuery(): Used for INSERT, UPDATE, and DELETE statements. It does not return rows of data; instead, it returns an integer representing the number of rows affected by the query (e.g., \(1\) row inserted).
• ExecuteReader(): Used for SELECT statements when you expect multiple rows and columns to display or process.
• ExecuteScalar(): Used for queries that return a single individual value (one row, one column), such as SELECT COUNT(*) FROM Members or SELECT MAX(Price) FROM Products.

Memory Trick: Scalar means a single value in mathematics—so ExecuteScalar is for when you want exactly one single value back!

5. Securing Your Application: Parameterized Queries

The Threat: SQL Injection

One of the most dangerous database vulnerabilities is SQL Injection. This occurs when an application takes raw, unchecked input typed into a textbox by a user and sticks it directly into a SQL command string (string concatenation).

If a malicious user types SQL commands (like ' OR '1'='1 or ; DROP TABLE Students; --) into a login box, the database might interpret that input as executable commands rather than plain text data, granting unauthorized access or destroying data.

The Solution: Parameters

To completely neutralize SQL injection, always use Parameterized Queries (also called Prepared Statements).

How Parameters Work:
• You write your SQL statement using placeholders (e.g., SELECT * FROM Users WHERE Username = @user AND Password = @pass).
• You add explicit parameter objects to your command, defining their exact data type (e.g., text, integer).
• The database treats the parameter contents strictly as literal values (data), never as executable SQL code, no matter what characters the user types!

Common Mistake to Avoid: Never use string concatenation (such as "SELECT * FROM Users WHERE Name = '" + txtName.Text + "'"). Always use parameters (@Name).

6. UI Data Binding and Controls

Displaying data cleanly in your desktop application is vital for a good user experience. Common UI controls include:

• DataGridView: A grid that displays data in rows and columns. Ideal for displaying full tables, search results, or reports.
• ComboBox / ListBox: Dropdown or list controls used to select a single record from a list (such as picking a CourseID or Department). These controls often use a DisplayMember (what the user sees, e.g., "Computing") and a ValueMember (the underlying primary key, e.g., "COMP101").
• TextBoxes and Labels: Used for single-field data entry or displaying details of a currently selected record.

7. Defensive Programming: Validation & Error Handling

A high-grade desktop solution must be robust: it should never crash when a user enters unexpected data or when a network connection fails.

A. Client-Side Input Validation

Always validate user input in your event handler before sending it to the database. This saves processing time and prevents errors:

• Presence Check: Has the user left a mandatory field blank?
• Type Check: Is the input a valid number where an integer/decimal is required?
• Range Check: Does a value fall within acceptable boundaries (e.g., \(1 \le Age \le 120\))?
• Length Check: Does a string exceed the database column length (e.g., Postcode \(\le 8\) characters)?

B. Exception Handling (Try - Catch - Finally)

Database interactions are prone to run-time errors (e.g., database server is offline, duplicate primary key entered, network cable unplugged). We handle these using structured exception handling:

• try block: Wraps the code that might cause an error (e.g., opening a connection, executing SQL).
• catch block: Catches the exception if an error occurs and handles it gracefully (e.g., displays a helpful error message to the user rather than crashing the program).
• finally block: Always runs, whether an error occurred or not. This is the ideal place to close database connections and release system resources (or you can use a using statement block, which automatically closes and disposes of connections).

Did You Know? Leaving database connections open is one of the most common causes of database slowdowns. Always ensure your connection is closed in a finally block or enclosed within a using block!

Chapter Summary & Key Checklist

Before moving to your practical assessments, make sure you are confident with these core concepts:

• Event-Driven: Code executes in response to user actions (events triggering event handlers).
• Connection String: Tells the application where the database is and how to authenticate.
• Connected (DataReader): Fast, forward-only, requires constant connection.
• Disconnected (DataSet/DataTable): Offline snapshot of data, lower server demand.
• ExecuteNonQuery: Used for INSERT, UPDATE, DELETE (returns rows affected).
• ExecuteScalar: Used for aggregate/single-value queries.
• ExecuteReader: Used for returning multiple rows to read through.
• Parameterized Queries: The standard technique to prevent SQL injection attacks.
• Robustness: Use client-side validation first, and wrap database code in try-catch-finally blocks to keep the system resilient.