Sitefinity ajax requests

On a project at work we used Sitefinity from Telerik as a CMS System. I can say that it is a very good and especially end-user friendly CMS System because it let's them drag & drop so called widgets on the page and this is straight forward for almost everyone.

We are using the mixed mode (aspx and mvc) but I developed all my widgets in MVC.
For one widget I had the request to show statistical data in a chart and the request to the datasource (an external web server) takes about 2-3 seconds. I didn't want to let the user feel that the whole page is not responding just because of this data request and so I decided to to an ajax request in the background.
With jQuery this is an easy task, but I was not able to return the json data to the client. I returned a JsonResult on the Controller but Sitefinity always injected somehow the master layout of the page and therefore it was invalid json when it was received by the client.

Fortunately I found a solution to this problem:

public ActionResult GetAjaxData()
{
    Response.ClearHeaders();
    Response.AddHeader("Content-type", "text/json");
    Response.AddHeader("Content-type", "application/json");
    Response.ClearContent();

    //here you obtain your list of objects

    List myResult = new List(); 

    Response.Write(myResult.ToJson());
    Response.End();
 
    return new EmptyResult();

}

WCF RIA Services with JSON endpoint

Today I had the task to add a JSON endpoint for a WCF RIA service for which there exists a Silverlight client. I wanted to state some problems that I encountered such that maybe others can get it to work faster:

1. Install the following two updates: 
1. WCF RIA Services V1.0 SP2 
2. WCF RIA Services Toolkit (September 2011)
2. Add a reference to "Microsoft.ServiceModel.DomainServices.Hosting" to your web application project
3. Add the following to your web.config into the tag

       <domainServices>
         <endpoints>
           <add name="OData" type="System.ServiceModel.DomainServices.Hosting.ODataEndpointFactory, System.ServiceModel.DomainServices.Hosting.OData, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
           <add name="Soap" type="Microsoft.ServiceModel.DomainServices.Hosting.SoapXmlEndpointFactory, Microsoft.ServiceModel.DomainServices.Hosting, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
           <add name="Json" type="Microsoft.ServiceModel.DomainServices.Hosting.JsonEndpointFactory, Microsoft.ServiceModel.DomainServices.Hosting, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
         endpoints>
       domainServices>
   
   

4. Here the first pitfall arised; the application did not compile anymore complaining:
   The "CreateRiaClientFilesTask" task failed unexpectedly.
   Unrecognized configuration section system.serviceModel/domainServices.
   
   To resolve this add the following to the tag:
   

   <sectionGroup name="system.serviceModel">
         <section name="domainServices" type="System.ServiceModel.DomainServices.Hosting.DomainServicesSection, System.ServiceModel.DomainServices.Hosting, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" allowDefinition="MachineToApplication" requirePermission="false" />
       sectionGroup>
   

5. According to some howtos the URL is composed as follows:
   http://localhost:[port]/[Name of Silverlight Project]-Web-[Name of Webservice]/Json/[Method name]
   e.g.: http://localhost:2002/MySilverlightApp-Web-DataAccessService/Json/GetArticles
6. The above URL always returned 404 Not found
7. My next pitfall was that my service class file was not in the web project but in a subfolder ("Services") of it
8. To solve that I had to modify the URL as follows:
   http://localhost:[port]/[Name of Silverlight Project]-Web-[Folder name]-[Name of Webservice]/Json/[Method name]
   e.g.: http://localhost:2002/MySilverlightApp-Web-Services-DataAccessService/Json/GetArticles
9. After that I finally got my JSON from my webservice :-)

Entity Framework no columns when mapping stored procedure

Recently I had the problem that I wanted to map a stored procedure with the Entity Framework. As soon as I clicked on "Get column information" no columns showed up despite I knew that the stored procedure returns a table with some columns.

After some research I found out that in some circumstances it is necessary to add the following two statements at the beginning of the stored procedure:

SET NOCOUNT OFF;
SET FMTONLY OFF;

After that everything worked as expected.

New version of Team Viewer

Since my first blog post about Team Viewer has become a sort of "support forum", which has reached 125 comments now, I decided to write about the new version.

As I already wrote on my first post Team Viewer is really great if you have to help kin and kith with all sorts of computer problems. It is much easier and takes less time to solve a problem if you can see it as if someone (who does not know much about computers) describes you what he/she sees. You could also use remote desktop through the MSN Messenger but I tried it out and I had problems to get it to work. Firewalls are the biggest problem for not experienced users.

Team Viewer is very easy to use and has no problems with firewalls; that is what I like so much about it; and of course that it is FREE for personal use ;-). Also a computer noob can run it and tell you the ID and the password. Team Viewer is also very useful to maintain remote machines such as your home server.

