Return a dataset for a multitable query

[John]

Hello all,

Im fairly new to datasets and databinding and all that stuff.

I was wondering if anyone could tell me the best way to go about returning a
typed dataset for a multitable query. The way I do it at the moment I have a
dataset .xsd (xml schema) for each table in my database and then I load the
data into each dataset corresponding to the table....problem is that I cant
do multitable queries like that so I figured Ill make a dataset that will
handle all the fields in the query and then load the data into it, but that
sounds like alot of work and my .xsd file is already hige , is that how
everyone else does it?

Im a little bit lost
Thank you for any ideas and thank you for reading even if you dont have
ideas
John

 

[Cor Ligthert[MVP]]

John,

The way you are going now, has gone many times before you and has resulted
in disapointments.

Be aware that a datatable is in fact a single user class. You will soon
become in problems now.

It is better to select only what is needed use that, "how" that is depending
on your solution.

Cor

 

[John]

Thank you cor,

My solution currently has 3 tiers, all in VB.Net 2.0...
A webservice layer
An application layer
A user interface layer

I think I can call them that anyway, I am taking over a project from another
guy, who I think is as clueless as I am about architecture...

The application layer has one big dataset in it. The webservice is then
responsible for offering up functions that fill certain tables within the
dataset with data...We only fill the sections that need to be filled, but
the reference to the big dataset is used rampantly in many different
instances...

e.g. (short psuedocode)
function getUserById(id as integer) as bigdataset (which is a reference to
the big dataset)
"select * from tblUsers where id=" & id"
return bigdataset with only the tblUsers filled. and all others empty.
end function

Now that all seems to work fine (although im really suss on having a big
dataset with like 50 tables in it), and I really have no idea how to go
about doing multitable queries

Im scared haha

I hope that clarifies with enough information to provide a "How" answer,
thank you so much for your time, it is very much appreciated.
John

 

[Cor Ligthert[MVP]]

John,

Getting only the data you need by using the ID is the way to go.

The problem is that you cannot go with some SQL code, by instance the Select
can be something as

"select * from tblUsers where ID = @id"
And then some code as by instance
\\\
Try
dim conn as new SqlConnection
dim cmd as new SqlCommand(SqlString,conn)
dim dt as new DataTable
dim da as new SqlDataAdapter(cmd)
dim parm as new SqlParameter("@ID",TheID)
cmd.Parameters.Add(parm)
da.Fill(dt)
'do somethings
'now you have to create the update commands, those are standard insert,
update and delete SQL strings
'if you don't want that then you can use the cmd builder, be aware that this
one goes only
'in simple situatitons.
new SqlCommandBuilder(da)
da.Update(dt)
Catch ex as Exception
MessageBox.Show(exeption.ToString)
End Try
///

This is the simples format I know, be aware it is typed in this message, not
in the IDE so it can be full of errors.

Cor

 

[Michel Posseth [MCP]]

I just create views in my database , when i need to present the fields of
multiple tables
strong typed and works flawless


HTH

Michel

 

[John]

How do you go about getting the typed dataset back from the view? does it do
all that automatically?

We are on a MS-Access backend at the moment, we cant migrate yet for a few
reasons, primarily being an existing access front end that is not designed
very well...so im not sure if we can do that with the queries in there...

Thanks heaps for your ideas.
John

 

[John]

Thanks cor...

That datatable that is returned won't be typed though will it?

John

 

[Cor Ligthert [MVP]]

John,

That datatable that is returned won't be typed though will it?

No however that is just a matter of something like

dim ds as New YourTypedDataSet
dim dt as YourTypedDataSet.YourDataTable = ds.YourTable

Cor

 

[Michel Posseth [MCP]]

How do you go about getting the typed dataset back from the view? does it
do
all that automatically?

Yes it does this all automaticly , instead of pointing to a table you just
point the table adapter to a view
you cand do anything as if it was a direct table except updating ofcourse

There should be no difference whether you use SQL server or Access with this

hth

Michel

 

[Cor Ligthert[MVP]]

Michel,

Are you sure about the "strongly" typed datasets and if so, how do you make
the XSD?

Are you dragging a view on the surface of the IDE?

For a non strongly typed dataset your answer is obvious of course for me.

(I did not try it, I never use views)

Cor

 

[Michel Posseth [MCP]]

Hello Cor , John


Well i had some spare time and tested this with ACCESS for SQL i could have
given you an inmediate yes
i have tested this with ACCESS and it works as expected


Steps :

Create a Access database , put in some tables , create a view in Access (
query with fields from multiple tables with joins ) save it with your
prefered name

open your datasource window , set up a new datsource , follow the steps , in
the end instead of selecting a table you select views , under views you
select your previously made query
now drag this view on the surface of your form and everything is generated
automaticly or do everything by hand as your project is now extended with a
xsd file generated by the wizard , wich you can use in your project
Dim ds As New Database1DataSet

Dim ta As New Database1DataSetTableAdapters.QueryTestTableAdapter

ta.Fill(ds.QueryTest)

and in my case i can say


ds.QueryTest(0).bladibla
so is this strongly typed or not ??



You can extend the table adapters with filters on the view that suits your
needs , right click on the xsd file in the project explorere and choose
"view designer"

Regards



Michel

 

[Cor Ligthert[MVP]]

Hi Michel,

Thanks for testing it, interesting to know.

By the way, I hope that this will not raise my gas/electricity bill?



Cor

 

[Cor Ligthert [MVP]]

Hi Michel,

(I have placed this message in the wrong line)

Thanks for testing it, interesting to know.

By the way, I hope that this will not raise my gas/electricity bill?



Cor

 

[Michel Posseth [MCP]]

Well i did it in my personal spare time so it doesn`t , and if i did well
i am not so expensive that you would notice it on your bill
( in fact i am much to cheap )

Michel