Sunday, October 23, 2011

.NET: Alternatives to .NET Reflector

.NET Reflector is dead, long live ILSpy. Well, .NET Reflector is not really dead, as you might already know, but you now have to pay if you want to use the disassembler. Below we have listed the most common alternatives to the previously popular tool:
  1. ILSpy
  2. dotPeek
  3. JustDecompile
  4. Common Compiler Infrastructure
  5. Mono Cecil
  6. Kaliro
  7. Dotnet IL Editor (DILE)
Personally I prefer to use the ILSpy because it is open-source, and works in the similar way as the old .NET reflector.

JavaScript: Get URL Query Argument

Often I have the need to parse the URL query to retrieve the value of a specific named argument. There is no direct way of doing this is JavaScript, so this requires a custom function.


function findQueryArgument (strQuery, strArgumentName) {

    // Valid query?
    if (strQuery) {

        // Split the parameteres
        var arrParameters = strQuery.split("&");

        // Walk through the parameters
        for (var i = 0; i < arrParameters.length; i++) {
            // Get the key/value pair
            var arrPair = arrParameters[i].split("=");

            // Is this the event argument?
            if (arrPair[0] == strArgumentName) {
                // Has value?
                if (arrPair.length > 1) {
                    // Get the value
                    return arrPair[1];
                }

                // Not set
                break;
            }
        }
    }

    // Not found
    return '';
}

The strQuery argument is the full URL query i.e. a=val1&b=val2 etc. The full URL should not be included.
strArgumentName is the name of the argument you're looking for, i.e. a or b.

Please notice that the returned value is URL encoded. To extract the actual string value you need to use the built in JavaScript method unescape.

Here is an example:

// Query string
var strQuery = 'a=val1&b=val2';

// Get the value of the 'b' argument
var strEscaped = findQueryArgument(findQueryArgument, strQuery, 'b');

// Get the unescaped value
var strUnescaped = unescape(strEscaped);

// Display the value
alert(strUnescaped);
Monday, October 10, 2011

Excel VBA: Sum Values IF

Below we have made a function which sums the values of one column/range given that the value of another column is equal to a fixed value. This function ignores the N/A values, which is not the case for the built-in function called SumIf in Excel. If the built-in function finds a N/A value the function returns N/A.

' SUM Value If
' Ignores N/A
Public Function SumValueIf(ByVal objRange As Range, ByVal objCriteria As Range, ByVal objSumRange As Range) As Currency
    Dim intRow As Integer
    Dim objRangeValue As Object
    Dim objCriteriaValue As Object
    Dim objValue As Object
    Dim dblValue As Currency
    Dim dblSum As Currency

    ' Get the criteria value
    objCriteriaValue = objCriteria(1, 1)

    ' Walk through the rows
    For intRow = 1 To objRange.Rows.Count Step 1
        ' Get the current value
        objRangeValue = objRange(intRow, 1)

        ' Compare values
        If (objRangeValue = objCriteriaValue) Then
            ' Get the value
            objValue = objSumRange(intRow, 1)

            ' Is valid number?
            ' Ignore any strings, #N/A, #Error, etc.
            If (IsNumeric(objValue)) Then
                ' Get the value
                dblValue = CCur(objValue)

                ' Sum
                dblSum = dblSum + dblValue
            End If
        End If
    Next

    ' Get the value
    SumValueIf = dblSum
End Function

The function can be used in the following way:

= SumValueIf(A1:A10; "abc"; B1:B10)

where A1:A10 is the compare column. Only sum the value in column B1:B10 if the value in column A is equal to "abc".

Excel VBA: Convert Variant Array to String Array

To simplify the conversion of a variant array to a string array we have made a set of utility functions.

' Array Variant to String
Public Function VariantArrayToStringArray(ByVal arrVariants As Variant) As String()
    Dim arrStrings() As String
   
    ' Get the string array
    Call ParamArrayToStringArray(arrVariants, arrStrings)
   
    ' Get the string array
    VariantArrayToStringArray = arrStrings
End Function

' Array Variant to String
Public Sub ParamArrayToStringArray(ByVal arrVariants As Variant, ByRef arrStrings() As String)
    Dim intLength As Integer
   
    ' Handle the array
    Call ParamArrayToStringArrayInternal(arrVariants, arrStrings, intLength)
End Sub


