(转)完美解决方案,可排除DATASET不支持System.Nullable错误

原创 于 2011-12-27 15:11:34 发布 · 492 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#c# #linq #name

本文介绍了一个使用LINQ to DataTable的方法，解决了SQL数据库中日期字段为NULL值时无法插入到DataRow中的问题。通过使用dataTable.LoadDataRow(array,true)方法，可以将为NULL的值写入到值类型的数据单元中，从而避免了使用通用DataAdapter.Fill方法时可能出现的错误。 

using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Reflection;
using System.Linq;
using System.Xml.Linq;

namespace UserFunction
{
    /// <summary>
    /// Summary description for LinqToDataTable
    /// </summary>
    static public  class LinqToDataTable
    {
        static public  DataTable ToDataTable<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn)
        {

            DataTable dtReturn = new DataTable();

            // column names

            PropertyInfo[] oProps = null;

            // Could add a check to verify that there is an element 0

            foreach (T rec in varlist)
            {

                // Use reflection to get property names, to create table, Only first time, others will follow

                if (oProps == null)
                {

                    oProps = ((Type)rec.GetType()).GetProperties();

                    foreach (PropertyInfo pi in oProps)
                    {

                        Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                        {

                            colType = colType.GetGenericArguments()[0];

                        }

                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));

                    }

                }

                DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps)
                {

                    dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);

                }

                dtReturn.Rows.Add(dr);

            }

            return (dtReturn);

        }

        public delegate object[] CreateRowDelegate<T>(T t);
    }
}

/*
 * sample:
 * var query = from ....;
 * DataTable dt = query.ToDataTable(rec => new object[] { query }); 
 * 
*/

 公司有个新项目,使用sqlite作为数据库,其中有一个非常不爽的地方,如果当日期列出现NULL值时,通用DataAdepter.Fill方法填充到DateTable中时会发生错误,后来没办法只好使用dataReader循环读取数据来代替Fill,但又出现一个问题,如果保留表中的数据类型,当日期字段为空时无法插入到DataRow中,最后整个DataTable全使用string类型,这个问题一直困扰了很久,今日在网上无意中得到这样一篇代码,解决了这个问题,关键语句是 dataTable.LoadDataRow(array, true);用此方式可以将为NULL的字写入到值类型的数据单元中.

 
 
方法类:
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
//==
using System.Collections;
using System.Reflection;
using System.Collections.Generic;
/// <summary>
///tool 的摘要说明
/// </summary>
public class tool
{
    //public tool()
    //{
    //    //
    //    //TODO: 在此处添加构造函数逻辑
    //    //
    //}
    /// <summary>
    /// 将泛型集合类转换成DataTable
    /// </summary>
    /// <typeparam name="T">集合项类型</typeparam>
    /// <param name="list">集合</param>
    /// <returns>数据集(表)</returns>
    ///
    //====表中无数据时使用:
    public static DataTable nullListToDataTable(IList list)
    {
        DataTable result = new DataTable();
        object temp;
        if (list.Count > 0)
        {
            PropertyInfo[] propertys = list[0].GetType().GetProperties();
            foreach (PropertyInfo pi in propertys)
            {
                //if (!(pi.Name.GetType() is System.Nullable))
                if (pi != null)
                {
                    //pi = (PropertyInfo)temp;  
                    result.Columns.Add(pi.Name, pi.PropertyType);
                }
            }
            for (int i = 0; i < list.Count; i++)
            {
                ArrayList tempList = new ArrayList();
                foreach (PropertyInfo pi in propertys)
                {
                    object obj = pi.GetValue(list[i], null);
                    tempList.Add(obj);
                }
                object[] array = tempList.ToArray();
                result.LoadDataRow(array, true);
            }
        }
        return result;
    }
    //====表中有数据时使用:
    public static DataTable noNullListToDataTable<T>(IList<T> list)
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable(typeof(T).Name);
        DataColumn column;
        DataRow row;
        System.Reflection.PropertyInfo[] myPropertyInfo =
            typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
        foreach (T t in list)
        {
            if (t == null) continue;
            row = dt.NewRow();
            for (int i = 0, j = myPropertyInfo.Length; i < j; i++)
            {
                System.Reflection.PropertyInfo pi = myPropertyInfo[i];
                String name = pi.Name;
                if (dt.Columns[name] == null)
                {
                    if (pi.PropertyType.UnderlyingSystemType.ToString() == "System.Nullable`1[System.Int32]")
                    {
                        column = new DataColumn(name, typeof(Int32));
                        dt.Columns.Add(column);
                        //row[name] = pi.GetValue(t, new object[] {i});//PropertyInfo.GetValue(object,object[])
                        if (pi.GetValue(t, null) != null)
                            row[name] = pi.GetValue(t, null);
                        else
                            row[name] = System.DBNull.Value;
                    }
                    else
                    {
                        column = new DataColumn(name, pi.PropertyType);
                        dt.Columns.Add(column);
                        row[name] = pi.GetValue(t, null);
                    }
                }
            }
            dt.Rows.Add(row);
        }
        ds.Tables.Add(dt);
        return ds.Tables[0];
    }
    //表中有数据或无数据时使用,可排除DATASET不支持System.Nullable错误
    public static DataTable ConvertToDataSet<T>(IList<T> list)
        {
            if (list == null || list.Count <= 0)
            //return null;
            {
                DataTable result = new DataTable();
                object temp;
                if (list.Count > 0)
                {
                    PropertyInfo[] propertys = list[0].GetType().GetProperties();
                    foreach (PropertyInfo pi in propertys)
                    {
                        //if (!(pi.Name.GetType() is System.Nullable))
                        //if (pi!=null)
                        {
                            //pi = (PropertyInfo)temp;  
                            result.Columns.Add(pi.Name, pi.PropertyType);
                        }
                    }
                    for (int i = 0; i < list.Count; i++)
                    {
                        ArrayList tempList = new ArrayList();
                        foreach (PropertyInfo pi in propertys)
                        {
                            object obj = pi.GetValue(list[i], null);
                            tempList.Add(obj);
                        }
                        object[] array = tempList.ToArray();
                        result.LoadDataRow(array, true);
                    }
                }
                return result;
            }
            else
            {
                DataSet ds = new DataSet();
                DataTable dt = new DataTable(typeof(T).Name);
                DataColumn column;
                DataRow row;
                System.Reflection.PropertyInfo[] myPropertyInfo =
                    typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
                foreach (T t in list)
                {
                    if (t == null) continue;
                    row = dt.NewRow();
                    for (int i = 0, j = myPropertyInfo.Length; i < j; i++)
                    {
                        System.Reflection.PropertyInfo pi = myPropertyInfo[i];
                        String name = pi.Name;
                        if (dt.Columns[name] == null)
                        {
                            if (pi.PropertyType.UnderlyingSystemType.ToString() == "System.Nullable`1[System.Int32]")
                            {
                                column = new DataColumn(name, typeof(Int32));
                                dt.Columns.Add(column);
                                //row[name] = pi.GetValue(t, new object[] {i});//PropertyInfo.GetValue(object,object[])
                                if (pi.GetValue(t, null) != null)
                                    row[name] = pi.GetValue(t, null);
                                else
                                    row[name] = System.DBNull.Value;
                            }
                            else
                            {
                                column = new DataColumn(name, pi.PropertyType);
                                dt.Columns.Add(column);
                                row[name] = pi.GetValue(t, null);
                            }
                        }
                    }
                    dt.Rows.Add(row);
                }
                ds.Tables.Add(dt);
                return ds.Tables[0];
            }
        }
}

