How to create C# method to do 2 Query SQL Insert?

Question

To create a c# method to insert data, this method does more than one INSERT behind the other?

'Cause I tried it here and it didn’t work out!

Answer 1

Good morning,

I did it this way and it worked out for me.

Following example:

    public static void InsereDadosArqTxtRetSaidaDAL(out string pstrMsg, out bool pbooRetorno, ArrayList parrHeaderArqTxt, List<ArrayList> plstItensArqTxt)
    {
        pstrMsg = string.Empty;
        pbooRetorno = false;

        SqlConnection conn = ConexaoBD.CriarConexao();

        // Inseri dados na tabela de cabeçalho
        using (SqlCommand cmd = new SqlCommand("uspCtzInsertConfirmPedProcess", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@cd_rejeicao", parrHeaderArqTxt[1]));
            cmd.Parameters.Add(new SqlParameter("@nu_ped", parrHeaderArqTxt[2]));
            cmd.Parameters.Add(new SqlParameter("@dt_hrs_inclusao", parrHeaderArqTxt[3]));

            SqlParameter nuRetornoID = cmd.Parameters.Add(new SqlParameter("@cd_confirmPedProcess", SqlDbType.Int));
            nuRetornoID.Direction = ParameterDirection.Output;

            SqlTransaction tran = cmd.Connection.BeginTransaction();
            cmd.Transaction = tran;

            try
            {
                cmd.ExecuteNonQuery();

                // Capturando o último ID de retorno do parâmetro OUTPUT
                int nuCdPedProcess = (int)nuRetornoID.Value;

                using (SqlCommand cmd2 = new SqlCommand("uspCtzInsertConfirmItPedProcess", conn))
                {
                    cmd2.CommandType = CommandType.StoredProcedure;

                    cmd2.Transaction = tran;

                    foreach (ArrayList itensArqTxt in plstItensArqTxt)
                    {
                        cmd2.Parameters.Add(new SqlParameter("@cd_PedProcess", nuCdPedProcess));
                        cmd2.Parameters.Add(new SqlParameter("@cd_rejeicao", itensArqTxt[1]));
                        cmd2.Parameters.Add(new SqlParameter("@cd_mercadoria", itensArqTxt[2]));
                        cmd2.Parameters.Add(new SqlParameter("@qtde_mercadoria_ped", itensArqTxt[3]));
                        cmd2.Parameters.Add(new SqlParameter("@qtde_mercadoria_ok", itensArqTxt[4]));

                        cmd2.ExecuteNonQuery();

                        cmd2.Parameters.Clear();
                    }
                }
                tran.Commit();

                pbooRetorno = true;
            }
            catch (SqlException ex)
            {
                tran.Rollback();

                pstrMsg = string.Format("Erro:\nMétodo: 'InsereDadosArqTxtRetSaidaDAL'\nDetalhes: {0}", ex.Message);

                pbooRetorno = false;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }
    }