Back here, I said the next time I wrote about the Active Record Pattern I'd show you how to handle parent and child relationships. So here we are. The first thing we are going to need is a SQL Server 2005 Express database to play in, so let's create one...
CREATE DATABASE ActiveRecord
go
USE ActiveRecord
go
Now, let's create minimalist Parent and Child tables...
CREATE TABLE Parent
(
ParentId
INT
NOT NULL
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
Tag
VARCHAR(20)
NOT NULL
)
go
CREATE TABLE Child
(
ChildId
INT
NOT NULL
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
ParentId
INT
NOT NULL
REFERENCES Parent(ParentId),
Tag
VARCHAR(20)
NOT NULL
)
go
And to finish, we need create and read stored procedures...
CREATE PROCEDURE CreateParent
@Tag VARCHAR(20)
AS
INSERT INTO Parent VALUES (@Tag)
SELECT scope_identity()
go
CREATE PROCEDURE CreateChild
@ParentId INT,
@Tag VARCHAR(20)
as
INSERT INTO Child VALUES (@ParentId, @Tag)
SELECT scope_identity()
go
CREATE PROCEDURE ReadParentAndChildrenById
@ParentId INT
AS
SELECT
p.ParentId,
c.ChildId,
p.Tag AS [Parent Tag],
c.Tag AS [Child Tag]
FROM
Parent p
inner join Child c ON p.ParentId = c.ParentId
WHERE
p.ParentId = @ParentId
go
Now that we have our demo database set up we need to add our handy little stored procedure for reflecting on the database...
CREATE PROCEDURE GetParamsForStoredProcNamed
@storedProcName VARCHAR(256)
AS
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
go
Finally, what we need to do is amend our implementation of the ActiveRecord class from last time to be able to handle the creation of parent and child rows in the table. To do this we simply add the ability of the class to use and enlist transactions. The code below should be well enough commented for you to follow but if not leave a comment on this post. N.B. this code is written in C# 3.5 using Visual Studio 2008 Beta 2.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Data;
using System.Reflection;
using System.Configuration;
namespace garyshort.org.patterns.ActiveRecord
{
abstract public class ActiveRecord
{
private SqlConnection _conn = null;
private SqlTransaction _transaction = null;
private Object lockObj = new object();
public void StartTransaction()
{
//GS - Create a connection
_conn = new SqlConnection();
_conn.ConnectionString =
ConfigurationManager.ConnectionStrings[
"ConnString"].ConnectionString;
_conn.Open();
//GS - Create a transaction
_transaction = _conn.BeginTransaction();
}
public void RollBackTransaction()
{
if (_transaction == null)
{
throw new Exception("No current transaction!");
}
_transaction.Rollback();
_transaction = null;
_conn = null;
}
public void CommitTransaction()
{
if (_transaction == null)
{
throw new Exception("No current transaction!");
}
_transaction.Commit();
_transaction = null;
_conn = null;
}
public SqlTransaction GetCurrentTransaction()
{
return _transaction;
}
public SqlConnection GetCurrentConneciton()
{
return _conn;
}
public void Create(
SqlTransaction currentTransaction,
SqlConnection currentConnection)
{
//GS - Set the connection and transaction
//values and call Create()
_transaction = currentTransaction;
_conn = currentConnection;
Create();
}
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
//GS - Set up a command object...
createCmd.CommandText = procName;
createCmd.CommandType =
CommandType.StoredProcedure;
//GS - and a connection object...
createCmd.Connection = _conn;
//GS - and a transaction...
createCmd.Transaction = _transaction;
//GS - Execute the command...
int newId =
Convert.ToInt16(createCmd.ExecuteScalar());
//GS - Assign the new Id to the object.
object[] args = { newId };
this.GetType().InvokeMember("Id",
BindingFlags.SetProperty, null, this, args);
}
reflectionCmd.Connection.Close();
}
}
}
public class Parent: ActiveRecord
{
public int Id { get; set; }
public string Tag { get; set; }
public List<Child> Children { get; set; }
public Parent()
{
Children = new List<Child>();
}
}
public class Child : ActiveRecord
{
public int Id { get; set; }
public int ParentId { get; set; }
public string Tag { get; set; }
}
class Program
{
static void Main(string[] args)
{
//GS - Let's test out the ActiveRecord
//GS - Create a new Parent
Parent p = new Parent();
//GS - Set its tag value
p.Tag = "Parent 1";
//GS - Start a transaction
p.StartTransaction();
try
{
//GS - Save the Parent to get an Id
p.Create();
//GS - Add children
for (int i = 0; i <= 5; i++)
{
Child c = new Child();
c.ParentId = p.Id;
c.Tag = "Child " + i.ToString();
p.Children.Add(c);
}
//GS - Save children, enlist the current transaction
foreach (Child c in p.Children)
{
c.Create(
p.GetCurrentTransaction(),
p.GetCurrentConneciton());
}
//GS - Uncomment this line to test transaction rollback
//throw new Exception();
//GS - Commit the transaction
p.CommitTransaction();
}
catch (Exception ex)
{
p.RollBackTransaction();
//GS - Set the object Ids to 0 to show
//they've not been serialised
p.Id = 0;
foreach(Child c in p.Children)
{
c.Id = 0;
}
}
//GS - Prove it was serialised by printing its ID
Console.WriteLine("Last Id created = " + p.Id.ToString());
Console.WriteLine("Here are my children..");
foreach (Child c in p.Children)
{
Console.WriteLine(c.Tag + " has id = " + c.Id.ToString());
}
//GS - Hold the console window open until the user hits enter
Console.ReadLine();
}
}
}