Custom Datagridview Paging

Question

I am trying to make Datagridview paging, I have already performed the paging and it works fine, the problem is when I try to search for records from the database the problem is that Datagridview doesn't change and the data returned from the database is not showing and the pages count keeps increasing on each search.

Please take a look at this video : https://www.youtube.com/watch?v=Ina0OlZagSo&ab_channel=RabeeQabaha

here is my Custom Datagridview class:

class GV_Paging : Guna.UI2.WinForms.Guna2DataGridView
{
    public int PageSize
    {
        get
        {
            return _pageSize;
        }
        set
        {
            _pageSize = value;
        }
    }

    public int _pageSize = 10;
    BindingSource bs = new BindingSource();
    BindingList<DataTable> tables = new BindingList<DataTable>();
    public void SetPagedDataSource(DataTable dataTable, BindingNavigator bnav)
    {
        DataTable dt = null;
        int counter = 1;
        foreach (DataRow dr in dataTable.Rows)
        {
            if (counter == 1)
            {
                dt = dataTable.Clone();
                tables.Add(dt);
            }
            dt.Rows.Add(dr.ItemArray);
            if (PageSize < ++counter)
            {
                counter = 1;
            }
        }
        bnav.BindingSource = bs;
        bs.DataSource = tables;
        bs.PositionChanged += Bs_PositionChanged;
        Bs_PositionChanged(bs, EventArgs.Empty);
    }
    void Bs_PositionChanged(object sender, EventArgs e)
    {
        this.DataSource = tables[bs.Position];
    }
}

and this is how I fill the data to Datagridview:

 GV.PageSize = Convert.ToInt32(Math.Floor(Convert.ToDecimal(GV.Height / GV.RowTemplate.Height))) - 1;
 DT = DBConn.ExecuteDataTable("select_all_materials", CommandType.StoredProcedure);
 GV.SetPagedDataSource(DT, bindingNavigator1);

and here is the code for searching data from the database(work on text box text change):

 GV.PageSize = Convert.ToInt32(Math.Floor(Convert.ToDecimal(GV.Height / GV.RowTemplate.Height))) - 1;
 DT = DBConn.ExecuteDataTable("search_materials_by_name", CommandType.StoredProcedure, new 
 SqlParameter[] { new SqlParameter("@name", Material_name_txt.Text.Trim()) });
 GV.SetPagedDataSource(DT, bindingNavigator1);

Answer 1

after working on it I was able to fix the problem, here is the working class:

public int PageSize
{
    get
    {
        return _pageSize;
    }
    set
    {
        _pageSize = value;
    }
}
public int _pageSize = 10;
BindingSource bs;//= new BindingSource();
BindingList<DataTable> tables;// = new BindingList<DataTable>();
public void SetPagedDataSource(DataTable dataTable, BindingNavigator bnav)
{
    if (dataTable == null || bnav == null)
    {
        return;
    }

    DataTable dt = null;
    bs = new BindingSource();
    tables = new BindingList<DataTable>();
    int counter = 1;

    foreach (DataRow dr in dataTable.Rows)
    {
        if (counter == 1)
        {
            dt = dataTable.Clone();
            tables.Add(dt);
        }

        dt.Rows.Add(dr.ItemArray);
        if (PageSize < ++counter)
        {
            counter = 1;
        }
    }
    bnav.BindingSource = bs;
    bs.DataSource = tables;
    bs.PositionChanged += Bs_PositionChanged;
    Bs_PositionChanged(bs, EventArgs.Empty);
}
void Bs_PositionChanged(object sender, EventArgs e)
{
    try
    {
        this.DataSource = tables[bs.Position];
    }
    catch (Exception ex)
    {

        MessageBox.Show(ex.Message);
    }
}