ExecuteNonQuery from SQLHelper

Question

I found this SQLHelper online that I would like to run a SQL query with. But the helper wants an list instead of an string. and I cannot seem to figure out how to make the executeNonQuery to work.

   type SqlHelper (connection) =
        let exec bind parametres query = 
            use conn = new SqlConnection (connection)
            conn.Open()
            use cmd = new SqlCommand (query, conn)
            parametres |> List.iteri (fun i p -> 
                            cmd.Parameters.AddWithValue(sprintf "@p%d"  i, box p) |> ignore)
            bind cmd
    
        member __.Execute = exec <| fun c -> c.ExecuteNonQuery() |> ignore
        member __.Scalar  = exec <| fun c -> c.ExecuteScalar()
        member __.Read f  = exec <| fun c -> [ let read = c.ExecuteReader()
                                               while read.Read() do 
                                                   yield f read ]
let sql = new SqlHelper (connectionString)

The query I have is for dopping the tables

and I'm trying to execute like this.

let emptyDb =
    let query =
    "SET NOCOUNT ON
      DROP TABLE IF EXISTS #STUFF
      ...
      ...    
      END"
sql.Execute [query ]

This compiles, but nothing happens when I execute it. Any ideas? Thanks in advance

Edit: sql.Read function works perfect

let GetToken Id= 
    sql.Read (fun r -> { token = unbox r.[0] })
             [Id;] 
             "SELECT Token
             FROM [dbo].[Token]
             WHERE id= 0"

GetToken "1337"

It appears you're not providing enough parameters to `sql.Execute` — Fyodor Soikin, Jan 05 '21 at 14:09
@FyodorSoikin any suggestion? I can make the sql.Read function work without a problem. but Execute is giving me gray hair. — Dymond, Jan 05 '21 at 14:16

score 3 · Accepted Answer · answered Jan 05 '21 at 15:02

You are not providing enough parameters for sql.Execute.

Look closely:

exec takes three parameters - bind, parametres (btw, typo), and query
In the body of Execute you give it one parameter - bind
Therefore, the result of Execute is a function that still expects the other two parameters - parametres and query
But when you're calling sql.Execute, you're only giving it one parameter - [query], which will end up bound to parametres

Therefore, the result of calling sql.Execute [query] is yet another function, which still expects the final parameter to be provided before its body will be executed. In fact, if you pay close attention to compiler warnings, you will see that the compiler actually tells you as much:

This expression is a function value, i.e. is missing arguments. Its type is ...

To fix, provide the correct parameters. Judging by the little piece of your query that I can see, I assume that it's not supposed to have any parametres, so I'll put an empty list there:

sql.Execute [] query

ExecuteNonQuery from SQLHelper

1 Answers1