Formula Fields in Grid View - Part 2

Tweet

We had seen three different ways on showing formula fields in Grid View in previous post Formula Fields in Grid View - Part 1. This is continuation of the same topic.

4th way (SubstituteValue.aspx)

In some situation, we required to show a value which required substitution of another value.

For Ex: The Northwind database has Products table, which has Discontinued column which contains boolean value True (If discontinued) or False (if not). When showing Discontinued column in the Grid View, it should show Discontinued when the value is 0 otherwise an empty (or an -) when 1.

The ASPX script

<asp:GridView ID="grdViewProducts" runat="server"
    AllowPaging="True" AutoGenerateColumns="False" TabIndex="1"
    DataKeyNames="ProductID" Width="100%"
    CellPadding="4" ForeColor="#333333" 
    onpageindexchanging="grdViewProducts_PageIndexChanging">
    <Columns>
        <asp:BoundField DataField="ProductID" HeaderText="Product ID" />
        <asp:BoundField DataField="ProductName" HeaderText="Product Name" />
        <asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit" />
        <asp:BoundField DataField="UnitsInStock" HeaderText="Units In Stock" DataFormatString="{0:#0.00}" >
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
        <asp:BoundField DataField="UnitsOnOrder" HeaderText="Units On Order" >
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
        <asp:TemplateField HeaderText="Discontinued?">
            <ItemTemplate>
                <%# (Convert.ToInt32(Eval("Discontinued")) == 0) ? "Yes" : "-" %>
            </ItemTemplate>
            <ItemStyle HorizontalAlign="Center" />
        </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# code

private void BindGrid()
{
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
    {
        SqlCommand command = new SqlCommand("SELECT ProductID, ProductName, QuantityPerUnit, UnitsInStock, UnitsOnOrder, Discontinued FROM Products", connection);

        SqlDataAdapter adptaptor = new SqlDataAdapter(command);
        DataTable products = new DataTable("Products");
        adptaptor.Fill(products);
        grdViewProducts.DataSource = products;
        grdViewProducts.DataBind();
    }
}
protected void grdViewProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    grdViewProducts.PageIndex = e.NewPageIndex;
    BindGrid();
}

The screen output of this source code would be as below:

5th way (CallServerOnBind.aspx)

In 3rd and 4th example, we use RowDataBound event to calculate the value and bind to the required control. But in some time, we have to call a server method from the Grid View script to show as a formula value. The server method could be defined in a separate class or in the code behind.

To call the server methods from Grid View script, we required to define it as a static method. So the static methods can be called from the client side by using Namespace.ClassName.MethodName(Parameters). The implementation is below.

Defining a static method in code behind page (which actually defined inside of a class and namespace).

public static string GetAmount(string strUnitPrice, string strQuantity, string strDiscount)
{
    return (Convert.ToDouble(strUnitPrice) * Convert.ToDouble(strQuantity) - Convert.ToDouble(strDiscount)).ToString("0.00");
}

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>
                <%# FormulaFieldGridView.CallServerOnBind.GetAmount(Eval("UnitPrice").ToString(), Eval("Quantity").ToString(), Eval("Discount").ToString()) %>
            </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# code

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 grdViewProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    grdViewOrders.PageIndex = e.NewPageIndex;
    BindGrid();
}

As defined here, we can call any static method from Grid View script by calling Namespace.ClassName.StaticMethodName(parameter values).

6th way (CallServerOnBind1.aspx)

Let us take the same example as defined above (5th way) and modify the same to call a static method defined in a public class (which is not a code behind).

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>
                <%# FormulaFieldGridView.FormulaCalculation.GetOrderAmount(Eval("UnitPrice").ToString(), Eval("Quantity").ToString(), Eval("Discount").ToString())%>
            </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# code (separate class FormulaCalculation.cs)

namespace FormulaFieldGridView
{
    public class FormulaCalculation
    {
        public static string GetOrderAmount(string strUnitPrice, string strQuantity, string strDiscount)
        {
            return (Convert.ToDouble(strUnitPrice) * Convert.ToDouble(strQuantity) - Convert.ToDouble(strDiscount)).ToString("0.00");
        }
    }
}

7th way (SubEntityValue.aspx)

When we are using ORM framework such as NHibernate, we will be mapping different entity class for a single table as properties.

For example – When mapping the Products table of Northwind, we might be mapping Supplier, Category classes as properties for the SupplierID, CategoryID columns. So when fetching a Product entity object, it will contain Supplier object and Category object as property to the Product object.

Now, when binding the Product collection to the Grid View, we required to show Supplier Name and Category Name which are the property of Supplier and Category object.

