DataTable帮助类

using Microsoft.CSharp;
using System;
using System.CodeDom.Compiler;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace Utility.Helpers
{
    public static class DataTableHelper
    {
        #region 集合转为对应的DataTable

        /// <summary>
        ///List转换为DataTable
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(this List<T> list) where T : new()
        {
            DataTable table = new DataTable();
            PropertyInfo[] ps = typeof(T).GetProperties();
            foreach (PropertyInfo p in ps)
            {
                if (!p.PropertyType.IsGenericType)
                {
                    table.Columns.Add(ConvertToTableColumnName(p.Name), p.PropertyType);

                }
                else
                {
                    Type GenericTypeDefinition = p.PropertyType.GetGenericTypeDefinition();
                    if (GenericTypeDefinition == typeof(Nullable<>))
                    {
                        table.Columns.Add(ConvertToTableColumnName(p.Name), Nullable.GetUnderlyingType(p.PropertyType));

                    }
                }
            }
            foreach (T obj in list)
            {
                DataRow row = table.NewRow();
                foreach (PropertyInfo p in ps)
                {

                    row[ConvertToTableColumnName(p.Name)] = p.GetValue(obj, null);


                }
                table.Rows.Add(row);

            }
            return table;

        }

        #endregion

        #region 扩展方法 DataTable转为对应实体的List集合

        public static List<T> ToList<T>(this DataTable table) where T : new()
        {
            List<T> list = new List<T>();
            PropertyInfo[] ps = typeof(T).GetProperties();
            foreach (DataRow row in table.Rows)
            {
                T obj = new T();
                foreach (DataColumn col in table.Columns)
                {
                    foreach (PropertyInfo p in ps)
                    {
                        if (p.Name == ConvertToEntityColumnName(col.ColumnName))
                        {
                            if (!p.PropertyType.IsGenericType)
                            {
                                p.SetValue(obj,
                                string.IsNullOrEmpty(row[col.ColumnName].ToString())
                                ? null
                                : Convert.ChangeType(row[col.ColumnName].ToString(), p.PropertyType), null);

                            }
                            else
                            {
                                if (p.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                                {
                                    p.SetValue(obj,
                                    string.IsNullOrEmpty(row[col.ColumnName].ToString())
                                    ? null
                                    : Convert.ChangeType(row[col.ColumnName],
                                    Nullable.GetUnderlyingType(p.PropertyType)), null);

                                }
                            }

                        }
                    }

                }
                list.Add(obj);
            }
            return list;
        }

        #endregion

        #region 列名转为对应实体类的属性名

        public static string ConvertToEntityColumnName(string name)
        {

            List<string> strList = name.Split('_').ToList();
            StringBuilder sb = new StringBuilder();
            foreach (string s2 in strList)
            {
                sb.Append(ReplaceString(s2));
            }
            return sb.ToString();
        }

        public static string ReplaceString(string s)
        {
            return Regex.Replace(s, (string)@"([A-Za-z]{1})([A-Za-z]*)", (MatchEvaluator)MathcEval);


        }

        private static string MathcEval(Match match)
        {
            return match.Groups[1].Value.ToUpper() + match.Groups[2].Value.ToLower();
        }

        #endregion
        public static IList ToGenericList(this DataTable dataTable)
        {
            Type GenericType = DataTableHelper.InitEntityType(dataTable);
            Type typeMaster = typeof(List<>);
            Type listType = typeMaster.MakeGenericType(GenericType);
            IList list = Activator.CreateInstance(listType) as IList;
            if (dataTable == null || dataTable.Rows.Count == 0)
                return list;
            var constructor = GenericType.GetConstructors(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)
            .OrderBy(c => c.GetParameters().Length).First();
            var parameters = constructor.GetParameters();
            var values = new object[parameters.Length];
            foreach (DataRow dr in dataTable.Rows)
            {
                int index = 0;
                foreach (ParameterInfo item in parameters)
                {
                    object itemValue = null;
                    if (dr[item.Name] != null && dr[item.Name] != DBNull.Value)
                    {
                        itemValue = Convert.ChangeType(dr[item.Name], item.ParameterType);
                    }
                    values[index++] = itemValue;
                }

                list.Add(constructor.Invoke(values));

            }
            return list;
        }
        #region 属性名转换为数据库对应的列名

        //属性名转换为数据库对应的列名
        public static string ConvertToTableColumnName(string name)
        {
            name = Regex.Replace(name, @"([A-Z]{1})([a-z]*)", MatchEval);
            name = name.TrimEnd('_');
            return name;
        }

        private static string MatchEval(Match match)
        {
            return match.Groups[1].Value.ToUpper() + match.Groups[2].Value.ToUpper() + "_";
        }

        private static Type InitEntityType(DataTable table)
        {
            CSharpCodeProvider p = new CSharpCodeProvider();
            CompilerParameters param = new CompilerParameters();
            string s = "namespace __ns" +
            "{" +
            "public class AAA";
            //"{ public AAA(string Name,int ID,string GuidType){this.Name=Name;this.ID=ID;this.GuidType=GuidType;}" +
            //" public string Name{ get;set; }" +
            //" public int ID{get;set;}" +
            //" public string GuidType{get;set;}" +
            s += "{ " +
            "public AAA(";
            for (int i = 0; i < table.Columns.Count; i++)
            {

                s += "string " + table.Columns[i].ColumnName + ",";

            }
            s = s.TrimEnd(',') + "){";
            for (int i = 0; i < table.Columns.Count; i++)
            {
                s += "this." + table.Columns[i].ColumnName + "=" + table.Columns[i].ColumnName + ";";
            }
            s += "}";
            for (int i = 0; i < table.Columns.Count; i++)
            {
                string columnName = table.Columns[i].ColumnName;
                s += " public string " + columnName + " {get;set;}\r\n";
            }
            s += " }}\r\n";

            List<object> objs = new List<object>();
            for (int i = 0; i < table.Columns.Count; i++)
            {
                objs.Add("");
            }

            CompilerResults rel = p.CompileAssemblyFromSource(param, s);
            Type t = rel.CompiledAssembly.GetType("__ns.AAA");

            object o = t.GetConstructors(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)
            .OrderBy(c => c.GetParameters().Length).First().Invoke(objs.ToArray());

            return o.GetType();
        }
        #endregion

        public static List<T> ConvertTomodel<T>(DataTable dt) where T : new()
        {
            //定义集合
            List<T> ts = new List<T>();
            //获得此模型的类型
            Type type = typeof(T);
            //定义一个临时变量
            string tempName = "";
            //遍历datatable中所有的数据行
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                //获得此模型的公共属性
                PropertyInfo[] propertys = t.GetType().GetProperties();
                //遍历所有属性
                foreach (PropertyInfo pi in propertys)
                {
                    //将属性名称赋值给临时变量
                    tempName = pi.Name;
                    //检查datatable是否包含此列
                    if (dt.Columns.Contains(tempName))
                    {
                        //判断此属性是否有setter
                        if (!pi.CanWrite) continue;//改属性不可写,直接跳出
                        //取值
                        object value = dr[tempName];
                        //如果非空,则赋给对象的属性
                        if (value != DBNull.Value)
                        {
                            pi.SetValue(t, value, null);
                        }
                    }

                }
                //对象添加到泛型集合中
                ts.Add(t);
            }
            return ts;

        }

    }
}

上一篇:XML 文件解析


下一篇:通过“访问多种数据库”的代码来学习多态!(.net2.0版)