The application has reached version 6 now and they say that they have improved many things such as performance, usability and much more. If you are interested in all the details have a look at http://www.teamviewer.com/en/download/tv6.aspx. You can also use it to share files with someone over the internet but since I’m using DropBox to store and share files I don’t use this feature very much.

Since I am a proud owner of an Android Smartphone (Samsung Galaxy S) I was curious to try out also the Team Viewer application for Android. It is really amazing how good it works!! I tried it over Wifi, where the speed is quite good, and over my mobile phone network where it goes much slower but is still usable. This allows me to help kin and kith with their computer problems also on the go :-) There exists also an iPhone and iPad app but I couldn’t try it out because I don’t have one.

Link to Team Viewer

Smelly code

Original, smelly code

public bool isLocked(string s)
{
    if (!File.Exists(s)) return false;
    try { FileStream f = File.Open(s, FileMode.Open, FileAccess.ReadWrite); f.Close(); }
    catch { return true; }
    return false;
}

Objections and Comments
The code abve has in my eyes several problems:

1. Method name starts with lower case
2. The name of the input parameter is meaningless
3. Logical code blocks are not separated by a new line
4. You need some time to understand what this 4 lines of code do
5. Multiple instructions on the same line
6. The "return false;" statement should be inside the try block because it is logically associated to it
7. Missing Method documentation

Proposed Refactoring

/// <summary>
/// Checks if a file is in use or not.
/// </summary>
/// <param name="filePath">The path to the file that should be checked.</param>
/// <returns>True if the file is locked; false otherwise</returns>
public bool IsLocked(string filePath)
{
    if (!File.Exists(filePath))
    {
        return false;
    }

    try
    {
        FileStream fileStream = File.Open(filePath, FileMode.Open, FileAccess.ReadWrite);
        fileStream.Close();
        return false;
    }
    catch
    {
        return true;
    }
}

Strange undestroyable folders

A friend of mine had a very very strange problem. Suddenly on his external hard disk there appeared a bunch of very strangely named folders like "%/sl94..." and other symbols which windows was not able to display. They occupied 300GB and clearly my friend wanted to delete them; unfortunately windows was not able to delete them. It gave always an error like "Path not found" or "Invalid path symbols".
I tried then to delete them with different data shredders but none of them was able to successfully delete them.
I have then run also different virus and spy-ware scanners over them but none of them found something.
I was stuck and at the end with my knowledge.

Then I found on the Internet the good old command line instruction:

del foldername

With that I was finally able to delete them all.

Direct SQL vs. View vs. Stored Procedure

I was wondering for a very very long time what all these "battels" around direct sql, views and stored procedures are about. I read several articles on the Internet where people were claiming that nowadays there is almost no difference anymore between these three database "constructs". I saw many discussions and blogs where people tested the performance of these three with different results and that made me even more confused.

So as I am curious and the fact that I need to know this as a software developer I decided to try this out by myself. At work I had the chance to test this against a very large amount of real data (I needed to optimize this query anyway);
Sometimes the sample databases which are used for theses kinds of tests are either very small or very simple or even both which makes not really sense in my eyes.

So the query consisted of joins of multiple tables with a case in the select statement. I run the query 300 times with every "construct" and then I took the average value for all three of them in order to reduce network and SQL Server workload "noise".

The result of my test (in Ticks):

Direct SQL	23077
View	19363
Stored procedure	3438

So what I found out for myself is that it really does matter what kind of "construct" you use to query your data; especially on a large amount of data.
On large data you should also take into consideration that a big performance impact may also have where you set your indexes and which statistical data you let create by SQL Server.

If you made other experiences on this, please let me know and leave a comment...

Executing SQLQueries on ObjectContext

Recently I worked with the entity framework (3.5) and at some point I had the need to execute a custom SQL command. Fortunately does the object context provide the underlying DBConnection and with that you can simply execute SQLCommands.

I read on the Internet that the new Version (4.0) has already integrated something similar.

This is an extension Method that I wrote which makes it easy to execute these queries:

public static void ExecuteSQL(this ObjectContext context, string sql)
{
	var connection = context.Connection;
	var command = connection.CreateCommand();
	command.CommandText = sql; 

	try
	{
	    connection.Open();
	    command.ExecuteNonQuery();
	}
	finally
	{
	    connection.Close();
	}
}

Handy Windows shortcut

Sometimes it happens to me that I have to copy the path of a file to the clipboard and paste the it somewhere (console, visual studio, etc.); but it is not as easy to get the path as it is with folders where you can simply get it from windows explorer.

Windows has a hidden shortcut to get the path; when you press "shift" and click with the right mouse button on a file then in the context menu you get an additional entry which is called

"Als Pfad kopieren" or "Copy as path". Afterwards you can simply paste wherever you need it.

I find this feature extremely useful.

Here you can find also some screenshots.

USB 2.0 vs. Firewire 400 vs. eSATA

To backup my data I bought the external harddisk Western Digital My Book Home Edition with a capacity of 1 Terabyte which provides the following interfaces:

• 2 x Firewire 400
• 1 x USB 2.0
• 1 x eSATA

As I was curious to see how big is the difference between these interfaces I did some performance testing.

This are the results:

I did the perfomance testing under Windows Vista 64bit edition with the tool HDD Speed Test Tool by Marko Oette.
Regarding the eSATA interface I would like to say that the hot plug works (connect/disconnect while windows is running) and that while working with my computer I encountered sometimes some strange problems. The whole computer was blocked especially when I used applications that show an open file dialog or something similar. As soon as I unplugged the external HDD everything worked well. I would say that eSATA does not work 100%. This may be especially frustrating when making a backup of very large files and during the backup the HDD disappears (which happened to me). I don't exactly know if it is a driver, hardware or Windows Vista problem.

Search highlighting in WPF DataGrid

Unfortunately the DataGrid of the WPF-Toolkit does not provide a text highlighting feature out of the box; but sometimes it is very usefull for the users to be able to search for text or numbers in the datagrid to find the interesting information more quickly.
Tomer Shamam did a very good work which highlights a search key in different colors depending on which column they were found.

The only things which I don't like on his solution are:

1. It works only if the datasource of the datagrid are objects
2. That you have to define a style for every property you want to be highlighted in the datagrid which may be a big work if you have 15 properties for example
3. When a row with a match is selected you do not see anymore which cell was highlighted
   

For my pupose I don't need the feature of the different coloring because I want only to highlight all cells in the datagrid where the search term is present in the same color. To resolve all of the above issues I changed the code of Shamam slightly.

You still need this two helper classes written by Shamam:

public class SearchTermConverter : IMultiValueConverter
{
public object Convert(object[] values, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
 var stringValue = values[0] == null ? string.Empty : values[0].ToString();
 var searchTerm = values[1] as string;

 return !string.IsNullOrEmpty(searchTerm) &&
        !string.IsNullOrEmpty(stringValue) &&
        stringValue.ToLower().Contains(searchTerm.ToLower());
}

public object[] ConvertBack(object value, Type[] targetTypes, object parameter, System.Globalization.CultureInfo culture)
{
 throw new NotSupportedException();
}
}

and

public static class SearchOperations
{
public static string GetSearchTerm(DependencyObject obj)
{
 return (string)obj.GetValue(SearchTermProperty);
}

public static void SetSearchTerm(DependencyObject obj, string value)
{
 obj.SetValue(SearchTermProperty, value);
}

public static readonly DependencyProperty SearchTermProperty =
 DependencyProperty.RegisterAttached(
 "SearchTerm",
     typeof(string),
     typeof(SearchOperations),
     new FrameworkPropertyMetadata(string.Empty, FrameworkPropertyMetadataOptions.Inherits));

public static bool GetIsMatch(DependencyObject obj)
{
 return (bool)obj.GetValue(IsMatchProperty);
}

public static void SetIsMatch(DependencyObject obj, bool value)
{
 obj.SetValue(IsMatchProperty, value);
}

/* Using a DependencyProperty as the backing store for IsMatch.  This enables animation, styling, binding, etc...*/
public static readonly DependencyProperty IsMatchProperty =
 DependencyProperty.RegisterAttached("IsMatch", typeof(bool), typeof(SearchOperations), new UIPropertyMetadata(false));
}

As far as I understood this code defines a new dependancy property on the datagrid where the search term is stored and another dependancy property on each cell which stores wheter it is a match or not. Through the style which is defined in the Resource section of the page the "IsMatch" property is used to format the cell accordingly.

This is my modified XAML code to search the text of the datagrid instead of the property. Here you can also change the different colors if you like.

<Grid.Resources>
<local:SearchTermConverter
   x:Key="SearchTermConverter" />

<SolidColorBrush
   x:Key="{x:Static SystemColors.HighlightBrushKey}"
   Color="Blue" />

<SolidColorBrush
   x:Key="HighlightColor"
   Color="Yellow" />

<SolidColorBrush
   x:Key="SelectedHighlightedColor"
   Color="Red" />

