<|start_filename|>src/SqlClient.DesignTime/Scripts/XE.fsx<|end_filename|>
//#I @"C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Extensions\Application"
#I @"C:\Program Files\Microsoft SQL Server\120\Shared"

#r "System.Transactions"
#r "Microsoft.SqlServer.XE.Core.dll"
#r "Microsoft.SqlServer.XEvent.Linq.dll"

open Microsoft.SqlServer.XEvent.Linq
open System.Data.SqlClient

let connection = "Data Source=.;Initial Catalog=master;Integrated Security=True"

let targetDatabase = "AdventureWorks2014"
let xeSession = "XE_Alter"

do 
    use conn = new SqlConnection(connection)
    conn.Open()
    conn.ChangeDatabase(targetDatabase)
    let createSession =
        sprintf "
            IF NOT EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='%s')
            BEGIN
                CREATE EVENT SESSION [%s] 
                ON SERVER
                    ADD EVENT sqlserver.object_created
                    (
                        ACTION (sqlserver.database_name, sqlserver.sql_text)
                        WHERE  (sqlserver.database_name = '%s')
                    ),
                    ADD EVENT sqlserver.object_deleted
                    (
                        ACTION (sqlserver.database_name, sqlserver.sql_text)
                        WHERE  (sqlserver.database_name = '%s')
                    ),
		            ADD EVENT sqlserver.object_altered
                    (
                        ACTION (sqlserver.database_name, sqlserver.sql_text)
                        WHERE  (sqlserver.database_name = '%s')
                    ),

                    --trash events to make buffer to flush
                    ADD EVENT sqlos.async_io_completed,
                    ADD EVENT sqlserver.sql_batch_completed,
                    ADD EVENT sqlserver.sql_batch_starting,
                    ADD EVENT sqlserver.sql_statement_completed,
                    ADD EVENT sqlserver.sql_statement_recompile,
                    ADD EVENT sqlserver.sql_statement_starting,
                    ADD EVENT sqlserver.sql_transaction,
                    ADD EVENT sqlserver.sql_transaction_commit_single_phase
            END

            IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='%s')
            BEGIN
                ALTER EVENT SESSION [%s] ON SERVER STATE = START
            END
        " xeSession xeSession targetDatabase targetDatabase targetDatabase xeSession xeSession
    use cmd = new System.Data.SqlClient.SqlCommand(createSession, conn)
    cmd.ExecuteNonQuery() |> ignore

do 
    use events = new QueryableXEventData(connection, xeSession, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache)

    for x in events do
        //printfn "Event name: %s" x.Name
        if x.Name = "object_altered" || x.Name = "object_created" || x.Name = "object_deleted"
        then
            let contains, ddl_phase = x.Fields.TryGetValue("ddl_phase") 
            if contains && string ddl_phase.Value = "Commit" 
            then 
                printfn "ddl_phase type: %A" ddl_phase.Type
        
                let fs = x.Fields
                let ac = x.Actions

    //            fs |> Seq.cast<PublishedEventField> |> Seq.map (fun e -> e.Name) |> String.concat ";" |> printfn "Fields: %s"
    //            x.Actions |> Seq.cast<PublishedAction> |> Seq.map (fun e -> e.Name) |> String.concat ";" |> printfn "Actions: %s"

                printfn "\nEvent %s.\nDDL Phase: %O.\nObject: id-%O; name-%O.\nDatabase:  id-%O; name-%O.\nSql text: %A" x.Name fs.["ddl_phase"].Value fs.["object_id"].Value fs.["object_name"].Value fs.["database_id"].Value ac.["database_name"].Value ac.["sql_text"].Value
                //printfn "\nEvent %s.\nDDL Phase: %O.\nObject: id-%O; name-%O.\nDatabase:  id-%O; name-%O." x.Name fs.["ddl_phase"].Value fs.["object_id"].Value fs.["object_name"].Value fs.["database_id"].Value fs.["database_name"].Value 


<|start_filename|>tests/SqlClient.Tests/ConnectionStrings.fs<|end_filename|>
﻿module FSharp.Data.ConnectionStrings 

[<Literal>]
let server = @"."

[<Literal>]
let AdventureWorksLiteral = @"Data Source=" + server + ";Initial Catalog=AdventureWorks2012;Integrated Security=True"
[<Literal>]
let AdventureWorksDesignOnly = @"name=AdventureWorksDesignOnly"
[<Literal>]
let AdventureWorksLiteralMultipleActiveResults = AdventureWorksLiteral + ";MultipleActiveResultSets=True"
[<Literal>]
let AdventureWorksNamed = @"name=AdventureWorks"
[<Literal>]
let MasterDb = @"name=MasterDb"
[<Literal>]
let LocalHost = @"Data Source=" + server + ";Integrated Security=True"
[<Literal>]
let AdventureWorksAzureRedGate = @"Data Source=mhknbn2kdz.database.windows.net;Initial Catalog=AdventureWorks2012;User ID=sqlfamily;Pwd=<PASSWORD>"

open System.Configuration
let AdventureWorks = ConfigurationManager.ConnectionStrings.["AdventureWorks"].ConnectionString


<|start_filename|>tests/SqlClient.Tests/Assert.fs<|end_filename|>
﻿[<AutoOpen>]
module AssertExtensions

open Xunit

[<assembly: CollectionBehavior(DisableTestParallelization = true)>]
do()

type Assert with 
    static member IsNone (value: _ option) = 
        Assert.True value.IsNone


