Managing allotment data manually from emails can be time-consuming and error-prone, especially for financial applications. In this tutorial, we will build a C# solution to automatically read emails, download CSV attachments, and update the database.
This system works with emails containing allotment files (subject contains "AllotmentsFiles" ) and updates the bidfiledetails table in SQL Server.
1. Overview
The process consists of three main steps:
Connect to the email server using POP3.
Read today's emails with the subject "AllotmentsFiles" .
Download CSV attachments , read their content, and update the database with allotment details.
Benefits of this approach:
Automates repetitive manual work
Reduces errors in updating allotment data
Works daily without human intervention
2. Required Tools and Libraries
<appSettings>
<add key="HostServer" value="pop.gmail.com" />
<add key="MailPort" value="995" />
<add key="MailUser" value="[email protected]" />
<add key="MailPwd" value="your-password" />
</appSettings>
3. C# Code to Read Emails and Process CSVs
public void ReadTheDataFromTheMail()
{
try
{
string mailHost = ConfigurationManager.AppSettings["HostServer"];
int mailPort = Convert.ToInt32(ConfigurationManager.AppSettings["MailPort"]);
string mailID = ConfigurationManager.AppSettings["MailUser"];
string mailPwd = ConfigurationManager.AppSettings["MailPwd"];
using (Pop3Client client = new Pop3Client())
{
client.Connect(mailHost, mailPort, true);
client.Authenticate(mailID, mailPwd);
int messageCount = client.GetMessageCount();
DateTime today = DateTime.UtcNow.Date;
for (int i = messageCount; i > 0; i--)
{
MessageHeader headers = client.GetMessageHeaders(i);
string subject = headers.Subject;
DateTime emailDate = headers.DateSent;
if (!string.IsNullOrEmpty(subject) && subject.Contains("AllotmentsFiles") && emailDate.Date == today)
{
OpenPop.Mime.Message message = client.GetMessage(i);
foreach (var attachment in message.FindAllAttachments())
{
if (attachment.FileName.Contains("AllotmentsFiles_") && attachment.FileName.EndsWith(".csv"))
{
string baseDirectory = @"E:\Allotment - Applns\Allotmentfiles";
string dateFolder = DateTime.Now.ToString("yyyy-MM-dd");
string fileFolder = Path.Combine(baseDirectory, dateFolder);
if (!Directory.Exists(fileFolder))
Directory.CreateDirectory(fileFolder);
string filePath = Path.Combine(fileFolder, attachment.FileName);
File.WriteAllBytes(filePath, attachment.Body);
errorlog($"CSV file '{attachment.FileName}'", "Downloaded successfully!");
ProcessAllotmentData(filePath);
}
}
}
}
}
}
catch (Exception ex)
{
errorlog("Error reading emails", ex.Message);
}
}
4. Process CSV and Update Database
public void ProcessAllotmentData(string filePath)
{
try
{
string fileName = Path.GetFileName(filePath);
string[] parts = fileName.Split('_');
string scriptName = parts.Length > 0 ? parts[0] : null;
using (StreamReader sr = new StreamReader(filePath))
{
string[] rows = sr.ReadToEnd().Split('\n');
for (int i = 1; i < rows.Length; i++)
{
string[] rowValues = rows[i].Split(',');
if (rowValues.Length > 7)
{
string appNo = rowValues[2].Trim();
string pan = rowValues[4].Trim();
string qty = rowValues[5].Trim();
string clientName = rowValues[6].Trim();
string reason = rowValues[7].Trim();
SqlParameter[] param = {
new SqlParameter("@symbol", scriptName),
new SqlParameter("@appno", appNo),
new SqlParameter("@pan", pan),
new SqlParameter("@qty", qty),
new SqlParameter("@reason", reason)
};
string statusQuery = @"SELECT * FROM bidfiledetails (NOLOCK)
WHERE AppNo=@appno AND Symbol=@symbol AND PanNo=@pan AND AllotmentFlag != 'Y'";
DataSet ds = SqlHelper.ExecuteDataset(SqlCon, CommandType.Text, statusQuery, param);
if (ds.Tables[0].Rows.Count > 0)
{
string updateQuery = @"UPDATE bidfiledetails
SET AllotmentFlag='Y', SharesAlloted=@qty, Sharesrej_reason=@reason
WHERE AppNo=@appno AND PanNo=@pan AND Symbol=@symbol";
SqlHelper.ExecuteNonQuery(SqlCon, CommandType.Text, updateQuery, param);
errorlog("Updated Record", $"PanNo: {pan}, ApplicationNo: {appNo}, Symbol: {scriptName}");
}
else
{
string header = "ApplicationNo|ClientName|Quantity|Reason|PanNo";
string message = $"{appNo}|{clientName}|{qty}|{reason}|{pan}{Environment.NewLine}";
errorlog(header, message);
}
}
else
{
errorlog("Row does not have enough columns", "");
}
}
}
}
catch (Exception ex)
{
ExceptionLogging.Exceptionlog("Exception in ProcessAllotmentData", ex.Message);
}
}
5. Key Points
Uses OpenPop.NET to read emails over POP3.
Automatically filters emails by subject "AllotmentsFiles" .
Downloads CSV attachments , organizes them in date-based folders .
Reads CSV rows, validates data, and updates SQL Server table.
Proper error logging for failed rows or exceptions.
Can be scheduled daily using Windows Task Scheduler or a Windows Service.
6. Next Steps / Enhancements
Convert this into a Windows Service for fully automated processing.
Add email notifications if some rows failed or updates were successful.
Improve logging by storing logs in a SQL table for historical tracking.
Implement retry logic for emails that fail to download.