What is the Active Record Design Pattern?
The Active Record design pattern is a technique to save data from in-memory objects to the database, and load data from the database into in-memory objects. This pattern includes the code for saving and loading inside the model class.
The Data Mapper Design Pattern is a similar pattern but moves the saving and loading code out of the model, into a separate class.
Example C# source code for the Active Record design pattern
Here, we have a typical Customer class.
using System.Data;
using System.Data.SqlClient;
namespace DesignPatternsCSharpNet6.ActiveRecord;
public class Customer
{
private const string CONNECTION_STRING =
"Data Source=(local);Initial Catalog=DesignPatterns;Integrated Security=True";
public int Id { get; set; }
public string Name { get; set; }
public bool IsPremiumMember { get; set; }
// Constructor is private.
// Static methods are used to create a new Customer or load an existing Customer
// This is not mandatory for the Active Record pattern, but is common
private Customer(int id, string name, bool isPremiumMember)
{
Id = id;
Name = name;
IsPremiumMember = isPremiumMember;
}
// Create a new Customer object
public static Customer CreateNew()
{
// Id of 0 indicates this is a new Customer that is not in the database
return new Customer(0, "", false);
}
// Create/load an existing Customer object from the database
public static async Task<Customer> GetByIdAsync(int id)
{
await using SqlConnection connection = new SqlConnection(CONNECTION_STRING);
connection.Open();
await using SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT TOP 1 * FROM [Customer] WHERE [Id] = @Id";
command.Parameters.AddWithValue("@Id", id);
SqlDataReader reader = await command.ExecuteReaderAsync();
// If the query returned a row, create the Customer object and return it.
if(reader.HasRows)
{
reader.Read();
string name = (string)reader["Name"];
bool isPremiumMember = (bool)reader["IsPremiumMember"];
return new Customer(id, name, isPremiumMember);
}
return null;
}
// INSERT if new, otherwise UPDATE in the database
public async void SaveAsync()
{
if (Id == 0)
{
await using SqlConnection connection = new SqlConnection(CONNECTION_STRING);
connection.Open();
await using SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
// SELECT SCOPE_IDENTITY() returns the Id value the database created
command.CommandText =
"INSERT INTO [Customer] ([Name], [IsPremiumMember]) VALUES (@Name, @IsPremiumMember); SELECT SCOPE_IDENTITY();";
command.Parameters.AddWithValue("@Name", Name);
command.Parameters.AddWithValue("@IsPremiumMember", IsPremiumMember);
// Get the Id generated by the database and save it to the object's property
Id = (int)await command.ExecuteScalarAsync();
}
else
{
await using SqlConnection connection = new SqlConnection(CONNECTION_STRING);
connection.Open();
await using SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText =
"UPDATE [Customer] SET [Name] = @Name, [IsPremiumMember] = @IsPremiumMember WHERE [Id] = @Id";
command.Parameters.AddWithValue("@Name", Name);
command.Parameters.AddWithValue("@IsPremiumMember", IsPremiumMember);
command.Parameters.AddWithValue("@Id", Id);
await command.ExecuteNonQueryAsync();
}
}
public async void DeleteAsync()
{
await using SqlConnection connection = new SqlConnection(CONNECTION_STRING);
connection.Open();
await using SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "DELETE FROM [Customer] WHERE [Id] = @Id";
command.Parameters.AddWithValue("@Id", Id);
await command.ExecuteNonQueryAsync();
}
}On lines 8 and 9, we have the connection string for the database. In a real program, this would probably be stored in a configuration file and passed into the object through the constructor.
On lines 11-13, we have the properties for this class.
The constructor is on lines 17-22.
I made this constructor private. The only ways to instantiate a new Customer object are to call the static CreateNew function (lines 26-30, for completely new customers) or the static GetByIdAsync function (lines 33-58, for when the customer exists in the database).
A private constructor is not mandatory to implement the Active Record design pattern, but it is something you may encounter.
When we want to save the Customer object to the database, we call the SaveAsync function on lines 61-96. This inserts new records into the database, when the Customer is new, and UPDATEs existing records. We can identify new records by the 0 in the Id property – the value set in the CreateNew function.
On line 73, the SQL statement for the insert also has “SELECT SCOPE_IDENTITY()” at the end. This is a SQL command that is run after the INSERT and it returns the ID value that the database created during the INSERT – in this example, the database creates the identity values on INSERTs.
On line 78, we get the Id value the database created and populate it into the object’s Id property. This way, if we call InsertOrUpdate a second time, the function will do an UPDATE – since the Id property is no longer 0.
On lines 98-110, we have the function to DELETE the Customer object from the database.
We didn’t have to pass in any parameters to the database functions. All the property values are available inside those functions because these functions are inside the business object being inserted, updated, or deleted.
Should you use the Active Record or Data Mapper design pattern?
The Data Mapper design pattern does similar functionality (saving/loading between in-memory model objects and database records). However, it moves the database code into a separate class.
Both patterns work; however, I prefer the Data Mapper pattern. Having the model also implement the code to save to the database and load from the database feels like a violation of the Single Responsibility Principle.
I usually want the Model classes to only hold property values and maybe have functions that execute business logic on those properties. Database code is not “business logic”, and mixing the two can lead to problems.
This extra database code makes the classes larger and gives each Model class an additional reason why it may need to be modified. An application can easily have dozens of Model classes. Using the Active Record pattern, if you make a change to the database, you may need to change dozens of classes. Moving the database functions out to a separate class can mean all database changes only need to take place in that one class.
A positive of the Active Record pattern is that, if you change the Model’s properties, the functions for the database are in the same place. It’s a bit easier to remember to change the database functions, and easier to see the Model’s properties – you don’t need to switch back and forth between the Model and the DataMapper class. Whichever pattern you choose, use that pattern for every Model in your project. Being consistent makes it easier to understand, modify, and maintain code.