CRUD Operations and Experimental ODBC support in the SQLProvider

The SQL provider now supports basic transactional CRUD functionality and an ODBC provider. A new nuget package is up for you to grab here. As always, you can download and build from source here.

The nuget package is still pre-release. You can find it in Visual Studio by toggling the search filter to include pre-release packages. I'm sure Xamarin has a similar feature. Once this work has been tested well enough, I will likely upgrade the SQL Provider to a proper release.

Experimental ODBC Support

We now have support for general ODBC connectivity in the SQL provider. This provides us with awesome power to connect to almost anything, including Excel spreadsheets. However, because each driver can vary substantially, not all drivers may work, and others may have reduced functionality. ODBC is provided in the .NET core libraries, although you will of course need the appropriate ODBC driver installed on your machine for it to function.

ODBC has Where, Select and Join support. If the source in question provides primary key information, you will also get Individuals. Although explicit joins are supported, foreign key constraint information and navigation are not yet available. The new CRUD operations are also supported, where appropriate. We will see how this works from ODBC later in the post.

1
2
3

[<Literal>] 
let excelCs = @"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=I:\test.xls;DefaultDir=I:\;" 
type xl = SqlDataProvider<excelCs, Common.DatabaseProviderTypes.ODBC>

You can use the new features with the following providers :

• SQL Server
• SQLite
• PostgreSQL
• MySQL
• Oracle
• ODBC (limited)

The following examples will use SQLite, but the mechanics are identical for all the providers.

CRUD Operations

IMPORTANT IMFORMATION ON RESTRICTIONS!

The CRUD operations will only work for tables that have a well-defined, non composite primary key. Identity keys are fully supported.

The data context is the core object that enables CRUD. It is important to understand that each data context you create from the type provider will track all entities that are modified from it. In a sense, the data context “owns” all of the entities returned by queries or that you have created. You can create more than one data context that have different connection strings at runtime. You can pass a different connection string in the GetDataContext method. This is an important concept as it allows you to connect to multiple instances of the same database, and even replicate data between them fairly easily.

1
2
3

type sql = SqlDataProvider< ConnectionString, Common.DatabaseProviderTypes.SQLITE, ResolutionPath > 
let ctx = sql.GetDataContext() 
let ctx2 = sql.GetDataContext("some other connection string")

whenever you select entire entities from a data context, be that by a query expression or an Individual, the data context involved will track the entity. You can make changes to the fields by setting the relevant properties. You do not need to do anything else, as the data context handles everything for you.

1
2
3
4

let hardy = ctx.``[main].[Customers]``.Individuals.``As ContactName``.``AROUT, Thomas Hardy`` 
hardy.ContactName <- "Pezi the Pink Squirrel"

ctx.SubmitUpdates()

Note the call to SubmitUpdates() on the data context. This will take all pending changes to all entities tracked by the data context, and execute them in a transaction. An interesting property of the above code is that after Thomas Hardy has had his name changed, the first line will no longer compile! Go F#!

Similarly, you can delete an entity by calling the Delete() method on it. This action will put the entity into a pending delete state.

1
2
3

hardy.Delete()

ctx.SubmitUpdates()

After the deletion is committed, the data context will remove the primary key from the entity instance.

Creation is slightly different. You will find various Create methods on the IQueryable Set types that represent the tables. Up to 3 different overloads of this method will be available. The first takes no parameters and will return a new entity which are you expected to populate with at least the required fields. A second version will accept the required fields as parameters – this is only available if there are any columns marked as NOT NULL. The final version will create an entity from a (string * obj) sequence – it is potentially unsafe but very handy for copying entities or creating them from some stream of data, if you know the attribute / column names are correct.

A note on primary keys – presently the SQL provider does not detect identity columns, although it does deal with them on the insert appropriately. You will never see a primary key field as a parameter to the Create method. If your primary key is an identity column, you can simply not include it. Upon insert, the provider will automatically update the instance of the entity with the ID it has been assigned. If on the other hand your key is not an identity column, you will be expected to assign it an appropriate value before attempting to submit the changes to the database.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

// create an employee 
let pez = ctx.``[main].[Employees]``.Create("Pezi","Squirrel") 
ctx.SubmitUpdates()

printfn "Pezi's new employee id is %i" pez.EmployeeID

//update something 
pez.Address <- "the forest" 
ctx.SubmitUpdates()

// delete 
pez.Delete() 
ctx.SubmitUpdates()

Now let’s see how we can effortlessly combine different SQLProviders together in order to perform "extract, transform, load" type processes.

1
2
3
4
5

type sql = SqlDataProvider<northwindCs, UseOptionTypes = true> 
type xl = SqlDataProvider<excelCs, Common.DatabaseProviderTypes.ODBC>

let northwind = sql.GetDataContext() 
let spreadsheet = xl.GetDataContext()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

// load our tasty new employees from our lovely spreadsheet
let newEmployees = 
  spreadsheet.``[].[Sheet1$]`` 
  |> Seq.map(fun e -> 
    let emp = northwind.``[dbo].[Employees]``.Create() 
    emp.FirstName <- e.FirstName 
    emp.LastName <- e.LastName 
    emp) 
  |> Seq.toList

// save those puppies away 
northwind.SubmitUpdates()

In this sample we use the ODBC type provider to gain instant typed access to an Excel spreadsheet that contains some data about new employees. the SQL Server type provider is also used to connect to a Northwind database instance.

We then very simply pull all the rows from the spreadsheet, map them to an Employee database record, and save them away. It doesn’t get much easier than this!

Or does it? Actually, in this case we know up front that the spreadsheet contains identical field names to that of the target database. In this case we can use the overload of Create that accepts a sequence of data – this method is unsafe, but if you know that the names match up it means you don’t have to manually map any field names :)

1
2
3
4

let newEmployees = 
 spreadsheet.``[].[Sheet1$]`` 
 |> Seq.map(fun e -> northwind.``[dbo].[Employees]``.Create(e.ColumnValues)) 
 |> Seq.toList

I think we can agree it really does not get much easier than that! You can also use this technique to easily copy data between different data contexts, as long as you watch out for primary and foreign keys where applicable.

Data Binding

The SQLProvider, with a fair amount of trickery, supports two-way data binding over its entities. This works together very well with the CRUD operations.

1
2
3
4
5
6
7
8

open System.Windows.Forms 
open System.ComponentModel

let data = BindingList(Seq.toArray ctx.``[main].[Customers]``) 
let form = new Form(Text="Edit Customers") 
let dg = new DataGridView(Dock = DockStyle.Fill,DataSource=data) 
form.Controls.Add dg 
form.Show()

This is all the code you need to create a fully editable data grid. A call to SubmitUpdates() afterwards will push all the changes to the database.

Other Bits

The data context now has a few new methods on it that you can use.

1. ClearPendingChanges() : Ronseal. Remove any tracked entities that have changed. Use this with caution, as subsequent changes to the entities will be tracked, but the previous changes will have been lost.
2. GetPendingChanges() : This function will return a list of the entities the data context has tracked. This is useful in a variety of situations, and it also means you do not have to bind to created or updated entities in order to not “lose” them in your program.