Handling Binary Data with SQLite 3.0 ,SQLite ADO.NET Provider, and SQLite "ADOHelper"
By Peter A. Bromberg, Ph.D.

Peter Bromberg

"The willingness with which our young people are likely to serve in any war, no matter how justified,
shall be directly proportional to how they perceive the Veterans of earlier wars were treated
and appreciated by their nation."
-- George Washington

Boy, times have changed in the last 250 years, haven't they (and, I might add, "and also, how they were treated and appreciated by their fellow veterans...") .



For my next article, I'm going to show how we can use neural networks to predict election results... But, why waste our time with that, when we can talk about SQLite! One of the coolest features of the SQLite 3.0 database engine is its new support for the BLOB datatype. Previously in SQLite 2.XXX versions, everything (including image data) was stored basically as a string. If we add to this the fact that the ADO.NET provider for SQLite supports parameters, we have all the ingredients for a compact, zero-install, fast database engine that can handle databases full of binary data - images, MP3 files, Word and Excel Documents, you name it. Here's a simple Windows Forms application that uses the SQLite3.dll C library coupled with the SQLite.NET.dll ADO.NET managed - code library to show how to insert and retrieve binary data.

First, the UI of my Winforms "Test Harness" app looks like so:

On the Form, I have a PictureBox, a label and two button controls. Now let's look at the codebehind for the INSERT and the GET PICTURE buttons (button3_Click and button4_Click, respectively:

private void button3_Click(object sender, System.EventArgs e)
{    // this is the "INSERT" button handler--
    //"path" is declared at class level=Environment.CurrentDirectory;
    // I was going to call it "Blob.db" but this sounds better...     
    string dbpath = path+@"\bob.db";
    // if the db file doesn't exist, create a new db (New=True to create a db)---
    string connString=@"Data Source="+dbpath+";New=True;UTF8Encoding=True;Version=3";
    if(!File.Exists(dbpath))
    {    
    SQLiteConnection con = new SQLiteConnection(connString);
    SQLiteCommand cmd = con.CreateCommand(); 
    cmd.CommandText="CREATE TABLE tbl (filename varchar(30),t BLOB)";   
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
    cmd.Dispose();
    }        
    //open a connection, prompt the user with file dialog
    // to select a picture to insert into db--
    string connString2=@"Data Source="+dbpath+";UTF8Encoding=True;Version=3";
    SQLiteConnection con2 = new SQLiteConnection(connString2);
    //can use either interface or SQLiteCommand--
    IDbCommand cmd2 = con2.CreateCommand();              
    openFileDialog1.ShowDialog();
    string path2= openFileDialog1.FileName;    
    string fileName = Path.GetFileName(openFileDialog1.FileName);
    FileStream fs = new FileStream(path2, FileMode.Open, FileAccess.Read);             
    byte[] myData = new byte[fs.Length];
    fs.Read(myData, 0,(int)fs.Length );
    fs.Close();             
    cmd2.CommandText = "INSERT INTO Tbl (filename,t) VALUES (@filename,@t)";
    SQLiteParameter param1 = new SQLiteParameter ("@filename", DbType.String );
    param1.Value =fileName;
    cmd2.Parameters .Add (param1);
    SQLiteParameter param = new SQLiteParameter ("@t", DbType.Binary );
    param.Value =myData; 
    cmd2.Parameters .Add (param);             
    con2.Open();
    cmd2.ExecuteNonQuery();
    cmd2.Dispose();
    con2.Close();        
}

private void button4_Click(object sender, System.EventArgs e)
{  // This is the "Retrieve" button Handler--
   string dbpath=path +@"\bob.db";
    SQLiteConnection con = 
        new SQLiteConnection(@"Data Source="+dbpath+";UTF8Encoding=True;Version=3");
    SQLiteDataAdapter da = new SQLiteDataAdapter("Select * From tbl ", con);
    SQLiteCommandBuilder MyCB = new SQLiteCommandBuilder(da);
    DataSet ds = new DataSet("MyImages");
    da.Fill(ds, "MyImages");
    con.Close();
    DataRow myRow;
    // get the latest row
    myRow=ds.Tables["MyImages"].Rows[ds.Tables["MyImages"].Rows.Count-1 ];     
    byte[] stuff=( byte[])myRow["t"];              
    MemoryStream stm  = new MemoryStream(stuff);    
    Bitmap bmp= new Bitmap(stm);
    this.pictureBox1.Image =  bmp;
    this.label1.Text=(string)myRow["filename"];}
    }

What the above code does, simply put, is this:

1) When the user presses the INSERT button, check to see if the bob.db database file exists. If not go ahead and create one. You can do this in SQLite by adding "New=True" to the connection string. Be aware that if the database actually does exist, it will be overwritten. We then create our single table "tbl" with 2 fields, "filename" and "t" of type BLOB.

2) Prompt the user with an Open File Dialog so they can select an image to store in the database. Get the filename without the path information to save in the "filename" column of the table.

