Using a Recursive CTE to Build Paths

Here’s the scenario. You have a hierarchical data set like categories, folders, etc. and you need to output the entire path for each.

Let’s say we take the category example and we have a table that looks like:

CREATE TABLE [dbo].[Category](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[ParentCategoryId] [int] NULL,
    -- normal PK script removed to keep this short
) 

To get the full path for each category, the SQL would look like:

WITH CTE_CategoriesWithPaths AS
( 
    SELECT 
        c.Id, 
        c.Name, 
        c.ParentCategoryId, 
        c.Name AS [Path] 
    FROM Category c 
    WHERE c.ParentCategoryId IS NULL 
    UNION ALL
    SELECT 
        c.Id, 
        c.Name, 
        c.ParentCategoryId,
        cte.[Path] + '/' + c.Name AS [Path] 
    FROM 
        Category c 
        INNER JOIN CTE_CategoriesWithPaths cte 
            ON c.ParentCategoryId = cte.Id
    WHERE c.ParentCategoryId IS NOT NULL 
)
SELECT * 
from CTE_CategoriesWithPaths

SQL Saturday Baton Rouge 2015

SQL Saturday Baton Rouge 2015 has opened registration! Please visit the website and sign up if you’re interested.

If you’re interested in development at all this is a must attend event. The sessions span all areas of development. As the site says:

Folks with the following skillsets are drawn to SQL Saturday Baton Rouge because of the professional networking, free training, and giveaways:

SQL Server Administrators
Business Intelligence Developers
Data Analysts
ETL Developers
C#/VB.NET Developers
Mobile Developers
Windows Server Admins
SharePoint Architects
SharePoint Developers
Network Administrators
Quality Assurance Analysts
IT Managers
Project Managers
Students

They also have a call for speakers going on and I’m still trying to decide if I’m going to submit a session.

ASP.NET Forms Disabled CSS for BootStrap

The following CSS is to handle Enabled set to true in any WebControl.

/* -- Bootstrap Additions - ASP.NET Disabled ----------------- */

input[type="radio"].aspNetDisabled,
input[type="checkbox"].aspNetDisabled,
.radio-inline.aspNetDisabled,
.checkbox-inline.aspNetDisabled,
.radio.aspNetDisabled label,
.checkbox.aspNetDisabled label
{
    cursor: not-allowed;
}

.btn.aspNetDisabled
{
    pointer-events: none;
    cursor: not-allowed;
    filter: alpha(opacity=65);
    -webkit-box-shadow: none;
    box-shadow: none;
    opacity: .65;
}

.btn-default.aspNetDisabled,
.btn-default.aspNetDisabled:hover,
.btn-default.aspNetDisabled:focus,
.btn-default.aspNetDisabled:active,
.btn-default.aspNetDisabled.active
{
    background-color: #fff;
    border-color: #ccc;
}

.btn-primary.aspNetDisabled,
.btn-primary.aspNetDisabled:hover,
.btn-primary.aspNetDisabled:focus,
.btn-primary.aspNetDisabled:active,
.btn-primary.aspNetDisabled.active
{
    background-color: #428bca;
    border-color: #357ebd;
}

.btn-success.aspNetDisabled,
.btn-success.aspNetDisabled:hover,
.btn-success.aspNetDisabled:focus,
.btn-success.aspNetDisabled:active,
.btn-success.aspNetDisabled.active
{
    background-color: #5cb85c;
    border-color: #4cae4c;
}

.btn-info.aspNetDisabled,
.btn-info.aspNetDisabled:hover,
.btn-info.aspNetDisabled:focus,
.btn-info.aspNetDisabled:active,
.btn-info.aspNetDisabled.active
{
    background-color: #5bc0de;
    border-color: #46b8da;
}

.btn-warning.aspNetDisabled,
.btn-warning.aspNetDisabled:hover,
.btn-warning.aspNetDisabled:focus,
.btn-warning.aspNetDisabled:active,
.btn-warning.aspNetDisabled.active
{
    background-color: #f0ad4e;
    border-color: #eea236;
}

.btn-danger.aspNetDisabled,
.btn-danger.aspNetDisabled:hover,
.btn-danger.aspNetDisabled:focus,
.btn-danger.aspNetDisabled:active,
.btn-danger.aspNetDisabled.active
{
    background-color: #d9534f;
    border-color: #d43f3a;
}

.dropdown-menu > .aspNetDisabled > a,
.dropdown-menu > .aspNetDisabled > a:hover,
.dropdown-menu > .aspNetDisabled > a:focus,
.dropdown-menu > li > a.aspNetDisabled,
.dropdown-menu > li > a.aspNetDisabled:hover,
.dropdown-menu > li > a.aspNetDisabled:focus
{
    color: #777;
}

.dropdown-menu > .aspNetDisabled > a:hover,
.dropdown-menu > .aspNetDisabled > a:focus,
.dropdown-menu > li > a.aspNetDisabled:hover,
.dropdown-menu > li > a.aspNetDisabled:focus
{
    text-decoration: none;
    cursor: not-allowed;
    background-color: transparent;
    background-image: none;
    filter: progid:DXImageTransform.Microsoft.gradient(enabled = false);
}

 

ASP.NET Membership Log Out

My boss likes to say “never use the words ‘simple’ or ‘easy’ in our line of work” and today was one of those days that demonstrates exactly why he loves this saying.

We were asked to setup an auto logout feature that redirects to the login screen. I’ve done so much Windows Authentication work that I’ve never actually had to build this functionality. I went straight to my favorite search engine and I found the following code.

FormsAuthentication.SignOut();
Session.Abandon();
FormsAuthentication.RedirectToLoginPage();

Everyone was commenting about how this worked great and lo and behold it wasn’t working. More research and I found an article that explained that the above neglected to clear cookies sometimes and that to 100% ensure a sign out you should clear the forms authentication and session cookies.

The following code is what I ended up using in our application. It only expires the cookies that are forms authentication and session related.

FormsAuthentication.SignOut();
Session.Abandon();

var cookies = new List<string>
{
    "ASP.NET_SessionId", 
    FormsAuthentication.FormsCookieName, 
    ".ASPXROLES"
};

foreach (var cookie in cookies)
{
    if (Request.Cookies.AllKeys.Contains(cookie))
    {
        Request.Cookies[cookie].Expires = DateTime.Now.AddYears(-1);
    }
}

FormsAuthentication.RedirectToLoginPage();