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

Answer 1

IMO you should avoid using on delete cascade because:

1. You lose control what is being removed
2. Table references has to be altered to enable it
3. 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).