Tips & Tricks

Re: Custom Paging and the GridView ASP.NET 2.0 - you'll thank me for this !

mahesh81 — Mon, 14 Sep 2009 11:36:10 GMT

refer this articel

http://maheshde.blogspot.com/2009/09/how-to-implement-custom-paging-using.html

Re: Custom Paging and the GridView ASP.NET 2.0 - you'll thank me for this !

WebBot Tech — Wed, 02 Sep 2009 03:02:43 GMT

I'm still new to ASP.Net. I'm using VWD'08 (.Net 3.5) and C#. I'm trying to create a landing page for several people. I want to use one master page with fields that fill in based on a database that I have already created. The idea is that when someone enters [site.com]/user the fields will display their contact info and site links based on the label I add to the content placeholder.

Also, when I get a new member, all I have to do is enter their info in my database. This will help keep me from creating many duplicate pages.

is a GridView or DetailsView the right control to use?

Re: Custom Paging and the GridView ASP.NET 2.0 - you'll thank me for this !

obwill74 — Mon, 31 Aug 2009 19:00:30 GMT

The part I was missing was setting the new page index during the PageIndexChanging routine of the grid view prior to calling the CreateTable routine.

Me.GridView1.PageIndex = e.NewPageIndex

Re: Custom Paging and the GridView ASP.NET 2.0 - you'll thank me for this !

chorofonfilo — Mon, 31 Aug 2009 17:53:12 GMT

It worked for me doing a little changes of the following methods, i am not sure tho if its how the author of the post meant to make this work or its completely correct according to some programmatical standars.

Imports System.Data
Imports System.Data.SqlClient

Public Class MyTableAdapter

    Dim dt As DataTable
    Dim vic As Integer 'virtual item count

    Public Sub New(ByVal dtp As DataTable, ByVal vicp As Integer)
        dt = dtp
        vic = vicp
    End Sub

    'returns the filled datatable
    Public Function GetData() As DataTable
        Return dt
    End Function

    'returns the number of records
    Public Function VirtualItemCount() As Integer
        Return vic
    End Function

   'this also returns the datatable but the ODS needs it for paging purposes
    Public Function GetData(ByVal startRow As Integer, ByVal maxRows As Integer) As DataTable
        Return dt
    End Function

End Class

----

Imports System.Data
Imports System.Data.SqlClient

Public Class MyGridViewFiller

Private dt As New DataTable
    Private vic As Integer
    Private callerpage As Page
    Private gid As String

Public Sub CreateCuteLittleTable(ByVal pagep As Page, ByVal gidp As String, _
ByVal regini As Integer, ByVal numregporpag As Integer)

'This is the stored procedure which retrieves the records based on the following parameters

Dim dap As New SqlDataAdapter("damenordenesdea", bd.globalcn)
dap.SelectCommand.CommandType = CommandType.StoredProcedure

'Initial Record from which the rest of records will be displayed
dap.SelectCommand.Parameters.Add("@nregini", SqlDbType.Int).Value = regini

'Number of Records per page
dap.SelectCommand.Parameters.Add("@nregporpag", SqlDbType.Int).Value = numregporpag

        'Retrieve the number of total records spit out by the query
      dap.SelectCommand.Parameters.Add("@nregis", SqlDbType.Int).Direction = ParameterDirection.Output
        dap.Fill(dt)

       'Set the vic variable with the total amount of records to display
        vic = dap.SelectCommand.Parameters("@nregis").Value
        'lets store the page we are in and the gridviewid of that page
        callerpage = pagep
        gid = gidp
        FillGridView()

End Sub

Protected Sub ods_ObjectCreating(ByVal sender As Object, ByVal e As ObjectDataSourceEventArgs)
        e.ObjectInstance = New MyTableAdapter(dt, vic)
    End Sub

    Public Sub FillGridView()
        'First let's make a GridView object that hold the GridView
        Dim gv As GridView = CType(callerpage.FindControl(gid), GridView)
        'Then create an ObjectDateSource object programmatically
        Dim ods As New ObjectDataSource
        'setting the necessary properties (these will interact with our TableAdapter !)
        ods.ID = "ods" & gid
        'the paging of ODS depends on the paging of the GridView
        ods.EnablePaging = gv.AllowPaging

        ods.TypeName = "MyTableAdapter"
        ods.SelectMethod = "GetData"
        ods.SelectCountMethod = "VirtualItemCount"
        ods.StartRowIndexParameterName = "startRow"
        ods.MaximumRowsParameterName = "maxRows"
        ods.EnableViewState = True

        'Instead of calling the parameterless constructor of our TableAdapter, I'm going
        'to hook up on the ods.ObjectCreating event to supply my own TableAdapter (WITH constructor parameters)
        'the following line is the "hook up" declaration
      'this means that at the object creating event, the CLR is going to jump to my event handler (ods_ObjectCreating)

