Display varbinary columns in Datagridview

Display varbinary columns in DataGridview,VB.NET,.NET Tips,Display varbinary,Display varbinary in Datagridview Varbinary to string,Handling of varbinary data,SQL Varbinary datatype, Byte[], Datagridview
I recently faced an issue while displaying SQL query results in a DataGridView, it was crashing on varbinary columns. The reason was that the default format for a varbinary column is to display it as an image. While that seems like a fine default, there is no way to change it.

The following VB.NET code shows how could I handle the varbinary data display after binding the dynamic datatable to DataGridview.

Steps to handle this scenario:

1. Bind the DataGridview DataSource with the datatable.
2. Create a new DataGridview Column of type String
2. Set the DataPropertyName and Name of new Datagridview Column as the Varbinary column name.
3. Remove the original  varbinary column from the DataGridview
4. Insert the new column at the same position as the original column in the Datagridview
5. Implement the CellFormatting event and set the value in the format you want . I have converted each byte  into hex format and displayed the value.

'Global declarationDim sByteArrayColumns As New ArrayList


Private Sub PopulateGrid()

RemoveHandler dbGridView.CellFormatting, AddressOf dbGridView_CellFormatting
AddHandler dbGridView.CellFormatting, AddressOf dbGridView_CellFormatting
  
Dim dataSource As New BindingSource(dt, Nothing)

dbGridView.DataSource = dataSource


sByteArrayColumns.Clear()


For colCnt As Integer = dbGridView.ColumnCount - 1 To 0 Step -1


If DirectCast(dbGridView.Columns(colCnt).ValueType, System.Type).IsArray Then


 sByteArrayColumns.Add(colCnt)

'Creation of new datagridview column
Dim dgColumn As New DataGridViewTextBoxColumn()
dgColumn.DataPropertyName = dbGridView.Columns(colCnt).Name
dgColumn.Name = dbGridView.Columns(colCnt).Name
dgColumn.HeaderText = dbGridView.Columns(colCnt).Name
dgColumn.ValueType = GetType(String)

'Removal of original varbinary column
dbGridView.Columns.RemoveAt(colCnt)

'Add the new column
dbGridView.Columns.Insert(colCnt, dgColumn)
dgColumn.Dispose()
dgColumn = Nothing
End If
Next

End Sub
Implementation of cell formatting event:

Private Sub dbGridView_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs)



Try


 If e.ColumnIndex < 0 OrElse e.Value Is Nothing Then
      Return
  End If

   Dim bArrayField As Boolean = False


   For i As Integer = 0 To sByteArrayColumns.Count - 1


   If e.ColumnIndex = CInt(sByteArrayColumns(i)) Then
        bArrayField = True
        Exit For
   End If


Next
 

If bArrayField Then
  Dim array As Byte() = DirectCast(e.Value, Byte())
  e.Value = String.Empty


  For Each b As Byte In array 'convert each byte value to hex value
      e.Value += [String].Format("{0:X2}", b)
Next

End If 
Catch ex As Exception 
End Try

End Sub


Sample Output