<Style
   x:Key="DefaultCell"
   TargetType="{x:Type toolkit:DataGridCell}">
   <Setter
       Property="local:SearchOperations.IsMatch">
       <Setter.Value>
           <MultiBinding
               Converter="{StaticResource SearchTermConverter}">
               <Binding
                   RelativeSource="{RelativeSource Self}"
                   Path="Content.Text" />
               <Binding
                   RelativeSource="{RelativeSource Self}"
                   Path="(local:SearchOperations.SearchTerm)" />
           </MultiBinding>
       </Setter.Value>
   </Setter>
   <Style.Triggers>
       <Trigger
           Property="local:SearchOperations.IsMatch"
           Value="True">
           <Setter
               Property="Background"
               Value="{StaticResource HighlightColor}">
           </Setter>
       </Trigger>
       <MultiTrigger>
           <MultiTrigger.Conditions>
               <Condition
                   Property="IsSelected"
                   Value="True" />
               <Condition
                   Property="local:SearchOperations.IsMatch"
                   Value="True" />
           </MultiTrigger.Conditions>
           <Setter
               Property="Background"
               Value="{StaticResource SelectedHighlightedColor}"></Setter>
       </MultiTrigger>
   </Style.Triggers>
</Style>
</Grid.Resources>

Then the style is assigned to the datagrid:

<toolkit:DataGrid
AutoGenerateColumns="True"
Name="dgvDataTable"
CellStyle="{StaticResource DefaultCell}"
Margin="0,256,0,0"
Background="White" />

And finally the textbox connected to the "SearchTerm" property of the datagrid;
either via code in the TextChanged event of the TextBox:

SearchOperations.SetSearchTerm(this.dgvObjects, this.textBox1.Text);

or via XAML:

<Window
    x:Class="SearchHighlighting.Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="Window1"
    Height="460.138"
    Width="502"
    xmlns:toolkit="http://schemas.microsoft.com/wpf/2008/toolkit"
    xmlns:local="clr-namespace:SearchHighlighting">
...
<toolkit:DataGrid
    AutoGenerateColumns="True"
    Name="dgvDataTable"
    CellStyle="{StaticResource DefaultCell}"
    Margin="0,256,0,0"
    Background="White"
    local:SearchOperations.SearchTerm="{Binding ElementName=textBox1, Path=Text}" />

The result looks like this:



You can download the whole project from here.

SQL Server and legacy systems

Sometimes it happenes that we have to format the data which is stored in an SQL Server Database for an older system which requires the data to have a specific length. To achieve the length different datatypes are filled differently (at least in my case).

Lets take this example (we assume a length of 8):
- numbers have to be filled with zeros on the left (e.g. 66 -> 000066)
- strings have to be filled with spaces on the right (e.g. 'abc' -> 'abc ')

How can we achieve this?

Well the first problem is that we have to measure the length of the data and SQL Server provides two functions for that: 'LEN' and 'DATALENGTH'

This is not a problem you would say but it is because they behave differently. Look at the following examples:

Note the leading and trailing spaces of the strings!
"SELECT LEN(' abc ')" => 4
"SELECT DATALENGTH(' abc ')" => 5

"SELECT LEN(123)" => 3
"SELECT LEN(123.4567)" => 8

"SELECT DATALENGTH(123)" => 4
"SELECT DATALENGTH(999999999999)" => 9

Well this "strange" behaviour is because of what these functions do. Let's see the definition from MSDN on this:

LEN (Transact-SQL)Returns the number of characters of the specified string expression, trailing blanks.

DATALENGTH (Transact-SQL)
Returns the number of bytes used to represent any expression.

So in principle I would say: if you want to know the number of characters of a value then you should use DATALENGTH for string values ( nchar, text, nvarchar,...) and LEN for numbers (int, float, decimal,...).

The next problem when filling values is the NULL value. According to the definition of the two functions is LEN(NULL) equal to NULL and DATALENGTH(NULL) also NULL. This may be a problem since I have to provide blanks or zeros if the value is NULL (e.g. ' ', 00000000).

The third problem is on how to fill the value with the requested fillers (zeros and blanks)? Well for this SQL Server provides a very nice function: REPLICATE

This function takes as input a character which should be replicated and a length. E.g.:

"SELECT REPLICATE('0', 10)" => 0000000000
"SELECT REPLICATE(' ', 10)" => '          '

This helps us to create what we want. So if we have the number '99' and we want it to fill on the left to a length of 8 we can do something like:

"SELECT REPLICATE('0', 8 - LEN(99)) + CAST(99 AS NCHAR)" => 00000099
"SELECT REPLICATE(' ', 8 - DATALENGTH(' abc ')) + ' abc '" => '    abc '

To fill on the right we have another possibility; it is casted to an NCHAR of the desired length; but this fills only with blanks:

"SELECT CAST(' abc ' AS NCHAR(8))" => ' abc    '
"SELECT CAST(99 AS NCHAR(8))" => '99      '

If you want to fill with something different then you have to use again the REPLICATE function as described above.

