Question 87201 in ASP.NET Question posted in
The official documentation can be found here.

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();


    }
Tags:

7

Answers


  1. 0

    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();
}
    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
  3. 0

    try cross checking the database connection or database name.

    Login or Signup to reply.
  4. 0

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

    Login or Signup to reply.
  5. 0

    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.

    Login or Signup to reply.
  6. 0

    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();


}
    Login or Signup to reply.
  7. 0

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

    Go here for reference

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search