Sqlite, database connection and windows phone 8.1 ( silverlight)

Question

I have recently started a new job as a software developer/specialist and I am currently upgrading an maintaining an app that targeted windows ce (I know, I know)

I am mostly working on my db connection at the moment, and I have found a Nuget package ( sqlite-net), but I have found little documentation on how to use it. There is this function under the sqlite3 class called sqlite_open(string, ByRef db) that is just driving me nuts. If any of you has successfully used this namespace, I'd greatly appreciate the help. ( posting this thru phone so if you guys need more details it may take a while)

Thank you in advance!

Answer 1

I'm not sure how your research is going on, but I used to develop some Apps with Windows Phone 8, 8.1 and UWP, I know about the library that you are talking and most of the documentation was on the Nokia Wiki (now dead), however, I was able to find some of it:

Windows Phone: How to use SQLite in Windows Phone

There are some really important things about this library that you need to consider when you are developing for Windows Phone:

In a standard Windows Phone 8 or Windows Phone 8.1 (Silverlight) project you must add this:

SILVERLIGHT;WINDOWS_PHONE;USE_WP8_NATIVE_SQLITE

If the previous line is not added in your WP project is not going to work at all, this is added in the configuration of your App.

There is some additional documentation here:

Working with SQLite in Windows Phone 8: a sqlite-net version for mobile

And there is a BackUp of the Official Wiki on Scribd maybe you could find another one on the Wayback Machine.

But let's focus on the important code, keep in mind this is a raw translation to VB.NET from C#, I don't use it very often and it's not fully tested. I'd strongly suggest you to switch to C# because there is a wider documentation and examples.

DBManagement Class:

Public Class DBManagement
    'DB location
    Public Shared DB_PATH As String = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "sample.sqlite"))

    'DB Connection
    Private dbConn As SQLiteConnection

    'Example of Opening or Closing the DB
    Private Sub OpenOrCreateDB()
        ''' Create the database connection.
        dbConn = new SQLiteConnection(DB_PATH)
        ''' Create the table Task, if it doesn't exist.
        dbConn.CreateTable(Of Task)()

    End Sub

    'Example of Closing the DB
    Private Sub CloseDB()
        If dbConn <> Nothing Then 
            ' Close the database connection.
            dbConn.Close()
        End If
    End Sub

    'Example of a Select Operation
    Private Function List(Of Task) ExecQuery(Query as String)
        Dim sqlCommand = new SQLiteCommand(dbConn)
        '"select * from task where title = 'filter_test'"
        sqlCommand.CommandText = Query
        Return sqlComm.ExecuteQuery(Of Task)()
    End Sub

    'Example of an Insert Operation
    Private Sub Insert(ByVal sender As Object, ByVal e As RoutedEventArgs)
        Dim task As Task = New Task() With {.Title = TitleField.Text, .Text = TextField.Text, .CreationDate = DateTime.Now}
        dbConn.Insert(task)
    End Sub
End Class

Entity Table class Task:

Public Class Task

    <PrimaryKey, AutoIncrement>
    Public Property Id As Integer

    Public Property Title As String

    Public Property Text As String

    Public Property CreationDate As DateTime

    Public Overrides Function ToString() As String
        Return Title & ":" & Text & " < " + CreationDate.ToShortDateString() & " " + CreationDate.ToShortTimeString()
    End Function
End Class

Hopefully, it might give you some light.