Client Data Virtualization and SQL Server

Question

I have a client application that contains a listview that needs to display records from a database table.

There are two complications:

1) Only some of the records should be displayed.

2) There could be millions of displayable records in the table.

I figure I need to use data virtualization here so that only the small number of records I need to display are retrieved. As the user scrolls through the listview more data will be retrieved.

The problem is that I do not have an [Id] or [Index] column in the table that can be mapped onto a listview item index. So if the listview needs data to display listview items #200 to #220 I cannot form a SQL query along the lines of "WHERE [Id] >= 200 AND [Id] <= 220"

I cannot modify the table, so I cannot create an Id column (nor would I know how to create one specifically for the displayable records).

My question is whether anyone knows of a good way of doing this kind of thing. What I want to avoid is coming up with a solution that is inferior to a well known "best practice" one.

For example one idea I had was to create an SQL View of the table which only contains the "displayable" records and also added an Id column to that table. Is that possible? More importantly is it a good solution? Is this the kind of thing Views are for? (I haven't used them before).

I just want to make sure I don't do something unnecessarily complicated, or flawed. You know someone comes along and says "you used *views* for that? Why didn't you just use X?"

Thanks!

p.s hope this is the right forum

Answer 1

Hi oblong,

The issue is related to bind data to the listview control. Could you please elaborate on the requirement on data displaying a bit more? According to which condition, the data is retrieved to the listview?

>> The problem is that I do not have an [Id] or [Index] column in the table that can be mapped onto a listview item index. So if the listview needs data to display listview items #200 to #220 I cannot form a SQL query along the lines of "WHERE [Id] >= 200 AND [Id] <= 220"
If you are attempted to query data page by page, with each page contained user-defined records, then you may have a look at the ROW_NUMBER() function on SQL Server 2005 or later. The code below is used to get the fifth page with 10 rows according to the parameters:

DECLARE @pagesize AS INT, @pagenum AS INT;
SET @pagesize = 10;
SET @pagenum = 5;
WITH SalesRN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY qty, empid) AS rownum,
empid, mgrid, qty
FROM dbo.Sales
)
SELECT rownum, empid, mgrid, qty
FROM SalesRN
WHERE rownum >=  @pagesize * (@pagenum-1)• AND rownum <= @pagesize * @pagenum
ORDER BY rownum;

You can create a stored procedure with required numbers to return partial data.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support

Answer 2

"Could you please elaborate on the requirement on data displaying a bit more? According to which condition, the data is retrieved to the listview?"

The row should only be retrieved if two columns have a certain value ( "WHERE [Type] = 6 AND [JobSeq] = 0" ), I think what you provided works. I am going to mark this as answer as this does exactly what I was hoping for. If I have any performance problems I'll raise another question but I suspect this will work fine.

Thanks for the help