How would i use 2 sql commands with 1 reader?

I am trying to Delete a Token from my SQL Database after it has been used.

            MySqlCommand cmdSel = new MySqlCommand("SELECT * FROM tokens WHERE token = " + int.Parse(passbox.Text), dbCon);
            MySqlDataReader dbRead = cmdSel.ExecuteReader();
            if (dbRead.Read())
            {
                int sqlkey = int.Parse(dbRead["token"].ToString()); 
                if (keyint == sqlkey)
                {
                    using (MySqlCommand delTok = new MySqlCommand("DELETE FROM tokens WHERE token = " + keyint, dbCon))
                    {
                        delTok.ExecuteNonQuery(); //MAIN PROBLEM HERE.
                        /*
                        MySql.Data.MySqlClient.MySqlException: 'There is already an open DataReader associated with this Connection which must be closed first.'
                        */
                        //ERROR ^^^^^^
                    }
                    try
                    {
                        dbCon.Close();
                        loading loading = new loading();
                        loading.Show();
                        this.Hide();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                        return;
                    }
                }
            }

do i have to close the DataReader or is there some way else, and how do i close the reader? i want to Delete the token once the if keyint is sqlkey statement is true/done. the Error only shows once i try to execute the script for the if statement. The "token" is an int(10)

3 answers

  • answered 2020-01-14 02:12 EngineEye

    Made a new private void and called it with the token after the if statement.

            private void delQuery(int token)
            {
                //SETUP CONNECTION
                MySqlConnection dbConn = new MySqlConnection("some connection");
    
                //OPEN CONNECTION
                dbConn.Open();
    
                //DELETE TOKEN
                MySqlCommand delcmd = new MySqlCommand("DELETE FROM tokens WHERE token = " + token, dbConn);
                MySqlDataReader dbReader = delcmd.ExecuteReader();
                dbReader.Read();
    
                //CLOSE CONNECTION
                dbConn.Close();
            }
    

    called it using:

    if (dbRead.Read())
                {
                    int sqlkey = int.Parse(dbRead["token"].ToString());
                    if (keyint == sqlkey)
                    {
                        dbCon.Close();
                        delQuery(keyint);
                    }
                }
    

  • answered 2020-01-14 02:35 Utsho

    You have to close the reader first in order to execute another command. Also, you can't execute a command while Db reader is reading data. So you can use a function or you can execute the command after the reader is closed.

    
    int sqlkey=0;
    
    using(MySqlCommand cmdSel = new MySqlCommand("SELECT * FROM tokens WHERE token = " + int.Parse(passbox.Text), dbCon))
    
     {
    
               MySqlDataReader dbRead = cmdSel.ExecuteReader();
               if (dbRead.Read())
                 {
                   sqlkey = int.Parse(dbRead["token"].ToString()); 
    
                   }
    
           reader.close();
    
      }
    
          if (keyint == sqlkey)
                   {
                       using (MySqlCommand delTok = new MySqlCommand("DELETE FROM tokens WHERE token = " + keyint, dbCon))
                          {
    
                           delTok.ExecuteNonQuery(); 
    
    
                       try
                       {
                           dbCon.Close();
                           loading loading = new loading();
                           loading.Show();
                           this.Hide();
                       }
                       catch (Exception ex)
                       {
                           MessageBox.Show(ex.Message);
                           return;
                       }
                     }
    }
    
    

  • answered 2020-01-14 03:26 Fabio

    Connection allows only one open reader.

    You can get rid of problem with two datareaders, by executing only one "delete" query.
    If token found, query will remove it, if not, query will do nothing.

    using (var connection = new MySqlConnection("connection-string"))
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "DELETE FROM tokens WHERE token = @token";
        var token = new MySqlParameter
        {
            ParameterName = "@token",
            MySqlDbType = MySqlDbType.Int32,
            Value = int.Parse(passbox.Text)
        };
        command.Parameters.Add(token);
    
        connection.Open();
        command.ExecuteNonQuery();                
    }
    

    Don't try to "keep" connection, simply dispose previous and create new every time you need. ADO.NET in background effectively reuse already opened actual connections.

    Use sql parameters for passing values to the query. Sql parameters defend from sql injections and improve sql queries performance, by reusing precompiled query plans.