This is Part 4 of the continuing series "Create a custom module with the free Advanced Datagrid module".  As in previous examples, you simply drop the ready-to-use Advanced Datagrid module on a page, populate a couple of settings and you're done:  instant "Login Redirection Based on User Role" for your DNN site.  Very cool...
 
 
Recap: Advanced Datagrid (ADG)
The ADG module is an unbelievably handy tool for DNN admins.  You can use it for tasks as diverse as creating reports, adding functionality missing in other modules or even creating entirely new modules as we'll do in this post.  All it takes is a little SQL knowledge and some imagination (or just copy-and-paste from my examples.)  If this is the first time you've heard of the the module then you'll probably want to check out my previous posts:  Part 1, Part 2 and Part 3.  A related post covers the similar (not open source but with more features) SQLView module from DNNStuff.  Both Advanced Datagrid and SQLView are third-party precursors to the DNN "Reports" module.
 
 
Login Redirect (Forwarding users based on User Roles)
I've seen this question asked many times in the DotNetNuke.com forums:  DNN lets me redirect users to a page after login -- how can I redirect to several different pages based on a user's assigned security roles?
 
In an enterprise enviroment you really want to do your redirect logic on the server side of thing by using a module built for the task.  My recommendation for the enterprise is the Dynamic Login module from Data Spings which, among other things, lets you redirect users based on roles as well as rolegroups.  If you need solid performance, ease of management and good customer support then Dynamic Login is for you.
 
In a non-enterprise environment you may tend to lean a bit more towards...er....free stuff.  That's where the Advanced Datagrid comes in.  Let's get an example working on a test page and then we'll discuss how it works...
 
 
Set It Up
As with the previous posts concerning Advanced Datagrid, this is pretty simple to set up.

1. Create a test page on your DNN site.  You definitely want to get everything just-right on a test page because, if you accidentally redirect everyone (including yourself) you won't be able to get back into module settings to change the redirect logic without going into the database.  So....create a test page.
    
2. Add the Advanced Datagrid module to the test page.  If you don't already have the (totally free) ADG module, you can get it and its source code here:  http://www.efficionconsulting.com/tabid/217/Default.aspx
    
3. Go into the ADG module's SETTINGS and add the following code to the "SQL Statement" box under the section "Advanced Datagrid Settings":
    
   declare @tmpName nvarchar(50);
   set @tmpName = (select top 1 username from users where userid = [dnn:UserID]);
    
   declare @tmpTable TABLE(rolename nvarchar(50))
   begin try
     exec sp_addlinkedserver 'loopback','','SQLOLEDB', @@SERVERNAME
   end try
   begin catch
   end catch
    
   declare @qStr varchar(max)
   set @qStr =
   N'(select q1.rolename
    from openquery(loopback, ''exec dnnweb..getUserRolesByUsername [dnn:PortalID],'''''+@tmpName+''''',null'') as q1)'
   insert @tmpTable(rolename) exec (@qStr)
    
   if 'SiteAdmin' in (select rolename from @tmpTable)
   select '< script >if(esRedirect){window.location="http://dotnetnuke.com"}< /script >'
   else if 'Research' in (select rolename from @tmpTable)
   select '< script >if(esRedirect){window.location="http://yahoo.com"}< /script >'
   else if 'Marketing' in (select rolename from @tmpTable)
   select '< script >if(esRedirect){window.location="http://google.com"}< /script >'
   
   Note:  You will need to remove the blank space just after each "<" and just before each ">" in the last five lines of the example code (remove the blank spaces within each SCRIPT tag).
    
4. Ok, you may be looking at step 3 and thinking "What the heck is THAT?!?"  We'll review the code in a bit.  For now, just know that I've tested it as far back as DNN 4.6.2 with SQL Server 2005.  It should work on all versions of DNN 4.x, 5.x and with SQL Server 2000 as well.  I have not tested this in a wide variety of shared-hosting environments so your milage may vary...
    
   Click "Update" to save your settings for the ADG module (the SQL code was the only thing we needed to add).
    
5. Now go into your test page's SETTINGS and add the following to the PAGE HEADER TAGS section:
    
   < script >esRedirect=true< /script >
   
   You'll need to remove the blank spaces inside the SCRIPT tags after you copy-and-paste.

You're done (sort of).  You now have a working "redirect based on user role" module.  You may also notice that nothing is happening.  This is because the example I gave redirects based on three roles:  "SiteAdmin", "Research" and "Marketing".  To be safe I avoided including default roles in the example.   
 
