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.

1: 
2: 
3: 

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<_> with default value None.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 

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 by omitting parameter
echoOr42.Execute() 

//Pass NULL explicitly
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.

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

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 

// 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.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 

[<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 = new System.Data.SqlClient.SqlCommand(getDatesQuery, conn)
    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>>)

• F# SqlClient
• Home page
• Get Library via NuGet
• Source Code on GitHub
• License
• Release Notes
• Documentation
• What's New
• Support for .NET Core
• Configuration and Input
• Output
• FAQ
• Data Modification
• Transactions
• Debugging
• Unit Testing
• Comparison
• Dynamic local db
• SqlEnumProvider
• Tools
• F# Formatting
• FAKE
• F# open-source project scaffolding
• Building great open-source libraries