muffi muffi - 28 days ago 13
Vb.net Question

VB.net: read and write an .Net-Object with SQLite

What will I have to do when I want to read a FileInfo from a SQLite-database? The following code works fine and stores the field (the column in SQLite is blob):

com.Parameters.Add("@info", DbType.Object)

com.Parameters("@info").Value = New FileInfo(filename)


How can I read the result of a query (result is of course one FileInfo) into a FileInfo using the SQLiteDataReader?

The following code (rd represents the DataReader) produces an error (can't convert from byte() to FileInfo):

Dim fi as FileInfo = DirectCast(rd.GetValue(0), FileInfo)


Thank you!

Answer

In order for the FileInfo object to be stored as Binary/BLOB, it has to be converted to a byte array. As far as I know, SQLite only knows how to do that for one thing, so you have to do it yourself using serialization:

Dim bf As New BinaryFormatter
...
Using ms As New MemoryStream
    bf.Serialize(ms, fi)
    ms.Position = 0
    cmd.Parameters.Add("@f", DbType.Binary).Value = ms.ToArray
End Using
cmd.ExecuteNonQuery()

You'll have to deserialize the result when reading it back. Neither is very difficult, but it would be simpler to save the full filename and create a new FileInfo from that:

If rdr.Read Then
   n = rdr.GetString(0)
   fi = New FileInfo(n)
   ...
Comments