Overcoming .NET problem of displaying binary columns in a DataGridView

Question

If a DataSet contains a column that is a timestamp or other binary value, its associated DataGridView throws an ArgumentException, when displaying any data in that column. That is, assume you have some table containing a binary column such as:

CREATE TABLE [dbo].[DataTest](
    [IdStuff] INT IDENTITY(1,1) NOT NULL,
    [ProblemColumn] TIMESTAMP NOT NULL )

In Visual Studio 2008, add a new Data Source pointing to the suspect table. Drag the table from the Data Source explorer onto the visual designer surface of a new WinForm to automatically create a DataGridView, BindingSource, etc. Execute the application and you will get a runtime exception. Sounds like a defect, right?

If you examine the Columns collection of the DataGridView you will find that it sets the column type to DataGridViewImageColumn. Why? Because, according to Microsoft, .NET assumes that binary columns are images. Indeed, Microsoft affirms that this behavior is by design! See this defect report on Microsoft Connect: http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=93639

One could suppress the error dialog by handling the DataError event for the DataGridView, as the dialog politely indicates, but that begs the question. I want to find a way to avoid having an error condition in the first place. That is, I want to have a DataGridViewTextColumn showing a textual representation of the binary data, e.g. "0x1234a8e9433bb2". And I am looking for a generic solution, since my actual code does not use a specific table as in my example above. Rather I put a somewhat arbitrary query into a dataAdapter.SelectCommand, then invoke

dataAdapter.Fill(dataTable)

to auto-generate my dataTable. Since it is the DataGridView that has the (IMHO) bug, I am thinking that I need to check the columns of the data table (i.e. dataTable.Columns[n].DataType.Name.Equals("Byte[]") ? ) and convert any byte arrays to their text forms manually before I connect the dataTable to the DataGridView with

bindingSource.DataSource = dataTable;

My question then:

Is there a simpler or more elegant way to display binary columns in a DataGridView?

(Note that this problem exists with both VS 2005 and VS 2008, .NET 2.0 and .NET 3.5.)

Fervor

Answer 1

Spurred on by Quandary's answer, plus having allowed sufficient time since posting my question to have a fresh perspective :-), I came up with a reasonably clean solution in the guise of the MorphBinaryColumns method below, embedded in a complete sample test program (except for VS's designer generated code from my WinForm containing a single DataGridView).

MorphBinaryColumns examines the column collection and, for each that is a binary column, generates a new column with the value converted to a hex string, then swaps out the original column replacing it with the new one, preserving the original column order.

public partial class Form1 : Form
{
  public Form1()
  {
    InitializeComponent();
  }

  private void Form1_Load(object sender, EventArgs e)
  {
    var sqlCnn = new SqlConnection("..."); // fill in your connection string
    string strsql = "select ... from ..."; // fill in your query

    var dataAdapter = new SqlDataAdapter();
    var dataTable = new DataTable();
    dataAdapter.SelectCommand = new SqlCommand(strsql, sqlCnn);
    dataAdapter.Fill(dataTable);
    MorphBinaryColumns(dataTable);
    dataGridView1.DataSource = dataTable;
  }

  private void MorphBinaryColumns(DataTable table)
  {
    var targetNames =  table.Columns.Cast<DataColumn>()
      .Where(col => col.DataType.Equals(typeof(byte[])))
      .Select(col => col.ColumnName).ToList();
    foreach (string colName in targetNames)
    {
      // add new column and put it where the old column was
      var tmpName = "new";
      table.Columns.Add(new DataColumn(tmpName, typeof (string)));
      table.Columns[tmpName].SetOrdinal(table.Columns[colName].Ordinal);

      // fill in values in new column for every row
      foreach (DataRow row in table.Rows)
      {
        row[tmpName] = "0x" + string.Join("",
          ((byte[]) row[colName]).Select(b => b.ToString("X2")).ToArray());
      }

      // cleanup
      table.Columns.Remove(colName);
      table.Columns[tmpName].ColumnName = colName;
    }
  }
}

Fervor

Answer 2

Adding a few improvements to above approach. #1 handling null binary columns, #2 improved performance when converting lots of columns (using same string builder over and over), #3 maximum display length of 8000 to avoid converting really large binary columns to string... #4 creating temp column name using a guid to avoid name collisions in case there is a column named "temp"...

/// <summary>
/// Maximum length of binary data to display (display is truncated after this length)
/// </summary>
const int maxBinaryDisplayString = 8000;

/// <summary>
/// Accepts datatable and converts all binary columns into textual representation of a binary column
/// For use when display binary columns in a DataGridView
/// </summary>
/// <param name="t">Input data table</param>
/// <returns>Updated data table, with binary columns replaced</returns>
private DataTable FixBinaryColumnsForDisplay(DataTable t)
{
    List<string> binaryColumnNames = t.Columns.Cast<DataColumn>().Where(col => col.DataType.Equals(typeof(byte[]))).Select(col => col.ColumnName).ToList();
    foreach (string binaryColumnName in binaryColumnNames)
    {
        // Create temporary column to copy over data
        string tempColumnName = "C" + Guid.NewGuid().ToString();
        t.Columns.Add(new DataColumn(tempColumnName, typeof(string)));
        t.Columns[tempColumnName].SetOrdinal(t.Columns[binaryColumnName].Ordinal);

        // Replace values in every row
        StringBuilder hexBuilder = new StringBuilder(maxBinaryDisplayString * 2 + 2);
        foreach (DataRow r in t.Rows)
        {
            r[tempColumnName] = BinaryDataColumnToString(hexBuilder, r[binaryColumnName]);
        }

        t.Columns.Remove(binaryColumnName);
        t.Columns[tempColumnName].ColumnName = binaryColumnName;
    }
    return t;
}
/// <summary>
/// Converts binary data column to a string equivalent, including handling of null columns
/// </summary>
/// <param name="hexBuilder">String builder pre-allocated for maximum space needed</param>
/// <param name="columnValue">Column value, expected to be of type byte []</param>
/// <returns>String representation of column value</returns>
private string BinaryDataColumnToString(StringBuilder hexBuilder, object columnValue)
{
    const string hexChars = "0123456789ABCDEF";
    if (columnValue == DBNull.Value)
    {
        // Return special "(null)" value here for null column values
        return "(null)";
    }
    else
    {
        // Otherwise return hex representation
        byte[] byteArray = (byte[])columnValue;
        int displayLength = (byteArray.Length > maxBinaryDisplayString) ? maxBinaryDisplayString : byteArray.Length;
        hexBuilder.Length = 0;
        hexBuilder.Append("0x");
        for(int i = 0; i<displayLength; i++)
        {
            hexBuilder.Append(hexChars[(int)byteArray[i] >> 4]);
            hexBuilder.Append(hexChars[(int)byteArray[i] % 0x10]);
        }
        return hexBuilder.ToString();
    }
}

Westphalia

Answer 3

You might find this useful: http://social.msdn.microsoft.com/Forums/en/winformsdatacontrols/thread/593606df-0bcb-49e9-8e55-497024699743

Basically:

• Getting the data from the DB to the datatable
• then adding a new column (typeof(string))

• then write the binary content over (using bytearray to hex string) into that new column

• then databind.

It's simple and annoying, yet it effectively solves the problem.

Sihunn

Essentially, your summary reiterates concisely what I stated in my question. Nevertheless, your input spurred me on to take a fresh look at the problem and come up with a generic solution, so I am giving you an upvote. Thanks! – Fervor

Essentially, you are correct, as I did only read the upper part. Sorry for that. However, you wanted to do it in the GridView itselfs, which is kinda overly complex. But never the less, +1 for that very nice piece of code. – Sihunn

Answer 4

How about basing your query on a view that does a CAST for that column?

Sapajou

Two reasons: (1) I don't think there is a way to do it with CAST in a query. Microsoft shows what is presumably the simplest way to do it, requiring a stored procedure; see support.microsoft.com/kb/104829 (2) I would like it to be transparent to the user who entered the arbitrary query string. – Fervor