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