faq.fsx

(*** hide ***)
#r @"..\..\bin\net462\FSharp.Data.SqlClient.dll"
#r "System.Transactions"
open FSharp.Data

[<Literal>]
let connectionString = @"Data Source=.;Initial Catalog=AdventureWorks2012;Integrated Security=True;TrustServerCertificate=true"
    
(**
FAQ
===================
### 1. How do I specify default value for a parameter?

`SqlCommandProvider` generates parameters for all unbound T-SQL script variables. 
By definition those variables don't have default value. 
One workaround is to assume NULL as default value (see next item).
The other option is to have default value logic in application code.

If you feel strongly about having default parameter values defined in T-SQL 
consider wrapping it into stored procedure or function. Both accept default values for parameters. 
`SqlProgrammabilityProvider` supports this scenario by mapping it to .NET method parameters with default values.

### 2. How do I pass NULL as parameter value?

By default SqlCommandProvider treat all parameters as mandatory. 
*)

let echoOnly = 
    new SqlCommandProvider<"SELECT ISNULL(@x, 42)", connectionString, SingleRow = true>(connectionString)
echoOnly.Execute( x = (* must pass int value here *) 1) 

(**
There is no way to pass NULL as value for parameter `@x` although the query knows how to handle it.

To resolve the issue specify `AllParametersOptional = true` as static parameter to type provider. 
It makes all parameters of type `option<_>`.

<div class="well well-small" style="margin:0px 70px 0px 20px;">

**Note** This is not same as F# optional parameters** but mimics them closely.
Unfortunately type providers in general restricted to emit .NET 1.1 types only. 
No generics, no F# specific types/annotations which includes optional parameters.

</p></div>

*)

let echoOr42 = 
    new SqlCommandProvider<"
        SELECT ISNULL(@x, 42)
    ", connectionString, SingleRow = true, AllParametersOptional = true>(connectionString)

// Pass parameter value. Specifying Some constructor is mandatrory.
echoOr42.Execute( Some 1) 

// Pass NULL
echoOr42.Execute( x = None) 

(**
### 3. How do I do dynamic filter?  

Dynamic filters is when your T-SQL command accepts multiple input arguments and is supposed 
to determine which columns to filter, if at all, based on that input.

Following command allows to filter dynamically information on sales people based on country and minimal sales amount.
*)

(**
### 4. Why do I get compile time error " ... The undeclared parameter '@p1' is used more than once in the batch being analyzed."?  

This is a limitation of [sys.sp\_describe\_undeclared\_parameters](https://msdn.microsoft.com/en-us/library/ff878260.aspx).

To work around this limitation, you can declare another variable in your script:

*)

// this one would fail with above mentionned error:
//let echoFail = 
//    new SqlCommandProvider<"
//        select 'hello' + @name, 'your name is :' @name
//    ", connectionString, SingleRow = true>(connectionString)

// this one is ok as @name parameter is used only once in the statement:
let echoOk = 
    new SqlCommandProvider< @"
        declare @theName nvarchar(max)
        set @theName = @name
        select 'hello' + @theName, 'your name is :' + @theName
    ", connectionString, ResultType.Tuples, SingleRow = true>(connectionString)


(**
### 5. Exposing types generated by SqlCommandProvider  
*)

(**
### 6. Reusing typed record with a custom command

With a datareader obtained from a custom command you can still reuse the typed record definition through the DynamicRecord class.
*)

[<Literal>]
let getDatesQuery = "SELECT GETDATE() AS Now, GETUTCDATE() AS UtcNow"
type GetDates = SqlCommandProvider<getDatesQuery,  connectionString>

open System.Data.SqlClient
type SqlDataReader with
    member this.ToRecords<'T>() = 
        seq {
            while this.Read() do
                let data = 
                    dict [ 
                        for i = 0 to this.VisibleFieldCount - 1 do 
                            yield this.GetName(i), this.GetValue(i)
                    ]

                yield FSharp.Data.SqlClient.DynamicRecord(data) |> box |> unbox<'T>
        }
    
let xs = 
    use conn = new SqlConnection(connectionString)
    conn.Open()
    let cmd = conn.CreateCommand(CommandText = getDatesQuery)
    cmd.ExecuteReader().ToRecords<GetDates.Record>() 
    |> Seq.toArray

(**
### 7. Why do I sometimes get ExecuteSingle and AsyncExecuteSingle options with the SqlProgrammabilityProvider?

There are two cases when you would get those extra methods.

* Referencing a stored procedure that returns some sort of result set you will get these extra methods.  
* Referencing a Table Valued Function 

In both cases the methods return `Option<'T>` (or `Async<Option<'T>>`) rather than `Seq<'T>` (or `Async<Seq<'T>>`)
*)