Designing database interaction while following Single Responsibility Principle

Question

I'm trying to adhere to Single Responsibility Principle better, and I'm having issues grasping how to structure the general class design for communicating with a database. In a simplified version, I essentially have a database containing:

Manufacturer <== Probes <==> ProbeSettings

A probe has a manufacturer. A probe has 1 set of settings. The related objects are accessed all over the application, and quite frankly, the current implementation is a mess.

Currently, here's a general view of how communication and objects are implemented:

public class Manufacturer
{
  public int ID; // Primary key, auto-incrementing on insert
  public string Name;
}

public class Probe
{
  public int ID; // Primary key, auto-incrementing on insert
  public int ManufacturerID;
  public string Name;
  public int Elements;
}

public class ProbeSettings
{
  public int ProbeID; // Primary key, since it is unique.
  public int RandomSetting;
}

// This class is a mess...
public static class Database
{
  public static string ConnectionString;

  public static void InsertManufacturer(Manufacturer manuf); // ID would be ignored here, since it's auto-incrementing.
  public static void InsertProbe(Probe probe); // Again, ID generally ignored.
  public static void InsertProbeSettings(ProbeSettings probeSet);

  public static Manufacturer[] GetAllManufacturer();
  public static Probe[] GetProbesFromManufacturer(int manufacturerID);
  public static Probe[] GetProbesFromManufacturer(Manufacturer manuf);
}

I see many issues here.

1. Database does far too much.
2. These objects can be immutable when read really, the only issue is after inserting, I'm not sure what ID they were assigned, and the inserted object is now obsolete.
3. Anytime a class needs to get information from the Database, I'd have to add another Get method to handle a specific query.

I'm really at a loss here on what a correct implementation would be. My only real idea for improvement is some kind of base interface for database objects, although it might only help for inserts...

public interface IDatabaseObject
{
    void Insert(Database db);
    bool Delete(Database db);
}

What is a good way to actually implement this?

Answer 1

It sounds like you are looking for an ORM. Since you're working in C#, I'll assume you have access to LinqToSQL as part of the .NET framework. Linq can do what you're looking for as far as managing your basic CRUD operations. Similar projects, also worth checking out, are Castle ActiveRecord and NHibernate.

Answer 2

Well the best solution to work with DB while maintaining SRP (or any other kind of sane pattern) is to use some kind of ORM (for example, NHibernate).

This will allow you to work with classes as they are, instead of manually tossing them from/to DB.

For example, with NH your classes can look like this:

public class Manufacturer
{
  public string Name { ... }
  public IList<Probe> Probes { ... }
}

public class Probe
{
  public string Name { ... }
  public int Elements { ... }
  public ProbeSettings Settings { ... }
}

public class ProbeSettings
{
  public int RandomSetting;
}

As you see, you already do not need GetProbesFromManufacturer since you can just navigate the collection within Manufacturer.

Also, ORM will manage object ids and saving for you. So all that you will need will be just a small and fixed number of general methods like LoadById/LoadAll, which fit nicely into a class SRP of which is data access. Also you would probably need a class per each complex and configurable query to the DB.