Continuous Integration

Monday, December 14, 2009 7:49
Posted in category whitepaper
1 Comment

1. Summary

An important part of any software development process is getting reliable builds of the software. Despite its importance, we are often surprised when this isn’t done. (Fowler, 2006)

Continuous Integration (CI) is the strategy and practice of making sure that changes to a software project’s code base are successfully built, tested, reported on, and rapidly made available to all parties after they are introduced.

2. Business Challenge

The current build process is immature. It is limited to only the code base; requires many manual steps; lacks structured testing; lacks status broadcasting; and introduces more complexities for projects with more than one developer. Ideally, the build process would include not only the code base but anything necessary to deploy to a clean machine; include both unit testing and integration testing; provide status updates to members of the project team; be the same process for all developers regardless of team size; and perform the majority of operations automatically.

3. Solution Description

Continuous Integration is a software development practice where members of a team integrate their work frequently, usually each person integrates at least daily – leading to multiple integrations per day. Each integration is verified by an automated build (including test) to detect integration errors as quickly as possible. Many teams find that this approach leads to significantly reduced integration problems and allows a team to develop cohesive software more rapidly. (Fowler, 2006)

The key practices that make up an effective Continuous Integration (CI) are the following:

  1. Source Repository (SVN): There should be a mainline, a single branch of the project as it is being developed; alternate branches should be kept to a minimum (production bug fixes or temporary experimentation); Everything needed to build the project on a clean machine should be included (i.e. test scripts, install scripts, third party libraries, etc.) and any other useful files (i.e. IDE configurations) can optionally be included.
  2. Build Automation (NAnt): Everything necessary to bring in a clean machine, check the source out of the repository, issue a single command, and have a running system on the machine.
  3. Test Automation (NUnit / VS2008 Test Projects): Run all unit tests; Run all functional / integration tests. The tests should be automated so that a single command executes them, and each must report the results of the test.
  4. Mainline Committal: CI is primarily about communication, allowing developers to tell other developers about the changes they have made. Frequent communication allows people to know quickly as changes develop. So, at a minimum, developers should commit to the mainline once every day. The more frequent the integration the sooner a conflict can be detected. The key to fixing problems quickly is finding them quickly and at a point that not much has happened, therefore they are easy to resolve.
  5. Continuous Integration Server (CruiseControl.Net): A CI server monitors the repository. Each committal initiates the CI server to automatically check out the source, build, test, and notify the committer of the results. The committer isn’t done until notification is received.
  6. Build Time: The whole point of CI is to provide rapid feedback. A ten minute build is perfectly within reason. For builds that take more than 10 minutes investigate implementing a staged build (a.k.a. a build pipeline).
  7. Production Environment: The point of testing is to flush out, under controlled conditions, any problem that the system will have in production. A significant part of this is the environment within which the production system will run. If you test in a different environment, every difference results in a risk that what happens under test won’t happen in production. As a result, you want to set up your test environment to be as exact a mimic of your production environment as possible.
  8. The Latest and Greatest: The resulting code should be available for anyone involved for demonstrations, exploratory testing, or just to see what changed. It’s very hard to specify what you want in advance and be correct; it’s much easier to see something that’s not quite right and say how it needs to be changed.
  9. Information Dissemination: CI is all about communication, so you want to ensure that everyone can easily see the state of the system, how long it’s been in that state and the changes that have been made to it.
  10. Deployment Automation: Although this is merely just the aforementioned steps executing on the desired application server, this is a crucial process, as it eliminates potential human error and, almost as important, provides the ability to perform automated rollbacks.

