Simultaneous insertion of items and sales with C#, Windows Forms and Postgresql

Question

Hello, I am developing a sales screen in C# Windows Forms. I’m just at the beginning of the project, but I came across a situation where I’m not sure what to do. I am using a Postgresql database, I created a table called SALE and another ITENSVENDA. The logic I am using is the following: When entering the first product creates a row in the SALE table generating a code that is of the SERIAL type. This code is then stored and used to enter the items of this sale in the ITENSVENDA table. That’s where the problem comes, when there is more than one machine inserting at the same time can give problem inserting items with COD from wrong sale, if happen to record multiple sales at the same time, precisely because I need to retrieve the code of my sale that I’m making on my machine. What is the best way for me to solve this problem? what to do not end up releasing items in on sales other than mine?

Answer 1

You need to open a transaction, in the first Insert returns the code that was inserted and then uses it to insert the items.

If all goes well, run Commit, otherwise Rollback.

Example:

using (NpgsqlConnection conexao = new NpgsqlConnection())
{
    conexao.Open();
    using (NpgsqlTransaction transacao = conexao.BeginTransaction())
    {
        try
        {
            string codigoVenda;
            using (NpgsqlCommand cmdInser = new NpgsqlCommand("Insert into [tabela]... returning id", conexao, transacao))
            {
                using (NpgsqlDataReader dr = cmdInser.ExecuteReader())
                {
                    dr.Read();
                    codigoVenda = dr[0].ToString();
                }

            }


            List<string> itens = new List<string>(); //apensa simulando uma lista de itens

            foreach (string item in itens)
            {
                //item.VendaId = codigoVenda; //Atribui o código da venda no item

                using (NpgsqlCommand cmdItem = new NpgsqlCommand("Insert into [tabela]...", conexao, transacao))
                {
                    cmdItem.ExecuteScalar();
                }

            }


            //ocorreu tudo certo
            transacao.Commit();
        }
        catch
        {
            //deu erro
            transacao.Rollback();
        }
    }
}