Monday, 30 January 2012

Check leap year in MSSQL

When just thinking about datetime fields in SQL level I just wanted to know how to check a given year is a leap year or not. Though it is a very common scenario, I couldn't find any built-in function in MSSQL to check leap year. 

I found the below code;

DECLARE @YEAR INT SET @YEAR = 2012
PRINT CASE WHEN ((@YEAR % 4 = 0 AND @YEAR % 100 <> 0) OR (@YEAR % 400 = 0)) THEN
                  'LEAP YEAR'
            ELSE
                  'NORMAL YEAR'
        END

So with this we can create a scalar-valued function and call whenever it need in stored procedures.

PS: Thanks for Thilina :)

Query database triggers for a text


Today I wanted to find the database triggers that contains a specific text in it. By referring to my previous post about query stored procedures for a specific text and doing some web search I've been able to build a query as below;
 
SELECT A.[name], M.[definition]
FROM SYS.SYSOBJECTS A
INNER JOIN SYS.SQL_MODULES M ON A.[id] = M.[object_id]
WHERE A.[type] = 'TR'
AND M.[definition] LIKE '%delete%'

PS: Thanks for Malmi :)
 

Sunday, 29 January 2012

C# Static Field Initialisation

Static Fields
Fields can be declared as static in the same manner as methods and properties. The addition of the static keyword indicates that a field is accessed using its class name, rather than via an instance of the class. When you create a class that contains static fields, it is important that you initialize them in the correct order. Consider the following code:

class AttemptController
{
    static int Threshold = MaxAttempts - WarningAttempts;
    static int MaxAttempts = 5;
    static int WarningAttempts = 2;
}

At a glance the above code looks OK. The MaxAttempts value is set to 5, the WarningAttempts field to 2 and the Threshold value to the difference of the other two fields. You might expect the Threshold value to be 3. However, if you run the following code to output the values, you will see results that you may not expect:

Console.WriteLine("Maximum:   {0}", AttemptController.MaxAttempts);
Console.WriteLine("Warning:   {0}", AttemptController.WarningAttempts);
Console.WriteLine("Threshold: {0}", AttemptController.Threshold);

/* OUTPUT

Maximum:   5
Warning:   2
Threshold: 0

*/
  
As you can see in the comment, it appears that the Threshold field's value is being incorrectly calculated.

C# Specification

The cause of the unexpected value can be found in the C# Language Specification

The Threshold field cannot be evaluated until MaxAttempts and WarningAttempts being correctly initialise. If the initialisation is fail to evaluate, then the default value of its type will return. For integer values, this is zero.

The C# Language Specification also tells us that when static fields are initialised by applying a value in their declaration, as we have done above, they are set in the order in which they appear in the code. This means that when the Threshold value is calculated, we are using two uninitialised values, each of which yields a result of zero. The Threshold field's calculation is therefore the result of zero minus zero, which is zero.

A simple fix is to reorder the declarations to ensure that the MaxAttempts and WarningAttempts values are present before they are needed, as shown below:

class AttemptController
{
    static int MaxAttempts = 5;
    static int WarningAttempts = 2;
    static int Threshold = MaxAttempts - WarningAttempts;
}

Running the program now gives the correct results:

Maximum:   5
Warning:   2
Threshold: 3

But this approach does not generate the most readable and maintainable code. It is quite possible that another developer may change the order of the fields at a later time, may be to sort them alphabetically, unknowingly introducing a bug. A better fix is to remove the initialisation from the field declarations and instead set the values within a static constructor, as shown below:

class AttemptController
{
    static int MaxAttempts;
    static int WarningAttempts;
    static int Threshold;

    static AttemptController()
    {
        MaxAttempts = 5;
        WarningAttempts = 2;
        Threshold = MaxAttempts - WarningAttempts;
    }
}

Tuesday, 8 November 2011

Sorting an alphanumeric column in a DataTable

I'm getting a DataTable via a database table view where it contains a varchar column. For example purpose we'll call the column as "UnitNumber" of a home address. UnitNumber most of the time contains only a number, but there can be letters as well.

My requirement is to sort the DataTable by UnitNumber column, but normal sorting will give a result like below;

1
10
11
12
14
17B
2
21
26A
3
32

This order is incorrect. In stored procedure level we can use REPLICATE() function to solve this problem, but I'm in CSharp code now.


The correct sorted order should be as below;

1
2
3
10
11
12
14
21
32
17B
26A
 
 The below function will accept a data table to be sorted and the sorting column name as string.(In this example I consider the column contains only string values and integer values only.)

 
/// <summary>
/// Sort a data table by an alphanumeric column
/// </summary>
/// <param name="dtUnsorted">Data table to be sorted</param>
/// <param name="columnName">The alphanumeric column name</param>
/// <returns>Sorted data table</returns>
public static DataTable SortByAlphanumericColumn(DataTable dtUnsorted, string columnName)
{
string tempColumnName = columnName + "INT";

// Add a temp column to hold the numberic columnName values
dtUnsorted.Columns.Add(new DataColumn(tempColumnName, typeof(int)));

// Loop the data table and copy the numberic unit number values into tempColumnName column
foreach (DataRow row in dtUnsorted.Rows)
{
int i;
if (int.TryParse(row[columnName].ToString(), out i))
{
row[tempColumnName] = i;
}
}

// Sort by tempColumnName and then columnName column (Numberic records come first and string records will come next)
dtUnsorted.DefaultView.Sort = string.Format("{0} ASC, {1} ASC",tempColumnName, columnName);

DataTable dtSorted = dtUnsorted.DefaultView.ToTable(dtUnsorted.TableName);
dtUnsorted.Dispose();

// Remove the temp column
dtSorted.Columns.Remove(tempColumnName);
dtSorted.AcceptChanges();
return dtSorted;
}
 

Saturday, 13 August 2011

Restore the backup in Xperia x10 after Gingerbread (2.3.3) upgrade

Today I've been able to upgrade my Sony Ericsson Xperia x10 phone from Android OS 2.1 to 2.3.3 as usual after taking a backup of all my contacts, apps, messages, etc. But after the upgrade there is no Backup & Restore application in the system that was in the 2.1 OS. As they have mentioned I tried MyBackup application to do the restoration, but it didn't able to read the sbf file. So I have no way of taking back my contacts from that sbf binary file.

While searching on the net I found few forums having the old Backup & Restoring application shared as a solution for my situation (while blaming on SE not to include the application).

If you also in the same situation, I have shared those sites for further information.


http://androidforums.com/sony-ericsson-xperia-x10/385596-gingerbread-backup-whoopsy.html

http://forum.xda-developers.com/showthread.php?p=16216185