Home > Uncategorized > Store Zipped data in #SqlLite BLOB with C#

Store Zipped data in #SqlLite BLOB with C#


SqlLite is great if you want to include pre-prepared data with your app or website, but if you find your .sqlite file becoming massive, you might look at how you are storing your data.

This approach is only relevant, if you have one or more fields that contain large blocks of text. It also comes in handy if you are storing base64 encoded binary data or text with UTF8 / UTF16 chars

First, use the BLOB data type, rather than TEXT for storing your big-data field.

Next, you’ll need these two C# functions to Compress and Decompress byte arrays in-memory:

static byte[] Compress(byte[] data)
using (var compressedStream = new MemoryStream())
using (var zipStream = new GZipStream(compressedStream, CompressionMode.Compress))
zipStream.Write(data, 0, data.Length);
return compressedStream.ToArray();

static byte[] Decompress(byte[] data)
using (var compressedStream = new MemoryStream(data))
using (var zipStream = new GZipStream(compressedStream, CompressionMode.Decompress))
using (var resultStream = new MemoryStream())
return resultStream.ToArray();

Then, when inserting into the Sqlite file, then use the functions;

To Insert a row;

var bXml = System.Text.Encoding.UTF8.GetBytes(strXML);
var bCompress = Compress(bXml);
var command = new SQLiteCommand(connection)
CommandText = “insert into Table (ID,xml) values (” + ID + “,@xml)”
var parameter = new SQLiteParameter(“@xml”, System.Data.DbType.Binary) {Value = bCompress};

Then to read the value back out again;

var connection = new SQLiteConnection(“Data Source=” + strDbFile);
var strSql = “select xml from Table limit 1;”;
var command = new SQLiteCommand(strSql, connection);
var obj = command.ExecuteScalar();
var bArray = (byte[])obj;
var bXml = Decompress(bArray);
var strXML = System.Text.Encoding.UTF8.GetString(bXml);

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: