我是否可以使用隐藏的 DataGridView 列来管理 SQL Server VarBinary 字段

我有一个表，我想在字段中存储一个字节数组。字节数组大约是 20 字节（160 位）的密钥数据。

我正在使用几种DataGridView来管理此表和此应用程序的其他表。我目前有几个例程允许我提供一个SQL选择字符串，DataGridView允许用户编辑数据。

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);
}

我有一个保存按钮，提供验证，然后调用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);
  }
}

当我将varbinary字段添加到表中时，它在DataGridView中显示为损坏的位图图像。这与我无关，因为我无论如何都不会显示它。

当我尝试为调用分配一个新的字节数组时，它会生成一个 DataGridView 默认错误对话框。

/// <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 中发生以下异常：System.ArgumentException： 参数无效。
at System.Drawing.Image.FromStream（Stream stream， Boolean useEmbeddedColorManagement， Boolean validateImageData） 在 System.Drawing.ImageConverter.ConvertFrom（ITypeDescriptorContext 上下文、文化信息文化、对象值） 在 System.Windows.Forms.Formatter.FormatObjectInternal（Object value， Type 目标类型， 类型转换器源转换器， 类型转换器 targetConverter， String formatString， IFormatProvider formatInfo， 对象格式化空值） 在 System.Windows.Forms.Formatter.FormatObject（Object value， Type 目标类型， 类型转换器源转换器， 类型转换器 targetConverter， String formatString， IFormatProvider formatInfo， Object formattedNullValue， Object dataSourceNullValue） 在 System.Windows.Forms.DataGridViewCell.GetFormattedValue（Object value， Int32 rowIndex， DataGridViewCellStyle&cellStyle， TypeConverter 值类型转换器， 类型转换器格式化值类型转换器， DataGridViewDataErrorContexts context）

要替换此默认对话框，请处理 DataError 事件。

这似乎与CellFormatting有关，但我不知道如何关闭它。

添加这段代码会提供一个更简单的对话框，其中包含以下错误消息：

System.FormatException：单元格的格式化值具有错误的类型。

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;
    }
  }
}

更新#1 - 使用Ivan Stoev建议的方法的附加代码，并显示我用于请求密码更改的按钮单元格。 它还增加了保存盐值。

此时的问题是，当我点击"更改密码"按钮时，我可以打开密码对话框，获取密码。生成 salt 和 key，将其保存到该行 salt 和 keyValue 的数据网格单元格中。

如果我执行TableSave()（请参阅前面的代码），则这些字段不在 SQL Server 表中，除非我单击网格中的其他行。该操作似乎表明该行是脏的，然后保存将起作用。

我还可以在更改密码之前或之后更改用户可见的字段之一，该行将保存。

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;
  }
}

更新 #2 将"NotifyCurrectCell"脏添加到"更改密码"方法中，通知网格需要保存单元格而不更改 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
  }
}