0

Good day guys! Currently i am working on a c# project which needs me to retrieve image from .db3 sqlite file and insert it into another .db3 file. I can successfully retrieve the blob data and save it as image in my local drive. There are 3 types of value in my blob which are: hex, text, and image. However, when i tried to retrieve the complete hex value or image i can only retrieve the text value within as text is the one showing in the rows. I have tried searching in several platform and google but still those solution give me the same outcome. In this case, i cannot view the image in the new database file. My code is like this:

rdrall.Read();
string rawimage = ($"{rdrall[5]}") ;
byte[] newByte = ToByteArray(rawimage);

string inserttbl1 = "INSERT INTO newtest3 (image) VALUES (" + "'" + newbyte + "'" + ")";

SQLiteCommand insert = new SQLiteCommand(inserttbl1, createnew);
insert.ExecuteNonQuery();

The outcome should be like this "????": expected output

But turns out like this "system.byte[]": actual output

And the actual output cannot be view as image. Is there anyway to allow me to retrieve the whole value, or just convert the image into the format? Thanks in advance!

Chan Jun Liang
  • 5
  • 3

1 Answers1

1

You are inserting a System.byte[] text value in database because the string concatenation in C# will call .ToString() method of objects, byte[] will use a generic way to represent a object (the type name) in string instead of represent the array content itself.

If you want to insert a byte[] in a blob column, I recommend you to use as a bind parameter:

SqliteCommand command = new SqliteCommand("insert into newtest3 (id, name, image) values (@id, @name, @image)", connection);

command.Parameters.Add("@id", SqliteType.Integer).Value = 10;
command.Parameters.Add("@name", SqliteType.Text).Value = "john";
command.Parameters.Add(new SqliteParameter()
{
    ParameterName = "@image",
    Value = data,
    DbType = System.Data.DbType.Binary
});
command.ExecuteNonQuery();

and to read blob content as byte[], simple cast the column to byte[]:

SqliteCommand query = new SqliteCommand("select image from newtest3", connection);
SqliteDataReader reader = query.ExecuteReader();
while (reader.Read())
{
    byte[] image = (byte[])reader["image"];
}
Leo
  • 1,990
  • 1
  • 13
  • 20
  • Thank you! I have some question regarding the parameter, may i know how to add more parameter if i have more than one field in the table? i tried to add like this but still the error is same commandimg.Parameters.Add(new SQLiteParameter() { ParameterName = "lotGUID", // or @image Value = lotGUID, DbType = System.Data.DbType.String }); – Chan Jun Liang Nov 27 '20 at 07:32
  • I just edited to add more columns. what error you're getting? – Leo Nov 27 '20 at 11:14
  • 1
    Sorry for late reply, i was trying hard looking for alternative these 2 days. I see, i had tried the code you gave to me, then I realized i was using System.data.sqlite instead of Microsoft.data.sqlite. currently im getting error message of: "You need to call SQLitePCL.raw.SetProvider(). If you are using a bundle package, this is done by calling SQLitePCL.Batteries.Init()." while creating connection. Sorry for all the troubles, i just started c# for like 1 months and still working on it – Chan Jun Liang Nov 30 '20 at 02:33
  • @ChanJunLiang no problem, I faced this issue too because the package name, we are here to learn and share knowledge – Leo Nov 30 '20 at 11:13
  • Thank you so much Leo, you saved my life! – Chan Jun Liang Dec 01 '20 at 04:01