How to make code below is faster and prevent hangs when timer works ?

[engahmedbarbary]

problem

How to make code below is faster and more performance good ?

I have windows form have 4 controls picturebox and 4 labels and timer control work every second

every picture box represent one gate as GatePic1,GatePic2,GatePic3,GatePic4 and 4 labels represent lblwelcome1,2,3,4

after i press start button screen is heavy and some times make black screen for 2 seconds then after that every

thing will hangs but not always hangs so that

i try to write code more performance i need to solve this hangs and not repeated

my code as below

SELECT ZKAccess.dbo.acc_monitor_log.id, ZKAccess.dbo.acc_monitor_log.device_id, ZKAccess.dbo.USERINFO.Badgenumber, ZKAccess.dbo.USERINFO.CardNo
FROM ZKAccess.dbo.acc_monitor_log LEFT OUTER JOIN
ZKAccess.dbo.USERINFO ON ZKAccess.dbo.acc_monitor_log.pin = ZKAccess.dbo.USERINFO.Badgenumber AND
ZKAccess.dbo.acc_monitor_log.card_no = ZKAccess.dbo.USERINFO.CardNo
WHERE (ZKAccess.dbo.acc_monitor_log.id =
(SELECT TOP (1) id
FROM ZKAccess.dbo.acc_monitor_log AS acc_monitor_log_1
ORDER BY id DESC)) AND (ZKAccess.dbo.acc_monitor_log.device_id =
(SELECT TOP (1) MachineId
FROM dbo.MachineSettingsData
ORDER BY MachineId)) AND (ZKAccess.dbo.USERINFO.CardNo IS NOT NULL OR
ZKAccess.dbo.USERINFO.CardNo <> '') AND (ZKAccess.dbo.USERINFO.Badgenumber IS NOT NULL OR
ZKAccess.dbo.USERINFO.Badgenumber <> '')
UNION ALL
SELECT ZKAccess.dbo.acc_monitor_log.id, ZKAccess.dbo.acc_monitor_log.device_id, ZKAccess.dbo.USERINFO.Badgenumber, ZKAccess.dbo.USERINFO.CardNo
FROM ZKAccess.dbo.acc_monitor_log LEFT OUTER JOIN
ZKAccess.dbo.USERINFO ON ZKAccess.dbo.acc_monitor_log.pin = ZKAccess.dbo.USERINFO.Badgenumber AND
ZKAccess.dbo.acc_monitor_log.card_no = ZKAccess.dbo.USERINFO.CardNo
WHERE (ZKAccess.dbo.acc_monitor_log.id =
(SELECT TOP (1) id
FROM ZKAccess.dbo.acc_monitor_log AS acc_monitor_log_1
WHERE (device_id =
(SELECT MachineId
FROM dbo.MachineSettingsData AS a
WHERE (2 =
(SELECT COUNT(MachineId) AS Expr1
FROM dbo.MachineSettingsData AS b
WHERE (a.MachineId >= MachineId))))) AND (ZKAccess.dbo.USERINFO.CardNo IS NOT NULL OR
ZKAccess.dbo.USERINFO.CardNo <> '') AND (ZKAccess.dbo.USERINFO.Badgenumber IS NOT NULL OR
ZKAccess.dbo.USERINFO.Badgenumber <> '')
ORDER BY id DESC))
UNION ALL
SELECT ZKAccess.dbo.acc_monitor_log.id, ZKAccess.dbo.acc_monitor_log.device_id, ZKAccess.dbo.USERINFO.Badgenumber, ZKAccess.dbo.USERINFO.CardNo
FROM ZKAccess.dbo.acc_monitor_log LEFT OUTER JOIN
ZKAccess.dbo.USERINFO ON ZKAccess.dbo.acc_monitor_log.pin = ZKAccess.dbo.USERINFO.Badgenumber AND
ZKAccess.dbo.acc_monitor_log.card_no = ZKAccess.dbo.USERINFO.CardNo
WHERE (ZKAccess.dbo.acc_monitor_log.id =
(SELECT TOP (1) id
FROM ZKAccess.dbo.acc_monitor_log AS acc_monitor_log_1
WHERE (device_id =
(SELECT MachineId
FROM dbo.MachineSettingsData AS a
WHERE (3 =
(SELECT COUNT(MachineId) AS Expr1
FROM dbo.MachineSettingsData AS b
WHERE (a.MachineId >= MachineId))))) AND (ZKAccess.dbo.USERINFO.CardNo IS NOT NULL OR
ZKAccess.dbo.USERINFO.CardNo <> '') AND (ZKAccess.dbo.USERINFO.Badgenumber IS NOT NULL OR
ZKAccess.dbo.USERINFO.Badgenumber <> '')
ORDER BY id DESC))
UNION ALL
SELECT ZKAccess.dbo.acc_monitor_log.id, ZKAccess.dbo.acc_monitor_log.device_id, ZKAccess.dbo.USERINFO.Badgenumber, ZKAccess.dbo.USERINFO.CardNo
FROM ZKAccess.dbo.acc_monitor_log LEFT OUTER JOIN
ZKAccess.dbo.USERINFO ON ZKAccess.dbo.acc_monitor_log.pin = ZKAccess.dbo.USERINFO.Badgenumber AND
ZKAccess.dbo.acc_monitor_log.card_no = ZKAccess.dbo.USERINFO.CardNo
WHERE (ZKAccess.dbo.acc_monitor_log.id =
(SELECT TOP (1) id
FROM ZKAccess.dbo.acc_monitor_log AS acc_monitor_log_1
WHERE (device_id =
(SELECT MachineId
FROM dbo.MachineSettingsData AS a
WHERE (4 =
(SELECT COUNT(MachineId) AS Expr1
FROM dbo.MachineSettingsData AS b
WHERE (a.MachineId >= MachineId))))) AND (ZKAccess.dbo.USERINFO.CardNo IS NOT NULL OR
ZKAccess.dbo.USERINFO.CardNo <> '') AND (ZKAccess.dbo.USERINFO.Badgenumber IS NOT NULL OR
ZKAccess.dbo.USERINFO.Badgenumber <> '')
ORDER BY id DESC))



