Selecting from Datasets

[aaj]

Hi all

I use a data adapter to read numerous tables in to a dataset.

The dataset holds tables which in turn holds full details of the records
i.e. keys, extra colums etc..

In some cases I need to use parts of the tables in datagrids, and here is
where my problem lies

lets say the Table in the dataset has 25 columns, but for display purposes
Im only interested in 2 of the columns, I envisage building a second (sub)
dataset and selecting the columns I'm interested in i.e. "SELECT
firstname,lastname from ..... WHERE ...." and using this as the sub DataSet,
ready for the datagrid, but I can't for the life of me figure how to do this

any advice would be most welcome

thanks

Andy

 

[Islamegy]

Do u mean you want to excute select query on first dataset?? I think you
can't..
I don't know why you get the all columns if you only need two.
For "Select........" you could Use add TableStyle to the datagrid and
display 2 columns only.
For "Where....." bind your gread to Dataview instead of dataset & use
RowFilter="colname like"+somecondition.

Islamegy

 

[aaj]

Hi Islamegy

Thanks for the reply

You are correct in your understanding of what I'm trying to achieve i.e.
execute a select on the first dataset to populate a second dataset, and I
think also you are correct that it doesn't look as though I can.

Just to explain a little further, the reason I bring back all the columns is
that I need the extra data elsewhere in the program e.g. I may have a table
for people who have firstname, lastname, address, telephone etc, but I may
only want to display firstname and last name in a particular datagrid, but
else where I need to know the primary key (when I do an update), and in
different section I might want to show the address and telephone number etc,
but I don't want to re query the source database each time.

I know that I could query the source database directly each time I need data
certain views of data for their datagrid, and modify the dataadapters select
to pull back the relevant columns, but I'm working on Pocket PC with
SQLServer CE at the be, and I would like to keep access to a minumum, by
having all read data buffered in a dataset for reads, and controlling writes
as necessary.

I think your correct in using the TableStyle, that's what I'm using at the
moment, and then removing the columns I don't need. It's this way of doing
things that prompted me to ask the question, as I don't think its sensible
to read in say 24 columns into the datagrid and then to delete 22 of them to
be left with the necessary ones

I think it would be more sensible to read all the necessary tables into a
dataset so in effect this becomes a virtual database, and then be able to
SELECT the columns I need into some sort of view, so only the relevant data
is displayed.

thanks again

Andy

 

[Islamegy]

Using your way will consume memory.. you want a new copy from you data which
is already there in memory in new dataset..
and i can see you want to use this technique everywhere u need few columns
which mean you will have so many copy of data!! i see it nonsense..

running query on a dataset is in memory operation and
using tablestyle in a grid will filter data for you (exactly the same)
without duplicate data in memory..

Use Dataview Filter or you may use Datatable Select() or
datatable1.Rows.Find(some PKey) to get single row... and u won't need to
query the database again.

hope this could help..
Islamegy

 

[aaj]

Thanks again for the response

The locating and displaying of filtered rows isn't a problem, as you say I
can use Table.select etc...

Its the hiding of columns loaded into a dataset that I'm thinking about.
Almost all the from end relies on reads, and I want to minimise hits to the
database file (.sdf) by recreating all necessary information as a dataset in
local memory. I want to be able to pull back a table that hold enough
information for all the classes that will depend on that data to work
without reqerying the database again i.e. PK, field 1, field 2,field 3 etc
is held in a table within a dataset.

If I then have a datagrid and I only want it to display Field 1 and field 2,
I dont want it to requery the database, as it already exists within the
dataset. But by binding the datagrid to the base dataset/table I then have
the overhead of removing each of the unused field. It just seems to me much
more sensible to build a second datatable with just the columns needed at
that instant and bind the grid to this instead, that way only the columns I
want to see are there, and there has been no hits to the database..

These copies for display will be transient and so only one copy of the extra
data will be needed at any time, the object will be destroyed as soon as it
is no longer displayed on the page, so there shouldn't be a memory problem.

From the discussions so far, It looks as though I cannot do this using the
built in functionality available. Thinking about it I suppose I could write
a class to do this for me, manually poplate the relevant columns from a
source into a new datatable, and provide an accessor to this class for the
GUI.

One reason I'm trying to do it this way is to ensure that there is only one
connection to the database as as far as I know, sdf files only support a
single open connection. If I let GUI classes contact the database each time
they need data, I'm imaginining that the overheads would be greater than
hitting an internal dataset.

again thanks for the help/advice so far, its an interesting discussion and
I'm always keen to see how other do things

thanks again

Andy

 

[Islamegy]

One last thing about Connection...
SQL provider manage Connection pooling which mean whatever sqlConnection
instance you call it's just one virtual connection with the database managed
by the pooling..
But little thing two know.. if there is any diffrent of any type in the
ConnectionString (Even a space char) this mean another connection so u don't
have to worry about connection..

look at google for .net connection pooling
thanx

Islamegy

 

[Cor Ligthert [MVP]]

Aaj.

The answer on your question is in my opinion completly written here

http://support.microsoft.com/default.aspx?scid=kb;en-us;317951

I hope this helps,

Cor