sqlserver数据库批量新增修改类

MSSql Server 数据库批量操作

需要引用的命名空间

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;

类源码：

    /// <summary>
    /// sqlserver数据库批量新增修改类
    /// </summary>
    public static class SqlBulkHelper
    {
        #region 数据库连接字符串
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static readonly string ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        #endregion

        #region SqlBulkCopy方式批量新增数据
        /// <summary>
        /// SqlBulkCopy方式批量新增数据
        /// </summary>
        /// <typeparam name="T">对象</typeparam>
        /// <param name="modelList">实体类集合</param>
        /// <param name="destinationTableName">目标表明</param>
        /// <param name="removeColumns">移除的字段列集合</param>
        /// <param name="bulkCopyTimeout">超时时间</param>
        public static void BulkCopy<T>(List<T> modelList, string destinationTableName, List<string> removeColumns = null, int? bulkCopyTimeout = null)
        {
            if (string.IsNullOrEmpty(destinationTableName))
            {
                destinationTableName = typeof(T).Name;
            }
            var dt = ListToDataTable(modelList);
            if (removeColumns != null && removeColumns.Count > 0)
            {
                foreach (var item in removeColumns)
                {
                    dt.Columns.Remove(item);
                }
            }
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                using (var sbc = new SqlBulkCopy(conn))
                {
                    sbc.BatchSize = modelList.Count;
                    sbc.DestinationTableName = destinationTableName;
                    sbc.BulkCopyTimeout = bulkCopyTimeout ?? 300;
                    conn.Open();
                    sbc.WriteToServer(dt);
                }
            }
        }
        #endregion

        #region SqlBulkCopy方式批量修改数据
        /// <summary>
        /// SqlBulkCopy方式批量修改数据
        /// </summary>
        /// <typeparam name="T">对象</typeparam>
        /// <param name="modelList">实体类集合</param>
        /// <param name="onRelations">关联字段</param>
        /// <param name="destinationTableName">目标表名</param>
        /// <param name="removeColumns">移除的字段列集合</param>
        /// <param name="UpdateColumns">更新的字段集合，不填则全部</param>
        public static void BatchUpdate<T>(List<T> modelList, string onRelations, string destinationTableName = null, List<string> removeColumns = null, List<string> UpdateColumns = null)
        {
            if (string.IsNullOrEmpty(destinationTableName))
                destinationTableName = typeof(T).Name.Replace("EN", "");
            var dt = ListToDataTable(modelList);
            if (removeColumns != null && removeColumns.Count > 0)
            {
                foreach (var item in removeColumns)
                {
                    dt.Columns.Remove(item);
                }
            }
            var sbUpdateColumns = new StringBuilder();
            var columnsIndex = 0;
            //只更新某字段
            if (UpdateColumns != null && UpdateColumns.Count > 0)
            {
                foreach (var updateColumn in UpdateColumns)
                {
                    if (columnsIndex > 0)
                    {
                        sbUpdateColumns.Append(", ");
                    }
                    sbUpdateColumns.AppendFormat("T.{0} = Tmp.{0}", updateColumn);
                    columnsIndex++;
                }
            }
            else
            {
                //更新全部字段
                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    var colname = dt.Columns[i];
                    if (colname.ColumnName != onRelations)
                    {
                        if (columnsIndex > 0)
                        {
                            sbUpdateColumns.Append(", ");
                        }
                        sbUpdateColumns.AppendFormat("T.{0} = Tmp.{0}", colname.ColumnName);
                        columnsIndex++;
                    }
                }
            }


            string sbOnRelation = string.Format("T.{0} = Tmp.{1}", onRelations, onRelations);
            var tempTableName = @"#Temp" + destinationTableName;
            var createtempsql = string.Format("select * into {0} from {1} where 1=2", tempTableName, destinationTableName);
            var updatesql = string.Format("UPDATE T SET {0} FROM {1} T INNER JOIN {2} Tmp ON {3}; DROP TABLE {2};", sbUpdateColumns.ToString(), destinationTableName, tempTableName, sbOnRelation.ToString());

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                using (SqlCommand command = new SqlCommand("", conn))
                {
                    try
                    {
                        conn.Open();
                        command.CommandText = createtempsql;
                        command.ExecuteNonQuery();
                        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                        {
                            bulkcopy.BulkCopyTimeout = 300;
                            bulkcopy.DestinationTableName = tempTableName;
                            bulkcopy.WriteToServer(dt);
                            bulkcopy.Close();
                        }

                        command.CommandTimeout = 300;
                        command.CommandText = updatesql;
                        int countQuery = command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("BatchUpdate:{0}表失败,原因:{1}", destinationTableName, ex.Message + ex.StackTrace);
                        // Handle exception properly
                    }
                    finally
                    {
                        //stopwatch.Stop();
                        //Console.WriteLine("更新耗时:{0}",stopwatch.ElapsedMilliseconds);
                        //list.Clear();
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 
        /// <summary>
        /// //list转化为table
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entitys"></param>
        /// <returns></returns>
        public static DataTable ListToDataTable<T>(List<T> entitys)
        {

            //检查实体集合不能为空
            if (entitys == null || entitys.Count < 1)
            {
                return new DataTable();
            }

            //取出第一个实体的所有Propertie
            Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();

            //创建传入对象名称的列
            //生成DataTable的structure
            //生产代码中，应将生成的DataTable结构Cache起来，此处略
            DataTable dt = new DataTable("dt");
            for (int i = 0; i < entityProperties.Length; i++)
            {
                dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
                //dt.Columns.Add(entityProperties[i].Name);
            }

            //将所有entity添加到DataTable中
            foreach (object entity in entitys)
            {
                //检查所有的的实体都为同一类型
                if (entity.GetType() != entityType)
                {
                    throw new Exception("要转换的集合元素类型不一致");
                }
                object[] entityValues = new object[entityProperties.Length];
                for (int i = 0; i < entityProperties.Length; i++)
                {
                    entityValues[i] = entityProperties[i].GetValue(entity, null);
                }
                dt.Rows.Add(entityValues);
            }
            return dt;
        }
        #endregion
    }