In SQL you can use the LIKE operator to perform simple keyword searches.  For example:

SELECT CustomerID, CompanyName, ContactName
FROM   dbo.Customers
WHERE  CompanyName LIKE '%St%' OR ContactName LIKE '%St%'

Using the Northwind database, the script above would return 21 results:

CustomerID  CompanyName                  ContactName
----------------------------------------------------------------
ALFKI       Alfreds Futterkiste          Maria Anders
BERGS       Berglunds snabbköp           Christina Berglund
EASTC       Eastern Connection           Ann Devon
ERNSH       Ernst Handel                 Roland Mendel
FRANR       France restauration          Carine Schmitt
GALED       Galería del gastrónomo       Eduardo Saavedra
GROSR       GROSELLA-Restaurante         Manuel Pereira
HILAA       HILARION-Abastos             Carlos Hernández
HUNGC       Hungry Coyote Import Store   Yoshi Latimer
...

However, this can be slow because it usually involves a full table scan.  SQL Server has a much more efficient way to perform full-text searches, which utilizes the Microsoft Search Engine.  This involves creating a full-text catalog, which is external to the database (actually stored outside normal database structure) so it requires a little configuration but has the potential to significantly improve search performance.

Defining A Full-Text Index
To define a full-text index, just navigate to the table you want the index on using SQL Management Studio, right click on the table name, and choose "Define Full-Text Index..." as shown below:

A wizard will appear like the one shown below, and you will need to configure what you want to index and how you want SQL to keep that index up-to-date (remember ... it is stored outside the normal database structure, so SQL has to keep it in-sync).  Most the time you don't need to change the wizard's default selections, but the next few screen shots show how I configured a full-text index for Northwind's Customer table.

After you have the index created, you can use the FREETEXT, FREETEXTTABLE, CONTAINS, and CONTAINSTABLE keywords (for more info on the functionality that each provides go here).  You could rewrite the query from the earlier example to be more like this:

SELECT   C.CustomerID, C.CompanyName, C.ContactName, R.[Rank]
FROM     dbo.Customers C INNER JOIN
         CONTAINSTABLE(dbo.Customers, (CompanyName, ContactName), '"St*"') R ON C.CustomerID = R.[KEY]
ORDER BY R.[Rank] DESC

Which would yield the following results:

CustomerID  CompanyName                  ContactName      Rank
----------------------------------------------------------------
LAZYK       Lazy K Kountry Store         John Steel       112
VICTE       Victuailles en stock         Mary Saveley     112
HUNGC       Hungry Coyote Import Store   Yoshi Latimer    96
LETSS       Let's Stop N Shop            Jaime Yorres     96
QUICK       QUICK-Stop                   Horst Kloss      96

The script using LIKE returned 21 results, but the one using the full-text index only returned 5 results.  However, that is actually a good thing, because the full-text index only returned relevant results (the ones someone would most likely be looking for if they searched for "St").  The LIKE clause returned any row that had "st" somewhere in it ... whether it was the first of a word or buried in the middle of it.  The full-text index is smart enough to only return words that start with "St," but notice that doesn't necessarily mean it is at the start of the string or preceded by whitespace ... because it returned "Quick-Stop" as well.  A hidden benefit of using full-text indexes is that the Microsoft Search Engine will intelligently parse the text, and yield more relevant results.

When you search a full-text index using the CONTAINSTABLE method, you are also able to utilize a new column named rank.  This column indicates how relevant the match is compared to the rest of the results.  In this example, the rank column isn't too useful.  But if you were searching a long product description, this column could become very useful.  I chose this really simple example to just show the bare bones functionality of searching SQL, but when you are searching columns with a lot of text (like a product description) ... that is when this approach really pays off in terms of efficiency, only returning relevant results, and providing a rank of how relevant a result was in comparison to the rest of the set.

This is based on a post by Vik Thairani that I have referred back to many times, and I just updated it for Outlook 2007 (he actually found the VB script here).  A lot of times I am frustrated when I enter a phone number in my phone (AT&T 8525 running Windows Mobile Pocket PC 5.0), because it always defaults to display contact names by "Last, First."  It seems a lot more intuitive to me if everyone is listed "First Last."  Outlook gives you the option to configure what you want the default format to be, but that functionality isn't available on my phone.  So when the two sync up, I have some listed one way and others listed differently.  The steps below will install a VB Script in Outlook that you can run anytime and it will spin through your every contact in your default contact folder and change them to all display in the "First Last" format ... instead of editing each one.

