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 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 void LockArticleForUpdate(DbConnection cnn, int id)
{
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(cnn);
var withlock = databaseType == DatabaseType.SqlServer ? "with(rowlock, updlock)" : "";
var forUpdate = databaseType != DatabaseType.SqlServer ? "for update" : "";
using (var cmd = DbCommandFactory.Create($@"select content_item_id from content_item {withlock} where content_item_id = @id {forUpdate}", cnn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@id", id);
var dt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(dt);
}
}
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 string GetConflictIds(DbConnection connection, int id, int contentId, string condition, List <FieldParameter> parameters)
{
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(connection);
using (var cmd = DbCommandFactory.Create($"SELECT CONTENT_ITEM_ID FROM CONTENT_{contentId}_UNITED WHERE {condition} AND CONTENT_ITEM_ID <> @id", connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@id", id);
foreach (var parameter in parameters)
{
cmd.Parameters.Add(CreateDbParameter(databaseType, parameter));
}
var dt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(dt);
return(IdCommaList(dt, FieldName.ContentItemId));
}
}
public static DataTable GetFieldTypes(DbConnection cnn, int[] ids)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(cnn);
var textType = dbType == DatabaseType.SqlServer ? "nvarchar(max)" : "text";
var text = $@"select attribute_id, BACK_RELATED_ATTRIBUTE_ID, attribute_type_id, link_id, is_classifier,
cast(case when coalesce(cast(enum_values as {textType}), '') <> '' then 1 else 0 end as bit) as is_string_enum
from content_attribute where attribute_id in (select id from {Common.IdList(dbType, "@ids")})";
using (var cmd = DbCommandFactory.Create(text, cnn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(Common.GetIdsDatatableParam("@ids", ids, dbType));
var dt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(dt);
return(dt);
}
}
public static IEnumerable <DataRow> RemovingActions_GetContentsItemInfo(int?siteId, int?contentId, DbConnection connection)
{
var query = $@"select S.SITE_ID, S.SITE_NAME, C.CONTENT_ID, C.CONTENT_NAME, cast(COALESCE(I.ITEMS_COUNT, 0) as int) AS ITEMS_COUNT from
(select content_id, count(CONTENT_ITEM_ID) ITEMS_COUNT from content_item group by content_id) I
RIGHT JOIN CONTENT C ON C.CONTENT_ID = I.CONTENT_ID
JOIN SITE S ON S.SITE_ID = C.SITE_ID
where (c.content_id = @content_id OR @content_id is null)
and (s.site_id = @site_id OR @site_id is null)";
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@content_id", (object)contentId ?? DBNull.Value);
cmd.Parameters.AddWithValue("@site_id", (object)siteId ?? DBNull.Value);
var dt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(dt);
return(dt.AsEnumerable().ToArray());
}
}
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 DataRow GetDefaultArticleRow(QPModelDataContext context, DbConnection connection, int contentId)
{
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var sql = "qp_get_default_article";
if (databaseType == DatabaseType.Postgres)
{
var fields = context.FieldSet.Where(n => n.ContentId == contentId).OrderBy(n => n.Name).ToArray();
var fieldNames = fields.Select(n => n.Name.ToLower()).ToArray();
var fieldNameResults = String.Join(",", fieldNames.Select(n => $@"""{n}"" TEXT"));
var fieldSelects = String.Join(",", fields.Select(n => $@"""{n.Name.ToLower()}""::{PgSelectType((int)n.TypeId)}"));
sql = $@"
SELECT {fieldSelects} FROM crosstab('
select 0::numeric as content_item_id, lower(ca.attribute_name),
case when ca.attribute_type_id in (9, 10) then coalesce(ca.default_value, ca.default_blob_value)
else qp_correct_data(ca.default_value::text, ca.attribute_type_id, ca.attribute_size, ca.default_value)::text
end as value from content_attribute ca
inner join content c on ca.content_id = c.content_id
where c.content_id = {contentId}
order by 1,2
') AS final_result(content_item_id numeric, {fieldNameResults})" ;
}
using (var cmd = DbCommandFactory.Create(sql, connection))
{
if (databaseType == DatabaseType.Postgres)
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@content_id", contentId);
}
var ds = new DataSet();
DataAdapterFactory.Create(cmd).Fill(ds);
return(0 == ds.Tables.Count || 0 == ds.Tables[0].Rows.Count ? null : ds.Tables[0].Rows[0]);
}
}
public static void GetContentModification(DbConnection sqlConnection, IEnumerable <int> articleIds, bool withAggregated, bool returnPublishedForLive, ref List <int> liveIds, ref List <int> stageIds)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var idsParam = withAggregated ? $"{DbSchemaName(dbType)}.qp_aggregated_and_self(@ids)" : "@ids";
var source = IdList(dbType, idsParam);
var aggFunc = returnPublishedForLive ? "max" : "min";
var ids = articleIds as int[] ?? articleIds.ToArray();
if (!ids.Any())
{
return;
}
var sql = $@"
select cast(a.content_id as int) as content_id, cast({aggFunc}(a.is_published) as bit) as is_published from
(
select ci.content_item_id, ci.content_id,
case when st.status_type_name = 'Published' and {IsFalse(dbType, "ci.splitted")} then 1 else 0 end as is_published
from {source}
inner join content_item ci {WithNoLock(dbType)} on i.id = ci.content_item_id
inner join status_type st on ci.status_type_id = st.status_type_id
) a group by a.content_id
";
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(GetIdsDatatableParam("@ids", ids, dbType));
var dt = new DataTable();
DataAdapterFactory.Create(cmd).Fill(dt);
var rows = dt.AsEnumerable().ToArray();
stageIds = rows.Select(n => n.Field <int>("content_id")).ToList();
bool Predicate1(DataRow n) => n.Field <bool>("is_published");
bool Predicate2(DataRow n) => !n.Field <bool>("is_published");
liveIds = rows.Where(returnPublishedForLive ? (Func <DataRow, bool>)Predicate1 : Predicate2).Select(n => n.Field <int>("content_id")).ToList();
}
}
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);
}
}
public static UserDAL Authenticate(DbConnection connection, string login, string password, bool useNtLogin, bool checkAdminAccess)
{
object[] parameters;
switch (connection)
{
case SqlConnection _:
parameters = new object[] {
new SqlParameter {
ParameterName = "login", DbType = DbType.String, Size = 255, Value = login
},
new SqlParameter {
ParameterName = "password", DbType = DbType.String, Size = 20, Value = password ?? string.Empty
},
new SqlParameter {
ParameterName = "use_nt_login", DbType = DbType.Boolean, Value = useNtLogin
},
new SqlParameter {
ParameterName = "check_admin_access", DbType = DbType.Boolean, Value = checkAdminAccess
}
};
break;
case NpgsqlConnection _:
parameters = new object[] {
new NpgsqlParameter {
ParameterName = "login", DbType = DbType.String, Size = 255, Value = login
},
new NpgsqlParameter {
ParameterName = "password", DbType = DbType.String, Size = 20, Value = password ?? string.Empty
},
new NpgsqlParameter {
ParameterName = "use_nt_login", DbType = DbType.Boolean, Value = useNtLogin
},
new NpgsqlParameter {
ParameterName = "check_admin_access", DbType = DbType.Boolean, Value = checkAdminAccess
}
};
break;
default:
throw new ApplicationException("Unknown connection type");
}
using (var cmd = DbCommandFactory.Create("qp_authenticate", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
var dt = new DataTable();
var dataAdapter = DataAdapterFactory.Create(cmd);
dataAdapter.Fill(dt);
if (dt.Rows.Count == 0)
{
return(null);
}
var r = dt.Rows[0];
return(new UserDAL()
{
Id = r.Field <decimal>("USER_ID"),
Disabled = r.Field <decimal>("DISABLED"),
FirstName = r.Field <string>("FIRST_NAME"),
LastName = r.Field <string>("LAST_NAME"),
Email = r.Field <string>("EMAIL"),
AutoLogOn = r.Field <decimal>("AUTO_LOGIN"),
NTLogOn = r.Field <string>("NT_LOGIN"),
LastLogOn = r.Field <DateTime?>("LAST_LOGIN"),
Subscribed = r.Field <decimal>("SUBSCRIBED"),
Created = r.Field <DateTime>(FieldName.Created),
Modified = r.Field <DateTime>(FieldName.Modified),
LastModifiedBy = r.Field <decimal>(FieldName.LastModifiedBy),
LanguageId = r.Field <decimal?>("LANGUAGE_ID"),
VMode = r.Field <decimal>("VMODE"),
AdSid = r.Field <byte[]>("ad_sid"),
AllowStageEditField = r.Field <decimal>("allow_stage_edit_field"),
AllowStageEditObject = r.Field <decimal>("allow_stage_edit_object"),
BuiltIn = r.Field <bool>("BUILT_IN"),
LogOn = r.Field <string>("LOGIN"),
PasswordModified = r.Field <DateTime>("PASSWORD_MODIFIED"),
MustChangePassword = r.Field <bool>("MUST_CHANGE_PASSWORD")
});
}
}