AddHandler ods.ObjectCreating, New ObjectDataSourceObjectEventHandler(AddressOf ods_ObjectCreating)

'after that, we only need to bind the ODS to the GridView

gv.DataSource = ods
gv.DataBind()

End Sub

End Class

---

The Default.aspx page which will alocate the gridview

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not Page.IsPostback Then

Dim gvf As New MyGridViewFiller
gvf.CreateCuteLittleTable(Me.Page, Me.GridView1.ID, 0, 10)

End If

End Sub

Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging

        Me.GridView1.PageIndex = e.NewPageIndex
        Dim gvf As New MyGridViewFiller

'the method pass the page, the gridviewid, the inital record number to be displayed according to the actual gridview pageindex, and the amount of records per page

gvf.CreateCuteLittleTable(Me.Page, Me.GridView1.ID, ((Me.GridView1.PageIndex + 1) * Me.GridView1.PageSize) -

(Me.GridView1.PageSize), Me.GridView1.PageSize)

End Sub

End Class

---

This is the storedprocedure which handles the paging, the example works with the table orders from the Northwind Database.

create procedure damenordenesdea
@nregini int,---number of the starting record to be displayed
@nregporpag int,---number of records to be displayed per gridview page
@nregis int output ---total number of the records resulted from the query
as
Declare @msql nvarchar(500)
set @msql=N'SELECT @nregis=count(*) from orders SELECT top ' + convert(varchar,@nregporpag) + ' * FROM orders
WHERE orderid
NOT IN
(SELECT TOP ' + convert(varchar,@nregini) + ' orderid FROM orders order by orderid asc)
order by orderid asc'
exec sp_executesql @msql,N'@nregini int,@nregporpag int,@nregis int output',@nregini=@nregini,@nregporpag=@nregporpag,@nregis=@nregis output

I have a working example if you want to test it, just let me know and i will send it to you.

I hope it helped you.

Choro.

Re: Custom Paging and the GridView ASP.NET 2.0 - you'll thank me for this !

obwill74 — Mon, 31 Aug 2009 16:18:46 GMT

You were right, this was very helpful.

I am having one issue though; I can not seem to pass the page number through from my aspx code behind page to the class with the data retrieval routine. Can you provide any information to help with that?

Re: Paging recordsets from various databases

chorofonfilo — Fri, 10 Jul 2009 18:31:43 GMT

keithoon:

Hi, I tried to convert it to VB and is facing some problem. Are you able to help?

How to convert the below to VB?

ods.ObjectCreating += new ObjectDataSourceObjectEventHandler(ods_ObjectCreating);

I got stuck at this as well, but i found out the equivalent expression for anyone interested.

'After your create the ods_ObjectCreating event Handler(as referred by the post of our friend):

Protected Sub ods_ObjectCreating(ByVal sender As Object, ByVal e As ObjectDataSourceEventArgs)

e.ObjectInstance = MyTableAdapter(_dt, _vic)

End Sub

'The hook to it should be set this way on the FillGrid Method

AddHandler ods.ObjectCreating, New ObjectDataSourceObjectEventHandler(AddressOf ods_ObjectCreating)

and thats it, i hope this was helpful to you all.

Best Regards.

Choro.

Re: Paging recordsets from various databases

tijoen — Tue, 23 Jun 2009 17:05:57 GMT

Thanks, it helped me too.

Re: Paging recordsets from various databases

binsys — Tue, 02 Jun 2009 16:17:51 GMT

Yes, yes, yes, I will thank you!!!!! - THANK GOD somebody wrote this down. I have been searching for days with this exact problem....

Re: Paging recordsets from various databases

keithoon — Fri, 11 Jan 2008 08:31:53 GMT

Hi, I tried to convert it to VB and is facing some problem. Are you able to help?

How to convert the below to VB?

ods.ObjectCreating += new ObjectDataSourceObjectEventHandler(ods_ObjectCreating);

Re: Paging recordsets from various databases

victorhvalle — Tue, 27 Mar 2007 18:37:04 GMT

Using the method of our friend the clown I've developed an "Enhanced Grid View"...

I had several problems because my business objects return IList instead of nasty DataTables so my adapter has little modifications. Just read the code and you'll can modify it to accept any Collection your business use. With this control the only thing you need to do is to add:

<%@ Register TagPrefix="[A prefix like 'netsux']" Namespace="[The namespace where EnhancedGridView class is located]" Assembly="[Your assembly]" %>

in your aspx file and to replace the <asp:GridView ...> tag with <prefix:EnhancedGridView ...>

