Thursday, May 24, 2007

Custom string formatting in .Net

Recently, while working on a project, I came across the need to format the phone number supplied as string in the standard U.S. phone format(i.e. 1234567890 should be displayed as (123) 456-7890. The phone number was being fetched as a string from the database. I personally feel, that a better option to operate on strings is to save them in a formatted manner i.e. the way you wish to display them, but this is not always possible.

So how do you custom format the strings in .Net? You got it - you use IFormatProvider. IFormatProvider is an interface that provides you an option to format the value as per your requirements. The sole member contained in this interface is GetFormat(). To provide the custom formatting, you need to make a class that implements IFormatProvider. Apart from this, your class must also implement ICustomFormatter. This is the interface that actually handles the custom logic of formatting the supplied value. ICustomFormatter contains a single method named Format() which accepts the format in which value should be formatted, the value that is to be formatted and an instance of IFormatProvider(i.e your custom class that implements IFormatProvider).

Lets get down to the code and see what it need to do a custom formatting:-

Our requirement: We have a string that contains the phone number. The phone number is a standard 10 digit number. However, it may or may not have a country code attached. If the country code is present, the format will be '+' followed by country code, a space and then the 10 digit phone number. So, example of valid values are:

Phone number without country code: 1234567890
Phone number with country code: +91 1234567890

Lets look at the implementation of our class that handles the formatting:

public class PhoneFormatter : IFormatProvider, ICustomFormatter

{

#region IFormatProvider Members


public object GetFormat(Type formatType)

{

// Check if the class implements ICustomFormatter

if (formatType == typeof(ICustomFormatter))

{

return this;

}

else

{

return null;

}

}


#endregion


#region ICustomFormatter Members


public string Format(string format, object arg, IFormatProvider formatProvider)

{

// if the passed in argument is null, return empty string

if (arg == null)

{

return string.Empty;

}


// Get the value of argument in string

string phoneNumber = arg.ToString();


// Check if phone number has country code

if (phoneNumber.StartsWith("+") && phoneNumber.IndexOf(' ') > 1)

{

// If it contains country code, separate it from phone number

string countryCode = phoneNumber.Substring(0, phoneNumber.IndexOf(' ') + 1);

phoneNumber = phoneNumber.Remove(0, countryCode.Length);


// Get the formatted value of phone number and prefix it with the country code

phoneNumber = string.Format("{0}{1}", countryCode, this.GetFormattedPhoneNumber(phoneNumber, format));

}


// Check if the phone number is a valid 10 digit number

if (phoneNumber.Length == 10)

{

// Get the formatted value of phone number

phoneNumber = this.GetFormattedPhoneNumber(phoneNumber, format);

}


return phoneNumber;

}


#endregion


#region Helper method


private string GetFormattedPhoneNumber(string phoneNumber, string format)

{

long number = 0;


//Check if the phone number is a valid numeric value

if (long.TryParse(phoneNumber, out number))

{

// If phone number is numeric, format it as per the passed in value

phoneNumber = number.ToString(format);

}

return phoneNumber;

}


#endregion

}

As you can see, we have a class defined, that implements IFormatProvider and ICustomFormatter. I have commented code at each step so that it is easier to understand whats going on.

Now, lets have a look at its usage:

string phoneNumber = "+91 1234567890";

Console.WriteLine(string.Format(new PhoneFormatter(), "{0:(###) ###-####}", phoneNumber));



This outputs: +91 (123) 456-7890

Similarly, following code produces (123) 456-7890

string phoneNumber = "1234567890";

Console.WriteLine(string.Format(new PhoneFormatter(), "{0:(###) ###-####}", phoneNumber));



Another interesting implementation could be in case of displaying Bank account numbers where only last 4 or 5 digits are displayed and rest are shown as '*' (e.g. ****-****-1234). Its not that this can't be achieved by string operations like substring, but its just that this approach provides you a more structured, managable and reusable way of formatting values.

I hope that this article was easy to follow and enjoyable to you as much as it was to me writing it.

kick it on DotNetKicks.com

Monday, May 21, 2007

T-SQL New features in SQL Server 2005 - Part 4

Have you ever come across a situation where you need to delete data from one table and keep the deleted records in another table? or insert data in one table and also add the new rows at the same time to another? Weird situation...but this is what I had to do while writing a stored procedure that operates on some physical table and also keep track of affected records in another table(a table data type variable, to be precise) so that I can do some more manipulations on that. This approach, of keeping two tables(a physical and a table type variable)in sync with each other, is definitely not a clean approach but comes in handy when you know that you will be working on a very small subset of a table containing thousands of rows. Now remember, I had to do all manipulations in the stored procedure itself and SQL Server 2000 or earlier version, do not provide you any option so that I can affect two tables with one query.By using Output clause, not only you can output the data into another table, but also send it to the client as if it were a select command.

Output Clause

SQL Server 2005 provides a new clause known as "Output". Output operates on similar lines as that of a trigger i.e. it provides you the details about affected records in logical tables named deleted and inserted.

Lets take up a quick example to understand what I have been talking about:

Suppose we have a table called Employees and we wish to insert a new record into it and also get the ID of the inserted record, which we want to return to the client of our stored procedure.

Lets assume, we have a column named EmployeeID in the table. The query we will write to insert the record looks like following:

INSERT INTO Employees([Name],DepartmentID, ManagerID)

VALUES('Alec', 1, 2)

To get the value of identity column, we can use well known @@Identity as follows:

INSERT INTO Employees([Name],DepartmentID, ManagerID)

VALUES('Alec', 1, 2)

SELECT @@Identity

This will output the inserted ID. However, while working on tables having triggers defined on it, it is better to use Scope_Identity() since it operates in the current scope. The modified statement will look as follows:

INSERT INTO Employees([Name],DepartmentID, ManagerID)

VALUES('Alec', 1, 2)

SELECT Scope_Identity()

In both the cases, however, you can see that we are required to write two statements: one for performing the operation(insert the record) and another to get the affected/inserted ID. Using the Output clause, makes it possible to insert the record as well as return the ID in same SQL statement:

INSERT INTO Employees([Name],DepartmentID, ManagerID)

OUTPUT inserted.EmployeeID

VALUES('Alec', 1, 2)



Pretty neat! You can see that all the above three statements produces the same result, but using output saves you from writing an extra statement to fetch the ID of inserted record. It is also worth noting that the Output clause give you the value before any triggers have fired on the table i.e. if there is an "instead of" trigger defined, and the record is not actually inserted, you will still get the ID of the inserted record.
Lets look at an interesting problem now. Suppose that you wish to update data in certain table as per the condition specified in where clause and you also wish to return the affected records to the client for some reason. Output does just that.

Lets look the table first:



Now, we wish to update the departmentID to 2 for all those employees who are reporting to EmployeeID 2(i.e. employeeID 4, 5 and 8) and have department ID set as 3 and also return these records to client of our stored procedure. Once these records are updated, we have absolutely no way to get the details about which records were updated. To do this, we can possibly store all the IDs(based on our condition) into some table variable before firing the update command and then use the previously saved IDs to get which records were updated. Another, more efficient, way can be: Output

UPDATE Employees

SET DepartmentID = 2

OUTPUT inserted.EmployeeID, inserted.[Name], inserted.DepartmentID, inserted.ManagerID

WHERE ManagerID = 2 AND DepartmentID = 3

Following is the output of above mentioned query:


As mentioned earlier, Output provides you access to inserted and deleted logical tables. The data that has been inserted into the table OR the data after update statement has been executed, is available in inserted logical table. Similarly, data deleted using delete statement OR as a result of updation(i.e. data that was there prior to updation) is available in deleted logical table.

Lets take one simple example now(simple because I feel that now you have a fair idea of what output is all about). We wish to update the name of an employee and we want to get both the old and the new value of the employee name:

UPDATE Employees

SET [Name] = 'Alex'

OUTPUT inserted.EmployeeID, deleted.[name] as OldName, inserted.[Name] as NewName

WHERE [Name] = 'Alec'



Following is the output of the above mentioned query:


You can see that we have the name that was updated, obtained from deleted logical table in column named "OldName" and the new value for the name in "NewName" from inserted logical table. Cool!!! Isn't it?

One last thing, which I discussed in the beginning of this article: storing the affected records in a table data type variable. Lets take the example of updating the department IDs of employees:

-- Declare a temporary table to hold the updated records

DECLARE @TempTable Table

(

EmployeeID INT,

[Name] VARCHAR(50),

DepartmentID INT,

ManagerID INT

)

-- Update the table and store the affected records in @tempTable via output

UPDATE Employees

SET DepartmentID = 2

OUTPUT inserted.EmployeeID, inserted.[Name], inserted.DepartmentID, inserted.ManagerID INTO @TempTable

WHERE ManagerID = 2 AND DepartmentID = 3


-- Select all the records to see which were updated

SELECT * FROM @TempTable

Introduction of Output gives a lot of opportunities to developers like me to write a better and manageable code. I think you must have already thought of a couple of places where you can use this... So what are you waiting for..give it a shot!

kick it on DotNetKicks.com