Should I even be Using Merge for this?

  • Thread starter Thread starter http://www.visual-basic-data-mining.net/forum
  • Start date Start date
H

http://www.visual-basic-data-mining.net/forum

OK, here's my problem and my (admittedly inelegant) solution, does anyone
know a better way to do this?

I have two separate tables (dtCC and dtACH) representing credit card and
check sales
both have a field (contactmasterkeyid) that shows the related primary key
value of a table which holds all customers

What I did is pull from both tables all unique contactmasterkeyid values
into an integer array, then loop through that array and for each value in
that array build a datarow array and copy each row from both tables with a
contactmasterkeyid value matching the current member of the ineger array
into the datarow array. The point of all of this is to convert those datarow
arrays into datatables, because I need to pass them to a method that only
takes a datatable as an argument, I used dataset.Merge(datarow(), false,
MissingSchemaAction.Add) to put the array into a table in a dataset and pass
that to the method. My problem is I don't know if this is going to work like
I think it is, and to further complicate matters Some of those datarow
arrays will contain rows from both tables, which have different columns.

I should add at this point that I am *not* a professional programmer, I sort
of fell into this project and I'm a little out of my league. Anyone know of
a more elegant solution than this? (Code Below)

Public Sub SplitData()
'Splits up Data from ACH and CC tables and creates Deal Objects based on
unique contactmaster fields
Dim intTarget1 As Integer
Dim intTarget2 As Integer
Dim drDeals(10) As DataRow
Dim dtDeals(200) As DataSet
Dim intKeys(200) As Integer ' holds the unique values from contactmaster
field
Dim intIndex As Integer = 1
Dim intbox As Integer = 1 ' temporarily stores values in the loops
Dim bolFound As Boolean = False 'flags repeats of the value in intBox in the
already established array
Dim bolAch As Boolean
intTarget1 = dsStats.Tables("dtACH").Columns.IndexOf("contactmasterkeyid")
intTarget2 = dsStats.Tables("dtCC").Columns.IndexOf("contactmasterkeyid")

intKeys(0) = CType(dsStats.Tables("dtACH").Rows(0).Item(intTarget1),
Integer)

For i As Integer = 1 To dsStats.Tables("dtACH").Rows.Count - 1
For ii As Integer = 0 To intKeys.GetUpperBound(0)
If Not intKeys(ii) = 0 Then
If CType(dsStats.Tables("dtACH").Rows(i).Item(intTarget1), Integer) =
intKeys(ii) Then
bolFound = True
End If
End If
Next
If Not bolFound Then
intKeys(intIndex) = CType(dsStats.Tables("dtACH").Rows(i).Item(intTarget1),
Integer)
intIndex += 1
Else
bolFound = False
End If
Next

For i As Integer = 1 To dsStats.Tables("dtCC").Rows.Count - 1
For ii As Integer = 0 To intKeys.GetUpperBound(0)
If Not intKeys(ii) = 0 Then
If CType(dsStats.Tables("dtCC").Rows(i).Item(intTarget2), Integer) =
intKeys(ii) Then
bolFound = True
End If
End If
Next
If Not bolFound Then
intKeys(intIndex) = CType(dsStats.Tables("dtCC").Rows(i).Item(intTarget2),
Integer)
intIndex += 1
Else
bolFound = False
End If
Next

For i As Integer = 0 To intKeys.GetUpperBound(0)
dtDeals(i) = New DataSet
Next

For i As Integer = 0 To intKeys.GetUpperBound(0)
intIndex = 0
For ii As Integer = 0 To dsStats.Tables("dtACH").Rows.Count - 1
If CInt(dsStats.Tables("dtACH").Rows(ii).Item(intTarget1)) = intKeys(i) Then
drDeals(intIndex) = dsStats.Tables("dtACH").Rows(ii)
intIndex += 1
End If
Next
For ii As Integer = 0 To dsStats.Tables("dtCC").Rows.Count - 1
If CInt(dsStats.Tables("dtCC").Rows(ii).Item(intTarget1)) = intKeys(i) Then
drDeals(intIndex) = dsStats.Tables("dtCC").Rows(ii)
intIndex += 1
End If
Next
dtDeals(i).Merge(drDeals, False, MissingSchemaAction.Add)
Next

For i As Integer = 0 To dtDeals.GetUpperBound(0)
If Not dtDeals(i).Tables(0).Rows.Count = 0 Then
Deals(i) = New Deal(dtDeals(i).Tables(0))
dtDeals(i).Dispose()
End If
Next
End Sub
 
You could overload the method so it takes your arrays, that way you'd know
that you're good to do. Otherwise I think you're going to have to create a
new datatable with allof the columns you want..loop through the arrays and
add the rows accordingly. You can use the ItemArray property to make this a
ilttle bit easier
http://msdn.microsoft.com/library/d...frlrfsystemdatadatarowclassitemarraytopic.asp

I'm a bit pressed for time at the moment but when I have a second I'll be
glad to take a look at this a lot closer and see if I can write it out for
you. What does the called method look like?
 
Back
Top