I'm not a nice person as him but however here's my code...

    // This class must be located whenever you want  
    public class VirtualItemCountTableAdapter {
        private IList data;
        private Int32 virtualItemCount;

        public VirtualItemCountTableAdapter(IList data, Int32 virtualItemCount) {
            this.data = data;
            this.virtualItemCount = virtualItemCount;
        }
        public IList GetData() {
            return data;
        }

        public Int32 GetVirtualItemCount() {
            return virtualItemCount;
        }

        public IList GetData(int startRow, int maxRows) {
            return data;
        }
    }

    public class EnhancedGridView : GridView {
        private IList pagedList;
        private Int32 virtualItemCount;

        public override Object DataSource {
            get {
                return base.DataSource;
            }
            set {
                if (value is IList) {
                    pagedList = value as IList;

                    ObjectDataSource ods = new ObjectDataSource();

                    ods.ID = "ods_" + this.ID;

                    ods.EnablePaging = this.AllowPaging;
                    ods.TypeName = "VirtualItemCountTableAdapter"; // This must be the full name of the class
                    ods.SelectMethod = "GetData";
                    ods.SelectCountMethod = "GetVirtualItemCount";
                    ods.StartRowIndexParameterName = "startRow";
                    ods.MaximumRowsParameterName = "maxRows";
                    ods.EnableViewState = false;

                    ods.ObjectCreating += new ObjectDataSourceObjectEventHandler(ods_ObjectCreating);

                    base.DataSource = ods;
                } else {
                    base.DataSource = value;
                }
            }
        }

        private void ods_ObjectCreating(Object sender, ObjectDataSourceEventArgs e) {
            e.ObjectInstance = new VirtualItemCountTableAdapter(pagedList, virtualItemCount);
        }

        public Int32 VirtualItemCount {
            get {
                return virtualItemCount;
            }
            set {
                virtualItemCount = value;
            }
        }
    }

If problems persist... change to Java

Paging recordsets from various databases

Erwin@ODS — Sun, 04 Mar 2007 01:43:57 GMT

I said in the article I would post another article that points to some great stuff about paging records from various databases.

You'll find my new article here : http://forums.asp.net/thread/1603489.aspx

Have fun !

Erwin

Re: Custom Paging and the GridView ASP.NET 2.0 - you'll thank me for this !

mru22 — Mon, 12 Feb 2007 16:58:22 GMT

I am a little confused on how I would apply a search method where I pass numerou parameters. Currently I am using the object datasourece with the select method and the select count methods so i can provide sorting and paging.

On the following two lines,

_dt = //some code to create the 10-row DataTable
_vic = //some code to calculate the total rows of the table (30.000)

What would be some sample code that would calculate the 10 row table or the total ?

Custom Paging and the GridView ASP.NET 2.0 - you'll thank me for this !

Erwin@ODS — Tue, 30 Jan 2007 06:28:58 GMT

Ok, because I think all you clods have the same problem as a clod like me, the following post can really be helpfull, I think.

Remember the DataGrid in ASP.NET 1.x, and it's VirtualItemCount property ? If you wanted the DataGrid to display a custom pager row, you just had to set to "AllowCustomPaging" to true, bind a datasource to it, and supply a number to the VirtualItemCount property. Et voilà : the pager bar came up automatically ! Magic !

With ASP.NET 2.0 a new kid was in town : the GridView. Great stuff !! But, wait a minute, it isn't that easy to display a custom pager row !
I looked and looked and searched and read quite some stuff, and finally I came up with the following solution. Now, I could very well keep this just to myself, but since I'm such a nice person, I'll share it with all you clowns..

