Stuart Cox's invitation is awaiting your response

Stuart Cox would like to connect on LinkedIn. How would you like to respond?   Stuart Cox Sr. Engineer at Phytel Confirm you know Stuart You are receiving Reminder emails for pending invitations. Unsubscribe. © 2013 LinkedIn Corporation. 2029 Stierlin Ct, Mountain View, CA 94043, USA.

Stuart Cox's invitation is awaiting your response

Stuart Cox would like to connect on LinkedIn. How would you like to respond?   Stuart Cox Sr. Engineer at Phytel Confirm you know Stuart You are receiving Reminder emails for pending invitations. Unsubscribe. © 2013 LinkedIn Corporation. 2029 Stierlin Ct, Mountain View, CA 94043, USA.

Invitation to connect on LinkedIn

From Stuart Cox Sr. Engineer at Phytel Dallas/Fort Worth Area I'd like to add you to my professional network on LinkedIn. - Stuart Confirm that you know Stuart You are receiving Invitation to Connect emails. Unsubscribe © 2012, LinkedIn Corporation. 2029 Stierlin Ct. Mountain View, CA 94043, USA

SharePoint 2007 (MOSS) Search Won't Crawl PDF's

We had a SharePoint 2007 (MOSS) implementation with many, many PDF documents. We installed the Adobe IFilter and crawled the site, but the conents of the PDF file were not being indexed. The icon showed up correctly, searches on document titles worked ok, but the PDF content was not searchable. Now for the fix! We had to install the IFilter on the database server.

Create a Linked Server to an Access Database

On a recent project I've had the need (unfortunately) to pull data from Access into SQL Server. We implemented this using Linked Server to query the Access database. In case I need to find it again, here's the code...

The Linked Server can be created using SQL Enterprise Manager, but we needed to script it.

USE MASTER
GO

-- Remove linked servers and drop associated logins
EXEC sp_dropserver 'Link_MyDatabase', 'droplogins'

-- Create a linked server
EXEC sp_addlinkedserver 'Link_MyDatabase', 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'D:\MyDatabase.mdb'

-- Provide login credentials for the access database (no username or password required in this case)
EXEC sp_addlinkedsrvlogin 'Link_MyDatabase', 'false', NULL, NULL, NULL

To query the database, we can add the linked server to the front of the table name (no database name or owner name required). Sometimes we needed to query around bad data before it gets to the ODBC driver to prevent data corruption issues and ODBC driver errors. Date data proved particularly troublesome in this regard. To do so, we use an OPENQUERY statement, which runs the query on Access before it gets to the OLEDB driver.

-- Query data directly
SELECT *
FROM Link_MyDatabase...MyTable

-- Query data using OPENQUERY to avoid invalid date values
SELECT *
FROM OPENQUERY(WXIS_OPS, 'select * FROM MyTable WHERE BadDate > #01/01/1900#')

Alternating Rows In SQL Reporting Services

Reporting Services does not support alternating row colors out of the box. But you can get the effect using a little custome code and expressions. Most object properties support expressions. For example, we can specify the background color for a cell as a color (i.e. "White") or a code expression.

To get alternating row colors, I use two functions which I add to the report code module. To access the report code module, view the report options, select the Code tab, and copy the following code into the code window.

Shared AltColorRowCounter as Long

Public Function GetAltColor(color As String, altColor As String, bumpRowCounter As Boolean) As String
  
  If (bumpRowCounter = True) Then
    AltColorRowCounter += 1
  End If

  If (AltColorRowCounter Mod 2 <> 0) Then
      Return color
  Else
      Return altColor
  End If
  
End Function

Public Function ResetAltColor(color As String) As String
  
  AltColorRowCounter = 0
  Return color

End Function

The code window in Reporting Services is painfully small with no code support. I usually do my coding in a script editor and then paste the code into the code window when I'm done. Since Reporting Services accepts most Visual Basic .Net code, I use Visual Studio) to create a .vb file, then do my code editing there. That way I at least get some language intellisense support, formatting and undo functionality to make code editing more pleasant.