===============================================================================================

调用:
    protected void Bind()
    {
        //var s = (from p in _7ctourDct.city
        //         orderby p.cityName descending
        //         select p);
        //List<city> c = new List<city>();
        //c.AddRange(s.ToList());
        //GridView1.DataSource = c;
        //GridView1.DataBind();
        var s = from subsectionRoute in _7ctourDct.subsectionRoute
                select subsectionRoute;
        DataTable dt = new DataTable();
        //var s = from subsectionRoute in _7ctourDct.subsectionRoute select subsectionRoute;
        //IList list = s.ToList();
        //dt = ToDataTable(list);
        //or
        //dt = tool.ToDataTable(s.ToList());
        if (s.ToList().Count >= 1)
        {
            dt = tool.noNullListToDataTable(s.ToList());
        }
        else
        {
            dt = tool.nullListToDataTable(s.ToList());
        }
        DataRow dr;
        //如果GRIDVIEW是自定义绑定字段,则必须为DT增加列,否则报错:数据源不包含字段
        dt.Columns.Add(new DataColumn("subsectionRouteId", typeof(Int32)));
        dt.Columns.Add(new DataColumn("loginId", typeof(Int32)));
        dt.Columns.Add(new DataColumn("dayOrder", typeof(Int32)));
        dt.Columns.Add(new DataColumn("placeOrder", typeof(Int32)));
        dt.Columns.Add(new DataColumn("placeInDayOrder", typeof(String)));
        dt.Columns.Add(new DataColumn("cityId", typeof(Int32)));
        dt.Columns.Add(new DataColumn("actionTime", typeof(Int32)));
        dt.Columns.Add(new DataColumn("routeDetail", typeof(String)));
        dt.Columns.Add(new DataColumn("trafficPriceId", typeof(Int32)));
        dt.Columns.Add(new DataColumn("trafficDetail", typeof(Int32)));
        dt.Columns.Add(new DataColumn("trafficRemark", typeof(Int32)));
        dt.Columns.Add(new DataColumn("eateryPriceId", typeof(Int32)));
        dt.Columns.Add(new DataColumn("hotelPriceId", typeof(String)));
        dt.Columns.Add(new DataColumn("remark", typeof(Int32)));
        dt.Columns.Add(new DataColumn("basicGroupId", typeof(Int32)));
        dt.Columns.Add(new DataColumn("trafficType", typeof(Int32)));
        dt.Columns.Add(new DataColumn("trafficId", typeof(String)));
        for (int nIndex = 1; nIndex <= 8 - s.ToList().Count; nIndex++)
        {
            dr = dt.NewRow();
            dt.Rows.Add(dr);
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
 

 

 

 

        #region ListToDataTable
        /// <summary>
        /// ListToDataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(this IEnumerable<T> list)
        {
            List<PropertyInfo> pList = new List<PropertyInfo>();
            Type type = typeof(T);
            DataTable dt = new DataTable();
            Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); });
            foreach (var item in list)
            {
                DataRow row = dt.NewRow();
                pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
                dt.Rows.Add(row);
            }
            return dt;
        }
        #endregion
 
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值