none
Do I need to merge or update a data table.? RRS feed

  • Question

  • Hi;

    I am trying to understand which approach to use when building a data table using data from multiple tabes in a dataset.

    I created my data table structure and I am looping through the tables in my data set inserting rows as I loop through.

    My problem is that I appending rows from each table and repeating the datakeys as I append the tables.

    I thought at one point that I needed to relates the tables but that is not exactly what I need.

    Maybe a merge/ group/ sort or an update from the subsequent tables once the first table has been inserted ?

    Here is my code - picture is worth 1000 words.

    Thanks for your suggestions !

     Public Function FxDatSet(ByVal dsCandidate As DataSet) As DataTable
        Dim aDataTable As New DataTable("aDataTable")
        Dim tempOfficer As String = ""
        Dim holdName As String = ""
        Dim holdTCnt = dsCandidate.Tables.Count
        Dim tblsCnted As Integer = 0
        Try
          'Create dataTable for Manager Report
    
          aDataTable.Columns.Add("Officer", GetType(String))
          'aDataTable.Columns.Add("Branch", GetType(String)) 
          aDataTable.Columns.Add("Admin Reviews", GetType(Int32)).AllowDBNull = True
          aDataTable.Columns.Add("Compliance Monitoring", GetType(Int32)).AllowDBNull = True
          aDataTable.Columns.Add("Investment Reviews", GetType(Int32)).AllowDBNull = True
          aDataTable.Columns.Add("Overdrafts", GetType(Int32)).AllowDBNull = True
          aDataTable.Columns.Add("Large Cash Balances", GetType(Int32)).AllowDBNull = True
          aDataTable.Columns.Add("Losses", GetType(Int32)).AllowDBNull = True
          aDataTable.Columns.Add("Security Trans Reporting", GetType(Int32)).AllowDBNull = True
          aDataTable.Columns.Add("Totals", GetType(Int32))
          Dim rowTotal As DataRow = aDataTable.NewRow
    
          'Add Data from Web service to new dataTable
    
          Dim row1 As DataRow
          'Dim holdTable As DataTable
          Dim colTotal As Integer
    
    
          Do While tblsCnted < holdTCnt
    
            For Each row1 In dsCandidate.Tables.Item(tblsCnted).Rows
              Dim ToDoRow As DataRow = aDataTable.NewRow
              ' ToDoRow("Officer") = row1.Item(0).ToString
    
              tempOfficer = row1.Item(0).ToString
              holdName = FindAName(tempOfficer)
              ToDoRow("Officer") = FindAName(holdName)
    
              'ToDoRow("Branch") = row1.Item(1).ToString
    
              'Select column number based on the value in Item(3)
              Select Case DirectCast(row1.Item(2), Integer)
                Case Is = 1
                  If Not IsDBNull(row1.Item(3)) Then
                    ToDoRow("Admin Reviews") = row1.Item(3)
                  Else
                    ToDoRow("Admin Reviews") = 0
                  End If
                Case Is = 2
                  If Not IsDBNull(row1.Item(3)) Then
                    ToDoRow("Compliance Monitoring") = row1.Item(3)
                  Else
                    ToDoRow("Compliance Monitoring") = 0
                  End If
                Case Is = 3
                  If Not IsDBNull(row1.Item(3)) Then
                    ToDoRow("Investment Reviews") = row1.Item(3)
                  Else
                    ToDoRow("Investment Reviews") = 0
                  End If
                Case Is = 4
                  If Not IsDBNull(row1.Item(3)) Then
                    ToDoRow("Overdrafts") = row1.Item(3)
                  Else
                    ToDoRow("Overdrafts") = 0
                  End If
                Case Is = 5
                  If Not IsDBNull(row1.Item(3)) Then
                    ToDoRow("Large Cash Balances") = row1.Item(3)
                  Else
                    ToDoRow("Large Cash Balances") = 0
                  End If
                Case Is = 6
                  If Not IsDBNull(row1.Item(3)) Then
                    ToDoRow("losses") = row1.Item(3)
                  Else
                    ToDoRow("losses") = 0
                  End If
                Case Is = 7
                  If Not IsDBNull(row1.Item(3)) Then
                    ToDoRow("Security Trans Reporting") = row1.Item(3)
                  Else
                    ToDoRow("Security Trans Reporting") = 0
                  End If
              End Select
    
              ' Total by column 
              ToDoRow("Totals") = row1.Item(3)
              colTotal += DirectCast(row1.Item(3), Integer)
              aDataTable.Rows.Add(ToDoRow)
            Next
    
            tblsCnted += 1
          Loop
          'Create column / row totals
          rowTotal(0) = "Totals"
          rowTotal(1) = colTotal
          'rowTotal(3) = colTotal
          rowTotal(8) = colTotal
          aDataTable.Rows.Add(rowTotal)
        Catch ex As Exception
          Throw ex
        End Try
        Return aDataTable
      End Function
    

    GeeMann
    Thursday, April 21, 2011 3:42 PM

