Using a saved query with a parameter

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

Please do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.

This forum is for discussing mojoPortal development

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

You can monitor commits to the repository from this page. We also recommend developers to subscribe to email notifications in the developer forum as occasionally important things are announced.

Before posting questions here you might want to review the developer documentation.

Do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.
3/12/2017 3:34:50 AM
ABM
Gravatar
Total Posts 29

Using a saved query with a parameter

I would like to use a saved query similar to the article describing how to use a saved query to populate a chart, but I have a multi-site installation and would like to use the saved query in more than one site by passing in the siteid as a parameter. Does anyone have an example on how to do this? Thanks in advance!

3/13/2017 1:58:40 PM
Gravatar
Total Posts 1991

Re: Using a saved query with a parameter

Hi,

This isn't currently possible but we will add it to our Feature Requests over at GitHub.

Thanks,
Joe

3/13/2017 2:10:24 PM
ABM
Gravatar
Total Posts 29

Re: Using a saved query with a parameter

Glad to hear it. I've been thinking about alternate ways of doing this since I posted. One way I came up with would be to do a search and replace of a token in the saved query since the example is passing the sql statement to the data reader to execute it. For example, I could have a where clause similar to Where siteid = #siteid# Then do a simple Replace in the code behind. Maybe this can help someone else with a similar problem.
6/17/2017 1:00:45 AM
Gravatar
Total Posts 1991

Re: Using a saved query with a parameter

Hi,

I took at a look at your response and I think string replacement is probably the best way to go about this.

One could easily add module settings to the custom module they create and use the settings as their parameters. I will probably add an example of that soon.

Saved Query:

SELECT SiteName,Skin FROM mp_sites WHERE SiteID = $_SiteID_$;

Custom Module:

<%@ Control Language="C#" ClassName="QueryWithParams.ascx" Inherits="mojoPortal.Web.SiteModuleControl" %>

<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Drawing" %>
<%@ Import Namespace="mojoPortal.Business" %>
<%@ Import Namespace="mojoPortal.Business.WebHelpers" %>
<%@ Import Namespace="mojoPortal.Web.Framework" %>
<%@ Import Namespace="mojoPortal.Web.Controls" %>
<%@ Import Namespace="mojoPortal.Web.Editor" %>
<%@ Import Namespace="mojoPortal.Net" %>

<script runat="server">
    /// <summary>
    /// Author:                    Joe Davis
    /// Created:                2017-06-16
    /// Last Modified:            2017-06-16
    ///
    /// The use and distribution terms for this software are covered by the
    /// Common Public License 1.0 (http://opensource.org/licenses/cpl.php)
    /// which can be found in the file CPL.TXT at the root of this distribution.
    /// By using this software in any fashion, you are agreeing to be bound by
    /// the terms of this license.
    ///
    /// You must not remove this notice, or any other, from this software.
    ///
    /// </summary>

    private SiteSettings siteSettings;
    private string siteName = string.Empty;
    private string siteSkin = string.Empty;
    protected override void OnInit(EventArgs e)
    {
        siteSettings = CacheHelper.GetCurrentSiteSettings();
        base.OnInit(e);
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        LoadSettings();
        ExecuteQuery();
        PopulateControls();
    }

    private void ExecuteQuery()
    {
        SavedQueryRespository repository = new SavedQueryRespository();
        SavedQuery q = repository.Fetch("QueryWithParams");
        if (q == null) { return; }

        StringBuilder query = new StringBuilder(q.Statement);

        query.Replace("$_SiteID_$", siteSettings.SiteId.ToString());

        using (IDataReader reader = DatabaseHelper.GetReader(string.Empty, query.ToString()))
        {
            while (reader.Read())
            {
                siteName = reader["SiteName"].ToString();
                siteSkin = reader["Skin"].ToString();
            }
        }
    }

    private void PopulateControls()
    {
        litSiteName.Text = siteName;
        litSkin.Text = siteSkin;
    }

    private void LoadSettings()
    {

        Title1.Visible = !this.RenderInWebPartMode;
        if (this.ModuleConfiguration != null)
        {
            this.Title = this.ModuleConfiguration.ModuleTitle;
        }

    }

</script>

<asp:Panel ID="pnlWrapper" runat="server" CssClass="panelwrapper ">
    <portal:ModuleTitleControl ID="Title1" runat="server" />
    <asp:Panel ID="pnlForumStatsChart" runat="server" EnableViewState="false" CssClass="modulecontent">
        <span><strong>Site Name:</strong></span> <asp:Literal ID="litSiteName" runat="server" /><br />
        <span><strong>Skin:</strong></span> <asp:Literal ID="litSkin" runat="server" />
    </asp:Panel>
</asp:Panel>

Hope this helps,
Joe