Skip to content

Progressive F# Tutorials Teaser

August 28, 2013

A big F# community event is coming to NYC on September 18-19 – Progressive F# Tutorials. Jack Pappas and me are doing Code Quotations tutorial in advanced track.

I want to do my share of promoting this great event. Here is something that might get you interested.

Code Quotations is an advanced and powerful tool. It is used extensively in Type Provider development. I came up with a simple yet practical type provider example that shows applicability of code quotations. If time permits, I will also give a quick 101 on type providers development during the tutorial.
The type provider example is SqlCommandTypeProvider. It’s capable of generating the following:

  • Properties with appropriate types for each unbound input parameter
  • Type for individual row in result set (no result)
  • “Execute” method with proper signature that uses result set row type or unit if there is no result

Here are some usage examples based on AdventureWorks database. First let’s define connection string

[<Literal>]
let connectionString="Data Source=.\SQLExpress;Initial Catalog=AdventureWorks2012;Integrated Security=True"

Example of a query that calls SQL Function with one parameter of type int:

type QueryPersonInfo = SqlCommand<"SELECT * FROM dbo.ufnGetContactInformation(@PersonId)", connectionString>

let query = new QueryPersonInfo(PersonId = 2)

query.Execute()
|> Async.RunSynchronously
|> Seq.toArray
|> Array.iter (fun x ->
    printfn "Person info: Id - %i, FirstName - %s, LastName - %s, JobTitle - %s, BusinessEntityType - %s" x.PersonID x.FirstName x.LastName x.JobTitle x.BusinessEntityType
)

query.Execute type is

(unit -> Async<System.Collections.Generic.IEnumerable>)

Here is an example of stored procedure that updates employee personal info. Note that Execute method signature is

(unit -> Async)

because there is no result set.

type UpdateEmplInfoCommand = SqlCommand<"EXEC HumanResources.uspUpdateEmployeePersonalInfo @BusinessEntityID, @NationalIDNumber, @BirthDate, @MaritalStatus, @Gender", connectionString>
let cmd = new UpdateEmplInfoCommand(BusinessEntityID = 2, NationalIDNumber = "245797967", BirthDate = System.DateTime(1965, 09, 01), MaritalStatus = "S", Gender = "F")
cmd.Execute() |> Async.RunSynchronously

Command text can be any valid SQL script, not only functions or stored procedures. For example:

type GetServerTime = SqlCommand<"IF @IsUtc = CAST(1 AS BIT) SELECT GETUTCDATE() AS Now ELSE SELECT GETDATE() AS Now", connectionString>
let getSrvTime = new GetServerTime(IsUtc = true)
getSrvTime.Execute() |> Async.RunSynchronously |> Seq.map (fun x -> x.Now) |> Seq.exactlyOne |> printfn "%A"
getSrvTime.IsUtc <- false
getSrvTime.Execute() |> Async.RunSynchronously |> Seq.map (fun x -> x.Now) |> Seq.exactlyOne |> printfn "%A"

Note how IsUtc parameter value got updated and query re-executed again.

Full sample script source code is here.

It’s enough for a teaser. If you want to find out more about code quotations, design and implementations details of this Type Provider, I shall see you at Progressive F# Tutorials NYC Sept 18-19.

Besides Jack and me we got line-up of amazing speakers:

and many others.

img-7077

Advertisements

From → F#

One Comment

Trackbacks & Pingbacks

  1. F# Weekly #35 2013 | Sergey Tihon's Blog

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

%d bloggers like this: