Autogenerating columns: SQLProvider and WebSharper.

A real world example

This is a short follow up to my previous Modern CSS3 article
We want to see how to autogenerate the table columns from the DB to the Web in a few lines with type checks all the way through.

The DB

I’ve recently discovered the cool bang-bang operator for the left join in SQLProvider.

let context = Sql.GetDataContext(ConfigurationManager.ConnectionStrings.["AppDB"].ConnectionString)

let tradesQuery = 
    query {
        for t in context.OilPhysical.EndurTradeValid do  
        join n_del in (!!) context.OilPhysical.EndurNominationValid on
                 ((t.DealNumber,t.ParcelGroupId,t.ParcelId) 
                 = (n_del.DeliveryDealNumber,n_del.DeliveryParcelGroup,n_del.DeliveryParcelId))
        join n_rec in (!!) context.OilPhysical.EndurNominationValid on
                 ((t.DealNumber,t.ParcelGroupId,t.ParcelId) 
                 = (n_rec.ReceiptDealNumber,n_rec.ReceiptParcelGroup,n_rec.ReceiptParcelId))
        take 90
        select (t,n_del.CargoId, n_rec.CargoId)  
        }

let trades = 
    tradesQuery |> Seq.toArray 
    |> Array.map(fun (t,n_del,n_rec) -> 
        t.ColumnValues
        |> Seq.append ([("Del Cargo ID", n_del :> obj)] |> Seq.append [("Rec Cargo ID", n_rec :> obj)]  ))

The Web

While for desktop app you would need property bindings and plain old classes, the modern type provider types are a nice fit for the awesome WebSharper dsl.
The conclusion is that you can easily configure an agile web page with all the fancy features you need.

        if Array.isEmpty ServerModel.trades then
            p [] [text "No trades selected"]
        else
            Doc.Concat [
            div [] [client <@ Client.Inspector() @>];
            table [attr.``class`` "table"; attr.``data-`` "role" "table"; attr.``data-`` "horizontal-scroll" "true"; attr.id "demo-table"] [
                
                yield thead [] [
                    tr [] [
                        for (k,v) in ServerModel.trades.[0] do
                            match v with
                            |  %3A? int
                            |  %3A? decimal -> yield th [attr.``class`` "sortable-column"; attr.``data-`` "format" "number"] [text k]
                            | _ ->  yield th [attr.``class`` "sortable-column"] [text k]
                    ] ]
                yield tbody [] [
                for tup in ServerModel.trades do
                    yield tr [] [
                        for (k,v) in tup do
                            yield td [] [text <| string v] ]
                    ] 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s