To handle the NULL value we use the case statement:
"SELECT CASE WHEN(null IS NOT NULL) THEN REPLICATE('0', 8 - LEN(null)) + CAST(null AS NCHAR) ELSE REPLICATE('0', 8) END" => '00000000'

"SELECT CASE WHEN(99 IS NOT NULL) THEN REPLICATE('0', 8 - LEN(99)) + CAST(99 AS NCHAR) ELSE REPLICATE('0', 8) END" => '00000099'

Of course written like this it does not make much sense since we know in advance which part of the case statement is executed; but imagine you write in the place of the "null" or "99" the name of a column, then it makes perfectly sense. You can now easily write a case statement for string values.

Capturing Google Earth flight

Recently I created a photoshow from my photos of the summer and there I wanted also to add a Google Earth flight, so that everybody has an idea where this location is; and also because its simply cool :-)

On the internet they say that the pro edition has such a functionality but it costs 400$.
I tried then severeal screen capturing tools CamStudio or AutoScreenRecorder. They may be good to capture the screen to show other people how to perform some actions but for the Google Earth flight they are too slow. If they are capturing the flight isn't smoothly anymore.
Fortunately I found a very good tool which is also used to capture games. It is called Fraps and does a very very good job. I tried it with the OpenGL as well as with the DirectX mode of Google Earth and it works well with both of them. I think that after the test period it makes a watermark on the video but I can live with that.

Full text search for Entity Framework

Recently I played a bit with the "new" Entity Framework (basically the Object Relational mapper of Microsoft). It seems to be made quite good and it makes it very easy to work with objects in your application without having to map them back and forth from and to SQL. Also the possibility to launch LINQ queries over the data is very interesting because this avoids many errors and improves usability.
But I missed something: a fulltext search.
SQL Server has a very good fulltext search integrated and it would be nice to be able to perform queries through LINQ with it; but since Microsoft wanted to support also other database management systems they had to use only standard SQL for their mappings.

Faced with this problem I resolved it by writing my own pseudo fulltext search by using the possibility of creating dynamic LINQ queries.
First I will show You the code and then I explain a bit what I have done.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Reflection;
using System.Data.Objects;


public static class ObjectContextExtensions
{
/*
  /// <summary>
  /// Searches in all string properties for the specifed search key.
  /// It is also able to search for several words. If the searchKey is for example 'John Travolta' then
  /// all records which contain either 'John' or 'Travolta' in some string property
  /// are returned.
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <param name="query"></param>
  /// <param name="searchKey"></param>
  /// <returns></returns>*/
  public static IQueryable<T> FullTextSearch<T>(this IQueryable<T> queryable, string searchKey)
  {
      return FullTextSearch<T>(queryable, searchKey, false);
  }

/*
  /// <summary>
  /// Searches in all string properties for the specifed search key.
  /// It is also able to search for several words. If the searchKey is for example 'John Travolta' then
  /// with exactMatch set to false all records which contain either 'John' or 'Travolta' in some string property
  /// are returned.
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <param name="query"></param>
  /// <param name="searchKey"></param>
  /// <param name="exactMatch">Specifies if only the whole word or every single word should be searched.</param>
  /// <returns></returns>*/
  public static IQueryable<T> FullTextSearch<T>(this IQueryable<T> queryable, string searchKey, bool exactMatch)
  {
      ParameterExpression parameter = Expression.Parameter(typeof(T), "c");

      MethodInfo containsMethod = typeof(string).GetMethod("Contains", new Type[] { typeof(string) });
      MethodInfo toStringMethod = typeof(object).GetMethod("ToString", new Type[] { });

      var publicProperties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly).Where(p => p.PropertyType == typeof(string));
      Expression orExpressions = null;

      string[] searchKeyParts;
      if (exactMatch)
      {
          searchKeyParts = new[] { searchKey };
      }
      else
      {
          searchKeyParts = searchKey.Split(' ');
      }

      foreach (var property in publicProperties)
      {
          Expression nameProperty = Expression.Property(parameter, property);
          foreach (var searchKeyPart in searchKeyParts)
          {
              Expression searchKeyExpression = Expression.Constant(searchKeyPart);
              Expression callContainsMethod = Expression.Call(nameProperty, containsMethod, searchKeyExpression);
              if (orExpressions == null)
              {
                  orExpressions = callContainsMethod;
              }
              else
              {
                  orExpressions = Expression.Or(orExpressions, callContainsMethod);
              }
          }
      }

      MethodCallExpression whereCallExpression = Expression.Call(
          typeof(Queryable),
          "Where",
          new Type[] { queryable.ElementType },
          queryable.Expression,
          Expression.Lambda<Func<T, bool>>(orExpressions, new ParameterExpression[] { parameter }));

