Using F# & SQLite.Net.Async PCL To Create a Database Table

Question

I am trying to create a table "Materials" using an F# project inside a C# Windows 8 App. This is probably more specific on the library (SQLite.Net.Async) but it I am not having much luck getting this to work. I get this, erorr near ")": syntax error, which is not helpful, if I could see the full SQL query it was trying to execute I may be able to get a bit further on solving this.

Sql.fsi

namespace DataAccess

open SQLite.Net.Async
open SQLite.Net.Interop
open System.Threading.Tasks

module Sql =
    val connect : ISQLitePlatform -> string -> SQLiteAsyncConnection
    val initTables : SQLiteAsyncConnection -> Task<SQLiteAsyncConnection.CreateTablesResult> option

Sql.fs

namespace DataAccess

open SQLite
open SQLite.Net
open SQLite.Net.Async
open SQLite.Net.Attributes
open System
open System.Threading.Tasks

module Sql =
    [<Table(name="Materials")>]
    type Material (id : int, name : string) = 
        let mutable m_id : int = id
        let mutable m_name : string = name

        new() = Material(0, null)

        [<Column(name="Id")>] [<PrimaryKey>] [<AutoIncrement>]
        member this.Id
            with get() = m_id
            and set(value) = m_id <- value

        [<Column(name="Name")>]
        member this.Name
            with get() = m_name
            and set(value) = m_name <- value

        override this.ToString() = System.String.Format("[{0}] {1}", m_id, m_name)

    let connect (platform : SQLite.Net.Interop.ISQLitePlatform) (databasePath : string) : SQLiteAsyncConnection =
        let connectionString = SQLiteConnectionString(databasePath, false)
        let connectionFactory = new Func<SQLiteConnectionWithLock>(fun () -> new SQLiteConnectionWithLock(platform, connectionString));
        let connection = new SQLiteAsyncConnection(connectionFactory)
        connection

    let initTables (connection : SQLiteAsyncConnection) : Task<SQLiteAsyncConnection.CreateTablesResult> option =
        try
            let result = connection.CreateTableAsync<Material>()
            Some(result)
        with
            | :? SQLite.Net.SQLiteException->
            None

Hub.cs

    async private void StackPanel_Tapped(object sender, TappedRoutedEventArgs e)
    {
        await GetDB();
    }

And then here is the exception:

An exception of type 'SQLite.Net.SQLiteException' occurred in mscorlib.dll but was not handled in user code

Additional information: near ")": syntax error

"  at SQLite.Net.Platform.WinRT.SQLiteApiWinRT.Prepare2(IDbHandle db, String query)
   at SQLite.Net.SQLiteCommand.Prepare()
   at SQLite.Net.SQLiteCommand.ExecuteNonQuery()
   at SQLite.Net.SQLiteConnection.Execute(String query, Object[] args)
   at SQLite.Net.SQLiteConnection.CreateTable(Type ty, CreateFlags createFlags)
   at SQLite.Net.Async.SQLiteAsyncConnection.<>c__DisplayClass0.<CreateTablesAsync>b__1()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at App1.HubPage1.<GetDB>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at App1.HubPage1.<StackPanel_Tapped>d__9.MoveNext()"

Answer 1

I was able to figure out the issue. It was my FSharp Signature file missing the "Material" definition.

Sql.fsi

namespace DataAccess

open SQLite.Net.Async
open SQLite.Net.Interop
open System.Threading.Tasks

module Sql =
    type Material =
        new : unit -> Material
        new : id:int * name:string -> Material
        member Id : int
        member Name : string
    val connect : ISQLitePlatform -> string -> SQLiteAsyncConnection
    val initTables : SQLiteAsyncConnection -> Task<SQLiteAsyncConnection.CreateTablesResult>

Sql.fs

namespace DataAccess

open SQLite
open SQLite.Net
open SQLite.Net.Async
open SQLite.Net.Attributes
open System
open System.Threading.Tasks

module Sql =
    type Material (id : int, name : string) = 
        let mutable m_id : int = id
        let mutable m_name : string = name

        new() = Material(0, null)

        [<PrimaryKey>] [<AutoIncrement>]
        member this.Id
            with get() = m_id
            and set(value) = m_id <- value

        member this.Name
            with get() = m_name
            and set(value) = m_name <- value

        override this.ToString() = System.String.Format("[{0}] {1}", m_id, m_name)

    let connect (platform : SQLite.Net.Interop.ISQLitePlatform) (databasePath : string) : SQLiteAsyncConnection =
        let connectionString = SQLiteConnectionString(databasePath, false)
        let connectionFactory = new Func<SQLiteConnectionWithLock>(fun () -> new SQLiteConnectionWithLock(platform, connectionString));
        let connection = new SQLiteAsyncConnection(connectionFactory)
        connection

    let initTables (connection : SQLiteAsyncConnection) : Task<SQLiteAsyncConnection.CreateTablesResult> =
        connection.CreateTableAsync<Material>()

Hub.cs

async public void GetDB()
{
    StorageFolder localFolder = ApplicationData.Current.LocalFolder;
    var file = await localFolder.CreateFileAsync("test.db", CreationCollisionOption.OpenIfExists);
    var filePath = file.Path;
    var connection = Sql.connect(new SQLitePlatformWinRT(), filePath);

    var result = await Sql.initTables(connection);
}