Suppose that you have the following situation :
- you have a GridView, and you want only 10 records to display at a time
- you have a table which contains 30.000 records
- you don't want to use the default paging of the GridView, because this will retrieve all 30.000 records at every round trip.
- you have figured out a way to retrieve only 10 records at a time (I'm not going into details of this, maybe in a later post, I found some great stuff to do just that !)

The only thing you need is to have the GridView display a pager row. And there, you're stuck. Because the GridView does NOT have a VirtualItemCount property, it only has a PageCount property, but that's read only !

A solution could be to do all the work declaratively at design time. In fact, all the tutorials and posts that you'll read show you how to do this declaratively, with the help of a ObjectDataSource and a TableAdapter.
But that is at design time !

What if you need to bind the GridView at run-time, i.e. programmatically ? That's where the real problem lies !

In order to solve this problem, you have to know 2 things :
- the ObjectDataSource (=ODS) needs to have the name of a class that will handle the data requests. When you do this by declaration, it's the TableAdapter that takes care of that. So, part of the solution is to create our own TableAdapter !
- on the other hand, the ODS at run-time creates an instance of that class with a parameterless constructor. You can shout "Yikes !" twice here : first, because the data-handling object can not be instantiated (constructed) with any parameters, and two : it can not be accessed because it's shielded in the ODS. So, how can you feed your precious 10-record table to that data-object ? Well, you can't...

Unless....

You do the following things :
1. Create your own TableAdapter that takes care of providing the data. Don't worry, if you're only interested in displaying records, it's done in a snap.
2. Create a event-handler that takes control over what kind of datasource object is created and handed over to the ODS. Again, this only takes some lines of code.

AND YOU'RE DONE !

Ok, let's get down to it.

First, let's create our own TableAdapter. When it's only for data-displaying, our TableAdapter should contain only 3 things :
- a method to supply the data (e.g. a datatable) (in our example, only 10 rows)
- a method to supply the total of numbers of the table (30.000)
- a method to supply the data with 2 parameters : one for setting the Start Row Index, the other for setting the Maximum Rows (actually, I'm not going to do anything with this, because I already have my DataTable with the right records ! Still, the method has to be there !)

Also, I'm going to write a constructor of this class that will take as parameters our cute little datatable and the total number of rows, which I'll will call "VirtualItemCount" (to salute the earlier DataGrid)

This is how it could look like :

    internal class MyTableAdapter
    {
        private DataTable _dt;
        private int _vic;

//this is the constructor that takes as parameters a cute little datatable
// with the right 10 records, an an integer vic = virtualitemcount
        public MyTableAdapter(DataTable dt, int vic)
        {
            _dt = dt;
            _vic = vic;
        }

//this returns the datatable (10 records)
        public DataTable GetData()
        {
            return _dt;
        }

//this returns the total number of records in the table (30.000)
        public int VirtualItemCount()
        {
            return _vic;
        }

//this also returns the datatable (10 records) but the ODS needs it for paging purposes
        public DataTable GetData(int startRow, int maxRows)
        {
            return _dt;
        }
    }

The next thing we'll do is to create a class which will handle the creation of a datatable, and hand it over to another function to feed the GridView

internal class MyGridViewFiller
{
  //these fields are for storing the cute little datatable and the virtualitemcount
  private DataTable _dt;
  private int _vic;

  //these fields are for storing the calling page and the GridViewID
  private Page _page;
  private string _id;

  public void CreateCuteLittleTable(Page Page, string GridViewID)
  {

   //here you create your cute little datatable
   //and also you calculate the total number of rows

   _dt = //some code to create the 10-row DataTable
   _vic = //some code to calculate the total rows of the table (30.000)

   //let's also store the page and the gridviewid
   _page = Page;
   _id = GridViewID

   //call the GridView filler
   FillGridView()
  }

  public void FillGridView()
  {

   //First let's make an GridView object that hold the GridView

   GridView gv = (GridView)_page.FindControl(_id);

   //Then create an ObjectDateSource object programmatically

                        ObjectDataSource ods = new ObjectDataSource();

   //setting the necessary properties (these will interact with our TableAdapter !)

                        ods.ID = "ods" + _id;

          ods.EnablePaging = gv.AllowPaging; //the paging of ODS depends on the paging of the GridView
          ods.TypeName = "MyTableAdapter"; //be sure to prefix the namespace of your application !!! e.g. MyApp.MyTableAdapter
          ods.SelectMethod = "GetData";
          ods.SelectCountMethod = "VirtualItemCount";
          ods.StartRowIndexParameterName = "startRow";
          ods.MaximumRowsParameterName = "maxRows";
          ods.EnableViewState = false;


   //HERE IS THE TRICK !!!
   //In stead of calling the parameterless constructor of our TableAdapter, I'm going
   //to hook up on the ods.ObjectCreating event to supply my own TableAdapter (WITH constructor parameters)
   //the following line is the "hook up" declaration

   ods.ObjectCreating += new ObjectDataSourceObjectEventHandler(ods_ObjectCreating);

   //this means that at the object creating event, the CLR is going to jump to my event handler (ods_ObjectCreating)

   //after that, we only need to bind the ODS to the GridView

   gv.DataSource = ods;
                        gv.DataBind();

   // AND THAT's ALL FOLKS !!!
  }

  //this is the ObjectCreating event handler
  private void ods_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
        {
   //calling a "parametered" constructor of my own TableAdapter and passing it to the ObjectInstance property

              e.ObjectInstance = new MyTableAdapter(_dt, _vic);

   //and that'all there's to it !!
        }

}

If you go very slowly through the code, you will find it very simple and straightforward.
And best of all : you have the GridView display the pagerrow !!!

Of course, the actual paging procedures are also to be written by you, but that's maybe for another post.

Good luck !

Erwin

PS Important remark : if you have cute little datatable of only 10 records, then also the GridView PageSize should be set to 10 !! (Duh!)