working with relationships

5 Jan 2006 11:27 AM

papalazarou

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!

6 Jan 2006 9:55 AM

Frans Bouma [C# MVP]

papalazarou wrote:

Show quote

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

    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

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------