Deserializing CSV file to Collection

Every one know that CSV file stores the tabular data in plain text format.A CSV file consists of any number of records, separated by line breaks of some kind; each record consists of fields, separated by some other character or string, most commonly a literal comma or tab. Usually, all records have an identical sequence of fields. I have a requirement like I want to read the CSV file and Convert to Collection of objects. Steps to convert the CSV file to collection as follows.

1. Read all the lines from the CSV file
2. Take the first line as the header and remaing lines as records
3. Convert the data to data table by parsing first line to data columns and remaining lines to data rows
4. Convert data table to collection

Sample code to implement step 1 to 3

 public static DataTable ToDataTable(string csvFileNameIncludingPath)
        {
            var dt = new DataTable();

            var s = File.ReadAllLines(csvFileNameIncludingPath);
            string[] tableData = s.Split("\r\n".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
            var col = from cl in tableData[0].Split(",".ToCharArray())
                      select new DataColumn(cl.TrimStart('"').TrimEnd('"')); // In my case all the field values are included in the double quotes ex: "FirstName". So used TrimStart and TrimEnd methods to remove double quotes
            dt.Columns.AddRange(col.ToArray());

            (from st in tableData.Skip(1)
             select dt.Rows.Add(st.Split(",".ToCharArray()).Select(str => str.TrimStart('"').TrimEnd('"')).ToArray())).ToList(); // In my case all the field values are included in the double quotes ex: "jhon". So used TrimStart and TrimEnd methods to remove double quotes

            return dt;
        }

Sample code to implement step 4

 public static List<T> ToCollection<T>(this DataTable dt, IList<KeyValuePair<string, string>> changedNames = null) // changeNames parameter used for mapping the column name change with object propertyname
        {
            List<T> lst = new List<T>();
            Type tClass = typeof(T);
            PropertyInfo[] pClass = tClass.GetProperties();
            List<DataColumn> dc = dt.Columns.Cast<DataColumn>().ToList();
            T cn;
            foreach (DataRow item in dt.Rows)
            {
                cn = (T)Activator.CreateInstance(tClass);
                foreach (PropertyInfo pc in pClass)
                {
                    // Can comment try catch block. 
                    try
                    {
                        DataColumn d = dc.Find(c => c.ColumnName == pc.Name || (changedNames != null && changedNames.Any(x => x.Key == c.ColumnName)));
                        var ds = dc.Where(x => x.ColumnName == pc.Name);
                        if (d != null)
                            pc.SetValue(cn, item[pc.Name], null);
                    }
                    catch
                    {
                    }
                }
                lst.Add(cn);
            }
            return lst;
        }

I hope this will helps you.

DataSet Deserialization from Webservice

I observed that few of the webservices or REST services returns the DataSet with the xml read mode as DiffGram. But our requirement is to deserialize into collection of object of sepecified type. The deserialization of dataset is different from the normal xml or json deserialization. Please find the below steps to deserialize dataset.

Step1: Create the response to DataSet object as follows.

private DataSet GetDataSet(string url)
        {
            DataSet ds = new DataSet();
            using (var client = new WebClient())
            {
                var xml = client.DownloadString(sURL);
                using (var reader = XmlReader.Create(new StringReader(xml)))
                {
                    ds.ReadXml(reader, XmlReadMode.DiffGram);
                }
            }
            return ds;
        }

Step 2: Use the following helper method to Convert Data Table to Collection

 public static List<T> ToCollection<T>(this DataTable dt)
        {
            List<T> lst = new System.Collections.Generic.List<T>();
            Type tClass = typeof(T);
            PropertyInfo[] pClass = tClass.GetProperties();
            List<DataColumn> dc = dt.Columns.Cast<DataColumn>().ToList();
            T cn;
            foreach (DataRow item in dt.Rows)
            {
                cn = (T)Activator.CreateInstance(tClass);
                foreach (PropertyInfo pc in pClass)
                {
                    // Can comment try catch block. 
                    try
                    {
                        DataColumn d = dc.Find(c => c.ColumnName == pc.Name);
                        if (d != null)
                            pc.SetValue(cn, item[pc.Name], null);
                    }
                    catch
                    {
                    }
                }
                lst.Add(cn);
            }
            return lst;
        }

Step 3: Use the methods as follows to Convert DataTable to collection

var ds= GetDataSet(url);
ds.Tables[DataTables.LotTable].ToCollection<Lot>();

I hope this helps you..