FSharp.Data.SqlClient


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
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: 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
Fork me on GitHub