4. Benefits

  1. Reduced Risk: By integrating many times a day, you can reduce risks on your project. Doing so facilitates the detection of defects, the measurement of software health, and a reduction of assumptions.
  2. Increased Productivity: Often, it takes more time to locate a bug then it does to correct it and the more bugs there are, the more difficult it is to remove each one, due to bug interactions. With CI, the vast majority of integration bugs are known as the build is created; more importantly, the offending code (at least ½ of it) is identified. This dramatically reduces the scope of the search for the bug. Furthermore, as bugs are identified and corrected on a continuous basis, there are fewer bugs in the development process which may impede developers working on other sections of code.
  3. Increased Portability: CI can enable you to release deployable software at any point in time.
  4. Improved Product Timeline: With developers spending more time on coding and less time on investigation, manual testing and building releases combined with the knowledge of which sections of code contain bugs and how many bugs in each, adhering to the project timeline is more easily attainable. Additionally, if adjustments are necessary to the timeline, more information is available to assist with the forecasting.
  5. Increase in Customer Confidence / IT Reputation: With most integration bugs identified, it becomes a decision rather than a mistake to release code with known bugs, resulting in fewer bugs being deployed to production and a more stable product.

5. Technical Specifications

A server environment separate from that of the one housing the application is necessary for the various automation tasks.

6. Target Market

Anyone involved with any application implementing CI, including, but not limited to, developers, business analysts, project managers and customers.

7. Case Studies

8. Additional Resources

  1. (Fowler, 2006): Fowler, M. (2006, 05 01). Continuous Integration. Retrieved 12 14, 2009, from http://martinfowler.com/articles/continuousIntegration.html
  2. SVN: http://subversion.tigris.org/
  3. NAnt: http://nant.sourceforge.net/
  4. NUnit: http://www.nunit.org/index.php
  5. VS2008 Test Projects: http://msdn.microsoft.com/en-us/library/bb385902.aspx
  6. CruiseControl.Net: http://confluence.public.thoughtworks.org/display/CCNET

Tags: , , , , ,

WCF Tutorial

Wednesday, August 12, 2009 10:09
Posted in category HowTo
No Comments

The following is a relatively simple address lookup service that illustrates how to create a loosely coupled WCF service with commonly accepted best practices: SRP; Repository Pattern; Service Layers; and Dependency Injection.

The first step is to identify the data that will need to be exchanged by the service.  Often, it will be necessary / desired to utilize objects for this data exchange.  This is accomplished through the use of Data Contracts.  Here is a sample Data Contact that one might have for an address lookup service:

   1:  <DataContract()> _
   2:  Public Class Address
   3:   
   4:      Private _address As String
   5:      Private _city As String
   6:      Private _state As String
   7:      Private _zip As String
   8:   
   9:      <DataMember()> _
  10:      Public Property Address() As String
  11:          Get
  12:              Return _address
  13:          End Get
  14:          Set(ByVal varAddress As String)
  15:              _address = varAddress
  16:          End Set
  17:      End Property
  18:   
  19:      <DataMember()> _
  20:      Public Property City() As String
  21:          Get
  22:              Return _city
  23:          End Get
  24:          Set(ByVal varCity As String)
  25:              _city = varCity
  26:          End Set
  27:      End Property
  28:   
  29:      <DataMember()> _
  30:      Public Property State() As String
  31:          Get
  32:              Return _state
  33:          End Get
  34:          Set(ByVal varState As String)
  35:              _state = varState
  36:          End Set
  37:      End Property
  38:   
  39:      <DataMember()> _
  40:      Public Property Zip() As String
  41:          Get
  42:              Return _zip
  43:          End Get
  44:          Set(ByVal varZip As String)
  45:              _zip = varZip
  46:          End Set
  47:      End Property
  48:   
  49:  End Class

Now that our data contracts are defined we need to create our Repository interface:

   1:  Public Interface IAddressRepository
   2:      Function ListAddresses(ByVal varAddress As String) As List(Of Address)
   3:  End Interface

