PostgreSQLCopyHelper Bulk Insert Postgresql Table

Question

I am attempting to Bukl Insert data from Fixed Width File into a Postgresql Table. I came accross the library PostgreSQLCopyHelper

https://github.com/bytefish/PostgreSQLCopyHelper

This is my update action in controller (updated 15/06/17)

        ProductData  pd = new ProductData();

        public ActionResult Update(q_product q_product, HttpPostedFileBase upload)
        {
        ProductData pd;
        var entities = new List<ProductData>();
        PostgreSQLCopyHelper<ProductData> insert;                       

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

                //need to use BULK INSERT or MULTIPLE INSERT at this point;                    

                //get the properties (columns) as set in ProductData.cs
                using (var fdr = new FileDataReader<ProductData>(stream))
                {
                    //read and get each line on imported file
                    while ((pd = fdr.ReadLine()) != null)
                    {
                        //Lets populate insert
                        //map table columns with properties
                        insert = new PostgreSQLCopyHelper<ProductData>("public", "q_product")
                            .MapUUID("q_guid", x => Guid.NewGuid())
                            .MapText("q_barcode", x => pd.barcode)
                            .MapText("q_description", x => pd.description)
                            .MapText("q_import_size", x => pd.size)
                            .MapNumeric("q_stocklevel", x => pd.quantity)
                            .MapText("q_import_vatcode", x => pd.vatCode) //vatcode is numeric in DB, this is a String (new column for this test)
                            .MapNumeric("q_casecost", x => pd.cost)
                            .MapNumeric("q_sellprice", x => pd.price);                            

                        //add the populated entries as we iterate through the file
                        entities.Add(pd);

                        using (var connection = new NpgsqlConnection("Host=192.168.0.52;Database=bolo;Username=western;Password=western"))
                        {
                            try
                            {
                                connection.Open();
                                insert.SaveAll(connection, entities);                                    
                                int lineCount = entities.Count();
                                TempData["SuccessMessage"] = lineCount+" Records Inserted!";
                            }
                            catch (Exception er)
                            {                                    
                                TempData["ErrorMessage"] = er.Message;
                                //TempData["ErrorMessage"] = "Error: importing records!";
                            }
                        }
                    }
                }

                return RedirectToAction("Index");
            }
        }

        catch(DataException error)
        {
            TempData["ErrorMessage"] = "Error importing records!";
            ModelState.AddModelError("", error.Message);
        }

        return RedirectToAction("Index");
    }

ProductData.cs file

public class ProductData 
{
    [Layout(22, 13)]
    public string barcode;        

    [Layout(49, 25)]
    public string description;

    [Layout(74, 5)]
    public string size;

    [Layout(95, 4)]
    public int quantity;

    [Layout(99, 1)]
    public string vatCode;

    [Layout(108, 7)]
    public decimal cost;

    [Layout(115, 7)]
    public decimal price;

    public override string ToString()
    {            
        return String.Format("string: {0}; string: {1}; string: {2}; int: {3}; string: {4}; decimal {5}; decimal {6}",
                barcode, description, size, quantity, vatCode, cost, price
            );            
    }
}

Upon debugging, the

entities

parameter on this line in the update action

 insert.SaveAll(connection, entities); 

happens to be null, thus no row is getting saved and its throwing an "Object not set reference" error. Now from the limited documentation about this CopyHelper library I cannot figure out which class or paramater I have to make IEnumerable, as SaveAll requries an IEnumerable second parameter

As you can see from the debug screen, my pd (ProductData) has the values it needs to store in the table. How do I link that with the IEnumerable paramater needed in the SaveAll method?

Answer 1

I suspect you want something like:

public ActionResult Update(q_product q_product, HttpPostedFileBase upload)
{    
    ProductData pd;
    var entities = new List<ProductData>();
    PostgreSQLCopyHelper<ProductData> insert = null;
    try
    {
        if(ModelState.IsValid && upload != null)
        {                    
            //uploaded file
            Stream stream = upload.InputStream;

            //need to use BULK INSERT or MULTIPLE INSERT at this point;                    

            //get the properties (columns)
            using (var fdr = new FileDataReader<ProductData>(stream))
            {
                //get each line on file
                while ((pd = fdr.ReadLine()) != null)
                {
                    //map table columns with properties
                    insert = insert ?? new PostgreSQLCopyHelper<ProductData>("public","q_product")
                        .MapUUID("q_guid", x => Guid.NewGuid())
                        .MapText("q_barcode", x => this.pd.barcode)
                        .MapText("q_description", x => this.pd.description)
                        .MapText("q_size", x => pd.size) 
                        .MapInteger("q_stocklevel", x => this.pd.quantity)
                        .MapText("q_vatcode", x => pd.vatCode)  
                        .MapMoney("q_casecost", x => this.pd.cost)
                        .MapMoney("q_sellprice", x => this.pd.price);
                    entities.Add(pd);
                }
            }
            using (var connection = new NpgsqlConnection("Host=192.168.0.52;Database=tester;Username=test;Password=test"))
            {
                try
                {
                    connection.Open();
                    insert.SaveAll(connection, entities);                                    
                    TempData["SuccessMessage"] = "Records Inserted!";
                }
                catch (Exception er)
                {
                    TempData["ErrorMessage"] = er.Message;                                    
                    //TempData["ErrorMessage"] = "Error importing records!";
                }
            }

            return RedirectToAction("Index");
        }
    }

    catch(DataException error)
    {
        TempData["ErrorMessage"] = "Error importing records!";
        ModelState.AddModelError("", error.Message);
    }

    return RedirectToAction("Index");
}

The key change is populating insert only once, and then adding entries to entities as you iterate through the uploaded files.

Answer 2

adding on to the awesome and much appreciated answer by mjwillis, if one cannot see the comment section. The following code put everything in order: change the mapping from

.MapText("q_barcode", x => this.pd.barcode)

to

.MapVarchar("q_barcode", x => x.barcode)

as follows

//map table columns with properties
                        insert = insert ?? new PostgreSQLCopyHelper<ProductData>("public", "q_product")
                            .MapUUID("q_guid", x => Guid.NewGuid())
                            .MapVarchar("q_barcode", x => x.barcode)
                            .MapVarchar("q_description", x => x.description)
                            .MapVarchar("q_import_size", x => x.size)
                            .MapNumeric("q_stocklevel", x => x.quantity)
                            .MapVarchar("q_import_vatcode", x => x.vatCode) 
                            .MapNumeric("q_casecost", x => x.cost)
                            .MapNumeric("q_sellprice", x => x.price);