Upsert with .net sqladapter

Question

   Dim sSelect As String = _
        "SELECT * FROM Contacts" & _
        " WHERE DataSetID = @DataSetID AND ID >= @FirstID AND ID <= @LastID ORDER BY ID"


        Dim dsDBFiles As New DataSet()
        Dim cmd As New SqlClient.SqlCommand(sSelect, m_connection)
        cmd.Parameters.Add("@FirstID", SqlDbType.Int).Value = nFirstID
        cmd.Parameters.Add("@LastID", SqlDbType.Int).Value = nLastID

        Dim daTable As New SqlClient.SqlDataAdapter(cmd)
        Dim bldr As New SqlClient.SqlCommandBuilder(daTable)

        daTable.Fill(dsDBFiles, sTable)
        Dim tbl As DataTable = dsDBFiles.Tables(sTable)

        Dim rdr As New Data.DataTableReader(dsFiles.Tables(0))
        dsDBFiles.Load(rdr, LoadOption.Upsert, tbl)

        daTable.Update(dsDBFiles, sTable)

Is there way to achieve this upsert functionality without retrieving the records ? I am using SQL Server 2005. I heard there is a way to use the sqladapter to do this, without running the select statement.

I am trying to speed up this process. Any suggestions?

Cheers.

Answer 1

The INSERT part is one thing - inserting new rows is not a problem.

If you want to update existing rows, you'll need to do the following things:

• add all the rows you want to update to your DataSet (this adds them with a RowState = Added, so they would be handled by the INSERT statement)
• call the .SetModified() on those rows to set their RowState to modified. Now, the UPDATE statement will pick those up and apply them to the database

Of course, you'll also need to set the UpdateCommand on your SqlDataAdapter, and you'll need to make sure the SQL UPDATE statement works in such a way that it only compares e.g. the primary key to match up rows to be updated.

With this, you should be able to add modified rows to your DataSet and update them without ever retrieving them in the first place.

Marc