3) Create a second connection and command with the insert statement "INSERT INTO Tbl (filename,t) VALUES (@filename,@t)"; with the parameter names defined inline in the SQL.

4) Create and populate two IDbParameter objects (or SQLiteParameter if you prefer) and populate them. The "@t" parameter is of type Binary and is assigned the byte array representing the image that was just read off the filesystem.

5) Call ExecuteNonQuery just as you would with any ADO.NET provider. DONE!

To get the Binary data out of your database, you can use a DataAdapter. and call its Fill method on your favorite new DataSet.

That's all there is to it, to build a database in which you can store and index images, Mp3 or WMV video files, Word or Excel Documents, whatever you want. The process is exactly the same. If you want to "serve" these binary items in an ASP.NET application, all you need to do is set the proper MIME type or OBJECT tag (such as for wmv, swf or mp3 files) and Response.Write them out to the requesting browser. Obviously, when you start thinking through the process for your application, you will have many more columns in your table(s) than are shown here. But, don't worry - SQLite can handle databases up into the TERABYTES and it's fast as can be!

Not being completely satisfied with the above, I decided to write an ADOHelper class for the SQLite ADO.NET provider in the style of the MS Data Access Application Block "SQLHelper". This allows you to call a static method to ExecuteReader, ExecuteDataSet, ExecuteXmlReader, ExecuteNonQuery or ExecuteScalar in the following easy signature:

string connString = "SQLite Connection string";
string SQL = "insert into tbl (filename,imageData) values (@filename, @imageData)";
object[] parms ={ fileName, myData};
DataSet ds = ADOHelper.ExecuteDataSet(connString, SQL, parms);

I haven't filled out all the overloads that you would find in, for example, the SQLHelper class, but if you are so inclined, this would be very easy to do. Also, I've only provider deriveParameter cases for the most commonly - used DbType choices.