      return queryable.Provider.CreateQuery<T>(whereCallExpression);
  }
}

I created this method as an extension method for an IQueryable<T>.
This means if you have your ObjectContext (from the Entity Framework) then when accessing all objects of some type e.g. "context.Customers" then this returns an IQueryable<Customers>
This means that You can call the above method like:

context.Customers.FullTextSearch("serachkey");

or

context.Customers.FullTextSearch("searchkey", true);

which searches only for whole words also if they contain spaces. This gives You then again an IQueryable<Customers> which contains only the filtered objects.

Ok. Now something about the code.
This method "simply" creates a lambda expression which gets all the string properties of the type T, calls the "Contains" method with the search key as parameter on them and connects them with an OR. This means that it would be similar to do something like:

context.Customers.Where(c => c.FirstName.Contains("searchkey") || c.LastName.Contains("searchkey") || c.Street.Contains("searchkey") || ...);

But this can become a very long, nasty and error prone task if You have for example 30 string columns. Exactly because of this I wrote this code :-)

Hope this helps You...

LINQ, Expression trees and ORMapper

In response to the question of Jonathan of my post on expression trees I will post here some code on how an expression tree can be analyzed and used to create your own object relational mapper.

The core of the app is the expression tree visitor. Practically it traverses all the expressions of a LINQ expression and creates the appropriate SQL instructions for it. To get the correct column and table names it uses an interface called IMappingProvider which makes it possible to have different mapping providers. I created an AttributeMappingProvider which provides the mapping by defining attributes on the classes and properties, an XmlMappingProvider which reads the mapping information from an XML file and a DBMappingProvider which reads from a table in a database. My ORMapper is able to create Select and Delete queries by specifying a LINQ expression as where clause.

But enough theory; here is some code :-)
This is the core method which dispatches the incoming expression and calls the appropriate method:

/* /// <summary>
/// This method is the dispatcher for the different expression types.
/// NOTE: not all expression types are considered.
/// </summary>
/// <param name="expression">The expression.</param>
/// <param name="sql">The SQL string builder.</param>
/// <param name="isOnRightSide">Tells if the expression is on the right hand side. Necessary for some expression evaluations.</param> */
private object VisitExpression(Expression expression, StringBuilder sql, bool isOnRightSide)
{
  LambdaExpression lambdaExpression = expression as LambdaExpression;
  BinaryExpression binaryExpression = expression as BinaryExpression;
  MemberExpression memberExpression = expression as MemberExpression;
  ConstantExpression constantExpression = expression as ConstantExpression;
  UnaryExpression unaryExpression = expression as UnaryExpression;
  MethodCallExpression methodCallExpression = expression as MethodCallExpression;
  ParameterExpression parameterExpression = expression as ParameterExpression;

  if (lambdaExpression != null)
  {
      VisitExpression(lambdaExpression.Body, sql, false);
  }
  else if (binaryExpression != null)
  {
      VisitBinaryExpression(sql, binaryExpression);
  }
  else if (memberExpression != null)
  {
      return VisitMemberExpression(sql, isOnRightSide, memberExpression);
  }
  else if (constantExpression != null)
  {
      VisitConstantExpression(sql, constantExpression);
  }
  else if (unaryExpression != null)
  {
      VisitUnaryExpression(sql, unaryExpression);
  }
  else if (methodCallExpression != null)
  {
      VisitMethodCallExpression(sql, methodCallExpression);
  }
  else if (parameterExpression != null)
  {
      VisitParameterExpression(sql, parameterExpression);
  }
  else
  {
      throw new NotSupportedException(string.Format("The '{0}' is not supported!", expression.GetType().Name));
  }

  return null;
}

Let's take for example the method to analyze a binary expression:

private void VisitBinaryExpression(StringBuilder sql, BinaryExpression binaryExpression)
{
  sql.Append("(");
  VisitExpression(binaryExpression.Left, sql, false);
  sql.Append(GetOperandFromExpression(binaryExpression));
  VisitExpression(binaryExpression.Right, sql, true);
  sql.Append(") ");
}

You can see here that for binary expressions (e.g. =, &&, ||) I always open a parenthesis; this creates also unnecessary parenthesis, but makes sure that boolean conditions are evaluated correctly. Then the left expression is evaluated, the operator is added, the right expression is evaluated and finally a closing parenthesis is added.
This is the dispatcher method for the operators:

