1

This is the basic problem. Say I have a base class Foo. This class does a call to the database to get some data to cache in the class.

public class Foo
{
    // Properties/fields go here.

    public virtual void ReadData()
    {
        // Queries the database for information and stores it in Foo.
    }
}

Now, say I have a derived class called Bar. This class wants to cache some additional data.

public class Bar : Foo
{
    // Additional properties/fields go here.

    public override void ReadData()
    {
        base.ReadData();

        // Queries the database for additional information and stores it in Bar.
    }
}

Now, looking at this, it seems like a somewhat common thing to do if you were doing regular OOP. But, in this case, you are accessing the database twice which is inefficient. I'm working on a legacy codebase that does practices like this all over the place. In the next release, they want database access optimization (meaning, less calls to the DB).

Is there a design pattern for database access that would work with OOP so I can minimize the amount of queries on the DB?

Improve this question
9
  • your example doesn't show where/how it accesses the db twice. imho going by the comments alone one cannot know what is really happening, and if (and how) it can be reduced Commented Dec 20, 2013 at 15:51
  • 2
    Just don't call base.ReadData? But do a combined SQL? Commented Dec 20, 2013 at 15:53
  • I'd look into the concept of composition, where the ReadData() method gets pushed into it's own IDataReader interface with two different implementations. Then you can use those in Foo or Bar interchangeably. Also, generally you want to make methods as small as possible, which benefits this approach. Basically, if you can imagine doing something two or more different ways, it should be an interface rather than inherited. Commented Dec 20, 2013 at 15:53
  • @Jason The comments for querying the DB involve opening a SqlConnection, creating a SqlCommand, calling ExecuteReader() on the command, and then called Read on the SqlDataReader. So, in both Foo.ReadData and Bar.ReadData, they are opening a new connection and creating a new command, getting the data, and storing it. Commented Dec 20, 2013 at 16:02
  • Am I correct in assuming that using an ORM (which would remove SQL-accessing code completely from these classes) is not an option? Commented Dec 20, 2013 at 16:05

4 Answers 4

Reset to default
3

The simplest trick would be to not to call the base class method. Instead, let the inherited class use a specialized query to get the data in possibly one shot.

If your requirement is to lower the number of queries, oop has no magic for this.

Then, another option would be to have a caching proxy, this is a regular design pattern to deal with caching.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

What about building an expression tree defining the data to fetch and how to map the data?

The parent class's ReadData() would ask for the data-to-fetch expression and do the fetching via a single SQL command. Through overriding, child classes could add to, manipulate or replace the parent's expression, as desired. Child classes would no longer need to query the db directly.

Writing the engine to convert the expression tree to SQL could take a bit of work....

In pseudo-code: Base Class

public void ReadData() {
    var expression = DataExpression();
    var connection = // new db connection

    // translates the expression into SQL, executes the query, retrieves results, maps those results
    DataExpressionHelper::Process(expression, connection);
}
protected virtual SqlExpression DataExpression() {
    // Return an  expression tree like:
    //  From("BaseDataTable").Map("Name" => this.Name).Map("Age" => this.Age);
}

Child Class

protected override SqlExpression DataExpression() {
    var expression = base.DataExpression();
    // Then, something to the effect of:
    //  expression += Join("ChildClassTable).On("parent.Id = child.ParentId).Map("Gender" => this.Gender);
}
Improve this answer

1 Comment

I definitely like this idea. However, it doesn't solve my current problem, and it really isn't a 'design pattern'. I will keep this kind of API in mind for the next version of the product. :-)
1

The general solution to this problem is to use composition, rather than inheritance.

Making this work most efficiently may also mean changing the design for your Foo type, so instead of caching data directly it only provides information about what things you want to cache. Then another type would look at instances of Foo and Bar, compose together what they want to cache, and get everything in one call.

Another wrinkle here is it's generally a bad idea for class objects to talk to the database directly. Typically, you want to have an object or a few objects grouped together to create a data access layer. All database access would go through these objects. Caching would take place at a higher level.

Improve this answer

3 Comments

It seems that you don't mean 'use composition'. Composition means a 'is-a-part-of' relationship between objects, whereas inheritance is an 'is-a' relationship. What you linked to is definitely a design concept that uses composition, but it isn't just 'composition'. I like their alternate name of Composite Reuse Principle, which correctly defines the concept.
I like the idea, but unfortunately, I can't redesign all the objects in the system. I'll keep the concept in mind for the new system. The concept seems similar to dependency injection, except not involving XML or non-code external files. Anyway, thanks for the info, but I don't think it'll solve my current dilemma.
@MichaelYanni: Dependency injection never implies those. Those are ways of doing it. Like the best solution to your problem, this is called the Strategy Pattern.
0

If your question is (and it is) "Is there a design pattern for database access that would work with OOP so I can minimize the amount of queries on the DB?"

The answer is NO - no design pattern will minimize amount of queries. It is the general design of your application that can lead to more or less queries.

Look into this 3 things:

  1. Lazy Loading

  2. Caching

  3. For performance of executing query: if you use dynamic Sql - parametrization of the Sql query will minimize resources used by DB Server to execute your query

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.