Friday, November 19, 2010

Must Have String Extensions

There is a great deal of love and hate toward extension methods. I will not digress into this debate, but I will give my opinion on when and where I feel extension methods are applicable and useful. For me, the rules are simple:

  1. Does it reduce the amount of code I would have to write if the extension method did not exist?
  2. Is there no other way to reduce my code by using existing framework code?
  3. Can I use this extension in multiple places throughout my project or even carry it with me across several projects?
With that in mind, I would like to share a few of my "must have" string extensions that follow me from project to project.

 
public static class StringExtensions {
    public static int ToInt32(this string s, int defaultValue) {
        try {
            return Convert.ToInt32(s);
        }
        catch {
            return defaultValue;
        }
    }

    public static int? ToInt32N(this string s, int? defaultValue) {
        try {
            return s == null ? null : (int?)Convert.ToInt32(s);
        }
        catch {
            return defaultValue;
        }
    }

    public static decimal ToDecimal(this string s, decimal defaultValue) {
        try {
            return Convert.ToDecimal(s);
        }
        catch {
            return defaultValue;
        }
    }

    public static decimal? ToDecimalN(this string s, decimal? defaultValue) {
        try {
            return s == null ? null : (decimal?)Convert.ToDecimal(s);
        }
        catch {
            return defaultValue;
        }
    }

    public static Guid ToGuid(this string s, Guid defaultValue) {
        try {
            return new Guid(s);
        }
        catch {
            return defaultValue;
        }
    }

    public static Guid? ToGuidN(this string s, Guid? defaultValue) {
        try {
            return s == null ? null : (Guid?)new Guid(s);
        }
        catch {
            return defaultValue;
        }
    }

    public static DateTime ToDateTime(this string s, DateTime defaultValue) {
        try {
            return Convert.ToDateTime(s);
        }
        catch {
            return defaultValue;
        }
    }

    public static DateTime? ToDateTimeN(this string s, DateTime? defaultValue) {
        try {
            return s == null ? null : (DateTime?)Convert.ToDateTime(s);
        }
        catch {
            return defaultValue;
        }
    }
}

The possibilities of application are endless.  However, I will give a few comparisons of how these methods might reduce your code.

