Determine the number of week days between 2 dates in T-SQL.
By: on 10.May.2k11 12:59 PM

Tags: SQL Server
 
The following T-SQL function will calculate the number of business days (Monday through Friday, optionally including Saturday) between 2 dates.

I found many other examples of similar functionality on the web, however most did not take into consideration that the T-SQL DATEPART function would return a different value for the weekday ("dw") depending on the server's configured @@DATEFIRST value.

According to this document, the DatePart function uses the configured @@DateFirst value to determine what the numerical representation for a week day is. The @@DateFirst value defaults based on the language and can also be changed via the SET DATEFIRST command, therefore most of the functions found on the web would return correct results if the language was not U.S. English.

The function below will work regardless of what this value is set to.

Example usage, including Saturdays:
Select dbo.udfDateDiffWeekDays( '4/1/2011', '4/29/2011', 1)
Returns 24

Example usage, excluding Saturdays:
Select dbo.udfDateDiffWeekDays( '4/1/2011', '4/29/2011', 0)
Returns 20

Create Function [dbo].[udfDateDiffWeekDays] (        
        @FromDate                datetime,
        @ToDate                        datetime,
        @IncludeSaturday        bit )
 
/*
Overview:        
=========        
Returns the number of business days (week days) between two dates.
 
Notes:
- Returns Null if either date is null
- Returns a negative number if the @ToDate is less than the @FromDate
                
Created:
========
May 10 2011 by Chad Hosick
*/
 
Returns int
As 
Begin
 
        Declare @WeekDayCount        int
 
        -- Return null if either date is null.
        If @FromDate Is Null Or @ToDate Is Null 
                Set @WeekDayCount = Null
        Else
        Begin
        
                Declare @ProcDate        datetime
                Declare @EndDate        datetime
                Declare @WeekDay        int
                Declare @AddDays        int
 
                -- Assume processing date is the start date
                Set @ProcDate = @FromDate
                Set @WeekDayCount = 0
                
                -- If the end date is before the start date, we're moving backwards
                If @ToDate < @FromDate
                        Set @AddDays = -1
                Else
                        Set @AddDays = 1
 
                -- Loop until our processing date is the same as the end date
                While ( @ProcDate <> @ToDate ) 
                Begin
 
                        -- @@DateFirst defaults to 7 for Sunday for US locale, but we'll adjust to make sure 
                        -- it works for all in case the locale is different or they changed it via the SET 
                        -- DATEFIRST command
                        -- Doing this will make Saturday = 0, through Friday = 6 respectively.
                        Set @WeekDay = ( DATEPART( dw, @ProcDate ) + @@DATEFIRST) % 7
                        
                        -- Saturday = 0, Sunday = 1
                        If  @WeekDay <> 1 And ( @WeekDay <> 0 Or @IncludeSaturday = 1 )
                                Set @WeekDayCount = @WeekDayCount + @AddDays
                
                        -- Adjust the current processing date        
                        Set @ProcDate = DateAdd(d, @AddDays, @ProcDate)
                End
        End
 
        Return @WeekDayCount
End


Leave comment
 
Taking a SQL Server database offline
By: on 29.Apr.2k10 4:07 PM

Tags: SQL Server
 
Here's a quick and dirty way to take a SQL server offline. For me, right-clicking on the Database in SSMS and trying to take it offline using the Tasks > Take Offline context option typically doesn't work, usually because the database is in use.

This is not recommended for production environments (as it will result in data loss if users are currently making changes or have uncommitted transactions) but I use it frequently when testing my database-dependent service applications to simulate a remote SQL server reboot or shutdown to make sure the service can recover properly when SQL Server comes back online.

To take it offline:
ALTER DATABASE [dbname] SET OFFLINE WITH ROLLBACK IMMEDIATE


To bring it back online:
ALTER DATABASE [dbname] SET ONLINE



Leave comment
 
