DataView Paging in WPF

• Download example - 24.71 KB

Introduction

In WPF (Windows Presentation Foundation), the GridView control does not have built-in paging support unlike Visual Studio 2005 (I think. May be I am missing something). When I was doing some test coding in WPF using GridView and wanted paging support, I Googled as usual but it was of no use. After lots of searching, I decided to implement custom paging for the purpose. After completing, I decided to share it with others because there aren't many such articles about WPF.

In this article, I will explain how to create a DataView, how to bind it to a dynamic datasource and I will also explain about implementing paging. The paging logic is very simple and can be used in any other similar situation.

Here I will use Northwind as the database and two tables Products for data.

First create a GridView to display the data. To create a gridview, use this syntax:

<ListView Name="lstProducts">
   <ListView.View>
    <GridView></GridView>
   </ListView.View>
</ListView>

To create columns, use this syntax:

<GridViewColumn Header="ProductID"></GridViewColumn>

For binding a datasource to the gridview, set the ItemsSource property to "{Binding}" and for binding a database field to the gridviewcolumn, set the DisplayMemberBinding property to "{Binding Path=[FieldName]}".

So, here is the XAML for bindable gridview:

<ListView Margin="15,115,15,48" Name="lstProducts" ItemsSource="{Binding}">
   <ListView.View>
      <GridView>
        <GridViewColumn Header="ProductID"
             DisplayMemberBinding="{Binding Path=ProductID}"></GridViewColumn>
          <GridViewColumn Header="Product Name"
               DisplayMemberBinding="{Binding Path=ProductName}"></GridViewColumn>
          <GridViewColumn Header="SupplierID"
               DisplayMemberBinding="{Binding Path=SupplierID}"></GridViewColumn>
          <GridViewColumn Header="CategoryID"
               DisplayMemberBinding="{Binding Path=CategoryID}"></GridViewColumn>
          <GridViewColumn Header="Qty. Per Unit"
               DisplayMemberBinding="{Binding Path=QuantityPerUnit}"></GridViewColumn>
          <GridViewColumn Header="Unit Price"
               DisplayMemberBinding="{Binding Path=UnitPrice}"></GridViewColumn>
          <GridViewColumn Header="In Stock"
               DisplayMemberBinding="{Binding Path=UnitInStock}"></GridViewColumn>
        </GridView>
    </ListView.View>
</ListView>

In the code behind, write the code to bind the data to the gridview. Here is the function to bind the data:

private void ListProducts()
{
     sqlCon = new SqlConnection();
     sqlCon.ConnectionString = \\ConnectionString.

     cmd = new SqlCommand();
     cmd.Connection = sqlCon;
     cmd.CommandType = CommandType.Text;
     cmd.CommandText = "SELECT * FROM Products";

     sqlDa = new SqlDataAdapter();
     sqlDa.SelectCommand = cmd;

     ds = new DataSet();
     try
     {
         sqlDa.Fill(ds,"Products");

         if (ds.Tables["Products"].Rows.Count > 0)
         {
             lstProducts.DataContext = ds.Tables["Products"].DefaultView;
         }
         else
         {
              MessageBox.Show("Message");
         }
      }
      catch (Exception ex)
      {
          MessageBox.Show("Error Message");
      }
      finally
      {
          sqlDa.Dispose();
          cmd.Dispose();
          sqlCon.Dispose();
      }
 }

To call this function in the window Loaded event (Load event in Visual Studio 2005), update the XAML like this:

<Window
.......
Loaded="OnLoad"
>

Write the code for OnLoad handler in the *.cs file.

private void OnLoad(object sender, System.EventArgs e)
{
    ListProducts();
}

Now we have the grid with all the products listed. Let's start implementing paging. First we need to create the buttons for paging. Buttons are created using <Button></Button> tag. And for click event, use Click="EventName".

So here is the XAML for buttons with events:

<Button Height="23" HorizontalAlignment="Left" Margin="18,0,0,22"
    Name="btnFirst" VerticalAlignment="Bottom" Width="40"
    Content="&lt;&lt;" Click="btnFirst_Click" Opacity="0.75">
</Button>
<Button Height="23" HorizontalAlignment="Right" Margin="0,0,474,22"
    Name="btnNext" VerticalAlignment="Bottom" Width="40"
    Content="&gt;" Click="btnNext_Click" Opacity="0.75">
</Button>
<Button Height="23" HorizontalAlignment="Right" Margin="0,0,429,22"
    VerticalAlignment="Bottom" Width="40" Name="btnLast"
    Click="btnLast_Click" Opacity="0.75" Content="&gt;&gt;">
