Delete file when deleting row from table

Question

On my website I use simple file management. User can upload files, see the list and delete them. In database I have one table Files which contain information about files (file name, description, insert date).

I display all the files in GridView control with SQLDataSource.

DeleteCommand="DELETE FROM Files WHERE id = @id"

What I want to do is to delete the asocciated file when user deletes row from table. I was trying to do this in OnDeleting event but it seams that I have to execute another SELECT to get file name. Is it the only way to do this? Or is it any other better way? Or how to get the file name from inside the OnDeleting event?

EDITED: Databse is SQL Server, but it is not important in this case. I store files in file system. In database there are only names of files.

Answer 1

You need some way to cross reference the table entry with the file - presumably they both have a unique file name? If so, you could use that to delete the file from the same place in your code-behind that you are issuing the DB delete command.

Answer 2

If you try this from the GridView.RowDeleting event, you can use the passed-in parameter GridViewDeleteEventArgs to get the row about to be deleted. Assuming the name is in this row, you can then use it to delete the file.

Answer 3

Lukasz,

If you are using a Gridview to bind the data, you can define the DataKeyNames attribute and then in the RowDeleting event do the following:

string documentName = (string)GridName.DataKeys[e.RowIndex].Value;
DeleteDoc(documentName);

Answer 4

I know they can be evil, but this seems like a place where triggers could be used, that way if the delete query is run from multiple places the file would still be deleted.

if it is MS SQL

DECLARE @Command varchar(8000)
SELECT @Command = 'del c:\' + DocID FROM Deleted
xp_cmdshell @Command

Answer 5

Rather than deleting your row directly, create a stored procedure called something like deleteFile(@ID int). Inside this proc, get the filename:

Select FileName From Files Where ID = @ID

Then Delete the file suing whatever method you're using to delete actual files.

Then delete the row

Delete Files Where ID = @ID