Store Byte array in SQLite using Blob

For my application I'm trying to store a byte array in my SQLite application, I'm filling my SQLite database this way:

public bool InsertMessage()
    {
        //create string SQl and fill it with the SQLite query for inserting a message.
        string SQL = "Insert into ClockMessages(InsertDateTime, SendDateTime, Data) Values('"+ insertdatetime + "', null, '" + data + "');";
        List<SQLiteParameter> pars = new List<SQLiteParameter>();
        pars.Add(new SQLiteParameter("InsertDateTime", insertdatetime));
        pars.Add(new SQLiteParameter("Data", data));
        //send the SQl query and it's parameters to the SQLiteDatabase class
        return SQLiteDatabase.ExecuteNonQuery(SQL, pars);
    }

Where InsertDateTime is the DateTime.UtcNow and Data is a byte array. The output of this results in a field Data that contains: System.Byte[], Can't I just show the real byte array in that field, without showing a string? I'am using the Byte[] type everywhere to fill the Database, so I don't convert it to a string. I would like to see something like this in the Data field: 01, 54, 32, 23, 11, 02, 53. The DataType of the Data field in the database is: BLOB.

Can anyone help me to achieve this?

回答1:

You should use parameters in the statement:

string SQL = "INSERT INTO ClockMessages (InsertDateTime, SendDateTime, Data) VALUES (@InsertDateTime, NULL, @Data)";

Everything else seems correct.

What you have now, calls the ToString() method on insertdatetime and data and concatenates the result to the sql statement. Actually you are not using any parameter in the statement.

回答2:

Use an overload of SQLiteParameter that takes the DBType parameter:

var dataParameter = new SQLiteParameter("Data", DbType.Binary) { Value = data };
pars.Add(dataParameter);