Inserting record and recovering ID generated by SQL Server 2012

Question

I want to insert a record into a table, recover the ID that was generated by AUTO_INCREMENT and insert records into other tables using this ID. But, I want to do this all using the BeginTransaction.

How could I do that?

I’m doing it this way:

    public void venda(Venda_ResumidaModel objVenda)
    {
        using (SqlConnection connection = new SqlConnection("SERVER= ;" + "DATABASE= ;" + "UID= ;" + "PASSWORD=;"))
        {
            SqlCommand cmd = connection.CreateCommand();
            SqlTransaction transaction = null;

            try
            {
                // BeginTransaction() Requires Open Connection
                connection.Open();

                transaction = connection.BeginTransaction();

                // Assign Transaction to Command
                cmd.Transaction = transaction;

                // Execute 1st Command
                cmd.CommandText = "INSERT INTO Venda(ID_Pessoa, ID_Caixa, ID_PDV, ID_Deposito, Data, Itens, Valor, Desconto, Valor_Final, Cancelada, Valor_Dinheiro, Valor_Cheque, Valor_Cartao, Valor_Crediario, Valor_Troco) " +
                "values (@ID_Pessoa, @ID_Caixa, @ID_PDV, @ID_Deposito, getdate(), @Itens, @Valor, @Desconto, @Valor_Final, @Cancelada, @Valor_Dinheiro, @Valor_Cheque, @Valor_Cartao, @Valor_Crediario, @Valor_Troco)";


            cmd.Parameters.AddWithValue("@ID_Pessoa", objVenda.ObjProprietario.ID_Pessoa);
            cmd.Parameters.AddWithValue("@ID_Caixa", objVenda.ObjCaixa.ID_Caixa);
            cmd.Parameters.AddWithValue("@ID_PDV", 1);
            cmd.Parameters.AddWithValue("@ID_Deposito", 1);
            cmd.Parameters.AddWithValue("@Itens", objVenda.Itens);
            cmd.Parameters.AddWithValue("@Valor", objVenda.Valor_Venda);
            cmd.Parameters.AddWithValue("@Desconto", objVenda.Desconto);
            cmd.Parameters.AddWithValue("@Valor_Final", objVenda.Valor_final);
            cmd.Parameters.AddWithValue("@Cancelada", 'N');
            cmd.Parameters.AddWithValue("@Valor_Dinheiro", objVenda.Valor_dinheiro);
            cmd.Parameters.AddWithValue("@Valor_Cheque", objVenda.Valor_cheque);
            cmd.Parameters.AddWithValue("@Valor_Cartao", objVenda.Valor_cartao);
            cmd.Parameters.AddWithValue("@Valor_Crediario", objVenda.Valor_crediario);
            cmd.Parameters.AddWithValue("@Valor_Troco", objVenda.Valor_troco);

            cmd.Parameters.AddWithValue("@id", 0).Direction = ParameterDirection.Output;

            cmd.ExecuteScalar();


                // Execute 2nd Command
            if (objVenda.Valor_dinheiro > 0)
            {
                cmd.CommandText = "Insert into Caixa_Movimentacao (Caixa, Data_Movimentacao, Tipo, Valor, Forma_Pagamento)" +
                    "values (@Caixa, getdate(), 6, @Valor_, 1)";


                cmd.Parameters.AddWithValue("@Caixa", objVenda.ObjCaixa.ID_Caixa);
                cmd.Parameters.AddWithValue("@Valor_", objVenda.ValorVenda);

                cmd.ExecuteNonQuery();
            }





            int retorno = Convert.ToInt32(cmd.Parameters["@id"].Value);

            cmd.CommandText = "insert into Venda_Item_Servico (Id_venda, ID_Servico, Valor_Venda, Desconto, Valor_Total) values ("+ retorno +", @ID_Servico, @Valor_Venda, @Desconto, @Valor_Total)";
            //cmd.Parameters.AddWithValue("@id", objVenda);
            cmd.Parameters.AddWithValue("@ID_Servico", objVenda.ObjServico.Codigo);
            cmd.Parameters.AddWithValue("@Valor_Venda", objVenda.ObjServico.Valor_Venda);
            //cmd.Parameters.AddWithValue("@Desconto", objVenda);
            cmd.Parameters.AddWithValue("@Valor_Total", objVenda.ObjServico.Valor_Venda);

            cmd.ExecuteNonQuery();

            transaction.Commit();

            }
            catch
            {
                transaction.Rollback();
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

Answer 1

Will change the query to (the OUTPUT is in the middle):

cmd.CommandText = "INSERT INTO Venda(ID_Pessoa, ID_Caixa, ID_PDV, ID_Deposito, Data,
     Itens, Valor, Desconto, Valor_Final, Cancelada, Valor_Dinheiro, Valor_Cheque, 
     Valor_Cartao, Valor_Crediario, Valor_Troco)
     OUTPUT INSERTED.ID 
     values (@ID_Pessoa, @ID_Caixa, @ID_PDV, @ID_Deposito, getdate(), @Itens, @Valor, 
    @Desconto, @Valor_Final, @Cancelada, @Valor_Dinheiro, @Valor_Cheque, @Valor_Cartao, 
     @Valor_Crediario, @Valor_Troco)";

And you will execute her so:

var IdInserido = (int)cmd.ExecuteScalar();

According to Giovanni Machado in comment below. This code may have problems if there is a trigger set to table. in this case it would be better to use

SELECT scope_identity()

or

Set @Id_Out = @@IDENTITY

I put in the Github for future reference.

Answer 2

You did not say which database you are using, but if you are using Postgresql change your query to:

cmd.CommandText = "INSERT INTO Venda(ID_Pessoa, ID_Caixa, ID_PDV, ID_Deposito, Data, Itens, Valor, Desconto, Valor_Final, Cancelada, Valor_Dinheiro, Valor_Cheque, Valor_Cartao, Valor_Crediario, Valor_Troco) " +
                "values (@ID_Pessoa, @ID_Caixa, @ID_PDV, @ID_Deposito, getdate(), @Itens, @Valor, @Desconto, @Valor_Final, @Cancelada, @Valor_Dinheiro, @Valor_Cheque, @Valor_Cartao, @Valor_Crediario, @Valor_Troco) RETURNING ID_Pessoa";

Answer 3

How are you using the Executescalar, you can add in your query/insert a select MAX(ID_Venda) right after the Insert and will be returned a object with the value.

cmd.CommandText = @"INSERT INTO Venda(
    ID_Pessoa, ID_Caixa, ID_PDV, ID_Deposito, Data, Itens, Valor, 
    Desconto, Valor_Final, Cancelada, Valor_Dinheiro, Valor_Cheque,
    Valor_Cartao, Valor_Crediario, Valor_Troco
)
VALUES (
    @ID_Pessoa, @ID_Caixa, @ID_PDV, @ID_Deposito, getdate(), 
    @Itens, @Valor, @Desconto, @Valor_Final, @Cancelada, @Valor_Dinheiro,
    @Valor_Cheque, @Valor_Cartao, @Valor_Crediario, @Valor_Troco);
SELECT MAX(ID_Venda) FROM Venda";

To get the returned ID

var idVenda = Convert.ToInt32(cmd.ExecuteScalar());