In this article I am going to explain how we can edit and update records while keeping old records as they are in MySql using ASP.NET. While we are working on a payroll application we got this type of requirement so I am going to explain how I did it. In general we replace the old data with the new but by using this we can retain the old data along with new one.
Actually we did it according to the following points:
- The user can only edit a record that has an End Date of '9999-12-31'.
- The user cannot edit or update the record on the same day, which means that if he inserted the record on the current date he cannot edit that same record as the End Date of the record should be Current Date – 1.
In this code I have hard coded the Employee ID and declared some variables as static variables as I am giving a sample which I have done. One can change according to his need.
This process is said to be delimitation of records.
Employee address table:
CREATE TABLE `tblemployeeaddressdetails1` (
`EmpID` int(11) NOT NULL DEFAULT '0',
`Address1` varchar(45) DEFAULT NULL,
`Address2` varchar(45) DEFAULT NULL,
`City` varchar(45) DEFAULT NULL,
`ZipCode` varchar(45) DEFAULT NULL,
`Country` varchar(45) DEFAULT NULL,
`StartDate` date DEFAULT NULL,
`EndDate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$
I used 3 stored procedures; namely:
- uspEmployeeAddressUpdate
- uspGetEmployeeAddressDate
- uspGetEmployeeaddressdetails
Detailed stored procedure of uspGetEmployeeaddressdetails
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `uspGetEmployeeaddressdetails`(_EmpID int(11),
_EndDate date,
_StartDate date)
BEGIN
select * from tblemployeeaddressdetails1 where EmpID=_EmpID and StartDate=_StartDate and EndDate=_EndDate;
END
Detailed stored procedure for uspGetEmployeeAddressDate
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `uspGetEmployeeAddressDate`(_EmpID INT(11))
BEGIN
select EmpID,DATE_FORMAT(StartDate, '%Y-%m-%d')
as StartDate,DATE_FORMAT(EndDate, '%Y-%m-%d')
as EndDate from tblemployeeaddressdetails1 where EmpID=_EmpID;
END
Detailed stored procedure for uspEmployeeAddressUpdate:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `uspEmployeeAddressUpdate`(_EmpId int,
_Address1 varchar(30),
_Address2 varchar(30),
_City varchar(30),
_ZipCode numeric(9,0),
_Country varchar(20),
_EndDate datetime)
BEGIN
declare p_ecount int;
set p_ecount=(select count(1) from tblemployeeaddressdetails1 where
Address1=_Address1 and
Address2=_Address2 and
City=_City and
ZipCode=_ZipCode and
Country=_Country and
EndDate='9999-12-31');
if p_ecount=0 then
begin
update tblemployeeaddressdetails1
set
EndDate=_EndDate
where EmpID=_EmpID and EndDate="9999-12-31";
insert into tblemployeeaddressdetails1 (EmpID,Address1,Address2,City,ZipCode,Country,Startdate,Enddate) values
(_EmpID,_Address1,_Address2,_City,_ZipCode,_Country,curdate(),'9999-12-31');
end;
end if;
END