CODEWITHSUNDEEP
c#sqlasp.net
tekar
tekar

Reputation: 93

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

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)
);

Upvotes: 1

Views: 848

Answers (4)

Patrick Hofman
Patrick Hofman

Reputation: 156928

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.

Upvotes: 3

Christian Phillips
Christian Phillips

Reputation: 18749

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;

Upvotes: 1

Dgan
Dgan

Reputation: 10285

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

Upvotes: 0

Subha
Subha

Reputation: 1051

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.

Upvotes: 1

Related Questions