The Technology Aces blog is a consolidation of the personal blog posts from the individuals that help make Technology Aces the truly elite IT professionals our clients have come to recognize and depend upon.
For more information about the individual authors please follow the links to their blog. Links can be found in the post details section of each post or in the sidebar navigation located on the left side of this page.
Common table expressions (CTEs) provide the significant advantage of being able to reference themselves, making recursion far simpler then earlier versions of SQL Server. CTEs consist of three parts: a name, an optional column list and a query. A recursive query also consists of three parts: an anchor member, a recursive member and a termination check. In the case of recursive CTEs, the termination check is implicit; recursion stops when no rows are returned from the previous invocation. This leaves only anchor member and recursive member for further discussion. The anchor member is relatively simple, this is your base query; think starting point; hierarchy level 1. The recursive member is a bit more tricky. I try to first compose a query which would return hierarchy level 2. Once I am happy with the hierarchy level 1 query (the anchor member) and the hierarchy level 2 query (what will become the recursive member), I construct the CTE.
A CTE must either be the first command executed or follow a command that is terminated with ";". So I have just become accustom to prefixing the CTE declaration with ";":
1: ; WITH CTE_NAME [ ( COLUMN_NAME [,...n] ) ] AS (
2:
3: HIERARCHY_LEVEL_1_QUERY
4:
5: UNION ALL
6:
7: HIERARCHY_LEVEL_2_QUERY
8: INNER JOIN CTE_NAME
9: ON COLUMN_NAME = CTE_NAME.COLUMN_NAME
10: )
11: SELECT *
12: FROM CTE_NAME
The INNER JOIN statement should be the relationship of PARENT = CURRENT (the CTE is always the current hierarchy level) and the UNION ALL in effect iterates through all of the CHILDREN (the remainder of the hierarchy levels).
okay, so your happy coding in css compliant world and then you remember … IE is not css compliant … but you’ve just written some code utilizing the "position: fixed;" property and value … you check IE 7 … all good … then IE 6 … rut roh raggy … enter conditional comments … yea it’s a wee bit hacky … but sometimes non compliant browsers require non compliant solutions … the trick here is the use of conditional comments to target the browser then use IE specific css properties and values like overflow-y (however css3 includes this property) and expression along with the so important !important declaration …
for example:
1: <!--[if lt IE 7]>
2: <style type="text/css">
3: body {
4: overflow: hidden;
5: }
6: #YOUR_MAIN_BODY_BLOCK_TAG {
7: height: expression(document.body.clientHeight + "px") !important;
8: overflow-y: scroll;
9: overflow-x: auto;
10: }
11: #YOUR_FIXED_BLOCK_TAG {
12: position: absolute !important;
13: }
14: </style>
15: <![endif]-->
also, ensure that #YOUR_FIXED_BLOCK_TAG is not a child tag of #YOUR_MAIN_BODY_BLOCK_TAG … they should be siblings …
Implement a Microsoft approved strategy with regards to the usage of a sitemap provider allowing for dynamic updates without requiring a restart of the web application.
The terms below are intended to provide a basic understanding of common terms and procedures used throughout the remainder of this document.
MSDN - Inside the ASP.NET Team: ASP.NET’s XmlSiteMapProvider goes to the extra trouble of monitoring the site map file and reloading it if it changes. If site map data is stored in a Microsoft SQL Server database, consider using ASP.NET 2.0’s SqlCacheDependency class to monitor the database for changes.
My initial implementation for my application’s site map was the SQL Site Map Provider as identified in section 3.3 (without the utilization of the SQL Cache Dependency class). My initial thought was that additional queries to the database for the site navigation would not impede the performance of the individual pages. It is ironic that this very assumption is what caused my production issue of missing nodes at what seemed to be irregular intervals. Since the SQLSiteMapProvider inherits from the StaticSiteMapProvider, the site map data source is static and is cached by the application. To accommodate for this, the SQLSiteMapProvider provides methods for clearing and querying the site map data source, similar to the XMLSiteMapProvider. These APIs will be entered by many worker threads in parallel in order to serve requests. Clearing the data at the same time the data is being queried is dangerous and prone to causing intermittent failures. By introducing the SQLDependancyCache class, these processes are isolated.
i http://msdn.microsoft.com/en-us/asp.net/aa336558.aspx
ii http://msdn.microsoft.com/en-us/library/aa479033.aspx
iii http://msdn.microsoft.com/en-us/library/aa479033.aspx#aspnetprovmod_prt3_topic1
iv http://msdn.microsoft.com/en-us/library/aa479033.aspx#aspnetprovmod_prt3_topic4
v http://msdn.microsoft.com/en-us/library/system.web.caching.sqlcachedependency.aspx
vi http://msdn.microsoft.com/en-us/library/ms178604.aspx#sectionToggle0
vii http://msdn.microsoft.com/en-us/library/system.web.xmlsitemapprovider.aspx
viii http://msdn.microsoft.com/en-us/library/aa479338.aspx#extndsi_topic6
ix http://msdn.microsoft.com/en-us/library/aa479033.aspx#aspnetprovmod_prt3_topic5
x http://msdn.microsoft.com/en-us/magazine/cc163787.aspx
In the past, when building small web applications with up to a couple concurrent users, I’ve placed the database on the same system as the web server. Blasphemy!!!…not really. You see, knowing that database systems will entirely consume their allocated memory allows for clear capacity planning.
There are many factors that need to be taken into consideration when designing an architecture and for the sake of this post lets just say that:
And really, while every project sponsor will tell you that their project requires the most powerful server ever built and for the sake of humanity can never go offline again, most web applications don’t require all that much memory…and the fate of the company is not dependant on 100% uptime…just check the SLA.
Ok, ok, just one more paragraph to provide context.
Large web applications with high concurrent user counts will commonly separate not only content types across systems but also application modules. For example, there might be a small web server cluster for images or a separate group of servers for the account management modules (login, register, profile). These practices are commonly accepted and I couldn’t agree with them more. Its just that most of the applications I am involved with don’t require this type of advanced architecture even though the project sponsor would have you believe otherwise. These, not the large web applications, are those we need to discuss..
So what does this all have to do with “Content Segregation”? Oh, and WTF is “Content Segregation”?
Until recently performance and availably have been the only reasons I’ve recognized to separate content across physical servers. That is until just very recently, in a meeting of the minds it was stated, as fact, that “Items of content type X are data and should be stored on the database server”
WTF? Let me clarify, not IN the database, but on the database server file system..
Ok, I have my head around what you are saying, I understand that your an advocate of Content Segregation, I just don’t understand why, but I can tell you why not.
1. Performance
Database servers are commonly separate physical systems for performance reasons. The DB server should not be a dumping ground of user generated content. It might seem silly but there is a chance that you might really use the SQL server for something that requires its full power. Maybe some form of analytics and when that happens you need to have the available power.
2. Slippery Slope
I truly hate this argument but it fits so perfect into this context. Its not so far of a leap to go from storing images on the database file system to storing images in the database.
My good friend Jamey’s first blog post is about helping himself. Well…I just did the same on a post about using the ESRI JavaScript API to zoom to a polygon so I thought it best to provide him with a ping back…
you can modify the .net sql role provider to be hierarchical in just a few simple steps …
first you need to modify the aspnet_Roles table:
1: ALTER TABLE [dbo].[aspnet_Roles] ADD [ParentRoleId] [uniqueidentifier] NULL
then add two new table value functions:
1: CREATE FUNCTION [tos].[aspnet_Roles_Ancestor_TVF] (
2: @RoleId uniqueidentifier
3: )
4: RETURNS
5: @aspnet_Roles TABLE (
6: ApplicationId uniqueidentifier
7: , RoleId uniqueidentifier
8: , RoleName nvarchar(256)
9: , LoweredRoleName nvarchar(256)
10: , Description nvarchar(256)
11: , ParentRoleId uniqueidentifier
12: )
13: AS
14: BEGIN
15: ; WITH aspnet_Roles_CTE (
16: ApplicationId
17: , RoleId
18: , RoleName
19: , LoweredRoleName
20: , Description
21: , ParentRoleId
22: , HierarchyLevel
23: ) AS (
24: SELECT
25: ApplicationId
26: , RoleId
27: , RoleName
28: , LoweredRoleName
29: , Description
30: , ParentRoleId
31: , 1 AS HierarchyLevel
32: FROM aspnet_Roles
33: WHERE RoleId = @RoleId
34:
35: UNION ALL
36:
37: SELECT
38: aspnet_Roles.ApplicationId
39: , aspnet_Roles.RoleId
40: , aspnet_Roles.RoleName
41: , aspnet_Roles.LoweredRoleName
42: , aspnet_Roles.Description
43: , aspnet_Roles.ParentRoleId
44: , aspnet_Roles_CTE.HierarchyLevel + 1 AS HierarchyLevel
45: FROM aspnet_Roles
46: INNER JOIN aspnet_Roles_CTE
47: ON aspnet_Roles.RoleId = aspnet_Roles_CTE.ParentRoleId
48: )
49:
50: INSERT INTO @aspnet_Roles (
51: ApplicationId
52: , RoleId
53: , RoleName
54: , LoweredRoleName
55: , Description
56: , ParentRoleId
57: )
58: SELECT
59: ApplicationId
60: , RoleId
61: , RoleName
62: , LoweredRoleName
63: , Description
64: , ParentRoleId
65: FROM aspnet_Roles_CTE
66: ORDER BY HierarchyLevel
67:
68: RETURN
69: END
and:
1: ALTER FUNCTION [tos].[aspnet_Roles_Descendant_TVF] (
2: @RoleId uniqueidentifier
3: )
4: RETURNS
5: @aspnet_Roles TABLE (
6: ApplicationId uniqueidentifier
7: , RoleId uniqueidentifier
8: , RoleName nvarchar(256)
9: , LoweredRoleName nvarchar(256)
10: , Description nvarchar(256)
11: , ParentRoleId uniqueidentifier
12: )
13: AS
14: BEGIN
15: ; WITH aspnet_Roles_CTE (
16: ApplicationId
17: , RoleId
18: , RoleName
19: , LoweredRoleName
20: , Description
21: , ParentRoleId
22: , HierarchyLevel
23: ) AS (
24: SELECT
25: ApplicationId
26: , RoleId
27: , RoleName
28: , LoweredRoleName
29: , Description
30: , ParentRoleId
31: , 1 AS HierarchyLevel
32: FROM aspnet_Roles
33: WHERE RoleId = @RoleId
34:
35: UNION ALL
36:
37: SELECT
38: aspnet_Roles.ApplicationId
39: , aspnet_Roles.RoleId
40: , aspnet_Roles.RoleName
41: , aspnet_Roles.LoweredRoleName
42: , aspnet_Roles.Description
43: , aspnet_Roles.ParentRoleId
44: , aspnet_Roles_CTE.HierarchyLevel + 1 AS HierarchyLevel
45: FROM aspnet_Roles
46: INNER JOIN aspnet_Roles_CTE
47: ON aspnet_Roles.ParentRoleId = aspnet_Roles_CTE.RoleId
48: )
49:
50: INSERT INTO @aspnet_Roles (
51: ApplicationId
52: , RoleId
53: , RoleName
54: , LoweredRoleName
55: , Description
56: , ParentRoleId
57: )
58: SELECT
59: ApplicationId
60: , RoleId
61: , RoleName
62: , LoweredRoleName
63: , Description
64: , ParentRoleId
65: FROM aspnet_Roles_CTE
66: ORDER BY HierarchyLevel
67:
68: RETURN
69: END
then you’ll need to modify two stored procedures:
1: ALTER PROCEDURE [tos].[aspnet_UsersInRoles_IsUserInRole]
2: @ApplicationName nvarchar(256),
3: @UserName nvarchar(256),
4: @RoleName nvarchar(256),
5: @Exclusive bit = 0
6: AS
7: BEGIN
8: DECLARE @ApplicationId uniqueidentifier
9: SELECT @ApplicationId = NULL
10: SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
11: IF (@ApplicationId IS NULL)
12: RETURN(2)
13: DECLARE @UserId uniqueidentifier
14: SELECT @UserId = NULL
15: DECLARE @RoleId uniqueidentifier
16: SELECT @RoleId = NULL
17:
18: SELECT @UserId = UserId
19: FROM tos.aspnet_Users
20: WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
21:
22: IF (@UserId IS NULL)
23: RETURN(2)
24:
25: SELECT @RoleId = RoleId
26: FROM tos.aspnet_Roles
27: WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
28:
29: IF (@RoleId IS NULL)
30: RETURN(3)
31:
32: /*
33: IF (EXISTS( SELECT * FROM tos.aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId))
34: RETURN(1)
35: ELSE
36: RETURN(0)
37: */
38:
39: IF @Exclusive = 1
40: BEGIN
41: IF (EXISTS( SELECT * FROM tos.aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId))
42: RETURN(1)
43: ELSE
44: RETURN(0)
45: END
46: ELSE
47: BEGIN
48: IF( EXISTS( SELECT * FROM tos.aspnet_Roles_Ancestor_TVF( @RoleId ) r INNER JOIN tos.aspnet_UsersInRoles ur ON r.RoleId = ur.RoleId WHERE ur.UserId = @UserId ) )
49: RETURN(1)
50: ELSE
51: RETURN(0)
52: END
53:
54: END
1: ALTER PROCEDURE [tos].[aspnet_UsersInRoles_GetRolesForUser]
2: @ApplicationName nvarchar(256)
3: , @UserName nvarchar(256)
4: , @Exclusive bit = 0
5: AS
6: BEGIN
7: DECLARE @ApplicationId uniqueidentifier
8: SELECT @ApplicationId = NULL
9: SELECT @ApplicationId = ApplicationId
10: FROM aspnet_Applications
11: WHERE LOWER(@ApplicationName) = LoweredApplicationName
12: IF (@ApplicationId IS NULL)
13: RETURN(1)
14: DECLARE @UserId uniqueidentifier
15: SELECT @UserId = NULL
16: SELECT @UserId = UserId
17: FROM tos.aspnet_Users
18: WHERE LoweredUserName = LOWER(@UserName)
19: AND ApplicationId = @ApplicationId
20: IF (@UserId IS NULL)
21: RETURN(1)
22:
23: /*
24: SELECT r.RoleName
25: FROM tos.aspnet_Roles r, tos.aspnet_UsersInRoles ur
26: WHERE r.RoleId = ur.RoleId
27: AND r.ApplicationId = @ApplicationId
28: AND ur.UserId = @UserId
29: ORDER BY r.RoleName
30: */
31:
32: IF @Exclusive = 1
33: BEGIN
34: SELECT r.RoleName
35: FROM tos.aspnet_Roles r, tos.aspnet_UsersInRoles ur
36: WHERE r.RoleId = ur.RoleId
37: AND r.ApplicationId = @ApplicationId
38: AND ur.UserId = @UserId
39: ORDER BY r.RoleName
40: END
41: ELSE
42: BEGIN
43: DECLARE @RoleId uniqueidentifier
44: DECLARE @RoleName nvarchar(256)
45: DECLARE @aspnet_Roles TABLE (
46: RoleName nvarchar(256)
47: )
48:
49: SELECT @RoleName = MIN( r.RoleName )
50: FROM tos.aspnet_Roles r, tos.aspnet_UsersInRoles ur
51: WHERE r.RoleId = ur.RoleId
52: AND r.ApplicationId = @ApplicationId
53: AND ur.UserId = @UserId
54:
55: WHILE @RoleName IS NOT NULL
56: BEGIN
57: SET @RoleId = ( SELECT RoleId FROM tos.aspnet_Roles WHERE RoleName = @RoleName )
58:
59: INSERT INTO @aspnet_Roles (
60: RoleName
61: )
62: SELECT
63: RoleName
64: FROM tos.aspnet_Roles_Descendant_TVF( @RoleId ) rd
65:
66: SELECT @RoleName = MIN( r.RoleName )
67: FROM tos.aspnet_Roles r, tos.aspnet_UsersInRoles ur
68: WHERE r.RoleId = ur.RoleId
69: AND r.ApplicationId = @ApplicationId
70: AND ur.UserId = @UserId
71: AND RoleName > @RoleName
72: END
73:
74:
75: SELECT DISTINCT RoleName
76: FROM @aspnet_Roles r
77: ORDER BY r.RoleName
78: END
79:
80:
81: RETURN (0)
82: END
add the parent unique identifiers as necessary and you’re done
There exists a new feature in Microsoft SQL Server 2005 called Service Broker. With Service Broker, internal or external processes can send and receive guaranteed, asynchronous messaging by using extensions to Transact-SQL.
One way to utilize this feature is by implementing SQLCacheDependency within a SqlSiteMapProvider. Thus allowing the SqlSiteMapProvider to update as changes occur to the SiteMap table.
Here’s a quick snippet to enable this feature within the database:
1: ALTER DATABASE YOUR_DATABASE_NAME SET NEW_BROKER WITH ROLLBACK IMMEDIATE
2: GO
3: ALTER DATABASE YOUR_DATABASE_NAME SET ENABLE_BROKER
4: GO
If you plan to use Service Broker for your own notification queries be sure to follow the rules.
Additionally, the SQL user must either be a member of the db_owner fixed database role for the intended database or the sysadmin fixed server role. I read that the user could alternately belong to the db_ddladmin fixed database role for the intended database, however; I was not able to get this configuration to work.
Lastly, if you are working within a schema other then dbo, the SQL user used to query the Service Broker must be the schema owner, not a SQL role. This has to do with the fact that a role cannot be assigned a default schema, and thus defaults to the dbo schema.
I started off to make a short list of the ASP.NET MVC sites I use often but it quickly turned into a top 10 list. That is until I realized I only visit 9 ASP.NET MVC related sites on a regular basis. So, here is my Top 9..That is until someone suggests another site worthy of my reading.
http://www.asp.net/mvc/default.aspx?wwwaspnetrdirset=1
http://www.hanselman.com/blog/
http://weblogs.asp.net/scottgu/
http://weblogs.asp.net/stephenwalther/
http://bradwilson.typepad.com/
ASP.NET on CodePlex
The ASP.NET MVC releases are posted under the ASP.NET umbrella project.
http://www.codeplex.com/aspnet
http://www.codeplex.com/MVCContrib/Wiki/View.aspx?title=Documentation&referringTitle=Home