public static void PersistArticle(DbConnection currentDbConnection, string customerCode, string xml, out int id) { var databaseType = DatabaseTypeHelper.ResolveDatabaseType(currentDbConnection); var ns = SqlQuerySyntaxHelper.DbSchemaName(databaseType); var sql = $"select id, modified from {ns}.qp_persist_article(@xml)"; using (var cmd = DbCommandFactory.Create(sql, currentDbConnection)) { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", xml, databaseType)); var dt = new DataTable(); try { DataAdapterFactory.Create(cmd).Fill(dt); } catch (PostgresException ex) { Logger.Error() .Exception(ex) .Message("Error while persisting article with xml: {xml}\n Query: {sql}", xml, sql) .Property("customerCode", customerCode) .Write(); throw; } id = (int)dt.Rows[0]["id"]; } }
public static IEnumerable <DataRow> BatchInsert(DbConnection sqlConnection, DataTable articles, bool visible, int userId) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); using (var cmd = DbCommandFactory.Create(BatchInsertQuery, sqlConnection)) { if (dbType == DatabaseType.SqlServer) { cmd.Parameters.Add(new SqlParameter("@values", SqlDbType.Structured) { TypeName = "Values", Value = articles }); } else { cmd.CommandText = $@"select * from qp_batch_insert(@values, @visible, @userId);"; var xml = GetValuesDoc(articles).ToString(); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@values", xml, dbType)); } cmd.Parameters.AddWithValue("@visible", visible ? 1 : 0); cmd.Parameters.AddWithValue("@userId", userId); var dt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(dt); return(dt.AsEnumerable().ToArray()); } }
public static List <int> InsertArticleIds(DbConnection sqlConnection, string xml, bool withGuids) { var result = new List <int>(); var withGuidsStr = withGuids ? ", UNIQUE_ID" : ""; var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); string baseInsert = $@"INSERT INTO CONTENT_ITEM (VISIBLE, STATUS_TYPE_ID, CONTENT_ID, LAST_MODIFIED_BY {withGuidsStr}) "; string baseSelect = $@"SELECT VISIBLE, STATUS_TYPE_ID, CONTENT_ID, LAST_MODIFIED_BY {withGuidsStr} "; string sql = dbType == DatabaseType.SqlServer ? $@" DECLARE @NewArticles TABLE (CONTENT_ID int, STATUS_TYPE_ID int, VISIBLE int, LAST_MODIFIED_BY int, UNIQUE_ID uniqueidentifier) DECLARE @NewIds TABLE ([ID] INT); INSERT INTO @NewArticles SELECT doc.col.value('./@contentId', 'int') CONTENT_ID ,doc.col.value('./@statusId', 'int') STATUS_TYPE_ID ,doc.col.value('./@visible', 'int') VISIBLE ,doc.col.value('./@userId', 'int') LAST_MODIFIED_BY ,doc.col.value('./@guid', 'uniqueidentifier') UNIQUE_ID FROM @xml.nodes('ITEMS/ITEM') doc(col) {baseInsert} OUTPUT inserted.CONTENT_ITEM_ID INTO @NewIds {baseSelect} from @NewArticles SELECT ID FROM @NewIds; " : $@" WITH inserted(id) AS ( {baseInsert} {baseSelect} from XMLTABLE('ITEMS/ITEM' passing @xml COLUMNS CONTENT_ID int PATH '@contentId', STATUS_TYPE_ID int PATH '@statusId', VISIBLE int PATH '@visible', LAST_MODIFIED_BY int PATH '@userId', UNIQUE_ID uuid PATH '@guid' ) x returning CONTENT_ITEM_ID ) select id from inserted;"; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", xml, dbType)); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { int id; if (int.TryParse(reader.GetValue(0).ToString(), out id)) { result.Add(id); } } } return(result); } }
public static void InsertArticleValues(DbConnection sqlConnection, string xmlParameter) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); string sql = dbType == DatabaseType.SqlServer ? "qp_insertArticleValues" : PgTableValuesBlock + $@" update content_data cd set data = coalesce(new.blob_data, new.data) from new where cd.content_item_id = new.content_item_id and cd.attribute_id = new.attribute_id; "; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.CommandType = dbType == DatabaseType.SqlServer ? CommandType.StoredProcedure : CommandType.Text; cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType)); var resultDt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(resultDt); } }
public static void UpdateArticleGuids(DbConnection sqlConnection, List <Tuple <int, Guid> > guidsByIdToUpdate) { if (guidsByIdToUpdate.Any()) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var doc = new XDocument(); doc.Add(new XElement("ITEMS")); foreach (var tuple in guidsByIdToUpdate) { var elem = new XElement("ITEM"); elem.Add(new XAttribute("id", tuple.Item1)); elem.Add(new XAttribute("guid", tuple.Item2)); doc.Root?.Add(elem); } string sql = dbType == DatabaseType.SqlServer ? $@" DECLARE @NewIds TABLE (CONTENT_ITEM_ID int, UNIQUE_ID uniqueidentifier) INSERT INTO @NewIds SELECT doc.col.value('./@id', 'int') CONTENT_ITEM_ID ,doc.col.value('./@guid', 'uniqueidentifier') UNIQUE_ID FROM @xml.nodes('ITEMS/ITEM') doc(col) UPDATE CONTENT_ITEM SET UNIQUE_ID = i.UNIQUE_ID FROM CONTENT_ITEM ci INNER JOIN @NewIds i on ci.CONTENT_ITEM_ID = i.CONTENT_ITEM_ID " : $@" WITH new AS ( select x.* from XMLTABLE('ITEMS/ITEM' passing @xml COLUMNS content_item_id int PATH '@id', unique_id uuid PATH '@guid' ) x ) update content_item ci set unique_id = new.unique_id from new where ci.content_item_id = new.content_item_id;"; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", doc.ToString(), dbType)); cmd.ExecuteNonQuery(); } } }
public static void ValidateO2MValues(DbConnection sqlConnection, string xmlParameter, string message) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var tableDefintionBlock = dbType == DatabaseType.SqlServer ? $@" DECLARE @NewArticles TABLE (CONTENT_ITEM_ID int, ATTRIBUTE_ID int, DATA nvarchar(3500), BLOB_DATA nvarchar(max)) INSERT INTO @NewArticles SELECT doc.col.value('(CONTENT_ITEM_ID)[1]', 'int') CONTENT_ITEM_ID ,doc.col.value('(ATTRIBUTE_ID)[1]', 'int') ATTRIBUTE_ID ,doc.col.value('(DATA)[1]', 'nvarchar(3500)') DATA ,doc.col.value('(BLOB_DATA)[1]', 'nvarchar(max)') BLOB_DATA FROM @xmlParameter.nodes('/PARAMETERS/FIELDVALUE') doc(col) " : PgTableValuesBlock; var tableUsing = dbType == DatabaseType.SqlServer ? "@NewArticles a" : "new a"; string sql = $@"{tableDefintionBlock} select * from ( select a.*, ca.ATTRIBUTE_NAME, rca.CONTENT_ID as RELATED_CONTENT_ID from {tableUsing} inner join CONTENT_ATTRIBUTE ca on a.ATTRIBUTE_ID = ca.ATTRIBUTE_ID inner join CONTENT_ATTRIBUTE rca on ca.RELATED_ATTRIBUTE_ID = rca.ATTRIBUTE_ID and ca.CONTENT_ID <> rca.CONTENT_ID inner join CONTENT rc on rc.CONTENT_ID = rca.CONTENT_ID and rc.VIRTUAL_TYPE <> 3 where a.data != '' ) as a left join CONTENT_ITEM ci on ci.CONTENT_ITEM_ID = cast(a.data as numeric) where ci.CONTENT_ID is null or ci.CONTENT_ID <> a.RELATED_CONTENT_ID"; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType)); var resultDt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(resultDt); if (resultDt.AsEnumerable().Any()) { var dr = resultDt.Rows[0]; var title = dr.Field <string>("ATTRIBUTE_NAME"); var data = dr.Field <string>("DATA"); var id = dr.Field <int>(FieldName.ContentItemId).ToString(); throw new ArgumentException(string.Format(message, id, title, data)); } } }
public static int[] GetArticleIdsByGuids(DbConnection sqlConnection, Guid[] guids) { if (guids == null) { throw new ArgumentNullException(nameof(guids)); } var databaseType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var noLock = WithNoLock(databaseType); var textCast = (databaseType == DatabaseType.Postgres) ? "::text" : ""; string xmlSource; if (databaseType == DatabaseType.Postgres) { xmlSource = "XMLTABLE('/guids/guid' PASSING @xml COLUMNS unique_id uuid PATH '.')"; } else { xmlSource = "(SELECT doc.col.value('.[1]', 'nvarchar(max)') UNIQUE_ID FROM @xml.nodes('/guids/guid') doc(col))"; } var query = $"select coalesce(ci.content_item_id, 0), guids.unique_id{textCast} from {xmlSource} guids left join content_item ci {noLock} on ci.unique_id = guids.unique_id"; using (var cmd = DbCommandFactory.Create(query, sqlConnection)) { var doc = new XDocument(new XElement("guids")); doc.Root?.Add(guids.Select(n => new XElement("guid", n.ToString()))); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", doc.ToString(), databaseType)); var result = new Dictionary <Guid, int>(); using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { result.Add(new Guid(dr.GetString(1)), (int)dr.GetDecimal(0)); } } return(guids.Select(n => result[n]).ToArray()); } }
public static void BatchUpdate(DbConnection sqlConnection, DataTable articles, int userId) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var sql = dbType == DatabaseType.SqlServer ? SqlBatchUpdateQuery : PgBatchUpdateQuery; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { if (dbType == DatabaseType.SqlServer) { cmd.Parameters.Add(new SqlParameter("@values", SqlDbType.Structured) { TypeName = "Values", Value = articles }); } else { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@values", GetValuesDoc(articles).ToString(), dbType)); } cmd.Parameters.AddWithValue("@userId", userId); cmd.ExecuteNonQuery(); } }
public static void UpdateArticlesDateTime(DbConnection sqlConnection, string xmlParameter) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); string sql = dbType == DatabaseType.SqlServer ? "qp_update_acrticle_modification_date" : $@" WITH new AS ( select x.* from XMLTABLE('ITEMS/ITEM' passing @xmlParameter COLUMNS content_item_id int PATH '@id', last_modified_by int PATH '@modifiedBy' ) x ) update content_item ci set modified = now(), last_modified_by = new.last_modified_by from new where ci.content_item_id = new.content_item_id; "; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.CommandType = dbType == DatabaseType.SqlServer ? CommandType.StoredProcedure : CommandType.Text; cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType)); var resultDt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(resultDt); } }
public static IEnumerable <DataRow> GetRelations(DbConnection sqlConnection, DataTable articles) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); using (var cmd = DbCommandFactory.Create(GetRelationsQuery(dbType), sqlConnection)) { if (dbType == DatabaseType.SqlServer) { cmd.Parameters.Add(new SqlParameter("@values", SqlDbType.Structured) { TypeName = "Values", Value = articles }); } else { var xml = GetValuesDoc(articles).ToString(); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@values", xml, dbType)); } var dt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(dt); return(dt.AsEnumerable().ToArray()); } }
public static void InsertO2MFieldValues(DbConnection sqlConnection, string xmlParameter) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); string sql = dbType == DatabaseType.SqlServer ? "qp_update_o2mfieldvalues" : $@" WITH new AS ( select x.* from XMLTABLE('ITEMS/ITEM' passing @xmlParameter COLUMNS content_item_id int PATH '@id', linked_id int PATH '@linked_id', attribute_id int PATH '@field_id' ) x ) update content_data cd set data = new.linked_id from new where cd.content_item_id = new.CONTENT_ITEM_ID and cd.attribute_id = new.attribute_id; "; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.CommandType = dbType == DatabaseType.SqlServer ? CommandType.StoredProcedure : CommandType.Text; cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType)); var resultDt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(resultDt); } }