We are now ready to access the data repository in the cloud. Doing so is not different than with a local SQL server instance or with a server in our LAN. We need a database driver supported by SQL server and the proper connection string. All the necessary elements can be found in the AWS console.
Once VS is open, go to the Server Explorer and create a new Data Connection using the same credentials used when creating the database in SQL management studio in the previous post
With this, we get our connection active
The connection string to be used in code is pretty basic:
[code] Data Source=sqlsrv1.cw61nfy6i76w.ap-southeast-2.rds.amazonaws.com;Initial Catalog=db1;User ID=sa;Password=sapassword [/code]
Working with data in F# interactive
We are now doing some simple tests with our AWS SQL server database using the following code. You can simply create an empty solution in VS, add a .fsx file (F# script) as a solution item and copy the source.
[code language="fsharp"] #r "System.Data.dll" #r "FSharp.Data.TypeProviders.dll" #r "System.Data.Linq.dll"
open FSharp.Data.TypeProviders
type dbs = SqlDataConnection<"Data Source=sqlsrv1.cw61nfy6i76w.ap-southeast-2.rds.amazonaws.com;Initial Catalog=db1;User ID=sa;Password=sapassword"> let db = dbs.GetDataContext()
let t1 = db.Table1 let nr = new dbs.ServiceTypes.Table1(Name = "Franco", Age = 50uy) t1.InsertOnSubmit(nr)
try db.DataContext.SubmitChanges() with | exn -> printfn "Exception:\n%s" exn.Message
let q = query { for item in db.Table1 do select item }
q |> Seq.iter (fun item -> printfn "%s %d" item.Name item.Age)
[ ("Alan", 15uy) ("Emma", 21uy) ] |> List.iter (fun (n, a) -> t1.InsertOnSubmit(new dbs.ServiceTypes.Table1(Name = n, Age = a))) [/code]
To execute code in F# interactive we simply select the lines we want to execute, right click and select Execute in F# interactive.
Tests
In the next tests we assume that F# interactive is reset and Table1 is empty.
1 - Referencing external libraries
This code initializes F# interactive to reference necessary packages.
2 - Accessing the database
The first line leverages the F# type provider feature. The value db is typed and contains properties and methods directly constructed over the database scheme. This can be seen in the next line let t1 = db.Table1 where Table1 is a built-in property constructed dynamically by the F# data type provider.
It's quite remarkable that it works even when the database service is not a plain SQL server but a virtual server hosted in AWS.
3 - Inserting a record
A new record is now inserted in the cached table, but not yet in the actual table. For this the changes to the cache must be committed; we'll do this in the next step
4 - Committing changes
SubmitChanges succeeds (if not we would get an error printout as per the with branch of the try|with. As you can see refreshing the table (right pane), the record has been successfully inserted in the table. We can see this also in SQL server management studio
5 - Reading data
We see an example of the F# capability to operate with LINQ through a simple F# Query Expression. Query Expressions are one of the many remarkable features F# has to offer. Notice how concise and clear the code is: table structure, field names and types are directly available to the programmer, no worries about data conversion and indirect addressing.
5 - Insert list
At this point, we have successfully tested the full chain and are confident about the health of the environment we built. Let's therefore leverage on F# functional patterns to insert a list of record
No errors, so, to complete the operation we have to commit, repeating step 4
Beautiful: after refreshing the table we see that all records are now saved in the database. Let's repeat step 5 to confirm we read all
Notice that I only select the last line (unlike in step 5); in fact, q as returned by let is an IQueryable, so it is dynamically executed on the current database content. We don't need to reassign it as long as the scheme remains the same.
Conclusion
As you probably noticed, I didn't fulfill the promise in the title of this post, since I only show Create and Read examples, not Update and Delete (CRUD); this is to leave to the reader the opportunity to have a go and exercise.
For those who like, I built a video showing the complete sequence regarding this series. Enjoy.