and our Repository concrete class:

   1:  Imports System.Data.SqlClient
   2:   
   3:  Public Class AddressRepository_DataServices
   4:      Implements IAddressRepository
   5:   
   6:      Public Function ListAddresses(ByVal varAddress As String) _
   7:                  As List(Of Address) _
   8:                  Implements IAddressRepository.ListAddresses
   9:          Dim _address As Address
  10:          Dim _addressList As List(Of Address) = New List(Of Address)
  11:          Dim _commandText As String
  12:          Dim _connectionString As String = "$SOME_CONNECTION_STRING$"
  13:          Dim _sqlCommand As SqlCommand
  14:          Dim _sqlDataReader As SqlDataReader
  15:   
  16:          Using _sqlConnection As New SqlConnection(_connectionString)
  17:              _sqlCommand = New SqlCommand()
  18:              _commandText = "USP_ADDRESS_SELECT"
  19:              With _sqlCommand
  20:                  .Connection = _sqlConnection
  21:                  .Parameters.AddWithValue("@ADDRESS", varAddress)
  22:                  .CommandText = _commandText
  23:                  .CommandType = CommandType.StoredProcedure
  24:              End With
  25:              _sqlConnection.Open()
  26:              _sqlDataReader = _sqlCommand.ExecuteReader()
  27:              If _sqlDataReader.HasRows Then
  28:                  Do While _sqlDataReader.Read()
  29:                      _address = New Address()
  30:                      With _address
  31:                          .Address = _sqlDataReader("ADDRESS").ToString()
  32:                          .City = _sqlDataReader("CITY").ToString()
  33:                          .State = _sqlDataReader("STATE").ToString()
  34:                          .Zip = _sqlDataReader("ZIP").ToString()
  35:                      End With
  36:                      _addressList.Add(_address)
  37:                  Loop
  38:              End If
  39:          End Using
  40:   
  41:          Return _addressList
  42:      End Function
  43:   
  44:  End Class

This example is relatively simple and as such only has one interface and corresponding concrete class.  Since the Repository is to handle any and all data requests, a typically Repository would have many more. However, as this Repository is complete, we can move on to creating our Service Layer; which will contain all of our business logic.  Again we create both an interface:

   1:  Public Interface IAddressServiceLibrary
   2:      Function Search(ByVal varAddress As String) As List(Of Address)
   3:  End Interface

and concrete class:

   1:  Public Class AddressServiceLibrary
   2:      Implements IAddressServiceLibrary
   3:   
   4:      Private _addressRepository As IAddressRepository = New AddressRepository_DataServices()
   5:   
   6:      Public Function Search(ByVal varAddress As String) As List(Of Address) Implements IAddressServiceLibrary.Search
   7:          Return _addressRepository.ListAddresses(varAddress)
   8:      End Function
   9:   
  10:  End Class

With the business logic taken care of it is time to create our WCF service, consisting of a Service Contract and one or more Operation Contracts.  Once again we create an interface:

   1:  <ServiceContract()> _
   2:  Public Interface IAddressLookupService
   3:      <OperationContract()> _
   4:      Function Search(ByVal varAddress As String) As List(Of Address)
   5:  End Interface

and a concrete class:

   1:  Public Class AddressLookupService
   2:      Implements IAddressLookupService
   3:   
   4:      Private _addressServiceLibrary As IAddressServiceLibrary = New AddressServiceLibrary()
   5:   
   6:      Public Function Search(ByVal varAddress As String) As List(Of Address) Implements IAddressLookupService.Search
   7:          Return _addressServiceLibrary.Search(varAddress)
   8:      End Function
   9:   
  10:  End Class

Our WCF service is now complete.  This sample could be extended to also utilize the Decorator Pattern; however, in the interest of brevity I have opted to not include in this posting.

Tags: , , , , , ,

Passing “Order By” parameters w/o utilizing dynamic sql

Thursday, May 14, 2009 8:10
Posted in category Tips & Tricks
No Comments

Similar to the SELECT and WHERE clauses, the ORDER BY clause can also utilize CASE statements.  Within these CASE statements, the input parameter is evaluated and used to sort the result set as desired.

