X11 X11 - 3 months ago 19
C# Question

DataGridView skip Column from Updating

Is there any possibility to skip or delete specific column of

DataGridView
column from updating with database?

I need to prevent column from updating to the database, because the column values is encrypted and when I decrypt, the decrypted values updated to the database.

I use this code before, but this really slows the gridview

private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
if (hide == false && e.ColumnIndex == 2 && e.RowIndex != this.dataGridView1.NewRowIndex)
{
e.Value = Decrypt(e.Value.ToString());
}
}


And I update values with this code:

private void dataGridView1_RowValidated(object sender, DataGridViewCellEventArgs e)
{
DataTable changes = ((DataTable)dataGridView1.DataSource).GetChanges();

if (changes != null)
{
MySqlCommandBuilder mcb = new MySqlCommandBuilder(mySqlDataAdapter);
((DataTable)dataGridView1.DataSource).AcceptChanges();
mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand();

mySqlDataAdapter.Update(changes);
}
}

Answer

Based on what the title is saying, it's enough to set ReadOnly property of the column to true, but since you want to show decrypted value of the column in your grid, to prevent the column from being updated in database, you can use either of these options:

  • Show encrypted value in the column itself and change your update command to not contain statement for updating that specific column.
  • You can show the encrypted value in another unbound column.

In this post I show you an example of the second solution.

I suppose you have a string Decrypt(string value) method which decrypts an encrypted string. Also I can you have a column "A" which contains encrypted value and as the question you want to show decrypted value in a "B" column in grid.

So perform these steps:

  • Set Visible property of "A" column to false.
  • Add a DataGridViewTextBox column and set its name to "B"
  • Handle CellFormatting event of DtaGridView like below:
private void grid_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
    if(e.ColumnIndex<0 || e.RowIndex<0)
        return;

    var columnB = grid.Columns[e.ColumnIndex];
    if (columnB.Name != "B")
        return;

    var value = grid.Rows[e.RowIndex].Cells["A"].Value;
    if (value == null || value == DBNull.Value)
        return;

    //Usually its enough to set cell.Value = Decrypt(value.ToString());
    //But since you may have performance issues by Decrypt
    //I check if the cell doesn't have value, will set the value for cell
    var cell = grid.Rows[e.RowIndex].Cells["B"];
    if (cell.Value== null || cell.Value == DBNull.Value)
    {
        cell.Value = Decrypt(value.ToString());
    }
}

Note

  • It's not good idea to save changes on after you leave row. It's better to save changes after the edit is finished by click on a Save button.
  • You don't need to call AcceptChanges. In fact you should not!