Solved

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

Posted on 2008-10-07
8
1,206 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 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