none
Tableadapter updates all rows for datatable with calculated column RRS feed

  • Question

  • Hi All

    I have a SQL DB with several columns linked to a datatable in VB.net.  I have added a couple of columns to the datatable where I loop through the data and calculate values for the these added columns.  I don't need to save the data in these calculated columns in the DB so they are not connected to any columns in the DB and are just displayed in the columns of a datagridview.

    The issue seems to be that because the values in these calculated columns are updated all rows in the datatable get marked as changed.  When the tableadapter is updated it updates all the rows in the table, not just the rows with actual changes.   With a local DB this isn't a problem however I am trying to migrate the application to SQL Azure and it uses a lot of transactions and seems to hang the application. 

    Is there a way I should handle this that would be more efficient or overcome the calculated columns changing the row state before calling the tableadapter.update

    Appreciate any assistance.

    ilr

    Monday, September 6, 2010 5:12 AM

Answers

  • Another way around the problem is to not use TableAdapters and to handle the updates yourself with DataAdapters instead. You could use the DataSet.GetChanges() to only deal with the changes rows and weed out the rows that have changed only due to the calculated columns. You can find the changed columns like this:

    DataTable dtChanged = MyDataTable.GetChanges();
    DataRow row;
    bool IsChanged;
    // iterate backwards through the rows, since we'll be removing those that aren't really changed
    for (int i = dtChanged.Rows.Count-1, i >= 0; i--)
    {
      row = dtChanged.Rows[i];
      IsChanged = false;
      for (int j=0; j < dtChanged.Columns.Count; j++) 
      { 
        if (!row[j, DataRowVersion.Current].Equals(row[j, DataRowVersion.Original])) 
        { 
          // it's changed, check to see if it's NOT a calculated column 
          if (dtChanged.Column[j].ColumnName != "MyCalculatedColumn")
          {
            IsChanged = true;
            break;
          }
        } 
      }
      if (!IsChanged) // only changes were calculated column, so remove row from this table
        dtChanged.Rows[i].Remove();
    }
    

    This is off the top of my head, so please excuse me if there are any typos or if it needs a bit more tweaking. At any rate, once you have the real changes in the dtChanged table, you use that table with the DataAdapter to do the actual update to the database.

    UPDATE: I updated the above code to take care of the problem I noted in a subsequent post below (using the same i iterator in both loops. The inner loop now iterates with j).


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, September 13, 2010 5:06 AM
  • Hi ilr,

     

    Bonnie's post is great!   I just translate the sample codes into VB.NET for your references:

    ========================================================================== 

        Dim dtChanged = MyDataTable.GetChanges
        Dim i As Integer = (dtChanged.Rows.Count - 1)
        Do While (i >= 0)
            Dim row As DataRow = dtChanged.Rows.Item(i)
            Dim IsChanged As Boolean = False
            Dim j As Integer
            For j = 0 To dtChanged.Columns.Count - 1
                If (Not row.Item(i, DataRowVersion.Current).Equals(row.Item(i, DataRowVersion.Original)) AndAlso (dtChanged.Columns.Item(j).ColumnName <> "MyCalculatedColumn")) Then
                    IsChanged = True
                    Exit For
                End If
            Next j
            If Not IsChanged Then
                dtChanged.Rows.Item(i).Delete
            End If
            i -= 1
        Loop

    ========================================================================== 

     

    If you have any questions, please feel free to let me know.

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by ilr Tuesday, September 14, 2010 12:09 AM
    Monday, September 13, 2010 9:44 AM
    Moderator
  • A couple of things:

    First, thanks for translating my C# code to VB, Lingzhi. I see that I inadvertantly had used i for the iterator in both loops, and you changed it to i and j (I had added the outer loop after the fact and just by force of habit used i for the iterator). I updated my code to fix this problem. Thanks for catching it!

    However, Lingzhi, you Deleted the row rather than Removed it (as I did) and that won't work because the row then remains in the DataTable (but has a RowState of Deleted), whereas we want to totally get rid of it ... the DataAdapter doesn't need to know about it at all. Remove does that.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by ilr Tuesday, September 14, 2010 12:08 AM
    • Unmarked as answer by ilr Tuesday, September 14, 2010 12:09 AM
    • Marked as answer by ilr Tuesday, September 14, 2010 12:09 AM
    Monday, September 13, 2010 3:08 PM

All replies

  • Hello ilr,

     

    Welcome to ADO.NET DataSet forum!

     

    Can you add these calculated columns as partial properties instead of additional columns in the typed data row?   The typed data row classes are all partial classes.   We can add our custom properties outside the DataSet.Designer.cs file.   If such custom properties are updated, the certain data row would be in changed status, so the TableAdapter won’t update these rows.  

     

    Does it work for you?  

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, September 6, 2010 9:33 AM
    Moderator
  • Hi ilr,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 9, 2010 1:34 AM
    Moderator
  • Another thought... why don't you just perform your calculations in SQL server and return the results as read-only columns in the data table.  In this case there is no need to update the rows in the data table and hence their rowstate will not be set to modified.  If you do it like this then the calculations can be set-based and are likely to be more efficient than looping through the rows and calculating data in the dataset.  You might also be able to return less columns in the query... perhaps some of your columns are only used in the calculations and are not displayed in the grid, which means they are not required in the data table... this will also make things more efficient!

    This might not be suitable, as it depends on how you are displaying the data, how you are updating data, etc... but hopefully will give you something to think about.  In a number of cases I use separate data tables for querying and updating data, which works well for me.

     

     

     

    Thursday, September 9, 2010 3:23 PM
  • Thanks Lingzhi

    That might be a possibility but I'm not really sure about how I would go about implementing it.  I'm using a lot of binding sources and datagridviews to display the child rows based on the parent being selected and are not sure how much work would be involved in making sure the partial properties are also displayed correctly.  

    Perhaps you could give me some further information on how I would go about it?

    Regards

    Ian

    Monday, September 13, 2010 2:35 AM
  • I have been considering doing the calculations in SQL and need to apply some more thought to it.  This might solve several problems I'm having and perhaps speed up the responsiveness of my application.  The calculations are reasonably complicated and my knowldege of SQL is limited so it would take a lot of work for me to change.  

    At the moment my application does the calculations and this takes time for all the rows.  New data is being added to the tables from a datalogger fairly regularly so is there a way I can do these calculations in SQL (or SQL Azure) on a timed basis or trigger it to do the calculations?  Either that or I guess I have my application run a stored procedure when the calculations are required.

    Regards

    Ian

     

     

    Monday, September 13, 2010 2:46 AM
  • Another way around the problem is to not use TableAdapters and to handle the updates yourself with DataAdapters instead. You could use the DataSet.GetChanges() to only deal with the changes rows and weed out the rows that have changed only due to the calculated columns. You can find the changed columns like this:

    DataTable dtChanged = MyDataTable.GetChanges();
    DataRow row;
    bool IsChanged;
    // iterate backwards through the rows, since we'll be removing those that aren't really changed
    for (int i = dtChanged.Rows.Count-1, i >= 0; i--)
    {
      row = dtChanged.Rows[i];
      IsChanged = false;
      for (int j=0; j < dtChanged.Columns.Count; j++) 
      { 
        if (!row[j, DataRowVersion.Current].Equals(row[j, DataRowVersion.Original])) 
        { 
          // it's changed, check to see if it's NOT a calculated column 
          if (dtChanged.Column[j].ColumnName != "MyCalculatedColumn")
          {
            IsChanged = true;
            break;
          }
        } 
      }
      if (!IsChanged) // only changes were calculated column, so remove row from this table
        dtChanged.Rows[i].Remove();
    }
    

    This is off the top of my head, so please excuse me if there are any typos or if it needs a bit more tweaking. At any rate, once you have the real changes in the dtChanged table, you use that table with the DataAdapter to do the actual update to the database.

    UPDATE: I updated the above code to take care of the problem I noted in a subsequent post below (using the same i iterator in both loops. The inner loop now iterates with j).


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, September 13, 2010 5:06 AM
  • Thanks Bonnie

    That could be my answer.  Don't suppose you could convert that to VB as it will take me quite a while to convert it as I'm not very familiar with C#.

    Regards

    Ian

    • Marked as answer by ilr Tuesday, September 14, 2010 12:09 AM
    • Unmarked as answer by ilr Tuesday, September 14, 2010 12:09 AM
    Monday, September 13, 2010 6:22 AM
  • Hi ilr,

     

    Bonnie's post is great!   I just translate the sample codes into VB.NET for your references:

    ========================================================================== 

        Dim dtChanged = MyDataTable.GetChanges
        Dim i As Integer = (dtChanged.Rows.Count - 1)
        Do While (i >= 0)
            Dim row As DataRow = dtChanged.Rows.Item(i)
            Dim IsChanged As Boolean = False
            Dim j As Integer
            For j = 0 To dtChanged.Columns.Count - 1
                If (Not row.Item(i, DataRowVersion.Current).Equals(row.Item(i, DataRowVersion.Original)) AndAlso (dtChanged.Columns.Item(j).ColumnName <> "MyCalculatedColumn")) Then
                    IsChanged = True
                    Exit For
                End If
            Next j
            If Not IsChanged Then
                dtChanged.Rows.Item(i).Delete
            End If
            i -= 1
        Loop

    ========================================================================== 

     

    If you have any questions, please feel free to let me know.

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by ilr Tuesday, September 14, 2010 12:09 AM
    Monday, September 13, 2010 9:44 AM
    Moderator
  • A couple of things:

    First, thanks for translating my C# code to VB, Lingzhi. I see that I inadvertantly had used i for the iterator in both loops, and you changed it to i and j (I had added the outer loop after the fact and just by force of habit used i for the iterator). I updated my code to fix this problem. Thanks for catching it!

    However, Lingzhi, you Deleted the row rather than Removed it (as I did) and that won't work because the row then remains in the DataTable (but has a RowState of Deleted), whereas we want to totally get rid of it ... the DataAdapter doesn't need to know about it at all. Remove does that.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by ilr Tuesday, September 14, 2010 12:08 AM
    • Unmarked as answer by ilr Tuesday, September 14, 2010 12:09 AM
    • Marked as answer by ilr Tuesday, September 14, 2010 12:09 AM
    Monday, September 13, 2010 3:08 PM
  • Thank you both.

    I'll adapt it to my application and let you know if I have any further queries.

    Appreciate your assistance.

    Ian

     

    Tuesday, September 14, 2010 12:13 AM
  • You're right, Bonnie!  :-)

     

     

    Good day!

     

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, September 14, 2010 2:24 AM
    Moderator
  • Always glad to help, Ian. I hope you get it all working. Let us know if you still  have problems.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, September 14, 2010 3:45 PM