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

Introduction

 

In this article, we will learn how to calculate age based on today's date and years of experience based on the date of joining and the date of leaving between dates by passing date parameters in a stored procedure using ASP.NET MVC and ADO.NET. Here, the user can see age and years of experience with other details records about the employee by filtering between dates.

 

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.

• Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC

Step 1

 

First, we need to create a table schema and prepare data for these tables. Refer to the below script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Country](
    [CountryID] [int] IDENTITY(1,1) NOT NULL,
    [CountryName] [varchar](100) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [CountryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[State]    Script Date: 31-05-2020 15:11:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[State](
    [StateID] [int] IDENTITY(1,1) NOT NULL,
    [CountryID] [int] NOT NULL,
    [StateName] [varchar](100) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [StateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Tbl_Applicant]    Script Date: 31-05-2020 15:11:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl_Applicant](
    [int_ApplicantID] [int] NOT NULL,
    [vch_ApplicantName] [varchar](104) NOT NULL,
    [vch_FatherName] [varchar](104) NOT NULL,
    [vch_MotherName] [varchar](104) NOT NULL,
    [dtm_DOB] [datetime] NOT NULL,
    [vch_CorMobileNo] [char](10) NOT NULL,
    [vch_EMailID] [varchar](104) NULL,
 CONSTRAINT [PK_Tbl_Applicant] PRIMARY KEY CLUSTERED
(
    [int_ApplicantID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Tbl_Company]    Script Date: 31-05-2020 15:11:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl_Company](
    [int_CompID] [int] NOT NULL,
    [vch_CompName] [varchar](104) NOT NULL,
    [dtm_Join] [datetime] NOT NULL,
    [dtm_Leave] [datetime] NOT NULL,
 CONSTRAINT [PK_Tbl_Company] PRIMARY KEY CLUSTERED
(
    [int_CompID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Country] ON
GO
INSERT [dbo].[Country] ([CountryID], [CountryName]) VALUES (1, N'Brazil')
GO
INSERT [dbo].[Country] ([CountryID], [CountryName]) VALUES (2, N'China')
GO
INSERT [dbo].[Country] ([CountryID], [CountryName]) VALUES (3, N'France')
GO
INSERT [dbo].[Country] ([CountryID], [CountryName]) VALUES (4, N'India')
GO
INSERT [dbo].[Country] ([CountryID], [CountryName]) VALUES (5, N'USA')
GO
SET IDENTITY_INSERT [dbo].[Country] OFF
GO
SET IDENTITY_INSERT [dbo].[State] ON
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (1, 5, N'California')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (2, 2, N'Beijing')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (3, 5, N'Iowa')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (4, 5, N'New York')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (5, 2, N'Hebei')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (6, 2, N'Jiangsu')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (7, 5, N'New Jersey')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (8, 5, N'Massachusetts')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (9, 5, N'Connecticut')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (10, 2, N'Guangdong')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (11, 5, N'Florida')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (12, 5, N'Texas')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (13, 5, N'Armed Forces US')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (14, 5, N'Tennessee')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (15, 5, N'Kentucky')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (16, 3, N'Ile-de-3nce')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (17, 5, N'Georgia')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (18, 1, N'Rio de Janeiro')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (19, 5, N'Illinois')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (20, 1, N'Ceara')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (21, 5, N'Colorado')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (22, 2, N'Zhejiang')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (23, 5, N'Utah')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (24, 2, N'Liaoning')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (25, 4, N'Haryana')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (26, 5, N'Maryland')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (27, 2, N'Shanghai')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (28, 2, N'Tianjin')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (29, 5, N'South Carolina')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (30, 5, N'Montana')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (31, 5, N'Louisiana')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (32, 2, N'Fujian')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (33, 1, N'Santa Catarina')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (34, 1, N'Espirito Santo')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (35, 5, N'Washington')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (36, 4, N'Andhra Pradesh')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (37, 5, N'Pennsylvania')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (38, 2, N'Guangxi')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (39, 5, N'North Carolina')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (40, 2, N'Shandong')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (41, 2, N'Chongqing')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (42, 5, N'Michigan')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (43, 2, N'Hubei')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (44, 4, N'Delhi')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (45, 5, N'Arkansas')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (46, 5, N'Wisconsin')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (47, 3, N'Midi-Pyrenees')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (48, 3, N'Picardie')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (49, 1, N'Bahia')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (50, 2, N'Heilongjiang')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (51, 4, N'Tamil Nadu')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (52, 5, N'Ohio')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (53, 5, N'New Mexico')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (54, 5, N'Kansas')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (55, 5, N'Oregon')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (56, 4, N'Uttar Pradesh')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (57, 5, N'Ne1ska')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (58, 5, N'West Virginia')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (59, 5, N'Virginia')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (60, 5, N'Missouri')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (61, 5, N'Mississippi')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (62, 5, N'Rhode Island')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (63, 1, N'Sao Paulo')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (64, 2, N'Shanxi')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (65, 4, N'Karnataka')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (66, 2, N'Hunan')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (67, 5, N'4iana')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (68, 5, N'Oklahoma')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (69, 5, N'Minnesota')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (70, 5, N'Alabama')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (71, 2, N'Hainan')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (72, 5, N'Arizona')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (73, 2, N'Sichuan')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (74, 5, N'South Dakota')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (75, 4, N'Maharashtra')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (76, 5, N'Nevada')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (77, 2, N'Henan')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (78, 4, N'Kerala')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (79, 5, N'New Hampshire')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (80, 5, N'Maine')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (81, 5, N'Hawaii')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (82, 4, N'Chhattisgarh')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (83, 2, N'Anhui')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (84, 5, N'District of Columbia')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (85, 5, N'Delaware')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (86, 4, N'West Bengal')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (87, 2, N'Shaanxi')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (88, 4, N'Madhya Pradesh')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (89, 4, N'Gujarat')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (90, 3, N'3nche-Comte')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (91, 5, N'Idaho')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (92, 4, N'Rajasthan')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (93, 2, N'Nei Mongol')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (94, 3, N'Alsace')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (95, 4, N'Orissa')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (96, 2, N'Jilin')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (97, 4, N'Jharkhand')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (98, 4, N'Chandigarh')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (99, 4, N'Punjab')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (100, 3, N'Languedoc-Roussillon')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (101, 4, N'Assam')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (102, 3, N'Centre')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (103, 3, N'Champagne-Ardenne')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (104, 3, N'Bretagne')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (105, 3, N'Rhone-Alpes')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (106, 3, N'Nord-Pas-de-Calais')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (107, 3, N'Lorraine')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (108, 1, N'Rio Grande do Sul')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (109, 3, N'Provence-Alpes-Cote d''Azur')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (110, 1, N'Minas Gerais')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (111, 3, N'Limousin')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (112, 2, N'Guizhou')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (113, 3, N'Haute-Normandie')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (114, 3, N'Poitou-Charentes')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (115, 5, N'Wyoming')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (116, 4, N'Daman and Diu')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (117, 1, N'Para')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (118, 3, N'Basse-Normandie')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (119, 4, N'Bihar')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (120, 3, N'Aquitaine')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (121, 1, N'Parana')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (122, 3, N'Auvergne')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (123, 1, N'Pernambuco')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (124, 3, N'Pays de la Loire')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (125, 1, N'Amazonas')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (126, 1, N'Distrito Federal')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (127, 5, N'North Dakota')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (128, 3, N'Bourgogne')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (129, 5, N'Vermont')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (130, 1, N'Goias')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (131, 4, N'Himachal Pradesh')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (132, 1, N'Sergipe')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (133, 5, N'Alaska')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (134, 1, N'Mato Grosso do Sul')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (135, 2, N'Yunnan')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (136, 4, N'Uttarakhand')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (137, 4, N'Meghalaya')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (138, 2, N'Jiangxi')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (139, 1, N'Rio Grande do Norte')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (140, 1, N'Paraiba')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (141, 1, N'Piaui')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (142, 2, N'Gansu')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (143, 4, N'Jammu and Kashmir')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (144, 4, N'Goa')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (145, 1, N'Maranhao')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (146, 1, N'Mato Grosso')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (147, 3, N'Corse')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (148, 1, N'Alagoas')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (149, 4, N'Puducherry')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (150, 4, N'Manipur')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (151, 1, N'Tocantins')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (152, 1, N'Roraima')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (153, 1, N'Rondonia')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (154, 2, N'Xizang')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (155, 2, N'Ningxia')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (156, 2, N'Xinjiang')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (157, 2, N'Qinghai')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (158, 4, N'Mizoram')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (159, 4, N'Dadra and Nagar Haveli')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (160, 4, N'Arunachal Pradesh')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (161, 4, N'Tripura')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (162, 1, N'Amapa')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (163, 1, N'Acre')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (164, 4, N'Sikkim')
GO
INSERT [dbo].[State] ([StateID], [CountryID], [StateName]) VALUES (165, 4, N'Nagaland')
GO
SET IDENTITY_INSERT [dbo].[State] OFF
GO
INSERT [dbo].[Tbl_Applicant] ([int_ApplicantID], [vch_ApplicantName], [vch_FatherName], [vch_MotherName], [dtm_DOB], [vch_CorMobileNo], [vch_EMailID]) VALUES (1, N'KIRAN KUMAR CHHATAI', N'MANOJ KUMAR CHHATAI', N'LABANYA CHHATAI', CAST(N'1999-06-10T00:00:00.000' AS DateTime), N'9778087807', N'[email protected]')
GO
INSERT [dbo].[Tbl_Applicant] ([int_ApplicantID], [vch_ApplicantName], [vch_FatherName], [vch_MotherName], [dtm_DOB], [vch_CorMobileNo], [vch_EMailID]) VALUES (2, N'Satyaprakash Samantaray', N'Dheerak Kumar', N'Nirmala Kumari', CAST(N'1979-07-11T00:00:00.000' AS DateTime), N'8798087807', N'[email protected]')
GO
INSERT [dbo].[Tbl_Applicant] ([int_ApplicantID], [vch_ApplicantName], [vch_FatherName], [vch_MotherName], [dtm_DOB], [vch_CorMobileNo], [vch_EMailID]) VALUES (3, N'Raj kishore', N'Raj kishore Kumar', N'Seeta kishore Kumari', CAST(N'1989-08-11T00:00:00.000' AS DateTime), N'7798087807', N'[email protected]')
GO
INSERT [dbo].[Tbl_Applicant] ([int_ApplicantID], [vch_ApplicantName], [vch_FatherName], [vch_MotherName], [dtm_DOB], [vch_CorMobileNo], [vch_EMailID]) VALUES (4, N'Saroj kishore', N'saroj kishore Kumar', N'geeta kishore Kumari', CAST(N'1974-04-14T00:00:00.000' AS DateTime), N'9498087807', N'[email protected]')
GO
INSERT [dbo].[Tbl_Company] ([int_CompID], [vch_CompName], [dtm_Join], [dtm_Leave]) VALUES (1, N'Infosys', CAST(N'2010-06-10T00:00:00.000' AS DateTime), CAST(N'2011-07-10T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Tbl_Company] ([int_CompID], [vch_CompName], [dtm_Join], [dtm_Leave]) VALUES (2, N'TCS', CAST(N'2009-01-11T00:00:00.000' AS DateTime), CAST(N'2011-02-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Tbl_Company] ([int_CompID], [vch_CompName], [dtm_Join], [dtm_Leave]) VALUES (3, N'IBM', CAST(N'2007-04-11T00:00:00.000' AS DateTime), CAST(N'2008-05-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Tbl_Company] ([int_CompID], [vch_CompName], [dtm_Join], [dtm_Leave]) VALUES (4, N'Wipro', CAST(N'2008-08-11T00:00:00.000' AS DateTime), CAST(N'2009-09-11T00:00:00.000' AS DateTime))
GO

Step 2

 

Then, create 2 scalar functions for calculating age and years of experience of employees.

 

GetYearsOfExp: This function is used for calculating years of experience between the date of joining and the date of leaving.

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

UDF_AgeInYears

 

This function is used for calculating age based on the date of birth and today's date.

ALTER FUNCTION [dbo].[UDF_AgeInYears]
(
@givenDate datetime
)

RETURNS varchar(100)
AS
BEGIN
DECLARE @tempDate datetime
DECLARE @years int, @months int, @days int

SELECT @tempDate = @givenDate

-- get year
SELECT @years = DATEDIFF(yy, @tempDate, GETDATE()) -
              CASE
                    WHEN
                            (MONTH(@givenDate) > MONTH(GETDATE()))
                                   OR
                            (MONTH(@givenDate) = MONTH(GETDATE())
                                          AND
                                          DAY(@givenDate) > DAY(GETDATE()))
                    THEN 1
                    ELSE 0
              END
SELECT @tempDate = DATEADD(yy, @years, @tempDate)

-- get months
SELECT @months = DATEDIFF(m, @tempDate, GETDATE()) -
              CASE
                     WHEN
                            DAY(@givenDate) > DAY(GETDATE())
                     THEN 1
                     ELSE 0
              END
SELECT @tempDate = DATEADD(m, @months, @tempDate)

-- get days
SELECT @days = DATEDIFF(d, @tempDate, GETDATE())

-- output the result
Return CAST(@years as varchar(5)) + ' years ' +
             CAST(@months as varchar(3)) + ' months ' +
             CAST(@days as varchar(3)) + ' days'
END

Step 3

 

In this step, create a stored procedure using dynamic SQL for filtering records between dates and calculating age and years of experience with other details of the employees.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

----Author : Satyaprakash
----Query : exec Sp_EmployeeDetailsWithAge @status ='DIS',@Fromdate='1979-04-04', @Todate='2000-04-04'
----Calculate age in Year/Month/Days and filter records between dates

ALTER procedure [dbo].[Sp_EmployeeDetailsWithAge]
@status varchar(10),
@Fromdate DATETIME=null,
@Todate DATETIME =null
AS
BEGIN
if @status ='DIS'
BEGIN
SET NOCOUNT ON;
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
Set @SQLQuery ='SELECT vch_ApplicantName as ApplicantName,CONVERT (varchar, dtm_DOB, 106) as DOB,[dbo].[UDF_AgeInYears](dtm_DOB) as Age,
CONVERT (varchar, dtm_Join, 106) as DOJ, CONVERT (varchar, dtm_Leave, 106) as DOL,
[dbo].[GetYearsOfExp](dtm_Join,dtm_Leave) as Expr,
vch_FatherName as FatherName, vch_MotherName as MotherName,C.CountryName, D.StateName,
vch_CorMobileNo as Mobile, vch_EMailID as Email
FROM Tbl_Applicant A
Inner join Tbl_Company b on a.int_ApplicantID = b.int_CompID
Inner join Country c on b.int_CompID = c.CountryID
Inner join State d on c.CountryID = d.StateID where A.int_ApplicantID<>0'

If (@Fromdate Is Not Null) AND (@Todate Is Not Null)
         Set @SQLQuery = @SQLQuery + 'And (A.dtm_DOB BETWEEN @Fromdate AND @Todate)'

Set @ParamDefinition = '@Fromdate DATETIME,@Todate DATETIME'
Execute sp_Executesql     @SQLQuery,@ParamDefinition,@Fromdate,@Todate

END
END

Step 4

 

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

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace WebApplication1.Models
{
    public class DateDetails
    {
        public string ApplicantName { get; set; }
        public string DOB { get; set; }
        public string Age { get; set; }
        public string DOJ { get; set; }
        public string DOL { get; set; }
        public string Expr { get; set; }
        public string FatherName { get; set; }
        public string MotherName { get; set; }
        public string CountryName { get; set; }
        public string StateName { get; set; }
        public string Mobile { get; set; }
        public string Email { get; set; }

        public List<DateDetails> usersinfo { get; set; }
    }
}

Step 5

 

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 DateView. 

 

Code Ref

using WebApplication1.Models;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Configuration;

namespace WebApplication1.Controllers
{
    public class DateController : Controller
    {
        // GET: Date
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult DateView(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_EmployeeDetailsWithAge", con)) //stored procedure name
                {
                    con.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@status", "DIS"); //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.ApplicantName = ds.Tables[0].Rows[i]["ApplicantName"].ToString(); //show records with selected columns
                        uobj.DOB = ds.Tables[0].Rows[i]["DOB"].ToString();
                        uobj.Age = ds.Tables[0].Rows[i]["Age"].ToString();
                        uobj.DOJ = ds.Tables[0].Rows[i]["DOJ"].ToString();

                        uobj.DOL = ds.Tables[0].Rows[i]["DOL"].ToString();
                        uobj.Expr = ds.Tables[0].Rows[i]["Expr"].ToString();
                        uobj.FatherName = ds.Tables[0].Rows[i]["FatherName"].ToString();
                        uobj.MotherName = ds.Tables[0].Rows[i]["MotherName"].ToString();

                        uobj.CountryName = ds.Tables[0].Rows[i]["CountryName"].ToString();
                        uobj.StateName = ds.Tables[0].Rows[i]["StateName"].ToString();
                        uobj.Mobile = ds.Tables[0].Rows[i]["Mobile"].ToString();
                        uobj.Email = ds.Tables[0].Rows[i]["Email"].ToString();

                        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 6

 

We need to add a view called DateView.cshtml.

 

Code Ref

@model WebApplication1.Models.DateDetails

@{
    /**/

    ViewBag.Title = "Date Details";
}

@*Post Data To Controller Without Page Refresh In*@
<script src="~/Scripts/jquery-3.3.1.js"></script>
<script src="~/Scripts/jquery.unobtrusive-ajax.js"></script>

<h4>Choose Below Options:</h4>

<style>
    table {
        font-family: arial, sans-serif;
        border-collapse: collapse;
        width: 100%;
    }

    td, th {
        border: 1px solid #dddddd;
        text-align: left;
        padding: 8px;
    }

    tr:nth-child(even) {
        background-color: #dddddd;
    }

    .button {
        background-color: #4CAF50;
        border: none;
        color: white;
        padding: 15px 32px;
        text-align: center;
        text-decoration: none;
        display: inline-block;
        font-size: 16px;
        margin: 4px 2px;
        cursor: pointer;
    }

    .button4 {
        border-radius: 9px;
    }

    input[type=date], select {
        width: 60%;
        padding: 12px 20px;
        margin: 8px 0;
        display: inline-block;
        border: 1px solid #ccc;
        border-radius: 4px;
        box-sizing: border-box;
    }

    input[type=text], select {
        width: 60%;
        padding: 12px 20px;
        margin: 8px 0;
        display: inline-block;
        border: 1px solid #ccc;
        border-radius: 4px;
        box-sizing: border-box;
    }
</style>
@*Filter records*@

@using (Html.BeginForm("DateView", "Date", FormMethod.Get))
{
    <span style="color:blue">From Date:</span><input type="date" name="From" />
    <span style="color:blue">To Date:</span><input type="date" name="To" /> <span> </span> <span> </span>

    <input type="submit" name="submit" value="Search" class="button button4" />

    <br/>
    <br/>
}

@if (Model != null)
{
    if (Model.usersinfo.Count > 0) /*Display records*/
    {
        <table align="center" border="1" cellpadding="4" cellspacing="4">
            <tr>
                <th style="background-color: Yellow;color: blue; width:140px">Applicant</th>
                <th style="background-color: Yellow;color: blue; width:140px">DOB</th>
                <th style="background-color: Yellow;color: blue; width:200px">Age</th>
                <th style="background-color: Yellow;color: blue; width:140px">DOJ</th>
                <th style="background-color: Yellow;color: blue; width:140px">DOL</th>
                <th style="background-color: Yellow;color: blue; width:200px">Expr</th>
                <th style="background-color: Yellow;color: blue; width:140px">Father</th>
                <th style="background-color: Yellow;color: blue; width:140px">Mother</th>
                <th style="background-color: Yellow;color: blue">Country</th>
                <th style="background-color: Yellow;color: blue">State</th>
                <th style="background-color: Yellow;color: blue">Mobile</th>
                <th style="background-color: Yellow;color: blue">Email</th>
            </tr>
            @foreach (var item in Model.usersinfo)
            {
            <tr>
                <td>@Html.DisplayFor(modelitem => item.ApplicantName) </td>
                <td>@Html.DisplayFor(modelitem => item.DOB)</td>
                <td>@Html.DisplayFor(modelitem => item.Age)</td>
                <td>@Html.DisplayFor(modelitem => item.DOJ)</td>
                <td>@Html.DisplayFor(modelitem => item.DOL) </td>
                <td>@Html.DisplayFor(modelitem => item.Expr)</td>
                <td>@Html.DisplayFor(modelitem => item.FatherName)</td>
                <td>@Html.DisplayFor(modelitem => item.MotherName)</td>
                <td>@Html.DisplayFor(modelitem => item.CountryName) </td>
                <td>@Html.DisplayFor(modelitem => item.StateName)</td>
                <td>@Html.DisplayFor(modelitem => item.Mobile)</td>
                <td>@Html.DisplayFor(modelitem => item.Email)</td>
            </tr>
            }
        </table>
    }
    else
    {
        <span style="color:red"><b>No Details Found.</b></span>
    }
}

@if (TempData["SelectOption"] != null)
{
    <script type="text/javascript">
        alert("From Date should be less than To Date");
    </script>
}

Code Description

 

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

 

Step 7

 

Add some flavor to the view page by modifying it in _Layout.cshtml.

 

Code Ref

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title - My ASP.NET Application</title>
    @Styles.Render("~/Content/css")
    @Scripts.Render("~/bundles/modernizr")
</head>
<body>
    <div class="navbar navbar-fixed-top" style="background-color:orangered;">
        <h4 style="color:white; text-align:center">Calculate Age and Experience Of Employees In MVC</h4>
    </div>
    <div class="container body-content">
        @RenderBody()
        <hr />
        <footer>
            <p style="background-color: Yellow; font-weight: bold; color:blue; text-align: center; font-style: oblique">© @DateTime.Now.ToLocalTime()</p> @*Add Date Time*@
        </footer>
    </div>

    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
    @RenderSection("scripts", required: false)
</body>
</html>

Output

 

The landing page is shown below:

 

 

Then filter data between two dates. It shows the calculated age and years of experience, along with other details of the employees.

 

 

If no records are found, then it is shown like this:

 

Then the alert is mentioned between the from date and to date comparison.

 

Link To Source Code

• Age and Years Of Experience calculation

Summary

 

In this article, we have learned:

• About scalar function and its uses in a stored procedure
• Calculating age and years of experience between dates
• Uses of dynamic SQL in a stored procedure for filtering records
• Managing alert message in MVC and design view using layout