C#의 대량 업데이트

C#의 대량 업데이트

를 삽입하기 , 하는 모든 하고 이 을 터베이많삽양데위해입기하를저, 삽모정이수다변목니습했집환을하로 변환하곤 .DataTable그런 다음 다음 다음을 통해 해당 목록을 데이터베이스에 삽입합니다.SqlBulkCopy.

된 목록을 수 생성된목보낼위
LiMyList
에 를 포함합니다.
인 그리고것그내대삽전달다니합술수에에 전달합니다.

InsertData(LiMyList, "MyTable");

에▁where디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#에서 데이터베이스로 업데이트하는 데 사용됩니다.

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

예:

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

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

이 을 처리하는 입니다.Table-Valued Parameter a 리고a.User-Defined Table Type데이터 테이블의 열로 유형을 설정하고 SQL 명령에 매개 변수로 해당 데이터 테이블을 전달하기만 하면 됩니다.

Procedure 키에 ), 삽입을 할 수 할 수 .Merge저장 프로시저 내에서 명령을 실행하여 해당하는 경우 업데이트와 삽입을 모두 처리합니다.

Microsoft에는 구문 참조와 병합 예제가 포함된 문서가 모두 있습니다.

piece의 을 .NET 조우경매변유다됩으로 입니다.SqlDbType.Structured업데이트할 레코드가 들어 있는 데이터 테이블에 해당 매개 변수의 값을 설정합니다.

이 방법은 명확성과 유지관리 용이성의 이점을 모두 제공합니다.성능 향상을 제공하는 방법(예: 임시 테이블에 테이블을 놓은 다음 테이블 위에서 반복)이 있을 수 있지만, .NET과 SQL이 테이블 전송 및 레코드 자체 업데이트를 처리할 수 있도록 하는 단순성이 그보다 더 중요하다고 생각합니다. K.I.S.

대량 업데이트:

1단계: 업데이트할 데이터와 기본 키를 목록에 넣습니다.

2단계: 아래와 같이 이 목록과 연결 문자열을 대량 업데이트 방법으로 전달

