Back here I posted about the ActiveRecord pattern. Since then people have written to me asking how you actually implement it with regard to the business object not having to know anything about the database, but the ActiveRecord having to deal with any number of business objects that may subclass it.
Well there's a number of ways to do it I suppose, but the one I use is to do it by way of a mixture of convention and reflection. So, by way of example, if you subclass ActiveRecord to create a Customer business object then, by convention, the stored procedure that creates it is called CreateCustomer. What I do then is to reflect on the database to find out what parameters that stored procedure takes, then I reflect on the object to get the property values. Again, by convention, the object has properties matching the stored procedure parameters.
I have posted an implementation of the create function below, to illustrate what I mean, along with the stored procedure I use to reflect on the database.
In the next post on this topic, I'll look at the special case of serializing a master / detail relationship to the database.
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;
using System.Reflection;
namespace garyshort.org.patterns.activerecord
{
/// <summary>
/// GS - Implement the ActiveRecord pattern
/// </summary>
abstract public class ActiveRecord
{
public void Create()
{
//GS - By convention this method calls the
//CreateObjectName stored procedure so we
//need to reflect on the database to find
//out what parameters that stored proc takes
using (SqlCommand reflectionCmd = new SqlCommand())
{
string procName = "Create" + this.GetType().Name;
SqlConnection conn = new SqlConnection();
reflectionCmd.Connection = conn;
reflectionCmd.Connection.ConnectionString =
ConfigurationManager.ConnectionStrings[
"ConnString"].ConnectionString;
reflectionCmd.Connection.Open();
reflectionCmd.CommandText =
"GetParamsForStoredProcNamed";
reflectionCmd.Parameters.AddWithValue(
"@storedProcName", procName);
reflectionCmd.CommandType =
CommandType.StoredProcedure;
SqlDataReader reflectionDr =
reflectionCmd.ExecuteReader();
//GS - Now we know what params the stored proc takes
//we can fill it with the object properties which, by
//convention, will be named the same as the params.
//Reflect on the object to fetch the property values
using (SqlCommand createCmd = new SqlCommand())
{
string paramName = String.Empty;
object paramValue;
while (reflectionDr.Read())
{
//GS - Get the parameter name and add it if
//its not there already. Remember the
//parameter name from the stored procedure
//reflection will have an @ before it, so we
//have to substring past it.
paramName = reflectionDr.GetString(0);
if (!createCmd.Parameters.Contains(
paramName))
{
paramValue = this.GetType().InvokeMember(
paramName.Substring(1),
BindingFlags.GetProperty, null,
this, null);
createCmd.Parameters.AddWithValue(
paramName, paramValue);
}
}
//GS - Now all we have to do is execute the
//stored proc
createCmd.CommandText = procName;
createCmd.CommandType =
CommandType.StoredProcedure;
SqlConnection createConn = new SqlConnection();
createConn.ConnectionString =
conn.ConnectionString;
createCmd.Connection = createConn;
createCmd.Connection.Open();
int newId =
Convert.ToInt16(createCmd.ExecuteScalar());
createCmd.Connection.Close();
//GS - Assign the new Id to the object
object[] args = { newId };
this.GetType().InvokeMember("Id",
BindingFlags.SetProperty, null, this, args);
}
reflectionCmd.Connection.Close();
}
}
}
/// <summary>
/// GS - Create a business object that inherits from ActiveRecord
/// </summary>
public class Customer : ActiveRecord
{
//GS - Set up some properties for our customer
private int _Id = 0;
public int Id
{
get { return _Id; }
set { _Id = value; }
}
private string _FirstName;
public string FirstName
{
get { return _FirstName; }
set { _FirstName = value; }
}
private string _Surname;
public string Surname
{
get { return _Surname; }
set { _Surname = value; }
}
private string _EmailAddress;
public string EmailAddress
{
get { return _EmailAddress; }
set { _EmailAddress = value; }
}
//GS - Customer business implementation goes here
}
/// <summary>
/// GS - Create a program to test our implementation
/// </summary>
class Program
{
static void Main(string[] args)
{
//GS - Instatiate a new customer
Customer cust = new Customer();
//GS - Fill out the fields
cust.FirstName = "Gary";
cust.Surname = "Short";
cust.EmailAddress = "gary@garyshort.org";
//GS - Serialize it to the database
cust.Create();
//GS - Prove it was actually saved and we got an Id back
Debug.WriteLine("Id = " + cust.Id);
}
}
}
And here is the SQL used for reflection
SELECT
c.name AS ParameterName,
t.name AS Type
FROM
sysobjects o
INNER JOIN syscolumns c ON o.id = c.id
INNER JOIN systypes t ON c.xtype = t.xtype
WHERE
o.type = 'p' AND
o.name = @storedProcName