Tuesday, June 19, 2012

HOW TO: Connect to SQL Server Compact from F#

I have previously blogged about connection to SQL Server Compact from other technologies than pure ADO.NET:

ASP Classic

VBA (Excel)

Silverlight

PowerShell

F# version 3.0 in Visual Studio 2012 makes it relatively easy to connect to a SQL Server Compact database (I am NOT a F# programmer, and even I could connect, so it must be very easy!).

In this blog post I will show the required steps to connect to a SQL Server Compact 4.0 database file and start using it via Entity Framework with F#.

In VS 2012 (currently RC), create a new F# application:

image

Now add references to the required libraries (Entity Framework and F# Type Providers):

FSharp.Data.TypeProviders
System.Data.Entity
System.Data.Linq

image

I order to access SQL Server Compact via EF 4 from F#, and Entity Data Model file (.EDMX) file is required. And the EDMX Wizard and Designer only works with C# and VB.NET projects, so to create this, add a Visual C# Console project to the solution:

image

Now add the EDMX file to this project, right click the project, select Add, New Item…, Data,

image

Select generate from database, and point to the desired SQL Server Compact 4.0 database.

Just say no:

image

The Console project now contains an EDMX file, and a useful connection string in app.config.

Now go back to the F# project clear program.fs, and add the following “using” statements:

open System
open System.Data.Linq
open System.Data.EntityClient
open Microsoft.FSharp.Data.TypeProviders



Grab the connectionString value from the console project app config, and add this line:

let internal connectionString = "metadata=res://*/;provider=System.Data.SqlServerCe.4.0;provider connection string='data source=C:\\Data\\SQLCE\\Test\\nw40.sdf';"



Notice the changes to the provider connection string - =" has been change to single quote, and backslashes have been escaped.


Now add the EDMX file from the Console project to the F# project as Content:


image


You can now initialize the EF type provider like so:

type internal edmx = EdmxFile<"NWModel.edmx">



And start using the context like this:

let internal context = new edmx.nw40Model.nw40Entities(connectionString)

let internal res = query { for supplier in context.Suppliers do select supplier }

res|> Seq.iter (fun supplier -> printfn "%s" supplier.Company_Name)

let wait = Console.ReadKey()