C# Excel, DataTable Basics - Tool Developer Notes - Part 13

Tip #1 - Difference between DataTable and DataSet
DataSet
From MSDN link

• Derived from System.Data
• Data obtained form ADO.NET store is stored as In-memory cache data using DataSet
• DataSet is a collection of DataTables

DataTable

• Used by DataSet
• In memory Data Storage

Tip #2 - Load DataTable from FlatFiles, Load DataSet

Sample File Format

C# Sample Code to Load Data from FlatFiles, Populate DataTable, Create DataSet and List Data. Console Application

namespace SampleExercises

{

    using System;

    using System.Collections.Generic;

    using System.Linq;

    using System.Text;

    using System.Data;

    using System.IO;

    public class DataTableLoading

    {

        static void Main(string[] args)

        {

            //CreateData Table

            DataTable fileData = new DataTable();

            //Add Columns to the DataTable

            fileData.Columns.Add("Name", typeof(string));

            fileData.Columns.Add("Value", typeof(int));

            //Files to be loaded in DataTable

            string[] fileNames = { "E:\\Test1.txt", "E:\\Test2.txt" };

            foreach (string fileName in fileNames)

            {

                string[] fileLines = File.ReadAllLines(fileName);

                //Parse Each Line and Load Data

                bool firstLine = true;

                foreach (string line in fileLines)

                {

                    //Flag to skip column Names

                    if (!firstLine)

                    {

                        string[] dataValue;

                        char[] spliChar = { '\t', ' ' };

                        //Fetch the values for each row

                        dataValue = line.Split(spliChar);

                         //This would change based on number of values, This is only example code

                        //Add Row

                        fileData.Rows.Add(dataValue[0], dataValue[1]);

                    }

                    firstLine = false;

                }

            }

            //Assign to DataSet

            DataSet fileDataSet = new DataSet();

            fileDataSet.Tables.Add(fileData);

            //List Every Row in DataSet

            foreach (DataRow rowDataVal in fileDataSet.Tables[0].Rows)

            {

                Console.WriteLine(rowDataVal["Name"].ToString() + " " + rowDataVal["Value"].ToString());

            }

            Console.ReadLine();

        }

    }

}

Output Window 

Tip #3 - Remove Columns from DataTable

Stackoverflow Link

Tip #4 - Remove DataTables from DataSet

MSDN Link

Tip #5 - Kill Excel Process

There were issues with Excel processing not closing after the program is completed. Following links were useful Link1, Link2

Sample example for Reading from Excel

namespace SampleExercises

{

    using System;

    using Excel = Microsoft.Office.Interop.Excel;

    using System.Collections.Generic;

    using System.Linq;

    using System.Text;

    using System.Runtime.InteropServices;

    public class ExcelRead

    {

        static void Main(string[] args)

        {

            Excel.Application exlApp = new Excel.Application();

            Excel.Workbook exlWorkbook = exlApp.Workbooks.Open("E:\\ExcelTemplate.xlsx");

            Console.WriteLine("Excel Work Book Name is " + exlWorkbook.Name);

            foreach (Excel.Worksheet exlWorksheet in exlWorkbook.Worksheets)

            {

                Console.WriteLine("Excel WorkSheet Name is " + exlWorksheet.Name);

            }

            exlApp.Workbooks.Close();

            Marshal.ReleaseComObject(exlApp.Workbooks);

            exlApp.Quit();

            Console.ReadLine();

        }

    }

}

Tip #6 - Working with Types and Date format Validation

Sample console app to validate Date Format, Data Type

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Globalization;

namespace SampleExercises

{

    class DateCheck

    {

        static void Main(string[] args)

        {

            //#1. DataTypes Check

            string dateValue = "10/10/2012 10:30";

            string dateFormat = "MM/dd/yyyy HH:mm";

            DateTime getDate;

            CultureInfo dateProvider = CultureInfo.InvariantCulture;

            try

            {

                //Verify it is of DateTime

                getDate = DateTime.ParseExact(dateValue, dateFormat, dateProvider);

                Console.WriteLine("Date Value is " + getDate);

                //Verify with Type check

                if (getDate.GetType() == typeof(DateTime))

                {

                  Console.WriteLine("DataType is of type Int, Value for getDate is " + getDate);

                }

            }

            catch(Exception Ex)

            {

                Console.WriteLine("Error is " + Ex.Message.ToString());

            }

            Console.ReadLine();

        }

    }

}

Happy Learning!!!

Good Read - Product Development for Startups

Good Reads

• Product Development for Startups.
• Dropbox Startup Lessons Learned.

Happy Reading!!!

C# - Notes - Working in XML in C# - Part X Tool Developer Notes

