DATASETがSystemをサポートしていないことを排除できる完璧なソリューションです.Nullableエラー
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