System.Data.SQLite

Hey All I'm a bit of a newbie to sql. I'm trying to learn how to read and write an image to a sqlite database. Does anyone have any pointers or example code of how to do this? I assume its done by storing the image as an array of blobs, but I have no idea how to do this. Any help would be greatly appreciated! Cameron

If you are dealing with small images (<1000K) then you can simply use a query such as:

INSERT INTO mytable (myid,myblob) VALUES(@myid, @myblob)

And define a parameter for the @myblob variable as Binary (Call the command's AddParameter with a Binary field type).

If you are dealing with larger BLOBs - you need to consider if it is really a good idea to store such big BLOBs in the database...

You can use the incremental BLOB API which is not supported directly by the .net sqlite provider (I wrote about it in this forum Here) OR you can use the standard API (but bear in mind that these BLOBs will enter the main memory before being written to the database file which may cause your application to hang or crash on OutOfMemory exceptions).

Most databases (SQLite included) do not support advanced BLOB manipulation operators. In SQLite - even checking the size of a BLOB field causes the entire BLOB image to get loaded into memory... I'd prefer to store file references to the actual files in disk and not load them to the SQLite database UNLESS they are very small and managable files.

Good Luck

Liron Levi (Creator of the SQLite Compare diff/merge utility)

Thank you for your reply Liron. Would you recommend storing the files in a local directory and storing the name of the file in the database? Then when a user wants to view a certain file, just check the database for the name and load it from the local directory? Cheers Cameron

Yes, exactly. Storing files as BLOB fields will hurt your software performance and is too much work anyway (especially in SQLite).

Best Regards

Liron Levi (developer of the SQLite Compare diff/merge utility)