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,

BuildAge Calculator Application Using MVC And SQL Server

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