BrINClHOF BrINClHOF - 5 months ago 29
Vb.net Question

Custom TableAdapter Delete Method Override

I'm attempting to overload the "Delete" method of a TableAdapter (approach). How can I execute an SQL statement from 'here' to handle the delete?

I've got:

Namespace AFL_BackendDataSetTableAdapters

Partial Class Log_entry_unitTableAdapter

Public Overloads Function Delete(ByVal LogEntryID As Integer) As Integer

Dim SQL As String

SQL = "DELETE FROM log_entry_unit WHERE log_entry_unit_id=" & LogEntryID

'?????.Execute SQL

Return 0

End Function

End Class

End Namespace


The overload is working fine, but I don't know how to do the hard part and actually manipulate the data from here. Previously, I've just gone into the Dataset Designer and manually updated the generated methods to work like I want them, but whenever I use the wizard to regenerate the dataset, that (as expected) gets overwritten.

I've previously only ever manipulated Data using the generated methods, and now I'm stuck.

EDIT w/ Final Answer
Based on William's help below here's the final working solution (Note I just had to use OleDb instead of SQL since my Dataset is Access:

Imports System.Data.OleDb

Namespace AFL_BackendDataSetTableAdapters

Partial Class Log_entry_unitTableAdapter

Public Overloads Function Delete(ByVal LogEntryID As Integer) As Integer

Dim queryString As String = "DELETE FROM log_entry_unit WHERE log_entry_unit_id=" & LogEntryID
Dim command As New OleDbCommand(queryString, Connection)
Dim r As Integer

Try
Connection.Open()
r = command.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
r = 0
Finally
Connection.Close()
End Try

Return r

End Function

End Class

End Namespace

Answer

I hardcoded a connection string for reference only. This should be in a config file. As an example:

    Dim connectionString As String = _
        "Data Source=(local);Initial Catalog=YourDatabase;" _
        & "Integrated Security=true"

    Dim queryString As String = "DELETE FROM log_entry_unit WHERE log_entry_unit_id=" & LogEntryID

    ' Create and open the connection in a using block. This
    ' ensures that all resources will be closed and disposed
    ' when the code exits.
    Using connection As New SqlConnection(connectionString)

        ' Create the Command
        Dim command As New SqlCommand(queryString, connection)

        ' Open the connection in a try/catch block. 
        Try
            connection.Open()
            command.ExecuteNonQuery()
        Catch ex As Exception
            ' handle exception here
        End Try
    End Using

EDIT

I probably should of mentioned you will probably want to fill your adapter again after the delete.