1. Set your default preference in Outlook
Go to Tools > Options > Contact Options
Set the Default "File As" Order

2. Setup Security to Allow Unsigned Macros
Go to Tools > Macro > Security
Change to "Warnings for all macros"
Restart Outlook

3. Creating the Macro
Go to Tools > Macro > Visual Basic Editor
In the Left hand window double click on "ThisOutlookSession" (you may have to expand the project tree)
Copy and paste the following script into the code window:

Public Sub FormatNamesAndNumbers()
    Dim objOL As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim objContact As Outlook.ContactItem
    Dim objItems As Outlook.Items
    Dim objContactsFolder As Outlook.MAPIFolder
    Dim obj As Object
    On Error Resume Next
    Set objOL = CreateObject("Outlook.Application")
    Set objNS = objOL.GetNamespace("MAPI")
    Set objContactsFolder = objNS.GetDefaultFolder(olFolderContacts)
    Set objItems = objContactsFolder.Items
    For Each obj In objItems
        If obj.Class = olContact Then
            Set objContact = obj
            With objContact
                ' Try to file the contact by their first name followed by their last name.
                ' If one of those names are missing, just use the one that is there, but if
                ' both are missing file it by the company name set for the contact.
                If .FirstName <> "" Or .LastName <> "" Then
                    .FileAs = Trim(.FirstName & " " & .LastName)
                Else
                    .FileAs = .CompanyName
                End If
                
                ' Format all of the common types of phone numbers to be in the standard
                ' (XXX)XXX-XXXX format
                If .MobileTelephoneNumber <> "" Then _
                    .MobileTelephoneNumber = FormatPhoneNumber(.MobileTelephoneNumber)
                If .BusinessTelephoneNumber <> "" Then _
                    .BusinessTelephoneNumber = FormatPhoneNumber(.BusinessTelephoneNumber)
                If .HomeTelephoneNumber <> "" Then _
                    .HomeTelephoneNumber = FormatPhoneNumber(.HomeTelephoneNumber)
                 
                .Save
            End With
        End If
        Err.Clear
    Next
    Set objOL = Nothing
    Set objNS = Nothing
    Set obj = Nothing
    Set objContact = Nothing
    Set objItems = Nothing
    Set objContactsFolder = Nothing
End Sub

Private Function FormatPhoneNumber(ByVal number)

    Dim defaultAreaCode As String
    Dim returnValue As String
    defaultAreaCode = "806"

    number = CStr(number)
    number = Replace(number, "-", "")
    number = Replace(number, "(", "")
    number = Replace(number, ")", "")
    number = Replace(number, "+1", "")
    number = Replace(number, " ", "")
    Select Case Len(number)
        Case 7
            ' The number doesn't include an area code ... append the default area code
            returnValue = "(" & defaultAreaCode & ") " & _
                            Mid(number, 1, 3) & "-" & Mid(number, 4, 4)
        Case 10
            returnValue = "(" & Mid(number, 1, 3) & ") " & _
                            Mid(number, 4, 3) & "-" & Mid(number, 7, 4)
        Case 11
            ' The number is prefixed with an unnecessary "1" for long distance
            returnValue = "(" & Mid(number, 2, 3) & ") " & _
                            Mid(number, 5, 3) & "-" & Mid(number, 8, 4)
        Case Else
            returnValue = number
    End Select
    FormatPhoneNumber = returnValue
End Function

4. Saving the Code and Running the Macro
Click File > Save
Close the editor window
Go to Tools > Macro > Macros
Select "ThisOutlookSession.FormatNamesAndNumbers" and click Run

That's it ... after the script completes all of your contacts will be in the "First Last" format, and the changes will be reflected on your phone next time you sync.  It is a good idea to change your macro security settings back to "Warnings for signed macros, all other macros are disabled", which you can do by repeating step 2 and choosing the appropriate option.

I also updated it to format mobile, home, and business phone numbers to be in the common (XXX)XXX-XXXX format.  Notice that the FormatPhoneNumber function has a default area code set in the first few lines.  It will append that to numbers that only have 7 digits (i.e. no area code).