when run query above it run quickly may be less 1 second

i need to avoid any thing make hangs on my code csharp below


public DataTable GetAllMachinesData()
{
string sql = "";

sql = "select * from ViewAllMachinesData ";



DataTable dtgetpunch = DataAccess.ExecuteDataTable(sql);
return dtgetpunch;
}



private void TimerGate1_Tick(object sender, EventArgs e)
{


//2- Get Last Record On Database
dtgetlastpunch = annaul.GetAllMachinesData();
if (dtgetlastpunch.Rows.Count > 0)
{

foreach (DataRow row in dtgetlastpunch.Rows)
{
CurrentId = Utilities.ObjectConverter.ConvertToLongInteger(row["id"]);
MemberCodeMachine1 = Utilities.ObjectConverter.ConvertToString(row["Badgenumber"]);
MachineId = Utilities.ObjectConverter.ConvertToInteger(row["device_id"]);
//For Gate Number 1
if (MachineId == GetMachine1)
{
if (CurrentId == PreviousIdForMachine1)
{
CountGate1++;
GetMembersData(MemberCodeMachine1, GetMachine1);
}
else
{
CountGate1 = 0;
PreviousIdForMachine1 = CurrentId;
}
if (CountGate1 >= 30)
{
GatePic1.Image = null;
lblwelcome1.Visible = false;
}
else
{
lblwelcome1.Visible = true;
}
}
//For Gate Number 2
if (MachineId == GetMachine2)
{
if (CurrentId == PreviousIdForMachine2)
{
CountGate2++;
GetMembersData(MemberCodeMachine1, GetMachine2);
}
else
{
CountGate2 = 0;
PreviousIdForMachine2 = CurrentId;
}
if (CountGate2 >= 30)
{
GatePic2.Image = null;
lblwelcome2.Visible = false;
}
else
{
lblwelcome2.Visible = true;
}
}
//For Gate Number 3
if (MachineId == GetMachine3)
{
if (CurrentId == PreviousIdForMachine3)
{
CountGate3++;
GetMembersData(MemberCodeMachine1, GetMachine3);
}
else
{
CountGate3 = 0;
PreviousIdForMachine3 = CurrentId;
}
if (CountGate3 >= 30)
{
GatePic3.Image = null;
lblwelcome3.Visible = false;
}
else
{
lblwelcome3.Visible = true;
}
}
//For Gate Number 4
if (MachineId == GetMachine4)
{
if (CurrentId == PreviousIdForMachine4)
{
CountGate4++;
GetMembersData(MemberCodeMachine1, GetMachine4);
}
else
{
CountGate4 = 0;
PreviousIdForMachine4 = CurrentId;
}
if (CountGate4 >= 30)
{
GatePic4.Image = null;
lblwelcome4.Visible = false;
}
else
{
lblwelcome4.Visible = true;
}
}
}



}
//after 30 second



//==============
}

private void BtnStart_Click(object sender, EventArgs e)
{
TimerGate1.Enabled = true;
TimerGate1.Start();
}


timer above work every second and enabled after press startbutton

if there are any new record then display his data and image on picturebox if no new record within 30 second then image and data will displayed

after 30 second data will hidden

Continue reading...