SQLProvider


SQLProvider

A general .NET/Mono SQL database type provider. Current features:

The provider currently has explicit implementations for the following database vendors:

There is also an ODBC provider that will let you connect to any ODBC source with limited features.

All database vendors except SQL Server and MS Access will require 3rd party ADO.NET connector objects to function. These are dynamically loaded at runtime so that the SQL provider project is not dependent on them. You must supply the location of the assemblies with the "ResolutionPath" static parameter.

SQLite is based on the .NET drivers found here. You will need the correct version for your specific architecture and setup.

PostgreSQL is based on the Npgsql .NET drivers found here. The type provider will make frequent calls to the database. Npgsql provides a set of performance related connection strings parameters for tweaking its performance

MySQL is based on the .NET drivers found here. You will need the correct version for your specific architecture and setup. You also need to specify ResolutionPath, which points to the folder containing the dll files for the MySQL driver.

Oracle is based on the current release (12.1.0.1.2) of the managed ODP.NET driver found here. However, although the managed version is recommended, it should also work with previous versions of the native driver.

SQL Server SSDT is based on the current release (160.20216.14) found here.

The library can be installed from NuGet:
PM> Install-Package SQLProvider

Example

No OR-mapping: FSharp compiles your database to .NET-types.

This example demonstrates the use of the SQL type provider:

// reference the type provider dll

#r "../../bin/netstandard2.0/FSharp.Data.SqlProvider.dll"

open FSharp.Data.Sql

let [<Literal>] resolutionPath = __SOURCE_DIRECTORY__ + @"/../files/sqlite" 
let [<Literal>] connectionString = "Data Source=" + __SOURCE_DIRECTORY__ + @"\northwindEF.db;Version=3;Read Only=false;FailIfMissing=True;"
// create a type alias with the connection string and database vendor settings
type sql = SqlDataProvider< 
              ConnectionString = connectionString,
              DatabaseVendor = Common.DatabaseProviderTypes.SQLITE,
              SQLiteLibrary=Common.SQLiteLibrary.SystemDataSQLite,
              ResolutionPath = resolutionPath,
              IndividualsAmount = 1000,
              UseOptionTypes = Common.NullableColumnType.OPTION
              >
let ctx = sql.GetDataContext()

// To use dynamic runtime connectionString, you could use:
// let ctx = sql.GetDataContext connectionString2

// pick individual entities from the database 
let christina = ctx.Main.Customers.Individuals.``As ContactName``.``BERGS, Christina Berglund``

// directly enumerate an entity's relationships, 
// this creates and triggers the relevant query in the background
let christinasOrders = christina.``main.Orders by CustomerID`` |> Seq.toArray

let mattisOrderDetails =
    query { for c in ctx.Main.Customers do
            // you can directly enumerate relationships with no join information
            for o in c.``main.Orders by CustomerID`` do
            // or you can explicitly join on the fields you choose
            join od in ctx.Main.OrderDetails on (o.OrderId = od.OrderId)
            //  the (!!) operator will perform an outer join on a relationship
            for prod in (!!) od.``main.Products by ProductID`` do 
            // nullable columns can be represented as option types; the following generates IS NOT NULL
            where o.ShipCountry.IsSome                
            // standard operators will work as expected; the following shows the like operator and IN operator
            where (c.ContactName =% ("Matti%") && c.CompanyName |=| [|"Squirrelcomapny";"DaveCompant"|] )
            sortBy o.ShipName
            // arbitrarily complex projections are supported
            select (c.ContactName,o.ShipAddress,o.ShipCountry,prod.ProductName,prod.UnitPrice) } 
    |> Seq.toArray

Samples & documentation

The library comes with comprehensive documentation.

Database vendor specific issues and considerations are documented on their separate pages. Please see the menu on the right.

Contributing and copyright

The project is hosted on GitHub where you can report issues, fork the project and submit pull requests. If you're adding new public API, please also consider adding samples that can be turned into a documentation. You might also want to read library design notes to understand how it works. Our tests have more samples. Learn more tech tech details.

The library is available under Public Domain license, which allows modification and redistribution for both commercial and non-commercial purposes. For more information see the License file in the GitHub repository.

Multiple items
namespace FSharp

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

--------------------
namespace Microsoft.FSharp.Data
namespace FSharp.Data.Sql
Multiple items
type LiteralAttribute = inherit Attribute new: unit -> LiteralAttribute
<summary>Adding this attribute to a value causes it to be compiled as a CLI constant literal.</summary>
<category>Attributes</category>


--------------------
new: unit -> LiteralAttribute
[<Literal>] val resolutionPath: string = "C:\git\SQLProvider\docs\content/../files/sqlite"
[<Literal>] val connectionString: string = "Data Source=C:\git\SQLProvider\docs\content\northwindEF.db;Version=3;Read Only=false;FailIfMissing=True;"
type sql = SqlDataProvider<...>
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 1000.</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.</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>
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
Common.DatabaseProviderTypes.SQLITE: Common.DatabaseProviderTypes = 1
[<Struct>] type SQLiteLibrary = | SystemDataSQLite = 0 | MonoDataSQLite = 1 | AutoSelect = 2 | MicrosoftDataSqlite = 3
Common.SQLiteLibrary.SystemDataSQLite: Common.SQLiteLibrary = 0
[<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>
val ctx: SqlDataProvider<...>.dataContext
SqlDataProvider<...>.GetDataContext() : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(selectOperations: SelectOperations) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='selectOperations'>Execute select-clause operations in SQL database rather than .NET-side.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(commandTimeout: int) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='commandTimeout'>SQL command timeout. Maximum time for single SQL-command in seconds.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(transactionOptions: Transactions.TransactionOptions) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string, selectOperations: SelectOperations) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param><param name='selectOperations'>Execute select-clause operations in SQL database rather than .NET-side.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(transactionOptions: Transactions.TransactionOptions, commandTimeout: int) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param><param name='commandTimeout'>SQL command timeout. Maximum time for single SQL-command in seconds.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string, commandTimeout: int) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param><param name='commandTimeout'>SQL command timeout. Maximum time for single SQL-command in seconds.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string, transactionOptions: Transactions.TransactionOptions) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param><param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string, resolutionPath: string) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param><param name='resolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types</param>
   (+0 other overloads)
