computer guy
Member
- Joined
- Feb 12, 2008
- Messages
- 18
- Programming Experience
- Beginner
Hello everyone,
I have written a piece of code to compare two DataTables and generate an SQL to update a MYSQL table to reflect the changes.
i.e. If there is a row in DataTable1 and not in DataTable2 it gets added to MYSQL Table.
But if there is a row in DataTable2 and not in DataTable1 it gets removed from MYSQL Table.
This code takes about 6 minutes to execute on a large DataTable and I was wondering if anyone can see an obvious way to improve on the code.
Thank you in advance.
I have written a piece of code to compare two DataTables and generate an SQL to update a MYSQL table to reflect the changes.
i.e. If there is a row in DataTable1 and not in DataTable2 it gets added to MYSQL Table.
But if there is a row in DataTable2 and not in DataTable1 it gets removed from MYSQL Table.
This code takes about 6 minutes to execute on a large DataTable and I was wondering if anyone can see an obvious way to improve on the code.
VB.NET:
Function compare_data()
Dim MYSQL_String As String = ""
Dim song As Data.DataRow
Dim i As Integer = 0
SongCount = 0
For Each song In SamData.Rows
Dim result() As DataRow
result = WebData.Select("title = '" + song.Item("title").ToString.Replace("'", "''") + "' AND album = '" + song.Item("album").ToString.Replace("'", "''") + "' AND artist = '" + song.Item("artist").ToString.Replace("'", "''") + "'")
If result.Count = 0 Then
If i = 0 Then
MYSQL_String += "INSERT INTO songs (`title`, `album`, `artist`, `dj_id`) VALUES (`" & song.Item("title") & "`,`" & song.Item("album") & "`,`" & song.Item("artist") & "`,`" & DJ_ID & "`)"
Else
MYSQL_String &= ",(`" & song.Item("title") & "`,`" & song.Item("album") & "`,`" & song.Item("artist") & "`,`" & DJ_ID & "`)"
End If
i += 1
SongCount += 1
End If
Next song
Dim title As New ArrayList
Dim album As New ArrayList
Dim artist As New ArrayList
i = 0
MYSQL_String += ";"
For Each song In WebData.Rows
Dim result() As DataRow
result = SamData.Select("title = '" + song.Item("title").ToString.Replace("'", "''") + "' AND album = '" + song.Item("album").ToString.Replace("'", "''") + "' AND artist = '" + song.Item("artist").ToString.Replace("'", "''") + "'")
If result.Count = 0 Then
title.add(song.Item("title"))
album.add(song.Item("album"))
artist.add(song.Item("artist"))
i += 1
End If
Next song
Dim titleString As String = ""
Dim albumString As String = ""
Dim artistString As String = ""
For t As Integer = 0 To i - 1
If t = 0 Then
titleString += "`" + title(t) + "`"
albumString += "`" + album(t) + "`"
artistString += "`" + artist(t) + "`"
Else
titleString += ",`" + title(t) + "`"
albumString += ",`" + album(t) + "`"
artistString += ",`" + artist(t) + "`"
End If
Next
If titleString.Length > 0 Then
MYSQL_String &= "DELETE FROM songs WHERE `title` IN (" & titleString & ") AND `artist` IN (" & artistString & ") AND `album` IN (" & artistString & ") AND `dj_id` = " & DJ_ID & ";"
End If
Return MYSQL_String
End Function
Thank you in advance.