Automating Extensionless Urls in MojoPortal

Posted by Shaun Geisert Wednesday, August 03, 2011 10:19:00 AM Categories: .NET Tools MojoPortal

Yesterday i put together some code to convert all of my sites to use friendly-urls for all pages (including those pages generated by Event Calendar Pro).  The code iterates through every entry in mp_FriendlyUrls, updating the urls there and also in mp_Pages, then adding a new entry to mp_RedirectList to make sure those individuals entering, eg, www.domain.com/mypage.aspx get redirected to www.domain.com/mypage.

Feel free to download the application at the bottom of this page, or copy the code below into your own application...

Btw, you could alternatively use a few mojo DLLS and rebuild the following app without the use of ad-hoc queries.

Web.Config Settings of Import:

<add name="MSSQLConnectionString" connectionString="Data Source=sqlprod;Initial Catalog=CWISXXXMojoPortal;Persist Security Info=True;User ID=cwisXXX;Password=XXX" providerName="System.Data.SqlClient"/>

 

<!-- If your site has a ridiculous number of urls, consider making this even higher than 10 minutes -->

        <httpRuntime executionTimeout = "600"/>

 

 

Code-Behind:

 #region "Attributes"

        //******************************************************************

        //Attributes/Fields + Module-level Constants+Variables

        //******************************************************************

 

        // NOTE: This application has been tested to work under mojo 2.3.6.7.  Theoretically it will continue to work for future

        // versions of mojo, unless the table schemas for mp_FriendlyUrls, mp_Pages, or mp_RedirectList changes.

 

 

        string aspxExtension = ".aspx";

 

        //Get all friendly urls in system (including urls created by modules, such as Event Cal Pro)

        private const string urlsQuery = @"SELECT     SiteGuid, SiteID, FriendlyUrl

FROM         mp_FriendlyUrls

WHERE FriendlyUrl LIKE '%.aspx'";

 

        //Get only page urls where we are to use the url and the page isn't pending

        /*@"SELECT        Url, SiteGuid, SiteID

        FROM            mp_Pages

        WHERE        (UseUrl = 'True') AND (IsPending = 'False')";*/

 

        private const string pagesUpdateQuery = @"UPDATE mp_Pages SET Url = REPLACE(Url, '{0}', '{1}')";

 

        private const string friendlyUrlsQuery = @"UPDATE mp_FriendlyUrls SET FriendlyUrl = REPLACE(FriendlyUrl, '{0}', '{1}')";

 

        private const string redirectListQuery = @"INSERT INTO mp_RedirectList (SiteGuid, SiteID, OldUrl, NewUrl, CreatedUtc, ExpireUtc) VALUES ('{0}', {1}, '{2}', '{3}', '{4}', '{5}')";

 

        #endregion //Attributes

 

        #region "Event Procedures"

        //******************************************************************

        //Event Procedures

        //******************************************************************

 

        protected void btnSubmit_Click(object sender, EventArgs e)

        {

            initiateExtensionlessUrls();

        }

 

        #endregion //Event Procedures

 

        #region "Data Methods"

        //******************************************************************

        //Data Methods

        //******************************************************************

 

 

        protected void initiateExtensionlessUrls()

        {

            SqlConnection conn = null;

 

            //get connection string from web.config

            string connString = ConfigurationManager.ConnectionStrings["MSSQLConnectionString"].ConnectionString;

 

            conn = new SqlConnection(connString);

 

            //get select all pages query

            string query = String.Format(urlsQuery);

 

            try

            {

                SqlCommand command = new SqlCommand(query, conn);

 

                conn.Open();

 

                SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);

 

                //For each page

                while (dr.Read())

                {

 

                    //Fetch url value

                    string oldUrl = dr["FriendlyUrl"].ToString();

 

                    //Fetch site guid

                    string siteGuid = dr["SiteGuid"].ToString();

 

                    //Fetch site id

                    string siteId = dr["SiteID"].ToString();

 

                    //Remove extension form url field, store new value

                    string newUrl = oldUrl.Replace(aspxExtension, "");

 

                    //Using new and old values, update friendly urls table

                    updateFriendlyUrlsTable(oldUrl, newUrl);

 

                    //Using new and old values, update pages table

                    updatePagesTable(oldUrl, newUrl);

 

                    //Using new/old url values, add a new record to RedirectList

                    insertRedirectListRecord(siteGuid, siteId, oldUrl, newUrl);

 

                }

 

                //cleanup object

                dr.Close();

 

                lblAlert.Text = "<strong>The conversion process finished successfully!  Feel free to test your site to confirm (you'll have to touch web.config again to see the changes).</strong>";

            }

            catch (SqlException ex)

            {

                lblAlert.Text = ex.ToString();

 

                return;

            }

            finally

            {

                //close connection

                conn.Close();

            }

        }

 

        protected void updatePagesTable(string oldUrl, string newUrl)

        {

            SqlConnection conn = null;

 

            //get connection string from web.config

            string connString = ConfigurationManager.ConnectionStrings["MSSQLConnectionString"].ConnectionString;

 

            conn = new SqlConnection(connString);

 

            SqlCommand nonqueryCommand = conn.CreateCommand();

 

            //get update query

            string query = String.Format(pagesUpdateQuery, oldUrl, newUrl);

 

            conn.Open();

 

            SqlCommand command = new SqlCommand(query, conn);

 

            command.ExecuteNonQuery();

 

            //close connection

            conn.Close();

 

        }

 

        protected void updateFriendlyUrlsTable(string oldUrl, string newUrl)

        {

            SqlConnection conn = null;

 

            //get connection string from web.config

            string connString = ConfigurationManager.ConnectionStrings["MSSQLConnectionString"].ConnectionString;

 

            conn = new SqlConnection(connString);

 

            SqlCommand nonqueryCommand = conn.CreateCommand();

 

            //get update query

            string query = String.Format(friendlyUrlsQuery, oldUrl, newUrl);

 

            conn.Open();

 

            SqlCommand command = new SqlCommand(query, conn);

 

            command.ExecuteNonQuery();

 

            //close connection

            conn.Close();

        }

 

        protected void insertRedirectListRecord(string siteguid, string siteid, string oldUrl, string newUrl)

        {

            SqlConnection conn = null;

 

            //get connection string from web.config

            string connString = ConfigurationManager.ConnectionStrings["MSSQLConnectionString"].ConnectionString;

 

            conn = new SqlConnection(connString);

 

            SqlCommand nonqueryCommand = conn.CreateCommand();

 

            //get insert query (SiteGuid, SiteID, OldUrl, NewUrl, CreatedUtc, ExpireUtc)

            string query = String.Format(redirectListQuery, siteguid, siteid, oldUrl, newUrl, DateTime.UtcNow, DateTime.UtcNow);

 

            conn.Open();

 

            SqlCommand command = new SqlCommand(query, conn);

 

            command.ExecuteNonQuery();

 

            //close connection

            conn.Close();

 

        }

 

        #endregion //Data Methods

 

You will obviously need to add a lblAlert and btnSubmit to your front-end to make this work, or just download my app.

 

Comments are closed on this post.