Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-07
8
Medium Priority
?
1,223 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

670 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