Learning F# - Step 5

The next step for me in learning a new language is interacting with a database. This step has kicked my butt!

Since F# is a Microsoft-sponsored language, I expected to find a straightforward approach to interacting with a SQL database. That wasn't the case!

When I searched for data access within F#, I was taken to this page. I wanted to work with a basic SQL database, so I clicked the link for SQL Data Access. I was pleasantly surprised to find several options for SQL data access. The first one I chose was FSharp.Data.SqlClient. I chose this one because it looked very similar to the SQL data access provided in C# by the System.Data.SqlClient namespace.

I jumped straight into implementing the sample code on the front page of FSharp.Data.SqlClient. As I built out the example and started to run it, I ran into errors that didn't make sense to me. As I read about this package, I realized it worked with System.Data.SqlClient, and I needed to add it to my program. I did that. I still had errors. As I read more about the errors I was seeing, I found that there is a dependency on mono for this package. In most cases, this wouldn't be a big deal, but it is for me at the moment.

I am doing most of my exploration of F# using .NET Core on a Macbook. As .NET has moved to support cross-platform development, they have relied on mono at times to supply functionality, so it isn't a surprise to learn that mono may be required for some packages still. For this particular learning step in my F# journey, I'm trying to minimize the complications and differences between F# on a Windows platform and cross-platform F#. To that end, I decided to use System.Data.SqlClient for this step.

The first thing I need to do is to set up a SQL database. For this purpose, I'm using SQL Server 2019 (actually running in a Docker image). I need to create a database and a table. Below are the T-SQL commands to do both:

create database FSharp

create table PlayingCard (
  ID int,
  CardValue varchar(5),
  Suit varchar(8)
)

Now that I have a database available, I can start working with it.

When I'm learning to use a new language and interact with a database, I try to do 2 basic operations: insert data and read data. Since I'm using System.Data.SqlClient and I'm familiar with this package in C#, I know that I need to create a SqlConnection, create a SqlCommand with my SQL statement, and then execute the command to perform the operation.

In F#, I start by making sure the package is available to my code with the open System.Data.SqlClient call. I then created my connection string for the database. I encountered the attribute Literal for the first time in F#. This annotation effectively creates a "constant" value. Now I'm ready to work with the database.

C# has a construct called using that allows a developer to open a resource (such as a file handle or database connection) in such a way that the compiler is told to dispose of the resource using the language construct instead of the developer calling it directly. It turns out, F# has the same construct!

I start my database interation by calling using (new SqlConnection(connString)). This instantiates a new SqlConnection. It then passes the connection to the function that I pass into the using call. In my case, I create an anonymous function by declaring it and creating it within the using block. An anonymous function is declared using the fun keyword, followed by any parameters that the function takes.

The first thing I do in my function is open the connection. The last thing I do is close the connection. This ensures that I don't abuse the connections available from the database.

Now, I want to put some data into the database so that I can try to read it back out. I create a new SqlCommand with my INSERT statement (values are hard coded) and the connection. I then execute the command with the ExecuteNonQuery call.

To read the data, I create a new SqlCommand with a SELECT statement to read all rows from my table and the connection. I execute the command with ExecuteReader and receive a SqlDataReader back. To get the values from the SqlDataReader, I iterate over the reader with a while loop and print the results to the console.

I started with an F# console app and added the data access package with the call:

dotnet add package System.Data.SqlClient

Here is the full code:

open System.Data.SqlClient

[<Literal>]
let connString = @"Data Source=.;Initial Catalog=FSharp;User=dbuser;Password=MyStrongPassword"
    
[<EntryPoint>]
let main argv =

    // System.Data.SqlClient
    using (new SqlConnection(connString)) ( fun conn ->
        conn.Open()
                                            
        let insertCmd = new SqlCommand("INSERT INTO PlayingCard (ID, CardValue, Suit) VALUES (3, '2', 'Diamonds')", conn)
        let count = insertCmd.ExecuteNonQuery()
                                            
        let readCmd = new SqlCommand("SELECT * FROM PlayingCard", conn)
        let reader = readCmd.ExecuteReader()
        while reader.Read() do
            printfn "%A %A %A" reader.[0] reader.[1] reader.[2]
            
        conn.Close()
    )
    
    0

If you are familiar with C# and interacting with a database in C#, this code is very comfortable and familiar. It also gives me another tool in my F# arsenal.

NOTE #1: F# has another construct for working with resources call use. It works similarly to using. I have not fully figured out the difference between the two versions.

NOTE #2: My "trick" to open and close the database connections in the same place is a common design pattern when working with resources such as files and databases. This pattern probably warrants its own blog post.

NOTE #3: I don't really like that I resorted to System.Data.SqlClient. It feels like I'm abusing the availability of C# libraries in the .NET space to get through a task. I need to do more research into F# data access to better understand the space and to find a more idiomatic approach to data access.

NOTE #4: Most of the code in this sample is procedural and not functional. I REALLY don't like this fact. I used it because it has the practical aspect that now I can start creating more meaningful applications with F#, but it hasn't helped me get better at thinking functionally.