Record number when using SqlCeResultSet

[uveper]

Does anybody know how to get a number of rows when using DataTable
with datasource that is an SqlCeResultSet.
I use the following code to "select" the data into DataTable:


SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Customer";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
dgrData.DataSource = rs; //dgrData is DataTable

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

If it's a server side cursor you only know the size of it when the cursor is
closed.
In the meantime you cannot
In the same way I think it's ont a good idea to use it as a datasource, or
if you do so make sure to close the connection after.

 

[Guest]

i will sometimes resort, like today, to doing a second select for the count.
In a storedprocedure the second select is returned as the return code.
No, not the best way, but still better than using a cursor, please avoid
cursors they are real real slow. In my oppinion slower than looping through a
record set.
I'm working on an sp now and i'll show you what i did:

(note the return. ignore the rest, but it's included to show totallity(that
really a word?))
CREATE PROCEDURE [dbo].[sp_getRatingSummaryRange]
-- Add the parameters for the stored procedure here
@idate as INT, -- last date to display
@iRange as INT -- numbers of days history
AS
BEGIN
WITH dte(ColNo,idate) as (
select ROW_NUMBER() OVER (ORDER BY x.idxhstdate desc) as colNo,x.idxhstdate
from
(select top (@iRange) idxhstdate from idxhst where idxhstdate <= @idate
group by idxhstdate order by idxhstdate desc) x
),
unvids(RowNo, unvID, unvName) as (
select ROW_NUMBER() OVER (ORDER BY
isnull(dbo.fn_getLTSectorRatingUsingUNVID(u.unvid, @idate), 0) desc) as
RowNo, u.unvid, u.unvName
from unv u where u.unvActive = 1 and u.unvtype = 2 and u.unvsubmemof <>0
and unvid <> 56001
)
select u.unvName, d.idate, u.unvid, d.ColNo, u.RowNo,
isnull(dbo.fn_getLTSectorRatingUsingUNVID(u.unvid, d.idate), 0) as rating,
dbo.fn_getDisplayDate(d.idate) as dsplydate
from dte d
join unvids u on 1=1

where 1=1
order by RowNo desc, ColNo

return (select count(*) from unv u where u.unvActive = 1 and u.unvtype = 2
and u.unvsubmemof <>0 and unvid <> 56001)

an alternative would be to load the last select into a table variable and
then do a count, but it was not really worth it here.
It's a compromise, not perfect, but it works.


to get the return code i'll include a function definition that just gets the
count as the return code so as not to confuse:

--
(i''ll be asking a lot of these, but I find C# totally way cooler than vb
and there''s no go''n back!!!)
thanks (as always)

kes