SqlBulkCopy 帮助类 批量数据导入数据库

公共类库 代码：

    /// <summary>
    /// SqlBulkCopy 帮助类
    /// </summary>
    public static class SqlBulkCopyHelper
    {

        /// <summary>
        /// 本地认证评估表建表SQL
        /// </summary>
        private const string CreateTemplateSql = @"[Id] [int] NOT NULL,[DisabilityCardId] [nvarchar](50) NOT NULL,[PartId] [nvarchar](32) NULL,[ProvinceCode] [nvarchar](4) NULL,[DisabilityLevel] [int] NULL,[DisabilityTypes] [nvarchar](16) NULL,[VisualDisabilityLevel] [int] NULL";

        /// <summary>
        /// 本地认证评估更新SQL 这里采用的merge语言更新语句 你也可以使用 sql update 语句
        /// </summary>
        private const string UpdateSql = @"Merge into DisabilityAssessmentInfo AS T 
Using #TmpTable AS S 
ON T.Id = S.Id
WHEN MATCHED 
THEN UPDATE SET T.[DisabilityCardId]=S.[DisabilityCardId],T.[PartId]=S.[PartId],T.[ProvinceCode]=S.[ProvinceCode],T.[DisabilityLevel]=S.[DisabilityLevel],T.[DisabilityTypes]=S.[DisabilityTypes],T.[VisualDisabilityLevel]=S.[VisualDisabilityLevel];";

        /// <summary>
        /// SqlBulkCopy 批量更新数据
        /// </summary>
        /// <typeparam name="T">model</typeparam>
        /// <param name="temptableName">表名</param>
        /// <param name="list">数据源</param>
        /// <param name="crateTemplateSql">创建表名</param>
        /// <param name="updateSql">修改语句</param>
        public static void BulkUpdateData<T>(List<T> list,string temptableName, string crateTemplateSql, string updateSql)
        {
            var dataTable = ConvertToDataTable(list);
            ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
            configHelper ch = new configHelper();
            string connect_string = ch.readAppConfig().AppSettings.Settings["connectionstrings"].Value;
            using (var conn = new SqlConnection(connect_string))
            {
                using (var command = new SqlCommand("", conn))
                {
                    try
                    {
                        conn.Open();
                        //数据库并创建一个临时表来保存数据表的数据
                        command.CommandText = $"  CREATE TABLE #"+ temptableName+" ({crateTemplateSql})";
                        command.ExecuteNonQuery();

                        //使用SqlBulkCopy 加载数据到临时表中
                        using (var bulkCopy = new SqlBulkCopy(conn))
                        {
                            foreach (DataColumn dcPrepped in dataTable.Columns)
                            {
                                bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
                            }

                            bulkCopy.BulkCopyTimeout = 660;
                            bulkCopy.DestinationTableName = "#"+ temptableName;
                            bulkCopy.WriteToServer(dataTable);
                            bulkCopy.Close();
                        }

                        // 执行Command命令 使用临时表的数据去更新目标表中的数据  然后删除临时表
                        command.CommandTimeout = 300;
                        command.CommandText = updateSql;
                        command.ExecuteNonQuery();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        /// <summary>
        /// SqlBulkCopy 批量插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="tableName"></param>
        public static void BulkInsertData<T>(List<T> list, string tableName)
        {
            var dataTable = ConvertToDataTable(list);
            ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
            using (var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ServiceDataContext"].ConnectionString))
            {
                foreach (DataColumn dcPrepped in dataTable.Columns)
                {
                    bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
                }

                bulkCopy.BulkCopyTimeout = 660;
                bulkCopy.DestinationTableName = tableName;
                bulkCopy.WriteToServer(dataTable);
            }
        }

        public static DataTable ConvertToDataTable<T>(IList<T> data)
        {
            var properties = TypeDescriptor.GetProperties(typeof(T));
            var table = new DataTable();

            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);

            foreach (T item in data)
            {
                var row = table.NewRow();

                foreach (PropertyDescriptor prop in properties)
                {
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                }

                table.Rows.Add(row);
            }

            return table;
        }
    }

存在即更新 不存在 就插入

Merge into 表1 AS T 
            Using #表2 AS S 
            ON T.字段1 = S.字段1 
            WHEN MATCHED 
            THEN UPDATE SET 
            T.[字段1] = S.[字段1],
            T.[字段2] = S.[字段2]
            WHEN NOT MATCHED THEN
            INSERT (    [字段1],
                        [字段2],
                    )
            VALUES( S.[字段1],
                S.[字段2],
                  )