For example, if you have a table with the following schema:

 1: CREATE TABLE [CONTACT_TX_TB] (
 2: [CONTACT_SYSID] [int] IDENTITY(1,1) NOT NULL
 3: , [TYPE_SYSID] [int] NOT NULL
 4: , [TITLE] [varchar](100) NULL
 5: , [FIRST_NAME] [varchar](100) NULL
 6: , [MIDDLE_NAME] [varchar](100) NULL
 7: , [LAST_NAME] [varchar](100) NULL
 8: , [CREATED] [datetime] NOT NULL
 9: , [CREATED_BY] [int] NOT NULL
 10: , [UPDATED] [datetime] NULL
 11: , [UPDATED_BY] [int] NULL
 12: , [INACTIVE] [bit] NOT NULL
 13: )

 

And the following stored procedure:

 1: CREATE PROCEDURE [CONTACT_SELECT_SP] (
 2: @CONTACT_SYSID int = NULL
 3: , @TYPE_SYSID int = NULL
 4: , @TITLE varchar(100) = NULL
 5: , @FIRST_NAME varchar(100) = NULL
 6: , @MIDDLE_NAME varchar(100) = NULL
 7: , @LAST_NAME varchar(100) = NULL
 8: , @CREATED_FROM datetime = NULL
 9: , @CREATED_TO datetime = NULL
 10: , @CREATED_BY int = NULL
 11: , @UPDATED_FROM datetime = NULL
 12: , @UPDATED_TO datetime = NULL
 13: , @UPDATED_BY int = NULL
 14: , @INACTIVE bit = NULL
 15: )
 16: AS
 17: BEGIN
 18:  SET NOCOUNT ON;
 19:  
 20:  SELECT TOP 5001
 21: [CONTACT_SYSID]
 22: , [TYPE_SYSID]
 23: , [TITLE]
 24: , [FIRST_NAME]
 25: , [MIDDLE_NAME]
 26: , [LAST_NAME]
 27: , [CREATED]
 28: , [CREATED_BY]
 29: , [UPDATED]
 30: , [UPDATED_BY]
 31: , [INACTIVE]
 32:  FROM [CONTACT_TX_TB]
 33:  WHERE CASE
 34:  WHEN @CONTACT_SYSID IS NULL THEN 1
 35:  WHEN [CONTACT_SYSID] = @CONTACT_SYSID THEN 1
 36:  ELSE 0
 37:  END = 1
 38:  AND CASE
 39:  WHEN @TYPE_SYSID IS NULL THEN 1
 40:  WHEN [TYPE_SYSID] = @TYPE_SYSID THEN 1
 41:  ELSE 0
 42:  END = 1
 43:  AND CASE
 44:  WHEN @TITLE IS NULL THEN 1
 45:  WHEN [TITLE] LIKE @TITLE + '%'THEN 1
 46:  ELSE 0
 47:  END = 1
 48:  AND CASE
 49:  WHEN @FIRST_NAME IS NULL THEN 1
 50:  WHEN [FIRST_NAME] LIKE @FIRST_NAME + '%'THEN 1
 51:  ELSE 0
 52:  END = 1
 53:  AND CASE
 54:  WHEN @MIDDLE_NAME IS NULL THEN 1
 55:  WHEN [MIDDLE_NAME] LIKE @MIDDLE_NAME + '%'THEN 1
 56:  ELSE 0
 57:  END = 1
 58:  AND CASE
 59:  WHEN @LAST_NAME IS NULL THEN 1
 60:  WHEN [LAST_NAME] LIKE @LAST_NAME + '%'THEN 1
 61:  ELSE 0
 62:  END = 1
 63:  AND CASE
 64:  WHEN @CREATED_FROM IS NULL THEN 1
 65:  WHEN [CREATED] >= @CREATED_FROM THEN 1
 66:  ELSE 0
 67:  END = 1
 68:  AND CASE
 69:  WHEN @CREATED_TO IS NULL THEN 1
 70:  WHEN [CREATED] <= @CREATED_TO THEN 1
 71:  ELSE 0
 72:  END = 1
 73:  AND CASE
 74:  WHEN @CREATED_BY IS NULL THEN 1
 75:  WHEN [CREATED_BY] = @CREATED_BY THEN 1
 76:  ELSE 0
 77:  END = 1
 78:  AND CASE
 79:  WHEN @UPDATED_FROM IS NULL THEN 1
 80:  WHEN [UPDATED] >= @UPDATED_FROM THEN 1
 81:  ELSE 0
 82:  END = 1
 83:  AND CASE
 84:  WHEN @UPDATED_TO IS NULL THEN 1
 85:  WHEN [UPDATED] <= @UPDATED_TO THEN 1
 86:  ELSE 0
 87:  END = 1
 88:  AND CASE
 89:  WHEN @UPDATED_BY IS NULL THEN 1
 90:  WHEN [UPDATED_BY] = @UPDATED_BY THEN 1
 91:  ELSE 0
 92:  END = 1
 93:  AND CASE
 94:  WHEN @INACTIVE IS NULL THEN 1
 95:  WHEN [INACTIVE] = @INACTIVE THEN 1
 96:  ELSE 0
 97:  END = 1

 

