Adding Columns To a DataTable and Retrieving Data From Another Table

I had an instance where I needed a couple of columns from a table that was not efficiently retrieved using a Join with MYSQL, because the Key field was not a key in the secondary file, necessitating a sequential read through the file for each of the primary records. So, I decided to read the inefficient table into a datatable, and then as I processed my main datatable, to find the appropriate rows and stuff its data into the primary table. This program needed to output a CSV file, so it made more sense to add the columns to the primary table than to add additional logic to output the extraneous fields.
Also, there was a field used only as a link between the tables and it should not be emitted on the output operations, so logic is added to bypass this field while creating the CSV column and data lines.
Only the last row added from the inefficient table was to be selected for the CSV file, so we checked to see if any records were selected and then took the first record.
By using a SQL join between the tables, it took nearly 20 minutes to run. Setting up and using the below method resulted in completion in just a few seconds. Quite the improvement.



Dim dcComment, dcCommentDate As DataColumn
dcComment = New DataColumn("Comment", GetType(String))
dcCommentDate = New DataColumn("CommentDate", GetType(Date))

' add the comment fields to the PO Data Table
dtPO.Columns.Add(dcComment)
dtPO.Columns.Add(dcCommentDate)

Dim CSVsb As New StringBuilder(200)

Dim fieldcount As Integer = dtPO.Columns.Count
Dim CertOrdinal As Integer
CertOrdinal = dtPO.Columns("Certificate").Ordinal

For i = 0 To fieldcount - 1
If i = CertOrdinal Then
Continue For
End If

If CSVsb.Length > 0 Then
CSVsb.Append(",")
End If

CSVsb.Append(Chr(34) & dtPO.Columns(i).ColumnName & Chr(34))
Next

mobjFileInfo = New FileInfo(strPath)
'mobjFileInfo.CreateText()
Dim fileWriteStream As StreamWriter = mobjFileInfo.AppendText()

Dim sSort As String
sSort = "CommentDate Desc"
fileWriteStream.WriteLine(CSVsb.ToString)

For Each myDataRow As DataRow In dtPO.Rows
CSVsb.Length = 0

'Retrieve Comments for this PO
Dim sCert As String
sCert = myDataRow("Certificate")
Dim sSelect As String
sSelect = String.Format("Certificate = {0}", sCert)
Dim foundRows As DataRow() = dtComments.Select(sSelect, sSort)

If foundRows.Length > 0 Then
Dim myCommentRow As DataRow
myCommentRow = foundRows(0)
myDataRow("Comment") = myCommentRow("Comment")
myDataRow("CommentDate") = myCommentRow("CommentDate")
End If

For i = 0 To fieldcount - 1
'do not output the Certificate, only used to retrieve Comments
If i = CertOrdinal Then
Continue For
End If

If CSVsb.Length > 0 Then
CSVsb.Append(",")
End If

CSVsb.Append(Chr(34))

If Not (IsDBNull(myDataRow.Item(i))) Then
Dim sValue As String
sValue = myDataRow.Item(i)
CSVsb.Append(sValue.Replace(Chr(34), ""))
End If

CSVsb.Append(Chr(34))

Next

fileWriteStream.WriteLine(CSVsb.ToString)

Next

Retrieving Last Inserted ID From MYSQL from C#

After inserting a record into the database, to retrieve the ID created on the insert, use the following code:

OdbcCommand1.CommandText="SELECT LAST_INSERT_ID()";

Int64 LastID;
LastID = Convert.ToInt64(OdbcCommand1.ExecuteScalar());