Fake repository code from SQL

As I venture into the early stages of life as a Test Driven Developer, I have found it useful to have fake repositories. This allows me to test how the UI will look with out having to wait on fully functional repository/service layer to be in place. I have little SQL pattern I use to generate objects for my fake repo. It may be of use to others, it may not.

Starting with the end in mind, for this example I basically want a bunch of Orders with their order lines. But if I want a small sample of data say 20 orders and each have only 5 lines that requires me to write a heap of code manually. Instead if we already have the test data agreed on that will populate the final DB or we have an existing database, I can use that data for my fake. Basically I want code to look like this:

Collection<Order> orders = new Collection<Order>();
Order order;

order = new Order();
order.OrderNumber = 1;
order.DateOrderPlaced = new DateTime(2001, 5, 17);
order.Lines.Add(new OrderLine(products[1], 50.26M, 4));
orders.Add(order);

order = new Order();
order.OrderNumber = 2;
order.DateOrderPlaced = new DateTime(2001, 5, 17);
order.Lines.Add(new OrderLine(products[359], 45.12M, 3));
order.Lines.Add(new OrderLine(products[360], 45.58M, 3));
orders.Add(order);

I have already written the code for the products dictionary so lets just focus on how to generate this code.

Using the AdventureWorks data base in SqlExpress I will get the data to populate this fake data. Now the tricky part here is that we are creating text that for each order has header info (the order object), body info (the order lines) and footer info (adding the order to the orders collection). Some may consider cursors straight off the bat, but I have found what I think to be an easier way. Using the ROW_NUMBER() feature I can determine when to output header, body or footer text. If we partition on the Order Id (in this case the PurchaseOrderId) we will get a Row numbers for each of our orders that start at 1 for each of our orders.

SELECT
    O.PurchaseOrderID,
    ROW_NUMBER()OVER (PARTITION BY O.PurchaseOrderID ORDER BY ProductId) as Row#,
    OrderDate,
    ProductId,
    UnitPrice,
    OrderQty
FROM 
    Purchasing.PurchaseOrderHeader H
    INNER JOIN Purchasing.PurchaseOrderDetail O
        ON H.PurchaseOrderId = O.PurchaseOrderId

From this data we know when to output the header stuff (WHERE RowNumber=1).

Id	Row#	OrderDate	ProductId	UnitPrice	OrderQty
1	1	17 May 2001	1	50.26	4
2	1	17 May 2001	359	45.12	3
2	2	17 May 2001	360	45.58	3
3	1	17 May 2001	530	16.09	550
4	1	17 May 2001	4	57.03	3
5	1	31 May 2001	512	37.09	550
6	1	31 May 2001	513	26.60	550
7	1	31 May 2001	317	27.06	550
7	2	31 May 2001	318	33.58	550
7	3	31 May 2001	319	46.06	550

But we don’t know when to to output the footer row. So we add a count mechanism.

SELECT
    O.PurchaseOrderID,
    ROW_NUMBER()OVER (PARTITION BY O.PurchaseOrderID ORDER BY ProductId) as Row#,
    OrderCount.[Count],
    orderdate,
    ProductId,
    UnitPrice,
    OrderQty
FROM 
    Purchasing.PurchaseOrderHeader H
    INNER JOIN Purchasing.PurchaseOrderDetail O
        ON H.PurchaseOrderId = O.PurchaseOrderId
    INNER JOIN 
    (
        SELECT 
            PurchaseOrderID, 
            Count(*) as [Count] 
        FROM 
            Purchasing.PurchaseOrderDetail T2
        GROUP BY 
            PurchaseOrderID
    ) as OrderCount
        ON O.PurchaseOrderID = OrderCount.PurchaseOrderID 

Id	Row#	Count	OrderDate	ProductId	UnitPrice	OrderQty
1	1	1	17 May 2001	1	50.26	4
2	1	2	17 May 2001	359	45.12	3
2	2	2	17 May 2001	360	45.58	3
3	1	1	17 May 2001	530	16.09	550
4	1	1	17 May 2001	4	57.03	3
5	1	1	31 May 2001	512	37.09	550
6	1	1	31 May 2001	513	26.60	550
7	1	3	31 May 2001	317	27.06	550
7	2	3	31 May 2001	318	33.58	550
7	3	3	31 May 2001	319	46.06	550

Now we can identify the first row per order and the last row. So all we need to do now is write the output code!

SELECT
    CASE
        --Single line order -->Header + body + footer
        WHEN ROW_NUMBER()OVER (PARTITION BY O.PurchaseOrderID ORDER BY ProductId) = 1 AND OrderCount.[Count] = 1 THEN 