/* /// <summary>
/// This method is the dispatcher for the operands.
/// NOTE: not all operands are implemented!
/// </summary>
/// <param name="expression">The expression to dispatch.</param>
/// <returns>The appropriate SQL operand.</returns>*/
private string GetOperandFromExpression(Expression expression)
{
  string operand = string.Empty;
  switch (expression.NodeType)
  {
      case ExpressionType.And:
          operand = "AND";
          break;
      case ExpressionType.AndAlso:
          operand = "AND";
          break;
      case ExpressionType.Equal:
          operand = "=";
          break;
      case ExpressionType.ExclusiveOr:
          operand = "OR";
          break;
      case ExpressionType.GreaterThan:
          operand = ">";
          break;
      case ExpressionType.GreaterThanOrEqual:
          operand = ">=";
          break;
      case ExpressionType.Not:
          operand = "NOT";
          break;
      case ExpressionType.NotEqual:
          operand = "<>";
          break;
      case ExpressionType.Or:
          operand = "OR";
          break;
      case ExpressionType.OrElse:
          operand = "OR";
          break;
      default:
          throw new NotImplementedException();
  }

  return operand + " ";
}

To get the correct string to add to the sql query I wrote a little method which performs also escaping of the single quote. Additionally it calls the to string method with the invariant culture if the object supports it.

private string FormatValue(object value)
{
  if(value == null || value == DBNull.Value)
  {
    return "NULL";
  }

  string stringValue = value.ToString();
  var invariantMethod = value.GetType().GetMethod("ToString", new Type[] { typeof(CultureInfo) });
  if (invariantMethod != null)
  {
      stringValue = (string)invariantMethod.Invoke(value, new object[] { CultureInfo.InvariantCulture });
  }

  stringValue = stringValue.Replace("'", "''");

  if (value is string || value is DateTime)
  {
      return string.Format("'{0}'", stringValue);
  }
  else
  {
      return stringValue;
  }
}

To test all this I created a little sample application and a sample class. The customer class supports the AttributeMappingProvider and specifies the mapping on the class and propeties:

[DBTable("cust")]
class Customer
{
    [DBField("nam")]
    public string Name { get; set; }

    [DBField("num")]
    public int Age { get; set; }

    [DBField("height")]
    public int Height;

    [DBField("surname")]
    public string Surname;
}

To get some results I wrote this code:

static void Main(string[] args)
{
    ORMapper mapper = new ORMapper();
    var c1 = new Customer{Name = "cus2"};
    var selectQuery = mapper.Select<Customer>(c => c.Name == "hallo" && c.Age == 12 || c.Name == c1.Name && c.Name == 4.ToString() && c.Name.Contains("aaa"));
    var deleteQuery = mapper.Delete<Customer>(c => c.Name == "hallo" && c.Age == 12 || c.Name == c1.Name && c.Name == 4.ToString() && c.Name.Contains("aaa"));
    Console.WriteLine(selectQuery);
    Console.WriteLine(deleteQuery);
    Console.ReadLine();
}

which outputs this:

SELECT * FROM cust WHERE (((nam = 'hallo') AND (num = 12)) OR ((((nam = 'cus2') AND (nam = '4')) AND (nam LIKE '%aaa%')) AND (height = 10)));

DELETE FROM cust WHERE (((nam = 'hallo') AND (num = 12)) OR (((nam = 'cus2') AND (nam = '4')) AND (nam LIKE '%aaa%')));

You can cahnge the mapping provider in the constructor of the ORMapper.
A sample file for the XmlAttributeMapper is also included. Here the mapping is specified like this:

<?xml version="1.0" encoding="utf-8" ?>
<mapping>
  <Customer table="tbl_cus">
    <map property="Name" column="cus_nam"></map>
    <map property="Age" column="cus_age"></map>
  </Customer>
</mapping>

I did not test the DBMappingProvider, but You can easily adapt it to your needs.

You can download the project and source code files from here.

NumericUpDown validation

Today I had the problem that I had to set up input validation on an edit form. Among other controls there was also a NumericUpDown (NUD) control and I thought: well if I specifiy the min value and the max value then the user is not able to input a wrong value;

This code prints the current value of the NUD to a message box:

MessageBox.Show(this.numericUpDown1.Value.ToString());

You can see an example for this in the following screenshot:

This works fine, but there is one exception. It is also possible to leave the NUD blank. If you show then the value of the NUD again with the above code then you get the last value that was present in the NUD control; as shown below:

This can also be explained. The value property of the NUD control is of type decimal in order to deal with the different types of numbers. The problem is that it is of type decimal and not decimal? (this is the nullable version of decimal) and therefore it has to have value. Now is the question which value to set if the control is empty: 0, -1, decimal.MinValue or decimal.MaxValue?? None of these value really expresses what the user has entered; namely nothing.
In a forum in the internet I read that a workaround for this issue is to check the Text property of the NUD control; but as you can see below the NUD control does not have such a property!!