Parsing QueryString values in a web application
BEFORE
 
    int id;
    if (!int.TryParse(Request.QueryString["id"], out id)) {
         id = 0;
    }
    
    int idn? = null;
    if (int.TryParse(Request.QueryString["id"], out id) {
        idn = id;
    }

AFTER
 
    int id = Request.QueryString["id"].ToInt32(0);
    int? idn = Request.QueryString["id"].ToInt32N(null);

Parse text from an input control
BEFORE
 
    DateTime dob;
    try {
        dob = Convert.ToDateTime(txtDob.Text);
    }
    catch {
        dob = DateTime.MinDate;
    }
    Employee emp = new Employee {
        FirstName = txtFirst.Text,
        LastName = txtLast.Text,
        DateOfBirth = dob
    }

AFTER
 
    Employee emp = new Employee {
        FirstName = txtFirst.Text,
        LastName = txtLast.Text,
        DateOfBirth = txtDob.Text.ToDateTime(DateTime.MinDate)
    }

You might wonder about the nullable methods (i.e. ToInt32N, ToDecimalN, etc...). The differences are subtle, but they are there. For example, the following use of ToInt32N is dangerous:
 
    int i = somestring.ToInt32N(25).Value;
Most of the time this will work as expected. However, if the value somestring happens to be null you will get an exception because ToInt32N will evaluate to null.

I often wonder why these type string methods have not found their way into the .Net Framework. To me they just seem so intuitively useful. Perhaps they will make it into the framework someday. Until then, the above extensions will be pervasive in my code.

Tuesday, September 7, 2010

Enumerations Between Code and Database

One of my biggest pet peeves concerning enumerated types is how the values are serialized such as to a database field.  I absolutely detest storing numerical data in my database when the whole point of an enumeration is to make numerical data human readable.

For example, let's consider we have a database table for Orders and in that table we might have an OrderStatus field.  Let us also suppose that the following list represents all the status values that this field may have: (New, Processing, Denied,Approved,Completed).

In our C# code we have an enum defined like so...

enum OrderStatus { 
    New, 
    Processing, 
    Denied, 
    Approved 
};


Now, the easiest way to store this in the database would be to cast the enum to an int and  use ToString() in our code on our enum type which would produce a number (i.e. New = 1, Processing = 2, etc...).  But anyone trying to run a query on the database would have no idea that 1 meant New and 2 meant Processing, etc...  They would have to either open the source code and find out for themselves or ask a developer familiar with the project.

Order.Status = ((int)status).ToString();

The other option is to call ToString() directly on the enum value which will produce a string with the enum value equal to the name of the value.  

Order.Status = status.ToString();

The problem comes when you have to convert that string back into the enum type.  Of course you can do this inline with code such as the following...

enum status;
try {
    status = (OrderStatus)Enum.Parse(typeof(OrderStatus), databaseValue);
}
catch {
    status = OrderStatus.New; // default value
}

switch(status){
  case OrderStatus.New:
      ....
      break;
  ....
}
 
That sure is a lot of code just convert a string to an enum.  Of course, you could create a utility method to do the conversion, but then you would have to have a method for each enum type.  That seems like a waste of code also.  So the question is how can we contain this conversion into a single method that can be re-used for any enum type?

The answer lies in world of Generics.  Coupled with the new language feature of Extension Methods, and we have a simple, yet powerful solution.  I believe the code below pretty much explains it all...

public static class StringExtensions {

    public static T ToEnum<T>(this string s, T defaultValue) {
        try {
            return (T)Enum.Parse(typeof(T), s);
        }
        catch {
            return defaultValue;
        }
    }

}


And now our switch statement from the first section of code above is drastically simplified...
switch(databaseValue.ToEnum<OrderStatus>(OrderStatus.New)){
  case OrderStatus.New:
      ....
      break;
  ....
}


As you can see, any string can be parsed into any enumerated type with the same simple extension method.

 

Thursday, July 22, 2010

TSQL Set Based Sequencing

Consider you have a database table with a sequence field (you might also call it your order field) and you need a fast way to rearrange the items without using a database cursor or some nasty recursive loop.  Fortunately, there is a set based solution.

For example, let us consider the following table:

[Photos]
PhotoID int identity(1, 1) not null,
AlbumID int not null,
SequenceNo int not null
Filename varchar(200) not null,
Caption varchar(50) null

Now, we will assume the following rules:
  • Each album has it's own set of sequence numbers for the photos contained in the album.
  • SequenceNo for new Photos is max(SequenceNo) + 1 where the AlbumID is equal to the AlbumID of the new Photo 
If you wish to move a photo either up or down in position (i.e. increment or decrement the SequenceNo), the following TSQL is the fastest approach to accomplishing this...

-- Hardcoded values for this example 
set @PhotoID = 1234
-- The positive or negative change in sequence to be applied
set @SequenceDelta = -1 

-- First, we need to obtain the AlbumID associated with this photo
declare @AlbumID int
select @AlbumID = AlbumID from Photo where PhotoID = @PhotoID

-- Next, we need the current sequence for this photo
declare @CurrSeq int
select @CurrSeq = SequencNo from Photo where PhotoID = @PhotoID

-- Next, we must make room for the move
if (@SequenceDelta > 0) begin
    -- This is an increase in position (sequence)
    -- So we shift the items >= the new position up
    update Photos set SequenceNo = SequenceNo - 1
    where SequenceNo <= (@CurrSeq + @SequenceDelta) and 
        AlbumID = @AlbumID
end

if (@SequenceDelta < 0) begin
    -- This is a decrease in position (sequence)
    -- So we shift the items <= the new position down
    update Photos set SequenceNo = SequenceNo + 1
    where SequenceNo >= (@CurrSeq + @SequenceDelta) and 
        AlbumID = @AlbumID
end

-- Next, we update the photo record to it's new position
update Photos set SequenceNo = SequenceNo + @SequenceNo
where PhotoID = @PhotoID

-- Finally, we used set based sequencing to resequence the photos in the album
-- so that the sequence numbers are in contiguous order starting at number 1
update Photos set SequenceNo = (
    select count(*) + 1 from Photos p
    where (
        (p.SequenceNo < Photos.SequenceNo) or
        (
            (p.SequenceNo <= Photos.SequenceNo) and
            (p.Caption < Photos.Caption)
        )
    ) and
    (p.AlbumID = Photos.AlbumID)
)
where AlbumID = @AlbumID

If you just had a Photos table with no AlbumID (i.e. no subsets of records) and you just wanted to keep all the records in a contiguous order, it is as simple as removing the 2 where clauses comparing the AlbumIDs.


Note: the where clause (p.Cation < Photos.Caption)  assures us that if 2 or more Photos in the same album happen to have the same SequenceNo, their sequence numbers will be determined by the value of their Caption.  More clauses could be added to further account for similarities.  For example if 2 or more photos had the same sequence number and caption, the filename could also be compared.  A condition where this might happen would be if a photo was moved to a different Album.



Friday, April 30, 2010

SSIS Package With FTP Task Running In SQL Server Agent

Well, this is my first of what I hope to be many posts to this blog.  It was a particularly frustrating experience of trying to get a particular SSIS Package running on a schedule within SQL Server Agent that provoked me to begin this blog.  So, let's dive in shall we...

The Project
I have a SQL Server 2005 SSIS Package that downloads a file from an ftp host, loads the file into a temp table on the local machine database, massages the data a little and then uploads to a web host database.  Sounds fairly simple right? And it is, until you get to the part where you setup a job in SQL Server Agent to execute your package.

Problem 1 : ProtectionLevel
At first, the package would not even run.  Immediately, I received some error about not being able to decrypt something.  A quick google search and I quickly discovered that I needed to do something about the package's ProtectionLevel.  If you don't know what that is, start here.

I opted for the EncryptSensitiveWithPassword protection level and choosing to customize the command line for the package running in SQL Server Agent because I wanted to just run it from the file system.  Here are the steps you need to follow to get this working:
  • In Visual Studio, select the package you want to modify the ProtectionLevel for
  • Click in the designer for the package (don't click on any items on the designer)
  • Now view the properties and look for the ProtectionLevel item and change it to EncryptSensitiveWithPassword
  • Now set the PackagePassword property and save/build your project
  • In SQL Server Agent, add/edit your job and select the step that will run your package
  • Click the Command line tab (you'll be prompted to enter the package password)
  • Select the Edit the command line manually option and add the following
    • /DECRYPT <password>

Problem 2 : Windows Firewall
Once I got the package to run, I ran into another problem.  It sat there for a long while, seemingly doing nothing (I was watching the target directory for the ftp downloaded file to appear) before finally failing with an FTP timeout error.  I initially considered that it must be a Windows Firewall issue, but I didn't want to just turn off Windows Firewall or open up FTP for everything.  I really wanted to just allow the process running my package to have access.  The question was what process is that?  sqlserver.exe? sqlagent90.exe?  

The answer is neither.  After digging around in the Event Logs I finally located the Windows Firewall logs and found the entry that gave me the answer.  The executable that actually runs an SSIS package is DTExec.exe (located here: %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\DTExec.exe)

So, all you have to do is add a rule in Windows Firewall to allow DTexec.exe on any protocol, port 21 and bam!... everything works like a champ.

Hope this helps someone save some time and frustration.