Sometime, when binding records to the Grid View we might not be able to bind all the data required to the Grid View. So we required displaying some columns which can be generated from other column values. (This is something like Formula field used in Excel.)
In this post, I am planning to provide some examples on formula field in Grid View.
As there are lots of ways to show formula fields in Grid View, I am giving each one after another. If someone things anything missing, please comment on this post.
Note: Considering the binding to the Grid View (to DataSource property), there are multiple ways exist - such as binding a Data Table, binding a collection of entity object (IList, Collection etc.,), Binding Arrays etc.,
Below implementation consider two ways
- Binding Data Table to the Grid View
- Binding collection of entity object to the Grid View.
Getting Started
Before going for formula field, let us get a normal Grid View into the screen.
The ASPX script:
<asp:GridView ID="grdViewOrders" runat="server"
AllowPaging="True" AutoGenerateColumns="False" TabIndex="1"
DataKeyNames="ProductID" Width="100%"
CellPadding="4" ForeColor="#333333"
onpageindexchanging="grdViewProducts_PageIndexChanging">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="Order ID" />
<asp:BoundField DataField="OrderDate" HeaderText="Order Date" DataFormatString="{0:dd-MMMM-yyyy}" />
<asp:BoundField DataField="ProductName" HeaderText="Product Name" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit" />
<asp:BoundField DataField="UnitPrice" HeaderText="Unit Price" DataFormatString="{0:#0.00}" >
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:BoundField DataField="Quantity" HeaderText="Quantity" >
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:BoundField DataField="Discount" HeaderText="Discount" DataFormatString="{0:#0.00}" >
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:TemplateField HeaderText="Amount">
<ItemTemplate>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Right" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
</asp:GridView>C# Code:protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGrid();
}
}
/// <summary>
/// Method which binds the data to the Grid
/// </summary>
private void BindGrid()
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
{
SqlCommand command = new SqlCommand(
"SELECT Orders.OrderID, Orders.OrderDate, Products.ProductName, Products.QuantityPerUnit, " +
"OrderDetails.ProductID, OrderDetails.UnitPrice, OrderDetails.Quantity, OrderDetails.Discount " +
"FROM Orders JOIN [Order Details] OrderDetails ON Orders.OrderID = OrderDetails.OrderID " +
"JOIN Products ON OrderDetails.ProductID = Products.ProductID " +
"ORDER BY Orders.OrderID, OrderDetails.ProductID", connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
IList<OrderView> orderViewList = new List<OrderView>();
while (dr.Read())
{
OrderView orderView = new OrderView();
orderView.OrderID = Convert.ToInt32(dr["OrderID"].ToString());
orderView.OrderDate = Convert.ToDateTime(dr["OrderDate"].ToString());
orderView.ProductName = dr["ProductName"].ToString();
orderView.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
orderView.ProductID = Convert.ToInt32(dr["ProductID"].ToString());
orderView.UnitPrice = Convert.ToDouble(dr["UnitPrice"].ToString());
orderView.Quantity = Convert.ToInt32(dr["Quantity"].ToString());
orderView.Discount = Convert.ToDouble(dr["Discount"].ToString());
orderViewList.Add(orderView);
}
grdViewOrders.DataSource = orderViewList;
grdViewOrders.DataBind();
}
}
protected void grdViewProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdViewOrders.PageIndex = e.NewPageIndex;
BindGrid();
}The OrderView entity class would bepublic class OrderView
{
public int OrderID { get; set; }
public DateTime OrderDate { get; set; }
public string ProductName { get; set; }
public string QuantityPerUnit { get; set; }
public int ProductID { get; set; }
public double UnitPrice { get; set; }
public int Quantity { get; set; }
public double Discount { get; set; }
}The output of the code would be as below:
1st way (NormalCalculation.aspx)
In the above code, we required to show the Amount column which should be calculated from Unit Price, Quantity and Discount columns.
So, Amount = Unit Price * Quantity – Discount
Add the below column in the Grid View to show the Amount column
<asp:TemplateField HeaderText="Amount">
<ItemTemplate>
<asp:Label runat="server" ID="lblProductId" Text='<%# (Convert.ToDouble(Eval("UnitPrice")) * Convert.ToDouble(Eval("Quantity")) - Convert.ToDouble(Eval("Discount"))).ToString("0.00") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
</asp:TemplateField>Here we have a label to show the value. Instead we can also show without the label control. <asp:TemplateField HeaderText="Amount">
<ItemTemplate>
<%# (Convert.ToDouble(Eval("UnitPrice")) * Convert.ToDouble(Eval("Quantity")) - Convert.ToDouble(Eval("Discount"))).ToString("0.00") %>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
</asp:TemplateField>As mentioned in the script, we can get the value of a column which bound to the Grid View by using Eval (or DataBinder.Eval) method. The code inside of ‘<%# %>’ tag is actually a .NET code.The output of the code would be as below:
2nd way (FormulaInEvent.aspx)
Instead of using ‘<%# %>’ tags we can also use RowDataBound event to show calculated values. So we can get more flexibility to do lots of calculations using .NET code and get a final value to show.
The ASPX script
<asp:GridView ID="grdViewOrders" runat="server"
AllowPaging="True" AutoGenerateColumns="False" TabIndex="1"
DataKeyNames="ProductID" Width="100%"
CellPadding="4" ForeColor="#333333"
onpageindexchanging="grdViewProducts_PageIndexChanging"
onrowdatabound="grdViewOrders_RowDataBound">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="Order ID" />
<asp:BoundField DataField="OrderDate" HeaderText="Order Date" DataFormatString="{0:dd-MMMM-yyyy}" />
<asp:BoundField DataField="ProductName" HeaderText="Product Name" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit" />
<asp:BoundField DataField="UnitPrice" HeaderText="Unit Price" DataFormatString="{0:#0.00}" >
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:BoundField DataField="Quantity" HeaderText="Quantity" >
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:BoundField DataField="Discount" HeaderText="Discount" DataFormatString="{0:#0.00}" >
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:TemplateField HeaderText="Amount">
<ItemTemplate>
<asp:Label runat="server" ID="lblAmount" Text=""></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Right" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
</asp:GridView>The C# RowDataBound event/// <summary>
/// Method which binds the data to the Grid
/// </summary>
private void BindGrid()
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
{
SqlCommand command = new SqlCommand(
"SELECT Orders.OrderID, Orders.OrderDate, Products.ProductName, Products.QuantityPerUnit, " +
"OrderDetails.ProductID, OrderDetails.UnitPrice, OrderDetails.Quantity, OrderDetails.Discount " +
"FROM Orders JOIN [Order Details] OrderDetails ON Orders.OrderID = OrderDetails.OrderID " +
"JOIN Products ON OrderDetails.ProductID = Products.ProductID " +
"ORDER BY Orders.OrderID, OrderDetails.ProductID", connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
IList<OrderView> orderViewList = new List<OrderView>();
while (dr.Read())
{
OrderView orderView = new OrderView();
orderView.OrderID = Convert.ToInt32(dr["OrderID"].ToString());
orderView.OrderDate = Convert.ToDateTime(dr["OrderDate"].ToString());
orderView.ProductName = dr["ProductName"].ToString();
orderView.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
orderView.ProductID = Convert.ToInt32(dr["ProductID"].ToString());
orderView.UnitPrice = Convert.ToDouble(dr["UnitPrice"].ToString());
orderView.Quantity = Convert.ToInt32(dr["Quantity"].ToString());
orderView.Discount = Convert.ToDouble(dr["Discount"].ToString());
orderViewList.Add(orderView);
}
grdViewOrders.DataSource = orderViewList;
grdViewOrders.DataBind();
}
}
protected void grdViewProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdViewOrders.PageIndex = e.NewPageIndex;
BindGrid();
}
protected void grdViewOrders_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
OrderView orderView = (OrderView)e.Row.DataItem; // Getting the entity which bound for this row
Label lblAmount = (Label)e.Row.FindControl("lblAmount");
lblAmount.Text = (Convert.ToDouble(orderView.UnitPrice) * Convert.ToDouble(orderView.Quantity) - Convert.ToDouble(orderView.Discount)).ToString("0.00");
}
}The output of the code would be same as 1st way of implementation (using ‘<%# %>’).
3rd way (FormulaInEvent1.aspx)
In the previous example, we use collection of entity class (IList) for binding to the Grid View. But in some times we may bind DataTable to the Grid View. So, How to get the values in the event and do the calculation to show in the Grid View.
Below is the implementation is same as 2rd way, but we bind DataTable as DataSource to the GridView instead IList.
The ASPX script is same as defined in FormulaInEvent.aspx.
The C# code
/// <summary>
/// Method which binds the data to the Grid
/// </summary>
private void BindGrid()
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
{
SqlCommand command = new SqlCommand(
"SELECT Orders.OrderID, Orders.OrderDate, Products.ProductName, Products.QuantityPerUnit, " +
"OrderDetails.ProductID, OrderDetails.UnitPrice, OrderDetails.Quantity, OrderDetails.Discount " +
"FROM Orders JOIN [Order Details] OrderDetails ON Orders.OrderID = OrderDetails.OrderID " +
"JOIN Products ON OrderDetails.ProductID = Products.ProductID " +
"ORDER BY Orders.OrderID, OrderDetails.ProductID", connection);
SqlDataAdapter adptaptor = new SqlDataAdapter(command);
DataTable products = new DataTable("Products");
adptaptor.Fill(products);
grdViewOrders.DataSource = products;
grdViewOrders.DataBind();
}
}
protected void grdViewOrders_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
Array arrOderView = ((DataRowView)e.Row.DataItem).Row.ItemArray; // Getting the entity which bound for this row
Label lblAmount = (Label)e.Row.FindControl("lblAmount");
lblAmount.Text = (Convert.ToDouble(arrOderView.GetValue(5)) * Convert.ToDouble(arrOderView.GetValue(6)) - Convert.ToDouble(arrOderView.GetValue(7))).ToString("0.00");
// Below are the index as per the query written in BindData()
// Index Orders.OrderID = 0
// Index Orders.OrderDate = 1
// Index Orders.ProductName = 2
// Index Orders.QuantityPerUnit = 3
// Index Orders.ProductID = 4
// Index Orders.UnitPrice = 5
// Index Orders.Quantity = 6
// Index Orders.Discount = 7
}
}The output of the code would be same as 1st way of implementation (using ‘<%# %>’).
For more examples on Formula fields in Grid View please refer Formula Fields in Grid View - Part 2.
Download the working example of the source code in C# here and VB here.
0 Responses to “Formula Fields in Grid View - Part 1”
Post a Comment