Using SQLite for Simple Database Storage - An Example User Object

(Page 3 of 6 )

Because I'm a huge believer in the benefits of object oriented design, we'll need a User object that will handle all of the actual database interface.  The prototype looks like this:

Imports System.Data
Imports Finisar.SQLite
Imports System.Security
Imports System.Text

Public Class User

  Public Id As Integer
  Public Name As String
  Public Email As String

  Public Conn As SQLiteConnection

  Public Sub Load(ByVal UID As Integer)
  Public Function Authenticate(ByVal N As String, ByVal P As String) _
                   As Boolean
  Public Sub Save()
  Public Sub Delete()
  Public Function Unique(ByVal requested As String) As Boolean
  Public Function HashPassword(ByVal s As String) As String
  Public Sub Populate(ByRef rdr As SQLiteDataReader)

End Class

The first group of functions provide basic persistent storage methods, and we'll concentrate there first.

The Populate(ByRef rdr AS SQLiteDataReader) sub bears some mentioning. It's responsible for populating the properties of the User object from a database reader.  When multiple User objects are placed in an aggregate data type, it's useful to retrieve all of the user objects in a single query, and populate each of them as they're inserted into the aggregate object.  Several internal methods also use it.

Public Sub Populate(ByRef rdr As SQLiteDataReader)

    Id = rdr("id")
    Name = rdr("name")
    Email = rdr("email")

End Sub

With this is place, we're ready to create the Load(Integer) sub. 

Public Sub Load(ByVal UID As Integer)

    Dim Query As New _
      SQLiteCommand("SELECT id, name, email FROM user WHERE id=?", Conn)
    Dim Reader As SQLiteDataReader

    Query.CreateAndAddUnnamedParameters()
    Query.Parameters(0).Value = UID
    Query.Prepare()
    Reader = Query.ExecuteReader
    If Reader.Read Then
        Populate(Reader)
    End If

End Sub

The SQLite ADO.NET provider supports prepared statements and parameter substitution, as you can see from the text of the SQLiteCommand object.  Coming from the native C interface to this library, I was a little thrown by this provider's behavior.  In the C interface, the Prepare method would be called before assigning parameters, parameters assigned, and then the query executed. 

In this interface, the CreateAndAddUnnamedParameters method is called, then parameters are assigned, and finally the Prepare method is called.  While I'm sure that the author had his reasons, the inconsistency caused a great deal of vexation, and the method name is excessively wordy.

Next: Saving a Record >>
 

More Database Articles
More By Clay Dowling

Comments On: Using SQLite for Simple Database Storage

· Hello!Great article here. SQLite really is a simple, fast and powerful database...    · Its a very helpful article...I have been looking for database designed with...   >>> Post your comment now!