Delete SQL records based on Datatable

[drawlings]

Afternoon all,

I load a number of records into a datatable. I am trying to find out if there is a simple way to remove all the records in my sql table that are in my datatable without having to loop through the datatable and remove 1 record at a time.

Basically, what happens is.. Data is loaded into a datatable and then extracted to a csv file. I therefore no longer require the data in the SQL table that is related to the datatable.

  Public Function LoadDatatable(_iSQL As String) As DataTable
        Try
            Dim _Datatable As New DataTable

            Using dCon As _SQLConnection = New _SQLConnection(Configuration.ConnectionString)
                Using dbCmd As New SqlClient.SqlCommand(_iSQL, dCon.Connection)
                    Using da = New SqlClient.SqlDataAdapter(dbCmd)
                        da.Fill(_Datatable)
                    End Using
                End Using
            End Using

            Return _Datatable
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Load Datatable", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return Nothing
        End Try
    End Function

I then do some data manipulation so that the data can be extracted into the correct formats etc....

I could just use a Delete from tableX, but there are likely to be new records created since I loaded the orignal data into the datatable and I don't want to loose these records.

So is it possible to delete the records from my SQL table that match my datatable. I have a unique field called 'id'

Cheers

Dan

[billboy]

How about a DELETE FROM WHERE statement ?

DELETE FROM table_name
WHERE some_column=some_value

would this work for you?

[drawlings]

How about a DELETE FROM WHERE statement ?

DELETE FROM table_name
WHERE some_column=some_value

would this work for you?

Thanks for the reply, but the datatable can have up to 1000 records on each extract so that would not work.

[dunfiddlin]

There seems to be a contradiction here. You start by saying you want to remove all the records, then claim to want to keep new records, and speak continually about deleting records from the datatable but don't mention what if anything you want to do with those records in the database. If you simply want to clear a datatable then either refill it with a new set of records or dispose of it. There is no need to do any specific deleting. If you want to do something a bit more complex then please explain exactly what it is.

[drawlings]

There seems to be a contradiction here. You start by saying you want to remove all the records, then claim to want to keep new records, and speak continually about deleting records from the datatable but don't mention what if anything you want to do with those records in the database. If you simply want to clear a datatable then either refill it with a new set of records or dispose of it. There is no need to do any specific deleting. If you want to do something a bit more complex then please explain exactly what it is.

Sorry if it wasn't clear..

1) I load a number of records into a datatable that need to be extracted...
2) The records loaded in the datatable need to be removed from the SQL table. But in the meantime, new records may have been added to the SQL table (from a external source) that are not loaded into the datatable.
3) Delete all the SQL records that are loaded into the datatable.

SQL Table
Column1, Column2, Column3
Record1,Record1,Record1
Record2,Record2,Record2
Record3,Record3,Record3

Data Table
Column1, Column2, Column3
Record1,Record1,Record1
Record2,Record2,Record2

Only delete Records 1 & 2 from the SQL table.

Is that a little clearer?

[dunfiddlin]

Well that sounds more like a database management problem than a coding one. Using a database merely as a buffer in this way is always going to leave you with synchronisation problems on a grand scale. Either you need to lock it down while you complete the task so that no new records can be added until it's completed or you need to activate some kind of transfer process within the database when and only when you execute this procedure so that the records you're extracting are first moved to a buffer table in the database so that new records will not interfere.

Frankly though if the only purpose of this whole set up is to create csv files and the dtatabase is essentially irrelevant I'm at a bit of a loss as to why you don't just do that directly.