.netTiers is the object relational mapping software that we currently use for all our applications.  In my experience it is extremely flexible, and saves me the several hours a week I used to spend writing custom CRUD (Create, Read, Update, Delete) methods to various objects in the database.  Plus since it is open-source and template-based, I can go in and change any part of it I see necessary. 

There are a lot of places on the Internet that list what the .netTiers templates provide, but I couldn’t really find anywhere that provided a clear, comprehensive overview of what it provided … without going into too much detail.  So I pieced together the following definition that explains what .netTiers is and the functionality it could provide.  .netTiers really does save me and my team hours of development time every week, so if it sounds like something that might help you out … you should definitely check into it further by going to www.netTiers.com.

.netTiers is a set of free CodeSmith templates that targets an existing datasource and automatically generates a personalized architecture to use in your .NET applications.  The generated code is custom to your domain, uses familiar patterns, and follows the guidance of Microsoft's recommended patterns and practices.  In fact, the .netTiers base architecture is built upon the Microsoft Enterprise Library Application Blocks.  Core features include:

• Generate strongly-typed business entities with a 1:1 mapping to the datasource (an entity for each table or view, with a property for each column):
  • All objects are serializable, and support trigger events
  • Implements an IEntity interface, which contains the columns that are present in every table
  • Each object has a concrete and a base class which it inherit from. The concrete class is generated just once, so after the first time you can then add your custom logic straight in the code-gened files
  • Uses a custom generic List for collections that are sortable, filterable, and directly bindable to datagrid and other ASP.NET and WinForm controls
• Generate Data Access Layer Components (DALC) for tables and views, with following database operations:
  • Support for basic CRUD methods, plus several other useful methods such as Select All, Paged Select, Find (with paging and sorting), etc and allows the addition of your own custom methods
  • Support for queries using primary & foreign keys, as well as keys that are part of an index or junction table
  • Support for Deep loading and saving, with children type selection and optional recursivity
• Option to use stored procedure or parameterized SQL inside the application
• Generates a complete nAnt build file, to compile, test and generate documentation
• Generates a full set of nUnit tests
• All code is fully nDoc commented and follow the Microsoft naming guidelines
• Open-source so you can modify the templates if necessary

Back in March I attended DevConnections in Orlando, and sat in one of Rob Howard's sessions on CodeSmith.  I have always had a pretty negative outlook on "code generators", because most tools don't give you enough control over what code is actually created.  I take a lot of pride in writing applications that are extremely scalable, and frankly some of the code that is generated is not as efficient or isn't as optimized for my particular application and environment as I would like.  So before I even sat down in that session, Rob already had an uphill battle to fight.

However, the tool that Rob showed us was quite different than any other code generator I had ever seen.  Simply put, CodeSmith is a template-based code generator that is commonly used to generate ORM-type architectures based on your database and settings.  The key phrase in that sentence is template-based.  There are a ton of templates available that are provided by a community and open source.  This means that you can download one of the proven architectures like .netTiers, CSLA, NHibernate, Wilson's ORMapper, or others and if something isn't coded, commented, or formatted exactly how you would like it ... you have the full ability to modify the template to your liking.  This means you don't have to write the entire architecture, but have control to change any line of generated code if you need to.  In my experience since using the product, you really don't have to mess with them too much, but it really gives me piece of mind to know that I could change the behavior instead of just having to live with whatever code the black-box generator spits out.

It is kind of hard to conceptualize what all CodeSmith has the ability to do.  It can generate C# and VB.NET code, but can also generate anything else: T-SQL, JavaScript, XML, RTF, or a language you just made up.  This was hard for me to grasp at first, but CodeSmith Studio (the IDE used to create/view/edit templates) looks and feels a look like Visual Studio.  But instead of writing code in Visual Studio that at the end of the day will become a software applications, you write code in CodeSmith Studio in a way that feels extremely similar to .NET and when you compile and run the code the output is generated code, XML, T-SQL scripts, or could even be documentation.  So it is kind of open-ended as to what you can actually use the tool to generate.  I will try to post some examples on the site sometime soon that demonstrates some of the significantly different types of things you can generate.

