SqlBulkCopy 同时插入与更新

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"></typeparam>
        /// <param name="list"></param>
        /// <param name="crateTemplateSql"></param>
        /// <param name="updateSql"></param>
        public static void BulkUpdateData<T>(List<T> list, string crateTemplateSql, string updateSql)
        {
            DataTable dataTable = ConvertToDataTable(list);
            ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString))
            {
                using (SqlCommand command = new SqlCommand("", conn))
                {
                    try
                    {
                        conn.Open();
                        //数据库并创建一个临时表来保存数据表的数据
                        command.CommandText = string.Format(@"  CREATE TABLE #TmpTable ({0})", crateTemplateSql);
                        command.ExecuteNonQuery();

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

                            bulkCopy.BulkCopyTimeout = 660;
                            bulkCopy.DestinationTableName = "#TmpTable";
                            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)
        {
            DataTable dataTable = ConvertToDataTable(list);
            ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["MSSQL"].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)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();

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

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

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

                table.Rows.Add(row);
            }

            return table;
        }

        public static void BulkInsertData(DataTable dataTable, string tableName)
        {
            //DataTable dataTable = ConvertToDataTable(list);
            ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["MSSQL"].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 int BulkUpdateData(DataTable dataTable, string crateTemplateSql, string updateSql)
        {
            //DataTable dataTable = ConvertToDataTable(list);
            //ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString))
            {
                using (SqlCommand command = new SqlCommand("", conn))
                {
                    try
                    {
                        conn.Open();
                        //数据库并创建一个临时表来保存数据表的数据
                        command.CommandText = string.Format(@"  CREATE TABLE #TmpTable ({0})", crateTemplateSql);
                        command.ExecuteNonQuery();

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

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

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