The below snippet will list all check constraints for a given table in SQL Server:
INNER JOIN Sys.objects O
on S.schema_id = O.schema_id
INNER JOIN Sys.default_constraints DC
ON O.object_id = dc.parent_object_id
S.name = 'SCHEMA_NAME'
O.name = 'TABLE_NAME'
A few years back when I first discovered Reddit, I found it to be a place full of insight and interesting content.
As someone that was used to traditional bulletin boards, the idea that most valuable content appeared at the top of a comment thread actually blew my mind.
Rather than the comments all being treated as equal, and just being displayed in chronological order, they were instead shown in the order that the community decided.
I loved the fact that someone might create a thread that linked to some content, and then the top voted comment was usually someone who was an expert in that field and could shed more light on the content. Users could then collaborate with them and garner more information.
The community decides a comment’s value through a system of upvoting and downvoting.
Here’s the problem – what one user thinks is upvote worthy, another user may not think is upvote worthy. Now this isn’t a big problem for smaller communities – e.g. a subreddit with a few hundred subscribers about something specific, like a particular model of a car. This is because it’s users all have a shared interest and will generally agree on what should be upvoted and what shouldn’t
When a community has a broader appeal (e.g. news, or funny images), this model is flipped on it’s head. There is no longer a united community present. The community has fewer common interests. So what content will appeal to most users? Something simple usually. Something that is quick to read and easy to understand. This usually is an attempt at being funny or witty, and might make you chuckle for a moment, but will not add any more insight into the subject at question. These will get the highest number of upvotes, leaving anything of genuine insight or value to sink to the bottom and drown in a sea of pointless remarks.
This can be illustrated very easily. Any thread on a popular subreddit will generally take the following format:
1. Thread posted linking to an image or article
2. Top voted comment is a statement of under 20 words attempting to be funny.
3. A deep thread of unfunny attempts at being witty are anchored to the top voted comment, as other redditors eagerly jump in an attempt at getting a few upvotes.
This breaks the upvote and downvote model. The top voted comment no longer adds any real value to the discussion, but instead distracts you. Comment voting systems are now bringing the noise back, and in a much worse way than chronological comments ever did. In wide appealing communities, they are a race to the bottom. The user that can write the most unintelligent, slapstick comment in the least amount of words wins the fake internet points.
You now need to scroll down through the comments and hunt for the points of genuine insight that actually add value to the discussion.
As the community collectively gets more and more unintelligent, this problem gets worse. Users will even downvote comments or linked articles that are factually accurate simply because they dislike it. This community is now a horrible place to be.
It isn’t just a Reddit problem. Upvoting systems have been bolted onto many discussion mediums as the internet woke up to their auto noise filtering benefits.
The comment thread is a festival of eye rolling idiocy, as the idiots take over and have an idiotic circlejerk free-for-all of bullshit, probably whilst on their lunch breaks at work. We can easily put the comments into 4 categories:
1. Slaptick attempts at being funny in as few words as possible
Thanks – your comment is funny but adds no value. 40 Upvotes at the time of writing.
2. Idiotically wild conspiracies that miss the point by a few hundred planets
This comment very misinformed, claiming that the whole episode is an attempt by Tesco to get their corporation tax bill down. Let’s just go over that for a minute – Tesco’s deliberately wiped off £6 Billion from their share price and suspended lots of senior managers, so that they could avoid tax? Oh and by the way, if you make more profit you pay more corporation tax.
101 upvotes at the time of writing. So thats at least 101 people that have read the comment and had some sort of belief in it. The upvote count is telling you that this comment has some sort of value. It doesn’t. It’s worthless.
3. Comments that have nothing to do with the current discussion
76 upvotes for the person telling us about their food shopping bill. Thanks. This comment reminds me of the “I brought my own mic!” line from a classic Simpsons episode.
Sitting there, smugly telling us irrelevant information that we don’t need to know. It’s almost spam.
4. Comments that actually contribute to the current discussion
And there we have it – this comment spurred a valuable discussion thread related to the content of the topic. 18 measly upvotes.
How do we fix this?
1 – Turn off comments
One of the fixes is to remove comments. On a news article that states factual information about something that has happened, how much value can the community really add? Well, I think the community can always add value and insight, especially for those that will want to dig deeper into a particular subject or story, so I wouldn’t like to see this happen.
2 – Filter the noise in a better way
I’ve got a few ideas how you could filter the noise out of discussion threads.
Rather than simply upvoting or downvoting, why don’t we apply a tag instead? Upvoting just feels too broad – if you upvote something it may be because you find it funny, because you agree with it, or because you found it insightful. So what if you could drag an icon onto a comment that represented how you felt about it instead?
So looking at our first comment from above:
This would have 40 “funny” tags instead of 40 upvotes.
Everything is late. Even my new year promises are late – it’s nearly February after all. My excuse is that I’ve been Down Under since new years day. It was an awesome trip but I returned with some pretty heavy jet lag. I’ve never felt such debilitating exhaustion before.
So, now for my new years promises. I realise that I’ve been seriously neglecting this blog, but this year I will be a lot more active on it. I also have a new version of this site that I began working on last year that I will finish and get up quite soon.
Once you’ve got your sitemap correctly configured and setup, you will be looking to use some of the features of a Sitemap – for example, breadcrumbs. In this post we will start with an empty MVC3 project, and add the asp.net sitemap provider, a few controller actions and corresponding views, and we will have the provider produce some breadcrumbs for us.
Lets start from an Empty MVC3 project. Fire up Visual Studio. Create a new Asp.Net MVC3 Web application. I’m going to call mine SitemapDemo:
For the sake of this demonstration, I have selected an empty template and have chosen Razor as my view engine.
Now before we go any further, lets go ahead and install the NuGet package. Select View > Other Windows and then select “Package Manager Console”:
This will then dock the Package Manager Console somewhere into your view. In order to add the Asp.net MVC sitemap provider to the current project, we need to enter the following command into the Package Manager Console, and hit enter:
PM> Install-Package MvcSiteMapProvider
This command will then download the necessary files (dlls, cshtml files) and add them into your MVC project. This could take a few minutes depending on your connection. If this has been successful, your Package Manager Console should give you an output similar to the following:
As we’re using Razor as our view engine, we can go ahead and delete the asax files that have been added to the SitemapDemo > Views > Shared > DisplayTemplates folder. Here’s how your solution should now look:
Now that’s the install over. Let’s add a half decent set of controller actions and views to the site before we go on to playing with the SiteMapProvider. The point of this is to capture the kind of structure that you would find in a typical website.
The MVC Sitemap provider will fail silently in some fashion if we try to force it to work with controller actions that either don’t exist or that point to non existent views. This is why we are doing this stage first.
All sites have a homepage, so lets add this first. Right click on your Controllers folder, and add a controller called “HomeController”. Lets leave the scaffolding options blank:
Once your controller is created and open, right click inside the Index action and select “Add View…”
In the Add View dialogue that pops up, just go a head hit add. Nothing will need changing. Now lets change the text inside the created page’s h2 tag on the page – something like “Index – this is the home page” will do.
And now lets add another controller in the same way that we added the HomeController. Let’s call it NewsController. Update the newly created news controller to contain an additional action result called “Sports”:
public class NewsController : Controller
// GET: /News/
public ActionResult Index()
// GET: /News/Sports/
public ActionResult Sports()
Now, lets add a view for each of our newly created NewsController actions. Lets do this in the same way that we added the view for the home page – by right clicking within the body of each controller action. Again, we can simply leave all of the defaults in the “Add View” dialogue and just hit “Add” for both views.
Now edit the h2 tag on the News Index cshtml file to read “News Index”. Lets also edit the h2 tag on the News Sports cshtml file to read “Sports News”.
Let’s now add one more Controller for illustration – AboutController. Once created, you can leave the controller unchanged, and can add a view for the Index controller action. This time, lets change the h2 to tags to read “About Page”.
As we have now added 4 pages to our website, it’s now worth just testing them out before we start integrating the Site Map Provider. Hit the debug button – below are screen shots and their corresponding URLs:
Ok, so we’ve now got a small site with a little bit of a structure. Lets represent that structure in an easy diagram:
Visualising our layout like this will really help us describe our site’s structure in our Mvc.sitemap file correctly. Our Index page is our wrapper for the entire site as it is the page that sits in the root, and is the first port of call into the site.
Now lets get into configuring our Sitemap. Lets start by editing our Mvc.sitemap file, which is in the root of our project. This file contains all of the xml nodes needed to represent your controller and action combinations.
Edit your Mvc.Sitemap file so that it is the same as the listing below:
We have now represented our website structure / workflow in the MVC.Sitemap file. A classic “gotcha” here is forgetting that your entire site is wrapped in a node that represents your homepage. Your sitemap file must contain this node – after all, your website’s homepage is the page that the client sees as the root of everything. So even though the Index action is actually at yourwebsite/Index, the client will typically see it just as yourwebsite/. The rest of the structure should make sense when compared to the website navigation diagram, earlier in this post.
Now that we’ve got some controllers and actions setup, and our site structure described properly in our Mvc.Sitemap file, lets add some navigation to all pages.
Open up your _Layout.cshtml partial, located in the Views/Shared folder. Update the listing so that the code between the body tags looks like this:
We are now calling the MvcSiteMap library and telling it to output the website’s navigation on every page. The parameters specified mean that:
We don’t want it to start from the current node (changing this to true will break it!)
We want the starting node to appear in child level
We want to show the starting node. Setting this to false will hide the parental “Home” node
And now if we run our application, we should see the navigation laid out on every page, complete with links:
Editing the navigation appearance
So now we’ve managed to output a simple navigation onto all pages of our website. If you want to change any styling, or how the navigation is displayed, simply alter the code in Views/Shared/DisplayTemplates/MenuHelperModel.cshtml. Lets make a simple change and add an inline style to change our bullet points from circles to squares:
You can now hit refresh in your browser without needing to re-compile. Your News index page should now look like this:
We can add breadcrumbs in a similarly easy fashion. Let’s open up our _Layout.cshtml partial and edit the listing:
@Html.MvcSiteMap().Menu(false, true, true)
<p>Start of Breadcrumbs:</p>
Now all pages on our site will have a handy set of breadcrumb links:
Similarly, if we want to customise the presentation of our breadcrumbs, we need to change the Views/Shared/DisplayTemplates/SiteMapPathHelperModel.cshtml file.
Dynamic URLs / Parametered URLs
Every real site will need to employ a dynamic / Parametered URL at some point. Incorporating a dynamic URL into the MVC Sitemap is straightforward when you know how. Lets start by adding a new action to the NewsController:
Now lets add a view – right click anywhere within the new action and select “Add View…”. Again, just hit Add – we don’t need to change any of the options. Now update the newly created Article.cshtml file with the following:
Now lets browse to localhost:xxxx/News/Article/1234:
Note that this new page does not appear anywhere in our sitemap, and that the breadcrumbs are totally empty.
In order to add the page into our navigation, we must first decide where it needs to go. I’d like this page to sit underneath the News section. So lets edit our Mvc.Sitemap file and add a Key attribute to the “News” node. This is simply to give it a unique identifier:
The ASP.net MVC sitemap provider is a solution aiming to provide your website with a fully functioning set of sitemap tools, such as breadcrumbs and node navigation.
When delving into using this solution, I made the huge mistake of first not reading up about ASP.net sitemaps in general. If you do not have any previous experience in using sitemaps on an ASP.net website, I would strongly recommend reading this MSDN article on ASP.net sitemaps. Many of the principles used in the ASP.net MVC framework are the same and reading this article will give you some fundamental basic principles.
Once you get the ASP.net MVC sitemap provider downloaded and registered, you should see that a sitemap file (Mvc.sitemap) has been created in your MVC solution’s root:
Next, you need to go about editing your MVC.sitemap file so that it actually reflects the pages on your web site that are available to the user. This is the important bit – if you don’t get this right, the sitemap provider will not work as expected. All nodes in your file must:
Be wrapped in an overall parent node representing the home page of your website. After all, the homepage is always the first point of call into any web application
Point to REAL controllers and REAL actions. If you fill up your MVC.sitemap file with dummy nodes pointing to non existent controllers and actions, asp.net MVC site provider will not return these nodes in any rendering of the sitemap. You will not get an error message, but will just not see your nodes.
Not point to a controller and an action that is already pointed to by a previous node. Again, the asp.net MVC sitemap provider will just remove any duplicate instances at runtime. You will not get an error message, but will just not see your nodes.
Here is an example of an MVC.sitemap file that is in a good format and is readable to the ASP.net MVC sitemap provider (apologies if the spacing has not worked correctly):
Now, all you need to do is get the call to display your menu correct. If you get this wrong, you will end up displaying nodes that you don’t want to display. Here’s the call that worked for me on a file in a Mvc.Sitemap file that is essentially the same as the code listing above. This is in Razor syntax:
@Html.MvcSiteMap().Menu(false, true, false)
This call is telling the MvcSiteMap to display a menu not starting from the current node (the page the client is currently viewing), starting from the first child node, and hiding the overall starting node. This call has worked for me, but you may wish to tweak it depending on what exactly you want.
I will shortly put up a post about customising the way that the ASP.net MVC Sitemap provider displays a menu or breadcrumb trail.
A more in-depth tutorial is now available from here. It covers starting from scratch, displaying navigation, breadcrumbs, and customising their appearance.