예:

         //Method for Bulk Update the Data
    public static void BulkUpdateData<T>(List<T> list, string connetionString)
    {

        DataTable dt = new DataTable("MyTable");
        dt = ConvertToDataTable(list);

        using (SqlConnection conn = new SqlConnection(connetionString))
        {
            using (SqlCommand command = new SqlCommand("CREATE TABLE 
                  #TmpTable([PrimaryKey],[ColumnToUpdate])", conn))
            {
                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();

                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                    {
                        bulkcopy.BulkCopyTimeout = 6600;
                        bulkcopy.DestinationTableName = "#TmpTable";
                        bulkcopy.WriteToServer(dt);
                        bulkcopy.Close();
                    }


                    command.CommandTimeout = 3000;
                    command.CommandText = "UPDATE P SET P.[ColumnToUpdate]= T.[ColumnToUpdate] FROM [TableName Where you want to update ] AS P INNER JOIN #TmpTable AS T ON P.[PrimaryKey] = T.[PrimaryKey] ;DROP TABLE #TmpTable;";
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    // Handle exception properly
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

3단계: 아래와 같이 ConvertToDataTable 메서드를 입력합니다.

예:

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

참고: 어디서나SquareBracket[]거기에 당신만의 가치를 두시오.

Nuget에서 사용할 수 있는 SqlBulkTools를 사용해 보십시오.

고지 사항:저는 이 도서관의 저자입니다.

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

'SomeColumn1' 및 'SomeColumn2'만 업데이트됩니다.자세한 예는 여기에서 확인할 수 있습니다.

임시 테이블에 새 값을 삽입한 다음 대상 테이블에 대해 병합을 수행합니다.

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

모든 데이터를 포함하는 쿼리를 작성할 수 있습니다.사용case이렇게 보일 수도 있습니다.

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)

TempTable 접근 방식을 택하겠습니다. 그러면 아무것도 잠그지 않습니다.그러나 논리가 프런트 엔드에만 있어야 하고 대량 복사를 사용해야 하는 경우에는 다음과 같은 무결성을 보장하기 위해 동일한 SqlTransaction에서 삭제/삽입 방식을 사용해 보겠습니다.

// ...

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

전체 답변, 고지 사항: 화살표 코드; 이것은 연구를 통해 작성된 내 것이며, SqlRapper에서 게시되었습니다.속성에 대한 사용자 지정 속성을 사용하여 키가 기본 키인지 여부를 확인합니다.네, 매우 복잡합니다.네, 슈퍼 재사용 가능합니다.네, 리팩터링이 필요합니다.네, 그것은 너겟 패키지입니다.아니요, github에 대한 설명서는 훌륭하지 않지만 존재합니다.모든 것에 효과가 있을까요?아마 아닐 것입니다.간단한 것에도 효과가 있을까요?오, 그래.

설정 후 사용하기가 얼마나 쉽습니까?

public class Log
{
    [PrimaryKey]
    public int? LogId { get; set; }
    public int ApplicationId { get; set; }
    [DefaultKey]
    public DateTime? Date { get; set; }
    public string Message { get; set; }
}


var logs = new List<Log>() { log1, log2 };
success = db.BulkUpdateData(logs);

작동 방식은 다음과 같습니다.

public class PrimaryKeyAttribute : Attribute
{
}

    private static bool IsPrimaryKey(object[] attributes)
    {
        bool skip = false;
        foreach (var attr in attributes)
        {
            if (attr.GetType() == typeof(PrimaryKeyAttribute))
            {
                skip = true;
            }
        }

        return skip;
    }

    private string GetSqlDataType(Type type, bool isPrimary = false)
    {
        var sqlType = new StringBuilder();
        var isNullable = false;
        if (Nullable.GetUnderlyingType(type) != null)
        {
            isNullable = true;
            type = Nullable.GetUnderlyingType(type);
        }
        switch (Type.GetTypeCode(type))
        {
            case TypeCode.String:
                isNullable = true;
                sqlType.Append("nvarchar(MAX)");
                break;
            case TypeCode.Int32:
            case TypeCode.Int64:
            case TypeCode.Int16:
                sqlType.Append("int");
                break;
            case TypeCode.Boolean:
                sqlType.Append("bit");
                break;
            case TypeCode.DateTime:
                sqlType.Append("datetime");
                break;
            case TypeCode.Decimal:
            case TypeCode.Double:
                sqlType.Append("decimal");
                break;
        }
        if (!isNullable || isPrimary)
        {
            sqlType.Append(" NOT NULL");
        }
        return sqlType.ToString();
    }

    /// <summary>
    /// SqlBulkCopy is allegedly protected from Sql Injection.
    /// Updates a list of simple sql objects that mock tables.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="rows">A list of rows to insert</param>
    /// <param name="tableName">a Table name if your class isn't your table name minus s.</param>
    /// <returns>bool success</returns>
    public bool BulkUpdateData<T>(List<T> rows, string tableName = null)
    {
        var template = rows.FirstOrDefault();
        string tn = tableName ?? template.GetType().Name + "s";
        int updated = 0;
        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("", con))
            {
                using (SqlBulkCopy sbc = new SqlBulkCopy(con))
                {
                    var dt = new DataTable();
                    var columns = template.GetType().GetProperties();;
                    var colNames = new List<string>();
                    string keyName = "";
                    var setStatement = new StringBuilder();
                    int rowNum = 0;
                    foreach (var row in rows)
                    {
                        dt.Rows.Add();
                        int colNum = 0;
                        foreach (var col in columns)
                        {
                            var attributes = row.GetType().GetProperty(col.Name).GetCustomAttributes(false);
                            bool isPrimary = IsPrimaryKey(attributes);
                            var value = row.GetType().GetProperty(col.Name).GetValue(row);

                            if (rowNum == 0)
                            {
                                colNames.Add($"{col.Name} {GetSqlDataType(col.PropertyType, isPrimary)}");
                                dt.Columns.Add(new DataColumn(col.Name, Nullable.GetUnderlyingType(col.PropertyType) ?? col.PropertyType));
                                if (!isPrimary)
                                {
                                    setStatement.Append($" ME.{col.Name} = T.{col.Name},");
                                }

                            }
                            if (isPrimary)
                            {
                                keyName = col.Name;
                                if (value == null)
                                {
                                    throw new Exception("Trying to update a row whose primary key is null; use insert instead.");
                                }
                            }
                            dt.Rows[rowNum][colNum] = value ?? DBNull.Value;
                            colNum++;
                        }
                        rowNum++;
                    }
                    setStatement.Length--;
                    try
                    {
                        con.Open();

                        command.CommandText = $"CREATE TABLE [dbo].[#TmpTable]({String.Join(",", colNames)})";
                        //command.CommandTimeout = CmdTimeOut;
                        command.ExecuteNonQuery();

                        sbc.DestinationTableName = "[dbo].[#TmpTable]";
                        sbc.BulkCopyTimeout = CmdTimeOut * 3;
                        sbc.WriteToServer(dt);
                        sbc.Close();

                        command.CommandTimeout = CmdTimeOut * 3;
                        command.CommandText = $"UPDATE ME SET {setStatement} FROM {tn} as ME INNER JOIN #TmpTable AS T on ME.{keyName} = T.{keyName}; DROP TABLE #TmpTable;";
                        updated = command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        if (con.State != ConnectionState.Closed)
                        {
                            sbc.Close();
                            con.Close();
                        }
                        //well logging to sql might not work... we could try... but no.
                        //So Lets write to a local file.
                        _logger.Log($"Failed to Bulk Update to Sql:  {rows.ToCSV()}", ex);
                        throw ex;
                    }
                }
            }
        }
        return (updated > 0) ? true : false;
    }

저는 이 일반적인 솔루션을 승인된 답변(템플릿 테이블을 만들고 대량 삽입으로 채운 다음 대상 테이블 업데이트)과 동일한 아이디어로 만들었습니다. 이 솔루션은 반사를 사용하여 속성을 읽으므로 다음과 같은 긴 UPDATE SET 명령을 작성할 필요가 없습니다.

public static class SqlHelper
{
    public static void BulkEdit<T>(SqlConnection connection, SqlCommand command, List<T> rowsToUpdate, string tableName, string idPropertyName)
    {
        var tempTableName = "#" + Guid.NewGuid().ToString().Replace("-", ""); // Use guid as table name to avoid collisions 

        // create temp table on database
        command.CommandText = $"SELECT * INTO {tempTableName} FROM {tableName} WHERE 1 = 0";
        command.ExecuteNonQuery();

        // fill temp table with updated data
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
        {
            bulkCopy.DestinationTableName = tempTableName;
            bulkCopy.WriteToServer(rowsToUpdate.AsDataReader()); // 
        }

        // merge temp table with original
        command.CommandText = $"MERGE {tableName} AS TargetTable " +
            $"USING {tempTableName} AS SourceTable " +
            $"ON TargetTable.{idPropertyName} = SourceTable.{idPropertyName} " +
            "WHEN MATCHED THEN UPDATE SET ";

        command.CommandText += string.Join(",", typeof(T)
            .GetProperties()
            .Where(x => x.Name != idPropertyName)
            .Select(x => $"TargetTable.{x.Name} = SourceTable.{x.Name}")
            .ToArray());
        command.CommandText += ";";
        command.ExecuteNonQuery();

        // delete temp table
        command.CommandText = $"DROP TABLE {tempTableName}";
        command.ExecuteNonQuery();
    }
}

이 솔루션은 반사를 사용하여 속성을 읽으므로 전체 업데이트 명령을 작성할 필요가 없습니다.

참고: AsDataReader()는 Microsoft의 ObjectDataReader의 확장 기능으로, https://github.com/microsoftarchive/msdn-code-gallery-community-m-r/tree/master/ObjectDataReader 에서 찾을 수 있습니다.

당신은 이 솔루션을 이렇게 사용할 수 있습니다.

using (var sqlConnection = new SqlConnection("connectionString"))
{
    using (var sqlCommand = new SqlCommand("", connection))
    {
         SqlHelper.BulkEdit<MyTable>(sqlConnection, sqlCommand, tableDataRows, $"[dbo].{nameof(MyTable)}", nameof(MyTable.ID));
    }
}

언급URL : https://stackoverflow.com/questions/20635796/bulk-update-in-c-sharp