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