Starting Out

I am thinking of a new career or adding to my income.

It’s not uncommon for the trainers at the Wardlaw Training Center to hear this from people that have lost jobs, or have realized that the job they are in is going nowhere for them. Even students fresh out of college may realize that a shrinking market for new employees could limit what job they may be employable for.

The path of becoming a successful independent insurance claims adjuster can be a very complex one. The adjusters that are deployed by Wardlaw Claims Service are fortunate because Wardlaw has helped make this life changing challenge an obtainable goal for most people.

Most people hear about these jobs from an adjuster working a claim for them or from knowing someone in the claims business already. Normally these types of resources are great starting points to asking questions and finding out about the job. Often times the people in these positions can give a true life experience as to what an adjuster is about.

The person asking the questions needs to remember that these people will usually tell them of the great money they can earn, all of the travel involved and seeing all the country. These are true facts and will generate quite a bit of interest.

BUT, in reality, becoming an adjuster shouldn’t be for the dollar or the sightseeing. It is a job that requires a great deal of empathy and the ability to handle difficult situations day in and day out. Take as an example, a large hurricane in a very populated area of a state. The claims volumes can be huge for most carriers and every adjuster on the storm site may have a voluminous amount of claims. The adjuster has to remember that every single person only cares about only one claim, their claim.

Given this example most people say they can handle this type of situation and are eager to start a new career. The biggest question now is HOW?

Starting out by gathering information about the job is a good idea.

Most states require that a person have a license to be an adjuster. This can be obtained several different ways but the most common and best way is to take a license class in a classroom setting. The advantage of the classroom course is the interaction with the instructor and other students. Networking is a very valuable resource in this profession. In a classroom course, the course will focus on the requirements set by the state and the contents needed to study for passing the license exam. Along the way, the student will be introduced to terminology to which they have never been exposed.

This is also a good way for students to develop a resume and have the instructor’s insight on the next steps after passing the test and obtaining the license.

Next blog …I have a license, now what?

Abstract Factory Pattern In C#.Net

Sometimes when building and application you need to plan for changes that you cannot possibly plan for. For example, if you are building an application to import data from different sources and you will receive that data in completely different formats one, approach might be to create a different application for each import. That's a valid approach, but one that requires a good deal of code duplication. Let us examine the Abstract Factory pattern and see how it might help us here.

Both the old classic Design Patterns (Gamma, Helm, Johnson and Vlissides) and the new classic Head First Design Patterns from O'Reilly (Freeman and Freeman) define the Abstract Factory pattern as a way to "Provide an interface for creating families of related or dependent objects without specifying their concrete classes. " What does that actually mean? In the example business problem from above, we could create a class for each import type that implements an interface, therefore decoupling the import parsing from the persistence of the data. In other words, we don't have to write a new application for each import type, just a new class.

The first thing I will do is create a new project and then add an Enum named ParsableType and an Interface named IImportParsable.

public class ParsableTypes

{

public enum ParsableType

{

Excel = 1,

FixedFormat = 2

}

}

public interface IImportParsable

{

bool Parse();

}

Next, I will create 2 new classes that implement our interface...

public class ExcelParser : IImportParsable

{

public bool Parse()

{

//do some excel parsing here

return true;

}

}

public class FixedFormatParser : IImportParsable

{

public bool Parse()

{

//do some text file parsing here

return true;

}

}

Next, we need a factory to determine which parser to use...

public class ImportParsableFactory

{

public static IImportParsable GetImportParser(ParsableTypes.ParsableType parsableType)

{

IImportParsable importParsable = null;

switch (parsableType)

{

case ParsableTypes.ParsableType.Excel :

importParsable = new ExcelParser();

break;

case ParsableTypes.ParsableType.FixedFormat :

importParsable = new FixedFormatParser();

break;

}

return importParsable;

}

}

Finally, we just need a controller to get the parse type and then call the parse method...

public void Import(int parseID)

{

IImportParsable importParser = ImportParsableFactory.GetImportParser((ParsableTypes.ParsableType)parseID);

importParser.Parse();

}

}

As new parse needs arise, we just add an enum entry, create a new class that implements IImportParsable and add another case to our switch and we are done. We have a nice decoupled approach that we can use by itself or combine with the Facade or Decorator patterns to add even more pattern goodness.

Introduction to Formatting Query Results as XML in Microsoft SQL Server 2008

We can use the FOR XML clause with SELECT statements in Microsoft SQL Server to produce XML output from our queries.  We might use this feature when creating output that will be consumed by an application that understands XML.  This post will demonstrate using this feature to create XML output of a contact list.


Here is the T-SQL used to create and populate our Contacts table:

CREATE TABLE Contacts
(
ContactId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
PhoneNumber VARCHAR(20) NOT NULL,
EmailAddress VARCHAR(50) NOT NULL
);

INSERT INTO Contacts ( FirstName, LastName, PhoneNumber, EmailAddress )
VALUES ( 'Smith', 'Jon', '254-555-1234', 'jon@example.com' );


