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)
  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)
  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)
  24. --RETURN 'Years: ' + CAST(@Years AS VARCHAR(4)) +
  25. -- ' Months: ' + CAST(@Months AS VARCHAR(2)) +
  26. -- ' Days: ' + CAST(@Days AS VARCHAR(2))
  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
  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 { 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
  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
  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);
  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();
  31. uobj.YearRange = ds.Tables[0].Rows[i]["YearRange"].ToString(); //show records with selected columns
  33. userlist.Add(uobj);
  34. }
  35. objuser.usersinfo = userlist;
  36. }
  37. con.Close();
  38. }
  39. return View(objuser);
  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