Fortunately there exists a solution to this problem. If you want to check if the user has entered a number into the NUD control you can use this code to check this:

if (string.IsNullOrEmpty(((Control)this.numericUpDown1).Text))
{
 /* the user entered nothing */
}
else
{
 /* the NUD contains a valid number */
}

HTML parsing

In a very small personal project I needed to parse an HTML page. I thought that HTML = XML and so I tried to load the response string into an XDocument; but I was too optimistic. I got a lot of errors from the XDocument.Parse() method. I read on the internet that it is only possible to do this if the web page is XHTML and follows all the standards. But there are a lot of web pages out there which never heard nothing about standards. A good example are pages from Microsoft. This page of the Sysinternals Suite for example produces 53 errors and 14 warnings when validating it with the W3C validator!!

There is a solution to the HTML parsing problem for C#. There exists a project called HTML Agility Pack on CodePlexx which provides a mechanism to parse an HTML string and to navigate it like ian XmlDocument. It works great but it does not provide support for LINQ queries which is very nasty if you are accustomed to it (like me :-))
But also for this exists a solution. Since the source code of the HTML Agility Pack is open for everyone Vijay Santhanam created the ToXDocument() method, which converts the HtmlDocument to XDocument. This is then queryable with LINQ to XML. The post about this and a link to his project can be found here.

It would be nice if they would add direct LINQ support to their HtmlDocument class but maybe they will do so in the future...

Reflection

While trying to speed up an application I searched the internet for information about reflection. I use reflection quite often when I have to write a special XML serializer or when I have to write a simple OR mapper. The problem is that it is slow. I understand now also because you have to specify the type when you use the XmlSerializer of the .Net framework. Internally it generates IL code which speeds up the serialization a lot because the intermediate code is much faster than direct reflection.

Regarding this problem I found a very interesting blog post which you can check out here.

There a comparison is made between the direct reflection, reflection by using Reflection.Emit and by caching Reflection.Emit operations. As you can see there from the test results the cached reflection is about 8 times faster!! This may not be of importance if you serialize only 100 objects but if you serialize thousands or millions of objects than this makes a big difference.

I tried his code in a project of mine where I use quite a lot reflection and indeed the operation finished about 6 times faster!

If you do a lot of reflection in your projects you should definitely give a look at this code. It's worth...

Performance

Today I had to solve a common problem. The customer sad that the application is too slow; substantially the data import from an XML file into the object model is too slow. As every developer knows, performance problems are very nasty and it means also that you have to break your nice object oriented structure to gain some performance boosts :-(
So I searched in the imort routine for code parts which can be optimized. I was able to change some code but that brought only a very small performance gain.
After some time I decided to switch off the logging that states how many percent of the elements were already imported. That message was written to the console window and since the operation required about 15 seconds it was interesting to see at which point the import is.
Then the surprise. After switching off the logging the data was imported in about 1,3 seconds. I knew that writing to the console is slow but that it is so slow...
To solve this problem now I tried first to use an asynchronous event logging. It worked also quite well, but when the data was already loaded and displayed the logger wrote for some seconds longer to the console by indicating that 97% of the data was loaded now.
This was also not a choice. Then I decided that it would be a good idea to switch off logging when running in release mode and turn it on when running in debug mode. So I have all informations on hand whenn looking for errors and the performance for the customer.
To realize this I implemented in my logger a the method "LogDebugEvent" and "decorated" it with the "Conditional" attribute:

[Conditional("DEBUG")]
public void LogDebugEvent(LogLevel logLevel, string message, params object[] args)
{
  /* some code here*/
}

This means that the compiler decides if the method call is executed or not. If you compile in Release mode than all calls to this method are removed from the code as if they do not exist. If you run in Debug mode then all calls are executed. This can be very usefull especially if you have code which slows down your program and is only for debug purposes.
Important is that it matches exactly the constant that you want to use. It is casesensitive!! In the case of debug it is as you can see in the example above "DEBUG".

More informations about the ConditionalAttribute can be found here and here.

The facts of life

Recently I heard a very interesting song. It is called "The facts of life - Lazy Boy".

This are the facts that impressed me most:

• 3 people die every year, testing if a 9 volt battery works on their tongue
• The 'Guinness Book Of Records' holds the record for being the most stolen book in public libraries
• The worlds best known word is 'okay', the second most well-known word is 'Coca-Cola'
• Charles Chaplin once won 3rd prize in a 'Charles Chaplin look-a-like contest'
• Only one book has been printed in more copies than the Bible - the IKEA-catalogue
• Did you know that originally a danish guy invented the burglar alarm? - unfortunately it got stolen

Here you can find the whole song text in english... :-)

And this is the video of the song (in italian):