How to Insert Update Delete View data in SQL Server using 3 Tiers in C#

This article demonstrates how to implement a 3-Tier Architecture for managing contacts in a C# application using Entity Framework. It covers the Data Layer, Business Layer, and Presentation Layer to create a scalable and maintainable application.

Difference between tier and layer

The distinction between "tiers" and "layers" can be confusing for beginners. While some may think they are the same, they are not. "Tiers" refer to multiple physical machines or hardware boxes, with each component separated across different systems. In contrast, "layers" refer to components that are all part of the same system, typically organized in a logical hierarchy rather than physically separated.

What are the Layers?

The concept is based on N-tier architecture, where theoretically, you can have as many layers as needed. However, in practice, code is usually classified into three main categories, forming a 3-tier architecture. This article will focus on the 3-tier model and demonstrate how to implement a sample application using this approach.

Presentation Layer/ UI Layer

The Presentation Layer is the top-most layer of an application where users interact. For example, when a user fills out a form, that form is part of the Presentation Layer. In Windows applications, this corresponds to Windows Forms, while in web applications, it's the web form. This layer handles user input validation and rule processing.

Business Layer

The Business Logic Layer sits above the Presentation Layer and handles most of the application's business operations. It processes tasks like validating form data against custom business rules. This layer defines classes and business entities that represent the core functionality of the application.

Data Access Layer

The Data Access Layer sits above the Business Logic Layer and contains methods for connecting to the database and performing CRUD operations. It handles all database-related tasks. In some cases, a platform-independent Data Access Layer is used to fetch data from different database vendors.

Implementing a 3-Tier Architecture for Contact Management in C#

Open Visual Studio, then click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "EF3Tiers" and then click OK

Select Class Library, then create DataLayer, Model and BusinessLayer project

Design your form as below

Form1

frmAddEditContact

Create an EF model as below

You should create a Contact table with fileds corespond the contact class

In the DataLayer project, define an interface (IContactRepository) to standardize database operations for Contact entities. This interface ensures a contract for CRUD operations.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;

namespace DataLayer
{
    public interface IContactRepository
    {
        List<Contact> GetAll();
        Contact GetById(int id);
        Contact Insert(Contact obj);
        void Update(Contact obj);
        void Delete(Contact obj);
    }
}

Create a ContactRepository class in the DataLayer project. This class implements the IContactRepository interface and uses Entity Framework to perform CRUD operations.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;

namespace DataLayer
{
    public class ContactRepository : IContactRepository
    {
        public void Delete(Contact obj)
        {
            using(ContactEntities db = new ContactEntities())
            {
                db.Contacts.Attach(obj);
                db.Contacts.Remove(obj);
                db.SaveChanges();
            }
        }

        public List<Contact> GetAll()
        {
            using (ContactEntities db = new ContactEntities())
            {
                return db.Contacts.ToList();
            }
        }

        public Contact GetById(int id)
        {
            using (ContactEntities db = new ContactEntities())
            {
                return db.Contacts.Find(id);
            }
        }

        public Contact Insert(Contact obj)
        {
            using (ContactEntities db = new ContactEntities())
            {
                db.Contacts.Add(obj);
                db.SaveChanges();
                return obj;
            }
        }

        public void Update(Contact obj)
        {
            using (ContactEntities db = new ContactEntities())
            {
                db.Contacts.Attach(obj);
                db.Entry(obj).State = System.Data.Entity.EntityState.Modified;
                db.SaveChanges();
            }
        }
    }
}

In the BusinessLayer project, create a static ContactServices class. This class interacts with the repository and provides methods for CRUD operations. It abstracts the database details, making the code reusable and testable.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataLayer;
using Model;

namespace BusinessLayer
{
    public static class ContactServices
    {
        static IContactRepository repository;

        static ContactServices()
        {
            repository = new ContactRepository();
        }

        public static List<Contact> GetAll()
        {
            return repository.GetAll();
        }

        public static Contact GetById(int id)
        {
            return repository.GetById(id);
        }

        public static Contact Insert(Contact obj)
        {
            return repository.Insert(obj);
        }

        public static void Update(Contact obj)
        {
            repository.Update(obj);
        }

        public static void Delete(Contact obj)
        {
            repository.Delete(obj);
        }
    }
}

You can easily change code in business layer, don't need modify code in UI

In the Presentation Layer, create a Windows Forms application to interact with users.

Below is an example of Form1, the main form.

using BusinessLayer;
using Model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace EF3Tiers
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //Call business layer to get data
            contactBindingSource.DataSource = ContactServices.GetAll();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            using(frmAddEditContact frm =new frmAddEditContact(null))
            {
                if (frm.ShowDialog() == DialogResult.OK)
                    contactBindingSource.DataSource = ContactServices.GetAll();
            }
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            if (contactBindingSource.Current == null)
                return;
            using (frmAddEditContact frm = new frmAddEditContact(contactBindingSource.Current as Contact))
            {
                if (frm.ShowDialog() == DialogResult.OK)
                    contactBindingSource.DataSource = ContactServices.GetAll();
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (contactBindingSource.Current == null)
                return;
            if(MessageBox.Show("Are you sure want to delete this record ?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                ContactServices.Delete(contactBindingSource.Current as Contact);
                contactBindingSource.RemoveCurrent();
            }
        }
    }
}

Add/Edit Contact Form (frmAddEditContact):

using BusinessLayer;
using Model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace EF3Tiers
{
    public partial class frmAddEditContact : Form
    {
        bool IsNew;
        public frmAddEditContact(Contact obj)
        {
            InitializeComponent();
            if (obj == null)
            {
                contactBindingSource.DataSource = new Contact();
                IsNew = true;
            }
            else
            {
                contactBindingSource.DataSource = obj;
                IsNew = false;
            }
        }

        private void frmAddEditContact_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (DialogResult == DialogResult.OK)
            {
                if (string.IsNullOrEmpty(txtContactName.Text))
                {
                    MessageBox.Show("Please enter your contact name.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    txtContactName.Focus();
                    e.Cancel = true;
                    return;
                }
                if (IsNew)
                    ContactServices.Insert(contactBindingSource.Current as Contact);
                else
                    ContactServices.Update(contactBindingSource.Current as Contact);
            }           
        }
    }
}

Each layer has a distinct responsibility:

• Data Layer: Manages database access.
• Business Layer: Handles business rules and logic.
• Presentation Layer: Manages user interaction.

Changes in one layer don’t affect others significantly. For example, switching to a new database technology only requires updating the Data Layer.

VIDEO TUTORIAL