# 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.
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
1. ALTER FUNCTION [dbo].[GetYearsOfExp]
2. (
3.    @FromDate DATETIME, @ToDate DATETIME
4. )
5. RETURNS NVARCHAR(100)
6. AS
7. BEGIN
8.     DECLARE @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
9.     SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
10.      - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
11.               @FromDate) > @ToDate THEN 1 ELSE 0 END)
12.
13.     SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
14.     SET @Months =  DATEDIFF(MONTH, @tmpFromDate, @ToDate)
15.      - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
16.               @tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
17.
18.     SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
19.     SET @Days =  DATEDIFF(DAY, @tmpFromDate, @ToDate)
20.      - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
21.               @tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
22.
23.
24.     --RETURN 'Years: ' + CAST(@Years AS VARCHAR(4)) +
25.     --        ' Months: ' + CAST(@Months AS VARCHAR(2)) +
26.     --        ' Days: ' + CAST(@Days AS VARCHAR(2))
27.
28.     Return CAST(@years as varchar(5)) + ' years ' +
29.              CAST(@months as varchar(3)) + ' months ' +
30.              CAST(@days as varchar(3)) + ' days'
31. END
Stored Procedure
1. ----Author : Satyaprakash
2. ----Script : exec  Sp_AgeWithExpBetweenDates @status ='SHW', @Fromdate='1991-05-10', @Todate='2020-05-31'
3. ----Age and exp. calculator
4.
5. ALTER procedure [dbo].[Sp_AgeWithExpBetweenDates]
6. @status varchar(10),
7. @Fromdate DATETIME=null,
8. @Todate DATETIME =null
9. AS
10. BEGIN
11. if @status ='SHW'
12. BEGIN
13. SELECT
14. [dbo].[GetYearsOfExp](@Fromdate,@Todate) as YearRange
15. END
16. END
Execute procedure to get age between dates
1. 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".
1. public string YearRange { getset; }
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
1. public ActionResult AgeCalc(DateTime? From, DateTime? To) //this value name should be same as input control name as used in view schtml file
2. {
4.     if (From > To)
5.     {
6.         TempData["SelectOption"] = 1;
7.     }
9.
10.     string mainconn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString; //added connection string
11.     DateDetails objuser = new DateDetails();
12.     DataSet ds = new DataSet();
13.     DataTable dt = new DataTable();
14.     using (SqlConnection con = new SqlConnection(mainconn))
15.     {
16.         using (SqlCommand cmd = new SqlCommand("Sp_AgeWithExpBetweenDates", con)) //stored procedure name
17.         {
18.             con.Open();
19.             cmd.CommandType = CommandType.StoredProcedure;
20.             cmd.Parameters.AddWithValue("@status""SHW"); //Parameters for filter records
23.
25.             da.Fill(ds);
26.             List<DateDetails> userlist = new List<DateDetails>();
27.             for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
28.             {
29.                 DateDetails uobj = new DateDetails();
30.
31.                 uobj.YearRange = ds.Tables[0].Rows[i]["YearRange"].ToString(); //show records with selected columns
32.
34.             }
35.             objuser.usersinfo = userlist;
36.         }
37.         con.Close();
38.     }
39.     return View(objuser);
40.
41.
42. }
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
1. @if (Model != null)
2. {
3.     if (Model.usersinfo.Count > 0) /*Display records*/
4.     {
5.         <table align="center" border="1" cellpadding="4" cellspacing="4">
6.             @foreach (var item in Model.usersinfo)
7.             {
8.             <tr>
9.                 <td style="background-color: Yellow;color: blue; width:140px ; font-size:large">Your Age Is : </td>
10.                 <td style="color:red ; font-size:large">@Html.DisplayFor(modelitem => item.YearRange) </td>
11.             </tr>
12.             }
13.         </table>
14.     }
15.     else
16.     {
17.         <span style="color:red"><b>No Details Found.</b></span>
18.     }
19. }
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.