How to convert Data Table Into Generic List using Custom Attribute and Reflection

jabir eriyaden
12y
12.5k
0
1

Blog

Here we have two class customer and sales, if we need a common method for converting data table into a list of any type we can use refection.

    public class Customer
    {
        public string Name{set;get;}
        public string Address{set;get}
    }
    public class Sales
    {
        public string Product{set;get;}
        public int Quantity{set;get}
    }

Problem is that when the name of properties and column is diffrent what we do? in that case we can use a custome atribute that allows us to keep declarative information ie column name
so our custome atribute is like this

     public class DataTableColName : Attribute
    {
        string coulumnName = string.Empty;

        public string CoulumnName
        {
            get { return coulumnName; }
            set { coulumnName = value; }
        }
        public DataTableColName(string colName)
        {
            coulumnName = colName;
        }
    }

Above class will be

    public class Customer
    {
        [DataTableColName("CustomerName")]
        public string Name{set;get;}
        [DataTableColName("CustomerAddress")]
        public string Address{set;get}
    }
    public class Sales
    {
        [DataTableColName("ProductName")]
        public string Product{set;get;}
        [DataTableColName("ProductQty")]
        public int Quantity{set;get}
    }

So generic method for converting DataTble is

    public static List<T> GetList<T>(DataTable dt)
    {
        List<T> lst = new List<T>();
        foreach (DataRow dw in dt.Rows)
        {
            Type Tp = typeof(T);
            //create instance of the type
            T obj = Activator.CreateInstance<T>();
            //fetch all properties
            PropertyInfo[]pf= Tp.GetProperties();
            foreach (PropertyInfo pinfo in pf)
            {
                ///read the implimeted custome atribute for a property
                object[] colname = pinfo.GetCustomAttributes(typeof(DataTableColName), false);
                if (colname == null) continue;
                if (colname.Length == 0) continue;
                ///read column name from that atribute object
                string col = (colname[0] as DataTableColName).CoulumnName;
                if (!dt.Columns.Contains(col)) continue;
                if (dw[col] == null) continue;
                if (dw[col] == DBNull.Value) continue;
                ///set property value
                pinfo.SetValue(obj, dw[col], null);
            }
            lst.Add(obj);
        }
      return lst;
    }

In same way we can create a method for generating SqlParametr array for inserting and updating into table.

    public static SqlParameter[] GetSqlParametr<T>(T data)
    {
        List<SqlParameter> parms = new List<SqlParameter>();
        Type Tp = typeof(T);
        PropertyInfo[] pf = Tp.GetProperties();
        foreach (PropertyInfo pinfo in pf)
        {
            object[] colname = pinfo.GetCustomAttributes(typeof(DataTableColName), false);
            if (colname == null) continue;
            if (colname.Length == 0) continue;
            string col = (colname[0] as DataTableColName).CoulumnName;
            if (string.IsNullOrEmpty(col)) continue;

            object obj= pinfo.GetValue(data, null);
            SqlParameter sqlparam = new SqlParameter("@"+col,obj);
            parms.Add(sqlparam);
        }
      return parms.ToArray();
     }