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.

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;
    }
}

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.

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.

LINQ Expression tree

Lately I had to implement my own LINQ to SQL "converter" which uses a custom mapping stored in the database. It is even not as difficult as I thought. You have "only" to recursively parse the expression tree and convert it to the correcsponding SQL instructions.
The only thing I had some problems with were local variables or parameters.
Consider the following code:

int a = 3;
Get<Person>(p => p.Age == a);

the class Person

public class Person
{
    public int Age { get; set; }
}

and the method

public static void Get<T>(Expression<Func<T, bool>> e)
{
    MemberExpression member = (MemberExpression)((BinaryExpression)e.Body).Right;
    ConstantExpression constant = (ConstantExpression)member.Expression;
    Console.WriteLine(constant.Value.ToString());
}

when analyzing the expression tree then You see that the right expression of the BinaryExpression is of type ConstantExpression. Normally ConstantExpression contain directly a value. But this is not the case here! The console writeline produces the following: "ConsoleApplication1.Program+<>c__DisplayClass0"
The value of the constant expression is an anonymous object which is created on the fly. This object has a field which is named a and that field contains the value.
Therefore I had to do the following:

public static void Get<T>(Expression<Func<T, bool>> e)
{
    MemberExpression member = (MemberExpression)((BinaryExpression)e.Body).Right;
    ConstantExpression constant = (ConstantExpression)member.Expression;
    FieldInfo info = (FieldInfo)member.Member;
    Console.WriteLine(info.GetValue(constant.Value).ToString());
}

Instead of evaluating the expression which is associated to the MemberExpression.Expression I used the Member and casted it to a FieldInfo. Then I called the GetValue method passing it the value of the ConstantExpression which is basically the object. In this way I finally got the correct value of a.
The same works also for method parameters. Consider this code:

public static void FromMethod(int a)
{
    Get<Person>(p => p.Age == a);
}

there we have exactly the same problem and it can be resolved in the way described above.

Bulk insert of data

Recently I faced the problem of inserting a big amount of data into the database. The point is that multiple tables are affected which are connected with foreign keys. After some research I found OpenXML as one possibility. You create a stored procedure where you read your XML string into an "XmlDocument" which you can then query by using XPath and inserting, updating or deleting the tables occordingly.

The other possibility I found is SQLXML which is now available in version 4.1. It is delivered with SQL Server but not with the Express version. If you need it you can download it from here. SQLXML allows you to bulk insert data from an XML file by using a so called annotated XSD schema where the mapping is defined. It is not very difficult to define the schema, only recursion and special cases are a little bit difficult to handle. I was not able to make recursion work with identity values coming from the database. Anyway; I did some performance tests where I had a structure like:
Table A
Table B has a foreign key to Table A
Table C has a foreign key to Table B
which corresponds to the following XML structure:
<root>
<a>
<b>
<c></c>
</b>
</a>
</root>

