A database-driven advising and appointment scheduling application built with C# Windows Forms and Microsoft SQL Server.
The application provides a graphical front end for students/advisees to log in, view advisor availability, schedule appointments, and manage existing appointments. The SQL Server database serves as the backend and uses stored procedures to handle application operations and database interactions.
This project was completed as a team final project for B321: Database Driven Application Development. The project was designed to demonstrate how a relational database can be integrated with a C# application to create a complete, database-driven software system.
The goal of this project was to create a cohesive application consisting of two primary components:
- A SQL Server backend responsible for storing and processing application data.
- A C# Windows Forms frontend responsible for providing users with an interface for interacting with the database.
The application models a hypothetical university advising system where students can log in and schedule appointments with academic advisors.
The database contains information about:
- Advisees
- Advisors
- Appointments
- Appointment types
- Meeting modalities
- Buildings
- Locations
- Appointment time slots
The frontend communicates with the SQL Server database through stored procedures, allowing user actions in the application to result in database operations.
The application provides an advisee login interface.
Users enter their username and password, which are passed from the C# frontend to a SQL Server stored procedure.
The database validates the credentials and returns the user's information to the application.
The application then stores the authenticated user's information in a session object for use throughout the application.
Users can create new advising appointments through the Windows Forms interface.
When creating an appointment, the application collects information such as:
- Advisor
- Location
- Modality
- Appointment type
- Date
- Start time
- Duration
- Advisee
The selected information is then passed to the SQL Server backend through the CreateScheduledAppointment stored procedure.
Existing appointments can be rescheduled by providing a new date and start time.
The SQL backend processes the change through the RescheduleAppointment stored procedure.
Appointments can be canceled through the application.
The CancelAppointment stored procedure verifies that the appointment exists before removing it from the database.
The application can retrieve existing appointment information and use it to determine advisor availability.
The database provides a GetAvailability stored procedure that returns scheduled appointments and their associated timing information.
This allows the frontend to use database information when determining available appointment times.
The application includes logic for detecting overlapping appointments before creating a new appointment.
Before an appointment is created, the C# application calls the database's overlap-checking procedure and passes information about the proposed appointment, including:
- Location
- Modality
- Advisor
- Advisee
- Appointment type
- Date
- Start time
- Duration
This helps prevent conflicting appointments from being scheduled.
The project uses a basic client/database architecture.
C# Windows Forms Application
|
v
Database Connection
|
v
SQL Server Backend
|
v
Stored Procedures
|
v
Relational Tables
The frontend is responsible primarily for:
- User interaction
- Collecting input
- Displaying information
- Calling stored procedures
- Handling application state
- Presenting database results
The backend is responsible primarily for:
- Data storage
- Relational integrity
- Database operations
- Appointment management
- Login processing
- Availability queries
- Conflict detection
This separation demonstrates how a database can act as the backend of a desktop application.
The SQL Server database was designed around the relationships involved in an academic advising system.
Stores information about students/advisees, including:
- Advisee ID
- Username
- Password
- First name
- Last name
- Phone
- Address
Each advisee receives a unique identifier.
Stores information about academic advisors, including:
- Advisor ID
- Username
- Password
- First name
- Last name
- Phone
- Default location
Stores appointment records and connects advisees with advisors.
Appointment information includes:
- Appointment ID
- Location
- Modality
- Advisor
- Advisee
- Appointment type
- Appointment date
- Start time
- Duration
- Description
Stores the different types of appointments that can be scheduled.
Stores information about how an appointment is conducted, such as the meeting type or modality.
Stores building and address information.
Stores specific appointment locations and associates those locations with buildings.
Provides information related to available scheduling periods.
The database uses primary keys and foreign keys to establish relationships between entities.
For example:
Buildings
|
+---- Locations
Advisors
|
+---- ScheduledAppointments
Advisees
|
+---- ScheduledAppointments
AppointmentType
|
+---- ScheduledAppointments
Modality
|
+---- ScheduledAppointments
Locations
|
+---- ScheduledAppointments
This relational structure prevents appointment information from having to repeatedly store the same advisor, location, or appointment-type information.
Instead, records reference the appropriate entities through foreign keys.
A major part of the project was moving database operations into SQL Server stored procedures.
The stored procedure script is contained in:
B321_StoredProceduresScript.sql
The project includes procedures for several application operations.
Authenticates an advisee using their username and password and returns their identifying information.
Creates a new appointment using the information supplied by the frontend.
Verifies that an appointment exists and removes it from the database.
Updates the date and start time of an existing appointment.
Retrieves advisee records for use by the frontend.
Retrieves advisor information.
Retrieves available appointment modalities.
Retrieves available appointment types.
Retrieves location information and joins it with building information.
Retrieves existing appointments that can be used by the frontend when determining availability.
Retrieves appointments associated with the current user.
Checks whether a proposed appointment conflicts with existing appointments.
Provides overlap checking specifically for appointment rescheduling.
The project was designed so that the frontend interacts with the database primarily through stored procedures.
Instead of placing all database operations directly into the C# application, SQL operations are encapsulated within the SQL Server backend.
This provides several advantages:
- Centralizes database logic
- Reduces duplicated SQL code
- Provides a defined interface between frontend and backend
- Makes database operations easier to maintain
- Allows database-side validation and processing
- Provides a foundation for improving database security
The original project documentation specifically identified database encapsulation through stored procedures as a security goal.
The frontend is implemented as a Windows Forms application using C#.
The project uses the Microsoft.Data.SqlClient library to establish connections to SQL Server and execute stored procedures.
The application contains several classes responsible for different parts of the system.
Examples include:
clsAdviseeclsAdvisorsclsAppointmentTypeclsDBUtilclsLocationclsModalityclsNewAppointmentclsScheduledAppointmentsclsSessionInfo
The application also contains Windows Forms for different user interactions, including:
- Login
- Appointment scheduling
- Appointment rescheduling
- Schedule overview
A typical appointment-creation workflow looks like this:
User selects appointment information
|
v
C# Windows Forms
|
v
Validate user selections
|
v
Check for appointment conflicts
|
v
SQL Server stored procedure
|
v
Create appointment record
|
v
Database confirmation
This approach demonstrates how a desktop application can use a relational database as its backend while keeping application logic and database logic separated.
The C# portion of the project uses classes to represent different entities and responsibilities.
For example:
clsAdviseerepresents advisee-related information.clsAdvisorsrepresents advisor information.clsLocationrepresents appointment locations.clsModalityrepresents appointment modalities.clsAppointmentTyperepresents appointment types.clsNewAppointmentrepresents appointment information being created.clsScheduledAppointmentsrepresents scheduled appointment information.clsSessionInfomaintains information about the currently logged-in user.clsDBUtilprovides database connection functionality.
This object-oriented structure allowed the application to work with meaningful objects rather than keeping all application state in a single form.
This project was particularly valuable because it required multiple technologies to work together rather than simply writing isolated SQL or C# programs.
The biggest lesson was learning how a database becomes part of a larger software system.
The database was not simply a collection of tables and queries. It served as the backend for an actual application.
This required understanding how:
User Interface
↓
Application Logic
↓
Database Operations
↓
Stored Procedures
↓
Relational Data
work together.
I gained practical experience connecting a C# application to SQL Server using Microsoft.Data.SqlClient.
The application creates database connections, passes parameters to stored procedures, executes commands, and processes returned data.
This project gave me significant experience designing and using SQL Server stored procedures.
I learned how stored procedures can provide a structured interface between an application and a database.
The project reinforced concepts such as:
- Primary keys
- Foreign keys
- Entity relationships
- Normalization
- Referential integrity
- Data modeling
- Relational queries
The project also introduced the challenge of maintaining state between different Windows Forms.
The clsSessionInfo class was used to maintain information about the currently logged-in user so that appointment operations could be associated with the correct advisee.
The application includes exception handling around database operations so that SQL errors can be caught and presented to the user instead of causing the application to terminate unexpectedly.
- C#
- Windows Forms
- Visual Studio
- SQL Server Management Studio (SSMS)
- T-SQL
- Stored Procedures
- Git
- GitHub
To run the project locally, you will need:
- Windows
- Visual Studio
- SQL Server
- SQL Server Management Studio (SSMS)
- Git
The repository's existing documentation identifies both SQL Server Management Studio and Visual Studio as required development tools.
git clone https://github.com/IBickCoding/B321_SQL_CSharp_Project.git
cd B321_SQL_CSharp_Project
Install Microsoft SQL Server and SQL Server Management Studio.
Open:
B321_Group1_FinalCreateLoadScript.sql
in SQL Server Management Studio.
Execute the script to create and populate the database.
The script creates the application's relational tables and inserts mock data for the hypothetical university advising system.
Open:
B321_StoredProceduresScript.sql
in SQL Server Management Studio.
Execute the script after the database has been created.
This creates the stored procedures required by the C# application.
Open:
AppointmentSchedulerApp/AppointmentSchedulerApp.sln
in Visual Studio.
The application requires a valid SQL Server connection string.
Update the application's database configuration so that it points to your local SQL Server instance and the database created by the SQL scripts.
Do not commit credentials or sensitive connection information to GitHub.
Build the solution in Visual Studio.
Run the application.
The application should launch with the login interface.
B321_SQL_CSharp_Project/
|
+-- AppointmentSchedulerApp/
| |
| +-- AppointmentSchedulerApp.sln
| +-- AppointmentSchedulerApp.csproj
| |
| +-- Program.cs
| |
| +-- LoginForm.cs
| +-- LoginForm.Designer.cs
| +-- LoginForm.resx
| |
| +-- AppSchedular.cs
| +-- AppSchedular.Designer.cs
| +-- AppSchedular.resx
| |
| +-- frmReschduleForm.cs
| +-- frmReschduleForm.Designer.cs
| +-- frmReschduleForm.resx
| |
| +-- frmScheduleOverview.cs
| +-- frmScheduleOverview.Designer.cs
| +-- frmScheduleOverview.resx
| |
| +-- clsAdvisee.cs
| +-- clsAdvisors.cs
| +-- clsAppointmentType.cs
| +-- clsDBUtil.cs
| +-- clsLocation.cs
| +-- clsModality.cs
| +-- clsNewAppointment.cs
| +-- clsScheduledAppointments.cs
| +-- clsSessionInfo.cs
|
+-- B321_Group1_FinalCreateLoadScript.sql
|
+-- B321_StoredProceduresScript.sql
|
+-- B321_Group1_FinalERD.png
|
+-- B321_Group1_FinalAssumptions.docx
|
+-- B321_Group1_FinalContributions.docx
|
+-- B321_Group1_FinalTimeline.docx
|
+-- README.md
The project includes an Entity Relationship Diagram:
B321_Group1_FinalERD.png
The ERD documents the relationships between the major database entities used by the application.
The database design includes relationships between:
- Advisees
- Advisors
- Scheduled appointments
- Appointment types
- Modalities
- Buildings
- Locations
The project uses a hypothetical university dataset populated with mock data.
This allowed the team to create meaningful relationships between:
- Students
- Advisors
- Locations
- Appointment types
- Appointment modalities
- Scheduled appointments
The purpose of the mock dataset was to provide enough realistic relationships for the Windows Forms application to demonstrate real database operations.
This was a team project completed for B321: Database Driven Application Development.
- Ian Bickford
- Shaun Poole
- Roddey Sims
The SQL creation script identifies these three developers as the project programmers.
Course: B321 — Database Driven Application Development
Project Type: Final Project
Development: Team
Frontend: C# Windows Forms
Backend: Microsoft SQL Server
Database Access: Stored Procedures / Microsoft.Data.SqlClient
IDE: Visual Studio
The assignment required the team to create a database backend and connect it to a C# Windows Forms frontend to create a cohesive database-driven application.