ASP.NET Populating Gridview (Complex) + Calulating 2 Columns with Stored Procedures.

[EDN Admin]

I need to populate a Gridview, but I also need to populate 2 of fields using stored procedures to calculate them. I have include the Gridview Code, and the Stored Procedure below:

How do I get it so, that I can populate each of the given fields: CurrentBalance, RemainingPayments below.
The reason is: I have subscriptions, and would like to return how much time is left on the subscription, and what the balance is:

<asp:GridView runat="server" ID="dgReport_Transaction"
AutoGenerateColumns="false"
GridLines="None"
AllowPaging="true"
PageSize="50"
OnPageIndexChanging="dgReport_Transaction_PageIndexChanging"
CssClass="mGrid"
PagerStyle-CssClass="pgr"
AlternatingRowStyle-CssClass="alt" PagerSettings-Position="Bottom" >
<Columns>
<asp:BoundField DataField="ID" Visible="false" HeaderText="ID" />
<asp:BoundField DataField="TransactionPrice" Visible="false" HeaderText="Price" DataFormatString="{0:0.00}" />
<asp:BoundField DataField="TransactionPaymentType" Visible="false" HeaderText="Method" />
<asp:BoundField DataField="TransactionInstallmentNumber" Visible="false" HeaderText="Installment" />
<asp:BoundField DataField="TransactionDepositValue" Visible="false" HeaderText="Deposit" DataFormatString="{0:0.00}" />
<asp:BoundField DataField="TransactionAmountPaid" Visible="false" HeaderText="Amount Paid" DataFormatString="{0:0.00}" />
<asp:BoundField DataField="TransactionPurchaseDate" Visible="false" HeaderText="Payment Date" />

//This Lines here need to be calulated:

//See Stored Procedure below
<asp:BoundField DataField="CurrentBalance" Visible="false" HeaderText="Current Balance" DataFormatString="{0:0.00}" />
<asp:BoundField DataField="RemainingPayments" Visible="false" HeaderText="Number of Remaining Payments" />
</Columns>
<PagerSettings Position="Bottom" Mode="NumericFirstLast" />
</asp:GridView>

To populate the Gridview:

try
{
SqlDataAdapter objAdapter;
DataSet objDataSet;
String strSQLQuery;

strSQLQuery = "SELECT * FROM Transactions " +
"INNER JOIN UserProfiles ON Transactions.TransactionUserName = UserProfiles.UserUsername ";

SqlCommand objCommand = new SqlCommand(strSQLQuery, myConnection);

objAdapter = new SqlDataAdapter(objCommand);
objDataSet = new DataSet();
objAdapter.Fill(objDataSet);
dgReport_Transaction.DataSource = objDataSet;
dgReport_Transaction.DataBind();
}
catch (Exception ex)
{
lblMessage2.Text = "Error: " + ex;
}

STORED PROCEDURE:

DECLARE
@CurrentDate date,
@MonthDifference int,
@RemainingValue int,
@InstallmentValue int,
@PurchasedDate datetime,
@InstallmentDate int,
@PaymentType nvarchar(150),
@UserName nvarchar(150), @TransId int


--Grab the Values needed
SELECT DISTINCT @InstallmentValue=TransactionPaymentInstallmentNumber, @PurchasedDate=TransactionPurchaseDate,
@InstallmentDate=TransactionInstallmentDate, @PaymentType=TransactionPaymentType FROM Transactions
INNER JOIN UserProfiles ON Transactions.TransactionUserName = UserProfiles.UserUsername
WHERE (Transactions.TransactionUserName=@UserName AND Transactions.Id=@TransId )

--PRINT Installment Number: + CONVERT(CHAR(15), @InstallmentValue)
--PRINT Pur Date: + CONVERT(CHAR(15), @PurchasedDate)
--PRINT Ins Date: + CONVERT(CHAR(15), @InstallmentDate)
--PRINT Payment: + CONVERT(CHAR(15), @PaymentType)

IF (@PaymentType = Payment Plan)
BEGIN
SET @CurrentDate = GETDATE()
SET @MonthDifference = DATEDIFF(MM, @PurchasedDate, @CurrentDate)

IF (@CurrentDate > (SELECT dateadd(day,(@InstallmentDate-datepart(DD,@CurrentDate)),@CurrentDate)))
BEGIN
SET @RemainingValue = (@InstallmentValue - (@MonthDifference + 1))
END
ELSE
BEGIN
SET @RemainingValue = (@InstallmentValue - (@MonthDifference))
END
END
ELSE
BEGIN
SET @RemainingValue = 0
END

--PRINT Remaing: + CONVERT(CHAR(15), @RemainingValue)
--PRINT Current: + CONVERT(CHAR(15), @CurrentDate)
--PRINT Month Diff: + CONVERT(CHAR(15), @MonthDifference)

Return(@CurrentDate)

View the full article