working with relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am working on an SQL Server client for .NET and I have created a simple
recordlist class that accepts a table or row-returning sql command on
construction and provides a list view of the resultant query.

It would be good if i could create a relationship-aware version of the
recordlist class, that could determine if a particular field forms part of a
relationship and if so perform the required lookups.

I've looked over the dataadapter and dataset classes and they appear to work
the other way around, i.e you impose a set of relationships upon your queries
or define them using data shaping.

I understand that the relationships are stored in the dtproperties system
table, so i guess it must be possible to create a stored proc or function to
return the relationship properties of any database column. But i dont want to
implement this myself if the sqlclient consumer classes can do it.

If anyone can offer advice, i'd be most greatful as im a little unsure of
how best to proceed and I dont want to waste time reinventing wheels!

regards and happy new year!
 
papalazarou said:
Hi,

I am working on an SQL Server client for .NET and I have created a
simple recordlist class that accepts a table or row-returning sql
command on construction and provides a list view of the resultant
query.

It would be good if i could create a relationship-aware version of
the recordlist class, that could determine if a particular field
forms part of a relationship and if so perform the required lookups.

I've looked over the dataadapter and dataset classes and they appear
to work the other way around, i.e you impose a set of relationships
upon your queries or define them using data shaping.

I understand that the relationships are stored in the dtproperties
system table, so i guess it must be possible to create a stored proc
or function to return the relationship properties of any database
column. But i dont want to implement this myself if the sqlclient
consumer classes can do it.
Click to expand...

Relations aren't defined in taht table. You should determine which FK
constraints are defined. Then define a relation between the FK side and
the PK side and vice versa, and add each relation to each side, so the
PK side gets the PK side -> FK side relation and the FK side gets the
FK side -> PK side relation. This is tricky, as you also need to
determine the relation type: m:1/1:n etc. 1:1 are a special case, as
FK+UC also makes a 1:1 relation, so you also have to read the UC
constraints. After you've created those relations, you can define the
m:n relations. Two entities which have both a 1:n relation with a 3rd
entity have a m:n relation via that 3rd entity.

It depends on the db type where the meta-data for constraints is
located: sqlserver 2005 stores it differently than sqlserver 2000/7 for
example. generally you could use a couple of queries on the
INFORMATION_SCHEMA views.

You can also use a 3rd party application to do it all for you of
course :), for example an O/R mapper :)

FB

--
 
Back
Top