private DbCommand GetReplicateDataCommand(IEnumerable <Dictionary <string, string> > values, int[] attrIds) { var cmd = CreateDbCommand(); var ids = values.Select(n => Int32.Parse(n[SystemColumnNames.Id])).ToArray(); if (DatabaseType == DatabaseType.SqlServer) { cmd.CommandText = "qp_replicate_items"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@ids", SqlDbType.NVarChar, -1) { Value = string.Join(",", ids) }); cmd.Parameters.Add(new SqlParameter("@attr_ids", SqlDbType.NVarChar, -1) { Value = string.Join(",", attrIds) }); } else { cmd.CommandText = attrIds.Any() ? "call qp_replicate_items(@ids, @attr_ids);" : "call qp_replicate_items(@ids);"; cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, DatabaseType)); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@attr_ids", attrIds, DatabaseType)); } return(cmd); }
private DbCommand GetUpdateModifiedCommand(IEnumerable <int> existingIds, int[] newIds, int contentId) { var cmd = CreateDbCommand($@" select content_item_id, modified from content_{contentId}_united {WithNoLock} where content_item_id in (select id from {IdList()}) "); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", newIds.Union(existingIds), DatabaseType)); return(cmd); }
private DataTable GetVersionDataValues(IEnumerable <int> versionIds, IEnumerable <int> attrIds) { var cmd = CreateDbCommand($@" select attribute_id, content_item_version_id, data from version_content_data where content_item_version_id in (select id from {IdList("@versionIds", "i")}) and attribute_id in (select id from {IdList("@attrIds", "a")}) "); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@versionIds", versionIds, DatabaseType)); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@attrIds", attrIds, DatabaseType)); return(GetRealData(cmd)); }
private DbCommand GetVersionIdsToRemoveCommand(int[] ids, int maxNumber) { var cmd = CreateDbCommand($@" select content_item_version_id from ( select content_item_id, content_item_version_id, row_number() over(partition by civ.content_item_id order by civ.content_item_version_id desc) as num from content_item_version civ where content_item_id in (select id from {IdList()}) ) c where c.num >= @maxNumber"); cmd.Parameters.AddWithValue("@maxNumber", maxNumber); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, DatabaseType)); return(cmd); }
private IEnumerable <int> GetAggregateVersionFunction(string function, int[] ids) { if (!ids.Any()) { return(new int[0]); } using (var cmd = CreateDbCommand()) { cmd.CommandText = $@" select cast({function}(content_item_version_id) as int) as data from content_item_version where content_item_id in (select id from {IdList()}) group by content_item_id "; cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, DatabaseType)); return(GetRealData(cmd).Select().Select(row => Convert.ToInt32(row["data"])).ToArray()); } }
private DbCommand GetValidateConstraintCommand(XContainer validatedDataDoc, IReadOnlyList <ContentAttribute> attrs, out string attrNames) { var sb = new StringBuilder(); var validatedIds = validatedDataDoc .Descendants("ITEM") .Where(n => !n.Descendants("MISSED_DATA").Any()) .Select(n => int.Parse(n.Attribute("id")?.Value ?? throw new InvalidOperationException())) .ToArray(); var contentId = attrs[0].ContentId; attrNames = string.Join(", ", attrs.Select(n => SqlQuerySyntaxHelper.FieldName(DatabaseType, n.Name))); if (DatabaseType == DatabaseType.SqlServer) { sb.AppendLine("declare @default_num int, @default_date datetime;"); sb.AppendLine("set @default_num = -2147483648;"); sb.AppendLine("set @default_date = getdate();"); sb.AppendLine($"WITH X(CONTENT_ITEM_ID, {attrNames})"); sb.AppendLine(@"AS (SELECT doc.col.value('./@id', 'int') CONTENT_ITEM_ID"); foreach (var attr in attrs) { var aName = SqlQuerySyntaxHelper.FieldName(DatabaseType, attr.Name); sb.AppendLine($",doc.col.value('(DATA)[@id={attr.Id}][1]', 'nvarchar(max)') {aName}"); } sb.AppendLine("FROM @xmlParameter.nodes('/ITEMS/ITEM') doc(col))"); } else { sb.AppendLine($@"WITH X AS( select xml.* from XMLTABLE('/ITEMS/ITEM' PASSING @xmlParameter COLUMNS content_item_id int PATH '@id'" ); foreach (var attr in attrs) { var aName = SqlQuerySyntaxHelper.FieldName(DatabaseType, attr.Name); sb.AppendLine($@", {aName} text PATH 'DATA[@id={attr.Id}]'"); } sb.AppendLine(") xml )"); } var defaultDate = (DatabaseType == DatabaseType.SqlServer) ? "@default_date" : "now()"; var defaultNum = (DatabaseType == DatabaseType.SqlServer) ? "@default_num" :"-2147483648"; sb.AppendLine($" SELECT c.CONTENT_ITEM_ID FROM CONTENT_{contentId}_UNITED c {WithNoLock} INNER JOIN X ON c.CONTENT_ITEM_ID NOT IN (select v.id from {IdList("@validatedIds", "v")})"); foreach (var attr in attrs) { var attrName = SqlQuerySyntaxHelper.FieldName(DatabaseType, attr.Name); if (attr.IsNumeric) { sb.AppendLine($"AND coalesce(c.{attrName}, {defaultNum}) = case when X.{attrName} = '' then {defaultNum} else cast (X.{attrName} as numeric(18, {attr.Size})) end"); } else if (attr.IsDateTime) { sb.AppendLine($"AND coalesce(c.{attrName}, {defaultDate}) = case when X.{attrName} = '' then {defaultDate} else cast (X.{attrName} as timestamp without time zone) end"); } else { sb.AppendLine($"AND coalesce(c.{attrName}, '') = coalesce(X.{attrName}, '')"); } } var xml = validatedDataDoc.ToString(SaveOptions.None); var cmd = CreateDbCommand(sb.ToString()); cmd.CommandTimeout = 120; cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParam("@xmlParameter", xml, DatabaseType)); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@validatedIds", validatedIds, DatabaseType)); return(cmd); }