How to Delete Data from Two tables in single query

I have two tables: Accounts, which contains the information of accounts that a user has, and AccountUsers, which contains the list of users that have accounts in aforementi开发者_如何学编程oned Accounts table.

If I have to delete a user then I would have to delete them from both tables where as AccountUsers will have one row of that user where as other may have many because a single user can have many accounts.

I wrote the following code that works well and deletes the row from both tables, but whenever there are multiple rows in the second table it fails to delete them and ExecuteNonQuery() returns 0, which means 0 rows are affected.

I would like to know if it can be done in a single line. Also, how can it be done for multiple rows in the second table, as my existing code only works if there is single row.

Code:

public static bool DeleteUser(int UserId)
        {
            if (ConnectDatabase())
            {
                sql_cmd = new SqlCommand();
                sql_cmd.Connection = sql_con;
                sql_cmd.Parameters.Add("@userId", SqlDbType.Int).Value = UserId;
                sql_cmd.CommandText = "DELETE FROM AccountsUsers WHERE Id = @userId";

                if (sql_cmd.ExecuteNonQuery() == 1)
                {
                    sql_cmd = new SqlCommand();
                    sql_cmd.Connection = sql_con;
                    sql_cmd.Parameters.Add("@userId", SqlDbType.Int).Value = UserId;
                    sql_cmd.CommandText = "DELETE FROM Accounts WHERE userId = @userId";
                    if (sql_cmd.ExecuteNonQuery() == 1)
                    {
                        return true;
                    }
                    else
                        return false;

                }
                else
                    return false;
            }
            else
                return false;
        } 

There is bit of a logic flaw in you code. It there is one record in the second table, ExecuteNonQuery will return 1, but it there are more, it will not return one, but some other number. I would change the conditional after the second delete to:

if (sql_cmd.ExecuteNonQuery() > 1)

Which raises the question - if there are no records in the second table, do you want your function to return false?

You probably need to delete the Accounts first, but it's not clear exactly which direction your foreign key is going in.

You cannot delete from two tables in a single query generally, but you can make a transaction and have two DELETE statements within the transaction.

You can also use a cascading delete constraint so that you only delete from the parent table (but I rarely recommend that).

I may be being too simplistic but you can probably just do the following - There is nothing wrong with including multiple sql statements in a single command separated by a semicolon:

    static bool DeleteUser(int UserId)
    {
        if (ConnectDatabase())
        {
            sql_cmd = new SqlCommand();
            sql_cmd.Connection = sql_con;
            sql_cmd.Parameters.Add("@userId", SqlDbType.Int).Value = UserId;
            sql_cmd.CommandText = "DELETE FROM Accounts WHERE userId = @userId;DELETE FROM AccountsUsers WHERE Id = @userId;";

            if (sql_cmd.ExecuteNonQuery() == 1)
            {
                return true;
            }
            else
                return false;               
    } 

It is definitely best practice to delete from the Accounts table first before the AccountsUsers because usually the Accounts table would have a foreign constraint on AccountsUsers table. This means no rows could be deleted in AccountsUsers before corresponding row in Accounts deleted first.