Solved

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

Posted on 2008-10-07
8
1,216 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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