<|start_filename|>tests/SqlClient.Tests/SqlClient.DesignTime.Tests.dll.html<|end_filename|>
﻿<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html>
  <head>
    <META http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>
                    xUnit.net Test Results - </title>
    <style type="text/css">
                    body { font-family: Calibri, Verdana, Arial, sans-serif; background-color: White; color: Black; }
                    h2,h3,h4,h5 { margin: 0; padding: 0; }
                    h3 { font-weight: normal; }
                    h5 { font-weight: normal; font-style: italic; margin-bottom: 0.75em; }
                    pre { font-family: Consolas; font-size: 85%; margin: 0 0 0 1em; padding: 0; }
                    .divided { border-top: solid 1px #f0f5fa; padding-top: 0.5em; }
                    .row, .altrow { padding: 0.1em 0.3em; }
                    .row { background-color: #f0f5fa; }
                    .altrow { background-color: #e1ebf4; }
                    .success, .failure, .skipped { font-family: Arial Unicode MS; font-weight: normal; float: left; width: 1em; display: block; }
                    .success { color: #0c0; }
                    .failure { color: #c00; }
                    .skipped { color: #cc0; }
                    .timing { float: right; }
                    .indent { margin: 0.25em 0 0.5em 2em; }
                    .clickable { cursor: pointer; }
                    .testcount { font-size: 85%; }
                </style><script language="javascript">
                    function ToggleClass(id) {
                        var elem = document.getElementById(id);
                        if (elem.style.display == "none") {
                            elem.style.display = "block";
                        }
                        else {
                            elem.style.display = "none";
                        }
                    }
                </script></head>
  <body>
    <h3 class="divided"><b>Assemblies Run</b></h3>
    <div>c:\code\FSharp.Data.SqlClient\src\SqlClient.Tests\SqlClient.DesignTime.Tests\bin\Release\net461\SqlClient.DesignTime.Tests.dll</div>
    <h3 class="divided"><b>Summary</b></h3>
    <div>
                    Tests run: <a href="#all"><b>3</b></a>  
                    Failures: <a href="#failures"><b>0</b></a>,
                    Skipped: <a href="#skipped"><b>0</b></a>,
                    Run time: <b>0.439s</b></div><br><h2><a name="all"></a>All tests</h2>
    <h5>Click test class name to expand/collapse test details</h5>
    <h3><span class="timing">0.439s</span><span class="clickable" onclick="ToggleClass('classID0EO')" ondblclick="ToggleClass('classID0EO')"><span class="success">✔</span>
                 DesignTimeConnectionStringTests
                 <span class="testcount">(3 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EO">
      <div class="row"><span class="timing">0.001s</span><span class="success">✔</span>
             DesignTimeConnectionStringTests.From config file<br clear="all"></div>
      <div class="altrow"><span class="timing">0.422s</span><span class="success">✔</span>
             DesignTimeConnectionStringTests.RuntimeConfig<br clear="all"></div>
      <div class="row"><span class="timing">0.016s</span><span class="success">✔</span>
             DesignTimeConnectionStringTests.Wrong config file name<br clear="all"></div>
    </div>
  </body>
</html>

<|start_filename|>src/SqlClient.DesignTime/SingleRootTypeProvider.fs<|end_filename|>
﻿namespace ProviderImplementation.ProvidedTypes

open FSharp.Data.SqlClient
open Microsoft.FSharp.Core.CompilerServices
open System.Reflection
open System

[<AbstractClass>]
[<CompilerMessageAttribute("This API supports the FSharp.Data.SqlClient infrastructure and is not intended to be used directly from your code.", 101, IsHidden = true)>]
type SingleRootTypeProvider(config: TypeProviderConfig, providerName, parameters, ?isErased) as this = 
    inherit TypeProviderForNamespaces (config, assemblyReplacementMap=[("FSharp.Data.SqlClient.DesignTime", "FSharp.Data.SqlClient")], addDefaultProbingLocation=true)

    let cache = new Cache<ProvidedTypeDefinition>()
    do 
        let isErased = defaultArg isErased true
        let nameSpace = this.GetType().Namespace
        let assembly = Assembly.GetExecutingAssembly()

        let providerType = ProvidedTypeDefinition(assembly, nameSpace, providerName, Some typeof<obj>, hideObjectMethods = true, isErased = isErased)

        providerType.DefineStaticParameters(
            parameters = parameters,             
            instantiationFunction = fun typeName args ->
                match cache.TryGetValue(typeName) with
                | true, cachedType -> cachedType.Value
                | false, _ -> 
                    let typ, monitors = this.CreateRootType(assembly, nameSpace, typeName, args)
                    monitors
                    |> Seq.iter(fun m ->
                        match m with
                        | :? System.Runtime.Caching.ChangeMonitor as monitor ->
                            monitor.NotifyOnChanged(fun _ -> 
                                cache.Remove(typeName)
                                this.Invalidate()
                                monitor.Dispose()
                            )
                        | _ -> ()
                    )
                    cache.GetOrAdd(typeName, typ)
        )

        this.AddNamespace( nameSpace, [ providerType ])

    abstract CreateRootType: assemblyName: Assembly * nameSpace: string * typeName: string  * args: obj[] -> Lazy<ProvidedTypeDefinition> * obj[] // ChangeMonitor[] underneath but there is a problem https://github.com/fsprojects/FSharp.Data.SqlClient/issues/234#issuecomment-240694390


<|start_filename|>docs/content/setup.fsx<|end_filename|>
﻿#nowarn "211"
#r @"..\..\bin\net40\FSharp.Data.SqlClient.dll"
#r "System.Transactions"

open FSharp.Data

[<Literal>]
let connectionString = @"Data Source=.;Initial Catalog=AdventureWorks2012;Integrated Security=True"


<|start_filename|>src/SqlClient.DesignTime/SingleFileChangeMonitor.fs<|end_filename|>
﻿namespace FSharp.Data.SqlClient

open System
open System.IO
open System.Runtime.Caching

[<CompilerMessageAttribute("This API supports the FSharp.Data.SqlClient infrastructure and is not intended to be used directly from your code.", 101, IsHidden = true)>]
type internal SingleFileChangeMonitor(path) as this = 
    inherit ChangeMonitor()

    let file = new FileInfo(path)
    let watcher = new FileSystemWatcher( Path.GetDirectoryName(path) )

    do
        let dispose = ref true
        try
            watcher.NotifyFilter <- NotifyFilters.LastWrite ||| NotifyFilters.FileName
            watcher.Changed.Add <| fun args -> this.TriggerOnFileChange(args.Name)
            watcher.Deleted.Add <| fun args -> this.TriggerOnFileChange(args.Name)
            watcher.Renamed.Add <| fun args -> this.TriggerOnFileChange(args.OldName)
            watcher.Error.Add <| fun _ -> this.TriggerOnChange()
            watcher.EnableRaisingEvents <- true
            dispose := false
        finally 
            base.InitializationComplete()
            if !dispose 
            then 
                base.Dispose()

    member private __.TriggerOnChange() = base.OnChanged(state = null)
    member private __.TriggerOnFileChange(fileName) = 
        if String.Compare(file.Name, fileName, StringComparison.OrdinalIgnoreCase) = 0  
        then 
            this.TriggerOnChange()

    override __.UniqueId = path + string file.LastWriteTimeUtc.Ticks + string file.Length;
    override __.Dispose( disposing) = if disposing then watcher.Dispose()



<|start_filename|>tests/SqlClient.Tests/Scripts/CreateCommand.fsx<|end_filename|>
#r "../../../bin/Fsharp.Data.SqlClient.dll"
#r @"Microsoft.SqlServer.Types.dll"
open System
open System.Data
open FSharp.Data
open System.Data.SqlClient

[<Literal>] 
let connectionString = "Data Source=.;Initial Catalog=AdventureWorks2012;Integrated Security=True"

type DB = SqlProgrammabilityProvider<connectionString>
type dbo = DB.dbo

//let x = DB.GetValue(42.)
//let xx = x * 12.<DB.bbl>

let connection = new SqlConnection(connectionString)
connection.Open()

let getTopSalespeople = 
    DB.CreateCommand<"
        SELECT TOP(@topN) FirstName, LastName, SalesYTD 
        FROM Sales.vSalesPerson
        WHERE CountryRegionName = @regionName AND SalesYTD > @salesMoreThan 
        ORDER BY SalesYTD
    ">(commandTimeout = 60)

getTopSalespeople.Execute(topN = 3L, regionName = "United States", salesMoreThan = 1000000M) |> printfn "%A"

let get42AndTime = DB.CreateCommand<"SELECT 42, GETDATE()", ResultType.Tuples, SingleRow = true>(SqlClient.Connection.Instance connection)
get42AndTime.AsyncExecute() |> Async.RunSynchronously |> printfn "%A"

let myPerson = DB.CreateCommand<"exec Person.myProc @x", ResultType.Tuples, SingleRow = true, TypeName = "MyProc">()
type MyTableType = DB.Person.``User-Defined Table Types``.MyTableType
myPerson.Execute [ MyTableType(myId = 1, myName = Some "monkey"); MyTableType(myId = 2, myName = Some "donkey") ] 

open Microsoft.SqlServer.Types
open System.Data.SqlTypes

let getEmployeeByLevel = DB.CreateCommand<"
    SELECT OrganizationNode 
    FROM HumanResources.Employee 
    WHERE OrganizationNode = @OrganizationNode", SingleRow = true>()

let p = SqlHierarchyId.Parse(SqlString("/1/1/"))

getEmployeeByLevel.Execute( p)|> printfn "%A"


<|start_filename|>tests/SqlClient.Tests/SqlClient.Tests.dll.html<|end_filename|>
﻿<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html>
  <head>
    <META http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>
                    xUnit.net Test Results - </title>
    <style type="text/css">
                    body { font-family: Calibri, Verdana, Arial, sans-serif; background-color: White; color: Black; }
                    h2,h3,h4,h5 { margin: 0; padding: 0; }
                    h3 { font-weight: normal; }
                    h5 { font-weight: normal; font-style: italic; margin-bottom: 0.75em; }
                    pre { font-family: Consolas; font-size: 85%; margin: 0 0 0 1em; padding: 0; }
                    .divided { border-top: solid 1px #f0f5fa; padding-top: 0.5em; }
                    .row, .altrow { padding: 0.1em 0.3em; }
                    .row { background-color: #f0f5fa; }
                    .altrow { background-color: #e1ebf4; }
                    .success, .failure, .skipped { font-family: Arial Unicode MS; font-weight: normal; float: left; width: 1em; display: block; }
                    .success { color: #0c0; }
                    .failure { color: #c00; }
                    .skipped { color: #cc0; }
                    .timing { float: right; }
                    .indent { margin: 0.25em 0 0.5em 2em; }
                    .clickable { cursor: pointer; }
                    .testcount { font-size: 85%; }
                </style><script language="javascript">
                    function ToggleClass(id) {
                        var elem = document.getElementById(id);
                        if (elem.style.display == "none") {
                            elem.style.display = "block";
                        }
                        else {
                            elem.style.display = "none";
                        }
                    }
                </script></head>
  <body>
    <h3 class="divided"><b>Assemblies Run</b></h3>
    <div>c:\code\FSharp.Data.SqlClient\src\SqlClient.Tests\bin\Release\net461\SqlClient.Tests.dll</div>
    <h3 class="divided"><b>Summary</b></h3>
    <div>
                    Tests run: <a href="#all"><b>144</b></a>  
                    Failures: <a href="#failures"><b>0</b></a>,
                    Skipped: <a href="#skipped"><b>4</b></a>,
                    Run time: <b>2.944s</b></div><br><h2><a name="skipped"></a>Skipped tests</h2>
    <div class="row"><span class="timing">Skipped</span><span class="skipped">❢</span>
             FSharp.Data.TVPTests.InputIsEnumeratedExactlyOnce : Flucky<br clear="all"></div>
    <div class="altrow"><span class="timing">Skipped</span><span class="skipped">❢</span>
             FSharp.Data.TVPTests.UsingTVPInQuery : Fails at runtime :(<br clear="all"></div>
    <div class="row"><span class="timing">Skipped</span><span class="skipped">❢</span>
             FSharp.Data.TypeProviderTest.CommandTimeout : Don't execute for usual runs. Too slow.<br clear="all"></div>
    <div class="altrow"><span class="timing">Skipped</span><span class="skipped">❢</span>
             FSharp.Data.TypeProviderTest.ConcurrentReaders : Thread safe execution is not supported yet<br clear="all"></div><br><h2><a name="all"></a>All tests</h2>
    <h5>Click test class name to expand/collapse test details</h5>
    <h3><span class="timing">0.267s</span><span class="clickable" onclick="ToggleClass('classID0EDH')" ondblclick="ToggleClass('classID0EDH')"><span class="success">✔</span>
                 FSharp.Data.CreateCommandTest
                 <span class="testcount">(13 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EDH">
      <div class="row"><span class="timing">0.026s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.columnsShouldNotBeNull2<br clear="all"></div>
      <div class="altrow"><span class="timing">0.006s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.ConditionalQuery<br clear="all"></div>
      <div class="row"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.connection is properly disposed<br clear="all"></div>
      <div class="altrow"><span class="timing">0.009s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.CreareDynamicRecords<br clear="all"></div>
      <div class="row"><span class="timing">0.018s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.datatableAndDataReader<br clear="all"></div>
      <div class="altrow"><span class="timing">0.003s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.Fallback to metadata retrieval through FMTONLY<br clear="all"></div>
      <div class="row"><span class="timing">0.038s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.getSequenceWithParams<br clear="all"></div>
      <div class="altrow"><span class="timing">0.009s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.getSingleRowNoParams<br clear="all"></div>
      <div class="row"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.optionalParams<br clear="all"></div>
      <div class="altrow"><span class="timing">0.021s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.resultSetMapping<br clear="all"></div>
      <div class="row"><span class="timing">0.004s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.Runtime column names<br clear="all"></div>
      <div class="altrow"><span class="timing">0.066s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.toTraceString<br clear="all"></div>
      <div class="row"><span class="timing">0.053s</span><span class="success">✔</span>
             FSharp.Data.CreateCommandTest.udttAndTuplesOutput<br clear="all"></div>
    </div>
    <h3><span class="timing">1.187s</span><span class="clickable" onclick="ToggleClass('classID0EO')" ondblclick="ToggleClass('classID0EO')"><span class="success">✔</span>
                 FSharp.Data.DataTablesTests
                 <span class="testcount">(24 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EO">
      <div class="row"><span class="timing">0.021s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.AddRowAndBulkCopy<br clear="all"></div>
      <div class="altrow"><span class="timing">0.710s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.AddRowAndBulkCopyWithConnOverride<br clear="all"></div>
      <div class="row"><span class="timing">0.004s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.can build arbitrary data table from inline sql<br clear="all"></div>
      <div class="altrow"><span class="timing">0.003s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.can build arbitrary table and columns exist<br clear="all"></div>
      <div class="row"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.Can use DataColumnCollection<br clear="all"></div>
      <div class="altrow"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.Can use datacolumns and access like a normal DataRow<br clear="all"></div>
      <div class="row"><span class="timing">0.009s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.Can use datacolumns GetValue and SetValue methods<br clear="all"></div>
      <div class="altrow"><span class="timing">0.005s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.Can use datacolumns on SqlProgrammabilityProvider<br clear="all"></div>
      <div class="row"><span class="timing">0.009s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.Can use Table property on SqlCommandProvider's rows<br clear="all"></div>
      <div class="altrow"><span class="timing">0.003s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.Can use Table property on SqlProgrammabilityProvider's rows<br clear="all"></div>
      <div class="row"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.Can use Table type when ResultType = ResultType.DataTable<br clear="all"></div>
      <div class="altrow"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.can't update on arbitrarilly constructed table<br clear="all"></div>
      <div class="row"><span class="timing">0.034s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.ColumnWithSpaceInNameAndDefaultValue<br clear="all"></div>
      <div class="altrow"><span class="timing">0.037s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.ContinueUpdateOnErrorFalse<br clear="all"></div>
      <div class="row"><span class="timing">0.008s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.ContinueUpdateOnErrorTrue<br clear="all"></div>
      <div class="altrow"><span class="timing">0.014s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.DEFAULTConstraint<br clear="all"></div>
      <div class="row"><span class="timing">0.029s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.DEFAULTConstraintInsertViaSqlDataAdapter<br clear="all"></div>
      <div class="altrow"><span class="timing">0.009s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.NewRowAndBulkCopy<br clear="all"></div>
      <div class="row"><span class="timing">0.078s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.NewRowAndBulkCopyWithTrsansactionScope<br clear="all"></div>
      <div class="altrow"><span class="timing">0.078s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.NullableDateTimeColumn<br clear="all"></div>
      <div class="row"><span class="timing">0.074s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.SqlCommandTableInsert<br clear="all"></div>
      <div class="altrow"><span class="timing">0.011s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.SqlCommandTableUpdate<br clear="all"></div>
      <div class="row"><span class="timing">0.003s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.TableTypeTag<br clear="all"></div>
      <div class="altrow"><span class="timing">0.020s</span><span class="success">✔</span>
             FSharp.Data.DataTablesTests.UpdatesPlusAmbientTransaction<br clear="all"></div>
    </div>
    <h3><span class="timing">0.374s</span><span class="clickable" onclick="ToggleClass('classID0EPEAC')" ondblclick="ToggleClass('classID0EPEAC')"><span class="success">✔</span>
                 FSharp.Data.DynamicRecordTests
                 <span class="testcount">(11 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EPEAC">
      <div class="row"><span class="timing">0.000s</span><span class="success">✔</span>
             FSharp.Data.DynamicRecordTests.Equal<br clear="all"></div>
      <div class="altrow"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.DynamicRecordTests.GetHashCode is same when equal<br clear="all"></div>
      <div class="row"><span class="timing">0.362s</span><span class="success">✔</span>
             FSharp.Data.DynamicRecordTests.JSON serialize<br clear="all"></div>
      <div class="altrow"><span class="timing">0.000s</span><span class="success">✔</span>
             FSharp.Data.DynamicRecordTests.Not equal of different size<br clear="all"></div>
      <div class="row"><span class="timing">0.000s</span><span class="success">✔</span>
             FSharp.Data.DynamicRecordTests.Not equal of different type<br clear="all"></div>
      <div class="altrow"><span class="timing">0.000s</span><span class="success">✔</span>
             FSharp.Data.DynamicRecordTests.Not equal with different keys<br clear="all"></div>
      <div class="row"><span class="timing">0.000s</span><span class="success">✔</span>
             FSharp.Data.DynamicRecordTests.Not equal with different values<br clear="all"></div>
      <div class="altrow"><span class="timing">0.001s</span><span class="success">✔</span>
             FSharp.Data.DynamicRecordTests.ToString<br clear="all"></div>
      <div class="row"><span class="timing">0.002s</span><span class="success">✔</span>
             FSharp.Data.DynamicRecordTests.ToString : Nulls to Nones<br clear="all"></div>
      <div class="altrow"><span class="timing">0.000s</span><span class="success">✔</span>
             FSharp.Data.DynamicRecordTests.TryGetMember fails<br clear="all"></div>
      <div class="row"><span class="timing">0.002s</span><span class="success">✔</span>
             FSharp.Data.DynamicRecordTests.TryGetMember succeeds<br clear="all"></div>
    </div>
    <h3><span class="timing">0.038s</span><span class="clickable" onclick="ToggleClass('classID0ESCAC')" ondblclick="ToggleClass('classID0ESCAC')"><span class="success">✔</span>
                 FSharp.Data.EnumTests
                 <span class="testcount">(9 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0ESCAC">
      <div class="row"><span class="timing">0.010s</span><span class="success">✔</span>
             FSharp.Data.EnumTests.ConvertUsdToGbp<br clear="all"></div>
      <div class="altrow"><span class="timing">0.001s</span><span class="success">✔</span>
             FSharp.Data.EnumTests.Enums<br clear="all"></div>
      <div class="row"><span class="timing">0.001s</span><span class="success">✔</span>
             FSharp.Data.EnumTests.MoreThan2ColumnReturnsCorrectTuples<br clear="all"></div>
      <div class="altrow"><span class="timing">0.001s</span><span class="success">✔</span>
             FSharp.Data.EnumTests.Name<br clear="all"></div>
      <div class="row"><span class="timing">0.003s</span><span class="success">✔</span>
             FSharp.Data.EnumTests.parse<br clear="all"></div>
      <div class="altrow"><span class="timing">0.003s</span><span class="success">✔</span>
             FSharp.Data.EnumTests.PatternMatchingOn<br clear="all"></div>
      <div class="row"><span class="timing">0.014s</span><span class="success">✔</span>
             FSharp.Data.EnumTests.ProductWeightAndSizeUnitsOfMeasure<br clear="all"></div>
      <div class="altrow"><span class="timing">0.005s</span><span class="success">✔</span>
             FSharp.Data.EnumTests.SingleColumn<br clear="all"></div>
      <div class="row"><span class="timing">0.000s</span><span class="success">✔</span>
             FSharp.Data.EnumTests.tinyIntMapping<br clear="all"></div>
    </div>
    <h3><span class="timing">0.020s</span><span class="clickable" onclick="ToggleClass('classID0EZFAE')" ondblclick="ToggleClass('classID0EZFAE')"><span class="success">✔</span>
                 FSharp.Data.OptionalParamsTests
                 <span class="testcount">(4 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EZFAE">
      <div class="row"><span class="timing">0.005s</span><span class="success">✔</span>
             FSharp.Data.OptionalParamsTests.BothOptinalParamsSupplied<br clear="all"></div>
      <div class="altrow"><span class="timing">0.004s</span><span class="success">✔</span>
             FSharp.Data.OptionalParamsTests.NullableStringInputMandatoryParameter<br clear="all"></div>
      <div class="row"><span class="timing">0.005s</span><span class="success">✔</span>
             FSharp.Data.OptionalParamsTests.NullableStringInputParameter<br clear="all"></div>
      <div class="altrow"><span class="timing">0.006s</span><span class="success">✔</span>
             FSharp.Data.OptionalParamsTests.SkipYParam<br clear="all"></div>
    </div>
    <h3><span class="timing">0.343s</span><span class="clickable" onclick="ToggleClass('classID0EXHAC')" ondblclick="ToggleClass('classID0EXHAC')"><span class="success">✔</span>
                 FSharp.Data.ProgrammabilityTest
                 <span class="testcount">(18 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EXHAC">
      <div class="row"><span class="timing">0.008s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.ConnectionObject<br clear="all"></div>
      <div class="altrow"><span class="timing">0.013s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.FunctionWithParamOfValueTypeWithNullDefault<br clear="all"></div>
      <div class="row"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.honors result type parameter: datatable<br clear="all"></div>
      <div class="altrow"><span class="timing">0.016s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.localTransactionCreateAndSingleton<br clear="all"></div>
      <div class="row"><span class="timing">0.012s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.localTransactionCtor<br clear="all"></div>
      <div class="altrow"><span class="timing">0.114s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.no bad error message when db is changed before table update <br clear="all"></div>
      <div class="row"><span class="timing">0.006s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.OutParam<br clear="all"></div>
      <div class="altrow"><span class="timing">0.026s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.PassingImageAsParamDoesntGetCut<br clear="all"></div>
      <div class="row"><span class="timing">0.008s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.ResultSetAndOutParam<br clear="all"></div>
      <div class="altrow"><span class="timing">0.000s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.routineCommandTypeTag<br clear="all"></div>
      <div class="row"><span class="timing">0.040s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.ScalarValuedFunction<br clear="all"></div>
      <div class="altrow"><span class="timing">0.009s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.SpAndTVPinDiffSchema<br clear="all"></div>
      <div class="row"><span class="timing">0.015s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.SpWithParamOfRefTypeWithNullDefault<br clear="all"></div>
      <div class="altrow"><span class="timing">0.010s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.SpWithParamOfTvpWithNullableColumns<br clear="all"></div>
      <div class="row"><span class="timing">0.008s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.SpWithParamOfTvpWithNullableColumns2<br clear="all"></div>
      <div class="altrow"><span class="timing">0.036s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.StaticCreateMethod<br clear="all"></div>
      <div class="row"><span class="timing">0.008s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.TableValuedFunction<br clear="all"></div>
      <div class="altrow"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.ProgrammabilityTest.update table should work when names connection string from design time is not available at the runtime but is substituded with a literal<br clear="all"></div>
    </div>
    <h3><span class="timing">0.006s</span><span class="clickable" onclick="ToggleClass('classID0E3NAC')" ondblclick="ToggleClass('classID0E3NAC')"><span class="success">✔</span>
                 FSharp.Data.ResultTypeTests
                 <span class="testcount">(1 test)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0E3NAC">
      <div class="row"><span class="timing">0.006s</span><span class="success">✔</span>
             FSharp.Data.ResultTypeTests.ResultTypeReader<br clear="all"></div>
    </div>
    <h3><span class="timing">0.034s</span><span class="clickable" onclick="ToggleClass('classID0EYGAC')" ondblclick="ToggleClass('classID0EYGAC')"><span class="success">✔</span>
                 FSharp.Data.SpReturnValueTests
                 <span class="testcount">(4 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EYGAC">
      <div class="row"><span class="timing">0.010s</span><span class="success">✔</span>
             FSharp.Data.SpReturnValueTests.AddRef<br clear="all"></div>
      <div class="altrow"><span class="timing">0.005s</span><span class="success">✔</span>
             FSharp.Data.SpReturnValueTests.BinaryOutput<br clear="all"></div>
      <div class="row"><span class="timing">0.014s</span><span class="success">✔</span>
             FSharp.Data.SpReturnValueTests.ResultSetAndOutParam<br clear="all"></div>
      <div class="altrow"><span class="timing">0.005s</span><span class="success">✔</span>
             FSharp.Data.SpReturnValueTests.TimestampOutput<br clear="all"></div>
    </div>
    <h3><span class="timing">0.008s</span><span class="clickable" onclick="ToggleClass('classID0EYGAE')" ondblclick="ToggleClass('classID0EYGAE')"><span class="success">✔</span>
                 FSharp.Data.SqlClient.ConfigurationTests
                 <span class="testcount">(1 test)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EYGAE">
      <div class="row"><span class="timing">0.008s</span><span class="success">✔</span>
             FSharp.Data.SqlClient.ConfigurationTests.SqlFiles<br clear="all"></div>
    </div>
    <h3><span class="timing">0.127s</span><span class="clickable" onclick="ToggleClass('classID0EZBAC')" ondblclick="ToggleClass('classID0EZBAC')"><span class="success">✔</span>
                 FSharp.Data.SynonymsTests
                 <span class="testcount">(3 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EZBAC">
      <div class="row"><span class="timing">0.084s</span><span class="success">✔</span>
             FSharp.Data.SynonymsTests.SPSynonym<br clear="all"></div>
      <div class="altrow"><span class="timing">0.005s</span><span class="success">✔</span>
             FSharp.Data.SynonymsTests.TableSynonym<br clear="all"></div>
      <div class="row"><span class="timing">0.038s</span><span class="success">✔</span>
             FSharp.Data.SynonymsTests.TVFSynonym<br clear="all"></div>
    </div>
    <h3><span class="timing">0.049s</span><span class="clickable" onclick="ToggleClass('classID0EFF')" ondblclick="ToggleClass('classID0EFF')"><span class="success">✔</span>
                 FSharp.Data.TempTableTests
                 <span class="testcount">(3 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EFF">
      <div class="row"><span class="timing">0.008s</span><span class="success">✔</span>
             FSharp.Data.TempTableTests.queryWithHash<br clear="all"></div>
      <div class="altrow"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.TempTableTests.queryWithHashAndTempTable<br clear="all"></div>
      <div class="row"><span class="timing">0.034s</span><span class="success">✔</span>
             FSharp.Data.TempTableTests.usingTempTable<br clear="all"></div>
    </div>
    <h3><span class="timing">0.028s</span><span class="clickable" onclick="ToggleClass('classID0EZAAE')" ondblclick="ToggleClass('classID0EZAAE')"><span class="success">✔</span>
                 FSharp.Data.The undeclared parameter 'X' is used more than once in the batch being analyzed
                 <span class="testcount">(4 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EZAAE">
      <div class="row"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.The undeclared parameter 'X' is used more than once in the batch being analyzed.Basic<br clear="all"></div>
      <div class="altrow"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.The undeclared parameter 'X' is used more than once in the batch being analyzed.DynamicFiltering<br clear="all"></div>
      <div class="row"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.The undeclared parameter 'X' is used more than once in the batch being analyzed.WithBoundDeclaration<br clear="all"></div>
      <div class="altrow"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.The undeclared parameter 'X' is used more than once in the batch being analyzed.WithUnboundDeclaration<br clear="all"></div>
    </div>
    <h3><span class="timing">0.124s</span><span class="clickable" onclick="ToggleClass('classID0EJOAC')" ondblclick="ToggleClass('classID0EJOAC')"><span class="success">✔</span>
                 FSharp.Data.TransactionTests
                 <span class="testcount">(12 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EJOAC">
      <div class="row"><span class="timing">0.004s</span><span class="success">✔</span>
             FSharp.Data.TransactionTests.Closing ConnectionStrings.AdventureWorks on complete<br clear="all"></div>
      <div class="altrow"><span class="timing">0.001s</span><span class="success">✔</span>
             FSharp.Data.TransactionTests.donNotOpenConnectionOnObject<br clear="all"></div>
      <div class="row"><span class="timing">0.018s</span><span class="success">✔</span>
             FSharp.Data.TransactionTests.doNotOpenConnectionOnObjectForAsyncNonQuery<br clear="all"></div>
      <div class="altrow"><span class="timing">0.001s</span><span class="success">✔</span>
             FSharp.Data.TransactionTests.doNotOpenConnectionOnObjectForNonQuery<br clear="all"></div>
      <div class="row"><span class="timing">0.018s</span><span class="success">✔</span>
             FSharp.Data.TransactionTests.implicit<br clear="all"></div>
      <div class="altrow"><span class="timing">0.034s</span><span class="success">✔</span>
             FSharp.Data.TransactionTests.implicitAsync<br clear="all"></div>
      <div class="row"><span class="timing">0.014s</span><span class="success">✔</span>
             FSharp.Data.TransactionTests.implicitAsyncNETBefore451<br clear="all"></div>
      <div class="altrow"><span class="timing">0.013s</span><span class="success">✔</span>
             FSharp.Data.TransactionTests.implicitWithConnInstance<br clear="all"></div>
      <div class="row"><span class="timing">0.014s</span><span class="success">✔</span>
             FSharp.Data.TransactionTests.local<br clear="all"></div>
      <div class="altrow"><span class="timing">0.002s</span><span class="success">✔</span>
             FSharp.Data.TransactionTests.notCloseExternalConnInCaseOfError<br clear="all"></div>
      <div class="row"><span class="timing">0.001s</span><span class="success">✔</span>
             FSharp.Data.TransactionTests.notCloseExternalConnInCaseOfError2<br clear="all"></div>
      <div class="altrow"><span class="timing">0.004s</span><span class="success">✔</span>
             FSharp.Data.TransactionTests.prematurelyOpenConnection<br clear="all"></div>
    </div>
    <h3><span class="timing">0.069s</span><span class="clickable" onclick="ToggleClass('classID0EKLAC')" ondblclick="ToggleClass('classID0EKLAC')"><span class="success">✔</span>
                 FSharp.Data.TVPTests
                 <span class="testcount">(12 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EKLAC">
      <div class="row"><span class="timing">0.006s</span><span class="success">✔</span>
             FSharp.Data.TVPTests.Basic<br clear="all"></div>
      <div class="altrow"><span class="timing">Skipped</span><span class="skipped">❢</span>
             FSharp.Data.TVPTests.InputIsEnumeratedExactlyOnce : Flucky<br clear="all"></div>
      <div class="row"><span class="timing">0.006s</span><span class="success">✔</span>
             FSharp.Data.TVPTests.NullableColumn<br clear="all"></div>
      <div class="altrow"><span class="timing">0.004s</span><span class="success">✔</span>
             FSharp.Data.TVPTests.ReuseTVPTypeForDynamicADONET<br clear="all"></div>
      <div class="row"><span class="timing">0.006s</span><span class="success">✔</span>
             FSharp.Data.TVPTests.SingleColumn<br clear="all"></div>
      <div class="altrow"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.TVPTests.SprocTupleValue<br clear="all"></div>
      <div class="row"><span class="timing">0.006s</span><span class="success">✔</span>
             FSharp.Data.TVPTests.SprocTupleValue works with empty table<br clear="all"></div>
      <div class="altrow"><span class="timing">0.004s</span><span class="success">✔</span>
             FSharp.Data.TVPTests.TableValuedTupleWithOptionalParams<br clear="all"></div>
      <div class="row"><span class="timing">0.008s</span><span class="success">✔</span>
             FSharp.Data.TVPTests.tvpSqlParamCleanUp<br clear="all"></div>
      <div class="altrow"><span class="timing">0.015s</span><span class="success">✔</span>
             FSharp.Data.TVPTests.TwoTVPParameterOfSameUDTT<br clear="all"></div>
      <div class="row"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.TVPTests.UsingMappedTVPInQuery<br clear="all"><h4>Output</h4>
        <pre>Data Source=.;Initial Catalog=AdventureWorks2012;Integrated Security=True
</pre>
      </div>
      <div class="altrow"><span class="timing">Skipped</span><span class="skipped">❢</span>
             FSharp.Data.TVPTests.UsingTVPInQuery : Fails at runtime :(<br clear="all"></div>
    </div>
    <h3><span class="timing">0.177s</span><span class="clickable" onclick="ToggleClass('classID0EYBAE')" ondblclick="ToggleClass('classID0EYBAE')"><span class="success">✔</span>
                 FSharp.Data.TypeProviderTest
                 <span class="testcount">(20 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0EYBAE">
      <div class="row"><span class="timing">0.009s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.asyncCustomRecord<br clear="all"></div>
      <div class="altrow"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.asyncSinlgeColumn<br clear="all"></div>
      <div class="row"><span class="timing">0.005s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.columnsShouldNotBeNull2<br clear="all"></div>
      <div class="altrow"><span class="timing">Skipped</span><span class="skipped">❢</span>
             FSharp.Data.TypeProviderTest.CommandTimeout : Don't execute for usual runs. Too slow.<br clear="all"></div>
      <div class="row"><span class="timing">Skipped</span><span class="skipped">❢</span>
             FSharp.Data.TypeProviderTest.ConcurrentReaders : Thread safe execution is not supported yet<br clear="all"></div>
      <div class="altrow"><span class="timing">0.007s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.ConditionalQuery<br clear="all"></div>
      <div class="row"><span class="timing">0.004s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.ConnectionClose<br clear="all"></div>
      <div class="altrow"><span class="timing">0.003s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.DeleteStatement<br clear="all"></div>
      <div class="row"><span class="timing">0.027s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.DynamicSql<br clear="all"></div>
      <div class="altrow"><span class="timing">0.004s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.emptyResultset<br clear="all"></div>
      <div class="row"><span class="timing">0.002s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.ExternalInstanceConnection<br clear="all"></div>
      <div class="altrow"><span class="timing">0.006s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.ResultsetExtendedWithTrailingColumn<br clear="all"></div>
      <div class="row"><span class="timing">0.034s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.ResultsetRuntimeVerificationDiffColumnTypes<br clear="all"></div>
      <div class="altrow"><span class="timing">0.018s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.ResultsetRuntimeVerificationLessThanExpectedColumns<br clear="all"></div>
      <div class="row"><span class="timing">0.005s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.Setting the command timeout isn't overridden when giving ConnectionStrings.AdventureWorksNamed context<br clear="all"></div>
      <div class="altrow"><span class="timing">0.018s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.singleRowOption<br clear="all"></div>
      <div class="row"><span class="timing">0.005s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.TinyIntConversion<br clear="all"></div>
      <div class="altrow"><span class="timing">0.005s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.ToTraceString<br clear="all"></div>
      <div class="row"><span class="timing">0.003s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.ToTraceString double-quotes<br clear="all"></div>
      <div class="altrow"><span class="timing">0.015s</span><span class="success">✔</span>
             FSharp.Data.TypeProviderTest.ToTraceString for CRUD<br clear="all"></div>
    </div>
    <h3><span class="timing">0.093s</span><span class="clickable" onclick="ToggleClass('classID0E5F')" ondblclick="ToggleClass('classID0E5F')"><span class="success">✔</span>
                 FSharp.Data.UnitsOfMeasure
                 <span class="testcount">(5 tests)</span></span><br clear="all"></h3>
    <div class="indent" style="display: none;" id="classID0E5F">
      <div class="row"><span class="timing">0.019s</span><span class="success">✔</span>
             FSharp.Data.UnitsOfMeasure.Datatable<br clear="all"></div>
      <div class="altrow"><span class="timing">0.020s</span><span class="success">✔</span>
             FSharp.Data.UnitsOfMeasure.FunctionDirect<br clear="all"></div>
      <div class="row"><span class="timing">0.015s</span><span class="success">✔</span>
             FSharp.Data.UnitsOfMeasure.FunctionInQuery<br clear="all"></div>
      <div class="altrow"><span class="timing">0.005s</span><span class="success">✔</span>
             FSharp.Data.UnitsOfMeasure.SingleOutput<br clear="all"></div>
      <div class="row"><span class="timing">0.034s</span><span class="success">✔</span>
             FSharp.Data.UnitsOfMeasure.WithParam<br clear="all"></div>
    </div>
  </body>
</html>

<|start_filename|>src/SqlClient.DesignTime/Scripts/Scratchpad.fsx<|end_filename|>

open System

type Date(dt: DateTime) = 
    member this.Year = dt.Year
    member this.Month = dt.Month
    member this.Day = dt.Day

    static member op_Implicit(x: DateTime) : Date = Date(x)
    static member op_Implicit(x: Date) : DateTime = DateTime(x.Year, x.Month, x.Day)

let now = DateTime.Now

let inline implicit arg =
  ( ^a : (static member op_Implicit : ^b -> ^a) arg)

Convert.ToDateTime(Date(now))

now |> box :> Date

//Convert.ChangeType(Nullable 42, typeof<int>)
Convert.ChangeType(42M, typeof<float>)



<|start_filename|>tests/SqlClient.Tests/TransactionTests.fs<|end_filename|>
﻿module FSharp.Data.TransactionTests

open System
open System.Data
open System.Transactions
open System.Data.SqlClient

open Xunit

open FSharp.Data.TypeProviderTest
open FSharp.Data.SqlClient

[<Fact>]
let ``Closing ConnectionStrings.AdventureWorks on complete``() =
    use command = new DeleteBitCoin()
    command.Execute(bitCoinCode) |> ignore
    Assert.Equal(System.Data.ConnectionState.Closed, (command :> ISqlCommand).Raw.Connection.State)

[<Fact>]
let implicit() =
    (new DeleteBitCoin()).Execute(bitCoinCode) |> ignore
    begin
        use tran = new TransactionScope() 
        Assert.Equal(1, InsertBitCoin.Create().Execute(bitCoinCode, bitCoinName))
        Assert.Equal(1, GetBitCoin.Create().Execute(bitCoinCode) |> Seq.length)
        Assert.Equal( Guid.Empty, Transaction.Current.TransactionInformation.DistributedIdentifier)
    end
    Assert.Equal(0, GetBitCoin.Create().Execute(bitCoinCode) |> Seq.length)

[<Fact>]
let implicitWithConnInstance() =
    (new DeleteBitCoin()).Execute(bitCoinCode) |> ignore
    begin
        use tran = new TransactionScope() 
        use conn = new SqlConnection(ConnectionStrings.AdventureWorks)
        conn.Open()
        Assert.Equal(1, InsertBitCoin.Create(conn).Execute(bitCoinCode, bitCoinName))
        Assert.Equal(1, GetBitCoin.Create(conn).Execute(bitCoinCode) |> Seq.length)
        Assert.Equal( Guid.Empty, Transaction.Current.TransactionInformation.DistributedIdentifier)
    end
    Assert.Equal(0, (new GetBitCoin()).Execute(bitCoinCode) |> Seq.length)

[<Fact>]
let implicitAsync() =
    (new DeleteBitCoin()).Execute(bitCoinCode) |> ignore
    begin
        use tran = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled) 
        Assert.Equal(1, (new InsertBitCoin()).AsyncExecute(bitCoinCode, bitCoinName) |> Async.RunSynchronously)
        Assert.Equal(1, (new GetBitCoin()).AsyncExecute(bitCoinCode) |> Async.RunSynchronously |> Seq.length)
        Assert.Equal( Guid.Empty, Transaction.Current.TransactionInformation.DistributedIdentifier)
    end
    Assert.Equal(0, (new GetBitCoin()).Execute(bitCoinCode) |> Seq.length)

[<Fact>]
let implicitAsyncNETBefore451() =
    (new DeleteBitCoin()).Execute(bitCoinCode) |> ignore
    begin
        use tran = new TransactionScope() 
        use conn = new SqlConnection(ConnectionStrings.AdventureWorks)
        conn.Open()
        conn.EnlistTransaction(Transaction.Current)
        let localTran = conn.BeginTransaction()
        Assert.Equal(1, (new InsertBitCoin(transaction = localTran)).AsyncExecute(bitCoinCode, bitCoinName) |> Async.RunSynchronously)
        Assert.Equal(1, (new GetBitCoin(transaction = localTran)).AsyncExecute(bitCoinCode) |> Async.RunSynchronously |> Seq.length)
        Assert.Equal( Guid.Empty, Transaction.Current.TransactionInformation.DistributedIdentifier)
    end
    Assert.Equal(0, (new GetBitCoin()).Execute(bitCoinCode) |> Seq.length)

[<Fact>]
let local() =
    (new DeleteBitCoin()).Execute(bitCoinCode) |> ignore
    use conn = new SqlConnection(ConnectionStrings.AdventureWorks)
    conn.Open()
    let tran = conn.BeginTransaction()
    Assert.Equal(1, (new InsertBitCoin(transaction = tran)).Execute(bitCoinCode, bitCoinName))
    Assert.Equal(1, (new GetBitCoin(transaction = tran)).Execute(bitCoinCode) |> Seq.length)
    tran.Rollback()
    Assert.Equal(0, (new GetBitCoin()).Execute(bitCoinCode) |> Seq.length)

type RaiseError = SqlCommandProvider<"SELECT 42; THROW 51000, 'Error raised.', 1 ", ConnectionStrings.AdventureWorksNamed>

[<Fact>]
let notCloseExternalConnInCaseOfError() =
    use conn = new SqlConnection(ConnectionStrings.AdventureWorks)
    conn.Open()
    let tran = conn.BeginTransaction()
    use cmd = new RaiseError(transaction = tran)
    try
        cmd.Execute() |> Seq.toArray |> ignore
    with _ ->
        Assert.True(conn.State = ConnectionState.Open)

[<Fact>]
let notCloseExternalConnInCaseOfError2() =
    use conn = new SqlConnection(ConnectionStrings.AdventureWorks)
    conn.Open()
    use cmd = new RaiseError(conn)
    try
        cmd.Execute() |> Seq.toArray |> ignore
    with _ ->
        Assert.True(conn.State = ConnectionState.Open)

[<Fact>]
let donNotOpenConnectionOnObject() =
    use conn = new SqlConnection(ConnectionStrings.AdventureWorks)
    //use cmd = new SqlCommandProvider<"SELECT 42", ConnectionStrings.AdventureWorksNamed>(Connection.Instance conn)
    use cmd = new SqlCommandProvider<"SELECT 42", ConnectionStrings.AdventureWorksNamed>(conn)
    Assert.Throws<InvalidOperationException>(fun() -> cmd.Execute() |> Seq.toArray |> ignore)    

type NonQuery = SqlCommandProvider<"DBCC CHECKIDENT ('HumanResources.Shift', RESEED, 4)", ConnectionStrings.AdventureWorksNamed>

[<Fact>]
let doNotOpenConnectionOnObjectForNonQuery() =
    use conn = new SqlConnection(ConnectionStrings.AdventureWorks)
    use cmd = new NonQuery(conn)
    Assert.Throws<InvalidOperationException>(fun() -> cmd.Execute() |> ignore)    
    
[<Fact>]
let doNotOpenConnectionOnObjectForAsyncNonQuery() =
    use conn = new SqlConnection(ConnectionStrings.AdventureWorks)
    use cmd = new NonQuery(conn)
    // should unwrap the aggregateexception here; not taking that on right now
    Assert.Throws<AggregateException>(fun() -> cmd.AsyncExecute() |> Async.RunSynchronously |> ignore)    
    
[<Fact>]
let prematurelyOpenConnection() =
    let cmd = new SqlCommandProvider<"SELECT 42", ConnectionStrings.AdventureWorksNamed>() 
    let _ = cmd.Execute()

    Assert.Equal(
        ConnectionState.Closed, 
        (cmd :> ISqlCommand).Raw.Connection.State
    )
    


<|start_filename|>src/SqlClient.Samples/WebApi.Controllers/HomeController.fs<|end_filename|>
﻿namespace WebApi

open System.Data.SqlClient
open System.Net
open System.Net.Http
open System.Web.Http
open System.Web.Configuration

open DataAccess

module SqlCommand = 
    let inline create() : 'a = 
        let designTimeConnectionString = (^a : (static member get_ConnectionStringOrName : unit -> string) ())

        match designTimeConnectionString with
        | DataAccess.AdventureWorks -> 
            //get connection string at run-time
            let adventureWorks = WebConfigurationManager.ConnectionStrings.["AdventureWorks2012"].ConnectionString
            //create command instance with connection string and command timeout override 
            (^a : (new : string * int -> ^a) (adventureWorks, 30)) 

        | _ -> failwithf "Unrecognized command type %s" typeof<'a>.FullName   


type HomeController() =
    inherit ApiController()

    member this.Get() = this.Get(7L, System.DateTime.Parse "2002-06-01")

    //http://localhost:61594/?top=4&sellStartDate=2002-07-01
    member this.Get(top, sellStartDate) =
        async {
            let cmd : QueryProducts = SqlCommand.create()

            //or get connnection info from web.config
            //let cmd = QueryProducts()

            let! data = cmd.AsyncExecute(top = top, SellStartDate = sellStartDate)
            return this.Request.CreateResponse(HttpStatusCode.OK, data)
        } |> Async.StartAsTask


<|start_filename|>docs/content/reference architecture.fsx<|end_filename|>
(*** hide ***)
#r "../../bin/FSharp.Data.SqlClient.dll"

(**

Beautiful Functional Architectures 
===================

[Project Euler. #17.](https://projecteuler.net/problem=17)

F# solution:
-------------------------------------
*)

let ``1..9`` = 
    [""; "one"; "two"; "three"; "four"; "five"; "six"; "seven"; "eight"; "nine"]
let ``10..19`` =  
    ["ten"; "eleven"; "twelve"; "thirteen"; "fourteen"; "fifteen"; "sixteen"; "seventeen"; "eighteen"; "nineteen"]
let dozens = 
    [""; ""; "twenty"; "thirty"; "forty"; "fifty"; "sixty"; "seventy"; "eighty"; "ninety"]

let spellNumber n = 
    [
        if n = 1000 then yield! "onethousand"
        else 
            let hundredsDigit = n / 100
            if hundredsDigit > 0 then yield! sprintf "%shundred" ``1..9``.[hundredsDigit] 
            let moduloOf100 = n % 100
            if hundredsDigit <> 0 && moduloOf100 <> 0 then yield! "and"
            if moduloOf100 >= 10 && moduloOf100 <= 19 then yield! ``10..19``.[moduloOf100 - 10]
            else
                yield! dozens.[moduloOf100 / 10] + ``1..9``.[moduloOf100 % 10]
    ]

[ 1 .. 1000 ]
|> List.collect spellNumber
|> List.length

(**

T-SQL solution:
-------------------------------------

<pre lang = 'sql'>
<code>
CREATE FUNCTION dbo.SpellNumber
(
	@n AS INT
)
RETURNS @result TABLE
(
	 Chars NVARCHAR(MAX) NOT NULL
)
AS
BEGIN
	IF @n = 1000
		INSERT INTO @result VALUES ('onethousand')
	ELSE
		WITH Digits AS 
		(
			SELECT offset, value
			FROM 
				(VALUES 
					(0, ''),  (1, 'one'),  (2, 'two'),  (3, 'three'),  (4, 'four'),
					(5, 'five'),  (6, 'six'),  (7, 'seven'),  (8, 'eight'),  (9, 'nine')) 
				AS T(offset, value)
		),
		TenToNineteen AS
		(
			SELECT offset, value
			FROM 
				(VALUES 
					(0, 'ten'),  (1, 'eleven'),  (2, 'twelve'),  (3, 'thirteen'),  (4, 'fourteen'),
					(5, 'fifteen'),  (6, 'sixteen'),  (7, 'seventeen'),  (8, 'eighteen'),  (9, 'nineteen')) 
				AS T(offset, value)
		),
		Dozens AS
		(
			SELECT offset, value
			FROM 
				(VALUES 
					(0, ''),  (1, ''),  (2, 'twenty'),  (3, 'thirty'),  (4, 'forty'),
                    (5, 'fifty'),  (6, 'sixty'),  (7, 'seventy'),  (8, 'eighty'),  (9, 'ninety')) 
				AS T(offset, value)
		)
		INSERT INTO @result 
			SELECT value + 'hundred' FROM Digits WHERE @n / 100 > 0 AND offset = @n / 100
			UNION ALL
			SELECT 'and' WHERE @n / 100 > 0 AND @n % 100 > 0 
			UNION ALL
			SELECT value FROM TenToNineteen WHERE (@n % 100) - 10 = offset
			UNION ALL
			SELECT value FROM Dozens WHERE (@n % 100 NOT BETWEEN 10 AND 19) AND ((@n % 100) / 10 = offset) 
			UNION ALL
			SELECT value FROM Digits WHERE (@n % 100 NOT BETWEEN 10 AND 19) AND ((@n % 100) % 10 = offset) 
	RETURN
END
</code>
</pre>

*)

open FSharp.Data

[<Literal>]
let adventureWorks = @"Data Source=(LocalDb)\v12.0;Initial Catalog=AdventureWorks2012;Integrated Security=True"

[<Literal>]
let euler17 = "
    WITH InfiniteSeq(N) AS
    (
        SELECT 1
	    UNION ALL
        SELECT 1 + N FROM InfiniteSeq
    )

    SELECT SUM(LEN(ys.Chars))
    FROM
        (SELECT TOP 1000 * FROM InfiniteSeq ) AS xs
	    CROSS APPLY dbo.SpellNumber(xs.N) AS ys
    OPTION (MAXRECURSION 0)
" 
type Euler17 = SqlCommandProvider<euler17, adventureWorks, SingleRow = true>

let cmd = new Euler17()
cmd.Execute() |> Option.get |> Option.get |> printfn "%i"




<|start_filename|>src/SqlClient.DesignTime/ProvidedTypesCache.fs<|end_filename|>
﻿[<AutoOpen>]
module internal FSharp.Data.SqlClient.Cache

open System.Collections.Concurrent

type TypeName = string

type Cache<'a>() =
    let cache = ConcurrentDictionary<TypeName, Lazy<'a>>()

    member __.TryGetValue(typeName: TypeName) =
        cache.TryGetValue(typeName)

    member __.GetOrAdd(typeName: TypeName, value: Lazy<'a>): 'a = 
        cache.GetOrAdd(typeName, value).Value

    member __.Remove(typeName: TypeName) =
        cache.TryRemove(typeName) |> ignore

<|start_filename|>tests/SqlClient.DesignTime.Tests/DesignTimeConnectionStringTests.fs<|end_filename|>
﻿module DesignTimeConnectionStringTests

open Xunit
open System.IO
open System.Configuration
open FSharp.Data.SqlClient

let adventureWorks = ConfigurationManager.ConnectionStrings.["AdventureWorks"].ConnectionString

[<Fact>]
let ``Wrong config file name`` () = 
    Assert.Throws<FileNotFoundException>(
        fun() -> 
            DesignTimeConnectionString.Parse("name=AdventureWorks", resolutionFolder = "", fileName = "non_existent") |> box
    ) |> ignore

[<Fact>]
let ``From config file`` () = 
    let x = DesignTimeConnectionString.Parse("name=AdventureWorks", __SOURCE_DIRECTORY__, "app.config")
    match x with
    | NameInConfig(name, value, _) ->
        Assert.Equal<string>("AdventureWorks", name)
        Assert.Equal<string>(adventureWorks, value)
    | _ -> failwith "Unexpected"

[<Fact>]
let RuntimeConfig() = 
    let x = DesignTimeConnectionString.Parse("name=AdventureWorks", __SOURCE_DIRECTORY__, "app.config")
    let actual = Linq.RuntimeHelpers.LeafExpressionConverter.EvaluateQuotation( x.RunTimeValueExpr(isHostedExecution = false)) |> unbox
    Assert.Equal<string>( adventureWorks, actual)


<|start_filename|>tests/SqlClient.Tests/UnitsOfMeasure.fs<|end_filename|>
﻿module FSharp.Data.UnitsOfMeasure

open Xunit

type DB = FSharp.Data.ProgrammabilityTest.AdventureWorks

type UOM = DB.Sales.``Units of Measure``

[<Fact>]
let SingleOutput() =
    use cmd = DB.CreateCommand<"SELECT SUM(TotalDue) FROM Sales.UnitedKingdomOrders", SingleRow = true>()
    Assert.Equal<_>(Some( Some(8570333.1218M<UOM.GBP>)), cmd.Execute())

[<Fact>]
let WithParam() =
    use cmd = DB.CreateCommand<"
        DECLARE @minTemp AS Sales.[<GBP>] = @min
        SELECT 
	        Total = SUM(x.TotalDue)
	        ,[Year] = DATEPART(year, y.OrderDate)
        FROM Sales.UnitedKingdomOrders x
	        JOIN Sales.SalesOrderHeader y on x.SalesOrderID = y.SalesOrderID
        GROUP BY DATEPART(year, y.OrderDate)
        HAVING SUM(x.TotalDue) > @minTemp
        ORDER BY 1
    ", TypeName = "GetUKSales">()

    let actual = cmd.Execute(2000000M<UOM.GBP>) |> Seq.toArray
    let expected = [|
        DB.Commands.GetUKSales.Record(Total = Some 2772402.4754M<UOM.GBP>, Year = Some 2008)
        DB.Commands.GetUKSales.Record(Total = Some 3873351.7965M<UOM.GBP>, Year = Some 2007)
    |]

    Assert.Equal<_[]>(expected, actual)

[<Fact>]
let FunctionInQuery() =
    use cmd = DB.CreateCommand<"SELECT * FROM Sales.GetUKSalesOrders(@min) ORDER BY 1", ResultType.Tuples>()

    let actual = cmd.Execute(2000000M<UOM.GBP>) |> Seq.toArray
    let expected = [|
        Some 2772402.4754M<UOM.GBP>, Some 2008
        Some 3873351.7965M<UOM.GBP>, Some 2007
    |]

    Assert.Equal<_[]>(expected, actual)

[<Fact>]
let FunctionDirect() =
    use cmd = new DB.Sales.GetUKSalesOrders()
    let actual = cmd.Execute(2000000M<UOM.GBP>) |> Seq.toArray |> Array.sortBy (fun x -> x.Total)
    let expected = [|
        DB.Sales.GetUKSalesOrders.Record(Total = Some 2772402.4754M<UOM.GBP>, Year = Some 2008)
        DB.Sales.GetUKSalesOrders.Record(Total = Some 3873351.7965M<UOM.GBP>, Year = Some 2007)
    |]

    Assert.Equal<_[]>(expected, actual)

[<Fact>]
let Datatable() =
    use cmd = DB.CreateCommand<"SELECT * FROM Sales.GetUKSalesOrders(@min) ORDER BY 1", ResultType.DataTable>()

    let actual = [| 
        for x in cmd.Execute(2000000M<UOM.GBP>).Rows do 
            let total: decimal<UOM.GBP> option = x.Total
            yield total, x.Year
    |]
    let expected = [|
        Some 2772402.4754M<UOM.GBP>, Some 2008
        Some 3873351.7965M<UOM.GBP>, Some 2007
    |]

    Assert.Equal<_[]>(expected, actual)



<|start_filename|>src/SqlClient.TestProjects/Lib/Script.fsx<|end_filename|>
﻿#r @"..\..\..\bin\net40\FSharp.Data.SqlClient.dll"
#r @"bin\Debug\Lib.dll"
#r @"System.Configuration"
#r @"System.Transactions"

DataAccess.get42()

open System

//let shifts = new DataAccess.AdventureWorks.HumanResources.Tables.Shift()
let shifts = DataAccess.getShiftTable()
do 
    use tran = new System.Transactions.TransactionScope()
    shifts.AddRow("French coffee break", StartTime = TimeSpan.FromHours 10., EndTime = TimeSpan.FromHours 12.)
    shifts.Update() |> printfn "Records affected %i"


<|start_filename|>src/SqlClient.DesignTime/Scripts/ReverseLineOrderForNotex.fsx<|end_filename|>

open System.IO

let releaseNotesfile = 
    [| __SOURCE_DIRECTORY__; "..\..\.."; "RELEASE_NOTES.md" |]
    |> Path.Combine    
    |> Path.GetFullPath

let release xs = 
    let isStart(s: string) = s.StartsWith("####")

    match xs with
    | h :: t when isStart h -> 
        let current = t |> List.takeWhile (not << isStart)
        let upcoming = t |> List.skipWhile (not << isStart)
        Some(h::current, upcoming)
    | _ -> None

let splitUpByRelease =
    releaseNotesfile 
    |> File.ReadAllLines 
    |> Array.toList
    |> Array.unfold release

let reversed = splitUpByRelease |> Array.rev |> Array.collect Array.ofList
    

File.WriteAllLines(releaseNotesfile, reversed)



<|start_filename|>src/SqlClient/Properties/launchSettings.json<|end_filename|>
{
  "profiles": {
    "SqlClient": {
      "commandName": "Executable",
      "executablePath": "C:\\Program Files (x86)\\Microsoft Visual Studio\\2017\\Professional\\Common7\\IDE\\devenv.exe",
      "commandLineArgs": "..\\..\\Tests.sln"
    }
  }
}

<|start_filename|>src/SqlClient/DynamicRecord.fs<|end_filename|>
﻿namespace FSharp.Data.SqlClient

open System
open System.Dynamic
open System.Collections.Generic

[<Sealed>]
///<summary>Custom implementation of <see cref='DynamicObject'/></summary>
type DynamicRecord(data: IDictionary<string, obj>) = 
    inherit DynamicObject() 

    do
        assert(data <> null)

    member internal this.Data = data 

    //Erase to this
    member this.Item with get key = data.[key] 

    //Dynamic support for JSON.NET serialization
    override this.GetDynamicMemberNames() = upcast data.Keys

    override this.TryGetMember( binder, result) = data.TryGetValue(binder.Name, &result)
    
    override this.TrySetMember( binder, result) = 
        let name = binder.Name
        if data.ContainsKey(name) 
        then 
            data.[name] <- result
            true
        else false

    //Object overrides 
    override this.Equals other = 
        match other with
        | :? DynamicRecord as v -> 
            Linq.Enumerable.SequenceEqual(data, v.Data)
        |_ -> false

    override this.GetHashCode () = List.ofSeq( data).GetHashCode()

    override this.ToString() =
        [|
            for KeyValue(key, value) in data ->
                sprintf "%s = %s" key ( if value = null || Convert.IsDBNull( value) then "None" else sprintf "%A" value) 
        |]
        |> String.concat "; "
        |> sprintf "{ %s }" 
    
            

<|start_filename|>docs/content/transactions.fsx<|end_filename|>
(*** hide ***)
#r @"..\..\bin\net40\FSharp.Data.SqlClient.dll"
#r "System.Transactions"
open FSharp.Data

[<Literal>]
let connectionString = @"Data Source=.;Initial Catalog=AdventureWorks2012;Integrated Security=True"
    
(**
Transactions 
===================

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

This chapter assumes a basic understanding of the "Transactions and Concurrency" topic as it applies to ADO.NET and SQL Server. 
(If you need to brush up your knowledge use favorite search engine \- there is plenty of information on the subject.)

The following links may be helpful:

 - Chapter 9 of [Microsoft SQL Server 2012 T-SQL Fundamentals](http://www.amazon.com/gp/product/0735658145). 
 - [Transactions and Concurrency in ADO.NET](https://msdn.microsoft.com/en-us/library/777e5ebh.aspx). 
 - Microsoft Virtual Academy has relevant courses
    - [Developing Microsoft SQL Server Databases. Chapter 04 - Managing Transactions.](http://www.microsoftvirtualacademy.com/training-courses/developing-microsoft-sql-server-databases) 
    - [Querying with Transact-SQL. Chapter 11 - Error Handling and Transactions.](http://www.microsoftvirtualacademy.com/training-courses/querying-with-transact-sql)

</p></div>

Explicit Transactions
-------------------------------------
Command types generated by both the SqlCommandProvider and the SqlProgrammabilityProvider have a constructor which accepts connection instance and optionally transaction information. 
This conforms to familiar [ADO.NET conventions](https://msdn.microsoft.com/en-us/library/352y4sff.aspx) for command constructors. 
*)

open System
open System.Data.SqlClient

type CurrencyCode = 
    SqlEnumProvider<"SELECT Name, CurrencyCode FROM Sales.Currency", connectionString>

type InsertCurrencyRate = SqlCommandProvider<"
        INSERT INTO Sales.CurrencyRate 
        VALUES (@currencyRateDate, @fromCurrencyCode, @toCurrencyCode, 
                @averageRate, @endOfDayRate, DEFAULT) 
    ", connectionString>
do
    //Don't forget `use` binding to properly scope transactoin.
    //It guarantees rollback in case of unhandled exception. 

    use conn = new SqlConnection(connectionString)
    conn.Open()
    use tran = conn.BeginTransaction()
    
    //Implicit assumption that
    assert (tran.Connection = conn)
    //Supply connection and transaction 
    use cmd = new InsertCurrencyRate(conn, tran)

    let recordsInserted = 
        cmd.Execute(
            currencyRateDate = DateTime.Today, 
            fromCurrencyCode = "USD", 
            toCurrencyCode = "GBP", 
            averageRate = 0.63219M, 
            endOfDayRate = 0.63219M) 

    assert (recordsInserted = 1)

    // Invoke Commit otherwise transaction will be disposed (roll-backed) when out of scope
    tran.Commit()

(**
Note, that ``Connection`` property of the transaction instance has to match the connection supplied up at the first position. 

Most often transactions are used in combination with data modification commands (INSERT, UPDATE, DELETE, MERGE). 
Commands based on SELECT statements or calls to a stored procedure (function) can join a transaction as well, but generally
do not do so if they stand alone.

*)

type AdventureWorks = SqlProgrammabilityProvider<connectionString>

do
    use conn = new SqlConnection(connectionString)
    conn.Open()
    //bump up isolation level to serializable
    use tran = conn.BeginTransaction(Data.IsolationLevel.Serializable)
    let jamesKramerId = 42

    let businessEntityID, jobTitle, hireDate = 
        //Include SELECT in transaction
        //Note that inline definition requires both design time connection string
        // and runtime connection object
        use cmd = new SqlCommandProvider<"
            SELECT 
	            BusinessEntityID
	            ,JobTitle
	            ,HireDate
            FROM 
                HumanResources.Employee 
            WHERE 
                BusinessEntityID = @id
            ", connectionString, ResultType.Tuples, SingleRow = true>(conn, tran)

        jamesKramerId |> cmd.Execute |> Option.get

    assert("Production Technician - WC60" = jobTitle)
    
    let newJobTitle = "Uber " + jobTitle

    use updatedJobTitle = new AdventureWorks.HumanResources.uspUpdateEmployeeHireInfo(conn, tran)
    let recordsAffrected = 
        updatedJobTitle.Execute(
            businessEntityID, 
            newJobTitle, 
            hireDate, 
            RateChangeDate = DateTime.Now, 
            Rate = 12M, 
            PayFrequency = 1uy, 
            CurrentFlag = true 
        )
    
    let updatedJobTitle = 
        // Static Create factory method can also be used to pass connection and/or transaction
        // It provides better intellisense. See a link below
        // https://github.com/Microsoft/visualfsharp/issues/449
        use cmd = new AdventureWorks.dbo.ufnGetContactInformation(conn, tran)
        //Use ExecuteSingle if you're sure it return 0 or 1 rows
        let result = cmd.ExecuteSingle(PersonID = jamesKramerId) 
        result.Value.JobTitle.Value

    assert(newJobTitle = updatedJobTitle)

    tran.Commit()

(**
Implicit a.k.a Ambient Transactions
-------------------------------------

It can become tedious to pass around a connection or transaction pair over and over again. 
Fortunately, the .NET BCL class [TransactionScope](https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx) was designed to address such tediousness. The basic idea is that all database connections opened within specific scope are included in that transaction. Thus, the example above can be re-written as follows:
*)

open System.Transactions
do
    use tran = new TransactionScope()

    let jamesKramerId = 42

    let businessEntityID, jobTitle, hireDate = 
        use cmd = new SqlCommandProvider<"
            SELECT 
	            BusinessEntityID
	            ,JobTitle
	            ,HireDate
            FROM 
                HumanResources.Employee 
            WHERE 
                BusinessEntityID = @id
            ", connectionString, ResultType.Tuples, SingleRow = true>(connectionString)

        jamesKramerId |> cmd.Execute |> Option.get

    assert("Production Technician - WC60" = jobTitle)
    
    let newJobTitle = "Uber " + jobTitle

    use updatedJobTitle = new AdventureWorks.HumanResources.uspUpdateEmployeeHireInfo(connectionString)
    let recordsAffrected = 
        updatedJobTitle.Execute(
            businessEntityID, 
            newJobTitle, 
            hireDate, 
            RateChangeDate = DateTime.Now, 
            Rate = 12M, 
            PayFrequency = 1uy, 
            CurrentFlag = true 
        )
    
    let updatedJobTitle = 
        use cmd = new AdventureWorks.dbo.ufnGetContactInformation(connectionString)
        //Use ExecuteSingle on sproc/function generated types
        //if you're sure it return 0 or 1 rows
        let result = cmd.ExecuteSingle(PersonID = jamesKramerId) 
        result.Value.JobTitle.Value

    assert(newJobTitle = updatedJobTitle)

    tran.Complete()

(**
Although very convenient, `TransactionScope` has some pitfalls and 
therefore requires a good understanding of what happens behind the scenes. 
Make sure to read  the [General Usage Guidelines](https://msdn.microsoft.com/en-us/library/ee818746.aspx) to avoid common mistakes.

There are two kind of issues you might run into when using `TransactionScope`:

Unexpectedly Distributed Transactions 
-------------------------------------
[Distributed Transactions](https://msdn.microsoft.com/en-us/library/ms254973.aspx) spell all kind of trouble. 
They are rarely required and should be avoided in most cases. 
Strictly speaking this problem is not specific to `TransactionScope`, but it can be exacerbated by 
automatic [Transaction Management Escalation](https://msdn.microsoft.com/en-us/library/ee818742.aspx), which thus makes it annoying easy to fall into the trap.

If a local transaction was accidently promoted to distributed it should be considered a design problem. It's generally best to have a simple check in your code right before `commit` to reveal the issue: 

*)

do
    use tran = new TransactionScope()
    //your transaction logic here
    let isDistributed = Transaction.Current.TransactionInformation.DistributedIdentifier <> Guid.Empty
    if isDistributed 
    then invalidOp "Unexpected distributed transaction."
    else tran.Complete()


(**

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

**TIP** SQL Server can use multiple `SQLConnections` in a `TransactionScope` without escalating, 
provided the connections are not open at the same time (which would result in multiple "physical" TCP connections and thus require escalation).
</p></div>

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

**TIP** The value of the [Enlist](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.enlist.aspx) 
key from [SqlConnection.ConnectionString](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx) 
property determines the auto-enlistment behavior of connection instance.
</p></div>

TransactionScope + AsyncExecute 
-------------------------------------

Another tricky problem involves combining `TransactionScope` with asynchronous execution. 
`TransactoinScope` [has thread affinity](http://stackoverflow.com/q/13543254/1603572). 
To propagate the transaction context to another thread, .NET 4.5.1 introduced [TransactionScopeAsyncFlowOption.Enabled](https://msdn.microsoft.com/en-us/library/system.transactions.transactionscopeasyncflowoption.aspx) which needs to be passed into the [TransactionScope constructor](https://msdn.microsoft.com/en-us/library/dn261473.aspx). 
Unfortunately if you are stuck with .NET Framework prior to version 4.5.1 the only way 
to combine `TransactionScope` with `AsyncExecute` is explicit transactions.  

*)

do
    use tran = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled)
    
    use cmd = new SqlCommandProvider<"
        INSERT INTO Sales.CurrencyRate 
        VALUES (@currencyRateDate, @fromCurrencyCode, @toCurrencyCode, 
                @averageRate, @endOfDayRate, @modifiedDate) 
    ", connectionString>(connectionString)

    let today = DateTime.Now.Date

    let recordsInserted = 
        cmd.AsyncExecute(
            currencyRateDate = today, 
            fromCurrencyCode = CurrencyCode.``US Dollar``, 
            toCurrencyCode = CurrencyCode.``United Kingdom Pound``, 
            averageRate = 0.63219M, 
            endOfDayRate = 0.63219M, 
            modifiedDate = today) 
        |> Async.RunSynchronously

    assert (recordsInserted = 1)

    tran.Complete()

(**
DataTable Updates/Bulk Load
-------------------------------------
Statically typed data tables generated either by SqlProgrammabilityProvider or by SqlCommandProvider 
with ResultType.DataTable have two helper methods `Update` and `BulkCopy` to send changes back into the database. 
Both methods accept connection and transaction to support explicit transactions.

The example above which inserts a new USD/GBP currency rate can be re-written as
*)

do
    let currencyRates = new AdventureWorks.Sales.Tables.CurrencyRate()
    let today = DateTime.Now
    currencyRates.AddRow(
            CurrencyRateDate = today, 
            FromCurrencyCode = CurrencyCode.``US Dollar``, 
            ToCurrencyCode = CurrencyCode.``United Kingdom Pound``, 
            AverageRate = 0.63219M, 
            EndOfDayRate = 0.63219M)

    use conn = new SqlConnection(connectionString)
    conn.Open()
    use tran = conn.BeginTransaction()

    let recordsAffected = currencyRates.Update(conn, tran)
    assert (recordsAffected = 1)
    
    //or Bulk Load
    //currencyRates.BulkCopy(conn, transaction = tran)

    tran.Commit()

(**
Same as above with implicit transaction.
*)

do
    let currencyRates = new AdventureWorks.Sales.Tables.CurrencyRate()
    let today = DateTime.Now
    currencyRates.AddRow(
            CurrencyRateDate = today, 
            FromCurrencyCode = CurrencyCode.``US Dollar``, 
            ToCurrencyCode = CurrencyCode.``United Kingdom Pound``, 
            AverageRate = 0.63219M, 
            EndOfDayRate = 0.63219M)

    use tran = new TransactionScope()

    let recordsAffected = currencyRates.Update()
    assert (recordsAffected = 1)
    
    //or Bulk Load
    //currencyRates.BulkCopy()

    tran.Complete()

(**
    
*)


<|start_filename|>build.cmd<|end_filename|>
@echo off

.paket\paket.bootstrapper.exe
if errorlevel 1 (
  exit /b %errorlevel%
)

.paket\paket.exe restore
if errorlevel 1 (
  exit /b %errorlevel%
)

packages\build\FAKE\tools\FAKE.exe --removeLegacyFakeWarning build.fsx %*

<|start_filename|>tests/SqlClient.Tests/SynonymsTests.fs<|end_filename|>
﻿module FSharp.Data.SynonymsTests

open System
open System.Data
open System.Data.SqlClient
open ProgrammabilityTest
open Xunit

type AdventureWorks = ProgrammabilityTest.AdventureWorks

[<Fact>]
let TVFSynonym() = 
    let personId = 42
    let actual = 
        use cmd = new AdventureWorks.HumanResources.GetContactInformation()
        cmd.ExecuteSingle(personId)
        |> Option.map(fun x -> x.FirstName, x.LastName)

    let expected = 
        use cmd = new ProgrammabilityTest.GetContactInformation()
        cmd.ExecuteSingle(personId)
        |> Option.map(fun x -> x.FirstName, x.LastName)

    Assert.Equal(expected, actual)

[<Fact>]
let SPSynonym() = 
    let personId = 42
    let actual = 
        use cmd = new AdventureWorks.HumanResources.GetEmployeeManagers()
        [ for x in cmd.Execute(personId) -> sprintf "%A.%A" x.FirstName x.LastName ]

    let expected = 
        use cmd = new AdventureWorks.dbo.uspGetEmployeeManagers()
        [ for x in cmd.Execute(personId) -> sprintf "%A.%A" x.FirstName x.LastName ]

    Assert.Equal<_ list>(expected, actual)


[<Fact>]
let TableSynonym() = 
    let adventureWorks = System.Configuration.ConfigurationManager.ConnectionStrings.["AdventureWorks"].ConnectionString
    use conn = new SqlConnection(connectionString = adventureWorks)
    conn.Open()
    use tran = conn.BeginTransaction()

    use cmd = new GetRowCount(transaction = tran) 
    Assert.Equal(Some( Some 3), cmd.Execute())
    
    let t = new AdventureWorks.dbo.Tables.HRShift()
    let row = t.NewRow()
    row.Name <- "French coffee break"
    row.StartTime <- TimeSpan.FromHours 10.
    row.EndTime <- TimeSpan.FromHours 12.
    t.Rows.Add row
    let rowsInserted = t.Update(conn, tran)
    Assert.Equal(1, rowsInserted)

    Assert.Equal(Some( Some 4), cmd.Execute())



<|start_filename|>docs/content/unit-testing.fsx<|end_filename|>
(*** hide ***)
#r @"..\..\bin\net40\FSharp.Data.SqlClient.dll"
#r @"..\..\packages\Test\xunit\lib\net20\xunit.dll"
#r "System.Transactions"
open FSharp.Data
open System

[<Literal>]
let connectionString = @"Data Source=.;Initial Catalog=AdventureWorks2012;Integrated Security=True"

(**

Unit-testing
===================

Often there is a need to test business or presentation logic independently of database. 
This can be archived via [Repository](http://martinfowler.com/eaaCatalog/repository.html) pattern. 

*)

//Command types definitions
type GetEmployeesByLevel = 
    SqlCommandProvider<"
        SELECT P.FirstName, P.LastName, E.JobTitle
        FROM HumanResources.Employee AS E
	        JOIN Person.Person AS P ON E.BusinessEntityID = P.BusinessEntityID
        WHERE OrganizationLevel = @orgLevel
    ", connectionString>

type GetSalesChampion = SqlCommandProvider<"
    SELECT TOP 1 FirstName, LastName
    FROM Sales.vSalesPerson
    WHERE CountryRegionName = @countryRegionName
    ORDER BY SalesYTD DESC
    " , connectionString, SingleRow = true>

//Repository inteface
type IRepository = 
    abstract GetEmployeesByLevel: int16 -> list<GetEmployeesByLevel.Record>
    abstract GetSalesChampion: country: string -> option<GetSalesChampion.Record>

//Production implementation
type Repository(connectionString: string) = 
    interface IRepository with 
        member __.GetEmployeesByLevel(orgLevel) = 
            use cmd = new GetEmployeesByLevel(connectionString)
            cmd.Execute(orgLevel) |> Seq.toList

        member __.GetSalesChampion( region) = 
            use cmd = new GetSalesChampion(connectionString)
            cmd.Execute(region) 
        
//logic to test
let whoReportsToCEO(db: IRepository) = 
    [ for x in db.GetEmployeesByLevel(1s) -> sprintf "%s %s" x.FirstName x.LastName, x.JobTitle ]

let bestSalesRepInCanada(db: IRepository) = 
    db.GetSalesChampion("Canada")

//unit tests suite
module MyTests = 
    
    //mock the real repo
    let mockRepository = {
        new IRepository with 
            member __.GetEmployeesByLevel(orgLevel) = 
                if orgLevel = 1s 
                then 
                    [ 
                        //Generated record types have single constructor that include all properties
                        //It exists to support unit-testing.
                        GetEmployeesByLevel.Record("David", "Bradley", JobTitle = "Marketing Manager") 
                    ]
                else []

            member __.GetSalesChampion( region) = 
                use cmd = new GetSalesChampion(connectionString)
                cmd.Execute(region) 
    }

    //unit test
    let ``who reports to CEO``() = 
        let expected = [ "<NAME>", "Marketing Manager" ]
        assert (whoReportsToCEO mockRepository = expected)        
        //replace assert invocation above with invocation to your favorite unit testing framework 
        //fro example Xunit.NET: Assert.Equal<_ list>(expected, actual)

    //unit test
    let ``best sales rep in Canada``() = 
        let expected = Some( GetSalesChampion.Record("José", "Saraiva"))
        assert (bestSalesRepInCanada mockRepository = expected)        





<|start_filename|>tests/SqlClient.Tests/SqlClient.SqlServerTypes.Tests.dll.html<|end_filename|>
﻿<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html>
  <head>
    <META http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>
                    xUnit.net Test Results - </title>
    <style type="text/css">
                    body { font-family: Calibri, Verdana, Arial, sans-serif; background-color: White; color: Black; }
                    h2,h3,h4,h5 { margin: 0; padding: 0; }
                    h3 { font-weight: normal; }
                    h5 { font-weight: normal; font-style: italic; margin-bottom: 0.75em; }
                    pre { font-family: Consolas; font-size: 85%; margin: 0 0 0 1em; padding: 0; }
                    .divided { border-top: solid 1px #f0f5fa; padding-top: 0.5em; }
                    .row, .altrow { padding: 0.1em 0.3em; }
                    .row { background-color: #f0f5fa; }
                    .altrow { background-color: #e1ebf4; }
                    .success, .failure, .skipped { font-family: Arial Unicode MS; font-weight: normal; float: left; width: 1em; display: block; }
                    .success { color: #0c0; }
                    .failure { color: #c00; }
                    .skipped { color: #cc0; }
                    .timing { float: right; }
                    .indent { margin: 0.25em 0 0.5em 2em; }
                    .clickable { cursor: pointer; }
                    .testcount { font-size: 85%; }
                </style><script language="javascript">
                    function ToggleClass(id) {
                        var elem = document.getElementById(id);
                        if (elem.style.display == "none") {
                            elem.style.display = "block";
                        }
                        else {
                            elem.style.display = "none";
                        }
                    }
                </script></head>
  <body>
    <h3 class="divided"><b>Assemblies Run</b></h3>
    <div>c:\code\FSharp.Data.SqlClient\src\SqlClient.Tests\SqlClient.SqlServerTypes.Tests\bin\Release\net461\SqlClient.SqlServerTypes.Tests.dll</div>
    <h3 class="divided"><b>Summary</b></h3>
    <div>
                    Tests run: <a href="#all"><b>3</b></a>  
                    Failures: <a href="#failures"><b>2</b></a>,
                    Skipped: <a href="#skipped"><b>0</b></a>,
                    Run time: <b>0.25s</b></div><br><h2><a name="failures"></a>Failed tests</h2>
    <div class="row"><span class="timing">0.007s</span><span class="failure">✘</span>
             FSharp.Data.SpatialTypesTests.spatialTypes : System.IO.FileLoadException : Could not load file or assembly 'FSharp.Core, Version=4.5.0.0, Culture=neutral, PublicKeyToken=<KEY>' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)<br clear="all"><pre>   at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMarkHandle stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName, ObjectHandleOnStack type)
   at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark&amp; stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName)
   at System.Type.GetType(String typeName, Boolean throwOnError)
   at FSharp.Data.ISqlCommand Implementation..ctor(DesignTimeConfig cfg, FSharpChoice`3 connection, Int32 commandTimeout)
   at FSharp.Data.SpatialTypesTests.spatialTypes()</pre>
    </div>
    <div class="altrow"><span class="timing">0.225s</span><span class="failure">✘</span>
             FSharp.Data.SpatialTypesTests.SqlHierarchyIdParam : System.IO.FileLoadException : Could not load file or assembly 'FSharp.Core, Version=4.5.0.0, Culture=neutral, PublicKeyToken=<KEY>' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)<br clear="all"><pre>   at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMarkHandle stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName, ObjectHandleOnStack type)
   at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark&amp; stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName)
   at System.Type.GetType(String typeName, Boolean throwOnError)
   at FSharp.Data.ISqlCommand Implementation..ctor(DesignTimeConfig cfg, FSharpChoice`3 connection, Int32 commandTimeout)
   at FSharp.Data.SpatialTypesTests.SqlHierarchyIdParam()</pre>
    </div><br><h2><a name="all"></a>All tests</h2>
    <h5>Click test class name to expand/collapse test details</h5>
    <h3><span class="timing">0.250s</span><span class="clickable" onclick="ToggleClass('classID0EO')" ondblclick="ToggleClass('classID0EO')"><span class="failure">✘</span>
                 FSharp.Data.SpatialTypesTests
                 <span class="testcount">(3 tests)</span></span><br clear="all"></h3>
    <div class="indent" id="classID0EO">
      <div class="row"><span class="timing">0.018s</span><span class="success">✔</span>
             FSharp.Data.SpatialTypesTests.GEOMETRY and GEOGRAPHY sp params<br clear="all"></div>
      <div class="altrow"><span class="timing">0.007s</span><span class="failure">✘</span>
             FSharp.Data.SpatialTypesTests.spatialTypes : System.IO.FileLoadException : Could not load file or assembly 'FSharp.Core, Version=4.5.0.0, Culture=neutral, PublicKeyToken=<KEY>' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)<br clear="all"><pre>   at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMarkHandle stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName, ObjectHandleOnStack type)
   at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark&amp; stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName)
   at System.Type.GetType(String typeName, Boolean throwOnError)
   at FSharp.Data.ISqlCommand Implementation..ctor(DesignTimeConfig cfg, FSharpChoice`3 connection, Int32 commandTimeout)
   at FSharp.Data.SpatialTypesTests.spatialTypes()</pre>
      </div>
      <div class="row"><span class="timing">0.225s</span><span class="failure">✘</span>
             FSharp.Data.SpatialTypesTests.SqlHierarchyIdParam : System.IO.FileLoadException : Could not load file or assembly 'FSharp.Core, Version=4.5.0.0, Culture=neutral, PublicKeyToken=<KEY>' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)<br clear="all"><pre>   at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMarkHandle stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName, ObjectHandleOnStack type)
   at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark&amp; stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName)
   at System.Type.GetType(String typeName, Boolean throwOnError)
   at FSharp.Data.ISqlCommand Implementation..ctor(DesignTimeConfig cfg, FSharpChoice`3 connection, Int32 commandTimeout)
   at FSharp.Data.SpatialTypesTests.SqlHierarchyIdParam()</pre>
      </div>
    </div>
  </body>
</html>

<|start_filename|>tests/SqlClient.Tests/Scripts/SqlCommand.fsx<|end_filename|>
(**
    Use cases
*)
#r "System.Configuration"
#r "../../../bin/FSharp.Data.SqlClient.dll"
open System
open FSharp.Data

[<Literal>] 
let connectionString = "Server=.;Database=AdventureWorks2012;Trusted_Connection=yes"
[<Literal>] 
let connectionStringName = "name=AdventureWorks"
[<LiteralAttribute>]
let appConfig = __SOURCE_DIRECTORY__ + @"\..\app.config"

[<Literal>]
let queryProductsSql = "
SELECT TOP (@top) Name AS ProductName, SellStartDate, Size
FROM Production.Product 
WHERE SellStartDate > @SellStartDate
"

//Custom record types and connection string override
type QueryProducts = SqlCommandProvider<queryProductsSql, connectionStringName, ConfigFile = @"..\app.config">
let cmd1 = new QueryProducts()
let result1 : Async<QueryProducts.Record seq> = cmd1.AsyncExecute(top = 7L, SellStartDate = System.DateTime.Parse "2002-06-01")
result1 |> Async.RunSynchronously |> Seq.iter (fun x -> printfn "Product name: %s. Sells start date %A, size: %A" x.ProductName x.SellStartDate x.Size)
let records = cmd1.Execute(top = 7L, SellStartDate = System.DateTime.Parse "2002-06-01") |> List.ofSeq
records |> Seq.iter (printfn "%A")
let record = records.Head

//Record constructor
let newrecord = QueryProducts.Record("foo", System.DateTime(2000,1,1), Some "bar")
record <> newrecord

//Two parallel executions

type cmdType = SqlCommandProvider<queryProductsSql, connectionStringName, ConfigFile = appConfig>
let par = new cmdType()
let reader1 = par.AsyncExecute(top = 7L, SellStartDate = System.DateTime.Parse "2002-06-01")
let reader2 = par.AsyncExecute(top = 7L, SellStartDate = System.DateTime.Parse "2002-06-01")
reader1 |> Async.RunSynchronously |> Seq.head |> printfn "%A"
reader2 |> Async.RunSynchronously |> Seq.head |> printfn "%A"

//Tuples
type QueryProductsAsTuples = SqlCommandProvider<queryProductsSql, connectionString, ResultType = ResultType.Tuples>
let cmd = new QueryProductsAsTuples()
let result = cmd.AsyncExecute(top = 7L, SellStartDate = System.DateTime.Parse "2002-06-01")
result |> Async.RunSynchronously |> Seq.iter (fun(productName, sellStartDate, size) -> printfn "Product name: %s. Sells start date %A, size: %A" productName sellStartDate size)
cmd.Execute(top = 7L, SellStartDate = System.DateTime.Parse "2002-06-01") |> Seq.iter (fun(productName, sellStartDate, size) -> printfn "Product name: %s. Sells start date %A, size: %A" productName sellStartDate size)

//DataTable for data binding scenarios and update
type QueryProductDataTable = SqlCommandProvider<queryProductsSql, connectionString, ResultType = ResultType.DataTable>
let cmd2 = new QueryProductDataTable() //top = 7L, SellStartDate = System.DateTime.Parse "2002-06-01")
let result2  = cmd2.AsyncExecute(top = 7L, SellStartDate = System.DateTime.Parse "2002-06-01") |> Async.RunSynchronously
result2.Rows |> Seq.iter (fun row -> printfn "Product name: %s. Sells start date %O, size: %A" row.ProductName row.SellStartDate row.Size)
cmd2.Execute(top = 7L, SellStartDate = System.DateTime.Parse "2002-06-01").Rows |> Seq.iter (fun row -> printfn "Product name: %s. Sells start date %O, size: %A" row.ProductName row.SellStartDate row.Size)

//Single row hint and optional output columns. Records result type.
type QueryPersonInfoSingletone = SqlCommandProvider<"SELECT * FROM dbo.ufnGetContactInformation(@PersonId)", connectionString, ResultType = ResultType.Records, SingleRow=true>
let cmd3 = new QueryPersonInfoSingletone()
let result3 = cmd3.AsyncExecute(PersonId = 2) 
result3 |> Async.RunSynchronously |> Option.get |> fun x -> printfn "Person info: Id - %i, FirstName - %O, LastName - %O, JobTitle - %O, BusinessEntityType - %O" x.PersonID x.FirstName x.LastName x.JobTitle x.BusinessEntityType
cmd3.Execute(PersonId = 2).Value |> fun x -> printfn "Person info: Id - %i, FirstName - %O, LastName - %O, JobTitle - %O, BusinessEntityType - %O" x.PersonID x.FirstName x.LastName x.JobTitle x.BusinessEntityType

//Single row hint and optional output columns. Tuple result type.
type QueryPersonInfoSingletoneTuples = SqlCommandProvider<"SELECT * FROM dbo.ufnGetContactInformation(@PersonId)", connectionString, SingleRow=true, ResultType = ResultType.Tuples>
let cmd35 = new QueryPersonInfoSingletoneTuples()
let result35 : Async<_> = cmd35.AsyncExecute(PersonId = 2) 
result35 |> Async.RunSynchronously |> Option.get |> fun(personId, firstName, lastName, jobTitle, businessEntityType) -> printfn "Person info: Id - %i, FirstName - %O, LastName - %O, JobTitle - %O, BusinessEntityType - %O" personId firstName lastName jobTitle businessEntityType
cmd35.Execute(PersonId = 2).Value |> fun(personId, firstName, lastName, jobTitle, businessEntityType) -> printfn "Person info: Id - %i, FirstName - %O, LastName - %O, JobTitle - %O, BusinessEntityType - %O" personId firstName lastName jobTitle businessEntityType

//Single row hint and optional output columns. Single value.
type QueryPersonInfoSingleValue = SqlCommandProvider<"SELECT FirstName FROM dbo.ufnGetContactInformation(@PersonId)", connectionString, SingleRow=true>
let cmd36 = new QueryPersonInfoSingleValue()
let result36 : Async<_> = cmd36.AsyncExecute(PersonId = 2) 
result36 |> Async.RunSynchronously |> (function | Some(Some firstName) -> printfn "FirstName - %s" firstName | _ -> printfn "Nothing to print" )
cmd36.Execute(PersonId = 2) |> (function | Some(Some firstName) -> printfn "FirstName - %s" firstName | _ -> printfn "Nothing to print" )

//Single row hint and optional output columns. Data table result type.
type QueryPersonInfoSingletoneDataTable = SqlCommandProvider<"SELECT * FROM dbo.ufnGetContactInformation(@PersonId)", connectionString, ResultType = ResultType.DataTable>
let cmd37 = new QueryPersonInfoSingletoneDataTable()
let result37 = cmd37.AsyncExecute(PersonId = 2) |> Async.RunSynchronously 
let printPersonInfo(x : QueryPersonInfoSingletoneDataTable.Row) = printfn "Person info: Id - %i, FirstName - %O, LastName - %O, JobTitle - %O, BusinessEntityType - %O" x.PersonID x.FirstName x.LastName x.JobTitle x.BusinessEntityType
result37.Rows |> Seq.iter printPersonInfo
result37.Rows.[0].FirstName <- result37.Rows.[0].FirstName |> Option.map (fun x -> x + "1")
result37.Rows |> Seq.iter printPersonInfo
result37.Rows.[0].FirstName <- None
result37.Rows |> Seq.iter printPersonInfo
cmd37.Execute(PersonId = 2).Rows |> Seq.iter printPersonInfo

//Single value
type GetServerTime = SqlCommandProvider<"IF @IsUtc = CAST(1 AS BIT) SELECT GETUTCDATE() ELSE SELECT GETDATE()", connectionString, SingleRow=true>
let getSrvTime = new GetServerTime()
let result5 = getSrvTime.AsyncExecute(IsUtc = true) 
result5 |> Async.RunSynchronously |> printfn "%A"
getSrvTime.Execute(IsUtc = false) |> printfn "%A"

//Non-query
type UpdateEmplInfoCommand = SqlCommandProvider<"EXEC HumanResources.uspUpdateEmployeePersonalInfo @BusinessEntityID, @NationalIDNumber,@BirthDate, @MaritalStatus, @Gender ", connectionString>
let cmd4 = new UpdateEmplInfoCommand()
let result4 : Async<int> = cmd4.AsyncExecute(BusinessEntityID = 2, NationalIDNumber = "245797967", BirthDate = System.DateTime(1965, 09, 01), MaritalStatus = "S", Gender = "F") 
let rowsAffected = result4 |> Async.RunSynchronously 
let cmd45 = new UpdateEmplInfoCommand()
cmd45.Execute(BusinessEntityID = 2, NationalIDNumber = "245797967", BirthDate = System.DateTime(1965, 09, 01), MaritalStatus = "S", Gender = "M")

//Command from file
type q = SqlCommandProvider<"../sampleCommand.sql", connectionString>
let cmdFromFile = new q()
cmdFromFile.Execute() |> Seq.toArray

//Fallback to metadata retrieval through FMTONLY
type UseFMTONLY = SqlCommandProvider<"exec dbo.[Init]", connectionString>
let useFMTONLY = new UseFMTONLY()
useFMTONLY.Execute()

//Runtime column names
type UseGet = SqlCommandProvider<"exec dbo.[Get]", connectionString, ResultType = ResultType.DataReader >
let useGet = new UseGet()
useGet.Execute().NextResult() = false

//Insert command
type InsertCommand = 
    SqlCommandProvider<"INSERT INTO dbo.ErrorLog
                VALUES (GETDATE(), @UserName, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage)", connectionString, SingleRow = true>

open System.Security.Principal

let cmdInsert = new InsertCommand()
let user = WindowsIdentity.GetCurrent().Name
cmdInsert.Execute(user, 121, 16, 3, "insert test", int __LINE__, "failed insert")

#r "Microsoft.SqlServer.Types"

type GetEmployeeByLevel = SqlCommandProvider<"SELECT * FROM HumanResources.Employee WHERE OrganizationLevel = @OrganizationLevel", connectionString>
let getEmployeeByLevel = new GetEmployeeByLevel()
getEmployeeByLevel.Execute(2s)


type MyCommand1 = SqlCommandProvider<"SELECT GETDATE() AS Now, GETUTCDATE() AS UtcNow", connectionString>
type MyRecord1 = MyCommand1.Record
let r1 = MyCommand1.Record(DateTime.Now, DateTime.UtcNow)

type MyCommand2 = SqlCommandProvider<"SELECT GETDATE() AS Now, GETUTCDATE() AS UtcNow", connectionString>
let r2 = MyCommand2.Record(DateTime.Now, DateTime.UtcNow)

type MyRecord = { Now: DateTime; UtcNow: DateTime }

let inline toMyRecord (x: 'Recrod) = 
    {
        Now = (^Record : (member get_Now : unit -> DateTime) x)
        UtcNow = (^Record : (member get_UtcNow : unit -> DateTime) x)
    }


[<Literal>]
let getDatesQuery = "SELECT GETDATE() AS Now, GETUTCDATE() AS UtcNow"
[<Literal>]
let localhost = "Data Source=.;Integrated Security=True;"
type GetDates = SqlCommandProvider<getDatesQuery,  localhost>

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(localhost)
    conn.Open()
    let cmd = new System.Data.SqlClient.SqlCommand(getDatesQuery, conn)
    cmd.ExecuteReader().ToRecords<GetDates.Record>() 
    |> Seq.toArray



<|start_filename|>tests/SqlClient.Tests/ConfigurationTest.fs<|end_filename|>
﻿module FSharp.Data.SqlClient.ConfigurationTests 

open Xunit
open FSharp.Data
open System.Configuration

let adventureWorks = ConfigurationManager.ConnectionStrings.["AdventureWorks"].ConnectionString

type Get42 = SqlCommandProvider<"SELECT 42", ConnectionStrings.AdventureWorksNamed, ConfigFile = "appWithInclude.config">

type LongQuery = SqlCommandProvider<"
    INSERT INTO Production.BillOfMaterials (BillOfMaterialsID, ProductAssemblyID, ComponentID, StartDate, EndDate, UnitMeasureCode, BOMLevel, PerAssemblyQty, ModifiedDate) 
    VALUES (1692, 776, 907, N'2004-07-20 00:00:00', NULL, N'EA ', 1, CAST(1.00 AS Decimal(8, 2)), N'2004-07-06 00:00:00')
", ConnectionStrings.AdventureWorksNamed>

type SampleCommand = SqlFile<"sampleCommand.sql">
type SampleCommandRelative = SqlFile<"sampleCommand.sql", "MySqlFolder">

[<Fact>]
let SqlFiles() = 
    use cmd1 = new SqlCommandProvider<SampleCommand.Text, ConnectionStrings.AdventureWorksNamed>()
    use cmd2 = new SqlCommandProvider<SampleCommandRelative.Text, ConnectionStrings.AdventureWorksNamed>()
    Assert.Equal<_ seq>(cmd1.Execute() |> Seq.toArray, cmd2.Execute() |> Seq.toArray)


<|start_filename|>docs/content/sqlenumprovider.quickstart.fsx<|end_filename|>
(*** hide ***)
#r "../../bin/FSharp.Data.SqlClient.dll"

(**
FSharp.Data.SqlEnumProvider
===========================

Motivation
-------------------------------------
Often there is a certain amount of reference/lookup data in a database. 
This information changes relatively rare. 
At same time it's never represented in an application types. 

Here is the specific example. We'll use AdventureWorks2012 as sample database.

Let's say we need to retrieve number of orders shipped in certain way since specific date.

Order shippment types defined in Purchasing.ShipMethod table. 
    
    [lang=sql]
    SELECT Name, ShipMethodID FROM Purchasing.ShipMethod

The query returns:

<table class="table table-bordered table-striped">
<thead><tr><td>Name</td><td>ShipMethodID</td></tr></thead>
<tbody>
    <tr><td>CARGO TRANSPORT 5</td><td>5</td></tr>
    <tr><td>OVERNIGHT J-FAST</td><td>4</td></tr>
    <tr><td>OVERSEAS - DELUXE</td><td>3</td></tr>
    <tr><td>XRQ - TRUCK GROUND</td><td>1</td></tr>
    <tr><td>ZY - EXPRESS</td><td>2</td></tr>
</tbody>
</table>

A typical implementation for overnight orders shipped since Jan 1, 2008 is following:
*)

[<Literal>]
let connStr = @"Data Source=.;Initial Catalog=AdventureWorks2012;Integrated Security=True"

open System 
open System.Data.SqlClient

let conn = new SqlConnection (connStr)
conn.Open()

let cmd = new SqlCommand ("
    SELECT COUNT(*) 
    FROM Purchasing.PurchaseOrderHeader 
    WHERE ShipDate > @shippedLaterThan AND ShipMethodID = @shipMethodId", conn)

cmd.Parameters.AddWithValue("@shippedLaterThan", DateTime(2008, 1, 1)) |> ignore
cmd.Parameters.AddWithValue("@shipMethodId", 4) |> ignore
cmd.ExecuteScalar() |> unbox<int>

(**
The query returns valid answer 748 but suffers from a serious issue - it uses  magic number (4).
  
The problem can alleviated by ad-hoc enum definition:
*)

type ShippingMethod = 
    | ``XRQ - TRUCK GROUND`` = 1
    | ``ZY - EXPRESS`` = 2
    | ``OVERSEAS - DELUXE`` = 3
    | ``OVERNIGHT J-FAST`` = 4
    | ``CARGO TRANSPORT 5`` = 5
    
cmd.Parameters.AddWithValue("@shippedLaterThan", DateTime(2008, 1, 1)) |> ignore
cmd.Parameters.AddWithValue("@shipMethodId", ShippingMethod.``OVERNIGHT J-FAST``) |> ignore
cmd.ExecuteScalar() |> unbox<int>

(**
But improvement is questionable because we traded one problem for another - 
keeping this enum type definition in sync with database changes.  

Solution - SqlEnumProvider
-------------------------------------

### F# idiomatic enum-like type

Idea is to generate enumeration like type based on a query to database leveraging F# type providers feature. 

The code above can be rewritten as follows:

*)

open FSharp.Data

//by convention: first column is Name, second is Value
type ShipMethod = 
    SqlEnumProvider<"SELECT Name, ShipMethodID FROM Purchasing.ShipMethod ORDER BY ShipMethodID", connStr>

//Now combining 2 F# type providers: SqlEnumProvider and SqlCommandProvider
type OrdersByShipTypeSince = SqlCommandProvider<"
    SELECT COUNT(*) 
    FROM Purchasing.PurchaseOrderHeader 
    WHERE ShipDate > @shippedLaterThan AND ShipMethodID = @shipMethodId", connStr, SingleRow = true>

let cmd2 = new OrdersByShipTypeSince(connStr) 
cmd2.Execute( DateTime( 2008, 1, 1), ShipMethod.``OVERNIGHT J-FAST``) 

(**
This type has semantics similar to standard BCL Enum type and stays in sync with database SqlEnumProvider pointed to. 
We get readability, up-to date lookup data verified by compiler and intellisense. 

**Important presumption that source reference data is synchronized with production environment** because SqlEnumProvider usually points to development environment database.

The SQL statement used to query database has to return resultset of certain shape:

    - It has to have 2 or more columns
    - The first columns is unique name (or tag/label).
    - The second column is value. In contrast to BCL Enum it can not only numeric type but also 
    decimal, DateTime, DateTimeOffet or string
    - If value consists of more than one column it represented as Tuple. It makes it similar to Java enums 

Again the idea is straightforward: anywhere in the code tag/label will be replace by value: ``XRQ - TRUCK GROUND`` with 1, ``ZY - EXPRESS`` with 1, etc.

The `ShipMethod` type provides more idiomatic interface that standard BCL Enum:
  - `Items` is read-only field of `list<string * 'T>` where `'T` is type of value
  - `TryParse` return `option<'T>`

Below are sample invocations and output from FSI:

*)

//Utility methods - provide more idiomatic F# interface
ShipMethod.Items
//val it : List<string * int> =
//  [("XRQ - TRUCK GROUND", 1); ("ZY - EXPRESS", 2); ("OVERSEAS - DELUXE", 3);
//   ("OVERNIGHT J-FAST", 4); ("CARGO TRANSPORT 5", 5)]

ShipMethod.``CARGO TRANSPORT 5``
//val it : int = 5
ShipMethod.``OVERNIGHT J-FAST``
//val it : int = 4
ShipMethod.TryParse("CARGO TRANSPORT 5") 
//val it : Option<int> = Some 5
ShipMethod.TryParse("cargo transport 5") 
//val it : Option<int> = None
ShipMethod.TryParse("cargo transport 5", ignoreCase = true) 
//val it : Option<int> = Some 5
ShipMethod.TryParse("Unknown") 
//val it : Option<int> = None
ShipMethod.Parse("CARGO TRANSPORT 5") 
//val it : int = 5


(**  
### F# tuple-valued enums

As mentioned above result set with more than 2 columns is mapped to enum with tuple as value. This makes it similar to [Java enums] (http://javarevisited.blogspot.com/2011/08/enum-in-java-example-tutorial.html).
*)

//ShipRate is included into the resultset in addition to ShipMethodID``` 
type ShipInfo = 
    SqlEnumProvider<"SELECT Name, ShipMethodID, ShipRate FROM Purchasing.ShipMethod ORDER BY ShipMethodID", connStr>

type TheLatestOrder = SqlCommandProvider<"
    SELECT TOP 1 * 
    FROM Purchasing.PurchaseOrderHeader 
    ORDER BY ShipDate DESC
    ", connStr, SingleRow = true>

let cmd3 = new TheLatestOrder(connStr) 
let theLatestOrder = cmd3.Execute().Value

//exploring multi-item value for application logic

//using the first item for conditional logic
if theLatestOrder.ShipMethodID = fst ShipInfo.``OVERSEAS - DELUXE``
then 
    //using the second item for computation
    printfn "Some calculation: %M" <| 50M * snd ShipInfo.``OVERSEAS - DELUXE``

(**
I have mixed feelings about applicability of multi-item value type. Please provide feedback/examples that prove it useful or otherwise._

### CLI native enums

SqlEnumProvider is unique because it supports two type generation strategies: F# idiomatic enum-behaving type and standard CLI enumerated types. 
Second can be useful where compiler allows only const declaration - attribute constructors for example. Set "CLIEnum" parameter to generate standard enum.

*)

//CLI Enum

#r "System.Web.Http.dll" 
#r "System.Net.Http.dll" 
open System.Web.Http

type Roles = 
    SqlEnumProvider<"
        SELECT * 
        FROM (VALUES(('Read'), 1), ('Write', 2), ('Admin', 4)) AS T(Name, Value)
    ", @"Data Source=(LocalDb)\v12.0;Integrated Security=True", Kind = SqlEnumKind.CLI>

type CustomAuthorizeAttribute(roles: Roles) = 
    inherit AuthorizeAttribute()

    override __.OnAuthorization actionContext = 
        //auth logic here
        ()

[<CustomAuthorizeAttribute(Roles.Admin)>]
type MyController() = 
    inherit ApiController()

    member __.Get() = 
        Seq.empty<string>

(**
It also makes this types **accessible from C#** or any other .NET language.

Miscellaneous
-------------------------------------

### Multi-platform. 

### Any ADO.NET supported database
SqlEnumProvider has a static parameter "Provider" which allows to pass ADO.NET provider [invariant name](http://msdn.microsoft.com/en-us/library/h508h681.aspx). 
This makes it usable with any ADO.NET supported database. "System.Data.SqlClient" is default value for ADO.NET provider.

Invariant names of available ADO.NET providers can be retrieved as follows:
*)

open System.Data.Common
[ for r in DbProviderFactories.GetFactoryClasses().Rows -> r.["InvariantName"] ]

(**
### Generated types accesable from C#, Visual Basic and other .NET languages
Show your fellow C#/VB developers magic of F# type provider accessible from their favorite language!!! Sample project is here. 
[Demo solution](https://github.com/dmitry-a-morozov/FSharp.Data.SqlEnumProvider/blob/master/demos/CSharp.Client/Program.cs) includes example.

### Xamarin ???
The type provider should work in XS when a project targets Mono runtime. Nothing technically stops to make it available for Xamarin supported mobile platforms (iOS, Android & Windows Phone) to access SQLite.

### Future extensions: [FlagsAttribute](http://msdn.microsoft.com/en-us/library/system.flagsattribute.aspx) Enums ?

### Educational
F# developers often ask about simple examples of "generated types" type providers. Here you go. I hope it will be useful.
*)


<|start_filename|>src/SqlClient/AssemblyInfo.fs<|end_filename|>
﻿// Auto-Generated by FAKE; do not edit
namespace System
open System.Reflection
open System.Runtime.CompilerServices

[<assembly: AssemblyTitleAttribute("SqlClient")>]
[<assembly: AssemblyProductAttribute("FSharp.Data.SqlClient")>]
[<assembly: AssemblyDescriptionAttribute("SqlClient F# type providers")>]
[<assembly: AssemblyVersionAttribute("2.0.5")>]
[<assembly: AssemblyFileVersionAttribute("2.0.5")>]
[<assembly: InternalsVisibleToAttribute("SqlClient.Tests")>]
do ()

module internal AssemblyVersionInformation =
    let [<Literal>] AssemblyTitle = "SqlClient"
    let [<Literal>] AssemblyProduct = "FSharp.Data.SqlClient"
    let [<Literal>] AssemblyDescription = "SqlClient F# type providers"
    let [<Literal>] AssemblyVersion = "2.0.5"
    let [<Literal>] AssemblyFileVersion = "2.0.5"
    let [<Literal>] InternalsVisibleTo = "SqlClient.Tests"


<|start_filename|>tests/SqlClient.Tests/DynamicRecordTests.fs<|end_filename|>
﻿module FSharp.Data.DynamicRecordTests

open System
open System.Dynamic
open System.Collections.Generic

open Xunit
open Newtonsoft.Json
open FSharp.Data.SqlClient

let recordWithNulls = DynamicRecord(dict ["DBNull", box DBNull.Value; "null", null; "foo", box "bar"])

[<Fact>]
let ``ToString : Nulls to Nones``() =
    Assert.Equal<string>(
        expected = @"{ DBNull = None; null = None; foo = ""bar"" }",
        actual = recordWithNulls.ToString()
    )

type Binder(name) = 
    inherit GetMemberBinder(name, false) 
    override this.FallbackGetMember(_,_) = null

[<Fact>] 
let ``TryGetMember succeeds``() = 
    Assert.Equal(
        expected = (true, box DBNull.Value),
        actual = recordWithNulls.TryGetMember(Binder("DBNull"))
    )

[<Fact>] 
let ``TryGetMember fails``() = 
    Assert.Equal(
        expected = recordWithNulls.TryGetMember(Binder("foobar")),
        actual = (false, null)
    )

[<Fact>] 
let ``Not equal of different type``() = 
    Assert.False( recordWithNulls.Equals( obj()))

[<Fact>] 
let ``Not equal of different size``() = 
    let condition = recordWithNulls = DynamicRecord(dict [])
    Assert.False condition

[<Fact>] 
let ``Not equal with different keys``() = 
    let condition = recordWithNulls = DynamicRecord(dict ["DBNull", box DBNull.Value; "bar", null])
    Assert.False condition

[<Fact>] 
let ``Not equal with different values``() = 
    let condition = recordWithNulls = DynamicRecord(dict ["DBNull", box DBNull.Value; "foo", box "foo"])
    Assert.False condition

let getData(x: DynamicRecord) = 
    dict [ for name in x.GetDynamicMemberNames() -> name, x.[name] ]

[<Fact>] 
let Equal() = 
    let data = getData recordWithNulls
    let condition = recordWithNulls = DynamicRecord(data)
    Assert.True condition

[<Fact>] 
let ``GetHashCode is same when equal``() = 
    let data = getData recordWithNulls
    let clone = DynamicRecord( data)
    if recordWithNulls = clone //did if to make assertion more granular
    then 
        let condition = recordWithNulls.GetHashCode() = clone.GetHashCode() 
        Assert.True condition

let dt = DateTime(2012,1,1)
let offset = DateTimeOffset(dt,TimeSpan.FromHours(2.))
let data = dict ["Date", box dt; "Offset", box offset]
let dateRecord = DynamicRecord(data) 
let dateRecordString = """{"Date":"2012-01-01T00:00:00","Offset":"2012-01-01T00:00:00+02:00"}"""
let recordWithNullsString = """{"DBNull":null,"null":null,"foo":"bar"}"""

[<Fact>] 
let ``JSON serialize``() = 
    Assert.Equal<string>(
        expected = recordWithNullsString,
        actual = JsonConvert.SerializeObject recordWithNulls
    )

    Assert.Equal<string>(
        expected = dateRecordString,
        actual = JsonConvert.SerializeObject( dateRecord)
    )

[<Fact>] 
let ToString() = 
    Assert.Equal<string>(
        expected = sprintf "{ Date = %A; Offset = %A }" data.["Date"] data.["Offset"],
        actual = dateRecord.ToString()
    )





<|start_filename|>tests/SqlClient.Tests/Scripts/SqlProgrammability.fsx<|end_filename|>
#I @"..\..\..\packages"
#r "../../../bin/Fsharp.Data.SqlClient.dll"
#r "Microsoft.SqlServer.Types.dll"
//#load "ConnectionStrings.fs"
open System
open System.Data
open FSharp.Data


//[<Literal>] 
//let connectionString = ConnectionStrings.AdventureWorksLiteral
//let connectionString = ConnectionStrings.AdventureWorksAzure

//[<Literal>] 
//let prodConnectionString = ConnectionStrings.MasterDb

type AdventureWorks = SqlProgrammabilityProvider<"Data Source=.;Initial Catalog = AdventureWorks2012;Integrated Security=True">
type dbo = AdventureWorks.dbo
let x = 42.<AdventureWorks.Sales.``Units of Measure``.GBP>
let y = 12.<AdventureWorks.Sales.``Units of Measure``.USD>
x.GetType().AssemblyQualifiedName

//let cmd = new SqlCommandProvider<"
//    SELECT X.* 
//    FROM Sales.SpecialOfferProduct X
//	    JOIN Sales.SalesOrderDetail Y ON X.ProductID = Y.ProductID 
//    WHERE X.ProductID = @specialOfferProductProductid 
//	    AND Y.ProductID = @salesOrderDetailProductid
//	    AND (X.SpecialOfferID IS NOT NULL 
//		    OR Y.SpecialOfferID IS NOT NULL)
//	     ", "Data Source=.;Initial Catalog = AdventureWorks2012;Integrated Security=True">()

//#r @"Newtonsoft.Json.8.0.2\lib\net45\Newtonsoft.Json.dll"
//
//open Newtonsoft.Json
//
////let dt = new DataTable()
////dt.Columns.Add("id", typeof<int>) |> ignore
////dt.Columns.Add("name", typeof<string>) |> ignore
////dt.LoadDataRow([|box 1; box "<NAME>"|], true) |> ignore
////dt.LoadDataRow([|box 2; box "<NAME>"|], true) |> ignore
////JsonConvert.SerializeObject dt
//
//let dt = new AdventureWorks.HumanResources.Tables.Shift()
//dt.AddRow("French coffee break", StartTime = TimeSpan.FromHours 10., EndTime = TimeSpan.FromHours 12., ModifiedDate = Some DateTime.Now)
//dt.AddRow("Spanish siesta", TimeSpan.FromHours 13., TimeSpan.FromHours 16., Some DateTime.Now)
//let s = JsonConvert.SerializeObject dt
//
//let dt2 = JsonConvert.DeserializeObject<DataTable>(s) //|> box |> unbox<AdventureWorks.HumanResources.Tables.Shift>
//let dt3 = new AdventureWorks.HumanResources.Tables.Shift()
//
//let reader = dt2.CreateDataReader()
//while reader.Read() do ()
//reader.Close()
//
//dt.Merge(dt2)

//[ for c in dt2.Columns -> c.ColumnName, c.DataType.FullName ] = [ for c in dt.Columns -> c.ColumnName, c.DataType.FullName ]

type Thermion = SqlProgrammabilityProvider<"Data Source=.;Initial Catalog = Thermion;Integrated Security=True">




<|start_filename|>docs/content/bulk load.fsx<|end_filename|>
(*** hide ***)
#r @"..\..\bin\net40\FSharp.Data.SqlClient.dll"
#r "System.Transactions"
open FSharp.Data
open System

[<Literal>]
let connectionString = @"Data Source=.;Initial Catalog=AdventureWorks2014;Integrated Security=True"

(**

Bulk Load
===================

*)


<|start_filename|>src/SqlClient.DesignTime/SqlFileProvider.fs<|end_filename|>
﻿namespace FSharp.Data

open System.IO
open Microsoft.FSharp.Core.CompilerServices
open ProviderImplementation.ProvidedTypes
open FSharp.Data.SqlClient
open System.Text 

[<TypeProvider>]
[<CompilerMessageAttribute("This API supports the FSharp.Data.SqlClient infrastructure and is not intended to be used directly from your code.", 101, IsHidden = true)>]
type SqlFileProvider(config : TypeProviderConfig) = 
    inherit SingleRootTypeProvider(
        config, 
        "SqlFile", 
        [
            ProvidedStaticParameter("Path", typeof<string>) 
            ProvidedStaticParameter("ResolutionFolder", typeof<string>, "") 
            ProvidedStaticParameter("Encoding", typeof<string>, "") 
        ])

    override __.CreateRootType( assembly, nameSpace, typeName, args) = 
        let path, resolutionFolder, encoding = string args.[0], string args.[1], string args.[2]

        if Path.GetExtension(path) <> ".sql" 
        then failwith "Only files with .sql extension are supported"

        let fullPath = 
            if Path.IsPathRooted( path)
            then 
                path 
            else
                let parent = 
                    if resolutionFolder = "" then config.ResolutionFolder 
                    elif Path.IsPathRooted( resolutionFolder) then resolutionFolder
                    else Path.Combine(config.ResolutionFolder, resolutionFolder)
                Path.Combine( parent, path)

        let typ = 
            lazy 
                let t = ProvidedTypeDefinition(assembly, nameSpace, typeName, baseType = Some typeof<obj>, hideObjectMethods = true)

                let content = 
                    if encoding = "" 
                    then File.ReadAllText( fullPath) 
                    else File.ReadAllText( fullPath, encoding = Encoding.GetEncoding( encoding))

                t.AddMember <| ProvidedField.Literal("Text", typeof<string>, content)

                t

        typ, [| new SingleFileChangeMonitor(fullPath) |]


<|start_filename|>docs/tools/generate.fsx<|end_filename|>
﻿// Binaries that have XML documentation (in a corresponding generated XML file)
let referenceBinaries = [ ]
// Web site location for the generated documentation
let website = "/FSharp.Data.SqlClient"

let githubLink = "http://github.com/fsprojects/FSharp.Data.SqlClient"

// Specify more information about your project
let info =
  [ "project-name", "FSharp.Data.SqlClient"
    "project-logo-url", "img/logo.png"
    "project-author", "<NAME>, <NAME>"
    "project-summary", "SqlCommandProvider provides statically typed access to input parameters and result set of T-SQL command in idiomatic F# way. SqlProgrammabilityProvider exposes Stored Procedures, User-Defined Types and User-Defined Functions in F# code."
    "project-github", githubLink
    "project-nuget", "http://www.nuget.org/packages/FSharp.Data.SqlClient" ]

#load "../../.paket/load/net46/Build/FSharp.Formatting.fsx"
#load "../../.paket/load/net46/Build/FAKE.Lib.fsx"

open Fake
open System.IO
open Fake.IO.FileSystemOperators
open FSharp.Literate
open FSharp.MetadataFormat

// see https://github.com/fsharp/FAKE/issues/1579#issuecomment-306580820
let execContext = Fake.Core.Context.FakeExecutionContext.Create false (Path.Combine(__SOURCE_DIRECTORY__, __SOURCE_FILE__)) []
Fake.Core.Context.setExecutionContext (Fake.Core.Context.RuntimeContext.Fake execContext)

let root = "."

// Paths with template/source/output locations
let bin        = __SOURCE_DIRECTORY__ @@ "../../bin/net40"
let content    = __SOURCE_DIRECTORY__ @@ "../content"
let output     = __SOURCE_DIRECTORY__ @@ "../output"
let files      = __SOURCE_DIRECTORY__ @@ "../files"
let templates  = __SOURCE_DIRECTORY__ @@ "templates"
let formatting = __SOURCE_DIRECTORY__ @@ "../../packages/Build/FSharp.Formatting/"
let docTemplate = formatting @@ "templates/docpage.cshtml"

// Where to look for *.csproj templates (in this order)
let layoutRoots =
  [ templates; formatting @@ "templates"
    formatting @@ "templates/reference" ]

// Copy static files and CSS + JS from F# Formatting
let copyFiles () =
  Fake.IO.Shell.copyRecursive files output true |> Fake.Core.Trace.logItems "Copying file: "
  Fake.IO.Directory.ensure (output @@ "content")
  Fake.IO.Shell.copyRecursive (formatting @@ "styles") (output @@ "content") true 
    |> Fake.Core.Trace.logItems "Copying styles and scripts: "

// Build API reference from XML comments
let buildReference () =
  Fake.IO.Shell.cleanDir (output @@ "reference")
  for lib in referenceBinaries do
    MetadataFormat.Generate
      ( bin @@ lib, output @@ "reference", layoutRoots, 
        parameters = ("root", root)::info,
        sourceRepo = githubLink @@ "tree/master",
        sourceFolder = __SOURCE_DIRECTORY__ @@ ".." @@ "..",
        publicOnly = true )

// Build documentation from `fsx` and `md` files in `docs/content`
let buildDocumentation () =
  let subdirs = Directory.EnumerateDirectories(content, "*", SearchOption.AllDirectories)
  for dir in Seq.append [content] subdirs do
    let sub = if dir.Length > content.Length then dir.Substring(content.Length + 1) else "."
    Literate.ProcessDirectory
      ( dir, docTemplate, output @@ sub, replacements = ("root", root)::info,
        layoutRoots = layoutRoots )

// Generate
copyFiles()
buildDocumentation()
buildReference()

<|start_filename|>src/SqlClient.Samples/WebApi.Controllers/DataAccess.fs<|end_filename|>
﻿module WebApi.DataAccess

open FSharp.Data

[<Literal>]
let AdventureWorks = "name=AdventureWorks"

type QueryProducts = SqlCommandProvider< const(SqlFile<"T-SQL/Products.sql">.Text), AdventureWorks, DataDirectory = "App_Data">

//type ProductQuery = SqlFile<"T-SQL/Products.sql">
//type QueryProducts = SqlCommandProvider<ProductQuery.Text, AdventureWorks, DataDirectory = "App_Data">

type AdventureWorks = SqlProgrammabilityProvider<AdventureWorks, DataDirectory = "App_Data">

