Represents a variable-length stream of binary data to be stored in or retrieved from a database.
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.
|
|---|
public void DownloadBlob(SQLiteConnection sqLiteConnection)
{
SQLiteCommand sqLiteCommand = new SQLiteCommand("SELECT * FROM Pictures", sqLiteConnection);
sqLiteConnection.Open();
SQLiteDataReader myReader = sqLiteCommand.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();
sqLiteConnection.Close();
}
}
public void UploadBlob(SQLiteConnection sqLiteConnection)
{
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 sqLiteCommand = new SQLiteCommand("INSERT INTO Pictures (ID, PicName, Picture) VALUES(1,'Water',:Pictures)", sqLiteConnection);
sqLiteCommand.Parameters.Add("Pictures",myBlob);
sqLiteConnection.Open();
try
{
Console.WriteLine(sqLiteCommand.ExecuteNonQuery()+" rows affected.");
}
finally
{
sqLiteConnection.Close();
r.Close();
}
} |
|
|---|
Public Sub DownloadBlob(ByVal sqLiteConnection As SQLiteConnection)
Dim sqLiteCommand As New SQLiteCommand("SELECT * FROM Pictures", sqLiteConnection)
sqLiteConnection.Open()
Dim myReader As SQLiteDataReader = sqLiteCommand.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()
sqLiteConnection.Close()
End Try
End Sub
Public Sub UploadBlob(ByVal sqLiteConnection 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 sqLiteCommand As SQLiteCommand = New SQLiteCommand("INSERT INTO Pictures (ID, PicName, Picture) VALUES(2,'Water',:Pictures)", sqLiteConnection)
sqLiteCommand.Parameters.Add("Pictures", myBlob)
sqLiteConnection.Open()
Try
Console.WriteLine(String.Concat(sqLiteCommand.ExecuteNonQuery(), " rows affected."))
Finally
sqLiteConnection.Close()
r.Close()
End Try
End Sub |