private void button2_Click_2(object sender, EventArgs e)
{
// 禁用按钮防止重复点击
button1.Enabled = false;
progressBar1.Value = 0;
label1.Text = "准备中...";
// 创建并配置BackgroundWorker
BackgroundWorker worker = new BackgroundWorker();
worker.WorkerReportsProgress = true;
worker.WorkerSupportsCancellation = true;
worker.DoWork += (s, args) =>
{
string connectionString = "Server=10.50.32.244;Port=9080;Database=weiwantian_db;Uid=weiwantian;Pwd=weiwantian123...;CharSet=utf8mb4;";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
try
{
connection.Open();
MySqlTransaction transaction = connection.BeginTransaction();
// 获取当前年月
DateTime currentDate = DateTime.Now;
int year = currentDate.Year;
int month = currentDate.Month;
int daysInMonth = DateTime.DaysInMonth(year, month);
// 查询在职人员数据并存入DataTable
string selectQuery = @"
SELECT
`14ID`, ID_Card_Number, Name, Type, Department,
Section, Workshop, Large_Group, Team,
Section_Leader, Workshop_Leader, Large_Group_Leader, Team_Leader
FROM zsg_personnel_list
WHERE Resignation_Type = '在职'";
DataTable personnelTable = new DataTable();
using (MySqlCommand selectCommand = new MySqlCommand(selectQuery, connection, transaction))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(selectCommand))
{
adapter.Fill(personnelTable);
}
}
// 计算总任务量
int totalTasks = personnelTable.Rows.Count * daysInMonth;
int completedTasks = 0;
// 存在性检查SQL
string existsQuery = @"
SELECT COUNT(1)
FROM ri_gongshi
WHERE `14ID` = @ID AND data = @Data";
// 插入SQL
string insertQuery = @"
INSERT INTO ri_gongshi
(`14ID`, ID_Card_Number, Name, Type, Department,
Section, Workshop, Large_Group, Team,
Section_Leader, Workshop_Leader, Large_Group_Leader, Team_Leader, data)
VALUES
(@ID, @IDCard, @Name, @Type, @Department,
@Section, @Workshop, @LargeGroup, @Team,
@SectionLeader, @WorkshopLeader, @LargeGroupLeader, @TeamLeader, @Data)";
using (MySqlCommand existsCommand = new MySqlCommand(existsQuery, connection, transaction))
using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection, transaction))
{
// 定义存在性检查参数
existsCommand.Parameters.Add("@ID", MySqlDbType.VarChar);
existsCommand.Parameters.Add("@Data", MySqlDbType.DateTime);
// 定义插入参数
insertCommand.Parameters.Add("@ID", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@IDCard", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@Name", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@Type", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@Department", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@Section", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@Workshop", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@LargeGroup", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@Team", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@SectionLeader", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@WorkshopLeader", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@LargeGroupLeader", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@TeamLeader", MySqlDbType.VarChar);
insertCommand.Parameters.Add("@Data", MySqlDbType.DateTime);
int insertCount = 0;
int skipCount = 0;
// 遍历DataTable中的每一行
for (int rowIndex = 0; rowIndex < personnelTable.Rows.Count; rowIndex++)
{
DataRow row = personnelTable.Rows[rowIndex];
for (int day = 1; day <= daysInMonth; day++)
{
// 检查是否请求取消
if (worker.CancellationPending)
{
args.Cancel = true;
transaction.Rollback();
return;
}
completedTasks++;
int progressPercentage = (int)((double)completedTasks / totalTasks * 100);
// 报告进度
worker.ReportProgress(progressPercentage,
new
{
CurrentRow = rowIndex + 1,
TotalRows = personnelTable.Rows.Count,
Day = day,
DaysInMonth = daysInMonth,
InsertCount = insertCount,
SkipCount = skipCount
});
DateTime recordDate = new DateTime(year, month, day);
// 检查记录是否存在
existsCommand.Parameters["@ID"].Value = row["14ID"];
existsCommand.Parameters["@Data"].Value = recordDate;
int count = Convert.ToInt32(existsCommand.ExecuteScalar());
if (count > 0)
{
skipCount++;
continue; // 记录已存在，跳过插入
}
// 设置插入参数值
insertCommand.Parameters["@ID"].Value = row["14ID"];
insertCommand.Parameters["@IDCard"].Value = row["ID_Card_Number"];
insertCommand.Parameters["@Name"].Value = row["Name"];
insertCommand.Parameters["@Type"].Value = row["Type"];
insertCommand.Parameters["@Department"].Value = row["Department"];
insertCommand.Parameters["@Section"].Value = row["Section"];
insertCommand.Parameters["@Workshop"].Value = row["Workshop"];
insertCommand.Parameters["@LargeGroup"].Value = row["Large_Group"];
insertCommand.Parameters["@Team"].Value = row["Team"];
insertCommand.Parameters["@SectionLeader"].Value = row["Section_Leader"];
insertCommand.Parameters["@WorkshopLeader"].Value = row["Workshop_Leader"];
insertCommand.Parameters["@LargeGroupLeader"].Value = row["Large_Group_Leader"];
insertCommand.Parameters["@TeamLeader"].Value = row["Team_Leader"];
insertCommand.Parameters["@Data"].Value = recordDate;
insertCommand.ExecuteNonQuery();
insertCount++;
// 每处理100条记录报告一次进度，减少UI更新频率
if (completedTasks % 100 == 0)
{
worker.ReportProgress(progressPercentage,
new
{
CurrentRow = rowIndex + 1,
TotalRows = personnelTable.Rows.Count,
Day = day,
DaysInMonth = daysInMonth,
InsertCount = insertCount,
SkipCount = skipCount
});
}
}
}
transaction.Commit();
args.Result = new { InsertCount = insertCount, SkipCount = skipCount, Total = totalTasks };
}
}
catch (Exception ex)
{
args.Result = ex;
}
}
};
worker.ProgressChanged += (s, args) =>
{
dynamic progressInfo = args.UserState;
progressBar1.Value = args.ProgressPercentage;
label1.Text = $"处理中: {args.ProgressPercentage}% - " +
$"人员: {progressInfo.CurrentRow}/{progressInfo.TotalRows}, " +
$"日期: {progressInfo.Day}/{progressInfo.DaysInMonth}, " +
$"新增: {progressInfo.InsertCount}, 跳过: {progressInfo.SkipCount}";
};
worker.RunWorkerCompleted += (s, args) =>
{
button1.Enabled = true;
if (args.Cancelled)
{
MessageBox.Show("操作已取消");
label1.Text = "已取消";
}
else if (args.Result is Exception ex)
{
MessageBox.Show($"操作失败：{ex.Message}");
label1.Text = "操作失败";
}
else
{
dynamic result = args.Result;
MessageBox.Show($"数据处理完成！\n" +
$"总记录数: {result.Total}\n" +
$"新增记录: {result.InsertCount}\n" +
$"已存在记录: {result.SkipCount}");
label1.Text = "处理完成";
}
};
// 启动后台操作
worker.RunWorkerAsync();
}执行太慢，耗时较长，请优化
扫一扫
4809
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
