How to delete a record along with all related records in ASP.NET (C#)

Question

I have this code to delete a photographer from my table, however, photographer_id is a foreign key from my table 'images', and when I delete a photographer I want to delete all the images in the 'images' table by the photographer I am deleting. How do I do that?

...

else if (e.CommandName == "Slet")
{
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString =
        ConfigurationManager.ConnectionStrings["DatabaseConnectionString1"].ToString();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;

    cmd.CommandText = "DELETE FROM photographers WHERE photographer_id = @photographer_id";

    cmd.Parameters.Add("@photographer_id", SqlDbType.Int).Value = e.CommandArgument.ToString();

    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();

    Repeater1.DataBind();
}

this is my IMAGES table :

CREATE TABLE [dbo].[images] (
    [image_id] INT  IDENTITY (1, 1) NOT NULL,
    [image] NVARCHAR (50) NOT NULL,
    [FK_photographer] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([billede_id] ASC),
    CONSTRAINT [FK_fotograf] FOREIGN KEY ([FK_fotograf]) REFERENCES [dbo].[Fotografer] ([fotograf_id]),

);

and this is my PHOTOGRAPHERS table :

CREATE TABLE [dbo].[photographers] (
    [photographer_id] INT IDENTITY (1, 1) NOT NULL,
    [photographer_name] NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([photographer_id] ASC)
);

You could add a Cascade Delete or check the Photo table first and delete the photos first. — Christian Phillips, Oct 07 '14 at 11:24
Note that you can (and should) improve your code by [using "using"](http://www.hanselman.com/blog/WhyTheUsingStatementIsBetterThanASharpStickInTheEyeAndASqlConnectionRefactoringExample.aspx). — BCdotWEB, Oct 07 '14 at 11:29

score 3 · Answer 1 · edited May 23 '17 at 11:52

3

Set cascading delete on your foreign key constraint. This will automatically delete the Images when you delete aPhotographer`.

There is no need to reinvent the wheel by doing this yourself.

edited May 23 '17 at 11:52

Community

1
1

answered Oct 07 '14 at 11:25

Patrick Hofman

153,850
22
249
325

hello maybe i should have specified this but im a beginner, and im not quite sure how to set " cascading delete " – tekar Oct 07 '14 at 11:32
@tekar: See this: http://stackoverflow.com/questions/4454861/how-do-i-edit-a-table-in-order-to-enable-cascade-delete-sql – Patrick Hofman Oct 07 '14 at 11:55
@tekar Use this query first to remove the FK constraint: `ALTER TABLE dbo.billeder DROP CONSTRAINT FK_fotograf`. Now use this query to add the FK constraint with Cascading delete: `ALTER TABLE dbo.billeder ADD CONSTRAINT FK_fotograf_Cascade FOREIGN KEY (FK_photographer) REFERENCES dbo.photographers(photographer_id) ON DELETE CASCADE`. – Alternatex Oct 07 '14 at 12:04

score 1 · Answer 2 · answered Oct 07 '14 at 11:27

Same approach -

DELETE FROM images WHERE photographer_id = @photographer_id

It is recommended to delete images first and then delete the photographer. If your tables are having physical FKs then it won't allow you to delete photographer before you delete all the dependancies.

Hope this helps.

Christian Phillips · Accepted Answer · 2014-10-07T11:45:35.650

1

As I mentioned in the comment, you can use Cascading Delete. You can alter your table similar to below.

ALTER TABLE billeder
ADD CONSTRAINT fk_photographer
FOREIGN KEY (photographer_id)
REFERENCES photographers (photographer_id)
ON DELETE CASCADE;

edited Oct 07 '14 at 11:45

answered Oct 07 '14 at 11:33

Christian Phillips

18,399
8
53
82

thank you, but where am i supposed to add this? in table difinition? and where in table difinition? ( i added 2 tables in my question). – tekar Oct 07 '14 at 12:04
omg ofc. im sorry i just couldent read it like that for some reason and my brain got boiled, but i used your method and it seems to work now :) thank you ! – tekar Oct 07 '14 at 12:25

score 0 · Answer 4 · answered Oct 07 '14 at 11:32

Better way is You can do it in Procedures But Here is Substitute and simple way

SqlCommand cmd = new SqlCommand();
                        cmd.Connection = conn;
    conn.Open();
    cmd.CommandText = "DELETE FROM photographers WHERE photographer_id = @photographer_id";


cmd.Parameters.Add("@photographer_id", SqlDbType.Int).Value = e.CommandArgument.ToString();


cmd.ExecuteNonQuery();
    //deleted from photographers 

cmd.CommandText = "DELETE FROM Images WHERE photographer_id = @photographer_id";


cmd.Parameters.Add("@photographer_id", SqlDbType.Int).Value = e.CommandArgument.ToString();
     cmd.ExecuteNonQuery();
    //deleted from images

How to delete a record along with all related records in ASP.NET (C#)

4 Answers4