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...