Most efficent way of adding rows to a datatable

[Nitesh]

hi guys,

I'm populating a datatable with this code:

Using con As New MySqlConnection(strCon)
                'get users direct friends
                Dim cmd As MySqlCommand = New MySqlCommand("SELECT distinct f.* FROM friends f WHERE (f.UserID = ?UserID) AND (f.FriendUserID <> ?FriendUserID)", con)

                cmd.Parameters.AddWithValue("?UserID", UserID)
                cmd.Parameters.AddWithValue("?FriendUserID", UserID)

                con.Open()

                Dim reader As MySqlDataReader = cmd.ExecuteReader

                _data.Load(reader)

                con.Close()
                reader.Close()
                cmd.Dispose()

end using

now I need to create another command, datatable and datareader in this same function and add the rows of that datatable to the one above. What is the most efficient way of doing this?

[jmcilhinney]

Do you really need another DataTable? You should probably just be able to use the same DataTable on both occasions. I've never tried but I would imagine that call Load on a DataTable twice would simply append the data the second time. That's how the Fill method of a DataAdapter works. It should even work if the two result set schema are different.

[Nitesh]

thanks JM. will try it out

[Shaggy Hiker]

By the way, since you asked about the most efficient way of doing this, I will point out that the most efficient way involves not using f.*. There is a performance penalty to using * to designate fields. If you really need all the fields, then you will still get slightly better performance by writing them out, tedious though that could be. If you don't need all the fields, then you would be much better off only getting the ones you needed.