Solved

Use typed Dataset/DataRow on untyped Dataset (OR mapping)

Posted on 2008-10-07
8
1,198 Views
Last Modified: 2012-05-05
Hello together

I need to migrate a project from VB6 to c#

I have a problem with a library that works as a OR mapper

This library got their data in a recordset and than loops through the recordset as it would be objects

example

wegot table cars 1:n to tyres 1:n to deliverers

now there was a join to this 3 tables and 1 recordset with all neccessary information in it

the code can now browse fully object oriantated through this recordset like

for each car in datasource
   for each tyre in car
      for each deliverer in tyre
         debug.print deliverer.price
      next
   next
next

the classes maped the information from the recordset correct to the object.

now i like to use typed datasets as my classes. this works great if i have only one table as datasource. but if i have more than on table like in the example above i dont know how this can be done.

my apprach was to make a custom dataset an give acces to it with typed datarows. but i don't know how it can be done.

using third party OR mapper is no solution as the project doesn't allow it.

if typed datasets dont work it would be a complete custom solution with fully custom businessobject.

but i like the approach with typed datasets as they are very flexible and fast developed, but at the moment noas flexible as i need it.

hopefully you understand my problem. i add information if you need it.

regards
chris
0
Comment
Question by:zeebee
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:rambovn
ID: 22657655
0
 

Author Comment

by:zeebee
ID: 22658042
if i use several tables and the relations, how can i know wicht data to load

guess the former statment was

SELECT * FROM vehicel JOIN tyres JOIN deliverers
WHERE
vehicle.size < 400 AND
deliverers.county='USA' AND
tyre.type='rubber'

how to load the data correct to the several tables ?
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22717925
You can use multiple DataTable in a Dataset and link them together,

for exemple these 3 tables

Car
   CARID
   NAME

Tyre
   TYREID
   CARID
   NAME

Deliverer
   DELIVERERID
   TYREID
   NAME

You can create your typed dataset with the three datatable linked together using the ...ID fields, you can fill the same dataset using three dataadapter or tableadapter

This code is not tested but it should work or need little adjustement
dsCar ds = new dsCar();
SqlConnection sqlcnn = new SqlConnection(....);
SqlDataAdapter daCar = new SqlDataAdapter("SELECT * FROM Car",sqlcnn);
SqlDataAdapter daTyre = new SqlDataAdapter("SELECT * FROM Tyre",sqlcnn);
SqlDataAdapter daDeliverer = new SqlDataAdapter("SELECT * FROM Deliverer",sqlcnn);
daCar.Fill(ds.Car);
daTyre.Fill(ds.Tyre);
daDeliverer.Fill(ds.Deliverer);
foreach (dsCar.CarRow cr in ds.Car.Rows)
{
    foreach (dsCar.TyreRow tr in cr.GetTyreRows())
    {
        foreach (dsCar.DelivererRow dr in tr.GetDelivererRows())
        {
        ....
        }
    }
}

Hope it will help
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22717943
You may also take a look to the Entity Framework using the Framework 3.5 you will be able to do your projet using an object approach

Here is an introduction to the Entity Framework :
http://msdn.microsoft.com/en-us/library/bb399567.aspx

And a quick start showing you how to use it with a course manager having Students, Courses, Departement. it is similar to your project
http://msdn.microsoft.com/en-us/library/bb399182.aspx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:zeebee
ID: 22718858
how can the complex where condition be done ?

WHERE
vehicle.size < 400 AND
deliverers.county='USA' AND
tyre.type='rubber'
0
 
LVL 5

Accepted Solution

by:
jfmador earned 500 total points
ID: 22720233
Using DataAdapter the only way is to do 3 different select statement, it is kind of ineficient on the database

But, as i can see in your exemple you don't seem to have any reference key between your three tables, then you can add the condition part of your query in each of your three queries. Also you won't be able to use the GetTyres or GetDeliverers since there is no reference between each table

dsCar ds = new dsCar();
SqlConnection sqlcnn = new SqlConnection(....);
SqlDataAdapter daCar = new SqlDataAdapter("SELECT * FROM Car WHERE size < 400",sqlcnn);
SqlDataAdapter daTyre = new SqlDataAdapter("SELECT * FROM Tyre WHERE Type = 'rubber'",sqlcnn);
SqlDataAdapter daDeliverer = new SqlDataAdapter("SELECT * FROM Deliverer WHERE Country = 'USA'",sqlcnn);
daCar.Fill(ds.Car);
daTyre.Fill(ds.Tyre);
daDeliverer.Fill(ds.Deliverer);
foreach (dsCar.CarRow cr in ds.Car.Rows)
{
    foreach (dsCar.TyreRow tr in ds.Tyres.Rows)
    {
        foreach (dsCar.DelivererRow dr in ds.Deliverer.Rows)
        {
        .... you can list all possibility of car, tyres, deliverer
        }
    }
}
0
 

Author Comment

by:zeebee
ID: 22720755
ok that was a fault from me, of course there are reference keys it is a sample that should work in every scenario

so do you think that making multiple queries to the db is the only solution ?
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22720907
Using TableAdapter or DataAdapter it is the only way to do it. With the DataAdapter you can use multiple query like new SQLDataAdapter("SELECT * FROM Car; SELECT * FROM Tyres; SELECT * FROM Deliverers", sqlcnn);
And then use Table mapping or your dataadapter to set which queries goes with each datatable, but on the server side it will process three queries. and it become hard to read in your code.

If you can use .Net Framework 3.5 you may take a look to the Entity Framework from ADO.Net, I didn't read much about it yet but it look like an interresting technology.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now