The following code shows a simple example that uses the SqlDataConnection type provider to retrieve a player and display its name. Of course, you will have to make the necessary changes to the ConnectionString in order to establish a connection with your database.
module PlayersAnalysis =
open System
open Microsoft.FSharp.Data.TypeProviders
type SqlServerConnection = SqlDataConnection<ConnectionString =
@"Data Source=WIN8X64;Initial Catalog=Retrogames;Integrated Security=True">
let db = SqlServerConnection.GetDataContext()
let GetPlayerById(id) =
query {
for player in db.Player do
where (player.PlayerId = id)
select player
head
}
let ReadAnyKey() = Console.ReadKey() |> ignore
let onePlayer = GetPlayerById 5
onePlayer.Name
|> printfn "The player's name is %s"
ReadAnyKey()
If you've worked with previous F# versions, you will notice that I didn't mention the need of an F# PowerPack when working with query expressions. F# 3.0 doesn't require F# PowerPack — query expressions provide support for LINQ in F# without requiring you to download additional libraries. All you have to do is include the appropriate references.
The previous code snippet is easy to understand. SqlServerConnection is the type abbreviation (also known as alias) for the SqlDataConnection type provider with the specified connection string. The GetDataContext static method gets a simplified data context for the SQL connection. db is of type SqlServerConnection.ServiceTypes.SimpleDataContextTypes.Retrogames. If you enter the following lines, you will notice how Intellisense makes it easy to generate the query when you enter db.P because you have two valid options: Player and PlayerScore (see Figure 10).
let GetPlayerById(id) =
query {
for player in db.P
Figure 10: Intellisense makes it easy to know the entity name.
As you continue building your query expression, Intellisense provides you with additional information. For example, after you write player. in a where statement, Intellisene displays the different fields available for the Player entity (see Figure 11).
query {
for player in db.Player do
where (player.
Figure 11: Intellisense makes it easy to know the field name.
Once you created the GetPlayerById function and you write the line that defines onePlayer, the type inference mechanism knows that onePlayer is of type SqlServerConnection.ServiceTypes.Player:
let onePlayer = GetPlayerById 5
Thus, after you write onePlayer, Intellisene will display the different fields available for the Player entity (see Figure 12). This way, you focus on the code and you can consume data in the same way you are accustomed to in Microsoft SQL Server Management Studio, but focusing primarily on creating your F# algorithms.
Figure 12: F# type inference + Intellisense provide you with the field names for onePlayer.
In this example, I specified the connection string in an unsecure way and without additional parameters. If you're building a library or an application, you need to make sure you build the connection string in a different way. However, I'm just providing an example of how to create quick prototypes for your algorithms with live connection to the data source. In addition, you will need to consider other parameters for the connection string, such as the timeout and the parameters that control the database elements that you want to be exposed as types.
In this first article, I've provided a brief overview of type providers and one of the options to establish a quick connection to a SQL database and start creating F# algorithms for this kind of data source. Now, you know all the necessary steps to start using the different type providers for SQL data sources. For example, if you are already using Entity Framework, you can select the appropriate type provider (either EdmxFile or SqlEntityConnection) and take advantage of all the mappings effort done through the ORM.
Obviously, the advantages of type providers are even more important when the data sources are unknown and more complex. In the More Information-Rich Programming with F#