INSERT INTO Contacts ( FirstName, LastName, PhoneNumber, EmailAddress )
VALUES ( 'Rogers', 'Amanda', '254-555-4321', 'amanda@example.com' );


INSERT INTO Contacts ( FirstName, LastName, PhoneNumber, EmailAddress )
VALUES ( 'Gomez', 'Paul', '254-555-5252', 'paul@example.com' );


INSERT INTO Contacts ( FirstName, LastName, PhoneNumber, EmailAddress )
VALUES ( 'Peterson', 'James', '254-555-9876 ext 234', 'james@example.com' );

Let us assume that we have a contact management application that we wish to export this data to and that this fictitious application can import XML that is formatted like the sample below.


<?xml version="1.0"?>
<Contact-List>
<Contact>
<First-Name>John</First-Name>
<Last-Name>Doe</Last-Name>
<Telephone>800-555-1111</Telephone>
<E-mail>doe.john@example.com</E-mail>
</Contact>
<Contact>
<First-Name>Tommy</First-Name>
<Last-Name>Atkins</Last-Name>
<Telephone>800-555-2222</Telephone>
<E-mail>atkins.tommy@example.com</E-mail>
</Contact>
</Contact-List>


As you can see in addition to formatting the output as XML we will also need to map our table's columns to the XML format's elements: "FirstName" in the table will be "First-Name" in the XML.   We can use the FOR XML clause in our SELECT statement to shape the query's result to match the required format.  Below is the T-SQL used to create the XML.

SELECT 

FirstName AS 'First-Name',

LastName AS 'Last-Name',

PhoneNumber AS Telephone,

EmailAddress AS 'E-mail'

FROM Contacts

ORDER BY LastName, FirstName

FOR XML PATH('Contact'), ROOT('Contact-List');

Here is the result of that query.

<Contact-List>

  <Contact>

    <First-Name>Rogers</First-Name>

    <Last-Name>Amanda</Last-Name>

    <Telephone>254-555-4321</Telephone>

    <E-mail>amanda@example.com</E-mail>

  </Contact>

  <Contact>

    <First-Name>Peterson</First-Name>

    <Last-Name>James</Last-Name>

    <Telephone>254-555-9876 ext 234</Telephone>

    <E-mail>james@example.com</E-mail>

  </Contact>

  <Contact>

    <First-Name>Smith</First-Name>

    <Last-Name>Jon</Last-Name>

    <Telephone>254-555-1234</Telephone>

    <E-mail>jon@example.com</E-mail>

  </Contact>

  <Contact>

    <First-Name>Gomez</First-Name>

    <Last-Name>Paul</Last-Name>

    <Telephone>254-555-5252</Telephone>

    <E-mail>paul@example.com</E-mail>

  </Contact>

</Contact-List>

The FOR XML clause instructs the server to transform the results to XML.  The PATH parameter specifies that each record should be wrapped in an element named "Contact".  The ROOT parameter specifies that the result set should be wrapped in an element names "Contact-List".  Note that since the elements contain dashes and dashes are not permitted in column names an alias is needed in our query to name the elements to match the XML elements.   Note that element names in query such as 'First-Name' can be escaped by single quotes, square brackets, or double quotes.  All of the following are equivlement: 'First-Name', "First-Name", [First-Name].

The FOR XML has additional parameters that can be used to control the format of the output.  This post is intended as a basic introduction.

References

"Basic Syntax of the FOR XML Clause" from SQL Server Books Online

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

HTML 5 Structure

HTML 5 came out with a whole new set of elements to make structuring a web page much easier. When marking up a page in HTML 4 most of the structure was contained within div elements.

Current versions of HTML 4 do not give us the ability to describe these parts more accurately. HTML 5 addresses this by giving us new elements that correspond with each of the basic layouts of a web page.

The markup would look like this:

<html>
<header>
<title>...</title>
</header>
<body>
<header>...</header>
<nav>...</nav>
<aside>...</aside>
<article>
<section>...</section>
</article>
<footer>...</footer>
</body>
</html>

For more information on the differences between HTML 4 and HTML 5 take a look at W3C

Singleton Design Pattern

Sometimes you need to guarantee that only one instance of a class is created. One example might be a database object where you only want one connection. This class should be available globally, but should only have one instance. The Singleton creational pattern conforms to this requirement.

The O'Reilly book Head First Design Patterns By Eric and Elisabeth Freeman define the Singleton Pattern as a class that has only one instance and provides a global point of access to it.

The classic book Design Patterns by Gamma, Helm, Johnson and Vlissides (Gang of Four) add the following points.

Participants

• Singleton

1. Defines an Instance operation that lets clients access it unique instance. Instance is a class operation.
2. May be responsible for creating its own unique instance.

For my example, I'll create a singleton class in c# using the .Net Framework 4.0 that has a connection object in it. First, start a new console app project. Next, add a new class and name it Singleton.cs with following code...

