SQLProvider

MSSQL SSDT Provider

The SSDT provider allows types to be provided via SQL Server schema scripts in an SSDT project. No live database connection is required!

Parameters

DatabaseVendor (required)

Use MSSQLSERVER_SSDT from the FSharp.Data.Sql.Common.DatabaseProviderTypes enumeration.

[<Literal>]
let dbVendor = Common.DatabaseProviderTypes.MSSQLSERVER_SSDT

SsdtPath (required)

The SsdtPath must point to a .dacpac file.

Notes:

[<Literal>]
let ssdtPath = __SOURCE_DIRECTORY__ + @"/../../files/mssqlssdt/AdventureWorks_SSDT.dacpac"

Example of the minimal required options for the SSDT provider:

type DB = SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER_SSDT, SsdtPath = ssdtPath>

// To reload schema: 1) uncomment the line below; 2) save; 3) recomment; 4) save again and wait.
//DB.GetDataContext().``Design Time Commands``.ClearDatabaseSchemaCache

Reloading the schema

Keeping the above Design Time Command commented out just below your SqlDataProvider type helps refresh the generated types after a schema change.

Optional Parameters

UseOptionTypes

If FSharp.Data.Sql.Common.NullableColumnType.OPTION, F# option types will be used in place of nullable database columns. If NO_OPTION, you will always receive the default value of the column's type even if it is null in the database.

Table Names Filter

The SSDT provider currently supports a simple comma-delimited list of allowed table names (wildcards are not currently supported).

AdventureWorks Example

let ctx = DB.GetDataContext()

let orderDetails =
    query {
        for o in ctx.SalesLt.SalesOrderHeader do
        for d in o.``SalesLT.SalesOrderDetail by SalesOrderID`` do
        select (o.SalesOrderId, o.OrderDate, o.SubTotal, d.OrderQty, d.ProductId, d.LineTotal)
    }

What is SSDT?

SQL Server Data Tools (SSDT) is a modern development tool for building SQL Server relational databases, databases in Azure SQL, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports.

It allows you to quickly compare and synchronize schema changes between your SQL Server database and the current state of your .sql scripts in source control. Schemas can be synchronized bi-directionally (SSDT -> SQL Server or SQL Server -> SSDT).

Advantages of using the SSDT provider

The main advantage of using the SSDT provider is that it does not require a live connection to the database. This makes running on a build server easier without manually spinning up a database instance.

Another advantage is that since your SSDT scripts are checked into your source control, you can easily have different schemas in each branch so that each branch can compile according to its local schema snapshot.

How to create an SSDT Project

SSDT Projects can be created in two ways: * Visual Studio SSDT * Azure Data Studio via the SQL Database Projects Extension

Known Issues

Tables

Views

Type Annotations

As a workaround for computed table and view columns having unresolved data types, the SSDT provider allows you to add type annotations directly to the table or view as in-line comments.

In the SalesOrderDetail.sql example table below, [LineTotal] is a computed column. Since the .dacpac file cannot determine the datatype for computed columns, the data type of the generated property will be defaulted to obj. As a workaround, an in-line type annotation /* MONEY NOT NULL /* can be added. NOTE: for computed table columns, the comment annotation must be contained within the parentheses.

CREATE TABLE [SalesLT].[SalesOrderDetail] (
[SalesOrderID]       INT              NOT NULL,
[SalesOrderDetailID] INT              IDENTITY (1, 1) NOT NULL,
[OrderQty]           SMALLINT         NOT NULL,
[ProductID]          INT              NOT NULL,
[UnitPrice]          MONEY            NOT NULL,
[UnitPriceDiscount]  MONEY            CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount] DEFAULT ((0.0)) NOT NULL,
[LineTotal]          AS               (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0)) /* MONEY NOT NULL */ ),
[rowguid]            UNIQUEIDENTIFIER CONSTRAINT [DF_SalesOrderDetail_rowguid] DEFAULT (newid()) ROWGUIDCOL NOT NULL,
[ModifiedDate]       DATETIME         CONSTRAINT [DF_SalesOrderDetail_ModifiedDate] DEFAULT (getdate()) NOT NULL,
...

In the example dbo.v_Hours view below, the Hours column is not linked back to the dbo.TimeEntries.Hours column in the .dacpac metadata because it is a calculated field, so the data type of the generated property will be defaulted to obj. Adding a type annotation within an in-line comment will inform the SSDT provider of the data type to use in the generated Hours property:

CREATE VIEW dbo.v_Hours
AS
SELECT dbo.Projects.Name AS ProjectName, COALESCE (dbo.TimeEntries.Hours, 0) AS Hours /* decimal not null */, dbo.Users.Username
FROM dbo.Projects
INNER JOIN dbo.TimeEntries on dbo.Projects.Id = dbo.TimeEntries.ProjectId
INNER JOIN dbo.Users on dboUsers.Id = dbo.TimeEntries.UserId
Notes:

Functions

Individuals

Multiple items
namespace FSharp

--------------------
namespace Microsoft.FSharp
Multiple items
namespace FSharp.Data

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

--------------------
new: unit -> LiteralAttribute
[<Literal>] val dbVendor: Common.DatabaseProviderTypes = 9
namespace FSharp.Data.Sql.Common
[<Struct>] type DatabaseProviderTypes = | MSSQLSERVER = 0 | SQLITE = 1 | POSTGRESQL = 2 | MYSQL = 3 | ORACLE = 4 | MSACCESS = 5 | ODBC = 6 | FIREBIRD = 7 | MSSQLSERVER_DYNAMIC = 8 | MSSQLSERVER_SSDT = 9 | DUCKDB = 10 | EXTERNAL = 11
<summary> Specifies the database provider type for the SQL type provider. Each provider has its own specific implementation for SQL generation and data type mapping. </summary>
Common.DatabaseProviderTypes.MSSQLSERVER_SSDT: Common.DatabaseProviderTypes = 9
<summary> Microsoft SQL Server using SSDT/DacPac for schema </summary>
[<Literal>] val ssdtPath: string = "C:\git\SQLProvider\docs\content\core/../../files/mssqlssdt/AdventureWorks_SSDT.dacpac"
type DB = obj
val ctx: obj
val orderDetails: Linq.IQueryable<obj * obj * obj * obj * obj * obj>
val query: Linq.QueryBuilder
val o: obj
val d: obj
custom operation: select ('Result) Calls Linq.QueryBuilder.Select

Type something to start searching.