' Array Variant to String
Private Sub ParamArrayToStringArrayInternal(ByVal arrVariants As Variant, ByRef arrStrings() As String, ByRef intLength As Integer)
    ' Parameter is array?
    If (IsArray(arrVariants)) Then
        Dim i As Integer
        Dim objValue As Variant
   
        ' Walk through the specified partner objects
        For i = LBound(arrVariants) To UBound(arrVariants) Step 1
            ' Get the value
            objValue = arrVariants(i)
       
            ' Array to string
            Call ParamArrayToStringArrayInternal(objValue, arrStrings, intLength)
        Next
    Else
        ' Next item
        intLength = intLength + 1
   
        ' Expand array
        ReDim Preserve arrStrings(1 To intLength)

        ' Set the value
        arrStrings(intLength) = CStr(arrVariants)
    End If
End Sub

Excel VBA: Expand and Collapse Group

In Excel it is possible to group rows and columns. To expand or collapse a a group you can use the VBA method called ShowLevels. In the example below we are expanding a row group. We have also disabled automatic recalculation to improve the performance.

' Show Level
Public Sub ShowGroupLevel(ByVal intRowLevel As Integer)
    Dim intCalculation As XlCalculation

    ' Get the old calculation state
    intCalculation = Application.Calculation

    ' Disable re-calculation
    Application.Calculation = xlCalculationManual

    ' Show the specified level (expanded)
    On Error Resume Next
    Call ActiveWorkSheet.Outline.ShowLevels(RowLevels:=intRowLevel)
    On Error GoTo 0

    ' Restore automatic calculation
    Application.Calculation = intCalculation
End Sub

Please notice that expand and collapse work in the same way. Collapsing a group is the same as only displaying the first level. Expand is the same as showing level 2 or more.

Excel VBA: Get all selected rows

If the user does does a selection of rows in Excel, the selection can contain multiple areas. An area is a subselection of rows created when the user presses the Shift button while doing a multiselect. In VBA we need to handle each area independently to be able to retrieve all the selected rows.

' Example
Public Sub Test()
    Dim objSelection As Range
    Dim objSelectionArea As Range
    Dim objCell As Range
    Dim intRow As Integer


   
' Get the current selection
    Set objSelection = Application.Selection

    ' Walk through the areas
    For Each objSelectionArea In objSelection.Areas

        ' Walk through the rows
        For intRow = 1 To objSelectionArea.Rows.Count Step 1
            ' Get the row reference
            Set objCell = objSelectionArea.Rows(intRow)

            ' Get the actual row index (in the worksheet).
            ' The other row index is relative to the collection.
            intActualRow = objCell.Row

            ' Get any cell value by using the actual row index
            ' Example:
            strName = objNameRange(intActualRow, 1).value
        Next
    Next
End Sub

In the example we have not defined the variables strName and objNameRange. For your reference the variables are defined as follows:

    Dim strName As String
    Dim objNameRange As Range

objNameRange can be any range in your selected worksheet.

Excel VBA: Send E-mail from Excel

There are several ways to send e-mail from Excel using Microsoft Outlook.

It is possible to use the built-in function ActiveWorkbook.SendMail. However, it only allows simple e-mails to be created, and there is no way to add attachments.

In this example we are going to use the COM library of Microsoft Outlook to get more control. Attachments are not covered in this example.

We start by checking if an instance of Outlook is already running. To be independent of the installed version of Outlook we use the GetObject to try to get an existing instance. Another option would have been to reference the Outlook library directly, but then we are required to update the reference every time a new version of Microsoft Office is installed. If no instance is running, we start Outlook by calling CreateObject. Please remember to close down Outlook when you are finished.  Otherwise you will get multiple instance of the same application without the user knowing it.

We get a reference to the current users inbox by calling getDefaultFolder(6).The value 6 is a constant referring to the Inbox. If you used a reference to the COM library, you would have used the enum value olFolderInbox.

' Send E-mail
' strTo - Recipients. List of e-mails (separated by ';')
Public Sub SendEmail(ByVal strTo As String)
    Dim objOutlookApp As Object
    Dim objEmail As Object
    Dim objMapi As Object
    Dim objInboxFolder As Object

    ' Valid e-mails?
    If (strTo = "") Then
        ' Error
        MsgBox "No e-mails have been set!", vbExclamation, "No e-mails"
       
        ' Finished
        Exit Sub
    End If
   
    ' Attach to outlook
    On Error Resume Next
    Set objOutlookApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
   
    ' Is Outlook running?
    If (objOutlookApp Is Nothing) Then
        ' Create new instance of outlook
        Set objOutlookApp = CreateObject("Outlook.Application")
       
        ' Get the MAPI namespace (e-mails)
        Set objMapi = objOutlookApp.GetNamespace("MAPI")
       
        ' Get the inbox folder
        Set objInboxFolder = objMapi.getDefaultFolder(6)
       
        ' Display the inbox folder (make outlook visible)
        Call objInboxFolder.Display
    End If
   
    ' Create the new e-mail
    Set objEmail = objOutlookApp.CreateItem(0)
   
    ' Set the properties of the new email
    With objEmail
        ' Set the recipients
        .To = strTo
       
        ' Show the message
        Call .Display
       
        ' Resolve all recipients (Same as pressing the "Check Names" button)
        Call .Recipients.ResolveAll
    End With

    ' Free memory
    Set objEmail = Nothing
    Set objOutlookApp = Nothing
End Sub
Saturday, October 8, 2011

Blogger: Free Templates for your Blog

Want to have a proffessional looking blog on blogger.com (blogspot.com)? The available templates for blogs on blogger.com are very limited. You can download free templates from the following sites:

Facebook Dev: Free webhosting for your App

If you're a student and want to learn Facebook App development, or just need a free webhost for your app, you can apply for free webhosting from numerous free webhosting providers.

If you are developing your Facebook App in ASP.NET, check out the list of free webhosts that support ASP.NET and Microsoft SQL Server:

Since there are few licenses related to setting up a webserver running Linux, it is much easier to find a free webhost if your Facebook application does not require a Windows server.

  1. 0000cost (1.5 GB storage, 15 GB bandwidth)
  2. Free domain site (1 GB storage, unlimited bandwidth)
  3. Bammz (unlimited storage and bandwidth)
  4. HelioHost (500 MB, unlimted bandwidth)
  5. Somee (150 MB storage, 5 GB transfer)
  6. No Fee Host (100 MB storage)
  7. 7 Host (50 MB storage)
  8. Brinkster (30 MB storage, 2 TB bandwidth) 
  9. 10k Host (unknown storage and bandwidth)
  10. Millenium Systems (only for webdesigners)
Please notice that the webhosts running Windows servers and ASP.NET normally also provide support for PHP and MySQL databases.


You can also get free SSL certificates for your webserver (which is required from October 1st for all Facebook Apps):

Webhosting: Free webhosting for ASP.NET

Believe it or not, it is possible to host your ASP.NET application for free. Storage, bandwidth, etc. is of course limited, but it is a good offer for students and test applications.

Below we have listed different hosts that offer free ASP.NET hosting:
  1. Free ASP Web Hosting (2 GB storage, 20 GB bandwidth, includes MS SQL)
  2. Web Host for ASP.net (150 MB storage)
  3. Somee (150 MB storage, 5 GB transfer)
  4. AspHost4Free (100 MB storage, unlimited bandwidth)
  5. Free domain site (1 GB storage, unlimited bandwidth)
  6. 7 Host (50 MB storage)
  7. ASP Spider
  8. Brinkster (30 MB storage, 2 TB bandwidth) 
  9. HelioHost (using Mono Project on Linux servers)
You can also get free SSL certificates for your webserver:

Facebook Dev: Choosing a SDK for ASP.NET

When first deciding to create a Facebook App in ASP.NET I was struggling to find a up to date Facebook SDK for .NET. Some of the SDKs contains very low level functions, while others don't support the latest features. When selecting SDK you also have to make sure that the "new" signed_request parameter is used by the framework. From October 1st 2011 all Facebook Apps and Facebook Pages must use this parameter to get information about the current user, page id, access token etc.

At the time of writing, it seems like it is only the offical Facebook SDK at CodePlex which is satisfying all the requirements when it comes to Facebook Development in .NET.
Unfortunately, the SDK contains a lot of bugs, and is very hard to debug if you happens to find one.

Personally I was forced to write most of my Facebook code from scratch without using the Facebook SDK due to all the bugs.

You can find the documentation of the Facebook API at the Developer area.

CSS: Support for transparent PNG in Internet Explorer

Versions prior to Internet Explorer 7 has limited support for transparent PNGs. To enable transparent PNGs in IE 6 you should use the following statement in your CSS:

<!--[if lt IE 7]>
<style type='text/css'>

 .some_class {
        background:none;
        filter: progid:DXImageTransform.Microsoft.AlphaImageLoader(src='img/x.png', sizingMethod='scale');
 }

</style>
<![endif]-->


Opacity Bug in IE
If you specify the opacity (transparency) for a already transparent PNG in IE 7 and 8, the transparent area will turn totally black. If you use a transparent PNG together with f. example jQuery plug-ins, make sure that the opacity/filter is set to 255 (no transparency).

.NET: SVG Support

After hours of searching for code for rendering SVG pictures I finally stumbled upon the SVG rendering engine at Codeplex.

The good thing about SVG is that it is supported in most modern browsers. However, SVG is not supported in .NET so you need to find an external library. It seems like Microsoft has focused all their energy on XAML, even though the two formats are very similar.

Fortunately for us the SVG Rendering Engine works more or less out of the box. I had to do some rewriting of the code to make it work with all SVG images. The library is still under development and needs a lot of new features to make it work 100% in accordance with the specifications.

ASP.NET: Free scheduling of your tasks (Cron jobs)

If you choose to run your website in a shared hosting environment on windows servers, most webhosts don't allow you to schedule your own tasks, like backup, changing status etc. If you run plug-and-play solutions like WordPress or Drupal you are more or less dependent on scheduled tasks.

There are numerous providers of webbased scheduling of tasks. The tasks can normally be sceduled by specifying the URL of a given webpage on your server and when/how often the should run.

Free scheduling services include:
  1. Host Tracker (1 free job)
  2. My Web Cron (free service)
  3. SetCronJob (limited cron jobs are free)
  4. Web Scheduler (one cron job for free)
For your reference I have also compiled a list of commersial cron job providers (in case the free ones don't fullfill your needs):
  1. SiteUptime
  2. WebCron

Cons
The disadvantage when using free scheduling services, is that the number of tasks to schedule is limited, short timeout and the scheduling needs to be re-activated after a few months. If your site is heavily dependent on scheduled tasks, you should consider to use a paid service, or change to VPS or Dedicated Server hosting.


Scheduling in Windows plans
Some webhosts have made their own control panel or use commersial panels providing scheduled tasks as an option to their shared window plans. After checking multiple webhosts I realized that it is rather rear that such option is available. The following known webhosts provide scheduled tasks as an option in their control panel:
  1. HostGator
  2. Mochahost

VPS and Dedicated Servers
If you run your website on a VPS or dedicated server, the need for a web based task scheduler does not apply. You can then either use the built-in scheduler in windows or set up your own windows service. This is possible since you will have full root-access.

MS SQL: Custom Date and Time functions

Transact-SQL really lack a the neccessary support for creating and modifying dates and times. Now and then I have the need for creating custom dates based on year, month and day. This is not an easy task. To simplify the manipulation of dates and times a small toolkit of userdefined functions has been developed:


Date and Time functions

Calendar functions

MS SQL: Create DateTime

To create a full DateTime structure in Transact-SQL we need to use two of our earlier defined userdefined functions.
 
 First we generate a raw base date, and then we add the hours, minutes and seconds.
 

-- Returns a dateTime value for the date and time specified.
create function [dbo].[sudf_Common_DateTime]
(
      @intYear    int,
      @intMonth   int,
      @intDay     int,
      @intHour    int,
      @intMinute  int,
      @intSecond  int
)
returns datetime
as
begin
      -- Create the datetime structure
      return dbo.sudf_Common_Time(@intHour, @intMinute,@intSecond, dbo.sudf_Common_Date(@intYear, @intMonth, @intDay))
end

MS SQL: Create Time

To create a new DateTime value for a given date and a given time, we can use the userdefined function below. We start by retrieving the raw date (no time portion) of the base date, and then add the hours, minutes and seconds.

-- Returns a datetime value for the specified time at the "base" date (1/1/1900)
CREATE function [dbo].[sudf_Common_Time]
(
      @intHour    int,
      @intMinute  int,
      @intSecond  int,
      @dtBaseDate datetime
)
returns datetime
as
begin
      -- Build the time
    return  dbo.sudf_Common_DateOnly(@dtBaseDate) +
                  dateadd(ss,(@intHour*3600) + (@intMinute*60) + @intSecond,0)
end