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.
1: 2: |
|
SsdtPath (required)
The SsdtPath must point to a .dacpac file.
Notes:
- A .dacpac file is generated when an SSDT project is built, and can be found in the bin/Debug folder.
- For development, you can set the SsdtPath to point to the generated .dacpac file in the SSDT project Debug folder. (Using a
[<Literal>]
ssdtPath allows relative pathing). - For deployment, the SSDT provider will search for the .dacpac file in the entry assembly folder.
- Linking the generated .dacpac file to your project and setting it to
CopyToOutputDirectory
will ensure that it will exist in the assembly folder for deployment.
1: 2: |
|
Example of the minimal required options for the SSDT provider:
1: 2: 3: 4: |
|
Reloading the schema
It is helpful to keep the above Design Time Command commented out just below your SqlDataProvider type for refreshing the generated types after a schema change.
Optional Parameters
UseOptionTypes
If true, F# option types will be used in place of nullable database columns. If false, 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
1: 2: 3: 4: 5: 6: 7: 8: |
|
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 easily 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 to using the SSDT provider is that it does not require a live connection to the database. This makes it easier to run on a build server without having to manually spin 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 each branch can compile according 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
- User defined data types are not yet supported
- Computed table columns will default to a data type of
System.Object
since the data type is not listed in the .dacpac file. (See Type Annotations below.)
Views
- Computed view columns will default to a data type of
System.Object
since the data type is not listed in the .dacpac file. (See Type Annotations below.)
Type Annotations
As a work-around 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.
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: |
|
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:
1: 2: 3: 4: 5: 6: |
|
Notes:
- If no null constraint is added after the column type, it will allow nulls by default.
- The annotations are case-insensitive.
- Hovering over a generated view property will designate if the data type was derived from a type annotations (or if it needs one).
- Do not include length information in the type annotation. For example, use
varchar
, notvarchar(20)
.
Functions
- Functions are not yet implemented
Individuals
- Get "Individuals" feature is not implemented (because it requires a database connection)
namespace FSharp
--------------------
namespace Microsoft.FSharp
namespace FSharp.Data
--------------------
namespace Microsoft.FSharp.Data
type LiteralAttribute =
inherit Attribute
new : unit -> LiteralAttribute
--------------------
new : unit -> LiteralAttribute
| MSSQLSERVER = 0
| SQLITE = 1
| POSTGRESQL = 2
| MYSQL = 3
| ORACLE = 4
| MSACCESS = 5
| ODBC = 6
| FIREBIRD = 7
| MSSQLSERVER_DYNAMIC = 8
| MSSQLSERVER_SSDT = 9
Calls Linq.QueryBuilder.Select