ExecuteNonQuery requires an open and available connection

[rkosafo]

Description

Queries with TempTables failed with the call to .LoadTempTables.

Repro steps

type Q = 
  SqlCommandProvider<
    "select 1 as id", //same error as "select * from #Temp1"
    ConnStr, 
    TempTableDefinitions = "create table #Temp1 (name nvarchar(32) not null)">
let f () =
  let cmd = new Q (ConnStr)
  cmd.LoadTempTables (Array.empty) //fails here
  cmd.Execute () |> List.ofSeq

[<EntryPoint>]
let main argv = 
  f () |> printfn "%A"
  System.Console.ReadLine () |> ignore
  0 // return an integer exit code

Expected behavior

Expected query to execute and return something

Actual behavior

System.InvalidOperationException: 'ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.'

Known workarounds

Not sure. Similar code with 1.8.5 worked in a different project so not sure what is different here.

Related information

• Operating system: windows 10
• Branch: ?
• Database versions and sample databases being used: 14.0.1000.169
• .NET Runtime, CoreCLR or Mono Version: .NET 4.7.1.
• Editing Tools (e.g. Visual Studio Version): VS 2017 15.8.0 Preview 2.0
• Lins to F# RFCs or entries on http://fslang.uservoice.com

[smoothdeveloper]

@rkosafo thanks for the issue report, as a work around, you might want to use one of the other constructors, taking a live connection object.

@davidtme it seems to be related or at least interacting to the temp table support PR (#278), would you be able to take a look? there are two ways of handling the connection (managed or not) and it might be something that we overlooked during implementation / review of #278.

[davidtme]

@rkosafo at the moment it needs to be an open connection so @smoothdeveloper is correct and you should use one of the other constructors:

use conn = new SqlConnection(ConnStr)
conn.Open()
use cmd = new Q(conn)
cmd.LoadTempTables (Array.empty)

@smoothdeveloper I guess LoadTempTables could open the connection if it needs to, I'll see what I can sort out.

[rkosafo]

@davidtme @smoothdeveloper Thanks. Will do that.

[cmeeren]

I too encountered this. Would be great to have LoadTempTables open the connection automatically!

I guess it's not too difficult to implement - probably something around here?

https://github.com/fsprojects/FSharp.Data.SqlClient/blob/4450560810f0970894d6a46226dae1ca55d7640d/src/SqlClient.DesignTime/DesignTime.fs#L745-L747

Any chance to have a look at this, or give me a pointer so I can make a PR myself? (I've never created a TP, so the code is a bit foreign to me.)

Will use the workaround in the meantime, though it's an extra two lines for each query I'd rather avoid. :)