SqlProgrammabilityProvider with Record based User-Defined Table Types

[davidtme]

Currently the User-Defined Table Types are classes. Could a config option be added to allow them to be records.

Sql Setup:

--DROP PROCEDURE InsertTypeIntoTable1
--DROP TYPE dbo.Type1
--DROP TABLE Table1
--GO

CREATE TABLE [dbo].[Table1](
    Id INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(50) NOT NULL)
GO

CREATE TYPE dbo.Type1 AS TABLE(
    Name NVARCHAR(50) NOT NULL
)
GO

CREATE PROCEDURE InsertTypeIntoTable1
    @Data dbo.Type1 READONLY
AS
BEGIN
    SET NOCOUNT ON;
    INSERT  Table1(Name)
    SELECT  Name
    FROM    @Data
END
GO

Test Script:

#r "./packages/FSharp.Data.SqlClient/lib/net40/FSharp.Data.SqlClient.dll"

open FSharp.Data

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

type Db = SqlProgrammabilityProvider<connectionString>

let cmd = new Db.dbo.InsertTypeIntoTable1(connectionString)

// Type1 is a class
type Type1 = Db.dbo.``User-Defined Table Types``.Type1

cmd.Execute([ Type1("Value 1"); Type1("Value 2") ]) |> ignore

However it would be great if the last line could be used as so (maybe with a config?):

cmd.Execute([ { Name = "Value 1" }; { Name = "Value 2" } ]) |> ignore

[smoothdeveloper]

@davidtme that would be nice, alas I think I think type providers don't allow yet to generate record types or other things relying on more advanced F# type system features (unless it is just an attribute).

I might be wrong and hopefully someone else will confirm or say what can be done.

In the meantime, can you check if you can specify argument names, which is probably can be fixed if it isn't the case already:

Type(Name = "Value1") // instead of  Type1("Value 1")

Thanks a lot for describing what you are after that way :smiley:

[smoothdeveloper]

Consider voting on https://github.com/fsharp/fslang-suggestions/issues/154 to have type provider support emitting record types and DUs.

[davidtme]

Voted. Shame about the TP and records. I may look at https://github.com/fsprojects/SQLProvider/blob/62d7f0dc700fdbfa1bd4d788c0e6c4801f8e27a5/src/SQLProvider/SqlRuntime.Common.fs#L218 for some ideas.