I created an XML file that contained 100 A's; every A contained 100 B's and every B contained 100 C's. This gives a total of 100 A's, 10.000 B's and 1.000.000 C's. To insert all them and updating the foreign keys it took 84 seconds. This is quite fast for such an amount of data. I think that this is the fastest way to bulk insert data into SQL Server (if I'm wrong You are welcome to coment on this).
If you want to use it with .Net add a reference to "Microsoft SQL Server Bulk Upload" or something like that.
I don't remember the exact name but as soon as I find the link of the code sample I will post it here.

The problem is that SQLXML cannot be used for updates. There exist so called updategrams or diffgrams but I didn't try them.

Updating App.config

Today I faced the problem to update the App.config. There exist several solutions out there but I was not able to find a simple one. Therefore I decided to try out the new Linq to XML feature of .NET 3.5 and wrote the following method which updates the value of a specific key in the App.config file:

public static void UpdateAppConfig(string key, object value)
{
  if (key == null)
      throw new ArgumentNullException("key");

  if (value == null)
      throw new ArgumentNullException("value");

  XDocument doc = XDocument.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
  var result = (from appSetting in doc.Descendants("appSettings").Descendants("add")
                where appSetting.Attribute("key").Value.Equals(key)
                select appSetting).FirstOrDefault();

  if (result != null)
  {
      result.Attribute("value").SetValue(value);
      doc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
      doc = null;
  }
  else
  {
      Console.WriteLine("The application setting with key '{0}' was not found!", key);
  }
}

Localizing .NET applications

Most applications nowadays have to be localized in order to provide user interfaces in the language of the user who uses the application. The .NET framework offers a very handy mechanism to provide localization for your apps. It uses so called resource files (.resx) to store keys and the corresponding translated texts or images. If you want to know more on how to work with this translation mechanism take a look at this post.

The main problem with these files is to keep them in sync (for example when adding new strings) and to give translators an easy tool to make translation without requiring them to have visual studio installed on their computers.
I found a tool called "ResEx ... the composite, translation friendly .NET Resource editor" which can be found here that meets exactly this requriements. It provides a nice spreadsheet interface which makes it also for non programmers easy to translate the resources in the files. It is also possible to group resources together by using an underscore e.g. Menu_Home, Menu_Customers; they will all be shown under the tree node "Menu" which makes it easier to understand where the translations are used. Even adding and deleting localization languages is very easy.
The only thing which is missing is to have an automatic translation by using one of the several online translators available nowadays. I will suggest it on the forum and maybe this feature will be available in a future release.

Another very useful tool when working with resource files is the Resoucre Refactoring Tool on CodePlex. It helps you by localize strings in your source code by putting the string into the resource file and replacing the corresponding call in order to get a translated string.

Extension Methods in C#

There are some new features in C# 3 which are introduced to make LINQ possible.
One of them are extension methods which I wanted to try out. Extension methods are static methods which can be applied to any object type which is specified as "special parameter". For example if you want to extend the type string with the method "Greet" you write something like this:

public static void Greet(this String s)
{
Console.WriteLine("Hello " +  s);
}

Then you can call it like:

string name = "Tom";
name.Greet();

This will produce the following on the console: "Hello Tom"

I played a bit with this extension mehtods and came up with the following extensions for object which I believe can be helpful.
Assume the following object:

Customer testCustomer = new Customer() { Name = "John", Age = 35, LastName = "Smith" };

PrintConsole() -> prints the string representation of an object to the console
Code:

testCustomer.PrintConsole();

Produces:
"ExtensionMethodTest.Customer"

PrintMessageBox() -> shows the string representation of an object in a message box
Code:

testCustomer.PrintMessageBox();

Produces:


PrintPropertiesConsole() -> Prints all public non inherited properties and their values of the object to the console
Code:

testCustomer.PrintPropertiesConsole();

Produces:
Properties of 'ExtensionMethodTest.Customer'
Name: John
LastName: Smith
Age: 35

PrintPropertiesMessageBox() -> Shows all public non inherited properties and their values of the object in a message box
Code:

testCustomer.PrintPropertiesMessageBox();

Produces:

string ToXml() -> Serializes the object to XML and returns the XML string
Code:

testCustomer.ToXml();

Produces:

John
Smith
35

ToXml(Stream stream) -> Serializes the object to XML and writes it to the stream
Code:

testCustomer.ToXml(myStream);

ToXml(string fileName) -> Serializes the object to XML and writes the XML string to the file specified.
Code:

testCustomer.ToXml("c://test.xml);

If you are interested in my project you can download it from here.
Or you can simply download the Extensions.dll from here and import it into your project.

All extension methods are in the namespace System. I know that this is not the way how to do it but so you don't have to add a using directive because System is by default present.

TreeView node font problem

Recently I encountered the following problem. I needed to set the font style of some nodes in a treeview at runtime to bold; but after setting the NodeFont property of the node the font changed correctly, but the text of the node was cut off.

This screenshot shows the problem:


The solution to the problem is to add an empty string to the Text property after setting the NodeFont property:

this.treeView1.SelectedNode.NodeFont = new Font(this.treeView1.Font, FontStyle.Bold);
this.treeView1.SelectedNode.Text += string.Empty;

The problem is also known at Microsoft and I found this workaround here.

This screenshot shows the correct text:

Enhanced combobox with readonly and working value member

As I had several problems with the standard combo box of the .Net 2.0 Framework such as the missing read only property or the not working value member property (if not using a datasource), I decided to write my own improved combo box. To change the default combobox to my needs I overwrote the OnPaint() mehtod and provided some additional methods.

With the enhanced combo box you can finally specify a value member without having to use a datasource as you can see in the code below:

Customer customer1 = new Customer();
customer1.FirstName = "John";
customer1.ID = 1;
customer1.LastName = "Smith";

Customer customer2 = new Customer();
customer2.FirstName = "Marco";
customer2.ID = 2;
customer2.LastName = "Polo";

this.cmbTest.Items.Add(customer1);
this.cmbTest.Items.Add(customer2);

this.cmbTest.DisplayMember = "FirstName";
this.cmbTest.ValueMember = "ID";

Or you can use a datasource:

Customer customer1 = new Customer();
customer1.FirstName = "John";
customer1.ID = 1;
customer1.LastName = "Smith";

Customer customer2 = new Customer();
customer2.FirstName = "Marco";
customer2.ID = 2;
customer2.LastName = "Polo";

Customer customer3 = new Customer();
customer3.FirstName = "Tom";
customer3.ID = 3;
customer3.LastName = "Hanks";

List customers = new List();
customers.Add(customer1);
customers.Add(customer2);
customers.Add(customer3);

this.cmbTest.DataSource = objects;

this.cmbTest.DisplayMember = "FullName";
this.cmbTest.ValueMember = "ID";

Additionally you can register for different object types a display and value member. This gives you a lot of flexibility when you have to show information of different objects in the same combobox.

To register a new type call the "RegisterType" method and pass it the type of the object, the name of the property which is used as display member and the name of the property which is used as value member.

this.cmbTest.RegisterType(typeof(Customer), "FirstName", "PK");

To unregister a registered type call "UnregisterType" and pass it the type you want to unregister.

this.cmbTest.UnregisterType(typeof(Customer));

To modify the display and/or value member of a registered type call "ChangeValueMember" or "ChangeDisplayMember" respectively and pass it the new name of the property you want to use.

this.cmbTest.ChangeDisplayMember(typeof(Customer), "FullName");
this.cmbTest.ChangeValueMember(typeof(Customer), "LastName");

Of course all this operations can be performed at runtime and the changes are immediately visible in the combo.

To obtain the value of the selected item, simply call "SelectedValue".

Console.WriteLine(this.cmbTest.SelectedValue.ToString());

To get the selected object simply call "SelectedItem" and cast it to the correct type.

Customer selectedCustomer = this.cmbTest.SelectedItem as Customer;
if (selectedCustomer != null)
{
...
}

NOTE: If the combobox has to visualize a type which is not registered it calls the ToString() method of the object and uses that string.

Now I'd like to show you the read only property. The enhanced combobox provides a read only property which disables the drop down button and changes the back color of the combobox to "Control". The read only property is not a real read only because it is not possible to select and then copy the visible text. The most important point is that you can change the fore- and backcolor of the combobox when it is in read only because it is not very easy to read the text of a disabled combo box. The images below show what I mean.

This is a disabled ComboBox:

This is a read only ComboBox:

You can set any color as read only back- and fore color you like:

That's all about my enhanced Combo Box. I hope it helps someone to achieve what he wants. If you have suggestions on how to improve it or you found a bug, feel free to leave a comment.

You can download the DLL from here and simply drag and drop it to your visual studio toolbox.

Or you can get the source code with a sample tool which shows how to use it from here.

Who is faster??

Friday 24. of April I attended a conference which was organized by the dotnetpro magazine. The theme of the conference was Jump! and it was intended to give developers an overview of the new features of Visual Studio 2008, .Net framework 3.5 and C# 3.0. It was very interesting to see the new technologies and in which direction we are going. Something that impressed me was the new query language LINQ which can be used to query many kinds of data such as lists, xml files or databases. I was curious about the performance of this new technology and therefore I did some performance tests. I wanted to compare the traditional for loop, the nowadays mainly used foreach loop, the FindAll method of the collections class and LINQ. I executed every "method" two times because especially for LINQ this makes a big difference. The task that each "method" had to perform was to find all values which are bigger than 5 in a list of integers which contains all together 1,000,000 elements and add it to a result list. The result list contained about 400,000 elements because I filled it with a random number between 0 and 10.

Since under windows it is almost impossible to get accurate performance results because services and applications in the background interrupt my test application I run it altogether 10 times and then I took the least value for every "method". I was very impressed of the results, but first I will show you the code that I used:

This is the simple for loop that everybody knows how it works.

for (int i = 0; i <> 5)
     result.Add(numbers[i]);
}

Here we have the well known foreach loop which is and should be used.

foreach (int num in numbers)
{
 if (num > 5)
     result.Add(num);
}

This the FindAll method of the collections class. It uses a predicate to decide which elments should be returned and I used an anonymous delegate to state my condition.

result = numbers.FindAll(new Predicate(delegate(int i) { return i > 5; }));

And finally here we have the LINQ query which looks like SQL and allows to make structured queries in the code. The var keyword in front states that the compiler decides of which type the variable "res" is. In this case it will be a collection of integers.

var res = from num in numbers
       where num > 5
       select num;

To talk about syntax; I prefer the LINQ syntax, because it is compact, structured and SQL like which many developers know quite well. Moreover when you have to make grouping and you do this with a foreach loop then you require much more lines of code as when you do it with LINQ. And as a speaker on the conference said:

More lines of code contain more errors.

Ok here there is the result of my measurements:

As you can see on the chart above, LINQ is by far the fastest "method" to perform such "queries". I was very impressed by the velocity. The difference between the first run and the second run lies in the implementation of LINQ. A speaker on the conference said that behind the scenes they build a binary tree and use it for searching. Therefore when this tree is build once all following queries are very very fast, also if you change the search condition.

Other things that you can see from the chart is that the good old foor loop is compared to the foreach and the FindAll "methods" the fastest. Despite this fact I will use the foreach loop in future because it is more structured and requires less thinking about the underlying array, list,... because the collection class does the work for you. I was disappointed of the performance of the FindAll method of the collection class. It was the slowest in the test. This could be because it uses predicates and anonymous delegates which may use more resources. The syntax of the FindAll is also compact and after some learning you know how to create anonymous delegates. The advantages of the methods of the collection class such as FindAll, Find, ConvertAll,... is that this features are available already under the .Net 2.0 framework and that you need only one line of code to do such "queries". The big disadvantage is as already said above that it is the slowest method to find a list of elements which satisfy a condition. For the other methods (Find, ConvertAll) I will maybe do some tests to see their performance.

If you are interested to run this performance test on your own machine you can download it here.

Tools used:
Microsoft Visual Studio 2008 Express Edition

Generic progress dialog

Quite often I have to perform long operations in an application and then I want to show a progress bar to the user that he can see that the application is working and how long it will take. My requirements for the progress bar dialog are:

• it should be generic and therefore not be tied with the work which has to be done
• it should not block or freeze the main application
• the user must have the possibility to abort the operation

It turned out that it is quite challenging to meet all these requirements. With different approaches I had different problems and could not fulfill my requirements. For example there was a version where the progress dialog was not updated or the user could not abort the operation. After some trying I found a quite usable solution. The main application uses a backgroundworker to do the work; this has the advantage that the computation is done in a different thread and the backgroundwoker provides cancel and progress notification. My progress bar dialog is started also in its own thread to make sure that its graphics are not blocked and updated when needed. With this "architecture" it is even possible that the main application performs some other operations while a heavy work is done and the progress shown to the user. The progress dialog provides all its functionalities through static methods which access a "private singleton". This means that the developer never gets an instance of the progress dialog and that at most one instance of the progress dialog exists. To notify the main application about the cancel event I used an event with a delegate. Therefore the main application can do what it wants when the user presses cancel.

When the user presses cancel then a message box is shown which asks if he really wants to abort. The nice thing with this implementation is that while this message box is shown the computation goes on and is not blocked.

The following code shows the progress bar, sets the title and message, assigns a method to the cancel event and starts the computation if the background worker is not already working:

if (!this.backgroundWorker.IsBusy)
{
    this.Enabled = false; /*lock the main application*/
    ProgressDialog.Show(max);
    ProgressDialog.SetTitle("this is the title");
    ProgressDialog.SetMessage("I am the message for the very long task. Please be patient and wait...");
    ProgressDialog.CancelEvent += new ProgressDialog.CancelEventHandler(pd_CancelEvent);
    this.backgroundWorker.RunWorkerAsync();
}

Important is to set the cancellation property of the background worker to true; this is best done in the designer or in the constructor of the main form:

this.backgroundWorker.WorkerSupportsCancellation = true;

The computation is done in the do_work event of the background worker. Here is important that in the loop the check for the cancel event is done because otherwise the backgroundworker will not stop on cancel. To update the progress bar it is enough to call the SetValue method of the progress dialog. This is only a very stupid operation for demonstration:

private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
    for (int i = 0; i < max; i++)
    {
        /*this check is needed in order to abort the operation if the user has clicked on cancel*/
        if (this.backgroundWorker.CancellationPending)
        {
            e.Cancel = true;
            return;
        }

        ProgressDialog.SetValue(i); /*update the value*/
        Console.WriteLine(i);
    }
}

