SQLiteBlob Class

In This Topic

Represents a variable-length stream of binary data to be stored in or retrieved from a database.

Syntax

Visual Basic
C#

'Declaration
 
Public Class SQLiteBlob 
   Inherits System.IO.Stream
   Implements System.Data.SqlTypes.INullable, System.ICloneable, System.IDisposable, System.Xml.Serialization.IXmlSerializable

public class SQLiteBlob : System.IO.Stream, System.Data.SqlTypes.INullable, System.ICloneable, System.IDisposable, System.Xml.Serialization.IXmlSerializable

Remarks

SQLiteBlob is used to insert or retrieve BLOB fields in databases.

Example

In this example two functions are presented. First can upload a file onto a server, and second can retrieve a BLOB field and paste it into a file. Notice that two classes used here (BinaryWriter and BinaryReader) to provide more flexible stream manipulation are actually redundant for simple data transfer operations.

csharp

public void DownloadBlob(SQLiteConnection sqConnection)
{
  SQLiteCommand sqCommand = new SQLiteCommand("SELECT * FROM Pictures", sqConnection);
  sqConnection.Open();
  SQLiteDataReader myReader = sqCommand.ExecuteReader(System.Data.CommandBehavior.Default);
  try
  {
    while (myReader.Read())
    {
      SQLiteBlob myBlob = myReader.GetSQLiteBlob(myReader.GetOrdinal("Picture"));
      if(!myBlob.IsNull)
      {
        string FN = myReader.GetString(myReader.GetOrdinal("PicName"));
        FileStream fs = new FileStream("D:\\Tmp\\"+FN+".bmp", FileMode.Create);
        BinaryWriter w = new BinaryWriter(fs);
        w.Write(myBlob.Value);
        w.Close();
        fs.Close();
        Console.WriteLine(FN+" downloaded.");
      }
    }
  }
  finally
  {
    myReader.Close();
    sqConnection.Close();
  }
}                                                                                                                   

public void UploadBlob(SQLiteConnection sqConnection)
{
  FileStream fs = new FileStream("D:\\Tmp\\_Water.bmp", FileMode.Open, FileAccess.Read);
  BinaryReader r = new BinaryReader(fs);
  SQLiteBlob myBlob = new SQLiteBlob(r.ReadBytes((int)fs.Length));
  SQLiteCommand sqCommand = new SQLiteCommand("INSERT INTO Pictures (ID, PicName, Picture) VALUES(1,'Water',:Pictures)", sqConnection);
  sqCommand.Parameters.Add("Pictures",myBlob);
  sqConnection.Open();
  try
  {
    Console.WriteLine(sqCommand.ExecuteNonQuery()+" rows affected.");
  }
  finally
  {
    sqConnection.Close();
    r.Close();
  }
}

Visual Basic

Public Sub DownloadBlob(ByVal sqConnection As SQLiteConnection)
  Dim sqCommand As New SQLiteCommand("SELECT * FROM Pictures", sqConnection)
  sqConnection.Open()
  Dim myReader As SQLiteDataReader = sqCommand.ExecuteReader(System.Data.CommandBehavior.Default)
  Try
    While myReader.Read()
      Dim myBlob As SQLiteBlob = myReader.GetSQLiteBlob(myReader.GetOrdinal("Picture"))
      If Not myBlob.IsNull Then
        Dim FN As String = myReader.GetString(myReader.GetOrdinal("PicName"))
        Dim fs As FileStream = New FileStream("D:\Tmp\" + FN + ".bmp", FileMode.Create)
        Dim w As BinaryWriter = New BinaryWriter(fs)
        w.Write(myBlob.Value)
        w.Close()
        fs.Close()
        Console.WriteLine(String.Concat(FN, " downloaded."))
      End If
    End While
  Finally
    myReader.Close()
    sqConnection.Close()
  End Try
End Sub

Public Sub UploadBlob(ByVal sqConnection As SQLiteConnection)
  Dim fs As FileStream = New FileStream("D:\Tmp\_Water.bmp", FileMode.Open, FileAccess.Read)
  Dim r As BinaryReader = New BinaryReader(fs)
  Dim myBlob As SQLiteBlob = New SQLiteBlob(r.ReadBytes(Convert.ToInt32(fs.Length)))
  Dim sqCommand As SQLiteCommand = New SQLiteCommand("INSERT INTO Pictures (ID, PicName, Picture) VALUES(2,'Water',:Pictures)", sqConnection)
  sqCommand.Parameters.Add("Pictures", myBlob)
  sqConnection.Open()
  Try
    Console.WriteLine(String.Concat(sqCommand.ExecuteNonQuery(), " rows affected."))
  Finally
    sqConnection.Close()
    r.Close()
  End Try
End Sub

Inheritance Hierarchy

System.Object
   System.MarshalByRefObject
      System.IO.Stream
         Devart.Data.SQLite.SQLiteBlob
            Devart.Data.SQLite.SQLiteText

Reference

SQLiteBlob Members
Devart.Data.SQLite Namespace

Send Feedback