The source code for my ADOHelper class in its current "usable" state follows ( it hasn't all been tested yet, so be careful):

using System;
using System.Data;
using System.Text.RegularExpressions ;
using System.Xml;
using System.IO;

namespace Finisar.SQLite
{
 /// <summary>
 /// Summary description for ADOHelper.
 /// </summary>
 public class ADOHelper
 {
  private ADOHelper()
  {
    
  }
        /// <summary>
        /// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
        /// </summary>
        /// <param name="connectionString">SQLite Connection string</param>
        /// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param>
        /// <param name="paramList">object[] array of parameter values</param>
        /// <returns></returns>
  public static DataSet ExecuteDataSet(string connectionString, string commandText, object[] paramList)
  {
   SQLiteConnection cn = new SQLiteConnection(connectionString);
   SQLiteCommand cmd =cn.CreateCommand();
    

   cmd.CommandText =commandText;
   if(paramList!=null)
   {
    SQLiteParameterCollection parms= deriveParameters(commandText,paramList);
    foreach (SQLiteParameter p in parms)
     cmd.Parameters.Add(p);
   }
   DataSet ds = new DataSet();
   if(cn.State ==ConnectionState.Closed)
    cn.Open();
   SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
   da.Fill(ds);
   da.Dispose();
   cmd.Dispose();
   cn.Close();
   return ds;
  }
   /// <summary>
   /// ShortCut method to return IDataReader
   /// </summary>
   /// <param name="connectionString">SQLite Connection String</param>
   /// <param name="commandText">SQL Statement with optional embedded "@param" style parameters</param>
   /// <param name="paramList">object[] array of parameter values</param>
   /// <returns></returns>
  public static IDataReader ExecuteReader(string connectionString, string commandText, object[] paramList)
  {
   SQLiteConnection cn = new SQLiteConnection(connectionString);
   SQLiteCommand cmd =cn.CreateCommand();
   cmd.CommandText =commandText;
   SQLiteParameterCollection parms= deriveParameters(commandText,paramList);
   foreach (SQLiteParameter p in parms)
    cmd.Parameters.Add(p);
   
   IDataReader rdr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
    
   cmd.Dispose();
    
   return rdr;
  }

/// <summary>
/// Shortcut to ExecuteNonQuery with SqlStatement and object[] param values
/// </summary>
/// <param name="connectionString">SQLite Connection String</param>
/// <param name="commandText">Sql Statement with embedded "@param" style parameters</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns></returns>
  public static int ExecuteNonQuery(string connectionString, string commandText, object[] paramList)
  {
   SQLiteConnection cn = new SQLiteConnection(connectionString);
   SQLiteCommand cmd =cn.CreateCommand();
   cmd.CommandText =commandText;
   SQLiteParameterCollection parms= deriveParameters(commandText,paramList);
   foreach (SQLiteParameter p in parms)
    cmd.Parameters.Add(p);
   if(cn.State ==ConnectionState.Closed)
   cn.Open();
    int result = cmd.ExecuteNonQuery();
    cmd.Dispose();
   cn.Close();
    
   return result;
  }
        /// <summary>
        /// Shortcut to ExecuteScalar with Sql Statement embedded params and object[] param values
        /// </summary>
        /// <param name="connectionString">SQLite Connection String</param>
        /// <param name="commandText">SQL statment with embedded "@param" style parameters</param>
        /// <param name="paramList">object[] array of param values</param>
        /// <returns></returns>
  public static object ExecuteScalar(string connectionString, string commandText, object[] paramList)
  {
   SQLiteConnection cn = new SQLiteConnection(connectionString);
   SQLiteCommand cmd =cn.CreateCommand();
   cmd.CommandText =commandText;
   SQLiteParameterCollection parms= deriveParameters(commandText,paramList);
   foreach (SQLiteParameter p in parms)
    cmd.Parameters.Add(p);
   
   object result = cmd.ExecuteScalar();
   cmd.Dispose();
   cn.Close();
    
   return result;
  }

         /// <summary>
         /// Execute XmlReader with complete Command
         /// </summary>
         /// <param name="command">SQLite Command</param>
         /// <returns>XmlReader</returns>
  public static XmlReader ExecuteXmlReader(IDbCommand command)
  { // open the connection if necessary, but make sure we 
   // know to close it when we�re done.
   if (command.Connection.State != ConnectionState.Open) 
   {
    command.Connection.Open();     
   }   

   // get a data adapter  
   Finisar.SQLite.SQLiteDataAdapter da = new Finisar.SQLite .SQLiteDataAdapter((SQLiteCommand)command);
   DataSet ds = new DataSet();
   // fill the data set, and return the schema information
   da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   da.Fill(ds);
   // convert our dataset to XML
   StringReader stream = new StringReader(ds.GetXml());    
    command.Connection.Close();
   // convert our stream of text to an XmlReader
   return new XmlTextReader(stream);
  }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="commandText">Sql Statement with "@param" style embedded parameters</param>
       /// <param name="paramList">object[] array of parameter values</param>
       /// <returns>SQLiteParameterCollection</returns>
  public static SQLiteParameterCollection deriveParameters(string commandText,object[] paramList)
  {
   if(paramList==null)return null;

   SQLiteParameterCollection coll = new SQLiteParameterCollection();
   string parmString = commandText.Substring(commandText.IndexOf("@"));
   parmString=parmString.Replace(","," ,");
   string pattern= @"(@)\S*(.*?)\b";
   Regex ex = new Regex ( pattern ,RegexOptions.IgnoreCase) ;
   MatchCollection mc = ex.Matches ( parmString ) ;
   string[] paramNames =new string[mc.Count];
   int i =0;
   foreach ( Match m in mc ) 
   { 
    paramNames[i]= m.Value  ;
    i++;
   } 
    
   int j = 0;
   Type t=null;
   foreach (object o in paramList)
   {
    t =   o.GetType();
    SQLiteParameter parm = new SQLiteParameter();   
   
    if( t.ToString() == "System.String")
    {
     parm.DbType=DbType.String ;
     parm.ParameterName =paramNames[j];
     parm.Value= (string)paramList[j];
     coll.Add(parm);
     goto cont;
    }
    if(t.ToString() == "System.Byte[]" )
    {
     parm.DbType=DbType.Binary;
     parm.ParameterName =paramNames[j];
     parm.Value =(byte[])paramList[j];
     coll.Add(parm);
     goto cont;
    }

    if(t.ToString()=="System.Int32" )
    {
     parm.DbType=DbType.Int32;
     parm.ParameterName =paramNames[j];
     parm.Value =(int)paramList[j];
     coll.Add(parm);
     goto cont;
    }
           
    if(t.ToString()=="System.Boolean" )
    {
     parm.DbType=DbType.Boolean;
     parm.ParameterName =paramNames[j];
     parm.Value =(bool)paramList[j];
     coll.Add(parm);
     goto cont;
    }
       
    if(t.ToString()=="System.DateTime" )
    {
     parm.DbType=DbType.Boolean;
     parm.ParameterName =paramNames[j];
     parm.Value =Convert.ToDateTime(paramList[j]);
     coll.Add(parm);
     goto cont;
    }
    if(t.ToString()=="System.Object" )
    {
     parm.DbType=DbType.Object;
     parm.ParameterName =paramNames[j];
     parm.Value =paramList[j];
     coll.Add(parm);
     goto cont;
    }
    
    if(t.ToString()=="System.Double" )
    {
     parm.DbType=DbType.Double;
     parm.ParameterName =paramNames[j];
     parm.Value =Convert.ToDouble(paramList[j]);
     coll.Add(parm);
     goto cont;
    }  
    //TODO: PAB: fill in rest of common param types here

   cont:
    j++;
   }
   return coll;
  }
 }
}

The full source code as a Visual Studio.NET 2003 solution is available at the link below!

Download the Visual Studio.NET 2003 solution accompanying this article


Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.
Article Discussion: