Can I use a hidden DataGridView column to manage a SQL Server VarBinary field?

Question

I have a table that I want to store a byte array in a field. The byte array is about 20 bytes (160 bits) of key data.

I am using several DataGridView to manage this table and other tables for this application. I currently have a couple of routines that allow me to provide a SQL select string and the DataGridView allows the user to edit the data.

private void InitializeUsersDataGrid()
{
  string sql = "SELECT UserId, Enabled, AccessLevel, Name, KeyValue FROM Users";

  DataGridViewIntialize(dgvUsers, sql);
  dgvUsers.Columns[fdKeyValue].Visible = false;
}

private void DataGridViewIntialize(DataGridView dataGridView, string sql)
{
  dataGridViewInUse = dataGridView; // This is the current DataGridView
  OleDbConnection oleDbConnection = new OleDbConnection(txtConnectionString.Text);
  dataAdapter = new OleDbDataAdapter(sql, oleDbConnection);
  OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);  //Creates SQL commands for IUD

  dataTable = new DataTable();
  dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;

  dataAdapter.Fill(dataTable);
  bindingSource1.DataSource = dataTable;

  dataGridView.DataSource = bindingSource1;

  // Resize the DataGridView columns to fit the newly loaded content.
  dataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
  AddDeleteRefreshContextMenu(dataGridView);
}

I have a Save button that provides the validating and then calls TableSave.

private void TableSave()
{
  // Update the database with the user's changes.
  try {
    dataAdapter.Update((DataTable)bindingSource1.DataSource);
  }
  catch (Exception ex) {
    MessageBox.Show(ex.Message);
  }
}

When I added the varbinary field to the table it appeared in the DataGridView as a broken bitmap image. This didn’t concern me because I wasn’t going to display it anyway.

When I try and assign a new byte array to the call it generates a DataGridView Default Error Dialog.

/// <summary>
/// Change the password hash for the selected login
/// </summary>
/// <param name="e"></param>
void ChangePassword( DataGridViewCellEventArgs e)
{
  // Request a new password with confirmation
  fPassword form = new fPassword();
  form.Confirm = true;
  DialogResult dr = form.ShowDialog();
  if (dr == DialogResult.OK) {
    // Combine the UserID with the password to generate a new key
    byte[] salt;
    byte[] key;
    string p = dgvUsers.Rows[e.RowIndex].Cells["UserId"].Value + form.Value;
    Data.PBK2DF2Hash.GenerateSaltAndKey(p, out salt, out key);
    if (dgvUsers.Rows[e.RowIndex].Cells[fdKeyValue].ValueType == key.GetType()) {
      // We do get this far Error occurs on the assignment in the next line
      dgvUsers.Rows[e.RowIndex].Cells[fdKeyValue].Value = key;
    } 
  }
}

DataGridView Default Error Dialog

The following exception occurred in the DataGridView: System.ArgumentException: Parameter is not valid.
at System.Drawing.Image.FromStream(Stream stream, Boolean useEmbeddedColorManagement, Boolean validateImageData) at System.Drawing.ImageConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value) at System.Windows.Forms.Formatter.FormatObjectInternal(Object value, Type targetType, TypeConverter sourceConverter, TypeConverter targetConverter, String formatString, IFormatProvider formatInfo, Object formattedNullValue) at System.Windows.Forms.Formatter.FormatObject(Object value, Type targetType, TypeConverter sourceConverter, TypeConverter targetConverter, String formatString, IFormatProvider formatInfo, Object formattedNullValue, Object dataSourceNullValue) at System.Windows.Forms.DataGridViewCell.GetFormattedValue(Object value, Int32 rowIndex, DataGridViewCellStyle& cellStyle, TypeConverter valueTypeConverter, TypeConverter formattedValueTypeConverter, DataGridViewDataErrorContexts context)

To replace this default dialog please handle the DataError event.

It appears that has to do with CellFormatting but I don’t know how to turn it off.

Adding this bit of code provides a simpler dialog with this error message:

System.FormatException: Formatted value of the cell has a wrong type.

private void dgvUsers_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
  DataGridViewColumn c = dgvUsers.Columns[fnKeyValue];
  if (c != null) {
    if (e.ColumnIndex == c.Index) {
      if (e.Value != null) {
        e.FormattingApplied = true;
      }
      else
        e.FormattingApplied = false;
    }
  }
}

Update #1 - Additional code using Ivan Stoev suggested method and shows the button cell that I used to solicit the password change. It also adds saving the salt value.

The issue at this point is that when I hit the Change Password button I can open my password dialog, get the password. Generate the salt and key, save it to the datagrid cells for that row/s salt and keyValue.

If I do a TableSave() (see earlier code) these fields are not in the SQL server table unless I do a mouse click on a different row in the grid. That action seems to indicate the row is dirty and then the save will work.

I can also change one of the fields visible to the user before or after I change the password and the row will save.

private void InitializeUsersDataGrid()
{
  string sql = "SELECT UserId, Enabled, AccessLevel, Name, Salt, KeyValue FROM Users";

  AddAccessLevelColumn(dgvUsers);  //Add column if needed
  AddPasswordChangeColumn(dgvUsers);
  DataGridViewIntialize1(dgvUsers, sql);
  dgvUsers.Columns[UsersFieldName.fdAccessLevel].Visible = false;  //Hide the raw column from the database

  MoveAccessLevelColumn(dgvUsers, AccessLevelComboBoxColumn, AccessLevelColumnPosition);
  MoveAccessLevelColumn(dgvUsers, PasswordButtonColumn, PasswordColumnPosition);
}

private void DataGridViewIntialize1(DataGridView dataGridView, string sql)
{
  dataGridViewInUse = dataGridView; // This is the current DataGridView
  OleDbConnection oleDbConnection = new OleDbConnection(txtConnectionString.Text);
  dataAdapter = new OleDbDataAdapter(sql, oleDbConnection);
  OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);  //Creates SQL commands for IUD

  dataTable = new DataTable();
  dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;

  dataAdapter.Fill(dataTable);
  dataTable.Columns[Data.UsersFieldName.fdSalt].ColumnMapping = MappingType.Hidden;
  dataTable.Columns[Data.UsersFieldName.fdKey].ColumnMapping = MappingType.Hidden;
  bindingSource1.DataSource = dataTable;

  dataGridView.DataSource = bindingSource1;

  // Resize the DataGridView columns to fit the newly loaded content.
  dataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
  AddDeleteRefreshContextMenu(dataGridView);
}

private void AddPasswordChangeColumn(DataGridView dataGridView)
{
  // This column will remain unless manually removed, so it only needs to be added
  // the first time the DVG is initialized.

  if (dataGridView.Columns[PasswordButtonColumn] == null) {
    // creating new ComboBoxCell Column
    DataGridViewButtonColumn btnColumn = new DataGridViewButtonColumn();
    btnColumn.Text = "Change Password";
    btnColumn.HeaderText = "Change Password";
    btnColumn.UseColumnTextForButtonValue = true;
    btnColumn.Name = PasswordButtonColumn;
    btnColumn.FlatStyle = FlatStyle.Popup;
    dataGridView.Columns.Insert(0, btnColumn);
    // Add a CellClick handler to handle clicks in the button column.
    dataGridView.CellClick += dataGridView_CellClick;
  }
}

private void dataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
  // Ignore clicks that are not on button cells.
  if (e.RowIndex >= 0 && e.RowIndex < dgvUsers.RowCount - 1 && e.ColumnIndex == dgvUsers.Columns[PasswordButtonColumn].Index) {
    ChangePassword(e);
  }
}

/// <summary>
/// Change the password hash for the selected login
/// </summary>
/// <param name="e"></param>
private void ChangePassword(DataGridViewCellEventArgs e)
{
  // Request a new password with confirmation
  fPassword form = new fPassword();
  form.Confirm = true;
  DialogResult dr = form.ShowDialog();
  if (dr == DialogResult.OK) {
    // Combine the UserID with the password to generate a new key
    byte[] salt;
    byte[] key;
    string p = dgvUsers.Rows[e.RowIndex].Cells[Data.UsersFieldName.fdUserId].Value + form.Value;
    Data.PBK2DF2Hash.GenerateSaltAndKey(p, out salt, out key);
    var gridRow = dgvUsers.Rows[e.RowIndex];
    var dataRow = (DataRowView)gridRow.DataBoundItem;
    var value = dataRow[Data.UsersFieldName.fdKey];
    dataRow[Data.UsersFieldName.fdSalt] = salt;  //This assignment now works
    dataRow[Data.UsersFieldName.fdKey] = key;
  }
}

Update #2 Adding 'NotifyCurrectCell' Dirty to the 'ChangePassword' method notifies the grid that the cell needs to be saved without changing rows in the UI.

private void ChangePassword(DataGridViewCellEventArgs e)
{
  // Request a new password with confirmation
  fPassword form = new fPassword();
  form.Confirm = true;
  DialogResult dr = form.ShowDialog();
  if (dr == DialogResult.OK) {
    // Combine the UserID with the password to generate a new key
    byte[] salt;
    byte[] key;
    string p = dgvUsers.Rows[e.RowIndex].Cells[Data.UsersFieldName.fdUserId].Value + form.Value;
    Data.PBK2DF2Hash.GenerateSaltAndKey(p, out salt, out key);
    var gridRow = dgvUsers.Rows[e.RowIndex];
    var dataRow = (DataRowView)gridRow.DataBoundItem;
    var value = dataRow[Data.UsersFieldName.fdKey];
    dataRow[Data.UsersFieldName.fdSalt] = salt;  //This assignment now works
    dataRow[Data.UsersFieldName.fdKey] = key;
    dgvUsers.NotifyCurrentCellDirty(true); //Tells the UI that this row has changed and needs updating
  }
}

Your image form is not valid. ImageFromStream() must be a valid image format otherwise you will get an exception. — jdweng
Data Type are found in System.Data.SqlDbType. Binary should work. To specify type you must add a parameter to your SQL Command. The parameter contains the data type. — jdweng
@jdweng I've tried adding a CAST, but that generally isn't working. Did you mean something else? string sql = "SELECT UserId, Enabled, AccessLevel, Name, CAST(KeyValue AS BINARY) FROM Users — Rich Shealer
You have to set the database column to binary. The type will automatically be transferred to VS as binary. You don't need a cast. — jdweng

Ivan Stoev Ivan Stoev · Accepted Answer · 2016-01-16T16:33:33

The problem you are experiencing is because DataGridView creates by default DataGridViewImageColumn for byte[] data type.

Here are some options.

A. Set DataGridView.AutoGenerateColumns property to false and create the grid columns manually.
B. If you don't really need that column in the grid, instead of trying to hide the grid column, do not create grid column at all. But how to achieve that? For class property one would use Browsable(false), but this is DataTable. Well, although not documented, one can use DataColumn.ColumnMapping property with MappingType.Hidden for the same purpose.

In your case, remove this line

dgvUsers.Columns[fdKeyValue].Visible = false;

and inside the DataGridViewIntialize method use

// ...
dataAdapter.Fill(dataTable);
dataTable.Columns[fdKeyValue].ColumnMapping = MappingType.Hidden;
bindingSource1.DataSource = dataTable;
// ...

You still can access your column data by using underlying data source like this

var gridRow = dgvUsers.Rows[...];
var dataRow = (DataRowView)gridRow.DataBoundItem;
var value = dataRow[fdKeyValue];

Can I use a hidden DataGridView column to manage a SQL Server VarBinary field?

1 Answers