XML Bulk Import of Records Using PostgreSQLCopyHelper - Npgsql.PostgresException: 42601

Question

On importing records from an XML with two records file into database table, I have an issue were the first record is being inserted twice while the second record isn't getting saved into the table.

This is the XML file with the two records/products

<?xml version="1.0" encoding="ISO-8859-1"?>
<ProdExtract>

 <ExtractHeader>
  <Schema>
   <id>Accord ePOS Product Extract</id>
   <Version>1.00</Version>
  </Schema>  
 </ExtractHeader>

 <Product>
  <Mode>Modify</Mode>
   <ProductID>  
    <LongDescription>ZOLMANS MINT SAUCE      #</LongDescription>  
   </ProductID>
   <BasicFields>  
    <Units>6</Units>
    <VAT VatCode="A" VatRate="0" />  
   </BasicFields>
   <Price>  
    <CurrentWSP>9.55</CurrentWSP>
    <CurrentRSP>2.01</CurrentRSP>  
   </Price>
   <Barcode>  
    <Eancode>5000147032921</Eancode>  
   </Barcode>
 </Product>

 <Product>
  <Mode>Modify</Mode>
   <ProductID>  
    <LongDescription>TEST XML IMPORT PRODUCT     </LongDescription>  
   </ProductID>
   <BasicFields>  
    <Units>10</Units>
    <VAT VatCode="A" VatRate="0" />  
   </BasicFields>
   <Price>  
    <CurrentWSP>8.88</CurrentWSP>
    <CurrentRSP>2.22</CurrentRSP>  
   </Price>
   <Barcode>  
    <Eancode>5000147032923</Eancode>  
   </Barcode>
 </Product>

</ProdExtract>

Reading the file and saving it to the database table

var entities = new List<ProductViewModel>();
        PostgreSQLCopyHelper<ProductViewModel> insert = null;

        NpgsqlConnection connection = new NpgsqlConnection("Host=192.168.0.52;Database=bolo;Username=western;Password=western");
        connection.Open(); /*OPEN DATABASE CONNECTION*/            

        ProductViewModel p;            

        try
        {
            if (ModelState.IsValid && upload != null)
            {
                //uploaded file
                Stream stream = upload.InputStream;

                XmlDocument xDoc = new XmlDocument();
                xDoc.Load(stream);
                XmlElement root = xDoc.DocumentElement;
                XmlNodeList nodes = root.SelectNodes("Product");

                foreach (XmlNode node in nodes)                    
                {
                    //while((p = node.InnerText) != null)
                    {
                        insert = new PostgreSQLCopyHelper<ProductViewModel>("public", "q_product");
                        p = new ProductViewModel();
                        p.q_guid = Guid.NewGuid();
                        p.q_description = node.SelectSingleNode("ProductID/LongDescription").InnerText;
                        p.q_barcode = node.SelectSingleNode("Barcode / Eancode").InnerText;
                        p.q_casesize = Convert.ToDecimal(node.SelectSingleNode("BasicFields/Units").InnerText);
                        p.q_sellprice = Convert.ToDecimal(node.SelectSingleNode("Price/CurrentRSP").InnerText);
                        p.q_casecost = Convert.ToDecimal(node.SelectSingleNode("Price/CurrentWSP").InnerText);
                        entities.Add(p);
                    }                        
                }

                try
                {
                   insert.SaveAll(connection, entities);
                    int lineCount = entities.Count();
                    TempData["SuccessMessage"] = lineCount + " Records Inserted!";
                    connection.Close(); /*CLOSE CONNECTION*/
                }
                catch (DataException error)
                {
                    TempData["ErrorMessage"] = error.Message;
                }

                return RedirectToAction("Index");
            }                
        }
        catch(DataException error)
        {

            ModelState.AddModelError("", error.Message);
        }

        return RedirectToAction("Index");

UPDATE

Mapping this way, only gets the first row saved into the table. Testing with a file that only has 3 products, and the first row will get saved 3 times in the table.

insert = insert ?? new PostgreSQLCopyHelper<ProductViewModel>("public", "q_product")
   .MapUUID("q_guid", x => Guid.NewGuid())
   .MapText("q_description", x => node.SelectSingleNode("ProductID/LongDescription").InnerText)
   .MapText("q_barcode", x => node.SelectSingleNode("Barcode/Eancode").InnerText)
  etc........
                        ;
  entities.Add(p);

Mapping this way gets all the approppiate records into entities.Add(p) but is crashing out on the line insert.SaveAll(connection, entities); with error Npgsql.PostgresException: 42601: syntax error at or near ")"... because insert has no columns to insert data into at time of saving.

insert = new PostgreSQLCopyHelper<ProductViewModel>("public", "q_product");
      p = new ProductViewModel();
      p.q_guid = Guid.NewGuid();
      p.q_description = node.SelectSingleNode("ProductID/LongDescription").InnerText;
      p.q_barcode = node.SelectSingleNode("Barcode / Eancode").InnerText;
      p.q_casesize = Convert.ToDecimal(node.SelectSingleNode("BasicFields/Units").InnerText);
      p.q_sellprice = Convert.ToDecimal(node.SelectSingleNode("Price/CurrentRSP").InnerText);
      p.q_casecost = Convert.ToDecimal(node.SelectSingleNode("Price/CurrentWSP").InnerText);
      entities.Add(p);

Update Solution

Removing the line insert = new PostgreSQLCopyHelper<ProductViewModel>("public", "q_product");

and using the following, works for my current needs. I am aware there are probably faster methods than this solution, but this works for my current needs at the moment.

NpgsqlCommand cmd = new NpgsqlCommand
                            ("INSERT INTO q_product (q_guid, q_description, q_barcode, q_casesize, q_sellprice, q_casecost, q_import_vatcode)" +
                            "VALUES (@pk, @des, @bcode, @csize, @sprice, @ccost, @vcode )",
                            con);

                        cmd.Parameters.AddWithValue("@pk", p.q_guid);
                        cmd.Parameters.AddWithValue("@des", p.q_description);
                        cmd.Parameters.AddWithValue("@bcode", p.q_barcode);
                        cmd.Parameters.AddWithValue("@csize", p.q_casesize);
                        cmd.Parameters.AddWithValue("@sprice", p.q_sellprice);
                        cmd.Parameters.AddWithValue("@ccost", p.q_casecost);
                        cmd.Parameters.AddWithValue("@vcode", p.q_import_vatcode);
                        cmd.ExecuteNonQuery();

Answer 1

Your issue is that you are repeatedly adding the same element:

entities.Add(p);

On each iteration of the loop you need to create a new object and set properties on it:

p = new WhateverYourTypeIs();
p.Property1 = node.Something;
p.Property2 = node.SomethingElse;
entities.Add(p);

etc.