Untitled
unknown
csharp
a year ago
8.5 kB
8
Indexable
using System.Data.SqlClient; string connectionString = "connectionstring here"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // get all invalid content versions, [current] might as well be called "edited" and "edited" and "published" should not be on the same version List<ContentVersionDTO> affectedVersions = new List<ContentVersionDTO>(); string selectSql = @" select cv.* from umbracoContentVersion cv, umbracoDocumentVersion dv, umbracoNode n where cv.id = dv.id and cv.[current]= 1 and published = 1 and n.id = nodeid and n.trashed = 0 "; using (SqlCommand selectCommand = new SqlCommand(selectSql, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { affectedVersions.Add(new ContentVersionDTO() { VersionId = (int)reader["id"], NodeId = (int)reader["nodeId"], UserId = (int)reader["userId"], Text = (string)reader["text"], PreventCleanup = (bool)reader["preventCleanup"] }); } } } foreach (ContentVersionDTO version in affectedVersions) { using (var transaction = connection.BeginTransaction()) { try { FIXIT.FixContent(version, connection, transaction); transaction.Commit(); Console.WriteLine("Fixed " + version.NodeId + " " + affectedVersions.IndexOf(version) + " out of " + affectedVersions.Count); } catch (Exception ex) { transaction.Rollback(); Console.WriteLine("Error processing " + version.NodeId + ": " + ex.Message); } } } // cleanup document versions string cleanupSQL = @"insert into umbracoDocumentVersion select cv.id, (select max(templateId) from umbracoDocumentVersion where id in (select id from umbracoContentVersion a where a.nodeId = cv.nodeId)) as templateId, 0 as published from umbracoDocument d, umbracoContentVersion cv left join umbracoDocumentVersion dv on dv.id = cv.id where [current]= 1 and d.nodeId = cv.nodeId and dv.id is null"; using (SqlCommand updateCommand = new SqlCommand(cleanupSQL, connection)) { int rowsAffected = updateCommand.ExecuteNonQuery(); Console.WriteLine($"Rows affected: {rowsAffected}"); } } public class FIXIT { public static void FixContent(ContentVersionDTO cDto, SqlConnection conn, SqlTransaction transaction) { var content = new List<PropertyDataDTO>(); string sql = "select * from umbracopropertydata where versionId = " + cDto.VersionId; using (SqlCommand cmd = new SqlCommand(sql, conn, transaction)) { // Add the versionId as a parameter using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { var propertyDataDTO = new PropertyDataDTO { Id = reader.GetInt32(reader.GetOrdinal("id")), VersionId = reader.GetInt32(reader.GetOrdinal("versionId")), PropertyTypeId = reader.GetInt32(reader.GetOrdinal("propertyTypeId")), LanguageId = reader.IsDBNull(reader.GetOrdinal("languageId")) ? null : reader.GetInt32(reader.GetOrdinal("languageId")), Segment = reader.IsDBNull(reader.GetOrdinal("segment")) ? null : reader.GetString(reader.GetOrdinal("segment")), IntValue = reader.IsDBNull(reader.GetOrdinal("intValue")) ? null : reader.GetInt32(reader.GetOrdinal("intValue")), DecimalValue = reader.IsDBNull(reader.GetOrdinal("decimalValue")) ? null : (decimal?)reader.GetDecimal(reader.GetOrdinal("decimalValue")), DateValue = reader.IsDBNull(reader.GetOrdinal("dateValue")) ? null : (DateTime?)reader.GetDateTime(reader.GetOrdinal("dateValue")), VarcharValue = reader.IsDBNull(reader.GetOrdinal("varcharValue")) ? null : reader.GetString(reader.GetOrdinal("varcharValue")), TextValue = reader.IsDBNull(reader.GetOrdinal("textValue")) ? null : reader.GetString(reader.GetOrdinal("textValue")), }; content.Add(propertyDataDTO); } } } // make new version sql = "select max(id) from umbracoContentVersion"; string update = "update umbracoContentVersion set [current] = 0 where id = " + cDto.VersionId; using (SqlCommand cmd = new SqlCommand(update, conn, transaction)) { cmd.ExecuteNonQuery(); } string insertSql = "INSERT INTO umbracoContentVersion (NodeId, UserId, Text, PreventCleanup, [Current]) " + "VALUES (@NodeId, @UserId, @Text, @PreventCleanup, 1)"; using (SqlCommand cmd = new SqlCommand(insertSql, conn, transaction)) { //cmd.Parameters.AddWithValue("@VersionId", cDto.VersionId); cmd.Parameters.AddWithValue("@NodeId", cDto.NodeId); cmd.Parameters.AddWithValue("@UserId", cDto.UserId); cmd.Parameters.AddWithValue("@Text", cDto.Text ?? (object)DBNull.Value); // Handling potential null value cmd.Parameters.AddWithValue("@PreventCleanup", cDto.PreventCleanup); cmd.ExecuteNonQuery(); } int nextVersionId = 0; using (SqlCommand cmd = new SqlCommand(sql, conn, transaction)) { object result = cmd.ExecuteScalar(); if (result != null && result != DBNull.Value) { nextVersionId = Convert.ToInt32(result); } } insertSql = "INSERT INTO umbracoPropertyData (VersionId, PropertyTypeId, LanguageId, Segment, IntValue, DecimalValue, DateValue, VarcharValue, TextValue) " + "VALUES (@VersionId, @PropertyTypeId, @LanguageId, @Segment, @IntValue, @DecimalValue, @DateValue, @VarcharValue, @TextValue)"; foreach (var line in content) { line.VersionId = nextVersionId; using (SqlCommand cmd = new SqlCommand(insertSql, conn, transaction)) { cmd.Parameters.AddWithValue("@VersionId", line.VersionId); cmd.Parameters.AddWithValue("@PropertyTypeId", line.PropertyTypeId); cmd.Parameters.AddWithValue("@LanguageId", line.LanguageId.HasValue ? (object)line.LanguageId : DBNull.Value); cmd.Parameters.AddWithValue("@Segment", string.IsNullOrEmpty(line.Segment) ? (object)DBNull.Value : line.Segment); cmd.Parameters.AddWithValue("@IntValue", line.IntValue.HasValue ? (object)line.IntValue : DBNull.Value); cmd.Parameters.AddWithValue("@DecimalValue", line.DecimalValue.HasValue ? (object)line.DecimalValue : DBNull.Value); cmd.Parameters.AddWithValue("@DateValue", line.DateValue.HasValue ? (object)line.DateValue : DBNull.Value); cmd.Parameters.AddWithValue("@VarcharValue", string.IsNullOrEmpty(line.VarcharValue) ? (object)DBNull.Value : line.VarcharValue); cmd.Parameters.AddWithValue("@TextValue", string.IsNullOrEmpty(line.TextValue) ? (object)DBNull.Value : line.TextValue); cmd.ExecuteNonQuery(); } } } } public class ContentVersionDTO { public int VersionId { get; set; } public int NodeId { get; set; } public int UserId { get; set; } public string Text { get; set; } public bool PreventCleanup { get; set; } } public class PropertyDataDTO { public int Id { get; set; } public int VersionId { get; set; } public int PropertyTypeId { get; set; } public int? LanguageId { get; set; } public string Segment { get; set; } public int? IntValue { get; set; } public decimal? DecimalValue { get; set; } public DateTime? DateValue { get; set; } public string VarcharValue { get; set; } public string TextValue { get; set; } }
Editor is loading...
Leave a Comment