Running balance datagridview

Question

I have a table called customerdetails something like this

create table customerdetails (Accountname nvarchar(30), openingbalance money,  
                       DR money,  
    				   CR money,  
    				   ClosingBalance money  
    				  )  
insert into customerdetails  values('XY co.',-2000,0,0,-2000) 
insert into customerdetails  values('XY Co.',-2000,3000,0,1000)

I want to save -2000 as 2000CR and 1000 as 1000DR in my database .
Also how do i show the above in the datagridview with running balance like below

Answer 1

Hi @vijay kumar , Welcome to Microsoft Q&A.

Since you don't need the calculation part, you just need to add the dataGridView1_CellFormatting event of the datagridview. Convert qualified data as needed. Here I use list to simulate the acquisition of the database.

using System;
using System.Collections.Generic;
using System. Windows. Forms;
namespace_7_19_2
{
     public partial class Form1: Form
     {
         public class customer details
         {
             public int ID
             {
                 get;
                 set;
             }
             public String Name
             {
                 get;
                 set;
             }
             public int DR
             {
                 get;
                 set;
             }
             public int CR
             {
                 get;
                 set;
             }
             public int ClosingBlance
             {
                 get;
                 set;
             }
             public customer details(int iD, string name, int dR, int cR, int closingBlance)
             {
                 ID = iD;
                 Name = name;
                 DR = dR;
                 CR = cR;
                 ClosingBlance = closingBlance;
             }
         }
         public List < customer details > list = new List < customer details > ();
         public Form1()
         {
             InitializeComponent();
         }
         private void Form1_Load(object sender, EventArgs e)
         {
             list.Add(new customerdetails(1, "XY co.", 0, 0, -2000));
             list.Add(new customerdetails(2, "XY co.", 3000, 0, 1000));
             list.Add(new customerdetails(3, "XY co.", 2000, 0, 3000));
             dataGridView1. DataSource = list;
         }
         private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
         {
             if(dataGridView1. DataSource != null)
             {
                 // Determine whether it is in a specific column (assuming it is column 5), and the data is of type int
                 if(e.ColumnIndex == 4 && e.Value is int intValue)
                 {
                     // Display int data with "-" as string ending with " CR"
                     if(intValue < 0)
                     {
                         e.Value = Math.Abs(intValue).ToString() + " CR";
                         e.FormattingApplied = true; // Indicates that the display value has been modified
                     }
                     else
                     {
                         e.Value = intValue.ToString() + "DR";
                         e.FormattingApplied = true; // Indicates that the display value has been modified
                     }
                 }
             }
         }
     }
}

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Food for thought, consider the following and I can supply form code to but feel adding CR DB to the balance is redundant. Here I would not do CR/DB but A) style or not style.

CREATE TABLE [dbo].[Transactions](
	[id] [INT] IDENTITY(1,1) NOT NULL,
	[TransDate] [DATE] NULL,
	[Credit] [MONEY] NULL,
	[Debit] [MONEY] NULL
) ON [PRIMARY]

Query

WITH CTE
AS (SELECT t2.id,
           t2.TransDate,
           t2.Credit,
           t2.Debit,
           SUM(COALESCE(t1.credit, 0) - COALESCE(t1.debit, 0)) AS Balance
    FROM Transactions t1
        INNER JOIN Transactions t2
            ON t1.TransDate <= t2.TransDate
    GROUP BY t2.TransDate,
             t2.Credit,
             t2.Debit,
             t2.id)
SELECT CTE.Balance
FROM CTE;

Sample done in SSMS

Backend code

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RunningTotalsFormsApp.Classes;
internal class DataOperations
{
    private static IConfiguration? _configuration;

    public static DataTable GetData()
    {

        DataTable table = new DataTable();
        var connectionString = _configuration!.GetValue<string>("ConnectionStrings:ExamplesConnection");
        using SqlConnection cn = new(connectionString);
        using SqlCommand cmd = new(TransactionsQuery(), cn);
        cn.Open();
        SqlDataReader? reader = cmd.ExecuteReader();
        table.Load(reader);
        return table;
    }

    public static void Configure()
    {
        _configuration = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
            .AddEnvironmentVariables()
            .Build();
    }

    private static string TransactionsQuery() =>
        """
        WITH CTE
        AS (SELECT t2.id,
                   t2.TransDate,
                   t2.Credit,
                   t2.Debit,
                   SUM(COALESCE(t1.credit, 0) - COALESCE(t1.debit, 0)) AS Balance
            FROM Transactions t1
                INNER JOIN Transactions t2
                    ON t1.TransDate <= t2.TransDate
            GROUP BY t2.TransDate,
                     t2.Credit,
                     t2.Debit, t2.id)
        SELECT *
        FROM CTE;
        """;
}