The code contains 2 functions and a shared module-level variable. The shared variable AltColorRowCounter is used to hold a row counter, used to determine if we're on an odd or even row. We use this counter to alternate row or column colors. Reports are rendered from top to bottom, left to right. The counter is bumped and reset by our code as the report is rendered, modifying the colors accordingly by calling the following functions.

The ResetAltColor() fuction is used to reset the counter. It returns the string passed in as the color, and I normally use it on a background expression. For example, if I want the alternating rows within a group, and usually like to reset the counter on each group header row. In the group header, I call the reset method with the background color of my group header (in this case LightSteelBlue). To do this, I set the Background expression of the group header. In this example, I set the expression to =Code.ResetAltColor("LightSteelBlue").

On the rows, I call the GetAltColor() function. I pass it the 2 colors I want to alternate, and a third boolean parameter I've named bumpRowCoutner. The bumpRowCounter parameter is used to increment the counter. I normally set it to True on the first column in the row, and leave it False for all additional columns. In this case, I want alternating row colors of White and LightGrey. On the first detail row column, I set the Background expression to =Code.GetAltColor("White", "LightGrey", True). On all the other columns in the row, I set the Background expression to =Code.GetAltColor("White", "LightGrey", False).

This gives me alternating row colors. If I set it to True for each column, I'll get alternating column colors, which can also be a nice effect to have in your bag of tricks. These two functions can be used to give you a wide variety of alternating color effects.

Generic Null Handling Class for .Net 2.0

Null conversions are a real pain. Nullable types in the 2.0 framework are a great addition, but fall a bit short in my mind. They don't handle DBNull values coming out of the database, so that leaves you with more null conversion work to do. For example:

// Wish these would work and do the null conversion for me
// That would just be too good to be true, wouldn't it?
int? i = DBNull.Value;
DateTime myDate = DBNull.Value ?? DateTime.Now;

I find that I typically need to convert the data coming out of the database to a .Net value type. If it's a nullable field in the database, I can implement it in the application with a nullable type, a default value, or the old MinValue trick. I'm usually doing some sort of if-then logic to pull the actual value or supply a default if the value is DBNull.

For example, in .Net 1.1, I would typically use the MinValue to represent null. DateTime.MinValue for example would represent a null date value. In .Net 2.0, I can use the same method, or I can create a nullable DateTime construct and set a null value with some code like this:

// Code to check for null integer value and default null values
int? managerID1 = (reader["MANAGER_ID"] != DBNull.Value) ? (int?) reader["MANAGER_ID"] : null;
int managerID2 = (reader["MANAGER_ID"] != DBNull.Value) ? (int) reader["MANAGER_ID"] : Int32.MinValue;
int managerID3 = (reader["MANAGER_ID"] != DBNull.Value) ? (int) reader["MANAGER_ID"] : 0;

// Date Sample
DateTime? hireDate1 = (reader["HIRE_DATE"] != DBNull.Value) ? (DateTime?) reader["HIRE_DATE"] : null;
DateTime hireDate2 = (reader["HIRE_DATE"] != DBNull.Value) ? (DateTime) reader["HIRE_DATE"] : DateTime.MinValue;
DateTime hireDate3 = (reader["HIRE_DATE"] != DBNull.Value) ? (DateTime) reader["HIRE_DATE"] : DateTime.Now;

In my mind, it's a lot of repetitive code. So I've used C# generics to create a class called NullConvert. I use it to convert null and DBNull values to a default value. It works with all datatypes I've run accross, handles conversions between datatypes intrincally, and even handles conversions from string, int, or null values to enumerations. Full code for the class is listed at the bottom of this post. Here's some sample usage:

