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
go
create proc uspDeleteObject(
@ObjectName varchar(40) ,
@Where varchar(200) ,
@ErrorPart varchar(100) = null
) as
exec('declare @Records int declare @errorMSG varchar(200)
select @Records = count(*) from ' + @ObjectName + ' where ' + @Where + '
if @Records <= 0
begin
set @errorMSG = ''The record ' + @ErrorPart + ', you are trying to delete does not exist in the registered ' + @ObjectName + '.''
raiserror(@errorMSG,16, 1)
return
end
delete from ' + @ObjectName + ' where ' + @Where + '')
go
-- exec uspDeleteObject 'Logins', 'LoginID = ''Admin''', 'Login ID: Admin'
You can now call such a stored procedure from your VB code as follows
''' <summary>
''' Use uspDeleteObject stored procedure to delete data
''' from different tables. This deletes from Clients table
''' whose primary key is ClientID
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
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
With comm.Parameters
.AddWithValue("@ObjectName", "Clients")
.AddWithValue("@Where", where)
.AddWithValue("@ErrorPart", errorPart)
End With
Try
conn.Open()
Return comm.ExecuteNonQuery() > 0
Catch eX As SqlException
Throw eX
Catch eX As Exception
Throw eX
Finally
conn.Close()
End Try
End Using
End Using
End Function
''' <summary>
''' This construct of connection string eliminates SQL injection
''' </summary>
''' <param name="_ConString"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function ConString() As String
Dim conBuilder As New SqlConnectionStringBuilder()
Dim serverName As String = "(local)"
Dim databaseName As String = "Accounting"
conBuilder.Clear()
conBuilder("Server") = serverName
conBuilder.InitialCatalog = databaseName
conBuilder("Integrated Security") = "SSPI"
conBuilder.PersistSecurityInfo = False
Return conBuilder.ConnectionString
End Function
Note:
You can modify the delete function such that the errorPart, where clause and tableName are supplied as its parameters.