Social Club: Sample application using WinForms, C#.NET, ADO.NET and MS Access

• Download John.SocialClub.zip - 235.6 KB

Contents

• Introduction
• Software environment
• Pre-requisite
• Layered Architecture
• Project Structure
  • Desktop (Windows Project)
  • Data (Library Project)
• Creating the Database
• Setting up the project
• Implementing the Data Layer
  • Named constants (Enums)
  • Data model (Entity)
  • Connection string
  • Creating the SQL statements for MS Access
  • Writing the Data access Using ADO.NET
    • ADO.NET Objects
    • Steps to Load the data using DataAdapter
    • Steps to modify the data using Command object
    • Class Diagram & Data access Code
  • Business Service Class
• Desktop: UI and Events
  • Login
  • Registration
  • Search & Manage
• Conclusion
• References

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

1. 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.
2. Right click on the table and go to the design view (A small window will prompt to save the table)
3. Give the name to the table (in our case, the table name is “ClubMember”) and click save.
4. Create the fields (columns) with necessary data type, properties and description.
5. 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.

1. Open visual studio and Select File -> New -> Project
2. In the new project window, Select “Visual studio solution” from “Other project types” templates
3. Give the project name as “SocialClub” and click OK.
4. Right click the solution and select Add -> New project
5. In the new project window, From the templates Select Visual C# -> Windows -> Windows Forms Application
6. Give the project name “SocialClub.Desktop” and click OK
7. Again, Right click the solution and select Add -> New project
8. In the new project window, From the templates Select Visual C# -> Windows -> Class Library
9. Give the project name “SocialClub.Data” and click OK
10. In the “SocialClub.Desktop” project, right click on References and Select Add reference
11. From the Add Reference window, Select “Projects” tab -> SocialClub.Data Project and Click OK
12. Press CTRL+SHFT+B and check if the application builds.
13. 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

/// <summary>
/// Enum for Health status
/// </summary>
public enum HealthStatus
{
    Excellent = 1,
    Good,
    Average,
    Poor
}
/// <summary>
/// Enum for Marital status
/// </summary>
public enum MaritalStatus
{
    Married = 1,
    Single
}
/// <summary>
/// Enum for Occupation
/// </summary>
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

/// <summary>
/// ClubMemberModel class
/// </summary>
public class ClubMemberModel
{
    /// <summary>
    /// Gets or sets member id
    /// </summary>
    public int Id { get; set; }
    /// <summary>
    /// Gets or sets member name
    /// </summary>
    public string Name { get; set; }
    /// <summary>
    /// Gets or sets date of birth
    /// </summary>
    public DateTime DateOfBirth { get; set; }
    /// <summary>
    /// Gets or sets occupation
    /// </summary>
    public Occupation Occupation { get; set; }
        
