Using SQLite for Simple Database Storage - Saving a Record

(Page 4 of 6 )

Saving a record is slightly more complicated, because the query can be either an update or an insert.  In the case of an insert, it's important for us to retrieve the value of the newly created primary key.  SQLite has an SQL function called LAST_INSERT_ROWID() which will return this value for us.

Public Sub Save()

  Dim Query As New SQLiteCommand()

  Query.Connection = Conn
  If Id > 0 Then
    Query.CommandText = "UPDATE user SET name=?, email=? WHERE id=?"
    Query.CreateAndAddUnnamedParameters()
    Query.Parameters(0).Value = Name
    Query.Parameters(1).Value = Email
    Query.Parameters(2).Value = Id
    Query.Prepare()
    Query.ExecuteNonQuery()
  Else
    Query.CommandText = "INSERT INTO user (name, email, password) " _
                        + "VALUES (?, ?, '*')"
    Query.CreateAndAddUnnamedParameters()
    Query.Parameters(0).Value = Name
    Query.Parameters(1).Value = Email
    Query.Prepare
    Query.ExecuteNonQuery()
    Query.CommandText = "SELECT LAST_INSERT_ROWID()"
    Id = Query.ExecuteScalar
  End If

End Sub

The last of our storage methods is Delete().

Public Sub Delete()

  Dim Query As New SQLiteCommand("DELETE FROM user WHERE id=?", Conn)
 
  Query.CreateAndAddUnnamedParameters()
  Query.Parameters(0).Value = Id
  Query.Prepare()
  Query.ExecuteNonQuery()

End Sub

For the sake of completeness, I'll also show the implementations of the methods for password and account verification handling.  The first function that's necessary is the password setting function.  Passwords will be stored as md5 encoded strings, so even if the contents of the user table are compromised, password information will not be divulged.  In VB.NET, this is a two step process.  First, the password must be encoded:

Private Function HashPassword(ByVal s As String) As String

  Dim tmpSource As Byte()
  Dim tmpHash As Byte()

  tmpSource = ASCIIEncoding.ASCII.GetBytes(s)
  tmpHash = New Cryptography.MD5CryptoServiceProvider.ComputeHash(tmpSource)
 
  Return BitConverter.ToString(tmpHash)

End Function

The result of this function is a 47 character string which consists of the hexadecimal representation of the MD5 hash of our password.  Each hexadecimal number is separated by dashes.

Next: Storing it in the Database >>
 

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!