Database

In this section we'll see how to add data access to our application. We'll use SQL Server for the database - you can use the Express version bundled with Visual Studio. Download the create.sql script to create SuaveMusicStore database.

There are many ways to talk with a database from .NET code including ADO.NET, light-weight libraries like Dapper, ORMs like Entity Framework or NHibernate. To have more fun, we'll do something completely different, namely try an awesome F# feature called Type Providers. In short, Type Providers allows to automatically generate a set of types based on some type of schema. To learn more about Type Providers, check out this resource.

SQLProvider is example of a Type Provider library, which gives ability to cooperate with a relational database. We can install SQLProvider from NuGet: install-package SQLProvider -includeprerelease

Note: SQLProvider is marked on NuGet as a "prerelease". While it could be risky for more sophisticated queries, we are perfectly fine to use it in our case, as it fulfills all of our data access requirements.

If you're using Visual Studio, a dialog window can pop asking to confirm enabling the Type Provider. This is just to notify about capability of the Type Provider to execute its custom code in design time. To be sure the SQLProvider is referenced correctly, select "enable".

Let's also add reference to System.Data assembly.

Having installed the SQLProvider, let's add Db.fs file to the beginning of our project - before any other *.fs file.

In the newly created file, open FSharp.Data.Sql module:

open FSharp.Data.Sql

Next, comes the most interesting part:

type Sql = 
    SqlDataProvider< 
        "Server=(LocalDb)\\v11.0;Database=SuaveMusicStore;Trusted_Connection=True;MultipleActiveResultSets=true", 
        DatabaseVendor=Common.DatabaseProviderTypes.MSSQLSERVER >

You'll need to adjust the above connection string, so that it can access the SuaveMusicStore database. At least you need to make sure that the server instance part is correct. If you're not sure how to configure it, here is a great resource on dealing with connection strings in SQL Server. After the SQLProvider can access the database, it will generate a set of types in background - each for single database table, as well as each for single database view. This might be similar to how Entity Framework generates models for your tables, except there's no explicit code generation involved - all of the types reside under the Sql type defined.

The generated types have a bit cumbersome names, but we can define type aliases to keep things simpler:

type DbContext = Sql.dataContext
type Album = DbContext.``[dbo].[Albums]Entity``
type Genre = DbContext.``[dbo].[Genres]Entity``
type AlbumDetails = DbContext.``[dbo].[AlbumDetails]Entity``

DbContext is our data context. Album and Genre reflect database tables. AlbumDetails reflects database view - it will prove useful when we'll need to display names for the album's genre and artist.

GitHub commit: 88f4e18a1e4f2a590605ebad3284dc9a102cc83d

Files changed:

• Db.fs (added)
• SuaveMusicStore.fsproj (modified)
• create.sql (added)
• packages.config (modified)