Delete from multiple tables ASP.NET

Question

How to delete from two tables at once using the same delete statement in ASP.Net?

Answer 1

If your database is MsSql you can combine several delete statements in one SqlCommand.

Try this:

string deleteQuery = "DELETE FROM Table1; DELETE FROM Table2;";
using (SqlConnection connection = new SqlConnection(connectionstring))
using (SqlCommand deleteCommand = new SqlCommand(deleteQuery, connection))
{
    connection.Open();
    deleteCommand.ExecuteNonQuery();
}

This will delete all records from both table1 and table2 in one pass.

Answer 2

I'm not sure what you mean, but if you want to delete from a table using another tables' data, this can be done using the following snippet (SQL Server):

DELETE x
FROM TableX x INNER JOIN TableY y
   ON x.SomeID = y.SomeID
WHERE ...

If you want to execute two DELETE statements in one command, you could either just concatenate them separating them with a semicolon:

DELETE TableX WHERE SomeID = ...; DELETE TableY WHERE SomeID = ...

You can also use a stored procedure:

CREATE PROCEDURE DeleteTwoTables
   @ParamX int,
   @ParamY int
AS
   DELETE TableX WHERE SomeID = @ParamX
   DELETE TableY WHERE SomeID = @ParamY

Answer 3

Just concatenate delete sql scripts :

Using connection As New SqlConnection(connectionString)
    Dim command As New 
       SqlCommand("DELETE FROM Table1 WHERE ID1=@ID1; DELETE FROM Table2 WHERE ID2=@ID2;", connection)

    command.Parameters.Add("@ID1", 1);
    command.Parameters.Add("@ID2", 2);

    command.Connection.Open()
    command.ExecuteNonQuery()
End Using