FSharp.Data.SqlClient


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.

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.

 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.spdescribeundeclared_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>>)

val echoOnly : obj

Full name: Faq.echoOnly
type SqlCommandProvider

Full name: FSharp.Data.SqlCommandProvider



<summary>Typed representation of a T-SQL statement to execute against a SQL Server database.</summary>
<param name='CommandText'>Transact-SQL statement to execute at the data source.</param>
<param name='ConnectionStringOrName'>String used to open a SQL Server database or the name of the connection string in the configuration file in the form of “name=&lt;connection string name&gt;”.</param>
<param name='ResultType'>A value that defines structure of result: Records, Tuples, DataTable, or SqlDataReader.</param>
<param name='SingleRow'>If set the query is expected to return a single row of the result set. See MSDN documentation for details on CommandBehavior.SingleRow.</param>
<param name='ConfigFile'>The name of the configuration file that’s used for connection strings at DESIGN-TIME. The default value is app.config or web.config.</param>
<param name='AllParametersOptional'>If set all parameters become optional. NULL input values must be handled inside T-SQL.</param>
<param name='ResolutionFolder'>A folder to be used to resolve relative file paths to *.sql script files at compile time. The default value is the folder that contains the project or script.</param>
<param name='DataDirectory'>The name of the data directory that replaces |DataDirectory| in connection strings. The default value is the project or script directory.</param>
val connectionString : string

Full name: Faq.connectionString
val echoOr42 : obj

Full name: Faq.echoOr42
union case Option.Some: Value: 'T -> Option<'T>
union case Option.None: Option<'T>
val echoOk : obj

Full name: Faq.echoOk
type ResultType =
  | Records = 0
  | Tuples = 1
  | DataTable = 2
  | DataReader = 3

Full name: FSharp.Data.ResultType
ResultType.Tuples: ResultType = 1
Multiple items
type LiteralAttribute =
  inherit Attribute
  new : unit -> LiteralAttribute

Full name: Microsoft.FSharp.Core.LiteralAttribute

--------------------
new : unit -> LiteralAttribute
val getDatesQuery : string

Full name: Faq.getDatesQuery
type GetDates = obj

Full name: Faq.GetDates
namespace System
namespace System.Data
namespace System.Data.SqlClient
type SqlDataReader =
  inherit DbDataReader
  member Close : unit -> unit
  member Depth : int
  member FieldCount : int
  member GetBoolean : i:int -> bool
  member GetByte : i:int -> byte
  member GetBytes : i:int * dataIndex:int64 * buffer:byte[] * bufferIndex:int * length:int -> int64
  member GetChar : i:int -> char
  member GetChars : i:int * dataIndex:int64 * buffer:char[] * bufferIndex:int * length:int -> int64
  member GetDataTypeName : i:int -> string
  member GetDateTime : i:int -> DateTime
  ...

Full name: System.Data.SqlClient.SqlDataReader
val this : SqlDataReader
member SqlDataReader.ToRecords : unit -> seq<'T>

Full name: Faq.ToRecords
Multiple items
val seq : sequence:seq<'T> -> seq<'T>

Full name: Microsoft.FSharp.Core.Operators.seq

--------------------
type seq<'T> = System.Collections.Generic.IEnumerable<'T>

Full name: Microsoft.FSharp.Collections.seq<_>
SqlDataReader.Read() : bool
val data : System.Collections.Generic.IDictionary<string,obj>
val dict : keyValuePairs:seq<'Key * 'Value> -> System.Collections.Generic.IDictionary<'Key,'Value> (requires equality)

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.dict
val i : int
property SqlDataReader.VisibleFieldCount: int
SqlDataReader.GetName(i: int) : string
SqlDataReader.GetValue(i: int) : obj
namespace FSharp
namespace FSharp.Data
namespace FSharp.Data.SqlClient
Multiple items
type DynamicRecord =
  inherit DynamicObject
  new : data:IDictionary<string,obj> -> DynamicRecord
  override Equals : other:obj -> bool
  override GetDynamicMemberNames : unit -> IEnumerable<string>
  override GetHashCode : unit -> int
  override ToString : unit -> string
  override TryGetMember : binder:GetMemberBinder * result:byref<obj> -> bool
  override TrySetMember : binder:SetMemberBinder * result:obj -> bool
  member private Data : IDictionary<string,obj>
  member Item : key:string -> obj with get

Full name: FSharp.Data.SqlClient.DynamicRecord

--------------------
new : data:System.Collections.Generic.IDictionary<string,obj> -> SqlClient.DynamicRecord
val box : value:'T -> obj

Full name: Microsoft.FSharp.Core.Operators.box
val unbox : value:obj -> 'T

Full name: Microsoft.FSharp.Core.Operators.unbox
val xs : obj []

Full name: Faq.xs
val conn : SqlConnection
Multiple items
type SqlConnection =
  inherit DbConnection
  new : unit -> SqlConnection + 1 overload
  member BeginTransaction : unit -> SqlTransaction + 3 overloads
  member ChangeDatabase : database:string -> unit
  member Close : unit -> unit
  member ConnectionString : string with get, set
  member ConnectionTimeout : int
  member CreateCommand : unit -> SqlCommand
  member DataSource : string
  member Database : string
  member EnlistDistributedTransaction : transaction:ITransaction -> unit
  ...

Full name: System.Data.SqlClient.SqlConnection

--------------------
SqlConnection() : unit
SqlConnection(connectionString: string) : unit
SqlConnection.Open() : unit
val cmd : SqlCommand
Multiple items
type SqlCommand =
  inherit DbCommand
  new : unit -> SqlCommand + 3 overloads
  member BeginExecuteNonQuery : unit -> IAsyncResult + 1 overload
  member BeginExecuteReader : unit -> IAsyncResult + 3 overloads
  member BeginExecuteXmlReader : unit -> IAsyncResult + 1 overload
  member Cancel : unit -> unit
  member Clone : unit -> SqlCommand
  member CommandText : string with get, set
  member CommandTimeout : int with get, set
  member CommandType : CommandType with get, set
  member Connection : SqlConnection with get, set
  ...

Full name: System.Data.SqlClient.SqlCommand

--------------------
SqlCommand() : unit
SqlCommand(cmdText: string) : unit
SqlCommand(cmdText: string, connection: SqlConnection) : unit
SqlCommand(cmdText: string, connection: SqlConnection, transaction: SqlTransaction) : unit
SqlCommand.ExecuteReader() : SqlDataReader
SqlCommand.ExecuteReader(behavior: System.Data.CommandBehavior) : SqlDataReader
module Seq

from Microsoft.FSharp.Collections
val toArray : source:seq<'T> -> 'T []

Full name: Microsoft.FSharp.Collections.Seq.toArray
Fork me on GitHub