How to perform multiple database insertions

Question

I am a beginner and I have a code in which I need to insert multiple products inside a shopping list, however, in the code I made, it is only possible to insert one JSON at a time.

    [HttpPost]
    [Route("lista/addproduto")]
    public HttpResponseMessage AddProdutoNaLista(ProdutosLista prod)
    {
        try
        {
            List<ProdutosLista> lstProdLista = new List<ProdutosLista>();

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    command.CommandText = "INSERT INTO PRODLISTA VALUES (@codigo, @nome, @cbarra, @pco_remar, @unidade, @quantidade, @peso, @pco_total, @id_lista)";

                        command.Parameters.AddWithValue("@codigo", prod.CodProduto);
                        command.Parameters.AddWithValue("@nome", prod.Nome);
                        command.Parameters.AddWithValue("@cbarra", prod.CodBarra);
                        command.Parameters.AddWithValue("@pco_remar", prod.Preco);
                        command.Parameters.AddWithValue("@unidade", prod.Unidade);
                        command.Parameters.AddWithValue("@quantidade", prod.Quantidade);
                        command.Parameters.AddWithValue("@peso", prod.Peso);
                        command.Parameters.AddWithValue("@pco_total", prod.PrecoTotal);
                        command.Parameters.AddWithValue("@id_lista", prod.IdLista);

                        lstProdLista.Add(prod);

                    if (prod.CodProduto != 0 && prod.Preco != 0 && prod.PrecoTotal != 0)
                    {
                        command.ExecuteNonQuery();  
                    }
                }
                connection.Close();
            }
            return Request.CreateResponse(HttpStatusCode.OK, lstProdLista.ToArray());
        }
        catch (Exception ex)
        {
            return Request.CreateResponse(HttpStatusCode.BadRequest, ex.Message);
        }
    }

If I pass only one JSON, it normally inserts, but if I pass more than one JSON, it returns the following error in Postman:

"Undefined object reference for an object instance."

I imagine it’s because I’m assigning more than one value to the properties, I thought about putting inside a while, but I can’t think about the condition of the command.

Answer 1

I will not go into merits of standards of its implementation. I will only look at your code.

You will forward your list of items per parameter using JSON itself and automatically bind to the list. Having the list you can yes make the loop inserts.

I think you’re confusing some points of your implementation.

I will adjust your code as I understand that you should work, because I’m not sure I understand your doubt. Come on:

[HttpPost]
[Route("lista/addproduto")]
public HttpResponseMessage AddProdutoNaLista(List<ProdutosLista> lstProdLista )

Then, from this received list you put a loop going through your list and adding the products.

[HttpPost]
[Route("lista/addproduto")]
public HttpResponseMessage AddProdutoNaLista(List<Produto> lstProdLista )
{
    try
    {
        foreach(Produto item in lstProdLista){
            if (item.CodProduto != 0 && item.Preco != 0 && item.PrecoTotal != 0)
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand())
                    {
                        command.Connection = connection;
                        command.CommandText = "INSERT INTO PRODLISTA VALUES (@codigo, @nome, @cbarra, @pco_remar, @unidade, @quantidade, @peso, @pco_total, @id_lista)";

                        command.Parameters.AddWithValue("@codigo", item.CodProduto);
                        command.Parameters.AddWithValue("@nome", item.Nome);
                        command.Parameters.AddWithValue("@cbarra", item.CodBarra);
                        command.Parameters.AddWithValue("@pco_remar", item.Preco);
                        command.Parameters.AddWithValue("@unidade", item.Unidade);
                        command.Parameters.AddWithValue("@quantidade", item.Quantidade);
                        command.Parameters.AddWithValue("@peso", item.Peso);
                        command.Parameters.AddWithValue("@pco_total", item.PrecoTotal);
                        command.Parameters.AddWithValue("@id_lista", item.IdLista);

                        command.ExecuteNonQuery();  

                    }
                }
                connection.Close();
            }
        }
        return Request.CreateResponse(HttpStatusCode.OK, lstProdLista.ToArray());
    }
    catch (Exception ex)
    {
        return Request.CreateResponse(HttpStatusCode.BadRequest, ex.Message);
    }
}

And taking a little verbosity to take an If and do so the loop.

[HttpPost]
[Route("lista/addproduto")]
public HttpResponseMessage AddProdutoNaLista(List<Produto> lstProdLista )
{
    try
    {
        foreach(Produto item in lstProdLista.Where(lp => lp.CodProduto != 0 && lp.Preco != 0 && item.PrecoTotal != 0)){
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    command.CommandText = "INSERT INTO PRODLISTA VALUES (@codigo, @nome, @cbarra, @pco_remar, @unidade, @quantidade, @peso, @pco_total, @id_lista)";

                    command.Parameters.AddWithValue("@codigo", item.CodProduto);
                    command.Parameters.AddWithValue("@nome", item.Nome);
                    command.Parameters.AddWithValue("@cbarra", item.CodBarra);
                    command.Parameters.AddWithValue("@pco_remar", item.Preco);
                    command.Parameters.AddWithValue("@unidade", item.Unidade);
                    command.Parameters.AddWithValue("@quantidade", item.Quantidade);
                    command.Parameters.AddWithValue("@peso", item.Peso);
                    command.Parameters.AddWithValue("@pco_total", item.PrecoTotal);
                    command.Parameters.AddWithValue("@id_lista", item.IdLista);

                    command.ExecuteNonQuery();  

                }
                connection.Close();
            }
        }
        return Request.CreateResponse(HttpStatusCode.OK, lstProdLista.ToArray());
    }
    catch (Exception ex)
    {
        return Request.CreateResponse(HttpStatusCode.BadRequest, ex.Message);
    }
}