SQLDataAdapter filling datatable with primary key produces error and exits sub

Question

Ok so this is going to take some explaining.

The process I am trying to do is grab data from a table function in SQL and then fill a dataset with the returned values. I then have to run this query twice more to query an alternative number table. Then add to the same table as the previous queries. This needs to be as fast as possible, so I am currently using an adapter.fill to populate the datasets and then a dataset.merge to put them all into one table.

The problem is the query can return duplicates which waste time and space, because of this I made column 3(part_ID) the primary key to stop duplicates.

When this is run with the .merge it quits at the first instance of a duplication and doesn't continue with the population.

The code below is what I used to fix this, I was just wondering if there is a better more elegant solution.

        com = New SqlCommand(sqlPN, myConnect)
        adapter.SelectCommand = com

        adapter.Fill(temp, "Table(0)")
        Dim data As New DataSet
        data = temp
        temp.Tables(0).Columns(3).Unique = True

        firstSet = temp.Tables(0).Rows.Count
        temp.AcceptChanges()
        If temp.Tables(0).Rows.Count < maxRecords Then
            Dim sqlAlt As String = "select Top " & (maxRecords + 10 - temp.Tables(0).Rows.Count) & " * from getAltEnquiry('" & tbSearchFor.Text & "') ORDER BY spn_partnumber"
            adapter.SelectCommand.CommandText = sqlAlt
            adapter.FillLoadOption = LoadOption.OverwriteChanges
            adapter.Fill(temp, "Table(1)")
            For i = 0 To temp.Tables(1).Rows.Count - 1
                Try
                    temp.Tables(0).ImportRow(temp.Tables(1).Rows(i))
                Catch e As Exception
                End Try
            Next
        End If
        If temp.Tables(0).Rows.Count < maxRecords Then
            Dim sqlSuPN As String = "select Top " & (maxRecords + 5 - temp.Tables(0).Rows.Count) & " * from getSuPNEnquiry('" & tbSearchFor.Text & "') ORDER BY spn_partnumber"
            adapter.SelectCommand.CommandText = sqlSuPN
            adapter.Fill(temp, "Table(2)")
            For i = 0 To temp.Tables(2).Rows.Count - 1
                Try
                    temp.Tables(0).ImportRow(temp.Tables(2).Rows(i))
                Catch e As Exception
                End Try
            Next
        End If</code>

Thanks for any help or advice ^__^

score 2 · Accepted Answer · answered Aug 14 '11 at 11:33

Since you are looping through the records from the additional queries and using the ImportRow, your code will throw an exception if more than one record with the same value in the primary key field is attempted to be inserted. That is the purpose of a primary key when using in this way. If you want to ensure that your table only has unique records, you will need to ensure that the records are distinct before inserting them by checking the new row's part_id value against those already in the table. However, your design isn't necessarily the ideal approach.

Since you mentioned that this needs to be fast, it will probably be best if you could write a stored procedure to return just the rows you need from all tables and do the Fill into the table once.

If that's not possible, you can call adapter.Fill on the same DataTable for each of your data sources. Use the Fill overload that takes just the DataTable to fill and as per the docs, it will merge the data together if more than one record with the same primary key exists. The way you have the Fill method called, it is creating a new DataTable with the name you provide for each time you call Fill. Instead, you want to Fill just one DataTable.

"You can use the Fill method multiple times on the same DataTable. If a primary key exists, incoming rows are merged with matching rows that already exist. If no primary key exists, incoming rows are appended to the DataTable."

oh wow this sounds brilliant I will try it now and get back to you on it, I originally did think I could write a sorted method but the first and second query needs to reference different table. — Malcolm Baker, Aug 15 '11 at 08:37
ok this helped alot, I renamed the temp dataset to a datatable then declared column 3 as a primary key. Just as you said it is now ammending the table ^__^ — Malcolm Baker, Aug 15 '11 at 09:08

SQLDataAdapter filling datatable with primary key produces error and exits sub

1 Answers1