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 `map`s here (use the `INSERT` *inside* the `iter`) — Random Dev, Aug 09 '15 at 13:33
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, Aug 09 '15 at 13:38
are there any other efficient ways of inserting data into ms access? — crumbly, Aug 09 '15 at 13:40
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, Aug 09 '15 at 13:45
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, Aug 09 '15 at 13:57
Even if you iterate in parallel, individual inserts are still going to be serialized on Access side. Access can't do multiple inserts into same table truly in parallel. You really need to find out a way to bulk-insert into Access, similar to `SqlBulkInsert` for SQL server. This has nothing to do with F#. — Fyodor Soikin, Aug 09 '15 at 16:05
If you use `INSERT INTO Table VALUES`, you should explicitly write column names `INSERT INTO Table (col1, col2, ...) VALUES`. It is more readable and safer. Second check also [Writing large number of records to Access](http://stackoverflow.com/questions/7070011/writing-large-number-of-records-bulk-insert-to-access-in-net-c) — Lukasz Szozda, Aug 09 '15 at 19:33

score 0 · Answer 1 · edited May 23 '17 at 12:06

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 Answers1