If you add one additional input parameter:

 1: , @ORDER_BY varchar(100) = NULL

 

And the following ORDER BY clause:

 1:  ORDER BY
 2:  -- ASCENDING
 3:  CASE
 4:  WHEN @ORDER_BY = 'CONTACT_SYSID' OR CONVERT( varchar(100), @ORDER_BY ) = '1' THEN [CONTACT_SYSID]
 5:  WHEN @ORDER_BY = 'TYPE_SYSID' OR CONVERT( varchar(100), @ORDER_BY ) = '2' THEN [TYPE_SYSID]
 6:  WHEN @ORDER_BY = 'CREATED_BY' OR CONVERT( varchar(100), @ORDER_BY ) = '8' THEN [CREATED_BY]
 7:  WHEN @ORDER_BY = 'UPDATED_BY' OR CONVERT( varchar(100), @ORDER_BY ) = '10' THEN [UPDATED_BY]
 8:  END
 9: , CASE
 10:  WHEN @ORDER_BY = 'TITLE' OR CONVERT( varchar(100), @ORDER_BY ) = '3' THEN [TITLE]
 11:  WHEN @ORDER_BY = 'FIRST_NAME' OR CONVERT( varchar(100), @ORDER_BY ) = '4' THEN [FIRST_NAME]
 12:  WHEN @ORDER_BY = 'MIDDLE_NAME' OR CONVERT( varchar(100), @ORDER_BY ) = '5' THEN [MIDDLE_NAME]
 13:  WHEN @ORDER_BY = 'LAST_NAME' OR CONVERT( varchar(100), @ORDER_BY ) = '6' THEN [LAST_NAME]
 14:  END
 15: , CASE
 16:  WHEN @ORDER_BY = 'CREATED' OR CONVERT( varchar(100), @ORDER_BY ) = '7' THEN [CREATED]
 17:  WHEN @ORDER_BY = 'UPDATED' OR CONVERT( varchar(100), @ORDER_BY ) = '9' THEN [UPDATED]
 18:  END
 19: , CASE
 20:  WHEN @ORDER_BY = 'INACTIVE' OR CONVERT( varchar(100), @ORDER_BY ) = '11' THEN [INACTIVE]
 21:  END
 22:  -- DESCENDING
 23: , CASE
 24:  WHEN @ORDER_BY = '-CONTACT_SYSID' OR CONVERT( varchar(100), @ORDER_BY ) = '-1' THEN [CONTACT_SYSID]
 25:  WHEN @ORDER_BY = '-TYPE_SYSID' OR CONVERT( varchar(100), @ORDER_BY ) = '-2' THEN [TYPE_SYSID]
 26:  WHEN @ORDER_BY = '-CREATED_BY' OR CONVERT( varchar(100), @ORDER_BY ) = '-8' THEN [CREATED_BY]
 27:  WHEN @ORDER_BY = '-UPDATED_BY' OR CONVERT( varchar(100), @ORDER_BY ) = '-10' THEN [UPDATED_BY]
 28:  END DESC
 29: , CASE
 30:  WHEN @ORDER_BY = '-TITLE' OR CONVERT( varchar(100), @ORDER_BY ) = '3' THEN [TITLE]
 31:  WHEN @ORDER_BY = '-FIRST_NAME' OR CONVERT( varchar(100), @ORDER_BY ) = '-4' THEN [FIRST_NAME]
 32:  WHEN @ORDER_BY = '-MIDDLE_NAME' OR CONVERT( varchar(100), @ORDER_BY ) = '-5' THEN [MIDDLE_NAME]
 33:  WHEN @ORDER_BY = '-LAST_NAME' OR CONVERT( varchar(100), @ORDER_BY ) = '-6' THEN [LAST_NAME]
 34:  END DESC
 35: , CASE
 36:  WHEN @ORDER_BY = '-CREATED' OR CONVERT( varchar(100), @ORDER_BY ) = '-7' THEN [CREATED]
 37:  WHEN @ORDER_BY = '-UPDATED' OR CONVERT( varchar(100), @ORDER_BY ) = '-9' THEN [UPDATED]
 38:  END DESC
 39: , CASE
 40:  WHEN @ORDER_BY = '-INACTIVE' OR CONVERT( varchar(100), @ORDER_BY ) = '-11' THEN [INACTIVE]
 41:  END DESC

 