Practical string extension example in C#
By: on 25.Nov.2k9 8:13 AM

Tags: C#
 
I love implementing framework code that simplifies frequent tasks and improves code readability. In SQL Server, there is the "In" clause which allows you to quickly specify an array of comparison values in SQL statements, for example:
-- This statement will return all records where the 'CustType' field contains "R" or "C" ( Residential or Commercial)
Select * From Customer Where CustType In ( 'R', 'C' )


In C#, assuming the Customer was translated into an object class, this same logic would traditionally be written as something like the following:
// Traditional example 1
if( customer.CustType == "R" || customer.CustType == "C" ) {
   // ...
}


While readability isn't too bad when making 2 comparisons, it becomes more difficult to read if there are more values to compare, or if the criteria is more complicated:
// Traditional example 2
if( customer.CustType == "R" || customer.CustType == "C" || customer.CustType == "X" || customer.CustType == "Y" || customer.CustType == "Z" ) {
   // ...
}
 
// Traditional example 3
if( customer.IsActive && customer.BanksOnline && ( customer.CustType == "R" || customer.CustType == "C" ) {
   // ...
}


Using an extension to the string type, I would like to be able to add an IsIn() method to strings so that I can rewrite these statements as follows:
// Example 1 using my string extension
if( customer.CustType.IsIn( "R", "C" ) ) {
   // ...
}
 
// Example 2 using my string extension
if( customer.CustType.IsIn( "R", "C", "X", "Y", "Z") ) {
   // ...
}
 
// Example 3 using my string extension
if( customer.IsActive && customer.BanksOnline && customer.CustType.IsIn( "R", "C" ) ) {
   // ...
}


Here is my implementation of the extension method. (Note that my string comparison is not case sensitive.)
namespace Framework.Extensions {
 
   public static class StringExt {
      
      /// <summary>
      /// Determines if the string matches any items in a string array.
      /// </summary>
      /// <param name="s">The string being extended.</param>
      /// <param name="list"></param>
      /// <returns>True if this string matches an item in the array, false otherwise.</returns>
      public static bool IsIn(this string s, params string[] list) {
         
         // Enumerate list of strings passed to look for match
         foreach( string val in list ) {
            
            // Ignoring case, return true if we find a match
            if( s.Equals( val, StringComparison.InvariantCultureIgnoreCase ) )
               return true;
         }
 
         return false;
      }
   }


Example usage:
using Framework.Extensions;
 
public frmMain() {
   
   InitializeComponent();
   
   string myString = "abc";
   
   bool eg1 = myString.IsIn( "efg", "xyz"); // false
      
   bool eg2 = myString.IsIn( "ABC", "xyz"); // true
      
   bool eg3 = myString.IsIn( "efg", "xyz"); // false
      
   bool eg4 = myString.IsIn( true, "xyz", "efg", "AbC"); // true
      
   bool eg5 = myString.IsIn( true, "xyz", "efg", "hij", "klmnopq", "abc" ); // true
}



Leave comment
 
Preventing a virtual directory from inheriting it's parent web.config settings in ASP.NET/IIS.
By: on 24.Nov.2k9 8:56 AM

Tags: ASP.Net, IIS
 
When running a separate ASP.Net application in both an application and a virtual directory under it, by default the child application will inherit configuration settings from the parent application's web.config file.

This includes settings for custom error pages, HTTP handlers and modules, etc, and while this at times might be desirable, many times it is not. For example, I had a custom httpHandler for Captcha images in my parent application in an assembly that was not available to the virtual directory application, so my virtual directory application was logging configuration errors into the event log and wouldn't start up.

To prevent this from happening, in the parent apps web.config, simply wrap the <system.web> section as follows:

<location path="." inheritInChildApplications="false">
   <system.web>
         .....
   </system.web>
</location>


Leave comment
 
Protecting email addresses from spambots through obfuscation with Javascript and ASP.Net
By: on 19.Nov.2k9 8:37 PM

Tags: ASP.Net, C#, Javascript
 
I get 80-100 spam emails a day, and I've had the misfortune of returning from 10 day vacations only to wait while 1500 emails get downloaded into my inbox. Even though Cloudmark cleans up a large percentage, I still waste a lot of time dealing with spam and phish.

In much the same way search engines crawl through web page to catalog content, spambots crawl through pages to identify and store email addresses. The ideal situation is that visitors to your site can view and interact with an email address in the traditional, convenient manner (meaning they click on your email address, and it opens up their mail application) but that spambots cannot find your email address by searching through the HTML code your web page is built from.

I've tried a few of the existing solutions to prevent an email address from being parseable by spambots and found that there are pros and cons to the various approaches. Using an image representation of an email address is both expensive (in terms of CPU processing and bandwidth) and is not "clickable" in the client's browser to open their mail client. As well, since captcha images can be defeated, it wouldn't be a far stretch for spambots to extrapolate an email address from an image in the near future (if not already).

Another recommended solution is to embed a unicode representation of the email address in the page, meaning instead of sending "bob@fakedomain.xyz" out, you send the unicode representation, which in this case would mean your HTML code would look as follows:
 <a href=\"mailto:&#98;&#111;&#98;&#64;&#102;&#97;&#107;&#101;&#100;&#111;&#109;&#97;&#105;&#110;&#46;&#120;&#121;&#122;\">Bob Zuruncle</a>


While this also raises the bar, I don't think it will be long before the bottom feeders are onto this little trick and will start looking for it - it wouldn't take much in the way of modification to existing routines to search for a string of unicode characters that includes the "at" symbol (&#64, ASCII 64) and a period (&#46 ASCII 46) instead of the old fashioned "@" and ".".

My solution here might seem like a bit of overkill now, but it introduces enough randomness and complexity to make it very difficult for a crawler to identify an address. The only way I can see it being defeated would be for spambots to actually execute each and every page via a javascript interpretor and parse the results, as opposed to simply parsing the source HTML code.

The following javascript code is an example of a function generated server-side and executed in the client's browser. The function will "build" an email address and display the result in the browser, however it does not contain any of the identifying traits of an email address that a spambot would look for - when crawling through web pages harvesting email addresses will skip past this script without recognizing that it's an email address. There is no "mailto", no "at" symbol, and no period, all items which a spambot could look for via regular expressions or old fashioned parsing to try and extrapolate email addresses from within page content.

Email obfuscation example for email address "bob@fakedomain.xyz":
<script type="text/javascript"> 
<!--
   var a=63+1,b=a-18,c=b+63,d=40;
   d=String.fromCharCode(d-2)+String.fromCharCode(30+5);
   var p="fakedomain" + d + b + "xyz";
   document.write("<a href=" + d + c + "ail" + "to:" + "bob" + d + a + p +  ">" + "Bob Zuruncle" + "</a>");
// -->
</script> 


Here's the same code with comments:
<script type="text/javascript">
<!--
   // ASCII codes, a=64("@"), b=46("."), c=109("m")
   // d is used in the following code to help build unicode prefix
   var a=63+1,b=a-18,c=b+63,d=40; 
 
   // build the unicode prefix, "&#", by concatenating the two characters
   d=String.fromCharCode(d-2)+String.fromCharCode(30+5);
 
   // build the domain name, substituting unicode representation of the period
   var p="fakedomain" + d + b + "xyz";  
 
   // concatenate all the characters and strings into the final result, a properly formed hypertext mailto link
  document.write("<a href=" + d + c + "ail" + "to:" + "bob" + d + a + p +  ">" + "Bob Zuruncle" + "</a>"););
// -->
</script> 


If javascript is enabled at the clients browser, the following text would be processed and rendered as HTML, which of course you (and the browser) recognize as a perfectly well formatted hypertext reference for email addresses:
<a href="mailto:bob@fakedomain.xyz">Bob Zuruncle</a>


As mentioned, the only drawback to this approach is that if javascript is disabled in the client's browser, they would not see anything whatsoever. I think it's a relatively safe assumption that most visitors to your site will have javascript enabled, however if it's catastrophic to your requirements if a user doesn't see an email, I wouldn't use this approach.

If your site is static and you just want to protect some existing links, you can just copy/paste the above javascript function into your pages and substitute in your email address details, however, if you're building content dynamically, the following C# code will actually build a string server-side containing the required javascript code for you, which can then be included in the page being returned to your site visitor.

If I can free up some time in the near future I will convert this to a ASP.Net server control to do this all automically, however, for now you can simply add a LiteralControl containing the results of these methods.

Code to generate the javascript:
/// <summary>
/// Obfuscates an email address with javascript code to prevent spambots from crawling it.
/// Use this override if you want the email address displayed to the user as an email address.
/// </summary>
/// <param name="emailAddress">Email address you wish to obfuscate.</param>
/// <param name="cssClass">The CSS class to apply to the HTML, pass null if you don't need to assign a CSS class</param>
/// <returns>Javascript code to obfuscate an email address.</returns>
public static string ObfuscateEmailAddress(string emailAddress, string cssClass) {
   return ObfuscateEmailAddress( emailAddress, emailAddress, cssClass );
}
 
/// <summary>
/// Obfuscates an email address with javascript code to prevent spambots from crawling it.
/// Use this override if you want a name to appear (instead of an email address)
/// </summary>
/// <param name="emailAddress"></param>
/// <param name="cssClass">The CSS class to apply to the HTML, pass null if you don't need to assign a CSS class</param>
/// <returns>Javascript code to obfuscate an email address.</returns>
public static string ObfuscateEmailAddress(string emailAddress, string displayText, string cssClass) {
   
   emailAddress = emailAddress.ToLower();
   
   // Verify address is valid, then get the user name and domain portions of the address
   Regex regex = new Regex( @"(?<user>\w+([-+.]\w+)*)@(?<domain>\w+([-.]\w+)*\.\w+([-.]\w+)*)" );
   
   // Try for a match
   Match match = regex.Match( emailAddress );
   
   // If no match, return a textual error
   if( !match.Success )
      return "Error: Invalid email address.";
 
   string user = match.Groups[ "user" ].ToString();
   string encodedDomain = match.Groups[ "domain" ].ToString().Replace( ".", "\" + d + b + \"" );
   
   if( emailAddress.Equals( displayText, StringComparison.InvariantCultureIgnoreCase ) )
      displayText = string.Concat( user, @""" + d + a + p" );
 
   return string.Concat( "<script type=\"text/javascript\">var a=63+1,b=a-18,c=b+63,d=40;d=String.fromCharCode(d-2)+String.fromCharCode(30+5);",
   "var p=\"", encodedDomain, "\"; document.write(\"<a href=\" + d + c + \"ail\" + \"to:\" + \"", user, "\" + d + a + p + \">\" + \"",
   displayText, "\" + \"</a>\");</script><noscript>[Email address requires javascript]</noscript>" );
}


Leave comment
 
Compress and/or encrypt ViewState in ASP.Net pages
By: on 18.Nov.2k9 5:04 PM

Tags: ASP.Net, C#
 
Occasionally I've found the need to store relatively large amounts of data in the viewstate. In scenarios where data is expensive to create server-side, such as in a CPU intenstive database sproc, or when it's for an admin-type page which is consumed infrequently, I've been known to take the lazy way out and stick objects (such as DataSets) in the viewstate.

Some argue that enabling compression in IIS is the better alternative, however, in a hosted environment you would rarely have the ability to modify this configuration.

The following is the base page I use in websites to optionally compress and/or encrypt view state for pages. To use this code, simply create a new class in your web project and call it "BaseWebPage.cs", paste the contents of the main class, below, into this page, and then in your code-behind pages, change any class definitions to inherit from BaseWebPage instead of System.Web.UI.Page.

For example, this code in your page:
public partial class _default : System.Web.UI.Page {
   // (Page code here...)
}


would get changed to:
public partial class _default : BaseWebPage {
   // (Page code here...)
}


As well, since this page includes static configuration variables that must be set in order to work correctly, you must make a call to the static SetEncryptionOptions() method from Application_Start() in your global.asax.cs file, ie:
public class Global : System.Web.HttpApplication {
   
   protected void Application_Start(object sender, EventArgs e) {
      
      // Configure page encryption with a password, using 256-bit strength,
         // with compression and encryption enabled for all pages by default.
      BaseWebPage.SetEncryptionOptions( "abcMyPassword123", BaseWebPage.CipherStrength.Bits256, true, true );
   }
}


Once you have everything in place, you can place items into the viewstate and retrieve them as you normally would, ie:
protected void Page_Load(object sender, EventArgs e) {
   
   DataSet myData;
   
   // If it's the first time the page is loading (ie not being posted back)
   // we get our data from our applicable datasource.
   if( !Page.IsPostBack ) {
      
      // Populate from database, middle-tier, etc.
      myData = MyDataProvider.GetData();
      
      // Add to viewstate
      ViewState[ "MyDataSet" ] = myData;
   }
 
   // Otherwise, we can retrieve the dataset directly from the viewstate
   // which is being returned to us in the web request
   else {
      myData = (DataSet) ViewState[ "MyDataSet" ];
   }
 
   // Consume data here...
}


Listing of BaseWebPage.cs
using System;
using System;
using System.IO;
using System.IO.Compression;
using System.Security.Cryptography;
using System.Web.UI;
 
namespace Framework.Web {
   
   public class BaseWebPage : System.Web.UI.Page {
      
      #region Static/Constants
      
      /// <summary>
      /// Formatter responsible for serialization and deserialization
      /// </summary>
      private static ObjectStateFormatter _formatter = new ObjectStateFormatter();
      
      /// <summary>
      /// Enum representing the strength of the encryption used when encrypting viewstate.
      /// </summary>
      public enum CipherStrength {
         Bits128,
            Bits192,
            Bits256
      }
 
      /// <summary>
      /// Static properties used for every page in the site.
      /// </summary>
      private static byte[] EncryptionPasswordBytes;
      private static byte[] EncryptionPasswordIV;
      
      private static bool EncryptDefault;
      private static bool CompressDefault;
      
      /// <summary>
      /// This method is used to configure encryption settings and compression/encryption defaults for the entire site. It should
      /// be called in global.asax during the Application_Start() event.
      /// </summary>
      /// <param name="password">The password used to encrypt/decrypt viewstate.</param>
      /// <param name="cipherStrength">Enumeration specifying strength of the cipher used.</param>
      /// <param name="enableEncryptionByDefault">Set to true to encrypt all inherited pages by default, false otherwise.</param>
      /// <param name="enableCompressionByDefault">Set to true to compress all inherited pages by default, false otherwise.</param>
      public static void SetEncryptionOptions(string password, CipherStrength cipherStrength, bool enableEncryptionByDefault, bool enableCompressionByDefault) {
         
         // Set static defaults, all pages will have these settings by default.
         // Note: These can be overridden at the page level.
         EncryptDefault = enableEncryptionByDefault;
         CompressDefault = enableCompressionByDefault;
         
         // Set static byte[] values used for encryption once at startup and store them in static variables
         // so they do not need to be calculated every time encrypt/decrypt is called.
         PasswordDeriveBytes pdb = new PasswordDeriveBytes( password, new byte[] { 0x71, 0x4C, 0x40, 0x00, 0x43, 0x1A, 0x10, 0x01, 0x34, 0x56, 0xFF, 0x99, 0x71, 0x22, 0x49 } );
         
         // Build encryption initialization vector and set the static byte array
         EncryptionPasswordIV = pdb.GetBytes( 16 );
         
         // Based on the cipher strength, build the appropriate key and set the static byte array
         switch( cipherStrength ) {
            case CipherStrength.Bits128: {
               EncryptionPasswordBytes = pdb.GetBytes( 16 );
               break;
            }
 
            case CipherStrength.Bits192: {
               EncryptionPasswordBytes = pdb.GetBytes( 24 );
               break;
            }
 
            case CipherStrength.Bits256: {
               EncryptionPasswordBytes = pdb.GetBytes( 32 );
               break;
            }
         }
      }
 
      #endregion
      
      #region Constructor
      
      /// <summary>
      /// Default constructor
      /// </summary>
      public BaseWebPage() {
         
         // For each page inherting from this base page, the compression and encryption settings
         // will be based on the site defaults.
         CompressViewState = CompressDefault;
         EncryptViewState = EncryptDefault;
      }
 
      #endregion
      
      #region Properties
      
      /// <summary>
      /// Does the current pages viewstate get compressed?
      /// </summary>
      public bool CompressViewState {
         get;
         set;
      }
 
      /// <summary>
      /// Does the current pages viewstate get encrypted?
      /// </summary>
      public bool EncryptViewState {
         get;
         set;
      }
 
      #endregion
      
      #region Overrides
      
      protected override void OnError(EventArgs e) {
         
         Exception exception = Server.GetLastError();
         
         // Good practice to log the error server-side somehow (ie event log, db table, etc)
         // and return a generic error screen to the user!!
         Server.Transfer( "~/errorpage.aspx" );
         Server.ClearError();
      }
 
      #endregion
      
      #region Viewstate overrides, Compression and Encryption
      
      /// <summary>
      /// Compresses a byte array.
      /// </summary>
      private static byte[] Compress(byte[] data) {
         
         MemoryStream output = new MemoryStream();
         
         GZipStream gzip = new GZipStream( output, CompressionMode.Compress, true );
         gzip.Write( data, 0, data.Length );
         gzip.Close();
         
         return output.ToArray();
      }
 
      /// <summary>
      /// Decompresses a byte array.
      /// </summary>
      private static byte[] Decompress(byte[] data) {
         
         MemoryStream input = new MemoryStream();
         input.Write( data, 0, data.Length );
         input.Position = 0;
         
         GZipStream gzip = new GZipStream( input, CompressionMode.Decompress, true );
         MemoryStream output = new MemoryStream();
         
         byte[] buff = new byte[ 64 ];
         int read = -1;
         read = gzip.Read( buff, 0, buff.Length );
         
         while( read > 0 ) {
            output.Write( buff, 0, read );
            read = gzip.Read( buff, 0, buff.Length );
         }
 
         gzip.Close();
         return output.ToArray();
      }
 
      /// <summary>
      /// Overrides the method that typically adds the serialized viewstate to the web request.
      /// We'll replace the standard viewstate string with our own depending on configured options.
      /// </summary>
      protected override void SavePageStateToPersistenceMedium(object viewState) {
         
         // If the page is neither compressed or encrypted, call the base
         // method to store the viewstate in the traditional manner.
         if( !CompressViewState && !EncryptViewState ) {
            base.SavePageStateToPersistenceMedium( viewState );
         }
 
         // Otherwise, we're compressing and/or encrypting it with our own implementation.
         else {
            
            // Serialize the viewstate into a byte array.
            MemoryStream ms = new MemoryStream();
            _formatter.Serialize( ms, viewState );
            byte[] viewStateArray = ms.ToArray();
            
            // Compress, if enabled for the current page
            if( CompressViewState )
               viewStateArray = Compress( viewStateArray );
 
            // Encrypt, if enabled for the current page
            if( EncryptViewState )
               viewStateArray = Encrypt( viewStateArray );
 
            // Register our custom viewstate object as a hidden field in the outgoing request.
            ClientScript.RegisterHiddenField( "__CVIEWSTATE", Convert.ToBase64String( viewStateArray ) );
         }
      }
 
      /// <summary>
      /// Overrides the method that typically extracts the serialized viewstate in a posted request.
      /// </summary>
      protected override object LoadPageStateFromPersistenceMedium() {
         
         // If the page is neither compressed or encrypted, call the base
         // method to return the viewstate in the traditional manner.
         if( !CompressViewState && !EncryptViewState ) {
            return base.LoadPageStateFromPersistenceMedium();
         }
 
         // Otherwise, we're decrypting and/or decompressing it with our own implementation.
         else {
            
            // Get the hidden field from the request
            byte[] bytes = Convert.FromBase64String( Request.Form[ "__CVIEWSTATE" ] );
            
            // If encryption is enabled for this page, decrypt the byte array
            if( EncryptViewState )
               bytes = Decrypt( bytes );
 
            // If compression is enabled, decompress the byte array.
            if( CompressViewState )
               bytes = Decompress( bytes );
 
            // Return the deserialized view state
            return _formatter.Deserialize( Convert.ToBase64String( bytes ) );
         }
      }
 
      /// <summary>
      /// Encrypts the supplied byte array.
      /// </summary>
      /// <param name="data"></param>
      private byte[] Encrypt(byte[] data) {
         
         // Create a MemoryStream to encrypt into
         MemoryStream ms = new MemoryStream();
         
         // Create a symmetric algorithm.
         Rijndael alg = Rijndael.Create();
         
         alg.Key = EncryptionPasswordBytes;
         alg.IV = EncryptionPasswordIV;
         
         // Create the crypto stream
         CryptoStream cs = new CryptoStream( ms, alg.CreateEncryptor(), CryptoStreamMode.Write );
         
         // Write the encrypted data into the memory stream
         cs.Write( data, 0, data.Length );
         
         // Close the crypto stream
         cs.Close();
         
         // Return the encrypted byte array.
         return ms.ToArray();
      }
 
      /// <summary>
      /// Decrypts the supplied byte array.
      /// </summary>
      /// <param name="cipherData"></param>
      public static byte[] Decrypt(byte[] cipherData) {
         
         // Create a MemoryStream to decrypt into.
         MemoryStream ms = new MemoryStream();
         
         // Create a symmetric algorithm.
         Rijndael alg = Rijndael.Create();
         
         // Now set the key and the IV.
         alg.Key = EncryptionPasswordBytes;
         alg.IV = EncryptionPasswordIV;
         
         // Create the crypto stream
         CryptoStream cs = new CryptoStream( ms, alg.CreateDecryptor(), CryptoStreamMode.Write );
         
         // Write the decrypted data into the memory stream
         cs.Write( cipherData, 0, cipherData.Length );
         
         // Close the crypto stream
         cs.Close();
         
         // Return the decrypted byte array.
         return ms.ToArray();
      }
 
      #endregion
   }
}


Leave comment
 
Converting a comma-separated (or other delimiter) list to a table in SQL Server
By: on 17.Nov.2k9 11:31 AM

Tags: SQL Server
 
This is a user defined function I frequently use in T-SQL. It converts a delimited string into a table for participation in "join" and "where" clauses in calling procedures...

The code is as follows...
Create Function dbo.udfDelimitListToTable (
        @DelimitList varchar(8000),  -- Delimited list i.e. 100|300|500 or 100,300,500
        @Delimiter   char(1)=',')    -- Delimiter to use
 
Returns @ListTable Table (ListValue varchar(50) NOT NULL)
 
-- Overview:        
-- =========        
--                 
-- This procedure returns values from a supplied delimited list.
-- 
-- The values are returned as a select statement result set, making
-- it easy for join and where clause participation in calling
-- procedures.
 
As
 
Begin
        
        -- Leftmost unused delimiter
        Declare @LeftDelimiter smallint
        
        -- Current item to be extracted
        Declare @Item varchar(50)
        
        -- Fill the list table with each value in the delimited list
        -- Wrap the list up with Delimiters
        Select @LeftDelimiter = 1, 
               @DelimitList = @Delimiter + @DelimitList + @Delimiter                                
        
        -- Extract entries from the list one at a time while not beyond last delimiter
        While CharIndex(@Delimiter, @DelimitList, @LeftDelimiter +1) > 0                                
        Begin
                Select @Item = Substring (@DelimitList, @LeftDelimiter + 1, 
                       CharIndex( @Delimiter, @DelimitList, @LeftDelimiter + 1) - (@LeftDelimiter + 1))
 
                -- If the item to be extracted is not blank, add it to the table
                If LTrim(RTrim(@Item)) <> ''                                        
                        Insert Into @ListTable (ListValue) Values (@Item)        
 
                -- Get the new leftmost delimiter to work with
                Select @LeftDelimiter = Charindex( @Delimiter, @DelimitList, @LeftDelimiter + 1)        
        End
        
        Return
End


Example usage #1:
Select ListValue From dbo.udfDelimitListToTable('A,B,C,D,E,F,G', ',')


Example usage #2:
Declare Procedure dbo.uspCustomerLoadByStatusList
        @StatusListCSV varchar(500) -- Comma separated list of valid status
        
As
 
Select
        FirstName,
        LastName,
        PhoneNumber
From
        Customer
Where
        Status In ( 
                Select ListValue
                From   dbo.udfDelimitListToTable( @StatusListCSV, ',' ) 
        )
        
Go
 
-- ACT=Active, DNC=Do Not Call
Exec dbo.uspCustomerLoadByStatusList @StatusListCSV='ACT,DNC'
Go


Leave comment
 
Identifying a Canadian postal code or US Zip code with Regular Expressions
By: on 16.Nov.2k9 4:33 PM

Tags: C#
 
The following code will identify the type of Postal/Zip code for a North American address:

namespace Framework.Helpers {
 
   public static class AddressHelper {
      
      /// <summary>
      /// Enum for an identified postal code type.
      /// </summary>
      public enum PostalCodeType {
         USZip5,
         USZip5Plus4,
         Canada,
         Unknown
         }
 
      /// <summary>
      /// Differentiates between the various North American zip/postal codes.
      /// </summary>
      /// <param name="value">The string value to test.</param>
      /// <returns>The identified <see cref="PostalCodeType">PostalCodeType</see> enum value.</returns>
      public static PostalCodeType DetermineNAPostalCodeType(string value) {
         
         // Test for Canadian
         Regex canadaRegEx = new Regex( "^[ABCEGHJ-NPRSTVXY]{1}[0-9]{1}[ABCEGHJ-NPRSTV-Z]{1}[ ]?[0-9]{1}[ABCEGHJ-NPRSTV-Z]{1}[0-9]{1}$",
         RegexOptions.IgnoreCase | RegexOptions.Compiled );
         
         if( canadaRegEx.IsMatch( value ) )
            return PostalCodeType.Canada;
 
         // Test for 5 digit zip code, ie 90210
         Regex usZip5RegEx = new Regex( @"^\d{5}$", RegexOptions.IgnoreCase | RegexOptions.Compiled );
         
         if( usZip5RegEx.IsMatch( value ) )
            return PostalCodeType.USZip5;
 
         // Test for 5+4 zip code, ie 90210-1234
         Regex usZip9RegEx = new Regex( @"^\d{5}-\d{4}?$", RegexOptions.IgnoreCase | RegexOptions.Compiled );
         
         if( usZip9RegEx.IsMatch( value ) )
            return PostalCodeType.USZip5Plus4;
 
         return PostalCodeType.Unknown;
      }
   }
}


Leave comment