Thursday, October 25, 2012

How can I reorder columns in a data table?

Problem: How can I reorder columns in a data table?

 Solution: Columns in datatable can be reordered using SetOrdinal method of datatable.
Here is an example code:


DataTable dt = new DataTable();
dt = lst.GetDataTable();
dt.Columns["bank"].SetOrdinal(6);
dt.Columns["remarks"].SetOrdinal(dt.Columns.Count - 1);

Here "bank" column will be set at position 6 and "remarks" column at last of all columns in the table.

Hope this small tips will be helpful.


How can I convert Datatable to Generic List and vice versa in C#?

Problem: I need to convert Generic list to Datatable and Data table to Generic list. How can I do this in C#?

 Solution: You may often require to convert your Datatable to Generic list of your Generic list to Datatable. Following code snippet use extension method for the conversion:




using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Text;

/// <summary>
/// Summary description for GenericListExtensionMethod
/// </summary>
public static class GenericListExtensionMethod
{
    public static DataTable GetDataTable<T>(this List<T> obj)
    {
        DataTable dt = new DataTable();
        //special handling for value types and string
        if (typeof(T).IsValueType || typeof(T).Equals(typeof(string)))
        {
            DataColumn dc = new DataColumn("Value");
            dt.Columns.Add(dc);
            foreach (T item in obj)
            {
                DataRow dr = dt.NewRow();
                dr[0] = item;
                dt.Rows.Add(dr);
            }
        }

        else//for reference types other than  string
        {

            //find all the public properties of this Type using reflection
            PropertyInfo[] piT = typeof(T).GetProperties();
            foreach (PropertyInfo pi in piT)
            {
                //create a datacolumn for each property
                if (pi.PropertyType.Name.Contains("Nullable"))
                {
                    DataColumn dc = new DataColumn(pi.Name, typeof(string));
                    dt.Columns.Add(dc);
                }
                else
                {
                    DataColumn dc = new DataColumn(pi.Name, pi.PropertyType);
                    dt.Columns.Add(dc);
                }
            }

            //now we iterate through all the items in current instance, take the corresponding values and add a new row in dt
            for (int item = 0; item < obj.Count; item++)
            {
                DataRow dr = dt.NewRow();

                for (int property = 0; property < dt.Columns.Count; property++)
                {
                    dr[property] = piT[property].GetValue(obj[item], null);
                }

                dt.Rows.Add(dr);
            }
        }

        return dt;
    }

    public static List<T> ToCollection<T>(this DataTable dt)
    {
        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)
            {
                string ptp = pc.PropertyType.Name;
               
                DataColumn d = dc.Find(c => c.ColumnName == pc.Name);
                if (d != null && item[pc.Name] != null && item[pc.Name] != DBNull.Value)
                {
                    string tt = d.DataType.Name;
                    switch (tt)
                    {
                        case "String":
                                pc.SetValue(cn, Convert.ToString(item[pc.Name]), null);
                            break;
                        case "Int16":
                            pc.SetValue(cn, Convert.ToInt16(item[pc.Name]), null);
                            break;
                        case "Int32":
                            pc.SetValue(cn, Convert.ToInt32(item[pc.Name]), null);
                            break;
                        case "Decimal":
                            pc.SetValue(cn, Convert.ToDecimal(item[pc.Name]), null);
                            break;
                        case "DateTime":
                            pc.SetValue(cn, Convert.ToDateTime(item[pc.Name]), null);
                            break;
                        default:
                            pc.SetValue(cn, Convert.ToString(item[pc.Name]), null);
                            break;
                    }
                   
                }
            }
            lst.Add(cn);
        }
        return lst;
    }

}

And here is how you can use these methods (in Ext.Net):

protected void btnExport_Click(object sender, DirectEventArgs e)
    {
        string json = e.ExtraParams["AllValues"];
        if (string.IsNullOrEmpty(json))
        {
            return;
        }
        List<dividend> lst = JSON.Deserialize<List<dividend>>(json);
        if (lst == null)
        {
            return;
        }
        try
        {
            DataTable dt = new DataTable();
            dt = lst.GetDataTable();

            string exportAs = "BankReturn" + CurrentDateString() + ".xlsx";


            List<string> columnNames = new List<string>() { "dividendyear", "declareid", "wno", "boid", "name", "bankcorr", "branch", "accno", "StatusName", "LastAction", "remarks" };
            Export(dt, columnNames, exportAs);

                    }
        catch (Exception ex)
        {
            X.Msg.Alert("Message", string.Format("{0}", ex.ToString())).Show();
            return;
        }
    }


protected void fuImport_FileSelected(object sender, DirectEventArgs e)
    {
        string json = e.ExtraParams["AllValues"];
        if (string.IsNullOrEmpty(json))
        {
            X.Msg.Alert("Message", string.Format("{0}", "Please search some data first to update on import")).Show();
            return;
        }

        List<dividend> lst = JSON.Deserialize<List<dividend>>(json);

        DataSet ds = ImportExcelXLS(fuImport.PostedFile, true);

        List<dividend> impLst = ds.Tables[0].ToCollection<dividend>();

        gpBoInfoStore.DataSource = lst;
        gpBoInfoStore.DataBind();
    }