You will be able to pass in the desired column (either by name or ordinal) as well as the direction (ascending or descending).

*Note: Each unique datatype requires its own CASE statement

 

Tags: , ,

Enabling SQL Cache Dependency within your .Net Application

Thursday, November 6, 2008 14:11
Posted in category HowTo
No Comments

This one’s short and sweet … add the following snippet to your web.config file (within the configuration > system.web > pages node):

   1:      <caching>
   2:        <sqlCacheDependency enabled="true"/>
   3:      </caching>

 

Also, don’t forget to add the following property and value to the desired providers node:

   1:  sqlCacheDependency="CommandNotification"

 

Finally, make sure your SQL Server has the Service Broker feature enabled.

(You can read more about SQL Cache Dependency here)

Tags: , , ,

Recursive Queries in SQL Server 2005 using CTE

Thursday, November 6, 2008 11:58
Posted in category Tips & Tricks
No Comments

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).

Tags: , , , ,

Fixed Positions in IE6

Wednesday, November 5, 2008 12:06
Posted in category Tips & Tricks
No Comments

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 …

Tags: , , ,

SiteMap Providers

Thursday, October 30, 2008 16:20
Posted in category Lessons Learned
No Comments
  1. Task

    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.

  2. Glossary of Terms

    The terms below are intended to provide a basic understanding of common terms and procedures used throughout the remainder of this document.

    1. Provider Model – an extensibility model that lets developers pull out and replace the built-in implementations of the core .net framework building block services allowing developers to quickly and easily make use of the rich productivity features.
      1. Is open source as of asp.net 2.0;
      2. Is used throughout asp.net;
      3. Makes asp.net much more flexible, expandable, and customizable;
      4. Includes, but is not limited to, the following providers: Membership; Role, Site Map, Session State and Profile;
    2. Site Map Providers – provide the interface between ASP.NET’s data-driven site-navigation features and site map data sources. The fundamental job of a site map provider is to read site map data from a data source and build an upside-down tree of SiteMapNode objects, and to provide methods for retrieving nodes from the site map.
      1. Site Map Provider – Defines the basic contract between ASP.NET and site map providers
      2. Static Site Map Provider – Aids developers in implementing the contract between ASP.NET and site map providers; is the base class for the XmlSiteMapProvider; can be used as the base class for custom site map providers; provider classes that derive from StaticSiteMapProvider require considerably less code than providers derived from SiteMapProvider; The word “Static” in StaticSiteMapProvider refers to the fact that the site map data source is static

      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.

    3. SQL Cache Dependency – Establishes a relationship between an item stored in an ASP.NET application’s Cache object and either a specific SQL Server database table or the results of a SQL Server 2005 query
        1. Can be used with SQL Server 7.0 and later versions
        2. Can dramatically increase application performance
        3. Can be used for both the application cache and the page output cache
        4. You do not need extensive SQL knowledge to configure SQL cache dependency in your application and in SQL Server. ASP.NET includes tools that automate the configuration.
        5. SQL Server 2005 and later versions implement a change notification model where notifications are sent to subscribing application servers, rather than relying on the polling model required in earlier versions of SQL Server.
  3. OPTIONS
    1. XML Site Map Provider – site maps stored in XML files conforming to a particular schema
      1. Is the default site map provider for ASP.NET
    2. Folder Site Map Provider – exposes the file system as a data source for site navigation
    3. SQL Site Map Provider – site maps stored in databases alongside other content conforming to a particular schema; is a StaticSiteMapProvider-derivative
      1. Jeff Procise – Contributing editor to MSDN Magazine; Author of several books; Co-founder of Wintellect. In June 2005, Jeff created an article indentifying an alternate solution to the xml site map provider, a new site map provider name SQLSiteMapProvider. In February 2006, Jeff updated his SQLSiteMapProvider to include the usage of the SQLCacheDependency which monitors the site map database and refreshes the site map if changes occur.
    4. Custom Site Map Provider – As the provider model is open source, one can create any site map provider desired.
  4. Lessons Learned

    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.

  5. References

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