'
            order = new Order();
            order.OrderNumber = ' + cast(O.PurchaseOrderID as varchar(100)) + ';
            order.DateOrderPlaced = new DateTime(' + cast(Year(orderdate) as varchar(max)) + ', ' + cast(Month(orderdate) as varchar(max)) + ', ' + cast(Day(orderdate) as varchar(max)) + ');
            order.Lines.Add(new OrderLine(products[' + cast(ProductId as varchar(100)) +'], ' + Cast(UnitPrice as varchar(max)) + 'M, ' + cast(orderQty as varchar(100)) +'));
            this.Orders.Add(order);'
        --Header
        WHEN ROW_NUMBER()OVER (PARTITION BY O.PurchaseOrderID ORDER BY ProductId) = 1 THEN 
'
            order = new Order();
            order.OrderNumber = ' + cast(O.PurchaseOrderID as varchar(100)) + ';
            order.DateOrderPlaced = new DateTime(' + cast(Year(orderdate) as varchar(max)) + ', ' + cast(Month(orderdate) as varchar(max)) + ', ' + cast(Day(orderdate) as varchar(max)) + ');
            order.Lines.Add(new OrderLine(products[' + cast(ProductId as varchar(100)) +'], ' + Cast(UnitPrice as varchar(max)) + 'M, ' + cast(orderQty as varchar(100)) +'));'
        --Footer
        WHEN ROW_NUMBER()OVER (PARTITION BY O.PurchaseOrderID ORDER BY ProductId) = OrderCount.[Count] THEN 
'            order.Lines.Add(new OrderLine(products[' + cast(ProductId as varchar(100)) +'], ' + Cast(UnitPrice as varchar(max)) + 'M, ' + cast(orderQty as varchar(100)) +'));
            this.Orders.Add(order);'
        --Body
        ELSE
'            order.Lines.Add(new OrderLine(products[' + cast(ProductId as varchar(100)) +'], ' + Cast(UnitPrice as varchar(max)) + 'M, ' + cast(orderQty as varchar(100)) +'));'
    END
FROM 
    Purchasing.PurchaseOrderHeader H
    INNER JOIN Purchasing.PurchaseOrderDetail O
        ON H.PurchaseOrderId = O.PurchaseOrderId
    INNER JOIN 
    (
        --Get the line count for each order so we can Identify the footer row.
        SELECT 
            PurchaseOrderID, 
            Count(*) as [Count] 
        FROM 
            Purchasing.PurchaseOrderDetail T2
        GROUP BY 
            PurchaseOrderID
    ) as OrderCount
        ON O.PurchaseOrderID = OrderCount.PurchaseOrderID 

Codeplex annoys me

I thought I might make this public in the hope that 1 of 2 things happens;

1. either everyone agrees with me
2. someone shows me the light and tells me how to use the bloody thing.

For a site that is basically a public code repository, I would think that it would be easy to navigate and provide a simple way to document the application so the end user doesn’t have to down load the whole thing and poke it with a stick to figure out what it is.

How Did You Get Started in Software Development?

After a post from little brother (well ten minutes younger, he is my twin) regarding his history in computers, I thought I would follow suit.

Like Rhys, my history started with trying to install games and getting them to either run, or run faster. My first computer exposure was an Amiga 500 at the age of 8 in primary school. We had a computer club with 4 members: A nerdy teacher, my mate, my mate’s older brother and me. We did nothing but learn to format floppy disks and play some flight simulator. We then got an Apple II gs at home which was used for basically printing out pretty thing we made in its word processor and dot matrix color printer. It always was running low on memory which at them time meant nothing to me as I thought all the memory was on the floppy disk. Then we upgraded to a PC. besides the CD drive I couldn't for the life of me figure out why this was an upgrade. Everything seemed worse than my 5+ year old Amiga. Then some gave us an illegal copy of doom and we never looked back to Amigas and apples (till recently). Yes we did end up getting a real copy of Doom by the way. Ironically stealing game software for a 13 year was a real introduction to IT. Zipping files across 8 disks, modifying AUTOEXEC.Bat and CONFIG.SYS files was the real first steps into IT. From there I took some Computer studies classes in High School. We learnt QBasic, HTML, Databases & Gate Logic. It was a really cool intro to programming (instead of word processing or IT admin).

University – Or lack of.

From high school I progressed to Uni. I started with CompSci, Physics and Calc. My grades were alright, but I really hated it. The material was kinda fun but didn’t feel like it was applicable to anything. So with some good advise I took up an Diploma in Business Programming. I know it sounds very dorky, but was leagues better than the crap I was being feed at Uni. Once I finished that full time year long course I had written an asp web site running off an Access database in my spare time. Looking back it is actually ok code. www.northsport.co.nz

Jobs – FTE to contractor

My first two jobs were post Y2K eCommerce companies. I was lucky to work with a range of talents which taught me my first lessons in corporate life. Conduit was an ASP + SQL Server 7 development shop. This is where I came to grips with (D)HTML, javascript, css, SQL Server, SPs, integration. I followed my mentor Jerome from my first job to my second at his company Neotek. Neotek was an amazing experience and cemented a wide range of skills I had started to pick up at Conduit. Neotek was an early adopter of .Net and had a fantastic product offering which constantly amazed clients with its speed (especially its pricing model).

