FSharp.Data.SqlClient


Dynamic creation of offline MDF

Sometimes you don't want to have to be online just to compile your programs. With FSharp.Data.SqlClient you can use a local .MDF file as the compile time connection string, and then change your connection string at runtime when you deploy your application.

1: 
2: 
3: 
4: 
open FSharp.Data

[<Literal>]
let connectionString=@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\git\Project1\Database1.mdf;Integrated Security=True;Connect Timeout=10"

However, binary files like this are difficult to diff/merge when working with multiple developers. For this reason wouldn't it be nice to store your schema in a plain text file, and have it dynamically create the MDF file for compile time?

Well the following scripts can do that for your project.

First create a file called createdb.ps1:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
#this is the name that Fsharp.Data.SqlClient TypeProvider expects it to be at build time
$new_db_name = "Database1" 

$detach_db_sql = @"
use master;
GO
EXEC sp_detach_db @dbname = N'$new_db_name';
GO
"@

$detach_db_sql | Out-File "detachdb.sql"
sqlcmd -S "(localdb)\v11.0" -i detachdb.sql
Remove-Item .

Remove-Item "$new_db_name.mdf"
Remove-Item "$new_db_name.ldf"

$create_db_sql = @"
    USE master ;
    GO
    CREATE DATABASE $new_db_name
    ON 
    ( NAME = Sales_dat,
        FILENAME = '$PSScriptRoot\$new_db_name.mdf',
        SIZE = 10,
        MAXSIZE = 50,
        FILEGROWTH = 5 )
    LOG ON
    ( NAME = Sales_log,
        FILENAME = '$PSScriptRoot\$new_db_name.ldf',
        SIZE = 5MB,
        MAXSIZE = 25MB,
        FILEGROWTH = 5MB ) ;
    GO
"@

$create_db_sql | Out-File "createdb.sql"
sqlcmd -S "(localdb)\v11.0" -i createdb.sql
Remove-Item .

sqlcmd -S "(localdb)\v11.0" -i schema.sql

$detach_db_sql | Out-File "detachdb.sql"
sqlcmd -S "(localdb)\v11.0" -i detachdb.sql
Remove-Item .

Then change your connection string to look like this

1: 
2: 
3: 
4: 
5: 
6: 
[<Literal>]
let connectionStringForCompileTime = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=" + __SOURCE_DIRECTORY__ + @"\Database1.mdf;Integrated Security=True;Connect Timeout=10"

type Foo = SqlCommandProvider<"SELECT * FROM Foo", connectionStringForCompileTime>

let myResults = (new Foo("Use your Runtime connectionString here")).Execute()

Lastly, edit your .fsproj file and add the following to the very end right before </Project>

1: 
2: 
3: 
4: 
<Target Name="BeforeBuild">
    <Message Text="Building out SQL Database: Database1.mdf" Importance="High" />
    <Exec Command="PowerShell -NoProfile -ExecutionPolicy Bypass -Command &quot;&amp; { $(ProjectDir)Createdb.ps1 }&quot;" />
</Target>

Now when you build, it will create a database named Database1 and then look for a file called schema.sql which will be used to create the database. It will then compile against this dynamically generated MDF file so you'll get full static type checking without the hassle of having to have an internet connection, or deal with binary .MDF files!

namespace FSharp
namespace FSharp.Data
Multiple items
type LiteralAttribute =
  inherit Attribute
  new : unit -> LiteralAttribute

Full name: Microsoft.FSharp.Core.LiteralAttribute

--------------------
new : unit -> LiteralAttribute
namespace Microsoft.FSharp.Data
val connectionStringForCompileTime : string

Full name: Dynamic local db.connectionStringForCompileTime
type Foo = obj

Full name: Dynamic local db.Foo
type SqlCommandProvider

Full name: FSharp.Data.SqlCommandProvider



<summary>Typed representation of a T-SQL statement to execute against a SQL Server database.</summary>
<param name='CommandText'>Transact-SQL statement to execute at the data source.</param>
<param name='ConnectionStringOrName'>String used to open a SQL Server database or the name of the connection string in the configuration file in the form of “name=&lt;connection string name&gt;”.</param>
<param name='ResultType'>A value that defines structure of result: Records, Tuples, DataTable, or SqlDataReader.</param>
<param name='SingleRow'>If set the query is expected to return a single row of the result set. See MSDN documentation for details on CommandBehavior.SingleRow.</param>
<param name='ConfigFile'>The name of the configuration file that’s used for connection strings at DESIGN-TIME. The default value is app.config or web.config.</param>
<param name='AllParametersOptional'>If set all parameters become optional. NULL input values must be handled inside T-SQL.</param>
<param name='ResolutionFolder'>A folder to be used to resolve relative file paths to *.sql script files at compile time. The default value is the folder that contains the project or script.</param>
<param name='DataDirectory'>The name of the data directory that replaces |DataDirectory| in connection strings. The default value is the project or script directory.</param>
val myResults : obj

Full name: Dynamic local db.myResults
Fork me on GitHub