SQL Server批量插入批量更新工具类

思路：

批量插入，使用SqlBulkCopy

批量更新，定义临时表，使用SqlBulkCopy批量插入临时表，用SQL语句更新临时表数据到实际表

定义：

/// <summary>
        /// 批量插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="lst"></param>
        /// <param name="tableName">数据库表名，不传表示取泛型T的类型名称</param>
        /// <param name="columnNames">字段名数组，不传表示取泛型T的所有属性名称</param>
        /// <returns></returns>
        public bool SqlBulkCopy<T>(IList<T> lst, string tableName = "", string[] columnNames = null) {}
/// <summary>
        /// 批量插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="lst"></param>
        /// <param name="tableName">数据库表名，空或null表示取泛型T的类型名称</param>
        /// <param name="columnList">字段映射字典</param>
        /// <returns></returns>
        public bool SqlBulkCopy<T>(IList<T> lst, string tableName, Dictionary<string, string> columnList) {}
/// <summary>
        /// 批量更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="lst"></param>
        /// <param name="tableName">临时表名</param>
        /// <param name="fieldStr">临时表字段定义</param>
        /// <param name="updateStr">UPDATE SQL语句</param>
        /// <param name="columnNames">临时表批量插入字段名数组，不传表示取泛型T的所有属性名称</param>
        /// <returns></returns>
        public bool BatchUpdate<T>(IList<T> lst, string tableName, string fieldStr, string updateStr, string[] columnNames = null) {}

调用

DbHelper dbHelper = new DbHelper("链接字符串");

            dbHelper.SqlBulkCopy(lst);

            dbHelper.SqlBulkCopy(lst, "DbModelName");

            dbHelper.SqlBulkCopy(lst, "DbModelName", new string[] { "Name", "Contact" });

            Dictionary<string, string> columnList = new Dictionary<string, string>();
            columnList.Add("ID", "id");
            columnList.Add("Name", "name");
            columnList.Add("Contact", "contact");
            dbHelper.SqlBulkCopy(lst, "DbModelName", columnList);

            dbHelper.BatchUpdate(lst, "#tmp", "ID int,Contact varchar(100)"
                    , "UPDATE a SET a.contact=b.Contact FROM DbModelName a INNER JOIN #tmp b ON a.id=b.ID WHERE b.ID IS NOT NULL");

源码下载