// Sample usage of NullConvert class using generics  
// Shows conversion to both nullable and non-nullable types
int? managerID1 = NullConvert.IsNull<int?>(reader["MANAGER_ID"], null);
int managerID2 = NullConvert.IsNull<int>(reader["MANAGER_ID"], Int32.MinValue);
int managerID3 = NullConvert.IsNull<int>(reader["MANAGER_ID"], 0);

// String sample
string managerName1 = NullConvert.IsNull<string>(reader["MANAGER_NAME"], null);
string managerName2 = NullConvert.IsNull<string>(reader["MANAGER_NAME"], Int32.MinValue);
string managerName3 = NullConvert.IsNull<string>(reader["MANAGER_NAME"], 0);

// Date sample
DateTime? hireDate1 = NullConvert.IsNull<DateTime?>(reader["HIRE_DATE"], null);
DateTime hireDate2 = NullConvert.IsNull<string>(reader["HIRE_DATE"], DateTime.MinValue);
DateTime hireDate3 = NullConvert.IsNull<string>(reader["HIRE_DATE"], DateTime.Now);

// Sample of data converstion.  SQL Decimal is automatically converted to
// decimal, double, or other numeric data types
decimal hourlyRate1 = NullConvert.IsNull<decimal?>(reader["HOURLY_RATE"], null);
double hourlyRate2 = NullConvert.IsNull<double?>(reader["HOURLY_RATE"], null);
float hourlyRate3 = NullConvert.IsNull<float?>(reader["HOURLY_RATE"], null);

// Sample of enumeration conversion.  
// Handles conversion of textual or numeric representation and null values
// Handles both nullable and non-nullable enumeration types
enum EmployeeType {Employee = 1, Manager = 2, Contractor = 3};
ContactType? contactType1 = NullConvert.IsNull<ContactType?>(reader["CONTACT_TYPE"], null);
ContactType contactType2 = NullConvert.IsNull<ContactType>(reader["CONTACT_TYPE"], ContactType.Employee);
ContactType contactType2 = NullConvert.IsNull<ContactType>("Manager", ContactType.Employee);
ContactType contactType2 = NullConvert.IsNull<ContactType>(2, ContactType.Employee);

The class also includes a couple of other useful methods:

• The NullConvert.ChangeType() method is an extended implementation of the System.Convert.ChangeType() method, designed to handle nullable types and enumerations which are not supported by the standard .Net implementation. Thanks to Peter Johnson for the original code, which can be found on ASP Alliance, I just added support for enumerations by adding a line or two.
• The NullConvert.SqlNullString() method handles string values being stored in the database. I don't like storing empty strings in my database. If I have an empty string entered by a user, I like to store it as a NULL value in the database. The SqlNullString() method handles this conversion, and also trims the string by default.

Full code for the NullConvert class:

using System;
using System.ComponentModel;

/// <summary>
/// Utility class exposing null handling routines to convert null values coming to
/// and from the database.
/// </summary>
public class NullConvert
{

    #region Constructors

    // Hide public constructor to implement a singleton pattern
    private NullConvert()
    {
    }

    static NullConvert()
    {
    }

    #endregion

    #region Static Methods

    /// <summary>
    /// Checks a value for a null or DBNull value.  If the value is not null, value is converted
    /// to the type specified. Works on value types, enumerations, and nullable values types.  If
    /// the value is null, the default value is returned.
    /// </summary>
    /// <typeparam name="T">Type expected in return</typeparam>
    /// <param name="value">Value to check and convert as needed</param>
    /// <param name="defaultValue">Default value to return if value is null or DBNull</param>
    /// <returns>Converted value of type specified as T</returns>
    public static T IsNull<T>(object value, T defaultValue)
    {
        //// Test for nullable types, if we need it later.  This works!
        //Type type = typeof(T);
        //if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
        //{
        //    throw new ApplicationException("Hit it");
        //}

        if (value == null || value == DBNull.Value)
        {
            return defaultValue;
        }
        else
        {
            return (T) NullConvert.ChangeType(value, typeof(T));
        }
    }