My sporting career lead me to Perth. While there I continued to help Neotek with small stand alone projects and picked up a contract building ad hoc websites for Pretzel Logic, a local company. Pretzel was fun as most of the work was 12 week projects that I could dive into get, get done and move on to the next site. From there I moved to contracting for a Bank. This is where I am now. Working for the Bank has proved to be bitter-sweet. We were (very) early adopters of WPF, CAB & WCF which has been awesome to get dirty with new technologies. However big banks move slowly and are full of politics. Bugger.

So Rhys, there is my spiel.

Serialization != XmlSerializer

I have seen some interesting code in recent months around serialization. It appears that an old habit that any serialization would be done with the XmlSerializer, has turned into serialization has to be done with the XmlSerializer.

Now while XmlSerializer is great, it has some pitfalls. Notably for this post, it can’t serialize object graphs with recursive references. To illustrate better consider an Order object with a collection of OrderLine objects. Each of the OrderLine object has a property that referenced the Order it belonged to. Now in .Net this is no problem and can be kind-of handy to have Orders know about their OrderLines and then be able to get back to the Order from the OrderLine. However this is hard to describe in XML (at least in a standardized way) as XML is inherently hierarchical.

[Serializable]
public class Order
{
    private Collection<OrderLine> _lines = new Collection<OrderLine>();
    public int OrderNumber { get; set; }

    public Collection<OrderLine> Lines
    {
        get { return _lines; }
    }
}

[Serializable]
public class OrderLine
{
    public string ItemCode { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public string Description { get; set; }
    public Order Parent { get; set; }

    public OrderLine()
    {
    }

    public OrderLine(string itemCode, decimal unitPrice, int quantity, string description, Order parent)
    {
        this.ItemCode = itemCode;
        this.UnitPrice = unitPrice;
        this.Quantity = quantity;
        this.Description = description;
        this.Parent = parent;
    }
}

I also thought it would be interesting to consider the impact of best practices regarding properties that are collection types.  It is considered to be best practice in .NET code to only provide a getter on properties that are collections.  [see Framework Design Guidelines 8.3.2]

This however throws a wobbly when used as a DTO with WCF. Now my assumption here is that to be interoperable with other systems (thinking Java), a getter and a setter would need to be provided to allow serialization and then de-serialization over the wire.

From my observations, this constraint has somehow been perceived to apply to all serialization. I have seen setters being applied to objects where there is no need but for the misinformed notion that it is required for serialization. The thing that really surprises me is that it is so easy to verify your assumptions regarding these problems. So, lets play!

1st up:  Serializing Hierarchies (well really collections)

If we take a simplified version of the code above where the Parent property is removed so we just have an object with a collection property. This serializes fine. I also do not need to provide a setter for my collection property.

Order myOrder = new Order();
myOrder.OrderNumber = 1;
myOrder.Lines.Add(new OrderLine("ABC", 10, 1, "First Item"));
myOrder.Lines.Add(new OrderLine("ABCD", 4, 1, "Some content"));
myOrder.Lines.Add(new OrderLine("ABD", 10, 4, "This Item"));
myOrder.Lines.Add(new OrderLine("ACD", 166, 1, "Test"));
myOrder.Lines.Add(new OrderLine("BCDE", 24, 9, "Last Item"));

System.Xml.Serialization.XmlSerializer oXS = new System.Xml.Serialization.XmlSerializer(typeof(Order));
System.IO.StreamWriter ostrW = new System.IO.StreamWriter(@"C:\SavedOrder.xml");
oXS.Serialize(ostrW, myOrder);
ostrW.Close();

2nd Test: recursive graphs (adding the Parent property back in)

If we add the parent property back in and set it correctly we will get an InvalidOperationException when we try to serialize it. Now to me, this is not a time to throw our hands in the air and start coming up with wild work-arounds for how to serialize our object. My first question would be “Does this result of the serialization have to be human readable?” if the answer is no then Binary Serialization works a treat.

This little helper method will serialize any old thing to disk for you.

public static void SerializeToBinaryFile(Object entity, string path)
{
    Stream stream = new FileStream(path, System.IO.FileMode.Create);
    IFormatter formatter = new BinaryFormatter();
    formatter.Serialize(stream, entity);
    stream.Close();
}

Now its not exactly production quality code but it proves a point.

If the answer to the above question of readability was yes, then we may have to ask some other questions of our requirements. It is very likely that we are leaking our domain model into another layer. If serialization is required to saved to disk then the disk can be considered a repository and may require a less specific model. The same can be said it serialization is for communication between systems. Both these scenarios would have no requirement to have the recursive nature of our model described. Our implementation with the recursion can be implied from the raw data.

For a working example of this code download this C# file.

SerializationSpike.zip