How to specify a parameter as collection in Commandtext

Question

I am using SqlCommandProvider and I need to get some data for each id in an id collection

let ids=["B058A99-C4B2-4CC3-BA9F-034B1F1ECCBD";"A09C01C-D51B-41C1-B44C-0995DD285088"]
[<Literal>]
let qLogo ="""SELECT Id,LogoUrl FROM Hotels WHERE Id IN (@IDS)"""
let queryLogo = new SqlCommandProvider<qLogo,constring>()
queryLogo .Execute(ids)//i need to be able to pass a collection here

`

What about `ids |> Seq.collect queryLogo.Execute`? (I have not tested it but it should come close) — Random Dev, Feb 01 '16 at 11:54
`ids |> Seq.collect queryLogo.Execute` would go to server for every id seperately, i probably will have 500+ id time to time so it would take long time — Evren, Feb 01 '16 at 12:03
for the above qLogo `SqlCommandProvider<...>.Execute(IDS: System.Guid) : System.Collections.Generic.IEnumerable.Record>` — Evren, Feb 01 '16 at 12:10

score 2 · Answer 1 · edited May 23 '17 at 10:27

Long story short, SqlCommandProvider is not even correct type provider to do this (assuming you don't consider string concatenation to build query). The reason is SQL Server only accepts array parameters through calling stored procedure and passing a Table Valued Parameter.

So, to call a stored procedure you need an SqlProgrammabilityProvider to achieve this. But you'll have to create Table Type and Stored Procedure up-front, as described in type provider documentation (scroll down to "Table-valued parameters (TVPs)" section).

Relevant discussion: How to use SQL IN statement in fsharp.data.sqlclient?

How to specify a parameter as collection in Commandtext

1 Answers1