SubTotal in GridView

To display Sub Totals in a GridView is a situation that arise frequently.

Here's a sample on how to do it using GridView.

First we need a table TBL_Patients. Here's the structure



Now lets populate some sample values to it.



The ASPX

<asp:GridView ID="gvwPatientBills" runat="server"
                    AutoGenerateColumns="false"
                    DataKeyNames="BillID"
                    GridLines="Both"
                    OnRowDataBound="gvwPatientBills_RowDataBound">
        <Columns>
            <asp:TemplateField HeaderText="Bill ID">
                <ItemTemplate>
                    <asp:Label ID="lblBillID" runat="server"
                            Text='<%# Eval("BillID") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Patient ID">
                <ItemTemplate>
                    <asp:Label ID="lblPatientID" runat="server"
                            Text='<%# Eval("PatientID") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Patient Name">
                <ItemTemplate>
                    <asp:Label ID="lblPatientName" runat="server"
                            Text='<%# Eval("PatientName") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Patient Ward">
                <ItemTemplate>
                    <asp:Label ID="lblPatientWard" runat="server"
                            Text='<%# Eval("PatientWard") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Bill Amount">
                <ItemTemplate>
                    <asp:Label ID="lblBillAmount" runat="server"
                            Text='<%# Eval("BillAmount") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <HeaderStyle BackColor="Black" ForeColor="White" />
    </asp:GridView>

The ASPX.CS

int iPatientIDCount = 0;
    int iRowsCount = 0;
    int iAddBills = 0;
    string sPatientID = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGridView();
        }
    }

    private void BindGridView()
    {
        DataTable dtPatientBills = new DataTable();
        string strSelectCommand = "SELECT * FROM TBL_PatientBills ORDER BY PatientID";
        using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
        {
            using (SqlDataAdapter adapPatientBills = new SqlDataAdapter(strSelectCommand, sqlConn))
            {
                adapPatientBills.Fill(dtPatientBills);
            }
        }
        iRowsCount = dtPatientBills.Rows.Count - 1;
        gvwPatientBills.DataSource = dtPatientBills;
        gvwPatientBills.DataBind();
    }

    protected void gvwPatientBills_RowDataBound(object sender, GridViewRowEventArgs e)
    {

        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string sName = "";
            if (e.Row.RowIndex == 0)
            {
                sName = ((Label)e.Row.FindControl("lblPatientName")).Text;
                sPatientID = ((Label)e.Row.FindControl("lblPatientID")).Text;
                iAddBills = Convert.ToInt32(((Label)e.Row.FindControl("lblBillAmount")).Text);
            }
            else
            {
                sName = ((Label)gvwPatientBills.Rows[e.Row.RowIndex - 1].FindControl("lblPatientName")).Text;
                if (sPatientID == ((Label)e.Row.FindControl("lblPatientID")).Text)
                {
                    iPatientIDCount = iPatientIDCount + 1;
                    iAddBills += Convert.ToInt32(((Label)e.Row.FindControl("lblBillAmount")).Text);
                }
                else
                {
                    iPatientIDCount = iPatientIDCount + 1;
                    sPatientID = ((Label)e.Row.FindControl("lblPatientID")).Text;
                    Table tblTemp = (Table)this.gvwPatientBills.Controls[0];
                    int intIndex = tblTemp.Rows.GetRowIndex(e.Row);
                    GridViewRow gvrTemp = new GridViewRow(intIndex, intIndex, DataControlRowType.Separator, DataControlRowState.Normal);
                    TableCell cellTemp = new TableCell();
                    cellTemp.BackColor = System.Drawing.ColorTranslator.FromHtml("#8FD8D8");
                    cellTemp.Font.Bold = true;
                    cellTemp.ColumnSpan = gvwPatientBills.Columns.Count;
                    cellTemp.HorizontalAlign = HorizontalAlign.Left;

                    cellTemp.Text = (iPatientIDCount != 1) ? "Total for " + sName + " in " + iPatientIDCount + " Entries: " + iAddBills : "";
                    cellTemp.Height = Unit.Pixel(20);
                    gvrTemp.Cells.Add(cellTemp);
                    tblTemp.Controls.AddAt(intIndex, gvrTemp);
                    iPatientIDCount = 0;
                    iAddBills = Convert.ToInt32(((Label)e.Row.FindControl("lblBillAmount")).Text); ;
                }

                if (iRowsCount == e.Row.RowIndex)
                {
                    iPatientIDCount = iPatientIDCount + 1;
                    Table tblTemp = (Table)this.gvwPatientBills.Controls[0];
                    int intIndex = tblTemp.Rows.GetRowIndex(e.Row);
                    GridViewRow gvrTemp = new GridViewRow(intIndex + 1, intIndex + 1, DataControlRowType.Separator, DataControlRowState.Normal);
                    TableCell cellTemp = new TableCell();
                    cellTemp.BackColor = System.Drawing.ColorTranslator.FromHtml("#8FD8D8");
                    cellTemp.Font.Bold = true;
                    cellTemp.ColumnSpan = gvwPatientBills.Columns.Count;
                    cellTemp.HorizontalAlign = HorizontalAlign.Left;
                    cellTemp.Text = (iPatientIDCount != 1) ? "Total for " + sName + " in " + iPatientIDCount + " Entries: " + iAddBills : "";
                    cellTemp.Height = Unit.Pixel(20);
                    gvrTemp.Cells.Add(cellTemp);
                    tblTemp.Controls.AddAt(intIndex + 1, gvrTemp);
                    iPatientIDCount = 0;
                    iAddBills = 0;
                }
            }
        }
    }
}

And the final result looks like this.


NB:

1. This code needs serious optimisation.
2. Its a no-no if you wanna implement Paging and Sorting here.For that we have to use the wonderful third party
GridView controls like Teleriks RadGrid and all

Paging a Filtered GridView

In many forums there are people struggling with filtering and paging in gridview.
When separated, both filtering and paging works smoothly.
But when trying to change the page index of a filtered result set, all hell will break loose.
Mostly a new page with the unfiltered result set comes into play.

Here's a sample on how to do it.


The ASPX

<table>
    <tr>
        <td>
            Search By Product Name:
        </td>
        <td>
            <asp:TextBox ID="txtProductName" runat="server"></asp:TextBox>
        </td>
        <td>
            <asp:Button ID="btnBindGridView" runat="server" 
                        Text="Populate Grid" onclick="btnBindGridView_Click" />
        </td>
    </tr>
    <tr>
        <td colspan="3">
            <asp:GridView ID="gvwProducts" runat="server" 
                            AllowPaging="true"
                            AutoGenerateColumns="false"
                            PageSize="3" 
                            OnPageIndexChanging="gvwProducts_PageIndexChanging">
                <Columns>
                    <asp:BoundField DataField="ProductName" HeaderText="Product Name"/>
                    <asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity"/>
                    <asp:BoundField DataField="UnitPrice" HeaderText="Price"/>
                </Columns>
            </asp:GridView>
        </td>
    </tr>
</table>

The ASPX.CS

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGridView();
    }
}

protected void btnBindGridView_Click(object sender, EventArgs e)
{
    BindGridView();
}

protected void gvwProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gvwProducts.PageIndex = e.NewPageIndex;
    BindGridView();
}

private void BindGridView()
{
    DataTable dtProducts = new DataTable();
    string strSelectCommand = "SELECT * FROM Products" 
            + ((txtProductName.Text.Trim() == "") ? "" : (" WHERE ProductName LIKE '" + txtProductName.Text.Trim() + "%'"));
    using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
    {
        using (SqlDataAdapter adapProducts = new SqlDataAdapter(strSelectCommand, sqlConn))
        {
            adapProducts.Fill(dtProducts);
        }
    }
    gvwProducts.DataSource = dtProducts;
    gvwProducts.DataBind();
}

DataBase used --> Northwind
Extra Namespace used -->System.Data.SqlClient;

Hope this helps!

Filtered GridView using DataList

Many times the requirement comes like this.

need to show the first letter of products as a link outside grid and when the user clicks on it the grid will be showing the result with that particular product only.

Can be easily done using GridView and DataList.
Database used is Northwind.

The ASPX

<asp:DataList ID="dlstProducts" runat="server"
               RepeatDirection="Horizontal">
   <ItemTemplate>
       <asp:LinkButton ID="lnkFirstLetters" runat="server"
                       Text='<%# Bind("ProductNameFirstLetter") %>'
           onclick="lnkFirstLetters_Click">
       </asp:LinkButton>
   </ItemTemplate>
</asp:DataList>

<asp:GridView ID="gvwProducts" runat="server"
               AutoGenerateColumns="false"
               DataKeyNames="ProductID">
   <Columns>
       <asp:TemplateField HeaderText="Product Name">
           <ItemTemplate>
               <asp:Label ID="lblProductName" runat="server" Text='<%# Bind("ProductName") %>'>
               </asp:Label>
           </ItemTemplate>
       </asp:TemplateField>
       <asp:TemplateField HeaderText="Quantity Per Unit">
           <ItemTemplate>
               <asp:Label ID="lblQuantityPerUnit" runat="server" Text='<%# Bind("QuantityPerUnit") %>'>
               </asp:Label>
           </ItemTemplate>
       </asp:TemplateField>
   </Columns>
</asp:GridView>

The ASPX.CS

public partial class GridView_GridViewDataListFirstAlphabet : System.Web.UI.Page
{
   DataTable dtProducts = new DataTable();

   protected void Page_Load(object sender, EventArgs e)
   {
       if (!IsPostBack)
       {
           InitialBindOfControls();
       }
   }

   protected void lnkFirstLetters_Click(object sender, EventArgs e)
   {
       BindGridView(((LinkButton)sender).Text.Trim());
   }

   private void InitialBindOfControls()
   {
       BindGridView("Initial");
       BindDataList();
   }

   private void BindGridView(string strChar)
   {
       string strQuery = "SELECT ProductID, ProductName, QuantityPerUnit FROM Products "
                   + ((strChar == "Initial") ? "" : "WHERE ProductName LIKE'" + strChar + "%'")
                   + "ORDER BY ProductName";
       using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString()))
       {
           using (SqlDataAdapter adapProducts = new SqlDataAdapter(strQuery, sqlConn))
           {
               adapProducts.Fill(dtProducts);
               gvwProducts.DataSource = dtProducts;
               gvwProducts.DataBind();
           }
       }
   }

   private void BindDataList()
   {
       if (gvwProducts.Rows.Count != 0)
       {
           DataTable dtTemp = new DataTable();
           dtTemp.Columns.Add("ProductNameFirstLetter");
           dtTemp.Rows.Add(((Label)gvwProducts.Rows[0].FindControl("lblProductName")).Text.Substring(0, 1));
           foreach (GridViewRow gvrProducts in gvwProducts.Rows)
           {
               if (dtTemp.Rows[dtTemp.Rows.Count - 1][0].ToString() != ((Label)gvrProducts.FindControl("lblProductName")).Text.Substring(0, 1))
               {
                   dtTemp.Rows.Add(((Label)gvrProducts.FindControl("lblProductName")).Text.Substring(0, 1));
               }
           }
           dlstProducts.DataSource = dtTemp;
           dlstProducts.DataBind();
       }
   }
}

Extra Namespace used

using System.Data.SqlClient;