Unless you've created at least one of those roles and are logged in with an account with that role, nothing will happen.
 
So, to test things you'll want to create a new Role called "Marketing" and then create a test user with the "Marketing" role.  Open another browser, log in with your test user and browse to your test page.  That user should be redirected to the Google search page.  Sweet!  Now that you know it works, you can go back into the module settings and alter final few lines of the SQL code to define your own URL destinations (pages on your site) based on user roles.
 
 
What's Going On
In a nutshell:

• A user logs in and is forwarded to whatever page DNN is set to forward logged in uders to.  This is the page where you will put your ADG module with our redirection code. 
   
• The page loads the ADG module which queries the DB with the user's ID and gets back the list of User Roles that the user belongs to. 
   
• The ADG code checks the list of roles for the first matching "redirect" role (specified by you).  Upon finding a match, the ADG returns a single line of JavaScript to the page which forward the user to a specified URL.  If no match is found, nothing is returned and the user remains on the current page.

 
The SQL code
The SQL code looks a little strange because we've used a couple of tricks to make this work without having to create a stored procedure or fully duplicate any DB tables as temp tables.  I won't explain it all in detail but I will tell you what each piece does.  Even so this will still get a little involved so, if you don't really care how it works, skip down to the section entitled "The Last Bit" (just before the section "Other Uses").
 
Here are our first two lines of SQL code:
 
  declare @tmpName nvarchar(50);
  set @tmpName = (select top 1 username from users where userid = [dnn:UserID]);
 
First let's recall that the ADG module provides tokens for DNN UserID and PortalID.  The user ID token shown above is [dnn:UserID].  Also note that these ADG tokens are case-sensitive.
 
We want to leverage built-in DNN objects and methods whenever possible.  The more we do so the more likely it will be that our code will work with future versions of DNN.  The same holds true for database operations so we're going to leverage an existing stored procedure "getUserRolesByUsername" which is just about perefect for this project.  Unfortunately ADG does not provide a token for UserName so our first two lines of SQL code (shown above) get the UserName based on the current user's UserID.  Simple enough so far.  Our next line is:
 
  declare @tmpTable TABLE(rolename nvarchar(50))
 
This line creates a local temporary table to store the list of RoleName values for the current user.  This is the list we'll check when looking for a match to one of our defined "redirect" roles.  Still simple, but then we get to....
 
  begin try
    exec sp_addlinkedserver 'loopback','','SQLOLEDB', @@SERVERNAME
  end try
  begin catch
  end catch

Oh, boy.  This probably doesn't look at all familiar unless you've needed to access a remote data source directly from a stored procedure.  The stored procedure "sp_addlinkedserver" does just that: sets up a link to a remote data source.   This lets you execute SQL on the remote source rather than locally.  You execute remotely through the use of the OPENQUERY command.  For instance, if you queried a different DNN database with 50,000 users for the most recent three user's names you'd prefer to execute an OPENQUERY on the remote DB and return only the resulting three names rather than execute the query locally which would grab all 50,000 users from the remote DB before applying any WHERE clauses.
 
In our case, we're setting up a "loopback" link pointing to our own DNN database.  Huh?  Why set up a link to ourselves?  We do this so we can use OPENQUERY on our own database.  (It's ok to ask "huh?" again at this point.)  You only need to define the link once so we take the easy way out and put the call inside of a TRY..CATCH block to avoid potential error messages when attempting to re-connect an existing connection.  There IS a stored procedure to un-connect from a remote source but that's (roughly) as performance costly as the TRY..CATCH so I'm giving you the "lazy" solution.
 
OPENQUERY to the local DB....why?
Why would we want to jump through hoops to remotely execute a query that will run locally?  We do this because OPENQUERY exposes the query results as a table that can be SELECTed into.  More importantly, OPENQUERY exposes the results as a table even for the results of stored procedures.  In short, by using OPENQUERY we're able to perform a SELECT directly on the results of an EXEC (running a stored procedure) without using a matching temp table to house all columns of the EXEC results.  (This is something many DBAs will tell you is not possible.)
 
The "getUserRolesByUsername" stored procedure returns 21 columns of data but we only want the "rolename" column.  OPENQUERY lets us operate with a single-column temp table instead of having to define a temp table with 21 columns.
 
