SQLProvider

MSSQL Provider

SQLProvider with Microsoft SQL Server (and SSDT) is available via both NuGet-Packages:

Parameters

DatabaseVendor

From the FSharp.Data.Sql.Common.DatabaseProviderTypes enumeration. For MSSQL, use Common.DatabaseProviderTypes.MSSQLSERVER.

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

ConnectionString

A basic connection string used to connect to MSSQL instance; typical connection strings for the driver apply here. See MSSQL Connecting Strings Documentation for a complete list of connection string options.

let [<Literal>] connString = "Server=localhost;Database=test;User Id=test;Password=test"

ConnectionStringName

Instead of storing the connection string in the source code / fsx script, you can store values in the App.config file. This is the name of the connectionString key/value pair stored in App.config.

// found in App.config
let [<Literal>] connexStringName = "DefaultConnectionString"

IndividualsAmount

Sets the count to load for each individual. See individuals for further info.

let [<Literal>] indivAmount = 1000

UseOptionTypes

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

let [<Literal>] useOptTypes  = FSharp.Data.Sql.Common.NullableColumnType.OPTION

type sql =
    SqlDataProvider<
        dbVendor,
        connString,
        IndividualsAmount = indivAmount,
        UseOptionTypes = useOptTypes>

Because MSSQL databases can be huge, there is an optional constructor parameter TableNames that can be used as a filter.

Using Microsoft.Data.SqlClient.dll instead of build-in System.Data.SqlClient.dll

To use another driver, Microsoft.Data.SqlClient.dll, you have to set your provider to Common.DatabaseProviderTypes.MSSQLSERVER_DYNAMIC and copy the reference files from the NuGet package to local resolutionPath (e.g. Microsoft.Data.SqlClient.dll, Microsoft.Data.SqlClient.SNI.dll and Microsoft.Data.SqlClient.SNI.x86.dll).

Using SQLProvider with SSDT

You can use SQLProvider with SSDT.

Multiple items
namespace FSharp

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

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

--------------------
new: unit -> LiteralAttribute
[<Literal>] val dbVendor: Common.DatabaseProviderTypes = 0
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
Common.DatabaseProviderTypes.MSSQLSERVER: Common.DatabaseProviderTypes = 0
[<Literal>] val connString: string = "Server=localhost;Database=test;User Id=test;Password=test"
[<Literal>] val connexStringName: string = "DefaultConnectionString"
[<Literal>] val indivAmount: int = 1000
[<Literal>] val useOptTypes: Common.NullableColumnType = 1
[<Struct>] type NullableColumnType = | NO_OPTION = 0 | OPTION = 1 | VALUE_OPTION = 2
Common.NullableColumnType.OPTION: Common.NullableColumnType = 1
<summary> Option types are Option&lt;_&gt;. (Old true.) </summary>
type sql = obj
type SqlDataProvider
<summary>Typed representation of a database</summary> <param name='ConnectionString'>The connection string for the SQL database</param> <param name='ConnectionStringName'>The connection string name to select from a configuration file</param> <param name='DatabaseVendor'> The target database vendor</param> <param name='IndividualsAmount'>The amount of sample entities to project into the type system for each SQL entity type. Default 50. Note GDPR/PII regulations if using individuals with ContextSchemaPath.</param> <param name='UseOptionTypes'>If set, F# option types will be used in place of nullable database columns. If not, you will always receive the default value of the column's type even if it is null in the database.</param> <param name='ResolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types. Types used in desing-time: If no better clue, prefer .NET Standard 2.0 versions. Semicolon to separate multiple.</param> <param name='Owner'>Oracle: The owner of the schema for this provider to resolve. PostgreSQL: A list of schemas to resolve, separated by spaces, newlines, commas, or semicolons.</param> <param name='CaseSensitivityChange'>Should we do ToUpper or ToLower when generating table names?</param> <param name='TableNames'>Comma separated table names list to limit a number of tables in big instances. The names can have '%' sign to handle it as in the 'LIKE' query (Oracle and MSSQL Only)</param> <param name='ContextSchemaPath'>The location of the context schema previously saved with SaveContextSchema. When not empty, will be used to populate the database schema instead of retrieving it from then database.</param> <param name='OdbcQuote'>Odbc quote characters: Quote characters for the table and column names: `alias`, [alias]</param> <param name='SQLiteLibrary'>Use System.Data.SQLite or Mono.Data.SQLite or select automatically (SQLite only)</param> <param name='SsdtPath'>A path to an SSDT .dacpac file.'</param>

Type something to start searching.