台账管理系统中如何逐行实现贷方减借方的滚动余额计算？

需求与问题 #

我正在开发台账管理系统（Ledger Management System），系统包含台账报表（Ledger Report）和账户报表（Account Report）两类报表。在账户报表中，从debit（借方）表和credit（贷方）表取数展示在dataGridView中。

设置了**Remaining（余额）**字段，预期规则：

• 当前行余额 = 贷方金额 - 借方金额
• 当前行余额需作为下一行的借方金额，供下一笔贷方金额继续抵扣

当前问题：查询返回的每行借方金额都是原始数据库值，导致后续行始终用原始借方计算余额，结果错误。需要实现从第二行开始，用当前行贷方金额减去上一行的余额（即当前行借方值），依次递推。

当前代码 #

private void PopulateAccountReport()
{
    string selectedName = comboBox2.SelectedItem.ToString();
    OleDbConnection connection = new OleDbConnection(connectionString);

    string query = "SELECT c.r_name AS 'Name', c.r_date AS 'Credit Date', c.r_amount AS 'Credit', d.d_date AS 'Debit Date', d.d_amount AS 'Debit', (d.d_amount - c.r_amount) AS 'Remaining', c.r_detail AS 'Credit Detail', d.d_detail AS 'Debit Detail' FROM credit c INNER JOIN debit d ON c.r_name = d.d_name WHERE c.r_name = @SelectedName ORDER BY c.r_sr ASC";

    using (OleDbCommand cmd = new OleDbCommand(query, connection))
    {
        cmd.Parameters.AddWithValue("@SelectedName", selectedName);
        connection.Open();
        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());
        connection.Close();
        dataGridView1.DataSource = dt;
        decimal totalDebit = 0;
        decimal totalCredit = 0;

        foreach (DataRow row in dt.Rows)
        {
            totalDebit += Convert.ToDecimal(row["'Debit'"]);
            totalCredit += Convert.ToDecimal(row["'Credit'"]);

            
            int rowIndex = dt.Rows.IndexOf(row);
            if (rowIndex < dt.Rows.Count - 1)
            {
                dt.Rows[rowIndex + 1]["'Debit'"] = row["'Remaining'"];
            }
        }
        txt_tdebit.Text = totalDebit.ToString();
        txt_tcredit.Text = totalCredit.ToString();
    }
}

解决方案 #

问题分析 #

1. SQL直接计算Remaining的逻辑错误：基于原始借方数据，未考虑余额递推
2. 字段引用错误：row["'Debit'"]多了单引号，实际应使用别名row["Debit"]
3. 遍历逻辑错误：foreach遍历原始行数据时修改下一行，无法实时更新当前行的余额计算

修改后代码 #

private void PopulateAccountReport()
{
    string selectedName = comboBox2.SelectedItem.ToString();
    // 使用using自动释放连接资源
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        // 仅查询基础数据，余额计算移至代码中处理
        string query = @"SELECT c.r_name AS Name, 
                                c.r_date AS [Credit Date], 
                                c.r_amount AS Credit, 
                                d.d_date AS [Debit Date], 
                                d.d_amount AS Debit, 
                                c.r_detail AS [Credit Detail], 
                                d.d_detail AS [Debit Detail] 
                         FROM credit c 
                         INNER JOIN debit d ON c.r_name = d.d_name 
                         WHERE c.r_name = @SelectedName 
                         ORDER BY c.r_sr ASC";

        using (OleDbCommand cmd = new OleDbCommand(query, connection))
        {
            cmd.Parameters.AddWithValue("@SelectedName", selectedName);
            connection.Open();
            DataTable dt = new DataTable();
            dt.Load(cmd.ExecuteReader());
            connection.Close();

            // 确保Remaining列存在，若不存在则添加
            if (!dt.Columns.Contains("Remaining"))
            {
                dt.Columns.Add("Remaining", typeof(decimal));
            }

            decimal totalDebit = 0;
            decimal totalCredit = 0;
            decimal previousRemaining = 0;

            // 用for循环按顺序处理每一行，跟踪上一行余额
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow row = dt.Rows[i];
                decimal credit = Convert.ToDecimal(row["Credit"]);
                // 第一行用原始借方，后续行用上一行的余额作为当前借方
                decimal debit = i == 0 ? Convert.ToDecimal(row["Debit"]) : previousRemaining;

                // 更新当前行的借方值
                row["Debit"] = debit;
                // 计算当前行余额：贷方 - 借方
                decimal remaining = credit - debit;
                row["Remaining"] = remaining;

                // 累加总计（使用更新后的借方值）
                totalDebit += debit;
                totalCredit += credit;

                // 保存当前余额，供下一行使用
                previousRemaining = remaining;
            }

            dataGridView1.DataSource = dt;
            txt_tdebit.Text = totalDebit.ToString();
            txt_tcredit.Text = totalCredit.ToString();
        }
    }
}

修改说明 #

• 移除SQL中的余额计算，改为代码内逐行递推计算，确保余额逻辑正确
• 使用for循环替代foreach，方便按顺序处理行并跟踪上一行的余额
• 修正字段引用，去掉多余的单引号，匹配SQL别名
• 自动添加Remaining列，避免因查询未包含该列导致错误
• 总计累加使用更新后的借方值，确保统计准确

预期效果 #

内容的提问来源于stack exchange，提问作者asif sumra