[Previous Post in Series - C# Basics - Tool Developer Notes - Part IX]


Tip #1 - This post is based on reading and updating XML data in C#. Example - Assume you have input as below xml file

Expected ouptut is to populate Sale value based on UnitPrice and Quantity

Steps include

• Parse the XML
• Update SaleValue based on Unit Price and Quantity
• Save the XML file

C# Example code (Console Application)

using System;
using System.Linq;
using System.Data;
using System.Xml.Linq;
namespace xmlExample
{
    class Program
    {
        static void Main(string[] args)
        {
            UpdateSaleValue();
        }
        public static string UpdateSaleValue()
        {
            try
            {
                XDocument xmlFile = XDocument.Load("E:\\SaleData.xml");
                var query = from SaleDataNode in xmlFile.Elements("Sales").Elements("SaleData")
               select SaleDataNode;
                foreach (XElement IndividualSaleData in query)
                {
                    int Quantity = 0;
                    double Price  = 0;
                    double SaleValue = 0;
                    Quantity = Convert.ToInt32(IndividualSaleData.Element("Qty").Value);
                    Price = Convert.ToDouble(IndividualSaleData.Element("UnitPrice").Value);
                    SaleValue = Quantity*Price;
                    IndividualSaleData.Element("SaleValue").Value = SaleValue.ToString();
                 }
                xmlFile.Save("E:\\SaleData.xml");
               return "0";
        }
        catch(Exception Ex)
        {
            return "-1";
        }
    }
}
}

Updated XML results

Tip #2 - For Error 'The type or namespace name ‘log4net’ could not be found'

Below link was useful to fix the issue

Happy Learning!!!

Databases Products

[Next Post in Series - Big Data Products, Big Data Updates]


Two interesting products in Database space - TeraData - Aster and Scalearc

• Using the Map reduce approach, SQL Map reduce product is available from TeraData - Aster. High Performance on large data, Real time Analytics seems impressive offering from SQL Map reduce
• Scalearc - Pattern based Caching, Load balancing, Perf monitoring available for Databases

Predictive caching 

In the context of database/reporting

• Caching the reports / avoiding DB calls
• Applying pagination to avoid database locks

In the context of Application

• Key usage time
• Key reports accessed which can be cached
• Prioritizing tasks based on users

More Reads

• IBM Debuts Netezza Analytics Appliance Geared to Retail Industry

Happy Learning!!!

C# LINQ Basics - Tool Developer Notes - Part IX

[Previous Post in Series - Tool Developer Notes Part VIII]

This post is learning's based on using LINQ to find aggregate sum from input text files. The Scenario is

• Parse tab delimited files
• Aggregate data based in few columns using LINQ queries

I am pretty much comfortable loading the data into SQL server and run TSQL queries to aggregate data in Database tables. LINQ was pretty good learning.

What is LINQ ?

Language Integrated Query. In Simple terms it provide querying capabilities in .NET languages ex-C#, TSQL queries operations - SUM, Group BY capabilites can be done in C# against a dataset

How LINQ works ?

Linq query consists of 3 parts

•  Data source (In our example it is a DataTable)
•  Query 
•  Execute Query

Command Tree is prepared and executed against data source during query execution. The Query is executed when the query variable is iterated. In below example the for loop iteration is the time when the LINQ query is executed, This is called deferred execution.

More reads Link - Link1, Link2

Coming to Exercise for the example

• Consider Input file is as per below format

Expected output contains

• Find MinDate and MaxDate based on Column2
• Aggregate SUM of Column4 based on Column2

Below Stackoverflow post provided useful directions for arriving at solution. Link1, Link2  

The steps for solution include

• Read the text files
• Load Data into a Datatable
• Run LINQ queries to aggregate data

Earlier we have tried similar approach using hash tables in previous post. Below is the example code in a C# console Application

Below is the solution code for a C# / VS2010 Console Application

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using System.Data;

namespace LinqExample1

{

    class Program

    {

        static void Main(string[] args)

        {

            FindAggregate();

        }

        public static IEnumerable<string> ReadAsLines(string filename)

        {

            using (var reader = new StreamReader(filename))

                while (!reader.EndOfStream)

                    yield return reader.ReadLine();

        }

        public static string FindAggregate()

        {

            try

            {

                var filename = "E:\\sample.txt";

                var reader = ReadAsLines(filename);

               

                //Define Data Table

                var data = new DataTable();

                //Add Columns to Data Table

                data.Columns.Add("Column1", typeof(string));

                data.Columns.Add("Column2", typeof(string));

                data.Columns.Add("Column3", typeof(string));

                data.Columns.Add("Column4", typeof(int));

                data.Columns.Add("Column5", typeof(string));

        

                //Add Rows to Data Table

                foreach (var record in reader)

                    data.Rows.Add(record.Split('\t'));

                //Run Aggregate Query to find SUM of Column4 group by Column2

                var result_sum = from rowdata in data.AsEnumerable()

                                 group rowdata by rowdata["Column2"]

                                 into groupData

                                 select new

                                 {

                                     Group = groupData.Key,

                                     Sum = groupData.Sum((r) => decimal.Parse(r["Column4"].ToString()))

                                 };

                //List Sum Group By Column2

                foreach (var val in result_sum)

                {

                    Console.WriteLine("Column2 {0}, Total Value {1} \n", val.Group, val.Sum);

                }

                //Find Min and Max Date based on Column2

                var result_date = from rowdata in data.AsEnumerable()

                                  group rowdata by rowdata["Column2"]

                                      into groupData

                                  select new

                                  {

                                      type = groupData.Key,

                                      MinDate = groupData.Min(record => record["Column5"]),

                                      MaxDate = groupData.Max(record => record["Column5"])

                                  };

                //List MinDate, MaxDate Group By Column2

                foreach (var val in result_date)

                {

                        Console.WriteLine("Column2 Value {0}, Max Date {1} , Min Date {2} \n", val.type ,val.MaxDate, val.MinDate);

                }

                Console.ReadKey();

                return "0";

            }

            catch (Exception EX)

            {

                return "-1";

            }

        }

    }

}

Below is the actual output

More Reads

Query Expression Syntax Examples: Aggregate Operators (LINQ to DataSet)

Happy Learning!!!