Add Group wise record count row in GridView using C# and VB.Net in ASP.Net

Hi ksdagar,

Check the below example. In this example i am counting the record by grouping with OrderID. In your case you need to group records  with bidmonth.

Database

For this example I am using Microsoft’s Northwind Database.

You can download from below link.

Download Northwind Database

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnDataBound="OnDataBound"
    OnRowCreated="OnRowCreated">
    <Columns>
        <asp:BoundField DataField="OrderID" HeaderText="Order ID" />
        <asp:BoundField DataField="ProductName" HeaderText="Product Name" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.BindGrid();
    }
}
private void BindGrid()
{
    string query = "SELECT OrderID,";
    query += "(SELECT ProductName FROM Products WHERE ProductID = details.ProductId) ProductName";
    query += " FROM [Order Details] details";
    query += " WHERE OrderID IN (10248, 10249, 10250)";
    query += " ORDER BY OrderID";
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }
        }
    }
}
int currentId = 0;
int subTotal = 0;
int total = 0;
int subTotalRowIndex = 0;
protected void OnRowCreated(object sender, GridViewRowEventArgs e)
{
    subTotal = 0;
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        DataTable dt = (e.Row.DataItem as DataRowView).DataView.Table;
        int orderId = Convert.ToInt32(dt.Rows[e.Row.RowIndex]["OrderID"]);
        total++;
        if (orderId != currentId)
        {
            if (e.Row.RowIndex > 0)
            {
                for (int i = subTotalRowIndex; i < e.Row.RowIndex; i++)
                {
                    subTotal++;
                }
                this.AddTotalRow("Order Total", subTotal.ToString());
                subTotalRowIndex = e.Row.RowIndex;
            }
            currentId = orderId;
        }
    }
}
protected void OnDataBound(object sender, EventArgs e)
{
    for (int i = subTotalRowIndex; i < GridView1.Rows.Count; i++)
    {
        subTotal++;
    }
    this.AddTotalRow("Order Total", subTotal.ToString());
    this.AddTotalRow("Total Rows", total.ToString());
}
private void AddTotalRow(string labelText, string value)
{
    GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal);
    row.BackColor = ColorTranslator.FromHtml("#F9F9F9");
    row.Cells.AddRange(new TableCell[2] {
                                    new TableCell { Text = labelText, HorizontalAlign = HorizontalAlign.Center},
                                    new TableCell { Text = value, HorizontalAlign = HorizontalAlign.Center } });

    GridView1.Controls[0].Controls.Add(row);
}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Me.BindGrid()
    End If
End Sub
Private Sub BindGrid()
    Dim query As String = "SELECT OrderID,"
    query += "(SELECT ProductName FROM Products WHERE ProductID = details.ProductId) ProductName"
    query += " FROM [Order Details] details"
    query += " WHERE OrderID IN (10248, 10249, 10250)"
    query += " ORDER BY OrderID"
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(conString)
        Using cmd As New SqlCommand(query)
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    sda.Fill(dt)
                    GridView1.DataSource = dt
                    GridView1.DataBind()
                End Using
            End Using
        End Using
    End Using
End Sub
Private currentId As Integer = 0
Private subTotal As Integer = 0
Private total As Integer = 0
Private subTotalRowIndex As Integer = 0
Protected Sub OnRowCreated(sender As Object, e As GridViewRowEventArgs)
    subTotal = 0
    If e.Row.RowType = DataControlRowType.DataRow Then
        Dim dt As DataTable = TryCast(e.Row.DataItem, DataRowView).DataView.Table
        Dim orderId As Integer = Convert.ToInt32(dt.Rows(e.Row.RowIndex)("OrderID"))
        total += 1
        If orderId <> currentId Then
            If e.Row.RowIndex > 0 Then
                For i As Integer = subTotalRowIndex To e.Row.RowIndex - 1
                    subTotal += 1
                Next
                Me.AddTotalRow("Order Total", subTotal.ToString())
                subTotalRowIndex = e.Row.RowIndex
            End If
            currentId = orderId
        End If
    End If
End Sub
Protected Sub OnDataBound(sender As Object, e As EventArgs)
    For i As Integer = subTotalRowIndex To GridView1.Rows.Count - 1
        subTotal += 1
    Next
    Me.AddTotalRow("Order Total", subTotal.ToString())
    Me.AddTotalRow("Total Rows", total.ToString())
End Sub
Private Sub AddTotalRow(labelText As String, value As String)
    Dim row As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal)
    row.BackColor = ColorTranslator.FromHtml("#F9F9F9")
    row.Cells.AddRange(New TableCell(1) {
                        New TableCell() With {.Text = labelText, .HorizontalAlign = HorizontalAlign.Center},
                        New TableCell() With {.Text = value, .HorizontalAlign = HorizontalAlign.Center}
                        })

    GridView1.Controls(0).Controls.Add(row)
End Sub

Screenshot