I work in a web team of 6 developers and we have a diverse range of skills. Some are more skilled dealing with the front end, the UI, and working with HTML, JavaScript and CSS. Others are better with the back end of things, the business logic and dealing with databases. However, there is an overlap.
One thing I noticed earlier this year when we took on our first front end specialist is that she did all sorts of things to optimise the HTML, CSS and JavaScript. It was wonderful, the application worked a lot better and looked a lot nicer. Unfortunately, some of the optimisations broke things because we’d been populating divs and spans from Ajax calls, third party code, or other bits of JavaScript. Because we knew where the data was coming from, having the full view as we did, we’d not bother commenting the HTML (and HTML comments waste bandwidth, right?)
It became obvious that something was going to need to be done if we were going to work with a specialist like this. It occurred to us that the ASP.NET rendering engine allows us to create server side comments that are not transmitted to the client. That helped keep the HTML clean on the client and allowed us to indicate to our specialist that there was a div or span that is needed (and shouldn’t be optimised away)
<div id="sizeSurchargesInfo"><%-- Populated by an AJAX Callback --%></div>
In hindsight, it seems rather obvious to simply comment the code. However, in these enlightened times it does seem unnecessarily frowned upon.
In this post, I’ll look at getting Umbraco and MVC to play nice with each other in the same project.
Installing Umbraco 4.8
First off create a Web Application project in Visual Studio. For this example, I’m just going to create the project as “UmbMvc”.
Visual Studio 2010: New Project
Once Visual Studio has created the project, delete most of its content. We’re doing this because we don’t have fully empty projects. If you were doing this in VS 2012 you could have selected the Empty Web Application project instead.
The Solution explorer should look like this when your done:
Visual Studio 2010: Solution Explorer
Next up, Umbraco has to be installed. This can be done with NuGet. I used the Package Manager Console, which can be accessed from the Tools menu:
Visual Studio: NuGut Package Manager Console
Then typed Install-Package UmbracoCms to install the package and its dependencies. The output looks like this:
PM> Install-Package UmbracoCms
'UmbracoCms.Core (= 4.8.0)' not installed. Attempting to retrieve dependency from source...
Done.
Successfully installed 'UmbracoCms.Core 4.8.0'.
Successfully installed 'UmbracoCms 4.8.0'.
Successfully added 'UmbracoCms.Core 4.8.0' to UmbMvc.
'web.config' already exists. Skipping...
Successfully added 'UmbracoCms 4.8.0' to UmbMvc.
Don’t worry about the message about web.config. It will write the necessary detail into the web.config file for you.
If you prefer to use the NuGet dialog, you can search for “UmbracoCms” and install the package from there. It will download and install the dependencies for you there too.
NuGet Package Manager Dialog
At this point you can run up Umbraco to configure it and set the databases up and so on. When you’ve finished this process you’ll arrive at the Umbraco administration area. At this point you want to stop the app from running in Visual Studio.
Wiring up MVC 4
Next up is to get MVC installed. For this I’m taking the advice on Aaron Powell’s blog, so go visit there for the detail. (Start at the section marked “Getting MVC installed”). I’ve added my own notes below for some differences I found between our experiences.
Installing ASP.NET at the time of writing installs MVC 4:
PM> install-package microsoft.aspnet.mvc
'Microsoft.AspNet.WebPages (= 2.0.20505.0)' not installed. Attempting to retrieve dependency from source...
Done.
'Microsoft.Web.Infrastructure (= 1.0.0.0)' not installed. Attempting to retrieve dependency from source...
Done.
'Microsoft.AspNet.Razor (= 2.0.20505.0)' not installed. Attempting to retrieve dependency from source...
Done.
Successfully installed 'Microsoft.Web.Infrastructure 1.0.0.0'.
Successfully installed 'Microsoft.AspNet.Razor 2.0.20505.0'.
Successfully installed 'Microsoft.AspNet.WebPages 2.0.20505.0'.
Successfully installed 'Microsoft.AspNet.Mvc 4.0.20505.0'.
Successfully added 'Microsoft.Web.Infrastructure 1.0.0.0' to UmbMvc.
Successfully added 'Microsoft.AspNet.Razor 2.0.20505.0' to UmbMvc.
Successfully added 'Microsoft.AspNet.WebPages 2.0.20505.0' to UmbMvc.
Successfully added 'Microsoft.AspNet.Mvc 4.0.20505.0' to UmbMvc.
Because of the way paths work in Umbraco, I was either going to have to reserve every controller and area name in the umbracoReservedPaths configuration element, or create a prefix. I decided it was probably best to create a prefix, that way I only have to modify the config once and everything else simply works after that. So, my RouteSetup class looked like this:
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
[assembly: PreApplicationStartMethod(typeof(UmbMvc.App_Start.RouteSetup), "Setup")]
namespace UmbMvc.App_Start
{
public class RouteSetup
{
public static void Setup()
{
RouteTable.Routes.MapRoute(
"Default", // Route name
"x/{controller}/{action}/{id}", // URL with parameters
new {controller = "Home", action = "Index", id = UrlParameter.Optional} // Parameter defaults
);
}
}
}
Note the “x” at the start of the URL part of the route.
My umbracoReservedPaths config element now looks like this:
<!-- Remember to add into the umbracoReservedPaths every route that MVC wants to take.
It may be better to create a prefix so you only have to do this the once.-->
<add key="umbracoReservedPaths" value="~/umbraco,~/install/,~/x" />
Fixing up the web.config file
From Aaron’s blog post, I still couldn’t quite get it to work. I got an error message that read: Compiler Error Message: CS0234: The type or namespace name ‘Helpers’ does not exist in the namespace ‘System.Web’ (are you missing an assembly reference?)
I found that I needed to add the following to the <assemblies> section of the web.config file:
That helped, but I was still getting error an error message. This time it was Compiler Error Message: CS0234: The type or namespace name ‘Ajax’ does not exist in the namespace ‘System.Web.Mvc’ (are you missing an assembly reference?)
I found that I also needed to set the reference to System.Web.Mvc to “Copy Local”
Visual Studio 2010: System.Web.Mvc Copy Local
Then when I ran the application and went to the URL http://localhost:60445/x/Home, I got a page back that said: Hello I’m a razor view.
This is finally what I expected.
Fixing up the project type.
One last thing, Aaron also mentions that you’ll get various errors in the views in Visual Studio because the project type was a Web Application not an MVC Web Appliction project. Although it doesn’t stop the application from running correctly, it is very disconcerting to see. He doesn’t give a solution to that. To solve this, you need to add a GUID in the csproj file.
To do this, you need to right click on the web project and click “Unload Project”, when it has unloaded, right-click again and click “Edit xxx.csproj”.
Look for the element named “ProjectTypeGuids” and add in the guid: {E3E379DF-F4C6-4180-9B81-6769533ABE47}. The whole line should now read:
Save the file, then right-click the project in the solution and click “Reload project”. It will prompt you to close the text version of the project file, and then the project will be loaded back. Now you should not have any issues with the views finding false errors, such as not being able to resolve ViewBag.
I’ve just found a script on another blog to go through all the clustered indexes in a SQL Server database and rebuild them in order to reduce fragmentation and improve the disk IO needed to get the data. The original script didn’t take into account tables in different schemas so I updated it. I also added some metrics to it so I could get a sense of how long the operation takes on each table.
The script can take quite a while to run. On my database it took over 6 minutes just to initially run the query to work out what needed rebuilding, and each index can take several seconds (or possibly more if you have a lot of data) on its own.
The new script is here:
SET NOCOUNT ON
DECLARE @Schema SYSNAME;
DECLARE @Table SYSNAME;
DECLARE @Index SYSNAME;
DECLARE @Rebuild NVARCHAR(4000)
DECLARE @StartTime DATETIME = GETUTCDATE();
PRINT (CONVERT(NVARCHAR(100), GETUTCDATE(), 113) + ' : Rebuild all indexes with over 10% fragmentation.')
DECLARE DB CURSOR FOR
SELECT SS.name [schema], SO.Name [table], SI.Name [index]
FROM SYS.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
INNER JOIN SYS.objects SO ON SO.object_id=SYS.dm_db_index_physical_stats.object_id
INNER JOIN SYS.schemas SS ON SO.schema_id=SS.schema_id
INNER JOIN SYS.indexes SI ON SI.index_id=SYS.dm_db_index_physical_stats.index_id AND
SI.object_id=sys.dm_db_index_physical_stats.object_id
-- Begin select only clustered indexes Index_id = 1
AND SI.Index_id = 1
-- End select only clustered indexes Index_id = 1
WHERE avg_fragmentation_in_percent > 10.0 AND SYS.dm_db_index_physical_stats.index_id > 0
ORDER BY SO.Name
OPEN DB
FETCH NEXT FROM DB INTO @Schema, @Table, @Index
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Rebuild = 'ALTER INDEX ' + @Index + ' ON ' + @Schema + '.' + @Table + ' REBUILD'
PRINT (CONVERT(NVARCHAR(100), GETUTCDATE(), 113) + ' : ' + @Rebuild)
-- Comment out the following line to see what tables would be affected without rebuilding the indexes
EXEC SP_EXECUTESQL @Rebuild
FETCH NEXT FROM DB INTO @Schema, @Table, @Index
END
CLOSE DB
DEALLOCATE DB
DECLARE @Duration DATETIME = GETUTCDATE() - @StartTime;
PRINT (CONVERT(NVARCHAR(100), GETUTCDATE(), 113) + ' : Finished. Duration = '+CONVERT(NVARCHAR(100), @Duration, 114))
SET NOCOUNT OFF
In HTML5 there are a few new types of text box available that can help with mobile development. For example, on mobile devices it can be useful to have the on-screen keyboard be defaulted to display a specific configuration when it is displayed, such as the numeric keypad.
Not all browsers support these new text box types at the moment, but it seems that if the browser doesn’t understand the type assigned to an input element it renders it as a textbox anyway.
Regular Textbox
By regular text box I mean something rendered with something like the following HTML:
<input type="text />
Normally, I use SwiftKey, but for this demonstration I’ve switched to the default Android keyboard.
Numeric Textbox
You can set the default keypad to numeric by specifying an input like this:
<input type="number" />
Email text box
Finally, there is an email input type that configures the keyboard to display a key for “.com” and a specific key for the “@” as well as dedicated keys for the “-” and “_” next to the space bar (at least that’s how the default Android keyboard configures itself in this mode – YMMV). The input element looks like this:
<input type="email" />
Finally
If you want to try this out for yourself, there is an example available.
When retrieving data, it may not be formatted as you would need it. Most obviously, dates are the most likely candidates as the grid can work with them much more easily if they are javaScript Date objects rather than any text or numeric representation. It should be noted however, that if by simply telling the dataSource configuration that the schema of the a specific field is a date then it may be able to work out the format for itself and you don’t need a parse function to help. However, for this example, assume you must convert the type of the value.
dataSource : schema : parse
The dataSource configuration allows you to specify a function that is called when the data needs to be preprocessed in some way.
The parse function takes a parameter where by it passes the object containing the data. The function must return the processed data. In my example I’ve simply replaced the values in the existing structure with the processed version.
function preprocessData(data) {
// iterate over all the data elements replacing the Date with a version
// that Kendo can work with.
$.each(data, function(index, item){
item.Date = kendo.parseDate(item.Date, "yyyy-MM-dd");
});
return data;
}
The JSON structure contains a date in a string with a specific format containing a 4 digit year, followed by a two digit month, followed by a two digit day, separated by dashes. However, the grid can work with dates more easily if they are Date objects, which is what the kendo.parseDate() function returns.
Dealing with percentages
In a previous post I mentioned that you can format a number as a percentage by using a specific format in the kendo.toString() function call. Unfortunately, that may not be the best solution in all cases. If your data is not going to be filtered and it is in range of 0 to 1 representing 0% to 100% then that solution is fine. However, if you want to filter on the data then you probably don’t want to do that, as you’d have to enter set up the filter in the same way as the source data – and it is not intuative for the user to have to type “”0.5” when they need “50%”.
What you can do instead is ensure that the data is in the form that 100.0 is 100%, and so forth. You can use the parse function to coerce the data if you need to do that. Once you have this the filters become more intuative from the user’s perspective. Also, instead of using the built in format for parsing percentages you will need to use your own, such as “0.0”, which ensures that the value has one digit after decimal point. For example:
Moving on slightly from my last post on the Kendo UI Grid we’re going to take a wee look at paging and accessing the results of the filter in javaScript.
pageable : true
By default paging is turned off. This means that when the grid is rendered you get all the data displayed in one go. If the amount of data is small then this
may be fine. However, if the amount of data runs into the hundreds of rows (or more) then you’ll probably want to turn paging on in order to make the display of the data more manageable for the user and potentially to reduce the amount of data send to the browser (but that part is for another day – in this example I’ll be using the same data set as previously which is loaded all at once).
To enable paging add to the configuration pageable : true and also remember to add in to the dataSource part of the configuration the pageSize that you want.
If you forget to put the pageSize in then the grid will display with all the elements, but the paging navigation bar will display a message such as “NaN – NaN of 150 items”
scrollable : false
By default the grid is scrollable. This is useful if you have something to scroll, such as the virtualised scrolling feature. But for the paging in this example, the scroll bar is simply displayed but not enabled.
To turn off the scrollbar, in the configuration set scrollable : false and the scroll bar will be removed.
Getting the filtered results in JavaScript
It is possible to get the results of the filter out of the grid. It isn’t actually a direct feature of the grid (or the dataSource) but it is possible in a round about sort of way.
Essentially, what needs to happen is that filter object in the grid is used to query the data all over again to produce a second result set that can be used directly in JavaScript.
In the example below, I’ve got the results of the filter being rendered into a unordered list block.
It works but first getting hold of the grid’s data source, getting the filter and the data, creating a new query with the data and applying the filter to it. While this does result in getting the results of the filter it does have the distinct disadvantage of processing the filter operation twice.
function displayFilterResults() {
// Gets the data source from the grid.
var dataSource = $("#MyGrid").data("kendoGrid").dataSource;
// Gets the filter from the dataSource
var filters = dataSource.filter();
// Gets the full set of data from the data source
var allData = dataSource.data();
// Applies the filter to the data
var query = new kendo.data.Query(allData);
var filteredData = query.filter(filters).data;
// Output the results
$('#FilterCount').html(filteredData.length);
$('#TotalCount').html(allData.length);
$('#FilterResults').html('');
$.each(filteredData, function(index, item){
$('#FilterResults').append('<li>'+item.Site+' : '+item.Visitors+'</li>')
});
}
The results look like this:
The filter results in 12 of 150 rows returned.
National Galleries of Scotland (Edinburgh sites) : 1281465
Edinburgh Castle (Historic Scotland) : 1210248
Kelvingrove Art Gallery & Museum (Glasgow) : 1070521
Royal Botanic Garden Edinburgh : 707244
Gallery of Modern Art (Glasgow Museums) : 490872
People's Palace (Glasgow Museums) : 245770
Burrell Collection (Glasgow Museums) : 187756
Museum of Transport (Glasgow Museums) : 160571
St Mungo Museum of Religious Art (Glasgow Museums) : 143017
Provand's Lordship (Glasgow Museums) : 107044
Scotland Street School Museum (Glasgow Museums) : 49346
Glasgow Museums Resource Centre : 9059
Full grid configuration
Here is the full configuration of the grid for this example:
I’ve recently started to use Telerik’s Kendo UI framework for web applications and I have to say I’m very impressed. Although it does come with a bunch of server side extensions for ASP.NET MVC I’ve found that the javascript configuration to be just as easy.
I pulled the data into a .NET application and converted it to JSON. First I took the spreadsheet I downloaded and then saved it as CSV file. I brought it into my .NET application using a .NET CSV Reader I found on Code Project.
First off, getData() is a simply loads the data so it is available in one array to start with. I didn’t want to complicate this with having lots of calls to other services.
The schema defines how the data is to be interpreted.
filterable defines if the grid columns can be filtered or not. How that filter is represented to user depends on whether columnMenu is true or false.
filterable : true
When filterable is set to true then an icon will appear in the right of the column header to indicate that you can apply a filter.
The filter allows you to specify one or two criteria for filtering the column.
I’m not going to go too much into the schema at the moment. Suffice to say that it allows to to define how the grid interprets the data that has been sent to it.
In this example, I’m using the schema to define the type of each field in the data. That way the filtering options can interpret the data correctly. For example, the Visitors column is a number, so it would be better to give filter options such as “greater than” or “less than” instead of the default string filter options of “contains” or “starts with”. Like this:
Other data types that the schema.model can interpret are string (the default), boolean, and date.
columnMenu : true
By default, if you don’t specifiy a columnMenu, it will be false. and you won’t get the menu. If, however, you set columnMenu to true then there will be a small down-arrow displayed which when clicked displays the menu.
Without any other settings, the menu will just allow you to turn on and off columns. If you set sortable to true then you also get the “Sort Ascending” and “Sort Descending” options. And if you set filterable to true then you get a menu item for filtering the data as the menu item replaces the icon for filtering the data in the column header.
The image below shows the columnMenu with the sortable and filterable options turned on.
In the definition of the Change column is a template parameter. This defines how the column should be displayed if it should not be simply displayed as is.
In this example, all that is happening is that the number is being represented as a percentage. The data contains the information as a floating point number so that a value of 0.05 is displayed as 5%.
Templated values are set between two # markers. After the opening marker you can put an equal sign or colon depending on how you want the value rendered. The = indicates the value is rendered as is, the : indicates that the value is to be HTML encoded before being rendered.
There is a toString function that allows you to format data in various ways. In this example, I’m taking a number and formatting it as a percentage. Like this:
#= kendo.toString(Change, "p") #
Just remember that if you have quotation marks inside your template to escape them if needs be for the code that the template is defined within.
In my previous posts (parts one and two), I showed how to generate mediawiki markup to generate lists of tables and columns that can be used as the starting blocks of a data dictionary. In this post I continue that by showing how to generate a list of inbound links (i.e. a list of other tables that reference this one)
The script
At the top of the script are three parameters that you need to set to suit your needs. The @catalogue is the name of the database, the @schema and @table are the names of the destination schema and table.
-- Edit these parameters to suit your needs.
DECLARE @catalogue SYSNAME = 'AdventureWorks';
DECLARE @schema SYSNAME = 'Production'
DECLARE @table SYSNAME = 'Product'
DECLARE @fk_catalogue SYSNAME;
DECLARE @fk_schema SYSNAME;
DECLARE @fk_table_name SYSNAME;
DECLARE @fk_column_name SYSNAME;
SET NOCOUNT ON
PRINT '{| class="wikitable sortable"
|-
! scope="col" | Schema
! scope="col" | Table
! scope="col" | Column
! scope="col" class="unsortable" | Notes
'
DECLARE fk_cursor CURSOR FOR
SELECT FK.TABLE_CATALOG, FK.TABLE_SCHEMA, FK.TABLE_NAME, CU.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
WHERE PK.TABLE_SCHEMA = @schema
AND PK.TABLE_NAME = @table
AND PK.TABLE_CATALOG = @catalogue
ORDER BY FK.TABLE_SCHEMA, FK.TABLE_NAME, CU.COLUMN_NAME
OPEN fk_cursor
FETCH NEXT FROM fk_cursor
INTO @fk_catalogue, @fk_schema, @fk_table_name, @fk_column_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '|-
| '+ @fk_schema+'
| [[' + @fk_catalogue + '.'+@fk_schema+'.'+@fk_table_name + '|'+ @fk_table_name+']]
| '+ @fk_column_name + '
|
'
FETCH NEXT FROM fk_cursor
INTO @fk_catalogue, @fk_schema, @fk_table_name, @fk_column_name
END
PRINT '|}'
CLOSE fk_cursor;
DEALLOCATE fk_cursor;
SET NOCOUNT OFF
How it renders
This is how the resulting mediawiki code renders in the browser.
In my previous post, I showed how to create a SQL Script that generates Mediawiki mark-up to create a list of tables (or views) in your database. In this post, I’ll continue with generating a list of columns.
Script walk through
At the top of the script are three variables used to define which table (or view) from which we want the columns. The @catalogue is the name of the database, the @schema and @table are the schema name (often simply ‘dbo’) and the table or view name, respectively.
After a little housekeeping (defining all the variables we’ll be using later) we output the head of the wiki table. This includes the instructions that it is to be sortable, and on which columns the sort is happening.
Then we open our first cursor. The table_cursor walks through all the columns in the table.
Inside the loop of the table_cursor, we create a constraint_cursor to get the constraints for each column by building up the @constraints variable with the details which will be put on the wiki table later.
After the that, a new cursor (pk_cursor) is set up to find the primary key end points of the foreign key column that is being rendered.
Finally, all the information that has been collected is output to the screen.
Once the table_cursor is over the mark-up for closing off the wiki table is output.
How it renders
The wiki mark-up looks like this:
{| class="wikitable sortable"
|-
! scope="col" | Column Name
! scope="col" | Ordinal Position
! scope="col" | Is Nullable
! scope="col" | Data type
! scope="col" | Constraints
! scope="col" | FK Endpoint
! scope="col" class="unsortable" | Notes
|-
| ProductID
| 1
| NO
| int
| PRIMARY KEY
|
|
|-
We’ve just installed MediaWiki as a documentation tool where I work. To get things up and running as quickly as possible, I created a few SQL Scripts to pull out schema information from the SQL Server in order to create a data dictionary. The SQL Scripts produce WikiMedia mark up that allows various things to be cross referenced in a way that makes navigating around the wiki fairly easy.
Getting a list of tables
The first thing to do is to get a list of tables. In the code below, the first line is the only one you’ll need to modify if you want to accept the output in its current format. The first line determines whether you are getting back a list of tables or a list of views.
SET NOCOUNT ON ensures that we don’t get any messages midway through saying things like “(504 row(s) affected)”
The first print statement simple outputs the header for the table in wiki markup. We are also marking certain columns on the table as sortable. That way the user can sort the table to the way they want it rather than the way it was initially rendered.
Then we head into the cursor (as we will be looping over each to the tables in turn outputting wiki mark up for each of them).
In each loop we are outputting various details about the tables. The mark up includes links to a wiki page that will contain more detail about the specific table. Although the rendered wiki table only shows the table name, the underlying link ensures a fully qualified name is used so that you don’t run into clashes between databases or schemas.
Finally, when the loop created by the cursor is complete we put in the closing markup for the wiki table.
There are a few more things going on, but I thought I’d just concentrate on the essentials and not go into a tutorial on how cursors work or anything like that
How it renders
The script renders Wiki Markup, which the MediaWiki engine renders as a table. Using the Adventure Works database as an example, this is what the end result looks like.
The Blog of Colin Mackay 