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!!!