F# store data in ms access table

Question

how can i export arrays to ms access table using OleDbConnection. Below is the code i have so far.

  let A=y|>Array.map (fun x->x.a) 
  let B=y|>Array.map (fun x->x.b) 
  let C=y|>Array.map (fun x->x.c) 
  let D=y|>Array.map (fun x->x.d) 

  let cnn = @"Provider=Microsoft.ACE.OLEDB.12.0;
    Data Source=U:\test.accdb;
    Persist Security Info=False;" 
  let conn = new OleDbConnection(cnn) 
  conn.Open() 
  use com = conn.CreateCommand()
  com.CommandText <- "INSERT INTO Test values" {A,B,C,D}
  com.ExecuteNonQuery() |> ignore
  conn.Close()

maybe you should include all the issues you have - also: do you know how to insert a single row? I would consider using Array.iter instead of multiple maps here (use the INSERT inside the iter) — Random Dev
do i have to use array.iter? is it possible to insert the whole array with single insert? the code will run much much slower if i do iter — crumbly
are there any other efficient ways of inserting data into ms access? — crumbly
to be honest I have no clue if you can do some kind of bulk-insert into MS-Access - but as you surely saw what you did will not work at all - you will have to map your stuff to some kind of sql-string (and yeah I guess I have to mention: you probably don't wanna do this because of SQL-injection attacks) or parameters - but this is not exactly a F# problem - it's really an ODBC/MS-Access thing — Random Dev
you are absolutely right,it's not working. If I adopt the Array.iter approach and store data row by row, is it possible to iterate it faster using some kind of parallel functionality. Otherwise are there any SqlDataproviders for ms access? — crumbly

Fyodor Soikin Fyodor Soikin · Accepted Answer · 2015-08-09T22:27:30

In order to insert multiple rows, you need to issue multiple commands. Access doesn't allow inserting multiple values with one insert statement, see this question for more: Insert multiple rows using one insert statement in Access 2010

As long as you're fine with using multiple statements, you have to prepare the command correctly - that is, include the actual values after the VALUES part. And while you're at it, you should use parameters, too, if you don't want to be vulnerable to SQL injection. Like this:

use com = conn.CreateCommand()
com.CommandText <- "INSERT INTO Test values( ?, ?, ?, ? )"

let param name = 
    let p = com.CreateParameter()
    p.ParameterName <- name
    com.Parameters.Add p
    p
let pA, pB, pC, pD = param "@a", param "@b", param "@c", param "@d"

y |> Array.iter (fun x ->
    pA.Value <- x.a
    pB.Value <- x.b
    pC.Value <- x.c
    pD.Value <- x.d
    com.ExecuteNonQuery() )

conn.Close()

If you really want to speed it up, you'll have to find a way to bulk-insert records into Access, similar to SqlBulkInsert for SQL server. I don't know of such way, if it even exists.

Alternatively, you can create a commands consisting of N insert statements (where N = y.Length), but then that statement will have 4N parameters, and I believe Access had a limitation on the number of parameters.

Alternatively-alternatively, you can do the above, but without parameters, inserting values right into the text of the SQL command:

let commandText = 
    y 
    |> Seq.map (fun x -> 
        // assuming all four parameters are strings
        sprintf "INSERT INTO Test values( '%s', '%s', '%s', '%s' )" x.a x.b x.c x.d 
    |> String.concat " "

use com = conn.CreateCommand()
com.CommandText <- commandText
com.ExecuteNonQuery()
conn.Close()

But in that case, you open yourself up to SQL injection attacks. You'll have to sanitize your array values very carefully to prevent it. I do not recommend doing this just to gain a few extra seconds in performance.

And speaking of performance: are you absolutely sure multiple insert statements are going to be too slow for your purposes? Why? Have you measured it? How?
Remember the golden rule of performance tuning: first measure, then optimize.

F# store data in ms access table

1 Answers