How to use local schema for F# SqlProgrammabilityProvider calling stored procedure

Question

I am able to use FSharp.Data.SqlProgrammabilityProvider to call my store procedure, but I am unable to figure out how to save down types to a local schema in order for a remote build server without DB access to build successfully.

For querying normal tables, I can save down a local schema which is used by the build server: FSharp.Data.TypeProviders.SqlEntityConnection

However, SqlEntityConnection cannot access my stored procedure.

While the SqlProgrammabilityProvider can call my stored procedure, it does not seem to have the option so use a local schema.

I am open for any method that works here.

let [<Literal>] private CONN = @"Data Source={...}; Initial Catalog={...};Integrated Security=True;Connect Timeout=180"
type myDB = SqlProgrammabilityProvider<CONN>
let cmd = new myDB.dbo.usp_MyStoredProcedure(CONN,commandTimeout=600)
type TVPType = myDB.dbo.``User-Defined Table Types``.MyTableType
type ReturnRecord = myDB.dbo.usp_myStoredProcedure.Record

let createRawRecord (r : ReturnRecord) = 
  ... //Turn into F# record type.

let ids = ["id1"; "id2"] |> Seq.map(fun i -> TVPType(i))
let startDate = ...
let endDate = ...

cmd.AsyncExecute(ids, startDate, endDate)
|> Async.RunSynchronously
|> Seq.map (createRawRecord)
|> Seq.toArray

Answer 1

I am open for any method that works here.

CONN in SqlProgrammabilityProvider<CONN> is he design/compile-time connection, used by the type provider to generate types. CONN in myDB.dbo.usp_MyStoredProcedure(CONN,commandTimeout=600) is used to access the database at runtime. These will be different.

The method we are using is:

1. Define tables/functions/stored procedures in an SSDT project.
2. Building the SSDT Project generates a create script (just tick the box), and a powershell build step in between building the SSDT project and the type provider project deploys this to localdb.
3. The type provider's design-time connection string references localdb, and the runtime connection string references the place where the data is.

This gives SQL intellisense via SSDT. And if the project compiles, the SQL schema must be compatible with the queries in the type provider project. Great combination of .Net and F# features.