Search for data not committed

Question

I was doing some tests with respect to transactions and I saw that if I perform a search of the data registered and not committed they are returned. In Entity Framework this doesn’t happen, and so I searched behind the scenes he works with transactions.

Does anyone know how I can get around this situation:

var conn = new SqlConnection(ConfigurationManager.
                ConnectionStrings["connNorthwind"].ConnectionString);

        conn.Open();
        var transaction = conn.BeginTransaction();

        string productName = "tEST1";


        var commad1 = conn.CreateCommand();
        commad1.Transaction = transaction;

        var commad2 = conn.CreateCommand();
        commad2.Transaction = transaction;

        var commad3 = conn.CreateCommand();
        commad3.Transaction = transaction; 


        commad1.CommandText = "INSERT products(ProductName, Discontinued)VALUES ('" + productName + "', 'true')";
        commad1.ExecuteNonQuery();

        commad2.CommandText = "INSERT products(ProductName, Discontinued)VALUES ('" + productName + "', 'true')";
        commad2.ExecuteNonQuery();

        commad3.CommandText = "SELECT * FROM products where ProductName like '%" + productName + "%'";
        var reader = commad3.ExecuteReader(System.Data.CommandBehavior.SingleResult);

        bool produtoExistente = false;
        if (reader.HasRows)
        {
            produtoExistente = true;
        }


        Assert.IsFalse(produtoExistente);

Answer 1

The data appears because you possibly use the same connection that opened the transaction to query, so it is correct that they appear.

In the Entity Framework, the transaction is not by connection, but by scope. This is because it is the Entity Framework that controls the connection(s), not the programmer. In addition, the transactional scope using MSDTC allows the use of distributed transactions, ideal for load balancing on large systems.

In the case of your code, missed yet perform or a Commit or a Rollback.