val christina: SqlDataProvider<...>.dataContext.main.CustomersEntity
property SqlDataProvider<...>.dataContext.Main: SqlDataProvider<...>.dataContext.mainSchema with get
property SqlDataProvider<...>.dataContext.mainSchema.Customers: SqlDataProvider<...>.dataContext.mainSchema.main.Customers with get
<summary> The table Customers belonging to schema main</summary>
property SqlDataProvider<...>.dataContext.mainSchema.main.Customers.Individuals: SqlDataProvider<...>.dataContext.main.Customers.Individuals with get
<summary>Get individual items from the table. Requires single primary key.</summary>
val christinasOrders: SqlDataProvider<...>.dataContext.main.OrdersEntity[]
Multiple items
module Seq from FSharp.Data.Sql

--------------------
module Seq from Microsoft.FSharp.Collections
<summary>Contains operations for working with values of type <see cref="T:Microsoft.FSharp.Collections.seq`1" />.</summary>
val toArray: source: seq<'T> -> 'T[]
<summary>Builds an array from the given collection.</summary>
<param name="source">The input sequence.</param>
<returns>The result array.</returns>
<exception cref="T:System.ArgumentNullException">Thrown when the input sequence is null.</exception>
<example id="toarray-1"><code lang="fsharp"> let inputs = seq { 1; 2; 5 } inputs |&gt; Seq.toArray </code> Evaluates to <c>[| 1; 2; 5 |]</c>. </example>
val mattisOrderDetails: (Option<string> * Option<string> * Option<string> * string * Option<decimal>)[]
val query: Linq.QueryBuilder
<summary>Builds a query using query syntax and operators.</summary>
<example id="query-1"><code lang="fsharp"> let findEvensAndSortAndDouble(xs: System.Linq.IQueryable&lt;int&gt;) = query { for x in xs do where (x % 2 = 0) sortBy x select (x+x) } let data = [1; 2; 6; 7; 3; 6; 2; 1] findEvensAndSortAndDouble (data.AsQueryable()) |&gt; Seq.toList </code> Evaluates to <c>[4; 4; 12; 12]</c>. </example>
val c: SqlDataProvider<...>.dataContext.main.CustomersEntity
val o: SqlDataProvider<...>.dataContext.main.OrdersEntity
custom operation: join var in collection on (outerKey = innerKey). Note that parentheses are required after 'on' Calls Linq.QueryBuilder.Join
<summary>A query operator that correlates two sets of selected values based on matching keys. Normal usage is 'join y in elements2 on (key1 = key2)'. </summary>
<example-tbd></example-tbd>
val od: SqlDataProvider<...>.dataContext.main.OrderDetailsEntity
property SqlDataProvider<...>.dataContext.mainSchema.OrderDetails: SqlDataProvider<...>.dataContext.mainSchema.main.OrderDetails with get
<summary> The table OrderDetails belonging to schema main</summary>
property SqlDataProvider<...>.dataContext.main.OrderDetailsEntity.OrderId: int64 with get, set
<summary>OrderID: integer</summary>
val prod: SqlDataProvider<...>.dataContext.main.ProductsEntity
custom operation: where (bool) Calls Linq.QueryBuilder.Where
<summary>A query operator that selects those elements based on a specified predicate. </summary>
<example-tbd></example-tbd>
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipCountry: Option<string> with get, set
<summary>ShipCountry: nvarchar(15)</summary>
property Option.IsSome: bool with get
<summary>Return 'true' if the option is a 'Some' value.</summary>
property SqlDataProvider<...>.dataContext.main.CustomersEntity.ContactName: Option<string> with get, set
<summary>ContactName: nvarchar(30)</summary>
property SqlDataProvider<...>.dataContext.main.CustomersEntity.CompanyName: string with get, set
<summary>CompanyName: nvarchar(40)</summary>
custom operation: sortBy ('Key) Calls Linq.QueryBuilder.SortBy
<summary>A query operator that sorts the elements selected so far in ascending order by the given sorting key. </summary>
<example-tbd></example-tbd>
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipName: Option<string> with get, set
<summary>ShipName: nvarchar(40)</summary>
custom operation: select ('Result) Calls Linq.QueryBuilder.Select
<summary>A query operator that projects each of the elements selected so far. </summary>
<example-tbd></example-tbd>
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipAddress: Option<string> with get, set
<summary>ShipAddress: nvarchar(60)</summary>
property SqlDataProvider<...>.dataContext.main.ProductsEntity.ProductName: string with get, set
<summary>ProductName: nvarchar(40)</summary>
property SqlDataProvider<...>.dataContext.main.ProductsEntity.UnitPrice: Option<decimal> with get, set
<summary>UnitPrice: money</summary>