SqlBuckCopy使用

晨风 2021-07-20 PM 408℃ 0条

public static class DataTableUtils
    {
        /// <summary>    
        /// 转化一个DataTable    
        /// </summary>    
        /// <typeparam name="T"></typeparam>    
        /// <param name="list"></param>    
        /// <returns></returns>    
        public static DataTable ToDataTable<T>(this List<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 dtReturn = new DataTable();
            // column names
            PropertyInfo[] oProps = null;
            // Could add a check to verify that there is an element 0
            foreach (T rec in list)
            {
                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);
        }

/// <summary>    
        /// DataTable 转换为List集合    
        /// </summary>    
        /// <typeparam name="TResult">类型</typeparam>    
        /// <param name="dt">DataTable</param>    
        /// <returns></returns>    
        public static List<T> ToList<T>(this DataTable dt) where T : class, new()
        {
            //创建一个属性的列表    
            List<PropertyInfo> prlist = new List<PropertyInfo>();
            //获取TResult的类型实例  反射的入口

Type t = typeof(T);

//获得TResult 的所有的Public 属性 并找出TResult属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表     
            Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });

//创建返回的集合

List<T> oblist = new List<T>();

foreach (DataRow row in dt.Rows)
            {
                //创建TResult的实例    
                T ob = new T();
                //找到对应的数据  并赋值    
                prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
                //放入到返回的集合中.    
                oblist.Add(ob);
            }
            return oblist;
        }

/// <summary>    
        /// 将集合类转换成DataTable    
        /// </summary>    
        /// <param name="list">集合</param>    
        /// <returns></returns>    
        public static DataTable ToDataTableTow(IList list)
        {
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();

foreach (PropertyInfo pi in propertys)
                {
                    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;
        }

/// <summary>    
        /// 将泛型集合类转换成DataTable    
        /// </summary>    
        /// <typeparam name="T">集合项类型</typeparam>    
        /// <param name="list">集合</param>    
        /// <param name="propertyName">需要返回的列的列名</param>    
        /// <returns>数据集(表)</returns>    
        public static DataTable ToDataTable<T>(IList<T> list, params string[] propertyName)
        {
            List<string> propertyNameList = new List<string>();
            if (propertyName != null)
                propertyNameList.AddRange(propertyName);
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (propertyNameList.Count == 0)
                    {
                        result.Columns.Add(pi.Name, pi.PropertyType);
                    }
                    else
                    {
                        if (propertyNameList.Contains(pi.Name))
                            result.Columns.Add(pi.Name, pi.PropertyType);
                    }
                }

for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        if (propertyNameList.Count == 0)
                        {
                            object obj = pi.GetValue(list[i], null);
                            tempList.Add(obj);
                        }
                        else
                        {
                            if (propertyNameList.Contains(pi.Name))
                            {
                                object obj = pi.GetValue(list[i], null);
                                tempList.Add(obj);
                            }
                        }
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }
    }
      private static bool SQLBulkCopyColumns(DataTable dource,string tableName,int batchSize = 10000)
        {
            bool issuccess = false;
            if (!string.IsNullOrEmpty(tableName) && dource != null && dource.Rows.Count > 0)
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(GetConnectionString(), SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlBulkCopy.BatchSize = batchSize;
                        sqlBulkCopy.DestinationTableName = tableName;
                        for (int i = 0; i < dource.Columns.Count; i++)
                        {
                            sqlBulkCopy.ColumnMappings.Add(dource.Columns[i].ColumnName, dource.Columns[i].ColumnName);
                        }
                        sqlBulkCopy.WriteToServer(dource);
                        issuccess = true;
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }
                }
            }
            return issuccess;
        }

标签: C#, SqlServer

非特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：http://blog.chenfengly.com/index.php/archives/8/

上一篇 Vue+ElementUI-Tree组件的一些问题

下一篇 Cesium自定义圆柱体Geomerty表面贴图

SqlBuckCopy使用

评论啦~

栏目分类

标签云

SqlBuckCopy使用

 评论啦~

 栏目分类

标签云

评论啦~

栏目分类

标签云