Monday 30 January 2012

How to Get Enum Elements' Values and Descriptions

Below is an example of how to populate a collection from an Enumeration elements' values and Description attributes (names if Description attributes are not exist).

Say that we have the following Enum:
public enum EnumStatus
{
    [Description("Not Submitted")]
    NotSubmitted = 0,

    Requested = 1,

    [Description("Pending Approval")]
    PendingApproval = 2,

    Approved = 3,

    Rejected = 4
}

Then the code to get each element's value and Description attribute (or name) is:
var type = typeof(EnumStatus);
foreach (var field in type.GetFields().Where(f => f.FieldType == type))
{
    var attribute = Attribute.GetCustomAttribute(field, typeof(System.ComponentModel.DescriptionAttribute)) 
        as System.ComponentModel.DescriptionAttribute;
    var value = field.GetValue(Activator.CreateInstance(type));
    myCollections.Add(
        new
        {
            Id = (int)value,
            Name = attribute != null ? attribute.Description : value.ToString() // or Enum.GetName(typeof(EnumStatus), value)
        }
    );
}

Friday 20 January 2012

Windows Installer Issue when Installing over Previous Version Application

Recently I worked on Windows Setup Project to deploy a new version of an existing application. The application and installer was developed using Visual Studio 2008. I'm using Visual Studio 2010 to work on the project. All of the projects including the Windows Setup are still using the same .NET framework and prerequisites.

When I try to install the application (it has a setup.exe, an msi and some other files that have been packaged together using IExpress) over the older version on a machine, a few issues occurred even tough the installer said that the installation was successful.

The issues were the application's shortcuts were disappeared and all unmodified files in the installation directory were gone, only the modified ones were still there. What I mean by unmodified files are the files that were exist in both version and have not been updated/changed in the new version.

According to these articles; http://connect.microsoft.com/VisualStudio/feedback/details/559575/problem-with-installing-and-removing-previous-versions-after-upgrading-my-setup-project-to-vs2010 and http://social.msdn.microsoft.com/Forums/en-US/winformssetup/thread/b87f1aea-d15a-484b-8cdc-0d212784f941/, the problem occurs because all of the files' component GUIDs are changed when the setup project is migrated from Visual Studio 2008 to Visual Studio 2010. A workaround for this is to re-sequence 'RemoveExistingProducts' right after 'InstallInitialize' in the installation processes sequence table of the application's msi file.

Here is the detail of the process:
1. Use 'Orca' to open the new version applicaton's msi file. 'Orca' is a tool for creating and editing Windows Installer packages and merge modules. 'Orca' can be downloaded from http://msdn.microsoft.com/en-us/library/windows/desktop/aa370557%28v=vs.85%29.aspx.
2. Right click the msi file then select 'Edit with Orca'.
3. Select 'InstallExecuteSequence' table on the left pane window.
4. Then on the right panel, find 'RemoveExistingProduct' in the 'Action' column, see the blue colour highlighted row.
5. Double click the 'Sequence' value (the yellow colour highlighted cell) then change the value to 1525.
6. Save the changes.

Then try the installation again.

Friday 13 January 2012

Template of a Stored Procedure with Savepoint

Savepoint is used for selective roll back. Using savepoint, a transaction can roll back to a selected location that has been marked. When it is rolled back, in the end the transaction must be completed by using 'commit transaction' or rolled back altogether.

Savepoint name should be unique even though duplicate is allowed. If a roll back is occurred where there is a duplicate, the transaction will be rolled back to the latest savepoint.

CREATE PROCEDURE [Procedure_Name]
AS
BEGIN

-- generate a unique savepoint name by appending procedure name (OBJECT_NAME(@@procid)) and nested level (@@nestlevel)
-- we could also use only @@nestlevel as it will always be unique in an active connection
-- savepoint name's maximum length is limited to 32 characters only
DECLARE @savepoint NVARCHAR(32) = CAST (OBJECT_NAME(@@procid) AS NVARCHAR(29)) +
           CAST (@@nestlevel AS NVARCHAR(3))

-- this is to check whether nested transactions exist when entering this procedure,
--  the value will be used later for checking condition
DECLARE @entryTrancount INT = @@trancount

BEGIN TRY
 BEGIN TRANSACTION
 SAVE TRANSACTION @savepoint
 
 --do something here
 
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 -- transaction is uncommittable (XACT_STATE() = -1) and no nested transactions exist (@entryTrancount = 0)
 IF XACT_STATE() = -1 AND @entryTrancount = 0
  ROLLBACK TRANSACTION
 -- otherwise if transaction is committable
 ELSE IF XACT_STATE() = 1    
  BEGIN
   ROLLBACK TRANSACTION @savepoint
   COMMIT TRANSACTION
  END
   
 DECLARE @ERROR_MESSAGE NVARCHAR(4000)
 SET @ERROR_MESSAGE = 'Error occured in procedure ''' + OBJECT_NAME(@@procid)
       + ''', Original Message: ''' + ERROR_MESSAGE() + ''''
 RAISERROR (@ERROR_MESSAGE, 16, 1)
 RETURN -100
END CATCH
END

According to MSDN, XACT_STATE function returns three values:
1 - The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
0 - There is no active user transaction for the current request.
-1 - The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.


References and further reading:
http://msdn.microsoft.com/en-us/library/ms188378%28v=SQL.105%29.aspx
Pro SQL Server 2008 Relational Database Design and Implementation - Louis Davidson
http://msdn.microsoft.com/en-us/library/ms189797.aspx
http://dosql.com/cms/index.php?option=com_content&view=article&id=101:trancount-and-xactstate&catid=40:microsoft-sql-server&Itemid=41