使用 C# 和 SQL Server 自动化邮件中的用户分配数据处理
引言
在金融应用领域，通过电子邮件手动管理用户分配数据不仅耗时而且容易出错。传统的人工处理方式需要工作人员每天检查邮件、下载附件、解析数据并更新数据库，这一系列重复性工作既低效又存在数据录入错误的风险。本文将介绍如何利用 C# 和 SQL Server 构建一个自动化解决方案，实现从邮件读取、附件下载到数据库更新的全流程自动化处理。该系统特别适用于处理主题包含"AllotmentsFiles"的邮件，并更新 SQL Server 中的 bidfiledetails 表，显著提高数据处理效率和准确性。
正文内容
1. 系统概述与优势
1.1 处理流程
该自动化系统主要包含三个核心处理步骤：
- 连接邮件服务器：使用 POP3 协议连接到邮件服务器，支持 Gmail 等常见邮件服务提供商。
- 筛选目标邮件：自动筛选出当天收到的、主题包含"AllotmentsFiles"的邮件。
- 处理附件并更新数据库：下载邮件中的 CSV 附件，解析内容并更新 SQL Server 数据库中的分配数据。
1.2 系统优势
- 自动化重复工作：取代人工检查邮件和处理附件的过程
- 减少人为错误：自动化的数据处理流程避免了手工录入可能导致的错误
- 全天候运行：系统可配置为每日自动运行，无需人工干预
- 高效数据处理：批量处理大量分配数据，显著提高工作效率
2. 开发环境与工具配置
2.1 所需工具
- 开发工具：Visual Studio（C#）
- 数据库：SQL Server
- NuGet 包：
- OpenPop.NET：用于通过 POP3 协议读取邮件
- System.Data.SqlClient：用于数据库连接和操作
2.2 配置设置
系统配置存储在 web.config 文件中，包含邮件服务器连接信息：
<appSettings>
<add key="HostServer" value="pop.gmail.com" />
<add key="MailPort" value="995" />
<add key="MailUser" value="your-email@gmail.com" />
<add key="MailPwd" value="your-password" />
</appSettings>
此配置允许灵活更改邮件服务器设置而无需修改代码。
3. 邮件处理核心代码实现
3.1 读取邮件功能
以下 C# 代码实现了从邮件服务器读取并处理目标邮件的功能：
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);
}
}
这段代码实现了以下功能：
- 从配置读取邮件服务器连接信息
- 使用 POP3 连接到邮件服务器
- 筛选当天且主题包含"AllotmentsFiles"的邮件
- 下载符合条件的 CSV 附件并保存到按日期组织的文件夹中
- 调用数据处理方法处理下载的附件
4. CSV数据处理与数据库更新
4.1 数据处理流程
下载的 CSV 文件需要被解析并更新到数据库中，以下是实现代码：
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);
}
}
此代码实现了：
- 从CSV文件名提取脚本名称
- 逐行读取CSV文件内容
- 验证数据完整性（确保有足够列数）
- 检查数据库中的记录状态
- 更新符合条件的记录或记录未匹配的情况
- 全面的错误处理和日志记录
5. 关键技术与实现要点
5.1 核心技术组件
- 邮件处理：使用 OpenPop.NET 库通过 POP3 协议读取邮件
- 文件处理：系统自动按日期组织下载的附件文件
- 数据验证：严格检查CSV文件结构和数据完整性
- 数据库操作：使用参数化查询确保SQL注入防护
- 错误处理：全面的异常捕获和日志记录机制
5.2 系统部署方式
- 任务计划程序：配置为每日自动运行的Windows计划任务
- Windows服务：可转换为服务实现24/7运行
- 日志监控：通过日志文件监控系统运行状态
6. 系统扩展与优化方向
6.1 功能增强建议
- 转换为Windows服务：实现完全自动化的后台处理
- 邮件通知功能：在处理完成或出现错误时发送通知邮件
- 日志数据库存储：将日志存入SQL表便于历史查询和分析
- 重试机制：为失败的邮件下载实现自动重试逻辑
6.2 性能优化建议
- 实现批量更新操作减少数据库往返
- 添加并发处理能力提高大量邮件的处理速度
- 优化内存使用处理超大附件文件
结论
本文详细介绍了一个基于C#和SQL Server的自动化邮件处理系统，能够高效地从邮件中提取分配数据并更新数据库。该系统通过自动化取代了繁琐的手工操作，显著提高了金融应用中分配数据处理的效率和准确性。核心功能包括邮件服务器连接、目标邮件筛选、CSV附件下载与解析以及数据库更新等完整流程。系统采用模块化设计，具有良好的可扩展性，可通过转换为Windows服务、添加通知功能等方式进一步增强。该解决方案不仅适用于金融领域的分配数据处理，其设计思路和实现方法也可应用于其他需要从邮件自动提取数据并更新数据库的类似场景。
浙公网安备 33010602011771号