BLOB data to simple string in DataGridView?

Question

I am using C# & MYSQL to develop a desktop application.

In one of my form I have a DataGridView (dgvBookings) and in my database table I have a table tblBookings which contains a field specialization of type BLOB.

I am selecting data with following query,

SELECT * FROM tblBookings WHERE IsActive=1;

and then binding the data with DataGridView as,

dgvBookings.DataSource = ds.Tables["allbookings"];

BUT after binding the data in gridview it shows Byte[] Array value for all rows of column specialization which is of BLOB type.

How yo solve this problem, I want the data in String format, whatever is written in that column should be show as it is there in the grid.

Answer 1

Sink the CellFormatting event and cast the byte array to a String, or cast the BLOB to a character type in your SQL query.

Answer 2

You'll have to convert the byte array to a string before using the DataSet:

DataTable allBookings = ds.Tables["allbookings"];
DataColumn column = allBookings.Columns.Add("NotABlobAnyMore", typeof(string));

foreach (DataRow row in allBookings.Rows) {
    row[column] = Encoding.Unicode.GetString((byte[])row["specialization"]);
}

dgvBookings.DataSource = allBookings ;

In the sample I'm using Unicode, but you should use whatever encoding was used to generate the byte array (blob).

If you are going to allow updates on the new column, you'll have to convert the string back to an array of bytes (in the old column) before sending the data back to the database.

Answer 3

Well, the inherent problem with a BLOB is that it... isn't text. It is binary. It could be an image (jpg etc), an exe, or it could be text... but in which encoding? UTF8? Not simple. If it was a CLOB I'd hope it works OK - but otherwise I would fully anticipate that you'd have to parse the BLOB manually, otherwise how would it be interpreted?

For an arbitrary BLOB, the closest I can think of is to just show something like the hex... but with DataTable even that is a challenge. You could do it for a class via a TypeConverter...