Examples

Below is a comprehensive list of examples that go through many of the features offered by the Jerrycurl framework. Most of these are available in the Jerrystore project from our samples repo.

Mapping the entire database from a customer point of view through a multiset query. Results are automatically joined using the predefined PK/FK relationship annotations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
public class CustomerDatabaseView : Customer
{
    public IList<OrderView> Orders { get; set; }
    public CustomerAddress Address { get; set; }

    public class OrderView : Order
    {
        public IList<OrderLineView> Lines { get; set; }
        public CustomerAddress Billing { get; set; }
    }

    public class OrderLineView : OrderLine
    {
        public Product Product { get; set; }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Queries/Customers/GetDatabase.cssql
@result Jerrystore.Views.Customers.CustomerDatabaseView

@{
    var o = this.R.Open(m => m.Orders);
    var ol = o.Open(m => m.Lines);

}

-- customers and addresses
SELECT
    @R.Star(),
    @R.Star(m => m.Address)
FROM
    @R.Tbl()
LEFT JOIN
    @R.Tbl(m => m.Address) ON @R.Col(m => m.Address.Id) = @R.Col(m => m.AddressId)

-- orders and addresses
SELECT
    @o.Star(),
    @o.Star(m => m.Billing)
FROM
    @o.Tbl()
INNER JOIN
    @o.Tbl(m => m.Billing) ON @o.Col(m => m.Billing.Id) = @o.Col(m => m.AddressId)

-- order lines and products
SELECT
    @ol.Star(),
    @ol.Star(m => m.Product)
FROM
    @ol.Tbl()
INNER JOIN
    @ol.Tbl(m => m.Product) ON @ol.Col(m => m.Product.Id) = @ol.Col(m => m.ProductId)

Creating a complete, new order with items and billing address by transferring identity values (SQL Server).

1
2
3
4
5
6
7
8
9
10
11
public class NewOrderModel
{
    public int CustomerId { get; set; }
    public IList<OrderItem> Items { get; set; }

    public class OrderItem
    {
        public int ProductId { get; set; }
        public int Units { get; set; }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- Commands/Orders/NewOrder.cssql
@model Jerrystore.Models.Orders.NewOrderModel

@project Customer c
@project CustomerAddress ca
@project Order o
@project OrderLine ol

-- copy address for order
INSERT INTO
    @ca.TblName() ( @ca.In().ColNames() )
SELECT
    @ca.In().Cols()
FROM
    @ca.Tbl()
INNER JOIN
    @c.Tbl() ON @c.Col(m => m.AddressId) = @ca.Col(m => m.Id)
WHERE
    @c.Col(m => m.Id) = @M.Par(m => m.CustomerId)

DECLARE @@BillingAddressId int = SCOPE_IDENTITY()

-- create order
INSERT INTO
    @o.TblName()
    (
        @o.ColName(m => m.CustomerId),
        @o.ColName(m => m.AddressId),
        @o.ColName(m => m.CreatedDate)
    )
    VALUES
    (
        @M.Par(m => m.CustomerId),
        @@BillingAddressId,
        GETDATE()
    )

DECLARE @@OrderId int = SCOPE_IDENTITY()

-- create order items
@foreach (var it in this.M.Vals(m => m.Items))

{
    INSERT INTO
        @ol.TblName()
        (
            @ol.ColName(m => m.OrderId),
            @ol.ColName(m => m.ProductId),
            @ol.ColName(m => m.Units)
        )
        VALUES
        (
            @@OrderId,
            @it.Par(m => m.ProductId),
            @it.Par(m => m.Units)
        )
}