none
Comparing original to modified rows in DataTable RRS feed

  • Question

  • Hello,

    I'm using a DataGridView + DataTable, and want to compare the original values to the changes the user applied to the DataGridView and log the differences.

    I figured that's precisely the purpose of DataViewState.ModifiedOriginal and DataViewState.ModifiedCurrent, however if I...

    1. Fill the table with data

    2. Connect it to a DataGridView

    3. Modify the contents

    4. Compare the results of table.select(null, null, ModifiedOriginal) and table.select(null, null, ModifiedCurrent), the values are the same. I am not calling Update on the data adapter or AcceptChanges on the table.

    This is my code:

    SqlConnection conn = new SqlConnection("Data Source=localhost\\SQLEXPRESS;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;");
    SqlDataAdapter dataAdapter;
    DataTable table;
    DataTable origTable;
    BindingSource bindingSource;
    SqlCommandBuilder sqlCommandBuilder;
    
    public void populateGridView()
    {			
    	conn.Open();
    	dataAdapter = new SqlDataAdapter("select * from PretendTable", conn);
    	
    	sqlCommandBuilder = new SqlCommandBuilder(dataAdapter);
    	dataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
    	
    	table = new DataTable();
    	
    	dataAdapter.Fill(table);
    			
    	bindingSource = new BindingSource();
    	bindingSource.DataSource = table;
    	
    	grdDataGrid.DataSource = bindingSource;
    }

    Later I compare the results from the calls below, and the data is the same:

    table.Select(null, null, DataViewRowState.ModifiedOriginal);
    table.Select(null, null, DataViewRowState.ModifiedCurrent);

    Any thoughts on what I'm doing wrong?

    Thanks!

    Friday, April 5, 2013 7:53 PM

Answers

  • I don't know why that doesn't work ... I tried it, it didn't work for me either: the one column I changed was the same (the changed value) in both the ModifiedOriginal and ModifiedCurrent. Maybe that's why I've never used that methodology.

    I'm assuming that you want to compare the columns which have changed value? If so, here's what I *have* used to do that:

    DataSet dsChanged = MyDataSet.GetChanges();
    foreach (DataTable dt in dsChanged.Tables)
    {
        foreach (DataRow row in dt.Rows)
        {
            for (int i=0; i < dt.Columns.Count; i++) 
            { 
                if (!row[i, DataRowVersion.Current].Equals(row[i, DataRowVersion.Original])) 
                { 
                    // it's changed, process it here 
                } 
            }
        } 
    }
    Obviously, it'll need tweaking for whatever it is you're actually doing with the changed columns, but this should give you a start.

    This is the kind of thing I usually have on my blog, but I don't have a post for this (maybe I'll add one later).

    Hope it helps ....


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, April 6, 2013 4:37 PM

All replies

  • I don't know why that doesn't work ... I tried it, it didn't work for me either: the one column I changed was the same (the changed value) in both the ModifiedOriginal and ModifiedCurrent. Maybe that's why I've never used that methodology.

    I'm assuming that you want to compare the columns which have changed value? If so, here's what I *have* used to do that:

    DataSet dsChanged = MyDataSet.GetChanges();
    foreach (DataTable dt in dsChanged.Tables)
    {
        foreach (DataRow row in dt.Rows)
        {
            for (int i=0; i < dt.Columns.Count; i++) 
            { 
                if (!row[i, DataRowVersion.Current].Equals(row[i, DataRowVersion.Original])) 
                { 
                    // it's changed, process it here 
                } 
            }
        } 
    }
    Obviously, it'll need tweaking for whatever it is you're actually doing with the changed columns, but this should give you a start.

    This is the kind of thing I usually have on my blog, but I don't have a post for this (maybe I'll add one later).

    Hope it helps ....


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, April 6, 2013 4:37 PM
  • Yes, you are simply filtering the current records by state, so you get the same records returned with the current values!

    In order to return the prior state you need to create a new table with the results in.

    See these posts: http://msdn.microsoft.com/en-us/library/thc1eetk.aspx

    http://msdn.microsoft.com/en-us/library/ww3k31w0(v=vs.110).aspx

    To get changed rows you can do this:

    DataTable changedRecordsTable = dataTable1.GetChanges();

    To compare row values loop through the rows and access the row version:

    http://msdn.microsoft.com/en-us/library/7kd9zhee.aspx

    DataSet dataSet1 = new DataSet();
    DataTable changedRecords = dataSet1.Tables[0].GetChanges(DataRowState.Modified);
    for (int i = 0; i < changedRecords.Rows.Count; i++)
    {
        DataRow current = changedRecords.Rows[i];
        DataRow original = (DataRow)current[0, DataRowVersion.Original];                   
    }

    Regards

    Rupert


    the problem is not what you don't know it's what you think you know that's wrong


    Thursday, August 28, 2014 10:20 AM
  • Your code sample utilizing the DataRowVersion is pretty much the same as what I posted almost a year and a half ago (although you did explain things better with the links you provided). Why revive a year-old thread when it's already been sufficiently answered?

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, August 28, 2014 3:56 PM