Bottom line of CodeSmith: This software is one of the most useful development tools I've seen.  In fact, I see CodeSmith and the RedGate Tools as must-buys for serious developers, and are almost on the same plain as Visual Studio itself.  Both of those tools are extremely reasonably priced, and the time saved developing and maintaining an app far outweigh their cost.  At the time this was written, CodeSmith Standard was $99/license, and CodeSmith Professional was $399.  For more info go to CodeSmithTools.com.

As IT professionals we are always dumbfounded when we see a user who has passwords written on sticky notes and then used to decorate the edges of a monitor.  We are even guilty of keeping track of some passwords in a spreadsheet from time to time, which is also not a security best practice, and we don't even like to think about people who really just have one or two passwords they use for everything.  Why do we do this?  Too many passwords.  I currently have around 40 passwords I have to remember for various software, networks, and web sites.

Most of my time is spent developing and maintaining my company's intranet/extranet, and I have it set up to use a hybrid forms/windows authentication.  That way if the user is already signed into a computer on our domain, the site will pull their windows credentials, match it up with a user in the database, and automatically log them in through forms authentication.  This happens in less than a second and the user never knows it happened.  But, users outside the company (that aren't signed into our network) can use the classic username/password form and gain access to the site without needing a user account in Active Directory. 

However, there are times when an employee who typically access the site from inside the network need to look at something from home or on the road.  Since they aren't signed into the domain, the site won't automatically pull their windows credentials ... so they are sent to the classic forms authentication log in page.  What credentials should they try to use?  Well, we could give them one more username/password to keep track of ... or the solution I went for is if the credentials don't match up with any forms authentication user in the database, query active directory from code to see if the credentials the user provided are valid domain credentials.  So in reality, on the classic forms authentication page the user could enter forms authentication credentials (validated against the database), or enter domain credentials (validated against Active Directory).

There are other solutions out there (like this one) that allow you to use forms authentication with Active Directory, but really the code that I needed was very simple.  It is based on a snippet I found in Developing More-Secure Microsoft ASP.NET 2.0 Applications.  It uses LDAP queries to authenticate a given set of credentials against Active Directory, but creating a DirectoryEntity object with those credentials and then forcing them to bind.  If it returns an error code of 2147023570, that indicates a login failure ... which means the credentials the user provided are not valid domain credentails.  If no error occurs, that means the credentials matched a user in Active Directory.

/// <summary>
/// Returns true if the given credentials match a valide forms authentication user in the database or an account in Active Directory, 
/// and false otherwise.
/// </summary>
public static bool ValidateCredentials(string Username, string Password)
{
    bool IsValid;
    using (SqlConnection thisConnection = new SqlConnection(Common.ConnectionString))
    {
        string SprocName = "sproc_ValidateCredentials";
        SqlCommand thisCommand = new SqlCommand(SprocName, thisConnection);
        thisCommand.CommandType = CommandType.StoredProcedure;
        thisCommand.Parameters.AddWithValue("@Username", Username);
        thisCommand.Parameters.AddWithValue("@Password", Password);
        thisConnection.Open();
        IsValid = Convert.ToBoolean(thisCommand.ExecuteScalar());
    }

    if (!IsValid)
        IsValid = AuthenticateAgainstActiveDirectory(Username, Password);

    return IsValid;
}

/// <summary>
/// Returns true if the given UserID and Password were valid network credentials according to Active Directory, and false otherwise.
/// The code uses LDAP to communicate with Active Directory, and simply creates a DirectoryEntry object using the given credentials
/// and then takes some action that causes a bind.  If no error occurs, the user would be allowed to log onto the domain.
/// </summary>
public static bool AuthenticateAgainstActiveDirectory(string Username, string Password)
{
    // Strip everything but the Username out from the text the user provided
    Username = Username.ToLower().Replace(@"DomainName\", "");

    // Create the entity that will connect to the LDAP server
    DirectoryEntry thisEntry = new DirectoryEntry(@"LDAP://192.168.0.1", Username, Password);

    try
    {
        // Perform an action that will force the bind to ActiveDirectory ... if this doesn't throw
        // an error then a user would be able to log onto the network with the given credentials
        thisEntry.RefreshCache();
        return true;
    }
    catch (System.Runtime.InteropServices.COMException thisExc)
    {
        // Make sure the error that got thrown was a login failure, and if it wasn't rethrow the error
        if (thisExc.ErrorCode != -2147023570)
            throw;

        return false;
    }
}