List 转 DataTable
public DataTable ListToDataTable<T>(IEnumerable<T> list)
{//创建属性的集合
List<PropertyInfo> pList = new List<PropertyInfo>();
//获得反射的入口
Type type = typeof(T);
DataTable dt = new DataTable();
//把所有的public属性加入到集合 并添加DataTable的列
Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); });
foreach (var item in list)
{
//创建一个DataRow实例
DataRow row = dt.NewRow();
//给row 赋值
pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
//加入到DataTable
dt.Rows.Add(row);
}
return dt;
}
DataTable批量存入SQL SERVER 要求datatable字段、字段类型完全 一样
public void BulkToDB(DataTable dt, string TableName){
string databaseType = DBServerConfig.Settings.DbConn;
//string s = ConfigurationManager.GetSection("DBServer").ToString();
//QTGlobal.DBHelper.Connection
//DBHelper _dbHelper = QTGlobal.DBHelper;
//SqlConnection sqlConn = new SqlConnection(databaseType);
SqlBulkCopy bulkCopy = new SqlBulkCopy(_dbHelper.Connection as SqlConnection);
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = TableName;
bulkCopy.BatchSize = dt.Rows.Count;
try
{
//sqlConn.Open();
if (dt != null && dt.Rows.Count != 0)
{
string settltDate = dt.Rows[0][0].ToString();
string clientAccount = dt.Rows[0][1].ToString();
DeleteSameData(settltDate,clientAccount);
bulkCopy.WriteToServer(dt);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
//sqlConn.Close();
if (bulkCopy != null)
{
bulkCopy.Close();
}
}
}