This project has moved and is read-only. For the latest updates, please go here.

SqlurlRuleProvider

This provider generates UrlRules for the Open Url Rewriter based on sql queries.

The query need to return a record for each url on each page and for each culture (language) you want to rewrite.

You can also generate redirection rules.
The old parameter url is automatically redirected.

The urls are included in the sitemap.
And are used by the enhanced 404 management.

How to proceed :

The queries have to be defined in Host, SQL and saved with a name starting with "OpenUrlRewriter"

Exemple : OpenUrlRewriter_Events
(for the events module)

The query need to return some predifined columns :

TabId (int) : the tabid of the page on which the url need to be rewriten
Parameters (varchar) : the parameters to rewitre (exemple : catid=10&itemid=54)
Url (varchar) : the module url part (exemple : cars/vokwagen)
CultureCode (varchar) : null of en-US, ... (optional default null)
InSitemap (bit) : to include in the sitemap (optional, default true)
RuleAction (int) : Rewrite = 0, Redirect = 1 (optional, default Rewrite)
RemoveTab (bit) : remove de pagename in the url (optional , default false)
RedirectDestination (varchar) : absolute url to redirect to (requred only for redirect rules)
RedirectStatus (int) : 301, 302, 404 (optional , default 301)

and need one parameter @PortalId to return only the record corresponding to one portal

Exemple for the event module :

  select 
e.EventName as url, 
t.Tabid, 
p.culturecode,  
'ModuleID='+cast(e.moduleid as varchar(20))+'&ItemID=' + cast(e.eventid as varchar(20)) + '&mctl=EventDetails' as parameters, 
m.PortalId

from events e

LEFT JOIN Modules M
 ON e.moduleid = m.moduleid
 
LEFT JOIN TabModules T
 ON T.moduleid = m.moduleid  

LEFT JOIN ModuleDefinitions d
 ON d.moduledefid = m.moduledefid 
 
LEFT JOIN tabs p
 ON p.tabid = t.tabid  
 
where d.Friendlyname = 'Events'
and m.PortalId = @PortalId

This exemple rewrite all the event details urls of the events module.
With the url equals to the event title.
The url is automatically cleanup if needed (special chars are removed, spaces are replaced by -).

Sources on github

Remarks for testing

The Host SQL query required by the sql provider is not a query that resolves without error in Host , SQL or SQL Management Studio.
The query needed by the module must have the clause "WHERE PortalId = @PortalId" to dynamically replace the parameters in the c# code of the SQL provider.

You can check the test query for each portal by adding the following lines at the beginning of your query. Change the '1' for other portal numbers.

DECLARE @PortalId Int
SET @PortalId = 1

Remove these two lines for the final version(s).

The final saved query must contain "WHERE PortalId = @PortalId" so the SQL provider can query the records for 1 portal and execute the same query for all other portals.

Last edited Mar 1, 2015 at 10:46 PM by sachatrauwaen, version 14

Comments

No comments yet.