Can I save changed information in datagridview at runtime?

Question

I have just started to learn C# and I'm a little bit confused about DataSet/DataTable/DataGridView. For now I'm having a simple form where there is DataGridView, listBox, Insert & Delete buttons. When my form loads in listBox I'm getting excel sheet's list. Then I choose one of the sheets and then it appears on DataGridView (all information I'm writing to DataSet and DataTable). I would like to add or delete columns at runtime. Here is my code for Delete button:

    excelConn = new OleDbConnection();
    excelConn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + excelFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
    DataTable dt = new DataTable();
    OleDbDataAdapter ExcelAdapt = new OleDbDataAdapter("Select * From " + "[" + tableName + "]", excelConn);
    ExcelAdapt.Fill(dt);
    try
    {
      dt.Columns.Remove(colName);
      dt.AcceptChanges();
      var bds = new BindingSource();
      bds.DataSource = dt;
      grid.DataSource = bds;

      MessageBox.Show("Column(s) deleted ");
      excelConn.Close();
      }

      catch (Exception ex)
      {
         MessageBox.Show(ex.Message);
      }

The problem I'm facing is that I can't save changes made at runtime and even at runtime these changes are temporary. If at runtime I add new column and then I choose another sheet from ListBox these changes disappear.

I found this tutorial but if I understood well it changes database; well in my case it would change excel file (but I want to have original excel file and making changes only in DataSet and DataTable).

Can I make changes only in DataSet/DataTable. If yes, then how?

Check out this answer: http://stackoverflow.com/questions/57987/writing-into-excel-file-with-oledb — udog, May 23 '12 at 21:25
No time to post an answer but the usual way to save a datatable is to serialize it to xml. That means you create an xml file on disk that represents the datatable. http://stackoverflow.com/questions/2458847/how-to-serialize-a-datatable-to-json-or-xml — David Hall, May 24 '12 at 07:18

AvkashChauhan · Accepted Answer · 2012-05-24T14:31:14.167

0

Here is the exact solution for your problem which does update the data from DataGridView to Excel, I just tested on my machine and it did worked:

http://blogs.msdn.com/b/spike/archive/2008/10/29/how-to-update-an-excel-worksheet-using-dataset-and-the-oledbdataadapter.aspx

[Edit]

For example the following code will remove Row #3 from the DataGridView1 and local copy of your ds however your EXCEL will not be affected.

dataGridView1.Rows.RemoveAt(2);
ds.Tables["[Sheet1$]"].Rows[2].Delete();

Later when you want to save this data to other EXCEL, you can just export to new EXCEL or same EXCEL different [SheetX].

edited May 24 '12 at 14:31

answered May 23 '12 at 23:06

AvkashChauhan

20,495
3
34
65

Well, I know that method with insert command but it changes excel document too. I want to keep changes only in DataSet/DataTable. Or I'm asking impossible ? Sorry, for my not perfect English and if it is difficult to understand what I am asking. – intentarr May 24 '12 at 06:52
I thought opposite what you just asked, your post was not very clearn. If that is the case you dont need to use DataSet.update or DataTable.update at all. When you make changes to your DataGridView, you can manually select the row/col from the DGV to modify/delete and this will update the local copy of DS/DT but your EXCEL will be unaffected. See above [Edit] section for the code sample. – AvkashChauhan May 24 '12 at 14:29

Can I save changed information in datagridview at runtime?

1 Answers1