So, how to show values which are property of object which is again a property of main object.

The ASPX script

<asp:GridView ID="grdViewProducts" runat="server"
    AllowPaging="True" AutoGenerateColumns="False" TabIndex="1"
    DataKeyNames="ProductID" Width="100%"
    CellPadding="4" ForeColor="#333333" 
    onpageindexchanging="grdViewProducts_PageIndexChanging">
    <Columns>
        <asp:BoundField DataField="ProductID" HeaderText="Order ID" />
        <asp:BoundField DataField="ProductName" HeaderText="Product Name" />
        <asp:TemplateField HeaderText="Supplier">
            <ItemTemplate>
                <%# Server.HtmlEncode(((Supplier)DataBinder.Eval(Container.DataItem, "Supplier")).CompanyName) %>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Category">
            <ItemTemplate>
                <%# Server.HtmlEncode(((Category)DataBinder.Eval(Container.DataItem, "Category")).CategoryName)%>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit" />
        <asp:BoundField DataField="UnitsInStock" HeaderText="Units In Stock" >
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
        <asp:BoundField DataField="UnitsOnOrder" HeaderText="Units On Order" >
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
        <asp:TemplateField HeaderText="Discontinued?">
            <ItemTemplate>
                <%# (Convert.ToInt32(Eval("Discontinued")) == 0) ? "Yes" : "-" %>
            </ItemTemplate>
            <ItemStyle HorizontalAlign="Center" />
        </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# Code

private void BindGrid()
{
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
    {
        IList<Product> productList = new List<Product>();

        DataSet dataSet = new DataSet();
        SqlDataAdapter dataAdaptor = new SqlDataAdapter("SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitsInStock, UnitsOnOrder, Discontinued FROM Products", connection);
        dataAdaptor.Fill(dataSet);

        foreach (DataRow row in dataSet.Tables[0].Rows)
        {
            Product product = new Product();
            product.ProductID = Convert.ToInt32(row["ProductID"].ToString());
            product.ProductName = row["ProductName"].ToString();
            product.QuantityPerUnit = row["QuantityPerUnit"].ToString();
            product.UnitsInStock = Convert.ToInt32(row["UnitsInStock"].ToString());
            product.UnitsOnOrder = Convert.ToInt32(row["UnitsOnOrder"].ToString());
            product.Discontinued = Convert.ToBoolean(row["Discontinued"].ToString());

            SqlCommand cmdTemp = new SqlCommand("SELECT * FROM Suppliers WHERE SupplierID = '" + row["SupplierID"].ToString() + "'", connection);
            connection.Open();
            SqlDataReader drTemp = cmdTemp.ExecuteReader(CommandBehavior.CloseConnection);
            Supplier supplier = new Supplier();
            while (drTemp.Read())
            {
                supplier.SupplierID = Convert.ToInt32(drTemp["SupplierID"].ToString());
                supplier.CompanyName = drTemp["CompanyName"].ToString();
                supplier.ContactName = drTemp["ContactName"].ToString();
                supplier.ContactTitle = drTemp["ContactTitle"].ToString();
            }
            product.Supplier = supplier;
            drTemp.Close();
            connection.Close();

            cmdTemp = new SqlCommand("SELECT * FROM Categories WHERE CategoryID = '" + row["CategoryID"].ToString() + "'", connection);
            connection.Open();
            drTemp = cmdTemp.ExecuteReader(CommandBehavior.CloseConnection);
            Category category = new Category();
            while (drTemp.Read())
            {
                category.CategoryID = Convert.ToInt32(drTemp["CategoryID"].ToString());
                category.CategoryName = drTemp["CategoryName"].ToString();
                category.Description = drTemp["Description"].ToString();
            }
            product.Category = category;
            drTemp.Close();
            connection.Close();

            productList.Add(product);
        }

        grdViewProducts.DataSource = productList;
        grdViewProducts.DataBind();
    }
}
protected void grdViewProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    grdViewProducts.PageIndex = e.NewPageIndex;
    BindGrid();
}

The Product entity class

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public string QuantityPerUnit { get; set; }
    public int UnitsInStock { get; set; }
    public int UnitsOnOrder { get; set; }
    public bool Discontinued { get; set; }
    public Supplier Supplier { get; set; }
    public Category Category { get; set; }
}

The Supplier entity class

public class Supplier
{
    public int SupplierID { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string ContactTitle { get; set; }
}

Note: Here I am using C# code to fetch the records which will be same when fetch using NHibernate (for reducing the complexity and to understand better). The main concept to understand is to have object (instead of primitive value) for property which is a foreign key of the main table.
The screen output of this code would be as below:

Download the working example of the source code in C# here and VB here.