Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I have the following sample code. The objective is to run SQL statement with multiple input parameters.

[<Literal>]
let connectionString = @"Data Source=Localhost;Initial Catalog=Instrument;Integrated Security=True"
[<Literal>]
let query = "SELECT MacroName, MacroCode FROM Instrument WHERE MacroCode IN (@codeName)"

type MacroQuery = SqlCommandProvider<query, connectionString>
let cmd = new MacroQuery()
let res = cmd.AsyncExecute(codeName= [|"CPI";"GDP"|]) |> Async.RunSynchronously

However, codeName is inferred to be string type instead of an array or list and give me an error.

Alternatively, I could run the query without where statement and filter based on the result. However, in lots of other cases that returns millions of rows, I would prefer filter data at the SQL server level to be more efficient.

I didn't find any relevant samples on the documentation of fsharp.data.sqlclient. Please help!

like image 283
zyzhu Avatar asked Oct 08 '14 02:10

zyzhu


Video Answer


1 Answers

"See Table-valued parameters (TVPs)" section in the documentation: http://fsprojects.github.io/FSharp.Data.SqlClient/configuration%20and%20input.html

like image 199
Petr Avatar answered Oct 28 '22 09:10

Petr