实体类转换成DataTable
/// 实体类转换成DataTable
/// 调用示例:DataTable dt= FillDataTable(Entitylist.ToList());
/// </summary>
/// <param name="modelList">实体类列表</param>
/// <returns></returns>
public DataTable FillDataTable<T>(List<T> modelList)
{
if (modelList == null || modelList.Count == 0)
{
return null;
}
DataTable dt = CreateData(modelList[0]);//创建表结构
foreach (T model in modelList)
{
DataRow dataRow = dt.NewRow();
foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
{
if (propertyInfo.PropertyType.ToString() != "System.DateTime")
{
dataRow[propertyInfo.Name] = propertyInfo.GetValue(model, null);
}
else
{
if (propertyInfo.Name == "C_CreateTime")
{
dataRow[propertyInfo.Name] = DateTime.Now;
}
else
{
dataRow[propertyInfo.Name] = Convert.ToDateTime("1900-1-1");
}
}
}
dt.Rows.Add(dataRow);
}
return dt;
}
/// <summary>
/// 根据实体类得到表结构
/// </summary>
/// <param name="model">实体类</param>
/// <returns></returns>
private DataTable CreateData<T>(T model)
{
DataTable dataTable = new DataTable(typeof(T).Name);
foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
{
//些字段为oracle中的Timesstarmp类型
if (propertyInfo.Name == "ModifyDate" || propertyInfo.Name == "C_DeleteTime" || propertyInfo.Name == "AuditDate" || propertyInfo.Name == "C_CreateTime" || propertyInfo.Name == "C_ModifyTime" )
{
dataTable.Columns.Add(new DataColumn(propertyInfo.Name, typeof(DateTime)));
}
else
{
string na = propertyInfo.Name;
Type we = propertyInfo.PropertyType;
dataTable.Columns.Add(new DataColumn(propertyInfo.Name, propertyInfo.PropertyType));
}
}
return dataTable;
}
将datatable插入到数据库
public bool InsertDataTable(DataTable dt,string surfaceName)
{
try
{
Stopwatch sw = new Stopwatch();
sw.Start();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
bulkCopy.DestinationTableName = surfaceName;
bulkCopy.BatchSize = dt.Rows.Count;
conn.Open();
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt);
}
}
sw.Stop();
}
catch (Exception ex)
{
return false;
}
return true;
}
从数据库查询数据
/// 返回DataSet
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet( string sql)
{
Dictionary<string, string> keyValuePairs = new Dictionary<string, string>();
DataSet data = new DataSet();
//第二步
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
//强大的SqlDataAdapter
System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter(sql, ConnectionString);
adapter.Fill(data);
conn.Close();
return data;
}
注释:如果想要datable 和 list 相互转换 可以通过json 的序列化和反序列化