Adventures in F# - F# and ADO.NET

Today's post is rather simplistic in what it does, but it illustrates some key language elements of F#, such as exception handling. 

The task to accomplish is quite it easy, nameley, to connect to a SQL Server 2005 database called "TestDB" on my local machine, using ADO.NET to pull out some rows of the "Person" table having the following schema:

Person (

  PersonID: int;

  FirstName nvarchar(50);

  LastName nvarchar (50);

  Street nvarchar(50);

  City nvarchar(50);

  ZIP int

)

The code we use in F# is a rather straightforward conversion of some typical C# code, and as such is probably not the best way to do things, but it gets the task done, and may illustate the point.

#light

We use the lightweight syntax option.

open System.Data;
open System;
open System.Data.SqlClient;

As a next step, we have to open the correct namespaces.

let ADONetTest = 
  let conStringBuilder = new SqlConnectionStringBuilder()
  conStringBuilder.IntegratedSecurity <- true
  conStringBuilder.["Data Source"] <- "localhost"
  conStringBuilder.InitialCatalog <- "TestDB"
  let con = new SqlConnection(conStringBuilder.ConnectionString)

These lines create the connection to the SQL Server 2005 database "TestDB" on my local machine. As you may see I use a mixture of passing values to properties (IntegratedSecurity, InitialCatalog) and the indexer (["Data Source"]) that is defined on the SqlConnectionStringBuilder type.

  try
    con.Open()
    let cmdString = "SELECT * FROM [dbo].[Person]"
    let cmd = new SqlCommand(cmdString, con)
    cmd.CommandType <- CommandType.Text
    using (cmd.ExecuteReader()) (fun reader ->
      if (reader.HasRows) then
        (
          printfn "Queried: %s" cmdString
          while (reader.Read()) do
            (
              let id = reader.GetInt32(0)
              let firstName = reader.GetString(1)
              let lastName = reader.GetString(2)
              let street = reader.GetString(3)
              let city = reader.GetString(4)
              let zip = reader.GetInt32(5) 
              printfn "%d %s %s %s %s %d" id firstName lastName street city zip
            )
          done
        )
      else (printfn "Empty result")
    )
    con.Close()
  with
    | :? System.Data.SqlClient.SqlException as sqlEx -> printfn "%A" sqlEx.Message
    | _ -> printfn "Unknown Exception"
 

  (con :> IDisposable).Dispose ()
;;

This part is responsible for the main functionality. We open the previously specified connection. We do this in a try-with block, to be prepared in case of exceptions. We actually try to handle one exceptio, the SqlExceptio. In the with block, we use type/pattern matching rule (|:? SqlException) to catch this particular exception. Since we do not have a handling strategy in place, a printf command is issued.The next with rule gets executed on whatever type not matching SqlException. We indicate that we do not even care about what it is (| _) using an anonymous rule.

Inside our try block, we declare a SqlCommand and pass in the Sql query stored in the cmdString. variable. This command encapsulates a Select-From-Where SQL-query. We execute the query, using one of F#'s equivalent's of the C# using statement - the using statement (the other one would be use).

The using keeps track for us, that in the case of an exception the "Dispose" method of the object used in the using statement is called - in this case it would be the Dispose declared by the result of  SqlCommand.ExecuteReader() - a DataReader object.

Inside the using block, it is asked whether the result of the query (DataReader) actually has rows that were returned by our particular query. This is done by if (reader.HasRows) then () else ().In case rows were returned by that query,they are enumerated by the while do () done loop. This loop does nothing but print each row present in the DataReader.

The last statement to mention is the  (con :> IDisposable).Dispose (). This statement casts the connection object into the IDisposable and calls the Dispose method on it. This has to be done, since it is not automatically called by a try-with block, and we have to ensure that all used objects are properly disposed of - espacially those that abstract native resources, such as database or file connections.