Class Design, this is getting out of hand. Trying not to 'hackit'....

[DotNetNewbie]

Hi,

In the PHP world, and similiarly in .NET, many people simply do the
SQL queries and return some sort of an array/hashtable (dataset or
datatables) as oppose to a strongly typed class in a collection.

Here is my issue. I have a class that maps to my Articles table, so
it has fields like ArticleID, articleTitle, articleBody, userID,
dateCreated etc.

So I can easily pull all the articles and store them in the class
Article inside some kind of a collection, no issues there.

The problem is this, some times I need to to some INNER JOINS on other
tables, and pull in extra columns. I have many combinations of
queries, so there are many different combinations of these additional
columns.

How do I go about doing this properly?

e.g. I could create another class like 'ArticleSet' or something, and
inherit from Article and then just add the additional columns. But
the problem with this approach is, sometimes these columns will be
NULL since it depends on the query I am performing at the given time
(i.e. if I am pulling all columns, or leaving out some etc).

What is the *best practice* in this scenerio?

Performance is an issue also if that effects anything.....

 

[Nicholas Paldino [.NET/C# MVP]]

DotNetNewbie,

Well, deriving from the Article class and adding the extra fields will
also only work if you have a one-to-one relationship with the Article class
as well. If you can't ensure that relationship, then it would be an error,
since you have multiple values that can be exposed, and yet you would have
to choose one.

In this situation, you are better off having a collection exposed from
the Article class which contains instances of objects that are the object
representation of the records that are joined to. Then, you populate the
instances that are exposed through the collection for each record that is
included in the join (or, have no related records, in which case the
collection you expose would have zero elements in it).

 

[Mufaka]

I am not sure if there is a best practice for this, but I would
personally subclass with read only properties for each type of query. I
am assuming that you only want these extra values to save db hits and
that you don't want to modify them.

ie:

class ArticleCategory : Article
{
private string _categoryName;

public ArticleCategory(string categoryName)
{
_categoryName = categoryName;
}

public string CategoryName
{
get { return _categoryName; }
}
}

class ArticleAuthor : Article
{
... (readonly author properties)
}

You may end up writing a lot of subclasses, but it will be very clear
how these are intended to be used.

When writing the subclass you can consider a few different options.

1. Provide properties for all values that can be retrieved from the
database for the type that you are joining. This limits the amount of
classes you will have to write, but leaves the users of the class to
know / check for what is available.

2. A variant on option 1 where you make every query populate all the
fields, which is more expensive. Consider allowing for large object
properties (text,ntext,binary) to be optionally populated.

3. Provide only properties that are provided by specific queries. This
means writing more classes (and more queries), but the users will know
what to expect and most likely maps to a specific view. It's less
flexible/re-usable.

I prefer option 2 myself until performance tweaks are needed. It gives
you a minimal amount of classes to work with so you can worry more about
what you are doing with the data rather than finding the most optimal
way to retrieve it beyond limiting database hits.

 

[Marc Gravell]

The problem is this, some times I need to to some INNER JOINS on other

tables, and pull in extra columns.  I have many combinations of
queries, so there are many different combinations of these additional
columns.

Sounds like a job for LINQ and anonymous types... if you have VS2008
and .NET 3.5, try looking at the "LINQ to SQL Classes" (in the "Add ->
New Item..." menu).

Marc

 

[DotNetNewbie]

Sounds like a job for LINQ and anonymous types... if you have VS2008
and .NET 3.5, try looking at the "LINQ to SQL Classes" (in the "Add ->
New Item..." menu).

Marc

Any other ideas?