CODEWITHSUNDEEP
c#design-patternsdata-access-layer
Mats
Mats

Reputation: 14817

Data access pattern for an unconventional database layout

I've started maintaining an application, which comes with a big database, which is partly not normalized / pretty messy. A lot of duplicate data and some tables with lots of fields (30+). For example, I've got a table called Orders which contains lots of fields. I'd go and split this table up, but changes to the database layout are not allowed. Now, if I stick to the repository pattern for example, I believe I should be creating an Order entity and the CRUD methods for it. The problem is, that the business logic almost never requires the entire Order Entity to be loaded/updated, but only a subset. That would make up a lot of Entities (like FullOrder, OrderMetaInfo, OrderProcessingDetails, and so on).

My question is, what would be the best way to deal with this kind of database-mess? I was thinking of creating a simple class called Orders, the Entities mentioned before as POCOs and than have methods like UpdateOrderMetaInfo() or GetOrderProcessingDetails(). Thats seems to be a pretty good way until you start thinking about, that there are two tables, one is Orders and the other one ArchivedOrders (and no, the fields are not identical but very similar - don't even ask). It seems that I'll be running in a huge amount of duplicate code. Now I'm starting to think about writing a really simple database access class, where you pass in a handmade sql query and get back a recordsset, as in the good old days. Do you have a better idea than this?

Facts and Limits: It is an Sql Database and the project is written in C#. There is another system using the same database, so changing the database layout is not an option. Using EF or any 3rd party product for data access is not an option, too.

Sorry for the extensively long post and thanks for your feedback.

Upvotes: 0

Views: 314

Answers (3)

Andre Gallo
Andre Gallo

Reputation: 2269

This seems like a problem every other developer has come across ... this requirement of NOT changing the database sucks but if you have to stick with it I can share some of the things I've done with a very close app I worked with ... I used to have DAO objects that had a Linq To Sql datacontext. I agree that the Order entity doesn't need to be fully loaded but the way we got around it was to use some goodies of linq to sql like what is described here:

http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html

Pay special attention to the data load options and the compiled queries.

I have to tell you that this isn't the best solution but is doable and you can keep your code nice and clean with Linq to Sql which isn't something very complex ... hope this helps.

Upvotes: 0

Jorge Córdoba
Jorge Córdoba

Reputation: 52123

Why don't you just use the property setters to actually "mark" what needs to be updated.

For example, a user may do

Order.Client = "Jorge";
Order.Price = 300;
Order.Provider = "Microsoft";

then your setter will look like

public string Client{
set
{
  mClient = value;
  ModifiedFields.Add("ClientField");
}

and finally your Update method will decide, based on the ModifiedFields information the actual query that needs to be run in order to update the modified fields.

To actually retreive data you can simply have a general GetOrder() method if the query is not that slow, or... if it really weights enough to kill your performance have the getter for each property retrieve the needed information for a given subset of the data, either by having a caching policy or by providing a GetLastValue to the property.

Example:

public class DBField<T>
{
  private DBCommand getCommand;

  public T Value {get;set;}
  public T GetLastValue()
  {
     // Execute getCommand here
  }

  public DBField<T>(DBCommand GetCommand)
  {
    this.getCommand = GetCommand;
  }
}

I user will then do:

string Client = Orders["Id"].Client.GetLastValue();

Upvotes: 1

woggles
woggles

Reputation: 7444

I'm in a similar situation at work.

The way I dealt with it was to create a new database that only consists of views onto the existing database mess. Our apps then use the views for all crud operations.

Upvotes: 0

Related Questions