Create n-tier Architecture in ASP.NET MVC Application

Download Source Code

Introduction

In this article we will learn how to create n-tier architecture in ASP.NET MVC application. You should have basic understanding creating an MVC Application in .NET. We will also create a generic Database Layer which can be used in any other application. I will not be using any ORM framework for Database connection and CRUD operations. Lets stick to the basics.

Lets Start the Fun

  1. Create the MVC Application in Visual Studio. You will see usual Models, View and Controller folder in the project.
  2. Now lets create our database table. We will name it Employee Table and it would like this
    CREATE TABLE [dbo].[EmployeeTable](
    	[EmpId] [int] NOT NULL,
    	[EmpName] [varchar](50) NOT NULL,
    	[Designation] [char](10) NULL)
    
  3. Now we will create our Model class. Right click on the Models folder and Add a class and name it Employee. Our Employee class should look like thi
     public class Employee
        {
            public int EmployeeId { get; set; }
            public string EmployeeName { get; set; }
            public string Department { get; set; }
    
        }
    

    So far So good. We have created a class which has a similar structure to our database table.

  4. Now we will Add our View which would take the input from User and insert those values in the database table Employee. I am not adding any validation on the HTML form in this article, if you want to learn how to perform validations in HTML form in ASP.NET MVC please read my article Custom validation of Date in ASP.NET MVC.
    Before adding a view make sure to compile your project. Add a folder in Views folder and name it Home. Now Add a view in this Home folder and name it index and select the checkbox “Create a strongly typed view” and Select the Employee class from the drop down. Your screen should look like this:
  5. Our index.cshtml file should look like this:

    @model MvcApplicationArch.Models.Employee
    
    @{
        ViewBag.Title = "index";
        Layout = "~/Views/Shared/_Layout.cshtml";
    }
    
    @{ using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
     {
           @Html.ValidationSummary(true)
        <table>
        <tr><td>Employee Id</td><td>@Html.TextBoxFor(m => m.EmployeeId)</td><td>@Html.ValidationMessageFor(m => m.EmployeeId)</td></tr>
        <tr><td>Employee Name</td><td>@Html.TextBoxFor(m => m.EmployeeName)</td><td>@Html.ValidationMessageFor(m => m.EmployeeName)</td></tr>
        <tr><td>Department</td><td>@Html.TextBoxFor(m => m.Department)</td><td>@Html.ValidationMessageFor(m => m.Department)</td></tr>
        
        <tr><td></td><td><input type="submit" value="Submit"/></td></tr>
        </table>
     }
    }
    
    <div>
    @ViewData["Message"]
    </div>
    
    

    So now our Model and View is ready but without controller the application is incomplete. In next steps we will not add the Controller rather we will add Layers which will make our application n-tier. In normal MVC applications many Developers would write the business logic in the Controller class and some even write the Database CRUD operations in the controller class. This violates the principle of “Single Responsibility”. The responsibility of Controller is like an entry gate. It acts as an entry to the applications. User inputs come to the controller but controller should not perform any business logic on this data. There should be separate classes to perform Business Logic and Database handling. In next steps we will create our Database class and Business Class. That was bit lengthy explanation but it was worth it. Wasn’t it?

  6. In your project Add a new Folder and name it DataAccessLayer. Now add a new class in this folder and name it SQLDBAccess. Now this class will perform all the activities related to the Database. This class will connect to the Database, Call the SQL Stored Procedures to get the Output, Call the SQL Stored Procedures which will perform Insert / Delete / Update statements on the Database tables. Our code of SQLDBAccess class look like this:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Configuration;
    
    namespace MvcApplicationArch.DataAccessLayer
    {
        public class SQLDBAccess 
        {
            #region Class Variables
            private string _connString = string.Empty;
            private SqlConnection _sqlConn = null;
    
            #endregion
    
            #region Private methods
            private string GetConnectionString()
            {
                try
                {
                    if(String.IsNullOrEmpty(_connString))
                    _connString = ConfigurationManager.ConnectionStrings["SQLDBConnection"].ConnectionString;
                    return _connString;
                }
                catch (Exception)
                {
                    
                    throw;
                }
            }
    
            private SqlConnection GetConnection()
            {
                var sqlConn = new SqlConnection(GetConnectionString());
                return sqlConn;
            }
    
            private void CloseConnection()
            {
                try
                {
                    if (_sqlConn != null && _sqlConn.State != ConnectionState.Closed)
                    {
                    _sqlConn.Close();
                    }
                }
                catch (Exception)
                {
                    
                    throw;
                }
            }
            #endregion
    
            #region Public Mehtods
    
            /// <summary>
            /// Call stored procedure with Sql Command. This will contain the I/P params
            /// </summary>
            /// <param name="spName"></param>
            /// <param name="command"></param>
            /// <returns></returns>
            public DataSet GetDataSet(string spName, SqlCommand command)
            {
                var dataSet = new DataSet();
                //var dataAdapter = new SqlDataAdapter();
                try
                {
                    using (var sqlConn = GetConnection())
                    {
                        command.Connection = sqlConn;
                        command.CommandText = spName;
                        command.CommandType = CommandType.StoredProcedure;
                        sqlConn.Open();
                        using (var dataAdapter = new SqlDataAdapter())
                        {
                            dataAdapter.SelectCommand = command;
                            dataAdapter.Fill(dataSet);
                        }
                    }
                }
                catch (Exception)
                {
                    
                    throw;
                }
                return dataSet;
            }
    
            /// <summary>
            /// Call stored proc without params
            /// </summary>
            /// <param name="spName"></param>
            /// <returns></returns>
            public DataSet GetDataSet(string spName)
            {
                var dataSet = new DataSet();
                try
                {
                    using (var command = new SqlCommand())
                    {
                        dataSet = GetDataSet(spName, command);
                    }
                }
                catch (Exception)
                {
                    
                    throw;
                }
                return dataSet;
            }
    
            public void ExecuteNonQuery(SqlCommand command)
            {
                try
                {
                    using (var sqlConn = GetConnection())
                    {
                        command.Connection = sqlConn;
                        sqlConn.Open();
                        command.ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {
                    
                    throw;
                }
            }
    
            public void ExecuteNonQuery(string spName, SqlCommand command)
            {
                try
                {
                    command.CommandText = spName;
                    command.CommandType = CommandType.StoredProcedure;
                    ExecuteNonQuery(command);
                }
                catch (Exception)
                {
                    
                    throw;
                }
            }
            #endregion        
        }
    }
    

    Its a long code but quite self explanatory. In nutshell it has methods to connect to Database and executing Stored Procedures. So now our lowest layer i.e DataAccess Layer is ready. This code is very generic in nature and can be reused in multiple applications where MS SQL Server is used. To interact with Oracle Database we can write a new class in a similar fashion but which will use Oracle driver to connect to Oracle Database.

  7. So now our Database table is ready, Our Model class is ready, we have created the view also and our Database layer is also ready. Now we will create the Business Layer. This layer will act as a medium between Controller and Database layer. When user inputs the data in the HTML form and clicks on the Submit, that data comes to the Controller. So now Controller will not perform any action on this data rather it will pass the Data to the Business Layer. Any Business Logic on this data should be handled by the Business Layer.
  8. In the Project Add a new Folder and name it Business Layer. Add a new Class in this folder and name it BusinessAccess. Now we will create a method in this BusinessAccess class which get the Input as the data entered by the user and pass this data to the Database Layer which in turn will call the Stored Procedure to insert the data in the database. If any Business logic needs to be performed on this data then BusinessAccess layer is place to write that logic.
    Our BusinessAccess code look like this:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Data;
    using System.Data.SqlClient;
    using MvcApplicationArch.Models;
    using MvcApplicationArch.DataAccessLayer;
    
    namespace MvcApplicationArch.BusinessLayer
    {
        public class BusinessAccess
        {
            public bool InsertEmployee(Employee emp)
            {
                bool flag = false;
                var sqlDb = new SQLDBAccess();
                using (var command = new SqlCommand())
                {
                    command.Parameters.Add("@EmpId", SqlDbType.Int).Value = emp.EmployeeId;
                    command.Parameters.Add("@EmpName", SqlDbType.VarChar,50).Value = emp.EmployeeName;
                    command.Parameters.Add("@Department", SqlDbType.Char,10).Value = emp.Department;
                    sqlDb.ExecuteNonQuery("spInsertEmployee", command);
                    flag = true;
                }          
                return flag;
            }
        }
    }
    

    The InsertEmployee method gets the Employee class object as input parameter and pass the values to the Database layer.

  9. Now only thing left is to create the Controller and link it with the BusinessAccess layer. In the Controller folder add a new Controller and Name it Home. Create a new method in the Controller which will take the object of the Employee class as an input and will be called by the HTML Post method from the View. Our Controller code look like this:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using MvcApplicationArch.Models;
    using MvcApplicationArch.BusinessLayer;
    
    namespace MvcApplicationArch.Controllers
    {
        public class HomeController : Controller
        {
           
            public ActionResult Index()
            {
                return View();
            }
    
            [HttpPost]
            public ActionResult Index(Employee employee)
            {
                var business = new BusinessAccess();
                if (business.InsertEmployee(employee))
                {
                    @ViewData["Message"] = "Employee inserted";
                }
                else
                {
                    @ViewData["Message"] = "Error";
                }
                return View();
            }
        }
    }
    
    

Conclusion

In this article we learnt how to create n-tier architecture in ASP.NET MVC applications. This article only explained how to insert the data in the Database table. The code of SQL Server Stored Procedure is available in the downloadable zip file. Here i have explained how to create different classes and implement the Single Responsibility Principle. I hope you enjoyed reading this article. Any issues or suggestions please let me know in the comment section.

One thought on “Create n-tier Architecture in ASP.NET MVC Application

  1. Hi there, I found your site via Google while searching for a related topic, your web site came up, it looks great. I’ve bookmarked it in my google bookmarks.

Leave a Reply

Your email address will not be published. Required fields are marked *