ExecuteScalar() with SqlTransaction

Question

I'm trying to do two inserts into a database using sqltransaction and executescalar to return the output ID of the first insert.

This is the code:

try
    {
      using (SqlConnection conn = new SqlConnection("ConnectionString"))
         {
           conn.Open();

           SqlTransaction safetransaction = conn.BeginTransaction();

           foreach (var dado in NFSe)
              {

                SqlCommand cmd_NFSe = new SqlCommand("SqlCommand", conn, safetransaction);


                var newID = cmd_NFSe.ExecuteScalar();

                foreach (var serv in Servico)
                   {

                     string ID = newID.Tostring(); 

                     SqlCommand cmd_NFSeServ = new SqlCommand("SqlCommand", conn, safetransaction);


                     cmd_NFSeServ.ExecuteNonQuery();
                   }                                

               }
          }
     }

 catch (SqlException ex)
     {
        MessageBox.Show(ex.ToString());
     }

But this is not working. Nothing is inserted into the database and I get no error or exception, just nothing happens.

If I use the same code but without the sqltransaction, everything works great.

Any ideas?

Answer 1

You need call safetransaction.Commit();

Answer 2

I wish you showed some of your SQL so we could see what you're doing but I have to assume you have an identity column you are getting the new id back from. I like to avoid multiple trips to the server so you might be well-to-know that you can combine multiple SQL statements in one call by separating them with ";". All statements within that single call are transactional as well so your overall code will be smaller and with a reduced number of trips to the server, faster too. IE:

INSERT INTO MyTable (MyField) VALUES ('Test1'); Select SCOPE_IDENTITY(); INSERT INTO MyTable (MyField) VALUES ('Test2'); Select SCOPE_IDENTITY()

Now use this with a DataAdapter.Fill and you will get a DataSet with 2 tables, each having the new ID for the inserts.

Oh, to your original question, you need to commit your transaction: safetransaction.Commit();