'Give two sql queries in asp.net controller

This is function from my ASP.NET mvc structure and i want to give two queries in same function.My task is to delete row from 'class' table also when row is deleted from teacher table where id="teacheid". How can i right query for that in this function.

 public void DeleteTeacher(int id)
    {
        //Create an instance of a connection
        MySql.Data.MySqlClient.MySqlConnection Conn = Blog.AccessDatabase();

        //Open the connection between the web server and database
        Conn.Open();

        //Establish a new command (query) for our database
        MySqlCommand cmd = Conn.CreateCommand();

        //SQL QUERY
        cmd.CommandText = "Delete from teachers where teacherid=@id";
        cmd.Parameters.AddWithValue("@id", id);
        /*cmd.CommandText = query;*/
        cmd.Prepare();
        

        cmd.ExecuteNonQuery();

        Conn.Close();


    }

asp.net

Solution 1:^[1]

I guess you use wrong database. It should be schooldb.sql Try to check ur database again.

Solution 2:^[2]

try cross checking the database connection or database name.

Solution 3:^[3]

There must be teacherid as a foreign key in your class table, use that to delete rows from class table.

Solution 4:^[4]

This can be achieved using referential integrity. When a teacher is deleted, the referential integrity (on delete cascade) will delete the respective rows in class table with that teacherid. Also please make sure to add log table for these events as you may lose data once deleted.

Solution 5:^[5]

You could create a stored procedure that executes both actions. Otherwise you could execute your new query before closing the connection and after you executed the first query.

public void DeleteTeacher(int id)
{
    // Create an instance of a connection
    MySql.Data.MySqlClient.MySqlConnection Conn = Blog.AccessDatabase();

    // Open the connection between the web server and database
    Conn.Open();

    // Establish a new command (query) for our database
    MySqlCommand cmd = Conn.CreateCommand();

    // SQL query
    cmd.CommandText = "Delete from teachers where teacherid=@id";
    cmd.Parameters.AddWithValue("@id", id);
    cmd.Prepare();
    cmd.ExecuteNonQuery();

    // EXECUTE YOUR NEW QUERY
    Conn.Close();
}

Solution 6:^[6]

There is a simple way to set two queries in the same function, but not the best performance.

public void DeleteTeacher(int id) {
    //Create an instance of a connection
    MySql.Data.MySqlClient.MySqlConnection Conn = Blog.AccessDatabase();

    //Open the connection between the web server and database
    Conn.Open();

    //Establish a new command (query) for our database
    MySqlCommand cmd = Conn.CreateCommand();

    //SQL QUERY
    cmd.CommandText = "Delete from classes where teacherid=@id";
    cmd.Parameters.AddWithValue("@id", id);
    /*cmd.CommandText = query;*/
    cmd.Prepare();
        

    cmd.ExecuteNonQuery();

    //create the second SQL QUERY.
    MySqlCommand cmd2 = Conn.CreateCommand();

    cmd2.CommandText = "Delete from teachers where teacherid=@id";
    cmd2.Parameters.AddWithValue("@id", id);
    cmd2.Prepare();

    cmd2.ExecuteNonQuery();

    Conn.Close();


}

Solution 7:^[7]

To achieve what you want, can also be done using single query using "JOIN" on DELETE.

Go here for reference

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Michie
Solution 2	lebron Brian
Solution 3	Hitesh
Solution 4	Hari
Solution 5	marc_s
Solution 6	Hello_World
Solution 7	Siddhant

'Give two sql queries in asp.net controller

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]