I want to get and show only some records to table, not all

[alfer]

Hi

I have following problem. I have a big database table. I should to display this table using DataGrid. But, becouse the table so big is, it takes too much time.
I have following idea. I get from database and display only 50 records. When user navigates to the end of my small subset of records I will call a stored procedure to get the next 50 records. If user wants to see the last records I will get only 50 records from the end of my big table.
But this is only a pure idea... Supose I have a procedure:
public DataTable GetRecords(fromRecNo, toRecNo)
{
...
}
When and where shall I call this function? This must be something near DataGrid, I supose.

Do you have any idea?
May be do you know any other methotds of getting records to display without sending the whole table through the network?

Thanks!

 

[kmfna]

Hi Alfer,

When you use a datagrid, you could use the paging option which will display only so much at a time and as the user clicks on the subsequent pages C# will requery and only bring back the block of data you want. ie. Page 1 - show records 1-50, Page 2 - Requery and show records 51 - 100... and so on. Using C# datagrid paging is really nice, since it handles all of the dirty work for you, in that respect.

Good luck,
Kevin

- "The truth hurts, maybe not as much as jumping on a bicycle with no seat, but it hurts.

 

[alfer]

I haven't wrote this. I dont work with ASP. I would like to do this in a windows application. DataGrid does not have a paging property there. The application works in network environment and dthe DataTable is transferred through the local network.

 

[chiph]

This is called "paging". The general technique is to have sql that looks something like this:

SELECT TOP 50 CustomerName, Addr1, Addr2
FROM tblCustomer
WHERE CustomerID > @LastRecordSeen
ORDER BY CustomerID

The TOP 50 just returns you the first 50 records. The WHERE clause says to start after the last record the user saw. The ORDER BY is actually very important, as relational databases are not required to use any order at all if you leave it off.

If the user is going backwards through the list of customers, it becomes:

SELECT TOP 50 CustomerName, Addr1, Addr2
FROM tblCustomer
WHERE CustomerID < @FirstRecordSeen
ORDER BY CustomerID DESC

The sort order is changed, as well as the WHERE clause condition. Because the order is different, you need to re-sort the rows yourself.

There are more complex ways to solve this problem (do a google), but this will suffice for most peoples needs.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[link9]

I have this sproc that I use to facilitate what these other folks are talking about. If you're using SQL Server, you can plug it right in. If not, it will need some mods. I got this from Paul Wilson at

http://wilsondotnet.com.

CREATE PROCEDURE spGetSortedPage(
  @TableName VARCHAR(50),
  @PrimaryKey VARCHAR(25),
  @SortField VARCHAR(100),
  @PageSize INT,
  @PageIndex INT = 1,
  @QueryFilter VARCHAR(100) = NULL
) AS
SET NOCOUNT ON

DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)

SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))

IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN

  EXEC(
  'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
    (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
      (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')
    ORDER BY ' + @SortField + ')
  ORDER BY ' + @SortField
  )
  EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)

END
ELSE
BEGIN

  EXEC(
  'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
    (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
      (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
    ORDER BY ' + @SortField + ')
  ORDER BY ' + @SortField
  )
  EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)

END

RETURN 0
GO

As an aside, it's pretty easy to modify this sproc to return just a single record, too, if you need that functionality. A numbered record, so to speak.

-paul

The answer to getting answered -- faq855-2992