</Button>
<Button Height="23" Margin="62,0,551,22" VerticalAlignment="Bottom"
    Name="btnPrev" Click="btnPrev_Click" Opacity="0.75" Content="&lt;">
</Button>

In the code behind, write the code to paging. For the paging, I have created four private members:

//For holding the data globally.
DataTable dt_Products = new DataTable("Products");

//For storing the current page number.
private int paging_PageIndex = 1;

//For storing the Paging Size. Here it is static but you can use a property
//to expose and update value.
private int paging_NoOfRecPerPage = 20;

//To check the paging direction according to use selection.
private enum PagingMode {First = 1,Next = 2,Previous = 3,Last = 4};

We are using dt_Products for storing the data that is retrieved from the database. For that, update the ListProducts() function a little bit. After retrieving data, we have to display the first set of records according to the value of paging_NoOfRecPerPage (here it is 20). For that, I have created a temporary table with the same schema as dt_Products using the clone method.

private void ListProducts()
{
...........

paging_PageIndex = 1;\\For default
sqlDa.Fill(dt_Products);

if (dt_Products.Rows.Count > 0)
{
    DataTable tmpTable = new DataTable();

    //Copying the schema to the temporary table.
    tmpTable = dt_Products.Clone();

    //If total record count is greater than page size then import records
    //from 0 to pagesize (here 20)
    //Else import reports from 0 to total record count.
    if (dt_Products.Rows.Count >= paging_NoOfRecPerPage)
    {
       for (int i = 0; i < paging_NoOfRecPerPage; i++)
       {
            tmpTable.ImportRow(dt_Products.Rows[i]);
       }
    }
    else
    {
       for (int i = 0; i < dt_Products.Rows.Count; i++)
       {
           tmpTable.ImportRow(dt_Products.Rows[i]);
       }
    }

    //Bind the table to the gridview.
    lstProducts.DataContext = tmpTable.DefaultView;

    //Dispose the temporary table.
    tmpTable.Dispose();
 }

Now only the first 20 (page size) records will be displayed. Now we have to write the code for navigation. Let's create a function for that:

private void CustomPaging(int mode)
{
    //There is no need for these variables but i created them just for readability
    int totalRecords = dt_Products.Rows.Count;
    int pageSize = paging_NoOfRecPerPage;

    //If total record count is less than  the page size then return.
    if (totalRecords  <= pageSize)
    {
       return;
    }

    switch (mode)
    {
        case (int)PagingMode.Next:
           if (totalRecords  > (paging_PageIndex  * pageSize))
           {
               DataTable tmpTable = new DataTable();
               tmpTable = dt_Products.Clone();

               if (totalRecords  >= ((paging_PageIndex  * pageSize) + pageSize))
               {
                   for (int i = paging_PageIndex * pageSize; i <
                       ((paging_PageIndex   * pageSize) + pageSize); i++)
                   {
                       tmpTable.ImportRow(dt_Products.Rows[i]);
                   }
               }
               else
               {
                   for (int i = paging_PageIndex * pageSize; i < totalRecords; i++)
                   {
                       tmpTable.ImportRow(dt_Products.Rows[i]);
                   }
               }

               paging_PageIndex += 1;

               lstProducts.DataContext = tmpTable.DefaultView;
               tmpTable.Dispose();
           }
           break;
       case (int)PagingMode.Previous:
           if (paging_PageIndex > 1)
           {
               DataTable tmpTable = new DataTable();
               tmpTable = dt_Products.Clone();

               paging_PageIndex -= 1;

               for (int i = ((paging_PageIndex * pageSize) - pageSize);
                   i < (paging_PageIndex * pageSize); i++)
               {
                  tmpTable.ImportRow(dt_Products.Rows[i]);
               }

               lstProducts.DataContext = tmpTable.DefaultView;
               tmpTable.Dispose();
           }
           break;
       case (int)PagingMode.First:
             paging_PageIndex = 2;
             CustomPaging((int)PagingMode.Previous);
             break;
       case (int)PagingMode.Last:
             paging_PageIndex = (totalRecords/pageSize);
             CustomPaging((int)PagingMode.Next);
             break;
     }
 }

Paging function is completed. We just need to call on button click and pass the correct mode.

private void btnFirst_Click(object sender, System.EventArgs e)
{
    CustomPaging((int)PagingMode.First);
}

private void btnNext_Click(object sender, System.EventArgs e)
{
   CustomPaging((int)PagingMode.Next);
}

private void btnPrev_Click(object sender, System.EventArgs e)
{
   CustomPaging((int)PagingMode.Previous);
}

private void btnLast_Click(object sender, System.EventArgs e)
{
   CustomPaging((int)PagingMode.Last);
}

That's all for the paging.

If you want to display paging information and page number along with this, then create two labels:

<Label Height="23.277" HorizontalAlignment="Left" Margin="14.37,89.723,0,0"
    Name="lblPagingInfo" VerticalAlignment="Top" Width="282.63"/>
<Label Height="23.277" HorizontalAlignment="Left" Margin="108.37,0,0,23"
    Name="lblPageNumber" VerticalAlignment="Bottom" Width="26.63" Content="1"/>

To display the paging information, let's write a simple function. Add this function call in the CustomPaging() function outside the switch block. DONE. Compile and RUN.

Here is the complete code:

XAML

<Window
.......
Loaded="OnLoad"
>

<Grid>

<ListView Margin="15,115,15,48" Name="lstProducts" ItemsSource="{Binding}">
   <ListView.View>
      <GridView>
        <GridViewColumn Header="ProductID"
            DisplayMemberBinding="{Binding Path=ProductID}"></GridViewColumn>
          <GridViewColumn Header="Product Name"
            DisplayMemberBinding="{Binding Path=ProductName}"></GridViewColumn>
          <GridViewColumn Header="SupplierID"
            DisplayMemberBinding="{Binding Path=SupplierID}"></GridViewColumn>
          <GridViewColumn Header="CategoryID"
            DisplayMemberBinding="{Binding Path=CategoryID}"></GridViewColumn>
          <GridViewColumn Header="Qty. Per Unit"
            DisplayMemberBinding="{Binding Path=QuantityPerUnit}"></GridViewColumn>
          <GridViewColumn Header="Unit Price"
            DisplayMemberBinding="{Binding Path=UnitPrice}"></GridViewColumn>
          <GridViewColumn Header="In Stock"
            DisplayMemberBinding="{Binding Path=UnitInStock}"></GridViewColumn>
        </GridView>
    </ListView.View>
</ListView>

<Button Height="23" HorizontalAlignment="Left" Margin="18,0,0,22"
    Name="btnFirst" VerticalAlignment="Bottom" Width="40" Content="&lt;&lt;"
    Click="btnFirst_Click" Opacity="0.75">
</Button>
<Button Height="23" HorizontalAlignment="Right" Margin="0,0,474,22"
    Name="btnNext" VerticalAlignment="Bottom" Width="40" Content="&gt;"
    Click="btnNext_Click" Opacity="0.75">
</Button>
<Button Height="23" HorizontalAlignment="Right" Margin="0,0,429,22"
    VerticalAlignment="Bottom" Width="40" Name="btnLast"
    Click="btnLast_Click" Opacity="0.75" Content="&gt;&gt;">
</Button>
<Button Height="23" Margin="62,0,551,22" VerticalAlignment="Bottom"
    Name="btnPrev" Click="btnPrev_Click" Opacity="0.75" Content="&lt;">
</Button>

<Label Height="23.277" HorizontalAlignment="Left" Margin="14.37,89.723,0,0"
    Name="lblPagingInfo" VerticalAlignment="Top" Width="282.63"/>
<Label Height="23.277" HorizontalAlignment="Left" Margin="108.37,0,0,23"
    Name="lblPageNumber" VerticalAlignment="Bottom" Width="26.63" Content="1"/>

</Grid>
</Window>

C#

//For holding the data globally.
DataTable dt_Products = new DataTable("Products");

//For storing the current page number.
private int paging_PageIndex = 1;

//For storing the Paging Size. Here it is static but you can use a property
//to expose and update value.
private int paging_NoOfRecPerPage = 20;

//To check the paging direction according to use selection.
private enum PagingMode {First = 1,Next = 2,Previous = 3,Last = 4};

private void OnLoad(object sender, System.EventArgs e)
{
    ListProducts();
}

private void btnFirst_Click(object sender, System.EventArgs e)
{
    CustomPaging((int)PagingMode.First);
}

private void btnNext_Click(object sender, System.EventArgs e)
{
   CustomPaging((int)PagingMode.Next);
}

private void btnPrev_Click(object sender, System.EventArgs e)
{
   CustomPaging((int)PagingMode.Previous);
}

private void btnLast_Click(object sender, System.EventArgs e)
{
   CustomPaging((int)PagingMode.Last);
}

private void ListProducts()
{
     sqlCon = new SqlConnection();
     sqlCon.ConnectionString = \\ConnectionString.

     cmd = new SqlCommand();
     cmd.Connection = sqlCon;
     cmd.CommandType = CommandType.Text;
     cmd.CommandText = "SELECT * FROM Products";

     sqlDa = new SqlDataAdapter();
     sqlDa.SelectCommand = cmd;

     try
     {

         paging_PageIndex = 1;\\For default
     sqlDa.Fill(dt_Products);

    if (dt_Products.Rows.Count > 0)
    {
             DataTable tmpTable = new DataTable();

            //Copying the schema to the temporary table.
            tmpTable = dt_Products.Clone();

           //If total record count is greater than page size then
           //import records from 0 to pagesize (here 20)
           //Else import reports from 0 to total record count.
           if (dt_Products.Rows.Count >= paging_NoOfRecPerPage)
           {
               for (int i = 0; i < paging_NoOfRecPerPage; i++)
               {
                    tmpTable.ImportRow(dt_Products.Rows[i]);
               }
           }
           else
           {
               for (int i = 0; i < dt_Products.Rows.Count; i++)
               {
                   tmpTable.ImportRow(dt_Products.Rows[i]);
               }
           }

          //Bind the table to the gridview.
          lstProducts.DataContext = tmpTable.DefaultView;

          //Dispose the temporary table.
          tmpTable.Dispose();
       }
       else
       {
          MessageBox.Show("Message");
       }
    }
    catch (Exception ex)
    {
       MessageBox.Show("Error Message");
    }
    finally
    {
       sqlDa.Dispose();
       cmd.Dispose();
       sqlCon.Dispose();
    }
 }

private void CustomPaging(int mode)
{
    //There is no need for these variables but i created them just for readability
    int totalRecords = dt_Products.Rows.Count;
    int pageSize = paging_NoOfRecPerPage;

    //If total record count is less than  the page size then return.
    if (totalRecords  <= pageSize)
    {
       return;
    }

    switch (mode)
    {
        case (int)PagingMode.Next:
           if (totalRecords  > (paging_PageIndex  * pageSize))
           {
               DataTable tmpTable = new DataTable();
               tmpTable = dt_Products.Clone();

               if (totalRecords  >= ((paging_PageIndex  * pageSize) + pageSize))
               {
                   for (int i = paging_PageIndex * pageSize;
                        i < ((paging_PageIndex   * pageSize) + pageSize); i++)
                   {
                       tmpTable.ImportRow(dt_Products.Rows[i]);
                   }
               }
               else
               {
                   for (int i = paging_PageIndex * pageSize; i < totalRecords; i++)
                   {
                       tmpTable.ImportRow(dt_Products.Rows[i]);
                   }
               }

               paging_PageIndex += 1;

               lstProducts.DataContext = tmpTable.DefaultView;
               tmpTable.Dispose();
           }
           break;
       case (int)PagingMode.Previous:
           if (paging_PageIndex > 1)
           {
               DataTable tmpTable = new DataTable();
               tmpTable = dt_Products.Clone();

               paging_PageIndex -= 1;

               for (int i = ((paging_PageIndex * pageSize) - pageSize);
                   i < (paging_PageIndex * pageSize); i++)
               {
                  tmpTable.ImportRow(dt_Products.Rows[i]);
               }

               lstProducts.DataContext = tmpTable.DefaultView;
               tmpTable.Dispose();
           }
           break;
       case (int)PagingMode.First:
             paging_PageIndex = 2;
             CustomPaging((int)PagingMode.Previous);
             break;
       case (int)PagingMode.Last:
             paging_PageIndex = (totalRecords/pageSize);
             CustomPaging((int)PagingMode.Next);
             break;
     }

     DisplayPagingInfo();
 }

private void DisplayPagingInfo()
{
    //There is no need for these variables but i created them just for readability
    int totalRecords = dt_Products.Rows.Count;
    int pageSize = paging_NoOfRecPerPage;

    string pagingInfo = "Displaying " + (((paging_PageIndex-1)*pageSize)+1) +
        to " + paging_PageIndex*pageSize ;

    if (dt_Products.Rows.Count < (paging_PageIndex * pageSize))
    {
        pagingInfo = "Displaying " + (((paging_PageIndex - 1) * pageSize) + 1) +
        to " + totalRecords;
    }
    lblPagingInfo.Content = pagingInfo;
    lblPageNumber.Content = paging_PageIndex;
}