Retrieving all rows from sqlce in a single fetch

Question

I have a query for the SQLCE database like

     select mobileNumber from customers where balance > 10000

Till now, I was looping over the SqlCeResultSet like

     while (resultSet.Read())
     {
         mobileNumberList.Add(resultSet.GetValue(0));
     }

So I want to get the list of mobile numbers in a single fetch may be as a list. How do i do that? Because the getting in a single fetch will be faster, right?

UPDATE
Few things I read after reading the answers:
SqlDataReader vs SqlDataAdapter

score 1 · Answer 1 · answered Aug 29 '11 at 08:12

currently you are using a DataReader which is a read only, forward only "cursor" scrolling the result set and works only while connected to the data source.

If you would change your ExecuteReader with a data adapter and Fill dataset you would get a DataSet/DataTable object which would contain all results and can still work disconnected, after you have closed the connection.

What you want to use depends on your real use case and needs, generally DataReaders are faster than datasets so you might be already using the fastest available approach.

+1 for the information. will adapter be slower than data reader if i am going to use all data at once? — Prakash, Aug 29 '11 at 09:32

score 0 · Answer 2 · edited Oct 19 '12 at 21:04

0

I don't see the dataset getting populated here. Shouldn't it be ds.Fill(adp) bit apd.Fill(ds)? Doesn't that fill a SqlCeDataAdapter using an empty DataSet that was never populated or am I totally confused?

SqlCeDataAdapter adp = new SqlCeDataAdapter("select mobileNumber from customers where balance > 10000");
DataSet ds = new DataSet();
adp.Fill(ds);

edited Oct 19 '12 at 21:04

Igor

33,276
14
79
112

answered May 17 '12 at 11:35

Lucas

1

First of all we have query result in data adapter(adp) and adp.Fill(ds) says fill the empty dataset(ds) with the result in data adapter(adp) – Darshana May 18 '12 at 04:24

score 0 · Accepted Answer · answered Aug 29 '11 at 08:10

You can use SqlCeDataAdapter to fill DataSet in a single fetch with all the records returned by query.

e.g:

SqlCeDataAdapter adp = new SqlCeDataAdapter("select mobileNumber from customers where balance > 10000");
DataSet ds = new DataSet();
adp.Fill(ds);

Retrieving all rows from sqlce in a single fetch

3 Answers3