    /// <summary>
    /// Gets or sets marital status
    /// </summary>
    public MaritalStatus MaritalStatus { get; set; }
    /// <summary>
    /// Gets or sets health status
    /// </summary>
    public HealthStatus HealthStatus { get; set; }
    /// <summary>
    /// Gets or sets salary
    /// </summary>
    public decimal Salary { get; set; }
    /// <summary>
    /// Gets or sets number of children
    /// </summary>
    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

1. Right click on the “SocialClub.Desktop” project, Select Add -> New item
2. From “Add New Item” window, Select Visual C# items -> General -> Application configuration file and click OK (app.config will be added to project)
3. 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;
/// <summary>
/// ConnectionAccess class
/// </summary>
public abstract class ConnectionAccess
{
    /// <summary>
    /// Gets connection string
    /// </summary>
    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

1. SELECT
2. SELECT (by input parameters)
3. INSERT
4. UPDATE
5. 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

/// <summary>
/// DBConstants static class contains sql string constants
/// </summary>
public static class Scripts
{
    /// <summary>
    /// Sql to get a club member details by Id
    /// </summary>
    public static readonly string sqlGetClubMemberById = "Select" +
        " Id, Name, DateOfBirth, Occupation, MaritalStatus, " + 
        "HealthStatus, Salary, NumberOfChildren" +
        " From ClubMember Where Id = @Id";
    /// <summary>
    /// Sql to get all club members
    /// </summary>
    public static readonly string SqlGetAllClubMembers = "Select" +
        " Id, Name, DateOfBirth, Occupation, MaritalStatus, " + 
        "HealthStatus, Salary, NumberOfChildren" +
        " From ClubMember";
    /// <summary>
    /// sql to insert a club member details
    /// </summary>
    public static readonly string SqlInsertClubMember = "Insert Into" +
        " ClubMember(Name, DateOfBirth, Occupation," + 
        " MaritalStatus, HealthStatus, Salary, NumberOfChildren)" +
        " Values(@Name, @DateOfBirth, @Occupation, " + 
        "@MaritalStatus, @HealthStatus, @Salary, @NumberOfChildren)";
    /// <summary>
    /// sql to search for club members
    /// </summary>
    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)";
    /// <summary>
    /// sql to update club member details
    /// </summary>
    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)";
    /// <summary>
    /// sql to delete a club member record
    /// </summary>
    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

1. Create an instance of DataAdapter object
2. Assign a new instance of Command Object to DataAdapter SelectCommand property
3. Set the connection and Command property of SelectCommand
4. Add (if any) parameters to the ParameterCollection of the SelectCommand
5. 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

// Create new instance of dataadapter object
OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter()
// Create the command and set its properties
oleDbDataAdapter.SelectCommand = new OleDbCommand();
oleDbDataAdapter.SelectCommand.Connection = new OleDbConnection(this.ConnectionString);
oleDbDataAdapter.SelectCommand.CommandType = CommandType.Text;
// Assign the SQL to the command object
oleDbDataAdapter.SelectCommand.CommandText = Scripts.SqlGetAllClubMembers;
// Fill the datatable from adapter
oleDbDataAdapter.Fill(dataTable);

Steps to modify the data using Command object

1. Create a new instance of Command Object
2. Set the connection property of Command object
3. Set the command type property of the Command Object as Text
4. Place the SQL statement into Command Object by setting the Command Text property
5. Add the input parameters to the Command Object’s parameter collection property
6. Open the connection
7. Execute the command by invoking the ExecuteNonQuery method on the command object
8. Close the connection

Sample code

// Create a new instance of command object
OleDbCommand dbCommand = new OleDbCommand()
// Set the command object properties
dbCommand.Connection = new OleDbConnection(this.ConnectionString);
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = Scripts.sqlUpdateClubMember;
// Add the input parameters to the parameter collection
dbCommand.Parameters.AddWithValue("@Name", clubMember.Name);
//..    
dbCommand.Parameters.AddWithValue("@Id", clubMember.Id);
// Open the connection, execute the query and close the connection
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;
/// <summary>
/// Data access class for ClubMember table
/// </summary>
public class ClubMemberAccess : ConnectionAccess, IClubMemberAccess
{
    /// <summary>
    /// Method to get all club members
    /// </summary>
    /// <returns>Data table</returns>
    public DataTable GetAllClubMembers()
    {
        DataTable dataTable = new DataTable();
        using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter())
        {
            // Create the command and set its properties
            oleDbDataAdapter.SelectCommand = new OleDbCommand();
            oleDbDataAdapter.SelectCommand.Connection = new OleDbConnection(this.ConnectionString);
            oleDbDataAdapter.SelectCommand.CommandType = CommandType.Text;
            // Assign the SQL to the command object
            oleDbDataAdapter.SelectCommand.CommandText = Scripts.SqlGetAllClubMembers;
            // Fill the datatable from adapter
            oleDbDataAdapter.Fill(dataTable);
        }
        return dataTable;            
    }
    /// <summary>
    /// Method to get club member by Id
    /// </summary>
    /// <param name="id">member id</param>
    /// <returns>Data row</returns>
    public DataRow GetClubMemberById(int id)
    {
        DataTable dataTable = new DataTable();
        DataRow dataRow;
        using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter())
        {
            // Create the command and set its properties
            dataAdapter.SelectCommand = new OleDbCommand();
            dataAdapter.SelectCommand.Connection = new OleDbConnection(this.ConnectionString);
            dataAdapter.SelectCommand.CommandType = CommandType.Text;
            dataAdapter.SelectCommand.CommandText = Scripts.sqlGetClubMemberById;
            // Add the parameter to the parameter collection
            dataAdapter.SelectCommand.Parameters.AddWithValue("@Id", id);
            // Fill the datatable From adapter
            dataAdapter.Fill(dataTable);
            // Get the datarow from the table
            dataRow = dataTable.Rows.Count > 0 ? dataTable.Rows[0] : null;
            return dataRow;
        }
    }
    /// <summary>
    /// Method to search club members by multiple parameters
    /// </summary>
    /// <param name="occupation">occupation value</param>
    /// <param name="maritalStatus">marital status</param>
    /// <param name="operand">AND OR operand</param>
    /// <returns>Data table</returns>
    public DataTable SearchClubMembers(object occupation, object maritalStatus, string operand)
    {
        DataTable dataTable = new DataTable();
        using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter())
        {
            // Create the command and set its properties
            oleDbDataAdapter.SelectCommand = new OleDbCommand();
            oleDbDataAdapter.SelectCommand.Connection = 
                   new OleDbConnection(this.ConnectionString);
            oleDbDataAdapter.SelectCommand.CommandType = CommandType.Text;
            // Assign the SQL to the command object
            oleDbDataAdapter.SelectCommand.CommandText = 
              string.Format(Scripts.SqlSearchClubMembers, operand);
            // Add the input parameters to the parameter collection
            oleDbDataAdapter.SelectCommand.Parameters.AddWithValue(
                "@Occupation", 
                occupation == null ? DBNull.Value : occupation);
            oleDbDataAdapter.SelectCommand.Parameters.AddWithValue(
                "@MaritalStatus", 
                maritalStatus == null ? DBNull.Value : maritalStatus);
            // Fill the table from adapter
            oleDbDataAdapter.Fill(dataTable);
        }
        return dataTable;
    }        
    /// <summary>
    /// Method to add new member
    /// </summary>
    /// <param name="clubMember">club member model</param>
    /// <returns>true or false</returns>
    public bool AddClubMember(ClubMemberModel clubMember)
    {
        using (OleDbCommand oleDbCommand = new OleDbCommand())
        {
            // Set the command object properties
            oleDbCommand.Connection = new OleDbConnection(this.ConnectionString);
            oleDbCommand.CommandType = CommandType.Text;
            oleDbCommand.CommandText = Scripts.SqlInsertClubMember;
            // Add the input parameters to the parameter collection
            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);
            // Open the connection, execute the query and close the connection
            oleDbCommand.Connection.Open();
            var rowsAffected = oleDbCommand.ExecuteNonQuery();
            oleDbCommand.Connection.Close();
            return rowsAffected > 0;
        }
    }
    /// <summary>
    /// Method to update club member
    /// </summary>
    /// <param name="clubMember">club member</param>
    /// <returns>true / false</returns>
    public bool UpdateClubMember(ClubMemberModel clubMember)
    {
        using (OleDbCommand dbCommand = new OleDbCommand())
        {
            // Set the command object properties
            dbCommand.Connection = new OleDbConnection(this.ConnectionString);
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandText = Scripts.sqlUpdateClubMember;
            // Add the input parameters to the parameter collection
            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);
            // Open the connection, execute the query and close the connection
            dbCommand.Connection.Open();
            var rowsAffected = dbCommand.ExecuteNonQuery();
            dbCommand.Connection.Close();
            return rowsAffected > 0;
        }
    }
    /// <summary>
    /// Method to delete a club member
    /// </summary>
    /// <param name="id">member id</param>
    /// <returns>true / false</returns>
    public bool DeleteClubMember(int id)
    {
        using (OleDbCommand dbCommand = new OleDbCommand())
        {
            // Set the command object properties
            dbCommand.Connection = new OleDbConnection(this.ConnectionString);
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandText = Scripts.sqlDeleteClubMember;
            // Add the input parameter to the parameter collection
            dbCommand.Parameters.AddWithValue("@Id", id);
            // Open the connection, execute the query and close the connection
            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

/// <summary>
/// Service class to query the DataAccess, implements IClubMemberService interface
/// </summary>
public class ClubMemberService : IClubMemberService
{
    /// <summary>
    /// interface of ClubMemberAccess
    /// </summary>
    private IClubMemberAccess memberAccess;
    /// <summary>
    /// Initializes a new instance of the ClubMemberService class
    /// </summary>
    public ClubMemberService()
    {
        this.memberAccess = new ClubMemberAccess();
    }
    /// <summary>
    /// Service method to get club member by Id
    /// </summary>
    /// <param name="id">member id</param>
    /// <returns>Data row</returns>
    public DataRow GetClubMemberById(int id)
    {
        // call to Data access method to get club member by id
        return this.memberAccess.GetClubMemberById(id);
    }
    /// <summary>
    /// Service method to get all club members
    /// </summary>
    /// <returns>Data table</returns>
    public DataTable GetAllClubMembers()
    {   
        // call to Data access method to get all club members         
        return this.memberAccess.GetAllClubMembers();
    }
    /// <summary>
    /// Service method to search records by multiple parameters
    /// </summary>
    /// <param name="occupation">occupation value</param>
    /// <param name="maritalStatus">marital status</param>
    /// <param name="operand">AND OR operand</param>
    /// <returns>Data table</returns>
    public DataTable SearchClubMembers(object occupation, object maritalStatus, string operand)
    {
        // Call to data access method with search parameters
        return this.memberAccess.SearchClubMembers(occupation, maritalStatus, operand);
    }
    /// <summary>
    /// Service method to create new member
    /// </summary>
    /// <param name="clubMember">club member model</param>
    /// <returns>true or false</returns>
    public bool RegisterClubMember(ClubMemberModel clubMember)
    {
        // Call to data access method to add club member details
        return this.memberAccess.AddClubMember(clubMember);
    }
    /// <summary>
    /// Service method to update club member
    /// </summary>
    /// <param name="clubMember">club member</param>
    /// <returns>true / false</returns>
    public bool UpdateClubMember(ClubMemberModel clubMember)
    {
        // Call to data access method to update club member details
        return this.memberAccess.UpdateClubMember(clubMember);
    }
    /// <summary>
    /// Method to delete a club member
    /// </summary>
    /// <param name="id">member id</param>
    /// <returns>true / false</returns>
    public bool DeleteClubMember(int id)
    {
        // Call to data access method to delete a member
        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

/// <summary>
/// Click event to handle the login process
/// </summary>
/// <param name="sender">sender object</param>
/// <param name="e">event data</param>
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

/// <summary>
/// Click event to handle registration
/// </summary>
/// <param name="sender">sender object</param>
/// <param name="e">event data</param>
private void Register_Click(object sender, EventArgs e)
{
    try
    {
        // Check if the validation passes
        if (this.ValidateRegistration())
        {
            // Assign the values to the model
            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)
            };
            // Call the service method and assign the return status to variable
            var success = this.clubMemberService.RegisterClubMember(clubMemberModel);
            // if status of success variable is true then display
            // a information else display the error message
            if (success)
            {
                // display the message box
                MessageBox.Show(
                    Resources.Registration_Successful_Message,
                    Resources.Registration_Successful_Message_Title,
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);
                // Reset the screen
                this.ResetRegistration();
            }
            else
            {
                // display the error messge
                MessageBox.Show(
                    Resources.Registration_Error_Message,
                    Resources.Registration_Error_Message_Title,
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Error);
            }
        }
        else
        {
            // Display the validation failed message
            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

/// <summary>
/// Click event to handle search
/// </summary>
/// <param name="sender">sender object</param>
/// <param name="e">event data</param>
private void Search_Click(object sender, EventArgs e)
{
    try
    {
        // Call to service method by passing the selected search paramters
        DataTable data = this.clubMemberService.SearchClubMembers(
            cmbSearchOccupation.SelectedValue, 
            cmbSearchMaritalStatus.SelectedValue, 
            cmbOperand.GetItemText(cmbOperand.SelectedItem)
            );
        // Call to private method by passing the result set to load the grid view
        this.LoadDataGridView(data);
    }
    catch (Exception ex)
    {
        this.ShowErrorMessage(ex);
    }            
}
/// <summary>
/// Method to load data grid view
/// </summary>
/// <param name="data">data table</param>
private void LoadDataGridView(DataTable data)
{
    // Setting the data source and table for data grid view to display the 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

• Microsoft Access 2010 Tutorial: Basic Help Creating Database
• Connection String for Access
• Using ADO.NET
• The DataGridViewPrinter Class