CODEWITHSUNDEEP
c#sqldatabaseoopdesign-patterns
Michael Yanni
Michael Yanni

Reputation: 1566

Database queries in OOP... design pattern?

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?

Upvotes: 1

Views: 2040

Answers (4)

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

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.

Upvotes: 1

T.S.
T.S.

Reputation: 19340

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

Upvotes: 0

Ben Gribaudo
Ben Gribaudo

Reputation: 5147

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);
}

Upvotes: 1

Wiktor Zychla
Wiktor Zychla

Reputation: 48230

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.

Upvotes: 3

Related Questions