Contents
Introduction
This article discusses about a simple windows database application developed using C#. It uses WinForms on the front-end (UI) and MS access as back-end (DB). The application is named “Social Club”, as it is to create and manage club member’s information.
The application User interface (UI) comprises of few screens (Login, Registration, Manage). Registration screen is to create/register new club membership. Mange screen provides options to Edit, Delete, Search, Print club members information & export data to excel. This article will focus on demonstrating how some topics are applied in the sample demo project; topics like 3-Tier architecture, Connection to access database, Creating basic SQL statements for access database, Writing ADO.NET code, Implementing CURD operation using WinForms and C#.NET.
Software environment
Application is developed in the following software environment
- Development environment : Visual Studio.NET 2010
- Framework : .NET Framework 4.0
- User Interface : Windows Forms
- Programming language : C#.NET
- Data Access : ADO.NET
- Database : MS Access 2010
- Code analysis tool : Stylecop
Pre-Requisite
You need to have the following tools installed in your machine to run the sample application.
- Microsoft Visual Studio 2010
- Microsoft Access 2010
Layered architecture
Presentation Layer
Presentation (Desktop) Layer is the topmost layer contains the User Interface of the application. This layer is responsible for delivery of information to the user and to the application. It contains Forms (UI) for user interaction and code-behind (UI Logic) to handle the events.
Data Layer
Data layer is the middle-layer and it contains the business service methods, data access logic, data model and SQL scripts
Business service
Business service contains method to communicate with the data access. It acts as a bridge between Presentation logic and data access logic. This layer is responsible for calling the data access methods and delivering the result to presentation.
Data Model
The data model contains properties that reflect the data; this is often called entity/business objects.
SQL Scripts
SQL Scripts contains SQL statements that carry the database logic.
Data access
Data access typically contains the logic for accessing the database data and is generally implemented in a separate class library project. In the demo project Business Service and Data Access are implemented in the same class library.
Database Layer
Database layer contains Database with table structure & data.
Project Structure
Solution explorer shows how the directories are structured within the projects. Solution contains two projects.
Desktop (Windows Project)
Desktop project which is a Windows Forms Application project that contains Forms (UI) and corresponding code-behind (.CS) files. Code-behind files contains the UI logic to handle the events.
Data (Library Project)
Data project is a Class library project that contains the database logic (SQL scripts), data access logic (ADO.NET code), and data model (Business entity). All service methods and data related logic resides in the same class library. Preferably, each of these will be in separate class library project in a multi-tier architecture system and the library projects will be referenced in whichever project it is necessary.
Organizing the folders, files, projects in this fashion separates the code into distinct sections such that each section addresses a separate concern. This is a design principle for separating the program into sections. This make the code reusable, readable, flexible to manage and maintain.
Creating the database
Social club application requires only a single table to store the club member information. Below displayed image shows the database in the design view. It show the table on the left panel and details (Field Name, Data Type, Description) on the right panel. For each Field Properties can be set through the "General" Tab shown below on the right panel.
Here are the steps to create a database in MS Access 2010
- Open the MS Access and click on the “Blank database” template (This opens a new blank database with a default table “Table1” on the left panel.
- Right click on the table and go to the design view (A small window will prompt to save the table)
- Give the name to the table (in our case, the table name is “ClubMember”) and click save.
- Create the fields (columns) with necessary data type, properties and description.
- 5. Click the “Save database as” option in the “File” menu and save the database by giving an appropriate name (In our case, the database name is “SocialClub”). Database is saved as {filename}.accdb file.
Setting up the project
I presume that you are aware of the visual studio environment to some extent. Here I’ll list down the steps to set up the Desktop and Data project.
- Open visual studio and Select File -> New -> Project
- In the new project window, Select “Visual studio solution” from “Other project types” templates
- Give the project name as “SocialClub” and click OK.
- Right click the solution and select Add -> New project
- In the new project window, From the templates Select Visual C# -> Windows -> Windows Forms Application
- Give the project name “SocialClub.Desktop” and click OK
- Again, Right click the solution and select Add -> New project
- In the new project window, From the templates Select Visual C# -> Windows -> Class Library
- Give the project name “SocialClub.Data” and click OK
- In the “SocialClub.Desktop” project, right click on References and Select Add reference
- From the Add Reference window, Select “Projects” tab -> SocialClub.Data Project and Click OK
- Press CTRL+SHFT+B and check if the application builds.
- Copy the database file (SocialClub.accdb) to the bin directory located in the application startup path
Now, you have setup the project in visual studio and ready to go with the coding. Below shown is the visual studio screen shot for Step 11
Implementing the Data Layer
Named constants (Enums)
A set of named constants (enum) is created for the fields (Health status, Marital Status, Occupation). These fields in the database will contain the numeric equivalent of enum value and these fields in the data model will be of respective enum type. Also these enumerated values (string equivalent) are shown in dropdown list in the UI for selection.
Class Diagram
Sample code
public enum HealthStatus
{
Excellent = 1,
Good,
Average,
Poor
}
public enum MaritalStatus
{
Married = 1,
Single
}
public enum Occupation
{
Doctor = 1,
Engineer,
Professor,
}
Data model (Entity)
We require business entity/model in the project which will have the properties that reflect the fields as in the database table. This entity will be used as data transfer object (DTO) simply to transfer the data between the layers. Below shown is the data model created for the sample application.
Class Diagram
Code
public class ClubMemberModel
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime DateOfBirth { get; set; }
public Occupation Occupation { get; set; }
public MaritalStatus MaritalStatus { get; set; }
public HealthStatus HealthStatus { get; set; }
public decimal Salary { get; set; }
public int NumberOfChildren { get; set; }
}
Connection string
Connection string contains information about the provider and the data source. The ACE OLDB 12.0 provider is used to connect to Access database. This connection string is passed into data access code for the ADO.NET objects to initiate a connection with the database.
Storing the connection string
Generally, Database connection strings are stored in CONFIG file for ease of configuration and are accessed from there. To store the connection string in Configuration file, follow the steps
- Right click on the “SocialClub.Desktop” project, Select Add -> New item
- From “Add New Item” window, Select Visual C# items -> General -> Application configuration file and click OK (app.config will be added to project)
- In the app.config, add the following code
<configuration>
<connectionStrings>
<add name="SocialClubDBConnection"
connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=|DataDirectory|\SocialClub.accdb;Persist Security Info=True"
providerName="System.Data.OleDb" />
</connectionStrings>
</configuration>
Accessing the connection string
The ConfigurationManager class provides access to configuration files. The connection string from the app.config can be accessed using the ConfigurationManager. In the demo project, this done by creating a abstract class called ConnectionAccess that contains a property called ConnectionString Which returns the connection string stored in the config file. This code is shown below:
using System.Configuration;
public abstract class ConnectionAccess
{
protected string ConnectionString
{
get
{
return ConfigurationManager
.ConnectionStrings["SocialClubDBConnection"]
.ToString();
}
}
}
Creating the SQL statements for MS Access
As the application includes CURD operation, we need the following SQL statements which can be executed against the access database
- SELECT
- SELECT (by input parameters)
- INSERT
- UPDATE
- DELETE
All the required SQL statements are added to a static class as read-only strings and are accessed.
Class diagram
Code : SQL scripts in static class
public static class Scripts
{
public static readonly string sqlGetClubMemberById = "Select" +
" Id, Name, DateOfBirth, Occupation, MaritalStatus, " +
"HealthStatus, Salary, NumberOfChildren" +
" From ClubMember Where Id = @Id";
public static readonly string SqlGetAllClubMembers = "Select" +
" Id, Name, DateOfBirth, Occupation, MaritalStatus, " +
"HealthStatus, Salary, NumberOfChildren" +
" From ClubMember";
public static readonly string SqlInsertClubMember = "Insert Into" +
" ClubMember(Name, DateOfBirth, Occupation," +
" MaritalStatus, HealthStatus, Salary, NumberOfChildren)" +
" Values(@Name, @DateOfBirth, @Occupation, " +
"@MaritalStatus, @HealthStatus, @Salary, @NumberOfChildren)";
public static readonly string SqlSearchClubMembers = "Select " +
" Id, Name, DateOfBirth, Occupation, MaritalStatus, " +
"HealthStatus, Salary, NumberOfChildren" +
" From ClubMember Where (@Occupation Is NULL OR @Occupation = Occupation) {0}" +
" (@MaritalStatus Is NULL OR @MaritalStatus = MaritalStatus)";
public static readonly string sqlUpdateClubMember = "Update ClubMember " +
" Set [Name] = @Name, [DateOfBirth] = @DateOfBirth, " +
"[Occupation] = @Occupation, [MaritalStatus] = @MaritalStatus, " +
" [HealthStatus] = @HealthStatus, [Salary] = @Salary, " +
"[NumberOfChildren] = @NumberOfChildren Where ([Id] = @Id)";
public static readonly string sqlDeleteClubMember = "Delete From ClubMember Where (Id = @Id)";
}
Writing the Data access Using ADO.NET
ADO.NET is one among the data access technologies that comes with .NET framework. It contains set of classes that provides data access service to .NET application. I will take you through some basics of ADO.NET objects used for database communication and steps to load and modify the data.
ADO.NET objects
Connection Object
The Connection object is to create a connection between the application and database.
Command Object
The Command object is used to store the SQL statements that need to be executed against the database. Command object can execute SELECT, INSERT, UPDATE, DELETE, Stored Procedures.
DataReader Object
The DataReader object is to retrieve data from the database. The DataReader object is forward-only read-only type of cursor that provides faster way of retrieving records from the database.
DataAdapter Object
The DataAdapter object is to retrieve data from the database and populate the data in a DataTable or DataSet. DataSet is an in-memory representation of a database contains DataTable collection. DataAdapter object uses the Fill method to populate the data in DataSet. It uses the connection object and command object to automatically open the connection, execute the command and close the connection.
Steps to Load the data using DataAdapter
- Create an instance of
DataAdapter object
- Assign a new instance of Command Object to DataAdapter
SelectCommand property
- Set the connection and Command property of
SelectCommand
- Add (if any) parameters to the
ParameterCollection of the SelectCommand
- Invoke the Fill method of the
DataAdapter object and pass to it the DataTable object.
Using these ADO.NET objects is relative to the data sources. MS Access is an OLE DB datasource. When connecting to OLEDB datasource, we need to use the OleDbConnection and OleDdCommand in the System.Data.OleDb namespace.
Sample code
OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter()
oleDbDataAdapter.SelectCommand = new OleDbCommand();
oleDbDataAdapter.SelectCommand.Connection = new OleDbConnection(this.ConnectionString);
oleDbDataAdapter.SelectCommand.CommandType = CommandType.Text;
oleDbDataAdapter.SelectCommand.CommandText = Scripts.SqlGetAllClubMembers;
oleDbDataAdapter.Fill(dataTable);
Steps to modify the data using Command object
- Create a new instance of Command Object
- Set the connection property of Command object
- Set the command type property of the Command Object as Text
- Place the SQL statement into Command Object by setting the Command Text property
- Add the input parameters to the Command Object’s parameter collection property
- Open the connection
- Execute the command by invoking the
ExecuteNonQuery method on the command object
- Close the connection
Sample code
OleDbCommand dbCommand = new OleDbCommand()
dbCommand.Connection = new OleDbConnection(this.ConnectionString);
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = Scripts.sqlUpdateClubMember;
dbCommand.Parameters.AddWithValue("@Name", clubMember.Name);
dbCommand.Parameters.AddWithValue("@Id", clubMember.Id);
dbCommand.Connection.Open();
var rowsAffected = dbCommand.ExecuteNonQuery();
dbCommand.Connection.Close();
While adding the input parameters to parameter collection, make sure you add the parameters in the same order as in the SQL statement.
Class Diagram and Data Access Code
Data Access code
using System;
using System.Data;
using System.Data.OleDb;
using John.SocialClub.Data.DataModel;
using John.SocialClub.Data.Sql;
public class ClubMemberAccess : ConnectionAccess, IClubMemberAccess
{
public DataTable GetAllClubMembers()
{
DataTable dataTable = new DataTable();
using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter())
{
oleDbDataAdapter.SelectCommand = new OleDbCommand();
oleDbDataAdapter.SelectCommand.Connection = new OleDbConnection(this.ConnectionString);
oleDbDataAdapter.SelectCommand.CommandType = CommandType.Text;
oleDbDataAdapter.SelectCommand.CommandText = Scripts.SqlGetAllClubMembers;
oleDbDataAdapter.Fill(dataTable);
}
return dataTable;
}
public DataRow GetClubMemberById(int id)
{
DataTable dataTable = new DataTable();
DataRow dataRow;
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter())
{
dataAdapter.SelectCommand = new OleDbCommand();
dataAdapter.SelectCommand.Connection = new OleDbConnection(this.ConnectionString);
dataAdapter.SelectCommand.CommandType = CommandType.Text;
dataAdapter.SelectCommand.CommandText = Scripts.sqlGetClubMemberById;
dataAdapter.SelectCommand.Parameters.AddWithValue("@Id", id);
dataAdapter.Fill(dataTable);
dataRow = dataTable.Rows.Count > 0 ? dataTable.Rows[0] : null;
return dataRow;
}
}
public DataTable SearchClubMembers(object occupation, object maritalStatus, string operand)
{
DataTable dataTable = new DataTable();
using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter())
{
oleDbDataAdapter.SelectCommand = new OleDbCommand();
oleDbDataAdapter.SelectCommand.Connection =
new OleDbConnection(this.ConnectionString);
oleDbDataAdapter.SelectCommand.CommandType = CommandType.Text;
oleDbDataAdapter.SelectCommand.CommandText =
string.Format(Scripts.SqlSearchClubMembers, operand);
oleDbDataAdapter.SelectCommand.Parameters.AddWithValue(
"@Occupation",
occupation == null ? DBNull.Value : occupation);
oleDbDataAdapter.SelectCommand.Parameters.AddWithValue(
"@MaritalStatus",
maritalStatus == null ? DBNull.Value : maritalStatus);
oleDbDataAdapter.Fill(dataTable);
}
return dataTable;
}
public bool AddClubMember(ClubMemberModel clubMember)
{
using (OleDbCommand oleDbCommand = new OleDbCommand())
{
oleDbCommand.Connection = new OleDbConnection(this.ConnectionString);
oleDbCommand.CommandType = CommandType.Text;
oleDbCommand.CommandText = Scripts.SqlInsertClubMember;
oleDbCommand.Parameters.AddWithValue("@Name", clubMember.Name);
oleDbCommand.Parameters.AddWithValue("@DateOfBirth",
clubMember.DateOfBirth.ToShortDateString());
oleDbCommand.Parameters.AddWithValue("@Occupation",
(int)clubMember.Occupation);
oleDbCommand.Parameters.AddWithValue("@MaritalStatus",
(int)clubMember.MaritalStatus);
oleDbCommand.Parameters.AddWithValue("@HealthStatus",
(int)clubMember.HealthStatus);
oleDbCommand.Parameters.AddWithValue("@Salary", clubMember.Salary);
oleDbCommand.Parameters.AddWithValue("@NumberOfChildren",
clubMember.NumberOfChildren);
oleDbCommand.Connection.Open();
var rowsAffected = oleDbCommand.ExecuteNonQuery();
oleDbCommand.Connection.Close();
return rowsAffected > 0;
}
}
public bool UpdateClubMember(ClubMemberModel clubMember)
{
using (OleDbCommand dbCommand = new OleDbCommand())
{
dbCommand.Connection = new OleDbConnection(this.ConnectionString);
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = Scripts.sqlUpdateClubMember;
dbCommand.Parameters.AddWithValue("@Name", clubMember.Name);
dbCommand.Parameters.AddWithValue("@DateOfBirth",
clubMember.DateOfBirth.ToShortDateString());
dbCommand.Parameters.AddWithValue("@Occupation",
(int)clubMember.Occupation);
dbCommand.Parameters.AddWithValue("@MaritalStatus",
(int)clubMember.MaritalStatus);
dbCommand.Parameters.AddWithValue("@HealthStatus",
(int)clubMember.HealthStatus);
dbCommand.Parameters.AddWithValue("@Salary", clubMember.Salary);
dbCommand.Parameters.AddWithValue("@NumberOfChildren",
clubMember.NumberOfChildren);
dbCommand.Parameters.AddWithValue("@Id", clubMember.Id);
dbCommand.Connection.Open();
var rowsAffected = dbCommand.ExecuteNonQuery();
dbCommand.Connection.Close();
return rowsAffected > 0;
}
}
public bool DeleteClubMember(int id)
{
using (OleDbCommand dbCommand = new OleDbCommand())
{
dbCommand.Connection = new OleDbConnection(this.ConnectionString);
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = Scripts.sqlDeleteClubMember;
dbCommand.Parameters.AddWithValue("@Id", id);
dbCommand.Connection.Open();
var rowsAffected = dbCommand.ExecuteNonQuery();
dbCommand.Connection.Close();
return rowsAffected > 0;
}
}
}
Business Service Class
The service class acts as a bridge between the data access and UI. It contains the service methods to call the data access methods.
Class Diagram
Code: Service class
public class ClubMemberService : IClubMemberService
{
private IClubMemberAccess memberAccess;
public ClubMemberService()
{
this.memberAccess = new ClubMemberAccess();
}
public DataRow GetClubMemberById(int id)
{
return this.memberAccess.GetClubMemberById(id);
}
public DataTable GetAllClubMembers()
{
return this.memberAccess.GetAllClubMembers();
}
public DataTable SearchClubMembers(object occupation, object maritalStatus, string operand)
{
return this.memberAccess.SearchClubMembers(occupation, maritalStatus, operand);
}
public bool RegisterClubMember(ClubMemberModel clubMember)
{
return this.memberAccess.AddClubMember(clubMember);
}
public bool UpdateClubMember(ClubMemberModel clubMember)
{
return this.memberAccess.UpdateClubMember(clubMember);
}
public bool DeleteClubMember(int id)
{
return this.memberAccess.DeleteClubMember(id);
}
}
Desktop: UI (Forms) and Events
Forms are the basic unit of a WinForms application. So, It is important that how we design the User interface. .NET Framework provides a rich set of controls to design the Forms and Visual studio IDE provides excellent support in designing the Forms (Using their drag and drop capability) and also in writing code for the control events. In the demo project, we have the following screens
Login
A simple Login screen with controls (Label, Textbox and Button) that accepts Username and Password. Button click event is handled in the code behind. The credentials (username, password) stored in the settings and common messages, texts are stored in the resource file. In the Desktop project, expand the properties node and double click on the Settings / Resources to modify / check the details.
Sample code for Login: Button click event
private void Login_Click(object sender, EventArgs e)
{
if (txtUsername.Text.Trim() == Settings.Default.Username &&
txtPassword.Text.Trim() == Settings.Default.Password)
{
var frmManage = new Manage();
frmManage.Show();
this.Hide();
}
else
{
MessageBox.Show(
Resources.Login_Validation_Message,
Resources.Login_Validation_Message_Title,
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
Registration
Registration screen provides the interface to enter the club member's information. The screenshot and code sample are shown below.
Sample code for Register: Button click event
private void Register_Click(object sender, EventArgs e)
{
try
{
if (this.ValidateRegistration())
{
ClubMemberModel clubMemberModel = new ClubMemberModel()
{
Id = 0,
Name = txtName.Text.Trim(),
DateOfBirth = dtDateOfBirth.Value,
Occupation = (Occupation)cmbOccupation.SelectedValue,
HealthStatus = (HealthStatus)cmbHealthStatus.SelectedValue,
MaritalStatus = (MaritalStatus)cmbMaritalStatus.SelectedValue,
Salary = txtSalary.Text.Trim() ==
string.Empty ? 0 : Convert.ToDecimal(txtSalary.Text),
NumberOfChildren = txtNoOfChildren.Text.Trim() ==
string.Empty ? 0 : Convert.ToInt16(txtNoOfChildren.Text)
};
var success = this.clubMemberService.RegisterClubMember(clubMemberModel);
if (success)
{
MessageBox.Show(
Resources.Registration_Successful_Message,
Resources.Registration_Successful_Message_Title,
MessageBoxButtons.OK,
MessageBoxIcon.Information);
this.ResetRegistration();
}
else
{
MessageBox.Show(
Resources.Registration_Error_Message,
Resources.Registration_Error_Message_Title,
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(
this.errorMessage,
Resources.Registration_Error_Message_Title,
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
this.ShowErrorMessage(ex);
}
}
Search & Manage
Search & Manage screen provides a simple search capability and allows user to select, update, delete, print and export members details. A light weight class library named "DataGridViewPrinter.dll" used to implement "print" functionality. This library taken from here. You can have a look at that article on how the print preview and Print functionality implemented. The Search & Manage screen and some sample code are shown below:
Sample Code: Search Button click event
private void Search_Click(object sender, EventArgs e)
{
try
{
DataTable data = this.clubMemberService.SearchClubMembers(
cmbSearchOccupation.SelectedValue,
cmbSearchMaritalStatus.SelectedValue,
cmbOperand.GetItemText(cmbOperand.SelectedItem)
);
this.LoadDataGridView(data);
}
catch (Exception ex)
{
this.ShowErrorMessage(ex);
}
}
private void LoadDataGridView(DataTable data)
{
dataGridViewMembers.DataSource = data;
dataGridViewMembers.DataMember = data.TableName;
dataGridViewMembers.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
}
Conclusion
I have covered something on connecting to MS Access using C# WinForms, Writting ADO.NET code and applying 3-Tier pattern. Hope you enjoyed reading this article.
References