    /// <summary>
    /// Prepares string values for insertion into the database.  Converts value to a string,
    /// trims it (optional), and checks for an empty string.  If String.Empty is the result,
    /// a null string is returned to insert a NULL value into the database.
    /// </summary>
    /// <param name="value">Value to convert</param>
    /// <param name="trimString">Trim the resulting string? Defaults to True</param>
    /// <returns>Resulting string or null string</returns>
    public static string SqlNullString(object value, bool trimString)
    {
        // Convert value to string
        string result = Convert.ToString(value);

        // Trim it?
        if (trimString)
        {
            result = result.Trim();
        }

        // If string is empty, convert it to null
        if (String.IsNullOrEmpty(result))
        {
            return null;
        }
        else
        {
            return result;
        }
    }

    /// <summary>
    /// Prepares string values for insertion into the database.  Converts value to a string,
    /// trims it (optional,) and checks for an empty string.  If String.Empty is the result,
    /// a null string is returned to insert a NULL value into the database.
    /// </summary>
    /// <param name="value">Value to convert</param>
    /// <returns>Resulting string or null string</returns>
    public static string SqlNullString(object value)
    {
        return SqlNullString(value, true);
    }

    /// <summary>
    /// Returns an Object with the specified Type and whose value is equivalent to the specified object (handles nullable types).
    /// </summary>
    /// <param name="value">An Object that implements the IConvertible interface.</param>
    /// <param name="conversionType">The Type to which value is to be converted.</param>
    /// <returns>An object whose Type is conversionType (or conversionType's underlying type if conversionType
    /// is Nullable and whose value is equivalent to value. -or- a null reference, if value is a null
    /// reference and conversionType is not a value type.
    /// </returns>
    /// <remarks>
    /// This method exists as a workaround to System.Convert.ChangeType(Object, Type) which does not handle
    /// nullables as of version 2.0 (2.0.50727.42) of the .NET Framework. The idea is that this method will
    /// be deleted once Convert.ChangeType is updated in a future version of the .NET Framework to handle
    /// nullable types, so we want this to behave as closely to Convert.ChangeType as possible.
    /// This method was written by Peter Johnson at:
    /// http://aspalliance.com/author.aspx?uId=1026.
    /// </remarks>
    public static object ChangeType(object value, Type conversionType)
    {
        // Note: This if block was taken from Convert.ChangeType as is, and is needed here since we're
        // checking properties on conversionType below.
        if (conversionType == null)
        {
            throw new ArgumentNullException("conversionType");
        } // end if


        // If it's not a nullable type, just pass through the parameters to Convert.ChangeType
        if (conversionType.IsGenericType &&
          conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
        {
            // It's a nullable type, so instead of calling Convert.ChangeType directly which would throw a
            // InvalidCastException (per http://weblogs.asp.net/pjohnson/archive/2006/02/07/437631.aspx),
            // determine what the underlying type is
            // If it's null, it won't convert to the underlying type, but that's fine since nulls don't really
            // have a type--so just return null
            // Note: We only do this check if we're converting to a nullable type, since doing it outside
            // would diverge from Convert.ChangeType's behavior, which throws an InvalidCastException if
            // value is null and conversionType is a value type.
            if (value == null)
            {
                return null;
            } // end if

            // It's a nullable type, and not null, so that means it can be converted to its underlying type,
            // so overwrite the passed-in conversion type with this underlying type
            NullableConverter nullableConverter = new NullableConverter(conversionType);
            conversionType = nullableConverter.UnderlyingType;
        } // end if

        // Is this an enumeration type?
        // Enumeration conversion added to Peter Johnson's original code by Stuart Cox
        if (conversionType.IsEnum)
        {
            return Enum.Parse(conversionType, value.ToString(), true);
        }
        else
        {
            // Now that we've guaranteed conversionType is something Convert.ChangeType can handle (i.e. not a
            // nullable type), pass the call on to Convert.ChangeType
            return Convert.ChangeType(value, conversionType);
        }
    }

    #endregion Static Methods
}