Create one delete stored procedure that can be used to delete data from different tables
Stored procedures offer powerful security control over the database and importantly, provide an abstraction layer between the physical structure of the database and the logical way in which it’s used.
Thus, whenever possible, database access should be performed through the use of stored procedures. If this is the case, you’ll quickly realize that delete stored procedure is identical for most delete operations with an exception of table name to delete from and the where clause.
Below is an example of a delete stored procedure that you can use to delete data from different tables
if exists (select * from sysobjects where name = 'uspDeleteObject')
drop proc uspDeleteObject
create proc uspDeleteObject(
@ObjectName varchar(40) ,
@Where varchar(200) ,
@ErrorPart varchar(100) = null
exec('declare @Records int declare @errorMSG varchar(200)
select @Records = count(*) from ' + @ObjectName + ' where ' + @Where + '
if @Records <= 0
set @errorMSG = ''The record ' + @ErrorPart + ', you are trying to delete does not exist in the registered ' + @ObjectName + '.''
delete from ' + @ObjectName + ' where ' + @Where + '')
-- exec uspDeleteObject 'Logins', 'LoginID = ''Admin''', 'Login ID: Admin'
You can now call such a stored procedure from your VB code as follows
''' Use uspDeleteObject stored procedure to delete data
''' from different tables. This deletes from Clients table
''' whose primary key is ClientID
Public Function Delete() As Boolean
Dim where As String = "ClientID = '" & Me.ClientID & "'"
Dim errorPart As String = "Client ID: " & Me.ClientID
' Assuming Clients table had a composite key (ClientID and Version),
' the where and errorPart would be as follows
' where = "ClientID = '" & Me.ClientID & "' and Version = '" & Me.Version & "'"
' errorPart = "ClientID: " & Me.ClientID & " and Version: " & Me.Version
Using conn As New SqlConnection()
conn.ConnectionString = ConString()
Using comm As SqlCommand = New SqlCommand("uspDeleteObject", conn)
comm.CommandType = CommandType.StoredProcedure
Return comm.ExecuteNonQuery() > 0
Catch eX As SqlException
Catch eX As Exception
''' This construct of connection string eliminates SQL injection
''' <param name="_ConString"></param>
Public Function ConString() As String
Dim conBuilder As New SqlConnectionStringBuilder()
Dim serverName As String = "(local)"
Dim databaseName As String = "Accounting"
conBuilder("Server") = serverName
conBuilder.InitialCatalog = databaseName
conBuilder("Integrated Security") = "SSPI"
conBuilder.PersistSecurityInfo = False
You can modify the delete function such that the errorPart, where clause and tableName are supplied as its parameters.