Overview
In enterprise attendance systems, especially those utilizing facial recognition, accurate data logging and timely exports are vital. In this article, we’ll walk through a real-world Windows Forms application that automatically fetches employee attendance data and exports it as a CSV file using AppBlock’s SqlHelper, with built-in error and process logging.
Key Functionalities
1. Form Timer + Manual Button Trigger
The application allows automated and manual execution of log processing.
timer1_Tick => CSVFiles();
button1_Click => CSVFiles();
This gives flexibility to either schedule or manually trigger data exports.
2. Error Logging Functionality
All exceptions are logged into a daily log file for easy debugging.
public void ErrorLog(string log)
{
// Add your error logging logic here
}
- Log Path: /ErrorLog/
- File: ddMMyy.txt
Each log entry includes.
- Timestamp
- Error message
- Separator line
3. Process Logging
Process milestones are written into a separate log file.
public void ProcessLog(string log)
{
// Your implementation here
}
- Log Path: /ProcessLog/
- File: ddMMyy.txt
This helps trace successful updates, such as which record (sno) was processed.
4. Data Extraction from SQL Server
Using AppBlock’s SqlHelper, the app connects to SQL Server and fetches logs from the LogTable and EmployeeDetails.
string qry = "SELECT b.payrollid, a.ID, ...";
DataSet ds = SqlHelper.ExecuteDataset(...);
Data includes: PayrollID, Name, LoginTime, LogoutTime, LogID, and more.
The connection string is loaded via.
ConfigurationManager.ConnectionStrings["Signin"].ToString();
5. CSV Export with Timestamped Filenames
The data is exported as a timestamped CSV file for easy tracking.
string filePath = Path.Combine(
source + "/File/",
"Face_" + DateTime.Now.ToString("ddMMyyyy_HHmm") + ".csv"
);
File.WriteAllText(
filePath,
csvContent.ToString(),
Encoding.UTF8
);
- Folder: /File/
- Filename: Face_ddMMyyyy_HHmm.csv
The use of Encoding.UTF-8 ensures compatibility across systems.
6. Time-Based Data Processing
The application checks for conditions based on current time and stored time/date values before exporting.
if (DateTime.Now.Date > tbleDate && tbleTime <= currentTime)
{
// Your logic here
}
Once processed, the tbleDate is updated.
UPDATE signin_Timer
SET tbleDate = GETDATE()
WHERE sno = ...;
update signin_Timer set tbleDate=getdate() where sno = ...
using AppBlock;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Globalization;
using System.Threading;
namespace User_Signin
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void timer1_Tick(object sender, EventArgs e)
{
try
{
CSVFiles();
}
catch (Exception ex)
{
string error = ex.Message.ToString();
ErrorLog(error);
}
}
private void button1_Click_1(object sender, EventArgs e)
{
try
{
CSVFiles();
}
catch (Exception ex)
{
string error = ex.Message.ToString();
ErrorLog(error);
}
}
public void ErrorLog(string log)
{
try
{
string source = Application.StartupPath;
string filepath = source + "/ErrorLog/";
if (!Directory.Exists(filepath))
{
Directory.CreateDirectory(filepath);
}
StreamWriter sw = new StreamWriter(filepath + DateTime.Now.ToString("ddMMyy") + ".txt", true);
sw.WriteLine(DateTime.Now.ToString());
sw.WriteLine(log);
sw.WriteLine("---------------------------------------------------------------------------------------------------------");
sw.Close();
}
catch (Exception Ex)
{
string str = Ex.ToString();
}
}
public void ProcessLog(string log)
{
try
{
string source = Application.StartupPath;
string filepath = source + "/ProcessLog/";
if (!Directory.Exists(filepath))
{
Directory.CreateDirectory(filepath);
}
StreamWriter sw = new StreamWriter(filepath + DateTime.Now.ToString("ddMMyy") + ".txt", true);
sw.WriteLine(DateTime.Now.ToString());
sw.WriteLine(log);
sw.WriteLine("---------------------------------------------------------------------------------------------------------");
sw.Close();
}
catch (Exception Ex)
{
string str = Ex.ToString();
}
}
public void CSVFiles()
{
string qry4 = "select * from signintable_Timer";
DataSet ds4 = SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["Signin"].ToString(), CommandType.Text, qry4);
for (int i = 0; i < ds4.Tables[0].Rows.Count; i++)
{
string ss = ds4.Tables[0].Rows[i]["sno"].ToString();
string currentdate = DateTime.Now.Date.ToString();
string predate = Convert.ToDateTime(ds4.Tables[0].Rows[i]["tbleDate"].ToString()).ToString();
string prvtime = Convert.ToDateTime(ds4.Tables[0].Rows[i]["tbleTime"].ToString()).ToString();
string currenttime = Convert.ToDateTime(DateTime.Now.ToString("hh:mm tt")).ToString();
if ((DateTime.Now.Date) > Convert.ToDateTime(ds4.Tables[0].Rows[i]["tbleDate"].ToString()) &&
(Convert.ToDateTime(ds4.Tables[0].Rows[i]["tbleTime"].ToString()) <= Convert.ToDateTime(DateTime.Now.ToString("hh:mm tt"))))
{
ProcessLog(" Update Sno is : " + ds4.Tables[0].Rows[i]["sno"].ToString());
string qry = @"SELECT b.payrollid, a.ID, b.Id as SigninID, a.Name, a.LoginTime, a.LogoutTime, a.Flag, a.Reason, a.LogID, a.CV
FROM [Signin].[dbo].[LogTable] a (nolock)
INNER JOIN [Signin].[dbo].[EmployeeDetails] b (nolock)
ON a.name = b.empid
WHERE logintime >= CONVERT(varchar(10), GETDATE(), 102)
ORDER BY a.id";
DataSet ds = SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["Signin"].ToString(), CommandType.Text, qry);
StringBuilder csvContent = new StringBuilder();
csvContent.AppendLine("PayrollID, ID, SigninID, Name, LoginTime, LogoutTime, Flag, Reason, LogID, CV");
foreach (DataRow row in ds.Tables[0].Rows)
{
string formattedLoginTime = "";
string formattedLogoutTime = "";
if (((row["LoginTime"] != DBNull.Value) && (!string.IsNullOrEmpty(row["LoginTime"].ToString()))) &&
((row["LogoutTime"] != DBNull.Value) && (!string.IsNullOrEmpty(row["LogoutTime"].ToString()))))
{
formattedLoginTime = Convert.ToDateTime(row["LoginTime"]).ToString("MMM dd yyyy hh:mmtt");
formattedLogoutTime = Convert.ToDateTime(row["LogoutTime"]).ToString("MMM dd yyyy hh:mmtt");
}
csvContent.AppendLine(
string.Join(",",
row["payrollid"],
row["id"],
row["SigninID"],
row["Name"],
formattedLoginTime,
formattedLogoutTime,
row["flag"],
row["reason"],
row["logid"],
row["cv"]
)
);
}
string source = Application.StartupPath;
string filePath = Path.Combine(source + "/File/", "Face_" + DateTime.Now.ToString("ddMMyyyy_HHmm") + ".csv");
File.WriteAllText(filePath, csvContent.ToString(), Encoding.UTF8);
string updqry1 = "update Signin_Timer set tbleDate=getdate() where sno = " + ds4.Tables[0].Rows[i]["sno"].ToString();
SqlHelper.ExecuteNonQuery(ConfigurationManager.ConnectionStrings["Signin"].ToString(), CommandType.Text, updqry1);
}
}
}
}
}
App.config
<appSettings>
<add key="time1" value="09:00 AM"/>
<add key="time2" value="12:00 PM"/>
<add key="time3" value="04:00 PM"/>
</appSettings>
<connectionStrings>
<add name="Signin"
connectionString="Data Source=softsql;Initial Catalog=FaceSign;Connect TimeOut=60; Max Pool Size=10000; user id=sa; password=capmark@09"
providerName="System.Data.SqlClient"/>
</connectionStrings>
This implementation is ideal for organizations needing daily attendance logs from a database.