Run multiple SQL Commands on button click EDIT SOLVED

Question

I have searched here for how to run multiple sql commands on this post and for me it's not clear.

I want to run two SQL queries on button1_click. 1st is delete from table tehnicke_karakteristike where sifra_proizvoda=@sifraProizvoda and 2nd is delete from table proizvod where sifra_proizvoda=@sifraProizvoda. sifra_proizvoda is FK to tehnicke_karakteristike.

Here is my code:

        private void button1_Click(object sender, EventArgs e)
    {
       konekcija = new SqlConnection(Konekcija.konString);

        SqlCommand komanda = new SqlCommand("DELETE FROM tehnicke_karakteristike WHERE sifra_proizvoda = @sifra_proizvoda", konekcija);
        SqlParameter sifraParam = new SqlParameter("@sifra_proizvoda", SqlDbType.Int);

        SqlCommand komanda1 = new SqlCommand("DELETE FROM proizvod WHERE sifra_proizvoda = @sifra_proizvoda", konekcija);
        SqlParameter sifraParam1 = new SqlParameter("@sifra_proizvoda", SqlDbType.Int);

        komanda.Parameters.Add(sifraParam);
        komanda1.Parameters.Add(sifraParam1);

        sifraParam.Value = tbSifra.Text;
        sifraParam1.Value = tbSifra.Text;
        try
        {
            konekcija.Open();
            komanda.ExecuteNonQuery();
            komanda1.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);

        }
        finally
        {
            konekcija.Close();
        }

    }

When i run this, only first command pass.

Some advice: look at "Dapper". Writing ADO.NET code yourself isn't fun, interesting, or useful. — Marc Gravell, Jun 12 '20 at 23:26

score 0 · Answer 1 · answered Jun 12 '20 at 23:17

0

You're missing komanda1.ExecuteNonQuery() after komanda.ExecuteNonQuery(), which is why only komanda gets executed while komanda1 doesn't.

Also, it's probably a good idea to wrap those 2 commands in a transaction if you're running them like this

answered Jun 12 '20 at 23:17

Janilson

1,042
1
9
23

i add that command line after komanda and komanda1 and i get this error: System.InvalidOperationException: 'ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.' – BeginnerCoding Jun 12 '20 at 23:21
Think you need to call `connection.Open()` – John Wu Jun 12 '20 at 23:23
@BeginnerCoding is the komanda1 execute inside the try segment? – Janilson Jun 12 '20 at 23:23
@Lucas no it is not – BeginnerCoding Jun 12 '20 at 23:31
@BeginnerCoding then it should be. You're closing the connection inside the finally segment that gets executed after the try segment, you want to run both commands before closing the connection – Janilson Jun 12 '20 at 23:35

score 0 · Answer 2 · answered Jun 12 '20 at 23:21

When i run this, only first command pass.

That is logical: you are executing the query only once:

komanda.ExecuteNonQuery();

Therefore it's the latest assignment that prevails.

You have two queries but a shared parameter, so what you could do is reuse the query by just changing the SQL. If you have an instantiated SqlCommand with a connection property, and parameters set, you can change the CommandText property and run a different query on that SqlCommand. Not tested but I would try something like this:

    SqlCommand komanda = new SqlCommand("DELETE FROM tehnicke_karakteristike WHERE sifra_proizvoda = @sifra_proizvoda", konekcija);
    SqlParameter sifraParam = new SqlParameter("@sifra_proizvoda", SqlDbType.Int);
    komanda.Parameters.Add(sifraParam);
    sifraParam.Value = tbSifra.Text;

    // execute the 1st statement
    komanda.ExecuteNonQuery();

    // reuse the command
    komanda1.CommandText = "DELETE FROM proizvod WHERE sifra_proizvoda = @sifra_proizvoda"
    // execute the 2nd statement
    komanda.ExecuteNonQuery();

That makes the code a bit more compact but you can perfectly define two distinct SqlCommand objects - which you did. Then each has to be executed once.

Run multiple SQL Commands on button click EDIT SOLVED

2 Answers2