Answers

  • LINQ might be a good way to handle this (if you're familiar with LINQ ... if not, it might get complicated). LINQ is typically faster though. I'm not a LINQ expert at all, but if I get some time later today or tomorrow, I could see if I can figure out a LINQ way of doing this.

    Getting the data initially from the first DataTable and then reading through the other DataTables is also a good idea and I think that relationships or just filtered DataViews of the other DataTables might help (I didn't go through your code that carefully to see how you're determining the Officer in all the tables, but there must be some common field).


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by GeeMann Wednesday, April 27, 2011 3:35 PM
    Saturday, April 23, 2011 4:40 PM
  • You may consider creating a dataset in the Visual Studio instead of creating the table (aDataTable) in code.

    Also, since your item(2) is a key, you may add a table in your dataset (as per above) to hold key / value pairs having two columns ‘Key’ and ‘Value’ (or ‘Id’ and ‘Description’) and pre-populate that table with all key / value pairs. (For example {Key = 1, Value = ‘Admin Reviews’}, {Key = 2, Value = ‘Compliance Monitoring’})

    Then, you may not need to loop through each row of the table you get from web service. Instead, one statement can then populate your destination table.

    And I do not understand why you are adding a total row at the end? You can always get totals from your table with a single command wherever it is needed.

    kr

    • Marked as answer by GeeMann Wednesday, April 27, 2011 3:34 PM
    Monday, April 25, 2011 3:33 PM
  • You are making decision based on item(0) and item(2) and supplying a string for each value found in these two columns. You then populate corresponding column in your destination table.

    I am proposing that you create two tables in visual studio designer

    1.       Destination table

    2.       Key table (as per my last post)

    Then you can JOIN source table and Key table and use CASE statement in SQL SELECT query to get correct value from your Key table and insert it into the destination table, in one step.

    You have not shown the code for your function FindAName. I am guessing that this function provides the name of the employee based on key in item(0). If this key and its value is coming from a table, you can add that table as well in your dataset and populate it with data from your database. Then, you may be able to deal with both at the same time.

    kr

     


    • Marked as answer by GeeMann Wednesday, April 27, 2011 3:34 PM
    Monday, April 25, 2011 4:26 PM

All replies

  • GeeMann,

    i don't understand what your question is. Is your problem that your record consists of keys, and you'd like some more related fields in them?

    Then you'd want to join them.

    What you're doing here in code, is soooo much easier (and shorter) if you can do it on the database side with SQL, but it's not clear if that is a possibility for you.

    regards,

    Nico

     

    Friday, April 22, 2011 7:03 AM
  • Thanks for your answer.

    Yes I agree that using SQL server to do this would be easier but I am working with a web service and it provides me with a dataset of multiple tabes. My goal then is to collect data from each table and create a dataTable which is then bound to a gridview.

    Should look like this:

    Officer                Reviews           compliance             overdrafts                   Losses        Total

     123                        1                      4                         1                                 3               9

      367                        9                      2                        2                                 2              15

     Totals                    10                       6                        3                                 5              24

     

    Now that I have given by code some more thought I think that I need to 1. Initially populate the datatable from the first table in my dataset. 2. Then read through the ramaining tables and update the datatable with their data.

    I don't think that relating the tables will help me here.

    Maybe coding the datatable updating might be easier using LINQ ?

     

     


    GeeMann
    Friday, April 22, 2011 3:35 PM
  • LINQ might be a good way to handle this (if you're familiar with LINQ ... if not, it might get complicated). LINQ is typically faster though. I'm not a LINQ expert at all, but if I get some time later today or tomorrow, I could see if I can figure out a LINQ way of doing this.

    Getting the data initially from the first DataTable and then reading through the other DataTables is also a good idea and I think that relationships or just filtered DataViews of the other DataTables might help (I didn't go through your code that carefully to see how you're determining the Officer in all the tables, but there must be some common field).


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by GeeMann Wednesday, April 27, 2011 3:35 PM
    Saturday, April 23, 2011 4:40 PM
  • You may consider creating a dataset in the Visual Studio instead of creating the table (aDataTable) in code.

    Also, since your item(2) is a key, you may add a table in your dataset (as per above) to hold key / value pairs having two columns ‘Key’ and ‘Value’ (or ‘Id’ and ‘Description’) and pre-populate that table with all key / value pairs. (For example {Key = 1, Value = ‘Admin Reviews’}, {Key = 2, Value = ‘Compliance Monitoring’})

    Then, you may not need to loop through each row of the table you get from web service. Instead, one statement can then populate your destination table.

    And I do not understand why you are adding a total row at the end? You can always get totals from your table with a single command wherever it is needed.

    kr

    • Marked as answer by GeeMann Wednesday, April 27, 2011 3:34 PM
    Monday, April 25, 2011 3:33 PM
  • Thanks Bonnie !
    GeeMann
    Monday, April 25, 2011 3:38 PM
  • Thanks Khalique !

    I will think about youe answer.

    The reason I am using a datatable is that I am trying to consolidate the data from several dataset tables. These tables have a different structure than the dataTable. The datatable layout will be merged with a gridview. Are you suggesting that I create a linking table ?

    Let me think about your response for a while.


    GeeMann
    Monday, April 25, 2011 3:43 PM
  • Code that you included shows that the schema of the table provided by web service is the same (or at least you are not dealing with any such differences). You are making decision based on item(0) and item(2) and supplying a string for each value found in these two columns. I do not see that you are dealing with different structures in your code. May be I missed something.

    kr
    Monday, April 25, 2011 4:10 PM
  • You are making decision based on item(0) and item(2) and supplying a string for each value found in these two columns. You then populate corresponding column in your destination table.

    I am proposing that you create two tables in visual studio designer

    1.       Destination table

    2.       Key table (as per my last post)

    Then you can JOIN source table and Key table and use CASE statement in SQL SELECT query to get correct value from your Key table and insert it into the destination table, in one step.

    You have not shown the code for your function FindAName. I am guessing that this function provides the name of the employee based on key in item(0). If this key and its value is coming from a table, you can add that table as well in your dataset and populate it with data from your database. Then, you may be able to deal with both at the same time.

    kr

     


    • Marked as answer by GeeMann Wednesday, April 27, 2011 3:34 PM
    Monday, April 25, 2011 4:26 PM
  • GeeMan ... sorry that I never got some time to try and work up a LINQ example for you. But, I'm curious ... you marked both my reply and Khalique's reply as answers and yet they are very different ... so I wonder what you actually ended up doing to solve your problem?

    It might be beneficial to other people reading this thread at some other time if you could post what you ended up with ...


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, April 27, 2011 4:25 PM
  • Sorry this project has been superseded by another  When I resume thiw one I will report on which soluton worked.

    Thanks


    GeeMann
    Monday, May 2, 2011 7:34 PM