Using ADO.NET from F# – Examples 0

ADO.NET is dead and replaced many years ago but this week I needed to write a bit of ADO.NET against SQL Server to test out Prepared statements and server side cursors for an application that requires to support it.

Here is the fully working example that covers off the bits I needed:

You need to add “ADODB” as a reference in your project.

 
open ADODB
 
[<EntryPoint>]
let main argv = 
    printfn "%A" argv
 
    let conn = new ADODB.ConnectionClass()
 
    conn.Open( "Provider=SQLNCLI11;Data Source=?;Initial Catalog=master;Uid=?;Pwd=?" ) |> ignore
    conn.DefaultDatabase <- "TPCH"
 
    let cmd = new ADODB.CommandClass()
 
    cmd.let_ActiveConnection(conn)
    cmd.CommandTimeout <- 0
 
    //  Single row
    cmd.CommandType <- CommandTypeEnum.adCmdText
    cmd.CommandText <- "SELECT DB_NAME() as DB"
 
    let rs = cmd.Execute()
 
    let db = rs.Fields.Item("DB").Value.ToString()
    rs.Close() 
 
    printfn "Database: %s" db
 
    //  Multiple rows    
    cmd.CommandText <- "SELECT name FROM sys.objects WHERE type = 'U' ORDER BY name"
 
    let mutable rs = new ADODB.RecordsetClass()
 
    rs.CursorLocation <- CursorLocationEnum.adUseServer
    rs.CursorType <- CursorTypeEnum.adOpenForwardOnly
 
    rs.Open( cmd)
 
    while not rs.EOF do
 
        printfn "%s" (rs.Fields.Item("name").Value.ToString())
 
        rs.MoveNext()
 
    rs.Close()
 
    //  Prepared statements
 
    cmd.CommandText <- "SELECT name FROM sys.objects WHERE type = ? ORDER BY name"
    cmd.Prepared <- true
 
    let mutable cmd_prm1 = cmd.CreateParameter("type", 
                                               DataTypeEnum.adChar, 
                                               ParameterDirectionEnum.adParamInput, 
                                               2)
    cmd.Parameters.Append(cmd_prm1)
 
    //  Call 1
    cmd.Parameters.Item("type").Value <- "U"
 
    let mutable rs = new ADODB.RecordsetClass()
 
    rs.CursorLocation <- CursorLocationEnum.adUseServer
    rs.CursorType <- CursorTypeEnum.adOpenForwardOnly
 
    rs.Open( cmd )
 
    while not rs.EOF do
 
        printfn "%s" (rs.Fields.Item("name").Value.ToString())
 
        rs.MoveNext()
 
    rs.Close()
 
    //  Call 2
    cmd.Parameters.Item("type").Value <- "P"
 
    let mutable rs = new ADODB.RecordsetClass()
 
    rs.CursorLocation <- CursorLocationEnum.adUseServer
    rs.CursorType <- CursorTypeEnum.adOpenForwardOnly
 
    rs.Open( cmd )
 
    while not rs.EOF do
 
        printfn "%s" (rs.Fields.Item("name").Value.ToString())
 
        rs.MoveNext()
 
    rs.Close()
 
 
    // The End
 
    conn.Close()
 
    0 // return