Need one Procedure from Ado.net Code Which Convert Ado.net Code to SQL Server Query

Question

here is my ado.net which take four values ProfileID=1, PartyId=3, FromDate (Where its start with the Company) and ToDate(CurrentDate) and get the ledger data of the Party but i want to convert this into one procedure...

using(SqlCommand cmd = new SqlCommand("Sp_Ledger_PartyLedgerReport", con))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@ProfileId", profileID);
    cmd.Parameters.AddWithValue("@PartyId", partyID);
    cmd.Parameters.AddWithValue("@FromDate", fromDate);
    cmd.Parameters.AddWithValue("@ToDate", toDate);

    DataTable dt = new DataTable();
    using(SqlDataAdapter da = new SqlDataAdapter(cmd))
    {
        da.Fill(dt);
        if(dt.Rows.Count > 0)
        {
            for(int i = 0; i < dt.Rows.Count; i++)
            {
                Ledger l = new Ledger();
                l.LedgerNo = (string)dt.Rows[i]["Ledger_No"];
                l.ProfileFullName = (string)dt.Rows[i]["Profile_FullName"];
                l.ProfileAddress = (string)dt.Rows[i]["Profile_Address"];
                l.ProfileContact = (string)dt.Rows[i]["Profile_Contact"];
                l.CustomerId = (int)dt.Rows[i]["Party_Id"];
                l.CustomerName = (string)dt.Rows[i]["Party_Name"];
                l.LedgerType = (string)dt.Rows[i]["Ledger_Type"];
                l.LedgerDate = DateTime.Parse(dt.Rows[i]["Ledger_Date"].ToString());
                l.Remarks = (string)dt.Rows[i]["Remarks"];
                l.OpeningBalance = int.Parse(dt.Rows[i]["OpeningBalance"].ToString());
                l.TotalCredit = (int)dt.Rows[i]["Credit"];
                l.TotalDebit = (int)dt.Rows[i]["Debit"];
                if(ClosingBalance == 0)
                {
                    l.Balance = l.OpeningBalance - l.TotalCredit + l.TotalDebit;
                    ClosingBalance = l.Balance;
                }
                else
                {
                    l.Balance = ClosingBalance - l.TotalCredit + l.TotalDebit;
                    ClosingBalance = l.Balance;
                }
                myList.Add(l);
            }
        }
    }
}

Here is my Sql Server Procedure and its give all the information but i need another column 'Closing Balance' which is mention in above ado.net code

PROC [dbo].[Sp_Ledger_PartyLedgerReport]
@ProfileId      INT,
@PartyId        INT,
@FromDate       DATE,
@ToDate         DATE
AS
BEGIN
SELECT Ledger_No, 
Ledger_Type,
Ledger_Date,
Credit, 
Debit, 
Remarks, 
REPLACE(Opening_Balance,',','') OpeningBalance, 
(?) ClosingBalance
Party_Id, 
Party_Name, 
Profile_Address,    
Profile_Contact,
Profile_FullName 
FROM tblLedger  JOIN tblCustomer A ON a.Party_Id = CustomerId JOIN tblProfile ON Profile_Id = A.ProfileId WHERE Ledger_Date BETWEEN @FromDate AND @ToDate AND Profile_Id = @ProfileId AND Party_Id = @PartyId order by Ledger_Date asc
END

and the joining table 'tblCustomer' to get opening balance

Select * from tblCustomer where Party_Id = 3

Sql Query output

score 0 · Accepted Answer · answered Sep 14 '18 at 10:52

0

If what you want is to calculate closing balance in the stored procedure, then you can use WHEN and CASE statement, (assuming that you have the ClosingBalance as a field/column in your table) :

SELECT
...  
,(CASE ClosingBalance
WHEN 0 THEN OpeningBalance-Credit+Debit
ELSE
   ClosingBalance-Credit+Debit  
END)
AS  [CalculatedClosingBalance]
...
FROM ...

answered Sep 14 '18 at 10:52

Noldy Nayoan

127
1
9

