Skip to main content

Linq to DataTables - The Easy Way

The Problem:

  • Couldn't easily read an excel file and query it programmatically.
  • Using JSON.Net took a little longer than I thought it would.
  • Wanted to brush up on my Linq skills.
  • This was a rush job


 The Solution:

  • Utilized "Excel Data Reader", which allowed me to read Excel data and converted it to a DataSet
  • Used Linq to query the data easily, and produced manageable code (for me anyway)
  • Used JSON>NET to serialize my code and write to the DOM

Code Samples:

             DataRow dr = ds.Tables[0].Rows.Cast<DataRow>()
                .Where(row => row["Column1"] == DBNull.Value)

                .FirstOrDefault();

 In this example, I'm wrote a simple Linq query which returned all rows in the DataTable what has any data in the first column. Not the most sophisticated, but you get the point.

 What I've Learned:

 Originally, I thought casting the DataTable as Enumerable was enough to bring back the desired result.  I had a lot of trouble creating a valid where clause, which caused a lot of casting and type comparison errors.  Rows.Cast<DataRow> resolved most of the issues. Essentially, casting the collection items to a DataRow type made it easy to grab the values I need quickly.

 More Reading:

 https://exceldatareader.codeplex.com/
http://stackoverflow.com/questions/12195952/making-a-datarow-select-query

Comments

Post a Comment

Popular posts from this blog

Resolved Sitecore "If you publish now, the selected version will not be visible on the web site" warning

The Problem:   Unable to publish any Sitecore item within a particular site, even out of the workflow. Rather, a warning reads " If you publish now, the selected version will not be visible on the web site " What I've Discovered: I couldn't publish any item in the site, not just one or two I viewed all parent items of the items in question The main home page displayed a different warning " This item will never be published because it's publishable option is disabled " Another sitecore developer reminded me of the standard fields option View --> check standard fields to show all standard page fields Found out that somebody checked Never Publish within the publishing section The Fix: After I unchecked the checkbox and saved the change, I was able to publish again.  :) Conclusion: Looks like another Sitecore user thought the children items would not be affected by this change.  Lesson  learned.
Post a Comment
Read more

[Resolved] Sitecore ParseException: End of string expected at position...

Problem:  I have a line of code that uses Sitecore Fast Query to pull all items + children starting with a site item, like so: Item [] allItems = db.SelectItems( "fast:" + sitecorePath + "//*" ); Unfortunately, I would get a Sitecore parsing error at runtime: ParseException: End of string expected at position... Turns out Sitecore doesn't like hyphens ('-') in any sitecore path when using fast query, which I have a few distributor sites in a folder which contained hyphens. Solution: I create a simple method that resolves a sitecore path to be Sitecore fast query friendly:             string sitecorePath = "" ;             if (siteItem.Paths.FullPath.Contains( "-" ))             {                 String [...
Post a Comment
Read more

Basic Recursion Example in JavaScript

Problem :  I've been having to write solutions which required some sort of recursion.  The last recursion assignment I had to write was a homework assignment about 10 years ago. I remembered the concept, but forgot how to write it. Solution :  Here’s a simple example of recursion. I start with an array, and after I fire the function, I remove that index from the array, so it shrinks. Once the array is empty, recursion stops.  Pretty straight forward! Code : let books = [ 'Chosen by God', 'Holiness of God', 'Essential Truths', 'Justified by Faith' ]; function readBooks(b){ while (b.length > 0){ console.log('I\'v read ' + b[0]); b.shift(); readBooks(b); } } readBooks(books);
Post a Comment
Read more