F sharp saving record type into access db

Question

I have the following record type "tbl" and it will contain lists or seq. How can i save/update this "tbl" into an existing access db table using f sharp with the field names as specified below.

type Tbl= {     P:string; 
                In:System.DateTime; 
                Ex:System.DateTime;
                B:string; 
                CC:string; 
                GG:double; 
                PR:double;
                DE:double;
                PRE:double; 
                DEU:double; 
                PRPL_DEDUC:double; 
                GUR:double; 
                GC:double; 
                PRP:double; 
                PDA:double; 
                PRO:double}

let conn = new OleDbConnection( @"Provider=Microsoft.ACE.OLEDB.12.0;
          Data Source=T:\Test.accdb;
           Persist Security Info=False;" )
conn.Open()    

let sql="INSERT INTO Test SELECT * FROM " Tbl
let DAdapter = new OleDbDataAdapter(sql,conn)
DAdapter.Update

Answer 1

There's a couple of things that come to mind here.

Firstly, I think you're tripping up on the interpretation of the record type - it isn't a recordset - so you can't just pump into an oledbdataadapter.

There's also not much support for Access in the F# type providers - I'm losing touch with how many are there but as an example SqlProvider lets you read from Access but not perform CRUD actions.

So, at least as far as I know, the old fashioned oledb approach you've got above is actually the right path here.

The code below uses SqlProvider to retrieve the contents of an access db with 3 columns, the saveARow function won't work and then it goes on to do it the oledb way...

#r @"packages\SQLProvider.0.0.9-alpha\lib\net40\FSharp.Data.SqlProvider.dll"

open System
open System.Linq
open FSharp.Data.Sql

[<Literal>]
let cnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb;Persist Security Info=False;"

type sql = SqlDataProvider<
                ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb;Persist Security Info=False;",
                DatabaseVendor = Common.DatabaseProviderTypes.MSACCESS>
let ctx = sql.GetDataContext()

// return some data
ctx.``[Database1].[Table1]`` |> Seq.take 1

// create a type to represent the data
type Row = {P: string; In:System.DateTime; GG:double}

// insert some more data P:string, In:datetime, Ex:datetime, B:string, GG:number
// unfortunately SqlDataProvider doesn't support CRUD operations on MS Access...
let saveARow (aRow: Row) =
    let table1 = ctx.``[Database1].[Table1]``.Create()
    table1.P <- aRow.P
    table1.In <- aRow.In
    table1.GG <- aRow.GG
    ctx.SubmitUpdates()

{P="abc"; In=DateTime.Now; GG=200.}
|> saveARow

// try it the old fashioned way
open System.Data
open System.Data.OleDb

#r @"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5.2\System.Transactions.dll"
open System.Transactions

let saveARow2(aRow: Row) =
    let cn = new OleDbConnection(cnStr)
    cn.Open()
    let cmd = new OleDbCommand()
    cmd.CommandType <- CommandType.Text
    let insertCmd = sprintf "insert into Table1 values ('%s', '%s', '%f')" aRow.P (aRow.In.ToShortDateString()) aRow.GG
    cmd.CommandText <- insertCmd
    cmd.Connection <- cn
    cmd.ExecuteNonQuery() |>ignore
    cn.Close()

{P="abcd"; In=DateTime.Now; GG=200.}
|> saveARow2

Answer 2

Why sequence are not array or list? Maybe better rewrite (i took the data from your other question):

type test = { G:double; P:double; GG:double; PP:double } 

let table = [for x in 0..(Un0.Length - 1) -> 
                let b = Un0.[x] in 
                if b=0.0 then {G=0.0; P=0.0; GG=0.0; PP=0.0}
                else {G=G_0.[x]/b; P=P0.[x]/b; GG=G0.[x]/b; PP=PP0.[x]/b}]

let str = @"Provider=Microsoft.ACE.OLEDB.12.0;
 Data Source=T:\Test.accdb;
 Persist Security Info=False;"

let conn = new OleDbConnection(str) 

let query text = 
    conn.Open() 
    use comm = conn.CreateCommand()
    comm.CommandText <- text
    comm.ExecuteNonQuery() |> ignore
    conn.Close()

let format = "INSERT INTO Test values ({0}, {1}, {2}, {3});"

table |> List.iter(fun x -> query (String.Format(format, x.G, x.P, x.GG, x.PP)))