2

What would be the right way to avoid duplication when querying datatable and then saving it to DataTable. I'm using the pattern below, which gets very error-prone once tables grow. I looked at below hints. With first one copyToDataTable() looks not really applicable and second is for me much too complex for the task. I would like to split the below code into 2 separate methods (first to build the query and second to retrieve the DataTable). Perhaps if I avoid the anonymous type in the query this should be easier to avoid hardcoding all the column names - but I'm somehow lost with this.

Filling a DataSet or DataTable from a LINQ query result set or https://msdn.microsoft.com/en-us/library/bb669096%28v=vs.110%29.aspx

public DataTable retrieveReadyReadingDataTable()
        {
        DataTable dtblReadyToSaveToDb = RetrieveDataTableExConstraints();
        var query = from scr in scrTable.AsEnumerable()
                    from products in productsTable.AsEnumerable()
                    where(scr.Field<string>("EAN") == products.Field<string>("EAN"))
                    select

                    new
                    {
                        Date = DateTime.Today.Date,
                        ProductId = products.Field<string>("SkuCode"),
                        Distributor = scr.Field<string>("Distributor"),
                        Price = float.Parse(scr.Field<string>("Price")),
                        Url = scr.Field<string>("Url")
                    };


        foreach (var q in query)
        {
            DataRow newRow = dtblReadyToSaveToDb.Rows.Add();

            newRow.SetField("Date", q.Date);
            newRow.SetField("ProductId", q.ProductId);
            newRow.SetField("Distributor", q.Distributor);
            newRow.SetField("Price", q.Price);
            newRow.SetField("Url", q.Url);
        }

        return dtblReadyToSaveToDb;
    }
Community
  • 1
  • 1
Turo
  • 1,537
  • 2
  • 21
  • 42
  • Please share the implementation of `RetrieveDataTableExConstraints()` – ViRuSTriNiTy Dec 30 '15 at 09:38
  • This is just retrieving the schema of the DataTable from SQL server with DataTable retrieveReady.... = reader.GetSchemaTable(); + some minor tweaks to remove PK, allow nulls etc. – Turo Dec 30 '15 at 09:42
  • What really is your question? What do you mean by "avoid duplication"? – Ivan Stoev Dec 30 '15 at 11:08
  • Hi Ivan. I stated this below in the comment to ViRuSTriNiTy. For me the duplication is that I hardcode the names in the anonymous type and hardcode them again as names of the DataRows, while these names are already set when I retrieved the DataTable. Anytime there are changes this needs to be changed in many places. – Turo Dec 30 '15 at 11:14

1 Answers1

0

Firstly, you have to decide what "duplicate" means in your case. According to your code i would say a duplicate is a row with the same value in column Date, ProductId and Distributor. So add a multi column primary key for those columns first.

Secondly, you should add some sort of code that first queries existing rows and then compares these existing rows to the rows you want to create. If a match is found, then simply just don't insert a new row.

ViRuSTriNiTy
  • 5,017
  • 2
  • 32
  • 58
  • Hmm, this is not really telling me much :( For me the duplication is that I set the names of the rows, when I already did this when 2nd. creating anonymous type 3rd. these are also already available in the retrieved TataDable initiated at the beginning of the code. – Turo Dec 30 '15 at 09:35
  • and actually fourth time, as attributes of q, luckily these are intellisensed – Turo Dec 30 '15 at 09:38