Moving on...
 
  declare @qStr varchar(max)
  set @qStr =
  N'(select q1.rolename from openquery (loopback, ''exec dnnweb..getUserRolesByUsername [dnn:PortalID], '''''+@tmpName+''''', null'') as q1)'

Two things are happening here.  At the core we're defining a string to represent our OPENQUERY query.  This includes the name of the remote data source ("loopback" in our example).  Normally we could simply execute OPENQUERY directly without first creating a string representation of the call.  Unfortunately OPENQUERY does not support SQL variables as parameters.  Our query hinges on passing along the UserName which we've stored in the variable @tmpName.  To get around OPENQUERY's limitation on using variables, we instead define the entire query as a string resolving any variables in the process.  Problem solved.
 
  insert @tmpTable(rolename) exec (@qStr)

Ah, this little line is what all the trouble was about.  Here we are executing the stored procedure "getUserRolesByUsername" and are selecting only the column "rolename" from the 21 resulting columns of data.  Without OPENQUERY we would have had to create a temporary table and manually define all 21 columns to match the SP result structure.  Further, by using OPENQUERY our solution is more robust as it will still work even if a future version of the "getUserRolesByRolename" procedure changes to include more (or less) columns in its results.  As long as it returns a column named "rolename" we're ok.
 
The Last Bit
So, our call to the stored procedure resulted in our temp table "@tmpTable" being filled with a list of the current user's assigned User Roles.  This leads to the last (and simplest) part of our code:
 
  if 'SiteAdmin' in (select rolename from @tmpTable)
  select '< script >if(esRedirect){window.location="http://dotnetnuke.com"}< /script >'

  else if 'Research' in (select rolename from @tmpTable)
  select '< script >if(esRedirect){window.location="http://yahoo.com"}< /script >'

  else if 'Marketing' in (select rolename from @tmpTable)
  select '< script >if(esRedirect){window.location="http://google.com"}< /script >'
 
For this example I included a check for three different role types but you could check for more by adding additional "else if" sections.  Each section checks for a specific role name and, if the user is assigned to that role, the code returns a JavaScipt "forward" to a specified URL.
 
A Small Safety Measure
Notice the JavaScript "if" statement which checks the boolean variable "esRedirect".  Recall the very last step (step #5) we performed when setting up the ADG module as a redirect module.  If we do not set "esRedirect" to "true" on the page then users will not be forwarded even if they are assigned to a Role with a specified redirection URL.  This is a safety measure.  If you accidentally include code that forwards everyone (including yourself) away from the page then you will not be able to easily access the SETTINGS for the ADG module.  If this happens, go to ADMIN->TABS, select the page with the redirect and click the EDIT icon to open that page's SETTINGS.  Now you can change "esRedirect" to "false" and you'll have direct access to the page and the module since the redirect will be disabled.  A simple but effective safety measure.
 

Other Uses
As will all posts in this series, the overall intent is not to provide a solution to a specific problem but rather to give you a jump start on creating your own solutions for all manner of problems.  Taking a step back from the "redirection" idea, it should be easy to see how the same solution can be used for other "based-on-user-role" tasks:

• Role-based messaging.  Pop an alert box if a user is in a subscription role that is about to lapse.
   
• Handy admin tool:  When an Administrator visits a particular page, automatically open a series of new browser windows to your site's Google Analytics reporting page, Feedburner reporting, AdSense reporting, etc.
   
• Role-based surveys.  Create a survey page with a "blank" skin.  For users who are members of roles you wish to survey, pop a new window with the survey page and pass unique querystring parameters based on various user roles.
   
• Create a "deny role" module.  DNN 5.x introduced the great "deny" feature in module permissions.  In DNN 4.x, though, you only have a few third-party modules that have implemented such a feature.  You can use ADG to set JavaScript to dynamically set the CSS "display" attribute to hide/show other modules on the page based on a user's assigned Roles.

 
Remember -- It's a Workaround
Keep in mind that using ADG (or SQLView, or Reports) in this manner is just a workaround to help you quickly attain custom functionality in the absence of an appropriate module.  Advanced Datagrid-based solutions like the ones I've shared are great to help you quickly prototype features and processes before you spend resources buying or developing a custom module.  An ADG-based solution can work perfectly well long-term for some applications but a good, professionally-developed module will nearly always outperform an ADG solution in one respect or another.
 
 
Please add your comments / questions below.  I have tons of info and ideas I'd like to post but never enough time to do so.  Your comments help me determine what subjects I'll attempt to tackle next.