Example #1
0
        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());
            }
        }
Example #2
0
        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"];
            }
        }
Example #3
0
        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);
            }
        }
Example #4
0
        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);
            }
        }
Example #5
0
        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));
            }
        }
Example #6
0
        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);
            }
        }
Example #7
0
        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());
            }
        }
Example #8
0
        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));
                }
            }
        }
Example #9
0
        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]);
            }
        }
Example #10
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();
            }
        }
Example #11
0
        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);
            }
        }
Example #12
0
        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());
            }
        }
Example #13
0
        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);
            }
        }
Example #14
0
        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")
                });
            }
        }