Cascading deletion with Ado.net

Question

I have an application which i need to delete a row from the table Client

public void Delete_Client(int _id_client)
        {
            Data.Connect();
            using (Data.connexion)
            {
                string s = "Delete From CLIENT where id_client = " + _id_client;
                SqlCommand command = new SqlCommand(s, Data.connexion);
                try
                {
                    command.ExecuteNonQuery();
                }
                catch { }
            }
        }

the table Client contains a foreign references to another table. So an exception appears indicates that the deletion must be cascade.

So how can i change my code to do this ( i'am using sql server as a dbms) ?

gzaxx · Accepted Answer · 2013-06-19T13:13:11.753

IMO you should avoid using on delete cascade because:

You lose control what is being removed
Table references has to be altered to enable it
Use parametrized query (as all around advice)

So lets change your query. I added ClientOrder as example table which holds foreign key reference to our soon to be deleted client. First of all I remove all orders linked to client, then I delete client itself. This should go like this for all the other tables that are linked with Client table.

public void Delete_Client(int _id_client)
{
    Data.Connect();

    using (Data.connexion)
    {
        string query = "delete from ClientOrder where id_client = @clientId; delete From CLIENT where id_client = @clientid";

        SqlCommand command = new SqlCommand(query, Data.connexion);
        command.Parameters.AddWithValue("@clientId", _id_client);

        try
        {
            command.ExecuteNonQuery();
        }
        catch { } //silencing errors is wrong, if something goes wrong you should handle it 
    }
}

Parametrized query has many advantages. First of all it is safer (look at SQL Injection attack). Second types are resolved by framework (especially helpful for DateTime with formatting).

Cascading deletion with Ado.net

1 Answers1