xi http://msdn.microsoft.com/en-us/magazine/cc163657.aspx

xii http://msdn.microsoft.com/en-us/magazine/cc164622.aspx?code=true&level=root&file=SqlSiteMapProvider.cs

xiii http://msdn.microsoft.com/en-us/library/aa479320.aspx

Tags: , , ,

Hierarchical SQL Role Provider

Thursday, October 16, 2008 18:22
Posted in category HowTo
No Comments

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

 
and:
 
   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

Tags: , , ,

SQL Server Service Broker

Thursday, October 16, 2008 7:52
Posted in category Tips & Tricks
No Comments

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.

Read More

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. 

Tags: , ,

Recursive Queries in SQL Server 2005

Thursday, September 18, 2008 15:34
Posted in category Tips & Tricks
No Comments

among other improvements, SQL Server 2005 included a new XML datatype and enhanced XML functions … thus providing another solution for recursive queries …

for example … if i want to get a list of all of the tables in my database with their associated columns …  i might write the following query:

   1:  SELECT
   2:      sys.objects.object_id AS ENTITY_ID
   3:      , sys.objects.type AS ENTITY_TYPE
   4:      , sys.objects.name AS ENTITY_NAME
   5:      , sys.columns.column_id AS COLUMN_ID
   6:      , sys.columns.system_type_id AS COLUMN_TYPE
   7:      , sys.columns.name AS COLUMN_NAME
   8:  FROM sys.objects
   9:  INNER JOIN sys.columns
  10:      ON sys.objects.object_id = sys.columns.object_id
  11:  ORDER BY sys.objects.type, sys.objects.name, sys.columns.column_id

however, if i want to return only as many rows as i have tables i would have to rewrite that query to either use a CTE pivot, a complex case statement, or i could take advantage of the new xml functions and xpath:

   1:  SELECT
   2:      sys.objects.object_id AS ENTITY_ID
   3:      , sys.objects.type AS ENTITY_TYPE
   4:      , sys.objects.name AS ENTITY_NAME
   5:      , CONVERT(
   6:          varchar(max), CONVERT(
   7:              xml, (
   8:                  SELECT
   9:                      CASE
  10:                            WHEN sys.columns.column_id > 1 THEN ','
  11:                            ELSE ''
  12:                          END AS DELIMETER
  13:                      , sys.columns.name AS COLUMN_NAME
  14:                  FROM sys.columns
  15:                  WHERE sys.columns.object_id = sys.objects.object_id
  16:                  ORDER BY sys.columns.column_id
  17:                  FOR XML RAW, ROOT( 'root' ), ELEMENTS XSINIL
  18:              )
  19:          ).query('//row/*/text()')
  20:      ) AS COLUMN_LIST
  21:  FROM sys.objects
  22:  ORDER BY sys.objects.type, sys.objects.name

happy programming!

Tags: , , , , ,
« Older Entries