el-GRen-US
 
     
Razor in DNN with an example of an SQL SProc

Razor in DNN with an example of an SQL SProc

Σεπ 18 2015
Κοινοποίηστε το άρθρο στο:

This is an introduction of Razor and how you can code a script (cshtml file) to fetch and display data in current DNN 7.x version.
Razor is supported in DNN from 2010 (as long as your DNN installation is supported by ASP.NET v4.0) and it will be a first class citizen for next major version of DNN.
Razor together with MVC - also a first class citizen in DNN 8.0 - will facilitate a lot the application development lifecycle in DNN and provide us developers a quick way to extend DNN's features using a familiar markup language and its helpers.

First what is Razor in how it is supported in ASP.NET?
An article in www.w3schools.com will explain everyting but I am borrowing two sentences of it:

  1. Razor is not a programming language. It is a markup syntax that lets you embed server-based code (Visual Basic and C#) into web pages.
  2. Razor is based on ASP.NET, and designed for creating web applications. It has the power of traditional ASP.NET markup, but it is easier to use, and easier to learn.

So Razor is a very powerful markup syntax and for Us old school developers - coming from an era that asp (and php) was our everyday tool of work - is really familiar and cozy.

In this article I will show you how you can write a Stored Procedure (SProc) in a DNN database and use it to get data in a Razor host module calling a simple cshtml script file.

This a single page, simple grid, HTML5 master-detail view with some jQuery support for pagination. My intention is to show you some basic stuff about Razor and the use of helpers (functions) and core DNN libraries. There are some very useful materials (documentation and examples) about Razor in DNN website so please visit the later link for an article on using core DNN libraries to create a contact list: http://www.dnnsoftware.com/community-blog/cid/136514/occamrsquos-razor-and-dotnetnuke by Joe Brinkman.
I strongly suggest to read all Joe’s articles about DNN here: http://www.dnnsoftware.com/community-blog/articletype/authorview/authorid/8129

On the figures below you can see a screenshot of the module with the list of members and a detail record of one person.

List of members with jQuery pagination
Figure.1 - List of members with jQuery pagination.

The details of a record. All information are exposed on user's security preferences
Figure.2 - The details of a record. All information are exposed on user's security preferences.

Let’s start first with the design and creation of the SProc

Enough with theory, let's start coding!

I wanted for a long time to create my own list of DNN users with all the information they provided as members of the website in a members directory.
That made my choice easy on selecting what data I wanted to fetch from the DNN database.

The way I did that is by transfering all Business Logic (BL) in the database layer using standard SQL and leave only the presentation of the data to HTML. 
This is the way we did it in the past using asp but it can be done in any other way you like (e.g. using Entity Framework).

The target table was the UserProfile table. The task was not very easy though because DNN stores the profile data of a user in rows with each row having a pair of name, which is a property in another table called ProfilePropertyDefinition, and its value which is the data I wanted to show in the HTML (Razor) view.

The way to convert rows into columns in SQL is to use the PIVOT function. I found some very useful information on this website:

http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server

The other issue I had is that member’s properties are of course dynamic so the number of rows to be converted into columns are also dynamic including their names. I had to find a way to dynamically construct the select clause so the information found on this website helped me a lot:

https://sqlwhisper.wordpress.com/2013/03/24/stuff-and-for-xml-path-for-string-concatenation/

The security for each field should be on the results set also so I created a second dynamic PIVOT with all properties and their visibility status.
Please notice that DNN has statuses for viewing your personal data, all the visitors, administrators and members of the website (you can also select which groups of members if you want to). These are enumerated by integer values, 0 is for all, 1 is for administrators, 2 is for members.
In the results set I have created columns for visibility (security) for all properties using the schema PropertyName_Security.

Last but not least and while I was debugging the SProc I found that the data in the Country and Region fields are not always numeric especially when you allow your users to register via Facebook or Google+ authentication (I do). In this case the data in the fields are text and that causes a lot of problems when you want to left join these fields with the Lists table that has the text values of the linked EntryID’s.
To deal with it I created an SQL Scalar Function that will check if a field value is text and return it as is or if it is numeric will search for it in the Lists table either for a Country or a Region.

That will wrap all things done to create the SProc.

Bellow is the SProc code

/****** Object:  StoredProcedure [dbo].[CS_ApexIT_myITPro_getAllUsers]    Script Date: 09/18/2015 08:32:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Apex IT Solutions, myITPro.eu, Spyros Samartzis
-- Create date: 10/09/2015
-- Description:	Get all users properties and security
-- =============================================
ALTER PROCEDURE [dbo].[CS_ApexIT_myITPro_getAllUsers] 
	-- Add the parameters for the stored procedure here
	@userid varchar(256) = NULL, @issuperuser char(1) = "1"
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @cols as nvarchar(max),
	@colsproperties as varchar(max),
	@query as nvarchar(max),
	@queryusername as nvarchar(max),
	@colssecurity as varchar(max),
	@querysecurity as varchar(max)
	-- This is a parameter now so don't declare it and assign a NULL value
	--select @username = 'ssamartzis'
	--select @username = NULL

	select @cols = STUFF((select ',' + quotename(propertyname) from ProfilePropertyDefinition group by propertyname FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,1,'')
	select @colsproperties = STUFF((select ',pvt.' + quotename(propertyname) from ProfilePropertyDefinition group by propertyname FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,1,'')
	select @colssecurity = STUFF((select ',ISNULL(ts.' + quotename(propertyname)+',2) as '+propertyname+'_Security' from ProfilePropertyDefinition group by propertyname FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,1,'')
	-- Some Debug print
	--print @colsproperties
	--print @colssecurity
	--print @cols

	if @userid is not null 
	begin 
		set @queryusername = N' where u.issuperuser < ' +@issuperuser+ ' and u.userid='''+ @userid +''''
	end 
	else 
	begin 
		set @queryusername= N' where u.issuperuser < ' +@issuperuser
	end
	
	-- Some Debug print
	--print @queryusername

	-- Built dynamic query and convert rows into columns for all userprofile properties using the PIVOT SQL statement
	set @query = N'select u.UserID,u.Username,u.DisplayName,u.IsSuperUser,up.FileName as Profilepic,up.Width,up.Height,up.FolderID,up.Folder,dbo.CS_fn_GetCountryOrRegion(pvt.country,0) as CountryName,dbo.CS_fn_GetCountryOrRegion(pvt.region,1) as RegionName,' + @colsproperties +','+@colssecurity+ N' from 
	(select up.UserID,ppd.PropertyName,up.PropertyValue 
	from  dbo.UserProfile up inner join ProfilePropertyDefinition ppd on up.PropertyDefinitionID = ppd.PropertyDefinitionID) t1
	pivot (
	max(t1.PropertyValue) for t1.PropertyName in (' + @cols + N')
	) pvt inner join (select UserID,Username,DisplayName,IsSuperUser from Users) u on pvt.UserID=u.UserID 
	left outer join files up on pvt.Photo = up.FileId 
	inner join (                     
	select *
	from (select up.UserID,ppd.PropertyName,up.Visibility
	from  dbo.UserProfile up inner join ProfilePropertyDefinition ppd on up.PropertyDefinitionID = ppd.PropertyDefinitionID) t2
	pivot
	(max(t2.Visibility) for t2.PropertyName 
	in (' + @cols + N')) pvt2
	) ts on pvt.UserID = ts.UserID 
	'+@queryusername+' order by u.UserID asc'
	
	-- Some Debug print
	--print @query
	
	exec sp_executesql @query
		 
END

 

And the UDF code

/****** Object:  UserDefinedFunction [dbo].[CS_fn_GetCountryOrRegion]    Script Date: 09/18/2015 08:29:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Apex IT Solutions, Spyros Samartzis
-- Create date: 15/09/2015
-- Description:	Get Country or Region
-- =============================================
ALTER FUNCTION [dbo].[CS_fn_GetCountryOrRegion] 
(
	-- Add the parameters for the function here
	@inputvalue varchar(512),@countryorregion int
)
RETURNS varchar(512)
AS
BEGIN
	DECLARE @Result varchar(512)
	DECLARE @Id int
	-- First check if it is a numeric value so you can lookup for it in Lists table
		IF ISNUMERIC(@inputvalue) > 0
		BEGIN
			SELECT @Id = ROUND(@inputvalue,0) -- ROUND it to make it an integer
			IF @countryorregion = 0 -- Get country
			BEGIN
				SELECT @Result = Text from  dbo.Lists where (ListName = 'Country') and EntryID = @Id 
			END
			ELSE -- Get region
			BEGIN
				SELECT @Result = Text from  dbo.Lists where (ListName = 'Region') and EntryID = @Id
			END
		END
		ELSE
		BEGIN
			SELECT @Result = @inputvalue
		END
		
	-- Return the result of the function
	RETURN @Result

END

Now we can proceed on setting up the DNN Razor Host and create (and run) the script

First you need to install DNN Razor host module. All 6.x and 7.x versions of DNN have it in the default distribution and you just need to install it via Host > Extensions.

Next you need to create or copy-paste your cshtml script file. The cshtml file can be created online in DNN's Razor host editor or any other text editor (I use notepad++) and if you have a hosted version of DNN with local access to your files it can be created and edited with Visual Studio also.
The folder that the file is created is DesktopModules\RazorModules\RazorHost\Scripts.

The code for the cshtml file I used for this example is shown below:

@using DotNetNuke.Common
@using DotNetNuke.Entities.Users

@{ 
    /* Get current user properties for IsSuperUser (host), IsAdmin (in Role Administrators) and UserID */
    var currentuser_IsSuperUser = UserController.Instance.GetCurrentUserInfo().IsSuperUser;
    var currentuser_UserID = UserController.Instance.GetCurrentUserInfo().UserID;
    var currentuser_IsAdmin = UserController.Instance.GetCurrentUserInfo().IsInRole("Administrators");
    
    /* Get, Set variables and connect to DNN Dbase*/
    var db = Database.Open("SiteSqlServer");
	var selectsuperusers = "2";
	var userId = Request["Id"];
	var selectQuery = "";
	if (userId == null || !(IsNumeric(userId))) {selectQuery = "EXEC CS_ApexIT_myITPro_getAllUsers NULL,'"+selectsuperusers+"'";} 
	else {selectQuery = "EXEC CS_ApexIT_myITPro_getAllUsers '"+ userId +"'" + ",'"+selectsuperusers+"'";};
 }

@{
if (userId == null || !(IsNumeric(userId))) //List of all records. Simple pagination via jQuery
 {
	
        @foreach (var row in db.Query(selectQuery))
        { 
		var profilepic = myProfilepic(row.IsSuperUser,row.Folder,row.Profilepic,row.Photo_Security,Request.IsAuthenticated);
			
        }	
	
@row.DisplayName

@getPropertyValue(row.DisplayName,0,Request.IsAuthenticated)

@getPropertyValue(row.Profession,row.Profession_Security,Request.IsAuthenticated)
@Html.Raw(WebUtility.HtmlDecode(getPropertyValue(row.Biography,row.Biography_Security,Request.IsAuthenticated,true)))
IsSuperUser: @{ @getIsSuperUser(Convert.ToString(row.IsSuperUser), Request.IsAuthenticated, currentuser_IsSuperUser); //getPropertyValue(Convert.ToString(row.IsSuperUser),2,Request.IsAuthenticated) //Don't use this method to display sensitive security info. //Instead I have created two(2) methods to get if the current user is a SuperUser(host) and also get their Username (only for Admins and Hosts). }
/* Replace the following javascript tags with script tags if you copy-paste this code */ /*Simple pagination, break page every 2 records(rows)*/ $(function () { $('table.table_contacts').each(function () { // var currentPage = 0; var currentPage = parseInt(@myPageNum(Request["page"]))-1; // Zero based elements. var numPerPage = 2; var displayPage = 0; // Change the href of links to details. It will include the page number to return back in the list (preserve state). $(".profilepic > a").each(function () { var _href = $(this).attr("href"); var displayOnPage = Math.floor(displayPage / numPerPage) + 1; $(this).attr("href", _href + '/page/' + displayOnPage); displayPage = displayPage + 1; }); var $table = $(this); $table.bind('repaginate', function () { $table.find('tbody tr').hide().slice(currentPage * numPerPage, (currentPage + 1) * numPerPage).show(); }); $table.trigger('repaginate'); var numRows = $table.find('tbody tr').length; var numPages = Math.ceil(numRows / numPerPage); var $pager = $('
'); for (var page = 0; page < numPages; page++) { $('').text(page + 1).bind('click', { newPage: page }, function (event) { currentPage = event.data['newPage']; $table.trigger('repaginate'); $(this).addClass('active').siblings().removeClass('active'); }).appendTo($pager).addClass('clickable'); } $pager.insertAfter($table).find('span.page-number:first').addClass('active'); }); // check pager and make current page active var currentPage = parseInt(@myPageNum(Request["page"])) - 1; $(".pager > .page-number").each(function () { var currentValue = parseInt($(this).text()) - 1; if (currentValue == currentPage) {$(this).addClass('active'); } else { $(this).removeClass('active'); } }); });
} else { //Details of a record foreach (var row in db.Query(selectQuery)) { var profilepic = myProfilepic(row.IsSuperUser,row.Folder,row.Profilepic,row.Photo_Security,Request.IsAuthenticated);

@getPropertyValue(row.DisplayName,0,Request.IsAuthenticated)

@getPropertyValue(row.Profession,row.Profession_Security,Request.IsAuthenticated)

@Html.Raw(WebUtility.HtmlDecode(getPropertyValue(row.Biography,row.Biography_Security,Request.IsAuthenticated,true)))
  • Is this a SuperUser? @getIsSuperUser(Convert.ToString(row.IsSuperUser), Request.IsAuthenticated, currentuser_IsSuperUser)
  • Is this page accessed by an authenticated user? @Request.IsAuthenticated
  • An Id is selected: @Request["Id"], the username is: @getUserName(row.UserID,row.Username,Request.IsAuthenticated,currentuser_IsSuperUser,currentuser_IsAdmin,currentuser_UserID)
/* Replace the following javascript tags with script tags if you copy-paste this code */ /*Hide Social header if all social list items are empty*/ $(function() { if (($.trim($("li.facebook").html())=='') && ($.trim($("li.twitter").html())=='') && ($.trim($("li.linkedin").html())=='')) {$(".social.header").hide();}; }); } ] @functions { public static string myProfilepic(bool isSuperUser, string Folder, string Profilepic, int PropertySecurity, bool IsAuthenticated) { string strmyProfilepic = Globals.ApplicationPath + "/images/no_avatar.gif"; string strmyProfilepicRootFolder = "/Portals/0/"; if (isSuperUser) {strmyProfilepicRootFolder = "/Portals/_default/";}; if (PropertySecurity!=0 && !(IsAuthenticated)) {strmyProfilepic = Globals.ApplicationPath + "/images/no_avatar.gif";} else { if (!(Profilepic == null)) {strmyProfilepic = strmyProfilepicRootFolder+Folder+Profilepic;}; }; return strmyProfilepic; } public static System.Boolean IsValidURL (string myURL){ Uri uriResult; bool result = Uri.TryCreate(myURL, UriKind.Absolute, out uriResult) && ( uriResult.Scheme == Uri.UriSchemeHttp || uriResult.Scheme == Uri.UriSchemeHttps ); return result; } public static string getURLfromPropertyValue (string PropertyValue) { string strmyPropertyValue; if (IsValidURL(PropertyValue)) {strmyPropertyValue=""+PropertyValue+"";} else {strmyPropertyValue=PropertyValue;}; return strmyPropertyValue; } public static string getURLfromPropertyValue (string PropertyValue, string ProtocolValue) { string strmyPropertyValue; string tempURL = ProtocolValue + PropertyValue; if (IsValidURL(PropertyValue)) {strmyPropertyValue=""+PropertyValue+""; return strmyPropertyValue; } if (IsValidURL(tempURL)) {strmyPropertyValue=""+PropertyValue+"";} else {strmyPropertyValue=PropertyValue;}; return strmyPropertyValue; } public static string getPropertyValue (string PropertyValue, int PropertySecurity, bool IsAuthenticated){ string strmyPropertyValue; if (string.IsNullOrEmpty(PropertyValue)) {strmyPropertyValue="";} else {strmyPropertyValue = PropertyValue; if (PropertySecurity!=0 && !(IsAuthenticated)) {strmyPropertyValue="*";} } return strmyPropertyValue; } public static string getPropertyValue (string PropertyValue, int PropertySecurity, bool IsAuthenticated, bool IsHtml){ string strmyPropertyValue; if (string.IsNullOrEmpty(PropertyValue)) {strmyPropertyValue=" ";} else {strmyPropertyValue = PropertyValue; if (PropertySecurity!=0 && !(IsAuthenticated)) {strmyPropertyValue=" ";} } //strmyPropertyValue = Html.Raw(WebUtility.HtmlDecode(strmyPropertyValue)); return strmyPropertyValue; } public static string getUserName (int UserID, string UserName, bool IsAuthenticated, bool IsSuperUser, bool IsAdmin, int HasUserId) { string strUserName = "*"; if (IsAuthenticated && (IsSuperUser || IsAdmin)) { strUserName = UserName; } if (IsAuthenticated && (UserID == HasUserId)) { strUserName = UserName + " (you, logged in!)"; return strUserName;} return strUserName; } public static string getIsSuperUser(string IsSuperUserValue, bool IsAuthenticated, bool IsSuperUser) { string strIsSuperUser = "*"; if (IsAuthenticated && IsSuperUser) { strIsSuperUser = IsSuperUserValue; } return strIsSuperUser; } public static string test (bool isSuperUser) { string mytest = "this is a test"; return mytest; } public static System.Boolean IsNumeric (System.Object Expression){ if(Expression == null || Expression is DateTime) return false; if(Expression is Int16 || Expression is Int32 || Expression is Int64 || Expression is Decimal || Expression is Single || Expression is Double || Expression is Boolean) return true; try { if(Expression is string) Double.Parse(Expression as string); else Double.Parse(Expression.ToString()); return true; } catch {} // just dismiss errors but return false return false; } public static string myPageNum(string PageNum) { string strmyPageNum = "1"; if (!(string.IsNullOrEmpty(PageNum))) {strmyPageNum=PageNum;} return strmyPageNum; } }

As you can see in the first lines of code there are two using statements that reference DNN libraries and then a @ Razor call to connect to database.
We can use the SiteSqlServer App variable, setup our variables and get ready to execute our query against DNN database.
You can of course use any valid C# (or VB) syntax to create the script and to access server variables e.g. the Request[“Id”] is getting the Id query string value and assign it to variable userId.

Then you start the iteration through all records (rows) of the results that came from SQL SProc and either you create a list or a details record of one person.
To get the details record the script has an if statement that will return the details when a Request[“Id”] is found in the query string.

Next in lines after number 145 there is @functions Razor call with my helper functions that have logic to display data from the Database in a valid manner.
For instance since DNN has a known issue on saving SuperUsers (host) profile pictures in Portals\_Default folder and not in Portals\PortalID folder we need to create the myProfilepic function to get the correct information and place it as html in the page. There are some additional functions for security and for using http(or https) links in profile values that you can explore yourself.

That’s it!

It is really good to see that DNN has the ability to extend your programming in such mainstream areas like MVC and Razor.

You can see this in action and download the SQL Scripts and the Razor view here.
You will need to be a registered user. Kindly ask to register here.

Please feel free to contact me for any issues or questions you want to ask.
Always your comments are most welcome. 

Best,
Spyros

Spyros Samartzis
Σχετικά με τον Spyros Samartzis
I love creating web applications using Microsoft technologies and open source platforms.
My moto is "I work and enjoy technology every day!"
Πρέπει να είστε μέλος στο website ώστε να μπορείτε να σχολιάσετε. Παρακαλώ εγγραφείτε εδώ
Top