Distinct count of a column in gridview[Answered] RSS

• • Reply
  ASPbun

  Member

  48 Points

  159 Posts

  Distinct count of a column in gridview

  Nov 03, 2016 02:47 PM|ASPbun|LINK

  Hi, I have a gridview, which needs to show a total of distinct rows in a column. This can be shown in a label under the gridview.

  Because there could be multiple same records with same product ids but with different other data, I need to show distinct count of products.

  I have tried this way, but it shows only the count for the records in the current page. When I move to other page, the count reflects that particular page data.

  protected void gdv_RowDataBound(object sender, GridViewRowEventArgs e)
               {
                   var distinctRows = (from GridViewRow row in gdv.Rows
                                       select row.Cells[1].Text
                    ).Distinct().Count();
  
  
                   lblCount.Text = "Total number of records: " + distinctRows.ToString();
  
               }
  

  How do I show a distinct count of ALL the records in gridview irrespective of what page we are looking at?

  In short, row.Cells[1] has product Ids which can contain duplicates and I want to extract distinct count of these product ids.

  And I've set the pagesize as 15.

  Thanks for any help!

• • Reply
  Nan Yu

  All-Star

  18815 Points

  3831 Posts

  Re: Distinct count of a column in gridview

  Nov 04, 2016 06:58 AM|Nan Yu|LINK

  Hi ASPbun ,

  You could disable paging temporarily and will re-bind the grid so that now we have access to all records . Code below is for your reference :

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true"
       OnPageIndexChanging="grdData_PageIndexChanging" PageSize="2">
      <Columns>
          <asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="Customer ID" />
          <asp:BoundField ItemStyle-Width="150px" DataField="ContactName" HeaderText="Contact Name" />
        
      </Columns>
  </asp:GridView>
          <asp:Button ID="Button1" runat="server" Text="Button"  OnClick="Button1_Click"/>
          <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>

  Code behind :

          protected void Page_Load(object sender, EventArgs e)
          {
              if (!this.IsPostBack)
              {
                  this.BindGrid();
              }
          }
  
          public void BindGrid()
          {
              DataTable dt = new DataTable();
              dt.Columns.Add(new DataColumn("CustomerID", typeof(string)));
              dt.Columns.Add(new DataColumn("ContactName", typeof(string)));
              dt.Rows.Add("1", "1");
              dt.Rows.Add("1", "1");
              dt.Rows.Add("3", "1");
              dt.Rows.Add("2", "1");
              GridView1.DataSource = dt;
              GridView1.DataBind();
          }
  
  
          protected void grdData_PageIndexChanging(object sender, GridViewPageEventArgs e)
          {
              GridView1.PageIndex = e.NewPageIndex;
              BindGrid();
          }
  
          protected void Button1_Click(object sender, EventArgs e)
          {
              var a = GridView1.PageIndex;
              GridView1.AllowPaging = false;        
              BindGrid();
              GridView1.DataBind();
               var distinctRows = (from GridViewRow row in GridView1.Rows
                                  select row.Cells[0].Text
                    ).Distinct().Count();
              GridView1.AllowPaging = true;
              GridView1.PageIndex = a;
              BindGrid();
              Label1.Text = distinctRows.ToString();
  
          }

  According to CustomerID , label shows "3" .

  Best Regards,

  Nan Yu