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. {  
  3.     //for alert purpose  
  4.     if (From > To)  
  5.     {  
  6.         TempData["SelectOption"] = 1;  
  7.     }  
  8.     //for alert purpose  
  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  
  21.             cmd.Parameters.AddWithValue("@Fromdate", From);  
  22.             cmd.Parameters.AddWithValue("@Todate", To);  
  23.   
  24.             SqlDataAdapter da = new SqlDataAdapter(cmd);  
  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.   
  33.                 userlist.Add(uobj);  
  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,
 
BuildAge Calculator Application Using MVC And SQL Server
Then perfrom age calculation between two dates using Age Calculator.
 
BuildAge Calculator Application Using MVC And SQL Server 
Link To Source Code

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