namespace SingletonExample
{
public sealed class Singleton
{
private static readonly SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=Chinook;Integrated Security=SSPI;");

static Singleton()
{
}

Singleton()
{
}

public static SqlConnection Connection
{
get
{
if (connection.State == ConnectionState.Closed) connection.Open();
return connection;
}
}
}
}

Now, in your program.cs file add the following code to Main...

static void Main(string[] args)
{
SqlConnection connection1 = singleton.connection;
SqlConnection connection2 = singleton.connection;
SqlConnection connection3 = singleton.connection;
SqlConnection connection4 = singleton.connection;
SqlConnection connection5 = singleton.connection;

Console.ReadLine();
}

As you step through the code run sp_who2 on the Chinook database and you will see only one connection added.

By the way, the Chinook database is on Codeplex and is designed as alternative to Northwind. It's small, will run on SQL Server, Oracle and MySQL and installs with a single script.

Enforcing Uniqueness on Optional Fields in SQL Server Database Tables

Enforcing the uniqueness of data in a table is a common requirement.  For example an "Employees" table might contain an "EmployeeID" field that must uniquely identify each row.  This is usually handled by creating a primary key or unique constraint on the field.  This works well when the field in question is required.  In our example each employee is assigned an employee ID number.  Sometimes however this is not sufficient to cover all circumstances.  Let us assume that each employee may be assigned a company email address, but not all employees will be given an email address.  No two employees can share the same email address.  We want the "EmailAddress" field in the Employees table either to be empty or contain a value not already in the table.  We can enforce the uniqueness of the email address values either by using an indexed view or by creating an index.

Here is the T-SQL used to create the Employees table we will use for our examples.

CREATE TABLE Employees

(

EmployeeID CHAR(4) NOT NULL PRIMARY KEY,

LastName VARCHAR(20) NOT NULL,

FirstName VARCHAR(20) NOT NULL,

EmailAddress VARCHAR(30) NOT NULL DEFAULT ''  

);

Option 1: Using an Indexed View

We can create a view of Employee records with an email address then create a unique index on the EmailAddress field.  If a user tries to insert a duplicate email address the insert will fail.

Here is the T-SQL used to create the view and index.   Note that the view only contains records which have a non-empty email address.

CREATE VIEW EmployeeEmailAddresses WITH SCHEMABINDING

AS

SELECT EmailAddress

FROM dbo.Employees

WHERE EmailAddress != '';

GO

CREATE UNIQUE CLUSTERED INDEX Index_EmployeeEmailAddresses_EmailAddress

ON EmployeeEmailAddresses ( EmailAddress );

We can then insert some records into the Employees table using the T-SQL statements below.

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E100', 'Smith', 'Jon', 'smith@example.com' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E200', 'Pond', 'Amy', '' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E300', 'Noble', 'Donna', 'noble@example.com' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E400', 'Jones', 'Martha', '' );

-- this insert will fail

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E500', 'Smith', 'Sarah-Jane', 'smith@example.com' );

The last insert will fail because a record already exists with the email address "smith@example.com".  This approach will work in both SQL Server 2005 and 2008.

Option 2: Create an Index

SQL Server 2008 introduced the WHERE clause into the syntax for the CREATE INDEX statement.  We can create a unique index on records which are not empty with the T-SQL statement below.

CREATE UNIQUE INDEX Index_Employees_EmailAddress

ON Employees ( EmailAddress )

WHERE ( EmailAddress != '' );

To test this index first drop the indexed view and empty the Employees table, then insert our test data again.  The last insert will fail.

-- this will drop the indexed view we created earlier if it exists

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'EmployeeEmailAddresses' )

BEGIN

DROP VIEW EmployeeEmailAddresses;

END;

--  empty the Employees table

DELETE 

FROM Employees;

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E100', 'Smith', 'Jon', 'smith@example.com' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E200', 'Pond', 'Amy', '' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E300', 'Noble', 'Donna', 'noble@example.com' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E400', 'Jones', 'Martha', '' );

-- this insert will fail

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E500', 'Smith', 'Sarah-Jane', 'smith@example.com' );

This technique is new to SQL Server 2008 and is not backwards compatible.  

For more information on each approach consult the SQL Server documentation.

References

Create View from SQL Server Books Online:

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

Create Index from SQL Server Books Online:

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

Using SET FMTONLY OFF in the Query string of Report Server

By default FMTONLY is set to ON in Report Server. When using a SQL statement containing temp tables an error message commonly occurs. You may get an message like "There is an error in the query. Invalid object name '#myTempTable'".



To fix this I’ve used the command SET FMTONLY OFF at the top of my SQL statement. This allows the SQL statement to return rows with data. The SET FMTONLY ON will only return column information.





Use the following SQL statement, changing the FMTONLY from ON to OFF, in Query Analyzer to see how this works.

USE AdventureWorks;
GO
SET FMTONLY ON;
GO
SELECT *
FROM HumanResources.Employee;
GO
SET FMTONLY OFF;
GO