Monday, April 26, 2010

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


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

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


'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

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

End Sub
Implementation of cell formatting event:

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


 If e.ColumnIndex < 0 OrElse e.Value Is Nothing Then
  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


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)

End If 
Catch ex As Exception 
End Try

End Sub

Sample Output

No comments:

Post a Comment