从实体类到datatable，然后批量插入数据库

实体类转换成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 的序列化和反序列化