C #의 대량 업데이트

C #의 대량 업데이트

---

데이터베이스에 엄청난 양의 데이터를 삽입하기 위해 모든 삽입 정보를 목록에 수집하고이 목록을 DataTable. 그런 다음을 통해 해당 목록을 데이터베이스에 삽입합니다 SqlBulkCopy.

LiMyList
데이터베이스에 삽입하려는 모든 대량 데이터의 정보를 포함하는 생성 된 목록 을 보내고
대량 삽입 작업에 전달하는 곳

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

이제 업데이트 작업을하고 싶습니다. 데이터를 삽입하는 방법은 SqlBulkCopyC # .Net에서 DataBase로 데이터를 업데이트 하여 수행됩니다.

---

이전에 수행 한 작업은 데이터에서 임시 테이블로 대량 삽입을 수행 한 다음 명령 또는 저장 프로 시저를 사용하여 임시 테이블과 관련된 데이터를 대상 테이블과 업데이트하는 것입니다. 임시 테이블은 추가 단계이지만 행 단위로 데이터를 업데이트하는 것과 비교하여 행의 양이 큰 경우 대량 삽입 및 대량 업데이트로 성능을 향상시킬 수 있습니다.

예:

public static void UpdateData<T>(List<T> list,string TableName)
{
    DataTable dt = new DataTable("MyTable");
    dt = ConvertToDataTable(list);

    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 = "#TmpTable";
                    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();
            }
        }
    }
}

임시 테이블의 범위는 연결 당이므로 각 단계에서 임시 테이블을 사용할 수 있도록 전체 작업을 수행하는 데 단일 연결이 사용됩니다.

---

제 개인적인 경험상이 상황을 처리하는 가장 좋은 방법은 a Table-Valued Parameter및 User-Defined Table Type. 데이터 테이블의 열로 유형을 설정하고 SQL 명령의 매개 변수로 해당 데이터 테이블을 전달하십시오.

Within the Stored Procedure, you can either join directly on some unique key (if all rows you are updating exist), or - if you might run into a situation where you are having to do both updates and inserts - use the SQL Merge command within the stored procedure to handle both the updates and inserts as applicable.

Microsoft has both syntax reference and an article with examples for the Merge.

For the .NET piece, it's a simple matter of setting the parameter type as SqlDbType.Structured and setting the value of said-parameter to the Data Table that contains the records you want to update.

This method provides the benefit of both clarity and ease of maintenance. While there may be ways that offer performance improvements (such as dropping it into a temporary table then iterating over that table), I think they're outweighed by the simplicity of letting .NET and SQL handle transferring the table and updating the records itself. K.I.S.S.

---

Try out SqlBulkTools available on Nuget.

Disclaimer: I'm the author of this library.

var bulk = new BulkOperations();
var records = GetRecordsToUpdate();

using (TransactionScope trans = new TransactionScope())
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager
    .ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
    {
        bulk.Setup<MyTable>()
            .ForCollection(records)
            .WithTable("MyTable")
            .AddColumn(x => x.SomeColumn1)
            .AddColumn(x => x.SomeColumn2)
            .BulkUpdate()
            .MatchTargetOn(x => x.Identifier)
            .Commit(conn);
    }

    trans.Complete();
}  

Only 'SomeColumn1' and 'SomeColumn2' will be updated. More examples can be found here

---

I would insert new values in a temporary table and then do a merge against the destination table, something like this:

MERGE [DestTable] AS D 
USING #SourceTable S
    ON D.ID = S.ID
WHEN MATCHED THEN 
    UPDATE SET ...
WHEN NOT MATCHED 
THEN INSERT (...) 
VALUES (...);

---

Not sure I got the point you are going to archive... If your question is about quick replacing entire table content, than I would go for truncate (http://technet.microsoft.com/en-us/library/ms177570.aspx) and bulk insert of a new portion of data. But this approach will only work in case you have no foreign key constraints.

If you want to real update than look for the answer from Guillermo Gutiérrez.

---

You could try to build a query that contains all data. Use a case. It could look like this

update your_table
set some_column = case when id = 1 then 'value of 1'
                       when id = 5 then 'value of 5'
                       when id = 7 then 'value of 7'
                       when id = 9 then 'value of 9'
                  end
where id in (1,5,7,9)

---

I'd go for a TempTable approach because that way you aren't locking anything. But if your logic needs to be only in the front end and you need to use bulk copy, I'd try a Delete/Insert approach but in the same SqlTransaction to ensure integrity which would be something like this:

// ...

dt = ConvertToDataTable(list);

using (SqlConnection cnx = new SqlConnection(myConnectionString))
{
    using (SqlTranscation tran = cnx.BeginTransaction())
    {
        DeleteData(cnx, tran, list);

        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(cnx, SqlBulkCopyOptions.Default, tran))
        {
            bulkcopy.BulkCopyTimeout = 660;
            bulkcopy.DestinationTableName = TabelName;
            bulkcopy.WriteToServer(dt);
        }

        tran.Commit();
    }
}

ReferenceURL : https://stackoverflow.com/questions/20635796/bulk-update-in-c-sharp