ado.net - DataRelation Vs InnerJoin

Hanzaplast

hi guys i have developed for now several "middle-difficult" web applications. in short lines, they are all composed of custom controls inerhit from DataGrid or DetailsView, using sql queries to populate them with data. in some case their structure could be quite complex. my approach was this: lets say we have somewhat complex query:

select
dip.id,
satnica_obilazak,
gg.naziv,
t.naziv,
klijenti,
iznos as dodatno,
(
select top 1 cijena_sata_rada_obilazak
from klijenti_cijene
where datum<=dip.datum and klijent_id=@klijent_id
order by datum DESC, id
) as cijena_sata_rada,
klijent_id

from (((
dnevni_izvjestaj_popis dip

        inner join dnevni\_izvjestaj\_trgovine dit
        on dit.izvjestaj\_id=dip.id
    )
    left join dnevni\_izvjestaj\_troskovi ditr
    on ditr.izvjestaj\_id=dip.id)

inner join trgovine t 
on t.id = dit.trgovina\_id
)

inner join grupacije_gradovi gg
on gg.id=dip.grad

where
ditr.klijenti like ('%,'+@klijent_id+',%')
and tip_troska<>1
and tip_troska<>4
and tip_troska<>6

this is copy-paste from my project... i made "SqlAnalize" class which is accepting string "sql" as argument. in global, it goes character by character and populate ArrayList of table names from sql string compering to joins (inner join, left join, outer join...). after that i use DataAdapter to populate DataSet with all (or some) tables from sql query. when this is done, algorithm can put DropDownList, CheckBoxList (or whatever i need) in DataGrid. so here i have ClassLibrary with several controls which works this way... recently i had to develop some simmilar class like above, but for Xml source. this is where i met "problem". using xml, DataAdapter fills DataSet very "nice"... "nice" would be best word.... and here i met DataRelation class. i started to study some "DataRelation" tutorials, and find content interesting... so my question is simple: my approach in developing "smart" DataGrid(edit, delete, javascript confirm, dropdown list, uploads, wysiwyg, that kind of stuff inside) is using only sql queries. analize somewhat complex query, pass few values via some property, and finaly bind it. am i doing this wrong way? this DataRelation seems quite interesting. should i change approach? anyone who was working on similiar projects, how did you designed DataSets, DataTable, queries and relation between em? thanks in advan

Abhishek Sur

KokosZG wrote:

my approach in developing "smart" DataGrid(edit, delete, javascript confirm, dropdown list, uploads, wysiwyg, that kind of stuff inside) is using only sql queries. analize somewhat complex query, pass few values via some property, and finaly bind it. am i doing this wrong way? this DataRelation seems quite interesting. should i change approach? anyone who was working on similiar projects, how did you designed DataSets, DataTable, queries and relation between em?

I cant understand these few lines :(( :(( Well, Regarding DataRelation, it is actually giving some restriction on update of data within a dataset. There is nothing to deal with database if you dont want to use Update to the Dataset again to the Database. I generally dont use DataRelation as such, I keep foreign key on the database and handle the restriction in database. But seriously, it is nice to have. If you can implement a Relationship in the client end, you can throw error from the application itself, and dont require to run the update in the database. Note: By client, I mean from Application. Queries in Database is very different from the Query in DataSets. DataSets are in memory data, while DataBase is the actual storage place. Please clarify what exactly the problem you are facing to help you ... Cheers.

Abhishek Sur **Don't forget to click "Good Answer" if you like this Solution.

My Latest Articles-->** Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript

Hanzaplast

i will try to simplify as much as i can what i wrote ahead. let's say i need WebControl that can manipulate data from database. WebControl should have 3 fields (columns). when editing or inserting data via this control, first field is plain text (TextBox), other two must be falling menus (DropDownList). in database i have 3 tables first table have this structure: ------------------------------------------ id | TextField | Foregin1_id | Foregin2_id ------------------------------------------ second and third have this structure: --------- id | name --------- i need to join first table with other two, then bind it to WebControl. so this is my work: 1. i created custom control (class) "MyGird" inherited from the System.Web.UI.WebControls.DataGrid. 2. i created sql query

select
TextField, t2.name, t3.name
from (
table1 t1 inner join table2 t2
on t2.id=t1.Foregin1_id
)
inner join table3 t3
on t3.id=t1.Foregin2_id

3. i created some code in "MyGrid" that examines sql query, creates DataSet and fill it with all 3 tables, and finaly calls DataBind() function from it's base class System.Web.UI.WebControls.DataGrid. After bind i have "custom datagird" MyGrid. i can edit MyGrid and manipulate (insert, delete, update) data from first table of database in many ways, depending on "some code i created". so the "problem" is that this "some code i created" is not very "nice". some parts of code is hard to read. globaly this approach doesn't feels right. so question is; can i use DataRelation for joining data from more tables instead of using sql join? DataRelation seems to me like thing i have been mising for creating "MyGrid" in right way....