Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-07
8
Medium Priority
?
1,227 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

885 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