Build 🏗️ Age Calculator Application Using MVC And SQL Server

Introduction

 

In this article, we will learn how to build an Age Calculator application using ASP.NET MVC and Stored Procedure. Using the same process, we can build a Years Of Experience Calculator. In most of the articles on the internet regarding age calculation, the process is static and uses only C#, but this article is unique and uses an MVC application using SQL Server to build  a dynamic age calculator.

 

Prerequisites

• Visual Studio
• SQL server

Note

Before going through this session, visit my previous articles related to ASP.NET MVC and SQL Server for a better understanding of how to set up the project.

• Calculate Age And Experience Using Table Data And Stored Procedure In MVC

Step 1

 

First, we need to create a stored procedure to perform age calculation between dates using scalar function. Refer to the below script:

 

Scalar Function

ALTER FUNCTION [dbo].[GetYearsOfExp]
(
   @FromDate DATETIME, @ToDate DATETIME
)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
    SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
     - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
              @FromDate) > @ToDate THEN 1 ELSE 0 END)

    SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
    SET @Months =  DATEDIFF(MONTH, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END)

    SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
    SET @Days =  DATEDIFF(DAY, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END)


    --RETURN 'Years: ' + CAST(@Years AS VARCHAR(4)) +
    --        ' Months: ' + CAST(@Months AS VARCHAR(2)) +
    --        ' Days: ' + CAST(@Days AS VARCHAR(2))

    Return CAST(@years as varchar(5)) + ' years ' +
             CAST(@months as varchar(3)) + ' months ' +
             CAST(@days as varchar(3)) + ' days'
END

Stored Procedure

----Author : Satyaprakash
----Script : exec  Sp_AgeWithExpBetweenDates @status ='SHW', @Fromdate='1991-05-10', @Todate='2020-05-31'
----Age and exp. calculator

ALTER procedure [dbo].[Sp_AgeWithExpBetweenDates]
@status varchar(10),
@Fromdate DATETIME=null,
@Todate DATETIME =null
AS
BEGIN
if @status ='SHW'
BEGIN
SELECT
[dbo].[GetYearsOfExp](@Fromdate,@Todate) as YearRange
END
END

Execute procedure to get age between dates

exec  Sp_AgeWithExpBetweenDates @status ='SHW', @Fromdate='1991-05-10', @Todate='2020-06-06'

Step 2

 

Here, we need create a model class with entities which should be the same as stored procedure column names. This is named "DateDetails.cs".

public string YearRange { get; set; }

Step 3

 

Here, we need to create a controller named DateController.cs inside the Controllers folder. Inside the Home controller, we added a controller action method named AgeCalc.

 

Code Ref

public ActionResult AgeCalc(DateTime? From, DateTime? To) //this value name should be same as input control name as used in view schtml file
{
    //for alert purpose
    if (From > To)
    {
        TempData["SelectOption"] = 1;
    }
    //for alert purpose

    string mainconn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString; //added connection string
    DateDetails objuser = new DateDetails();
    DataSet ds = new DataSet();
    DataTable dt = new DataTable();
    using (SqlConnection con = new SqlConnection(mainconn))
    {
        using (SqlCommand cmd = new SqlCommand("Sp_AgeWithExpBetweenDates", con)) //stored procedure name
        {
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@status", "SHW"); //Parameters for filter records
            cmd.Parameters.AddWithValue("@Fromdate", From);
            cmd.Parameters.AddWithValue("@Todate", To);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            List<DateDetails> userlist = new List<DateDetails>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                DateDetails uobj = new DateDetails();

                uobj.YearRange = ds.Tables[0].Rows[i]["YearRange"].ToString(); //show records with selected columns

                userlist.Add(uobj);
            }
            objuser.usersinfo = userlist;
        }
        con.Close();
    }
    return View(objuser);


}

Code Description

 

Here, I added code with a description in the green comment mark "//" at one place for easier understanding.

 

Step 4

 

We need to add a view called AgeCalc.cshtml

 

Code Ref

@if (Model != null)
{
    if (Model.usersinfo.Count > 0) /*Display records*/
    {
        <table align="center" border="1" cellpadding="4" cellspacing="4">
            @foreach (var item in Model.usersinfo)
            {
            <tr>
                <td style="background-color: Yellow;color: blue; width:140px ; font-size:large">Your Age Is : </td>
                <td style="color:red ; font-size:large">@Html.DisplayFor(modelitem => item.YearRange) </td>
            </tr>
            }
        </table>
    }
    else
    {
        <span style="color:red"><b>No Details Found.</b></span>
    }
}

Code Description

 

Here, I added code with a description in a green comment mark in one place for easier understanding. 

 

Output

 

The landing page is shown below,

 

Then perfrom age calculation between two dates using Age Calculator.

 

 

Link To Source Code

• Age Calculator Using MVC

Summary

 

In this article, we have learned:

• About scalar function and its uses in a stored procedure
• Calculating age and years of experience Using Age Calculator
• About Age Calculator using C# and Sql
• Managing alert message in MVC and design view using layout