Debugging

Following FSharp.Data.SqlClient specific techniques can be used to diagnose various issues.

ToTraceString

Call ToTraceString to get text representation of sql statement that will be executed in Sql Server

1: 
2: 
3: 
4: 
5: 
6: 
7: 
8: 
9:

let cmd = new SqlCommandProvider<"
    SELECT TOP(@topN) FirstName, LastName, SalesYTD 
    FROM Sales.vSalesPerson
    WHERE CountryRegionName = @regionName AND SalesYTD > @salesMoreThan 
    ORDER BY SalesYTD
    " , connectionString>(connectionString)

cmd.ToTraceString(topN = 3L, regionName = "United States", salesMoreThan = 1000000M) 
|> printfn "Sql: %s"

Direct access to underlying SqlCommand instance.

If you feel that getting your hands on underlying ADO.NET SqlCommand can help to address a problem that can be done. Expect to see a warning because this is not intended for public use and subject for change. Avoid tempering state of this SqlCommand instance otherwise all bets are off.

1: 
2: 
3: 
4:

let adonetCmd = (cmd :> ISqlCommand).Raw

[ for p in adonetCmd.Parameters -> p.ParameterName, p.SqlDbType ]
|> printfn "Inferred parameters: %A"

Result set runtime verification.

While enjoying all benefits of static types at design time one can easily end up in a situation when runtime Sql Server database schema is different from compile time. Up until now this resulted in confusion runtime exception: InvalidCastException("Specified cast is not valid.").

To improve diagnostics without hurting performance a new global singleton configuration object is introduced. To access Configuration type open up FSharp.Data.SqlClient namespace.

1: 
2:

open FSharp.Data.SqlClient
assert(Configuration.Current.ResultsetRuntimeVerification = false)

So far it has only one property ResultsetRuntimeVerification which set to false by default. Set it to true to see more descriptive error like:

InvalidOperationException(Expected column [Total] of type "System.Int32" at position 1 (0-based indexing) but received column [Now] of type "System.DateTime").

1:	Configuration.Current <- { Configuration.Current with ResultsetRuntimeVerification = true }

Other debugging/instrumentation tools to consider:

val cmd : ISqlCommand

Full name: Debugging.cmd

val connectionString : string

Full name: Debugging.connectionString

abstract member ISqlCommand.ToTraceString : parameters:(string * obj) [] -> string

val printfn : format:Printf.TextWriterFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.printfn

val adonetCmd : Data.SqlClient.SqlCommand

Full name: Debugging.adonetCmd

val p : Data.SqlClient.SqlParameter

property Data.SqlClient.SqlCommand.Parameters: Data.SqlClient.SqlParameterCollection

property Data.SqlClient.SqlParameter.ParameterName: string

property Data.SqlClient.SqlParameter.SqlDbType: Data.SqlDbType

namespace FSharp

namespace FSharp.Data

namespace FSharp.Data.SqlClient

Multiple items
namespace System.Configuration

--------------------
module Configuration

from FSharp.Data

--------------------
type Configuration =
{ResultsetRuntimeVerification: bool;}

Full name: FSharp.Data.SqlClient.Configuration

property Configuration.Current: Configuration

Configuration.ResultsetRuntimeVerification: bool