Tell me where to maintain this 'ClosingBalance' Column/field in database. I have to tables one is Customer table for Opening Balance and Other is Ledger Table for maintaining Ledgers for Customer...!!! – Sep 14 '18 at 12:39
In my example above, you can add the ClosingBalance column in your tblLedger table. You can manually update it's value every time you insert a new row to the table, or you can use SQL SERVER computed column to calculate it's value (Use Table Designer to define the computed column). – Noldy Nayoan Sep 14 '18 at 13:52
FYI, I just focused on the SQL Syntax for your problem. But I really don't get the logic of "ClosingBalance-Credit+Debit", because as I understand it ClosingBalance should be just OpeningBalance-Credit+Debit. – Noldy Nayoan Sep 14 '18 at 14:04

Joel Coehoorn · Answer 2 · 2018-09-14T17:47:58.693

First of all, the procedure. In SQL, you can make this kind of calculation with a CASE statement:

PROC [dbo].[Sp_Ledger_PartyLedgerReport]
    @ProfileId      INT,
    @PartyId        INT,
    @FromDate       DATE,
    @ToDate         DATE
AS
BEGIN
    SELECT Ledger_No, 
        Ledger_Type,
        Ledger_Date,
        Credit, 
        Debit, 
        Remarks, 
        --if this is stored as a string (varchar, nvarchar, etc) column,
        -- you can REALLY help things by converting this column to a numeric type
        CAST(REPLACE(Opening_Balance,',','') AS int) OpeningBalance, 
        CASE WHEN ClosingBalance = 0
            THEN OpeningBalance - Credit + Debit
            ELSE  ClosingBalance - Credit + Debit 
            END ClosingBalance
        Party_Id, 
        Party_Name, 
        Profile_Address,    
        Profile_Contact,
        Profile_FullName 
    FROM tblLedger  
    JOIN tblCustomer A ON a.Party_Id = CustomerId 
    JOIN tblProfile ON Profile_Id = A.ProfileId 
    WHERE Ledger_Date BETWEEN @FromDate AND @ToDate 
        AND Profile_Id = @ProfileId 
        AND Party_Id = @PartyId 
    ORDER BY Ledger_Date ASC
END

But I'm worried you need this to act as a running total, and that's a bit more complicated. Or possibly the ClosingBalance field is that simple, but you want to also handle the running total calculation on the server, rather than in C#. Either way, the link can help you. Windowing Functions are your friend :)

We can also make some improvements to the C#:

using(SqlCommand cmd = new SqlCommand("Sp_Ledger_PartyLedgerReport", con))
{
    cmd.CommandType = CommandType.StoredProcedure;
    // use EXPLICIT types here that match your database columns. Trust me.
    cmd.Parameters.Add("@ProfileId", SqlDbType.Int).Value = profileID;
    cmd.Parameters.Add("@PartyId", SqlDbType.Int).Value = partyID;
    cmd.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = fromDate;
    cmd.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = toDate;

    //if you're just gonna convert to a List right away, DataAdapter/DataTable have extra overhead
    con.Open();
    using (var rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            //reduce boilerplate & indentation with DataReader and type initializer
            var l = new Ledger() {
                LedgerNo = (string)rdr["Ledger_No"],
                ProfileFullName = (string)rdr["Profile_FullName"],
                ProfileAddress = (string)rdr["Profile_Address"],
                ProfileContact = (string)rdr["Profile_Contact"],
                CustomerId = (int)rdr["Party_Id"],
                CustomerName = (string)rdr["Party_Name"],
                LedgerType = (string)rdr["Ledger_Type"],
                //Do you *really* need ToString() and Parse() calls here?
                //they have surprisingly high performance costs, and 
                // you get by just fine without them on other columns.
                LedgerDate = DateTime.Parse(rdr["Ledger_Date"].ToString()), 
                Remarks = (string)rdr["Remarks"],
                OpeningBalance = (int)rdr["OpeningBalance"],
                TotalCredit = (int)rdr["Credit"],
                TotalDebit = (int)rdr["Debit"], 
            };
            int cb = (int)rdr["ClosingBalance"];
            l.Balance = (cb == 0) ? 
               l.OpeningBalance - l.TotalCredit + l.TotalDebit : 
               cb - l.TotalCredit + l.TotalDebit;

            myList.Add(l);
        }
    }
}

Need one Procedure from Ado.net Code Which Convert Ado.net Code to SQL Server Query

2 Answers2