| PROXY is a difficult pattern to use and is overkill for most applications. I would not use it unless convinced that I needed absolute separation between the business rules and the database schema. Normally, the kind of absolute separation afforded by PROXY is not necessary, and some coupling between the business rules and the schema can be tolerated. TABLE DATA GATEWAY (TDG) is a pattern that usually achieves sufficient separation, without the cost of PROXY. Also known as data access object (DAO), this pattern uses a specialized FACADE for each type of object we wish to store in the database (see Figure 34-9). Figure 34-9. TABLE DATA GATEWAY pattern 
OrderGateway (Listing 34-24) is an interface that the application uses to access the persistence layer for Order objects. This interface has the method Insert for persisting new Orders and a method Find for retrieving already persisted Orders. DbOrderGateway (Listing 34-25) implements the OrderGateway and moves Order instances between the object model and the relational database. It has a connection to an SqlServer instance and uses the same schema used previously in the PROXY example.[1] [1] I have to say that I hate the database access systems in today's major platforms. The idea of building up SQL strings and executing them is messy at best and certainly baroque. It's a shame that we have to write programs to generate SQL which was meant to be read and written by humans but that instead is parsed and interpreted by the database engine. A more direct mechanism could (and should) be found. Many teams use persistence frameworks, such as NHibernate, to hide the worst of the arcane SQL manipulations, and this is a good thing. However, these frameworks merely hide what ought to be eliminated.
Listing 34-24. OrderGateway.cs public interface OrderGateway { void Insert(Order order); Order Find(int id); }
|
Listing 34-25. DbOrderGateway.cs public class DbOrderGateway : OrderGateway { private readonly ProductGateway productGateway; private readonly SqlConnection connection; public DbOrderGateway(SqlConnection connection, ProductGateway productGateway) { this.connection = connection; this.productGateway = productGateway; } public void Insert(Order order) { string sql = "insert into Orders (cusId) values (@cusId)" + "; select scope_identity()"; SqlCommand command = new SqlCommand(sql, connection); command.Parameters.Add("@cusId", order.CustomerId); int id = Convert.ToInt32(command.ExecuteScalar()); order.Id = id; InsertItems(order); } public Order Find(int id) { string sql = "select * from Orders where orderId = @id"; SqlCommand command = new SqlCommand(sql, connection); command.Parameters.Add("@id", id); IDataReader reader = command.ExecuteReader(); Order order = null; if(reader.Read()) { string customerId = reader["cusId"].ToString(); order = new Order(customerId); order.Id = id; } reader.Close(); if(order != null) LoadItems(order); return order; } private void LoadItems(Order order) { string sql = "select * from Items where orderId = @orderId"; SqlCommand command = new SqlCommand(sql, connection); command.Parameters.Add("@orderId", order.Id); IDataReader reader = command.ExecuteReader(); while(reader.Read()) { string sku = reader["sku"].ToString(); int quantity = Convert.ToInt32(reader["quantity"]); Product product = productGateway.Find(sku); order.AddItem(product, quantity); } } private void InsertItems(Order order) { string sql = "insert into Items (orderId, quantity, sku)" + "values (@orderId, @quantity, @sku)"; foreach(Item item in order.Items) { SqlCommand command = new SqlCommand(sql, connection); command.Parameters.Add("@orderId", order.Id); command.Parameters.Add("@quantity", item.Quantity); command.Parameters.Add("@sku", item.Product.Sku); command.ExecuteNonQuery(); } } }
|
The other implementation of OrderGateway is InMemoryOrderGateway (Listing 34-26). InMemoryOrderGateway will save and retrieve Orders just like the DbOrderGateway, but stores the data in memory, using a Hashtable. Persisting data in memory seems rather silly, since all will be lost when the application exits. However, as we'll see later, doing so is invaluable when it comes to testing. Listing 34-26. InMemoryOrderGateway.cs public class InMemoryOrderGateway : OrderGateway { private static int nextId = 1; private Hashtable orders = new Hashtable(); public void Insert(Order order) { orders[nextId++] = order; } public Order Find(int id) { return orders[id] as Order; } }
|
We also have a ProductGateway interface (Listing 34-27) along with its DB implementation (Listing 34-28) and its in-memory implementation (Listing 34-29). Although we could also have a ItemGateway to acess data in our Items table, it's not neccessary. The application is not interested in Items outside the context of an Order, so the DbOrderGateway deals with both the Orders table and Items table of our schema. Listing 34-27. ProductGateway.cs public interface ProductGateway { void Insert(Product product); Product Find(string sku); }
|
Listing 34-28. DbProductGateway.cs public class DbProductGateway : ProductGateway { private readonly SqlConnection connection; public DbProductGateway(SqlConnection connection) { this.connection = connection; } public void Insert(Product product) { string sql = "insert into Products (sku, name, price)" + " values (@sku, @name, @price)"; SqlCommand command = new SqlCommand(sql, connection); command.Parameters.Add("@sku", product.Sku); command.Parameters.Add("@name", product.Name); command.Parameters.Add("@price", product.Price); command.ExecuteNonQuery(); } public Product Find(string sku) { string sql = "select * from Products where sku = @sku"; SqlCommand command = new SqlCommand(sql, connection); command.Parameters.Add("@sku", sku); IDataReader reader = command.ExecuteReader(); Product product = null; if(reader.Read()) { string name = reader["name"].ToString(); int price = Convert.ToInt32(reader["price"]); product = new Product(name, sku, price); } reader.Close(); return product; } }
|
Listing 34-29. InMemoryProductGateway.cs public class InMemoryProductGateway : ProductGateway { private Hashtable products = new Hashtable(); public void Insert(Product product) { products[product.Sku] = product; } public Product Find(string sku) { return products[sku] as Product; } }
|
The Product (Listing 34-30), Order (Listing 34-31), and Item (Listing 34-32) classes are simple data transfer objects (DTO) that conform to the original object model. Listing 34-30. Product.cs public class Product { private readonly string name; private readonly string sku; private int price; public Product(string name, string sku, int price) { this.name = name; this.sku = sku; this.price = price; } public int Price { get { return price; } } public string Name { get { return name; } } public string Sku { get { return sku; } } }
|
Listing 34-31. Order.cs public class Order { private readonly string cusid; private ArrayList items = new ArrayList(); private int id; public Order(string cusid) { this.cusid = cusid; } public string CustomerId { get { return cusid; } } public int Id { get { return id; } set { id = value; } } public int ItemCount { get { return items.Count; } } public int QuantityOf(Product product) { foreach(Item item in items) { if(item.Product.Sku.Equals(product.Sku)) return item.Quantity; } return 0; } public void AddItem(Product p, int qty) { Item item = new Item(p,qty); items.Add(item); } public ArrayList Items { get { return items; } } public int Total { get { int total = 0; foreach(Item item in items) { Product p = item.Product; int qty = item.Quantity; total += p.Price * qty; } return total; } } }
|
Listing 34-32. Item.cs public class Item { private Product product; private int quantity; public Item(Product p, int qty) { product = p; quantity = qty; } public Product Product { get { return product; } } public int Quantity { get { return quantity; } } }
|
Testing and In-Memory TDGs Anyone who has practiced test-driven development will attest to the fact that tests add up quickly. Before you know it, you'll have hundreds of tests. The time it takes to execute all the tests grows every day. Many of these tests will involve the persistence layer; if the real database is being used for each, you might as well take a coffee break every time you run the test suite. Hitting the database hundreds of times can be time consuming. This is where the InMemoryOrderGateway comes in handy. Since it stores data in memory, the overhead of external persistence is sidestepped. Using the InMemoryGateway objects when testing saves a significant amout of time when executing tests. It also allows you to forget about configuration and database details, simplifying the test code. Furthermore, you don't have to cleanup or restore an in-memory database at the end of a test; you can simply release it to the garbage collector. InMemoryGateway objects also come in handy for acceptance testing. Once you've got the InMemoryGateway classes, it's possible to run the whole application without the persistent database. I've found this to be handy on more than one occasion. You'll see that the InMemoryOrderGateway has very little code and that what code it does have is trivial. Of course, some of your unit tests and acceptance tests should use the persistent versions of the gateways. You do have to make sure that your system works with the real database. However, most of your tests can be redirected to the in-memory gateways. With all the benefits of in-memory gateways, it makes a lot of sense to write and use them where appropriate. Indeed, when I use the TABLE DATA GATEWAY pattern, I start by writing the InMemoryGateway implementation and hold off on writing the DbGateway classes. It's quite possible to build a great deal of the application by using only the InMemoryGateway classes. The application code doesn't know that it's not really using the database. This means that it's not important to worry about which database tools you're going to use or what the schema will look like until much later. In fact, the DbGateways can be one of the last components to be implemented. Testing the DB Gateways Listings 34-34 and 34-35 show the unit tests for the DBProductGateway and the DBOrderGateway. The structure of these tests is interesting because they share a common abstract base class: AbstractDBGatewayTest. Note that the constructor for DbOrderGateway requires an instance of ProductGateway. Note also that the InMemoryProductGateway rather than the DbProductGateway is being used in the tests. The code works fine despite this trick, and we save a few roundtrips to the database when we run the tests. Listing 34-33. AbstractDbGatewayTest.cs public class AbstractDbGatewayTest { protected SqlConnection connection; protected DbProductGateway gateway; protected IDataReader reader; protected void ExecuteSql(string sql) { SqlCommand command = new SqlCommand(sql, connection); command.ExecuteNonQuery(); } protected void OpenConnection() { string connectionString = "Initial Catalog=QuickyMart;" + "Data Source=marvin;" + "user id=sa;password=abc;"; connection = new SqlConnection(connectionString); this.connection.Open(); } protected void Close() { if(reader != null) reader.Close(); if(connection != null) connection.Close(); } }
|
Listing 34-34. DbProductGatewayTest.cs [TestFixture] public class DbProductGatewayTest : AbstractDbGatewayTest { private DbProductGateway gateway; [SetUp] public void SetUp() { OpenConnection(); gateway = new DbProductGateway(connection); ExecuteSql("delete from Products"); } [TearDown] public void TearDown() { Close(); } [Test] public void Insert() { Product product = new Product("Peanut Butter", "pb", 3); gateway.Insert(product); SqlCommand command = new SqlCommand("select * from Products", connection); reader = command.ExecuteReader(); Assert.IsTrue(reader.Read()); Assert.AreEqual("pb", reader["sku"]); Assert.AreEqual("Peanut Butter", reader["name"]); Assert.AreEqual(3, reader["price"]); Assert.IsFalse(reader.Read()); } [Test] public void Find() { Product pb = new Product("Peanut Butter", "pb", 3); Product jam = new Product("Strawberry Jam", "jam", 2); gateway.Insert(pb); gateway.Insert(jam); Assert.IsNull(gateway.Find("bad sku")); Product foundPb = gateway.Find(pb.Sku); CheckThatProductsMatch(pb, foundPb); Product foundJam = gateway.Find(jam.Sku); CheckThatProductsMatch(jam, foundJam); } private static void CheckThatProductsMatch(Product pb, Product pb2) { Assert.AreEqual(pb.Name, pb2.Name); Assert.AreEqual(pb.Sku, pb2.Sku); Assert.AreEqual(pb.Price, pb2.Price); } }
|
Listing 34-35. DbOrderGatewayTest.cs [TestFixture] public class DbOrderGatewayTest : AbstractDbGatewayTest { private DbOrderGateway gateway; private Product pizza; private Product beer; [SetUp] public void SetUp() { OpenConnection(); pizza = new Product("Pizza", "pizza", 15); beer = new Product("Beer", "beer", 2); ProductGateway productGateway = new InMemoryProductGateway(); productGateway.Insert(pizza); productGateway.Insert(beer); gateway = new DbOrderGateway(connection, productGateway); ExecuteSql("delete from Orders"); ExecuteSql("delete from Items"); } [TearDown] public void TearDown() { Close(); } [Test] public void Find() { string sql = "insert into Orders (cusId) " + "values ('Snoopy'); select scope_identity()"; SqlCommand command = new SqlCommand(sql, connection); int orderId = Convert.ToInt32(command.ExecuteScalar()); ExecuteSql(String.Format("insert into Items (orderId, " + "quantity, sku) values ({0}, 1, 'pizza')", orderId)); ExecuteSql(String.Format("insert into Items (orderId, " + "quantity, sku) values ({0}, 6, 'beer')", orderId)); Order order = gateway.Find(orderId); Assert.AreEqual("Snoopy", order.CustomerId); Assert.AreEqual(2, order.ItemCount); Assert.AreEqual(1, order.QuantityOf(pizza)); Assert.AreEqual(6, order.QuantityOf(beer)); } [Test] public void Insert() { Order order = new Order("Snoopy"); order.AddItem(pizza, 1); order.AddItem(beer, 6); gateway.Insert(order); Assert.IsTrue(order.Id != -1); Order foundOrder = gateway.Find(order.Id); Assert.AreEqual("Snoopy", foundOrder.CustomerId); Assert.AreEqual(2, foundOrder.ItemCount); Assert.AreEqual(1, foundOrder.QuantityOf(pizza)); Assert.AreEqual(6, foundOrder.QuantityOf(beer)); } }
|
|