Another thing that I have to mention. In debug mode there occurs always an exception which says that this operations are not thread safe. Till now I did not had the time to look how I can make this code thread safe, but as soon as I have a thread safe version (if one exists) I will post it.
UPDATE:
I finally had the time to learn how to write thread safe method calls. The code below shows how it is done. First you have to check if the mehtod was called by a Thread other than the own Thread. This does the property "InvokeRequired". If so a delegate is created and the method is called with "Invoke".

public static void SetTitle(string title)
{
    if (instance.InvokeRequired) /*if another thread called this method*/
    {
        SetTitleCallback s = new SetTitleCallback(SetTitle);
        instance.Invoke(s, title);
    }
    else
    {
        instance.Text = title;
    }
}

You can download the full demo project here.

Finally a screenshot on how my progress bar dialog looks like:


If you have suggestions, ideas or You know how to improve this code or if You have a thread save version You are welcome to post a comment.

How to draw a disabled control?

Recently I had to overwrite the OnPaint method of a button because I needed to draw a gradient background. My button had to display images and text. As the button should also have the disabled state where the image is gray scale I did not know how to get such an effect. After some research I found the ControlPaint class which is very useful not only in my case. It basically helps you to draw controls or part of controls such as the drop down button for a combo box and so on.

On the following picture you can see how this may look like:

The code to accomplish this is the following:

public class MyButton : Button
{
   protected override void OnPaint(PaintEventArgs pevent)
   {
       Graphics g = pevent.Graphics;
       g.FillRectangle(Brushes.White, pevent.ClipRectangle);
       g.DrawRectangle(new Pen(Brushes.Black), pevent.ClipRectangle);
       if (Enabled)
       {
           if(Image != null)g.DrawImage(Image, 0, 0);
           g.DrawString(Text, Font, Brushes.Black, new PointF(100, 0));
       }
       else
       {
           if(Image != null)ControlPaint.DrawImageDisabled(g, Image, 0, 0, Color.Transparent);
           ControlPaint.DrawStringDisabled(g, Text, Font, Color.Transparent, new RectangleF(100, 0, 100, 20), StringFormat.GenericDefault);
       }
   }
}

The class ControlPaint offers also other usefull operations and you can take a look at all its members here.

Object - Relational converter

Are you also bored of writing all these sql statements which are needed to persistently store your objects into a database? 80% of these sql statements are inserts, updates or deletes and can be a quite an amount of work to write them especially if you have objects with more than 10 properties. Additionally there is maybe a file which is often called something like “database manager” which contains all this stuff and grows and grows with every new class that you write.
I was faced with the same problem and wanted to do something against this problem. If you are starting to build a project I would suggest you to use a free object relational mapper such as Castle Active Record which takes care of all the database stuff and makes your life a lot easier. It supports by far more features and supports different DBMS. If you are already within development or you do not want to use an object relational mapper or you simply do not want to inherit all your classes from a base class (which is used often used by object relational mapper) you could consider to use my approach.
I created a small object relational converter which is able to generate insert, update and delete statements for you based on an object that you pass as parameter. Additionally it provides the possibility to create objects from a datatable or datareader object.
I developed this code for Microsoft SQL Server Express Edition and therefore if you use another DBMS or you need to add another data type you have to change the “ToSQL” method which is responsible to convert the different data types into their correct sql representation:

/* This method is responsible to convert the datatypes into their corresponding */
/* sql representation. Change it if you encounter problems or if a data type */
/* needs special treatmend. */
private static string ToSQL(object obj)
{
 if (obj is string || obj is Boolean)
     return "'" + obj.ToString() + "'";
 else if (obj is DateTime)
     return "'" + ((DateTime)obj).ToString(DATE_TIME_FORMAT) + "'";
 else if (obj is decimal)
     return obj.ToString().Replace(',', '.');
 else
     return obj.ToString();
}

To realize my object relational converter I used reflection and additionally the objects which should be converted have to provide some additional information such as the table name, the column name for each property and the primary key. With reflection I obtain all public, not inherited properties and use them to create the SQL statements. If you want a different behaviour such as you want to include also inherited members then you have to modify the “GetProperties” method according to your needs:

/* This method is responsible to obtain all public, not inherited properties of an object. */
private static PropertyInfo[] GetProperties(object obj)
{
 return obj.GetType().GetProperties(
     BindingFlags.DeclaredOnly |
     BindingFlags.Instance |
     BindingFlags.Public);
}

Another thing that you should know is that you have to take care to establish a connection to your DBMS and to execute the queries. My code does only the conversions.

Finally I would like to state how a class must look like in order to use my object relational converter. You have to specify for the class the table name and for each property it’s associated column name in the database.
CAUTION: DO NOT USE RESERVED KEYWORDS OF THE SQL LANGUAGE BECAUSE THIS IS NOT CHECKED AND MAY CAUSE UNEXPECTED PROBLEMS!!
If the property represents the primary key of the table then you have to add a true because the primary key is needed for the update and delete statement. Here is now a sample class:

[Table("myTable")]
public class TestObject
{
 private int table_pk;
 private string text;
 private DateTime datetime;
 private int number;
 private bool boolean;
 private decimal decimalNum;

 [Column("mytable_pk", true)]
 public int Table_PK
 {
     get { return table_pk; }
     set { table_pk = value; }
 }

 [Column("datetime")]
 public DateTime Datetime
 {
     get { return datetime; }
     set { datetime = value; }
 }

 [Column("number")]
 public int Number
 {
     get { return number; }
     set { number = value; }
 }

 [Column("text")]
 public string Text
 {
     get { return text; }
     set { text = value; }
 }

 [Column("boolean")]
 public bool Boolean
 {
     get { return boolean; }
     set { boolean = value; }
 }

 [Column("decimalnum")]
 public decimal DecimalNum
 {
     get { return decimalNum; }
     set { decimalNum = value; }
 }

 public TestObject()
 {
     this.table_pk = 999;
     this.number = 5;
     this.text = "this is a test string";
     this.datetime = DateTime.Now;
     this.boolean = true;
     this.decimalNum = new decimal(1.5);
 }

 public override string ToString()
 {
     return "pk = " + this.Table_PK + ", datetime = " + this.Datetime.ToLongDateString() + ", number = " + this.Number.ToString() + ", text = " + this.Text + ", boolean = " + this.Boolean.ToString() + ", decimal = " + this.DecimalNum.ToString() ";";
 }
}

And here some sample code on how to use the Object - Relational converter:

TestObject a = new TestObject();
string sql = ObjectToSqlConverter.CreateInsertStatement(a);
sql = ObjectToSqlConverter.CreateUpdateStatement(a);
sql = ObjectToSqlConverter.CreateDeleteStatement(a);

You can download the whole project and a small sample from here.


If you have suggestions or you found an error feel free to write a comment and tell me about them.
I give you this code “as is” without any warranty that it works or that it does not cause problems or delete/modify important data. Use it at your own risk in your private and commercial projects and feel free to modify it.


Related links:
SQL Zoo (provides SQL statements for actual DBMSes)
Reserved words in standard SQL
Microsoft SQL Server datatypes and corresponding .NET datatypes