在C＃中批量更新 [英] Bulk Update in C#

问题描述

有关在数据库中插入数据量巨大的，我用来收集所有的信息插入到一个列表，并转换列表中成数据表。然后我插入列表数据库通过 SqlBulkCopy的。

For inserting a huge amount of data in a database, I used to collect all the inserting information into a list and convert this list into a DataTable. I then insert that list to a database via SqlBulkCopy.

在哪里我把我生成列表 - LiMyList
包含我要插入到数据库
并将它传递给所有的大量数据信息我的批量插入操作

Where I send my generated list
LiMyList
which contain information of all bulk data which I want to insert to database
and pass it to my bulk insertion operation

InsertData(LiMyList, "MyTable");

其中， InsertData 是

 public static void InsertData<T>(List<T> list,string TableName)
        {
                DataTable dt = new DataTable("MyTable");
                clsBulkOperation blk = new clsBulkOperation();
                dt = ConvertToDataTable(list);
                ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString))
                {
                    bulkcopy.BulkCopyTimeout = 660;
                    bulkcopy.DestinationTableName = TableName;
                    bulkcopy.WriteToServer(dt);
                }
        }    

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;
        }

现在我想做的更新操作，有没有什么办法，作为由 SqlBulkCopy的的更新数据将数据库从C＃进行插入数据的.Net

Now I want to do an update operation, is there any way as for inserting data is done by SqlBulkCopy for Updating data to DataBase From C#.Net

推荐答案

我所做的就是之前执行批量插入从数据到一个临时表，然后使用一个命令或存储过程来更新相关的数据临时表与目标表。该临时表是一个额外的步骤，但你可以有批量插入和大规模的更新性能提升，如果行的数量大，比起按行更新数据行。

What I've done before is perform a bulk insert from the data into a temp table, and then use a command or stored procedure to update the data relating the temp table with the destination table. The temp table is an extra step, but you can have a performance gain with the bulk insert and massive update if the amount of rows is big, compared to updating the data row by row.

例如：

public static void UpdateData<T>(List<T> list,string TableName)
{
    DataTable dt = new DataTable("MyTable");
    clsBulkOperation blk = new clsBulkOperation();
    dt = ConvertToDataTable(list);
    ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString))
    {
        using (SqlCommand command = new SqlCommand("", conn))
        {
            try
            {
                conn.Open();

                //Creating temp table on database
                command.CommandText = "CREATE TABLE #TmpTable(...)";
                command.ExecuteNonQuery();

                //Bulk insert into temp table
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                {
                    bulkcopy.BulkCopyTimeout = 660;
                    bulkcopy.DestinationTableName = TableName;
                    bulkcopy.WriteToServer(dt);
                    bulkcopy.Close();
                }

                // Updating destination table, and dropping temp table
                command.CommandTimeout = 300;
                command.CommandText = "UPDATE T SET ... FROM " + TableName + " T INNER JOIN #TmpTable Temp ON ...; DROP TABLE #TmpTable;";
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                // Handle exception properly
            }
            finally
            {
                conn.Close();
            }
        }
    }
}

注意，一个单一连接被用于执行整个操作，以便能够使用所述临时表中的每一步，因为临时表的范围是每个连接

Notice that a single connection is used to perform the whole operation, in order to be able to use the temp table in each step, because the scope of the temp table is per connection.

这篇关于在C＃中批量更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！