ComboBox SelectedValueChanging Event

Introduction

For some reason, a standard WinForm ComboBox does not contain SelectedValueChanging event, which may be useful if you're requested to intercept a change or cancel it.

After searching for a possible solution on the web, I've found a nice example and adopted it with slight modifications.


SelectedValueChanging Event

Generally, subclassing common WinForm controls is a good idea, since it allows you to customize their appearance and behavior in a single place and affect all instances in the entire application.

In this case, we will subclass a ComboBox class and add the SelectedValueChanging event:

public partial class nessComboBox : ComboBox
{
  public event CancelEventHandler SelectedValueChanging;

  private object m_LastAcceptedSelectedValue;
  private bool m_IgnoreNullPreviousValueChanging = false;

  public nessComboBox()
  {
      InitializeComponent();
  }
    
  [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
  [Browsable(false)]
  public object LastAcceptedSelectedValue
  {
      get { return m_LastAcceptedSelectedValue; }
      private set { m_LastAcceptedSelectedValue = value; }
  }

  [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
  [Browsable(false)]
  public bool IgnoreNullPreviousValueChanging
  {
      get { return m_IgnoreNullPreviousValueChanging; }
      set { m_IgnoreNullPreviousValueChanging = value; }
  }

  protected void OnSelectedValueChanging(CancelEventArgs e)
  {
      if (SelectedValueChanging != null)
          SelectedValueChanging(this, e);
  }

  protected override void OnSelectedValueChanged(EventArgs e)
  {
      if (SelectedValueChanging != null)
      {
          if ((!m_IgnoreNullPreviousValueChanging ||
               LastAcceptedSelectedValue != null) &&
               (LastAcceptedSelectedValue ?? string.Empty).ToString()
               !=  (SelectedValue ?? string.Empty).ToString())
          {
              CancelEventArgs cancelEventArgs = new CancelEventArgs();
              OnSelectedValueChanging(cancelEventArgs);

              if (!cancelEventArgs.Cancel)
              {
                  LastAcceptedSelectedValue = SelectedValue;
                  base.OnSelectedValueChanged(e);
              }
              else
                  SelectedValue = LastAcceptedSelectedValue;
          }
          else if (m_IgnoreNullPreviousValueChanging &&
                      LastAcceptedSelectedValue == null
                      && SelectedValue != null)
          {
              LastAcceptedSelectedValue = SelectedValue;
          }
      }
      else
      {
          base.OnSelectedValueChanged(e);
      }
  }
}

As you can see, the overrided OnSelectedValueChanged method performs all required logic: checks if there are subscribers to the new event and raises it before calling of the OnSelectedValueChanged method.

The variable m_IgnoreNullPreviousValueChanging may be used to ignore the initial selection change from null to some specific value in case of data-binded combo.

That's it,
Mark.

WCF Data Services

Introduction

WCF technology provides several types of services sharing the same undelying infrastructure and we can choose the appropriate service type based on our needs:

In this post, I would like to make a quick overview of WCF Data Services.

According to MSDN, WCF Data Services (formerly known as "ADO.NET Data Services") is a component of the .NET Framework that enables you to create services that use the Open Data Protocol (OData) to expose and consume data over the Web or intranet by using the semantics of representational state transfer (REST).

The main advantage of using WCF Data Services is that it allows easy access to data from any client that supports OData.

Visual Studio makes it easy to create OData service by utilizing an ADO.NET Entity Framework data model.

The following example is based on WCF Data Service Quickstart and built with Visual Studio 2010.

WCF Data Services Quick Start

The example is divided into four steps:

1. Creating a simple ASP.NET application
2. Defining a data model based on the well-known Northwind database by using the Entity Framework 4
3. Adding the data service to to the web application
4. Creating a WPF client that consumes the service

Let's start with the ASP.NET application:

File--->Project--->ASP.NET Web Application

Name it NorthwindService.

Next step is creating the corresponding data model:

Right-click the name of the ASP.NET project--->Add New Item--->ADO.NET Entity Data Model

For the name of the data model, type Northwind.edmx

Third step is creating the data service:

Right-click the name of the ASP.NET project--->Add New Item--->WCF Data Service

For the name of the service, type Northwind

After completing of these three steps, your project might look like this:

In order to enable access to our data service, we need to grant rights to the particular enities within the model:

public static void InitializeService(DataServiceConfiguration config)
{
    config.SetEntitySetAccessRule("Orders", EntitySetRights.AllRead
      | EntitySetRights.WriteMerge
      | EntitySetRights.WriteReplace);

    config.SetEntitySetAccessRule("Order_Details", EntitySetRights.AllRead
      | EntitySetRights.AllWrite);

    config.SetEntitySetAccessRule("Customers", EntitySetRights.AllRead);

}

Our final step will be creating a simple WPF client for consuming and modifying the model data:

In Solution Explorer, right-click the solution,
click Add--->New Project--->WPF Application.

Enter NorthwindClient for the project name.
Replace the existing code in MainWindow.xaml with this code:

<Window x:Class="MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="Northwind Orders" Height="335" Width="425"
Name="OrdersWindow" Loaded="Window1_Loaded">
<Grid Name="orderItemsGrid">
<ComboBox DisplayMemberPath="OrderID" ItemsSource="{Binding}"
         IsSynchronizedWithCurrentItem="true"
         Height="23" Margin="92,12,198,0" Name="comboBoxOrder" VerticalAlignment="Top"/>
<DataGrid ItemsSource="{Binding Path=Order_Details}"
         CanUserAddRows="False" CanUserDeleteRows="False"
         Name="orderItemsDataGrid" Margin="34,46,34,50"
         AutoGenerateColumns="False">
   <DataGrid.Columns>
       <DataGridTextColumn  Header="Product" Binding="{Binding ProductID, Mode=OneWay}" />
       <DataGridTextColumn  Header="Quantity" Binding="{Binding Quantity, Mode=TwoWay}" />
       <DataGridTextColumn  Header="Price" Binding="{Binding UnitPrice, Mode=TwoWay}" />
       <DataGridTextColumn  Header="Discount" Binding="{Binding Discount, Mode=TwoWay}" />       
   </DataGrid.Columns>
</DataGrid>
<Label Height="28" Margin="34,12,0,0" Name="orderLabel" VerticalAlignment="Top"
      HorizontalAlignment="Left" Width="65">Order:</Label>
<StackPanel Name="Buttons" Orientation="Horizontal" HorizontalAlignment="Right"
           Height="40" Margin="0,257,22,0">
   <Button Height="23" HorizontalAlignment="Right" Margin="0,0,12,12"
       Name="buttonSave" VerticalAlignment="Bottom" Width="75"
           Click="buttonSaveChanges_Click">Save Changes
   </Button>
   <Button Height="23" Margin="0,0,12,12"
       Name="buttonClose" VerticalAlignment="Bottom" Width="75"
           Click="buttonClose_Click">Close</Button>
</StackPanel>
</Grid>
</Window>

It will give the following look to our client:



We need to add a data service reference to the client project:

Right-Click the project--->Add Reference--->Discover
In the Namespace text box, type Northwind

The only thing left is to to access the service data and we're done.
Copy this code into MainWindow.xaml.cs:

private NorthwindEntities context;
private string customerId = "ALFKI";

// Replace the host server and port number with the values
// for the test server hosting your Northwind data service instance.
private Uri svcUri = new Uri("http://localhost:12345/Northwind.svc");

private void Window1_Loaded(object sender, RoutedEventArgs e)
{
 try
 {
     // Instantiate the DataServiceContext.
     context = new NorthwindEntities(svcUri);

     // Define a LINQ query that returns Orders and
     // Order_Details for a specific customer.
     var ordersQuery = from o in context.Orders.Expand("Order_Details")
                       where o.Customer.CustomerID == customerId
                       select o;

     // Create an DataServiceCollection<t> based on
     // execution of the LINQ query for Orders.
     DataServiceCollection&lorder> customerOrders = new
         DataServiceCollection&lorder>(ordersQuery);

     // Make the DataServiceCollection<> the binding source for the Grid.
     this.orderItemsGrid.DataContext = customerOrders;
 }
 catch (Exception ex)
 {
     MessageBox.Show(ex.ToString());
 }
}

private void buttonSaveChanges_Click(object sender, RoutedEventArgs e)
{
 try
 {
     // Save changes made to objects tracked by the context.
     context.SaveChanges();
 }
 catch (DataServiceRequestException ex)
 {
     MessageBox.Show(ex.ToString());

 }
}
private void buttonClose_Click(object sender, RoutedEventArgs e)
{
 this.Close();
}

Now we can build and run the application.

As you can see, exposing and consuming a model data with WCF data services is a straightforward process, but that's not the issue, the more important thing that our WPF client can be easily replaced by other clients located in a totally different environment with no change of the service itself.

The runnable code for this tutorial could be downloaded here.

This is it,

Mark..

Custom Binding with INotifyPropertyChanged Interface

Introduction

In this post I would like to show a simple example of using INotifyPropertyChanged interface for binding a custom object properties to WinForm controls.

According to Microsoft's documentation INotifyPropertyChanged interface is used to notify clients about properties value changes.

The underneath idea is very simple: implementing this interface, forces raising PropertyChange event, which in-turn notifies client that binded property has changed.

The Example

Let's start with building a simple BankAccount class :

public class BankAccount : INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;

        private decimal m_Balance;
        private decimal m_CreditLimit;

        public BankAccount(decimal initialBalance)
        {
            this.m_Balance = initialBalance;
            this.m_CreditLimit = initialBalance * 2;
        }

        public decimal Balance
        {
            get { return m_Balance; }
        }

        public decimal CreditLimit
        {
            get { return m_CreditLimit; }
        }

        public void Withdrawal(decimal sum)
        {
            if (sum <= m_Balance) 
            { 
                m_Balance -= sum; 
                if (PropertyChanged != null) 
                { 
                    PropertyChanged(this, 
                       new PropertyChangedEventArgs("Balance")); 
                } 
            }
        }

        public void Deposit(decimal sum)
        {
            if (sum > 0)
            {
                m_Balance += sum;
                if (PropertyChanged != null)
                {
                    PropertyChanged(this, 
                       new PropertyChangedEventArgs("Balance"));
                }

                if(m_Balance >= m_CreditLimit)
                {
                    m_CreditLimit = m_Balance * 2;
                    if (PropertyChanged != null)
                    {
                        PropertyChanged(this, 
                          new PropertyChangedEventArgs("CreditLimit"));
                    }
                }
            }
        }

    }

The class contains two properties "Balance" and "CreditLimit" which are going to be binded to our simple UI elements:

Here is the code demonstrating how we actually bind it:

public partial class MainForm : Form
{
    private BankAccount m_Account;

    public MainForm()
    {
       InitializeComponent();
       m_Account = new BankAccount(1000);

       BindControls();
    }

    private void BindControls()
    {
        txtBalance.DataBindings.Add("Text",
                               m_Account,
                               "Balance",
                               true,
                               DataSourceUpdateMode.OnPropertyChanged);

       txtCreditLimit.DataBindings.Add("Text",
                                  m_Account,
                                  "CreditLimit",
                                  true,
                                  DataSourceUpdateMode.OnPropertyChanged);
    } 

    private void btnWithdrawal_Click(object sender, EventArgs e)
    {
        m_Account.Withdrawal(decimal.Parse(txtSum.Text));
    }

    private void btnDeposit_Click(object sender, EventArgs e)
    {
        m_Account.Deposit(decimal.Parse(txtSum.Text));
    }
 }

Now, the binded textboxes will automatically reflect changes of "Balance" and "CreditLimit" properties after calling Withdrawal/Deposit methods:

The working example could be downloaded here.

This is it,

Mark

Parallel Loops with .NET Framework 4

Introduction

Parallel computing draws a lot of attention at these days, because of amazing pace in which multi-core computers become widely available for industrial and personal use.

Developing applications that can take advantage of this computitional power, requires revision of the existing practices and design patterns, since those practices were born in the sequential world and do not solve similar

We all know that writting paraller applications is difficult and painful mission, not only because of well-known issues such as deadlocks and race-conditions, but simply because human beings have been always struggling to think in a parallel way.

That's why, existing of well-designed patterns for parallel programming is so crucial.

As a C# developer, I've started looking for the parallel programming resources by using .NET framework 4 , and found an excellent paper by Stephen Toub "Patterns of Parallel Programming".

The paper contains in-depth tour in .NET framework 4 for parallel programming.

In this post I would like to show one of the examples from the mentioned above paper :"Parallel Loops".

Parallel Loops

As you can guess, parallel loops allow running independent actions in parallel.

Loops are the most common control stuctures that enable the application to repeatedly execute some set of instructions.

We can use such loop when the statements within loop body have a few or no dependencies.

Creating Manual Parallel For

Let's start with the example of performing parallel "For" manually:

public static void MyParallelFor(int inclusiveLowerBound, int exclusiveUpperBound, Action body)
{
// Determine the number of iterations to be processed, the number of
// cores to use, and the approximate number of iterations to process
// in each thread.
int size = exclusiveUpperBound - inclusiveLowerBound;
int numProcs = Environment.ProcessorCount;
int range = size / numProcs;
// Use a thread for each partition. Create them all,
// start them all, wait on them all.
var threads = new List(numProcs);
for (int p = 0; p <>
{
int start = p * range + inclusiveLowerBound;
int end = (p == numProcs - 1) ?
exclusiveUpperBound : start + range;
threads.Add(new Thread(() =>
{
for (int i = start; i <>
 body(i);
}));
}
foreach (var thread in threads) thread.Start();
foreach (var thread in threads) thread.Join();
}

The main drawback of this solution is relative high cost paid for creating and destroying each thread.
We can improve it by utilizing pools of threads:

public static void MyParallelFor(
int inclusiveLowerBound, int exclusiveUpperBound, Action body)
{
// Determine the number of iterations to be processed, the number of
// cores to use, and the approximate number of iterations to process in
// each thread.
int size = exclusiveUpperBound - inclusiveLowerBound;
int numProcs = Environment.ProcessorCount;
int range = size / numProcs;
// Keep track of the number of threads remaining to complete.
int remaining = numProcs;
using (ManualResetEvent mre = new ManualResetEvent(false))
{
// Create each of the threads.
for (int p = 0; p <>
{
int start = p * range + inclusiveLowerBound;
int end = (p == numProcs - 1) ?
exclusiveUpperBound : start + range;
ThreadPool.QueueUserWorkItem(delegate
{
 for (int i = start; i <>
  body(i);
 if (Interlocked.Decrement(ref remaining) == 0)
  mre.Set();
});
}
// Wait for all threads to complete.
mre.WaitOne();
}
}

PARALLEL.FOR

The new "Parallel" class has been added to the .NET Framework 4 library. The class provides methods for performing parallel loops and regions, one of them is "For". In his paper Stephen gives a very good example of using Parallel.For in order to trace rays of light. Following code snippets demonstrate the sequential and parallel variations of this problem:

void RenderSequential(Scene scene, Int32[] rgb)
{
Camera camera = scene.Camera;
for (int y = 0; y <>
{
int stride = y * screenWidth;
for (int x = 0; x <>
{
Color color = TraceRay(
new Ray(camera.Pos, GetPoint(x, y, camera)), scene, 0);
rgb[x + stride] = color.ToInt32();
}
}
}

void RenderParallel(Scene scene, Int32[] rgb)
{
Camera camera = scene.Camera;
Parallel.For(0, screenHeight, y =>
{
int stride = y * screenWidth;
for (int x = 0; x <>
{
Color color = TraceRay(
new Ray(camera.Pos, GetPoint(x, y, camera)), scene, 0);
rgb[x + stride] = color.ToInt32();
}
});
}

We can notice that only difference between the implementations is replacing of regular for by

Parallel.For

That's it for now.

I'll continue extracting and adding such small articles to give a "gustations" of the wonderful stuff from Stephen's book.

Mark.

Dependency Injection

Introduction

Dependency Injection (DI) is a design pattern which allows us to "inject" the concrete object into a class instead of having this initialization within the class itself.

One common way to achive similar result is using "Factory Method" design pattern.

"Factory Method" is a method, responsible for creating and returning of an instance of a class.

Usually a variable is passed to the "Factory" method to signalize which specific subclass should be returned.

The major drawbacks of the "Factory Method" are:

1. The method implementation is too specific and therefore cannot be used across other applications.

2. The creation options are hardcoded into "Factory" implementation, which means that all dependencies are known at compile time and cannot be dynamically extented without re-compiling.

3. The class which calls "Factory" method should know which subclass to create (sounds like dependency itself).

Just to make it clear: I'm not saying that using factories is bad. There are many applications in which using factories is valuable and sufficient, but if you need more flexible solution, that's when DI enters the picture.

Dependency Injection

DI is implemented by using so-called containers - configurable components that host the abstraction, create the concrete instance variables and inject it into appropriate classes.

There are many DI providers available on the market, here is the partial list:

1. Castle Windsor

2. Spring.NET

3. Unity

In the following example we will use the Unity 2.0 components which is part of Enterprise Library 5.0.

The Example

Lets start with creating of a simple interface IDatabase containing a single method "Save"

interface IDataBase

{

void Save();

}

Now, we'll add two classes that implement IDatabase:

class OracleDatabase : IDataBase

{

public OracleDatabase()

{

Console.WriteLine("OracleDatabase Constructor");

}

public void Save()

{

Console.WriteLine("Save with Oracle");

}

}

And

public class SqlDatabase : IDataBase

{

public SqlDatabase()

{

Console.WriteLine("SqlDatabase Constructor");

}

public void Save()

{

Console.WriteLine("Save with SQL Server");

}

}

The "Customer" class will be the main class which uses the services of IDatabase component:

class Customer

{

private IDataBase m_Database = null;

[Dependency]

public IDataBase Database

{

get { return m_Database; }

set { m_Database = value; }

}

public void Save()

{

m_Database.Save();

}

}

The "Dependency" attribute is part of the Unity application block and it's simply a gateway used by the Unity container for the concrete type injection.

The mapping between the abstract variable and the concrete type may be performed through the code or by adding a special configuration section to App.config file.

The final usage is quite simple:

IUnityContainer container = new UnityContainer();

UnityConfigurationSection configSection = (UnityConfigurationSection)ConfigurationManager.GetSection("unity");

configSection.Configure(container);

Customer customer = container.Resolve();

customer.Save();

The complete example could be downloaded here

That's it,

Mark.

Propagator Design Pattern

Introduction

I've encountered this design pattern while I was looking for an existing solution to the problem of propagating an object property to the dependant objects.
Actually the "Propagator" is based on well-known "Observer" design pattern
in which an object known as the Subject holds a list of Observers and notifies them automatically of any state changes.

The difference is that "Propagator" makes it possible to construct each object to be "Subject" and "Observer" interchangeably.

The Propagator

The idea behind the pattern is to establish a network of dependent objects and when a change occurs, push it through propagators to all descendants.

Let's give a brief explanation of each actor in the diagram above:

1. IPropagator interface contains methods for adding/removing dependent objects and processing state changes.
2. Propagator class, implements the IPropagator interface and contains
   the "AddHandler" method used for managing a list of delegates to be invoked when a change takes place.
3. StateChange class represents the change itself.
4. StateChangeOptions enum allows specifying a change propagation options.

The final usage is quite simple - we just need to call "Process" method of corresponding propagator to have all objects synchronized with the recent change.

The detailed description of Propagator design pattern along with a nice code example could be found in this post by Martijn Boeker.

That's it,

Mark.

How to Compare Two Spreadsheets in Excel

This time a contribution from Yoav Ezer the CEO of Cogniview.

Enjoy,
Diego

Being Microsoft Excel experts, the Cogniview team are often being emailed with tricky spreadsheet questions. A particularly interesting puzzle came through recently that we thought we would share with you here:

"Each month I have to compare two spreadsheets of products, each with the same header rows and same number of column. The number of rows are different each time.

Is it possible using Excel to compare these two lists and show only the differences? I would love it if we could create a macro to solve this??"

Seems like a cool challenge, and I would love to say I had a hand in coming up with the solution but I am afraid it was the programming boffins at Cogniview who solved it!

The Solution

Several ideas were suggested and considered, including going through each sheet line by line using VB code. The problem always came when either or both sheets were really long and contained tons of data. Even on the fastest machine it could be seen to grind to a halt.

The answer was to use Excel's built-in function RemoveDuplicates to handle this. Unfortunately that makes it a solution not available to users of older versions.

We need two source spreadsheets to compare, then a target spreadsheet for the results, as shown below.

Basically, this process requires our macro to perform two steps.

Step one, we copy the first sheet's data to a new sheet and put the second's data after it, then we use the RemoveDuplicates function which removes the items from the second sheet that appear in the first.

In the second step we do the same again in reverse order - copy the second sheet's data and then the first, and use RemoveDuplicates again. The left over data in each case is the difference we need to display.

All we need to do then is present the results.

For each row we state where the data was found.

The Macro

Most of the first routine sets everything up, copying the range of data as described above. The last two lines call our custom function LeaveOnlyDifferent.

Sub CompareSheets()
' Merge into the current sheet
Dim sheetResult As Worksheet, Sheet1 As Worksheet, Sheet2 As Worksheet
Set sheetResult = ActiveSheet

' Clean merge sheet so we can work with it
If (MsgBox("This will erase all the data on the current sheet." & vbCrLf & "Do you wish to continue?", vbYesNo Or vbQuestion) <> vbYes) Then
Exit Sub
End If
sheetResult.UsedRange.Delete

' Ask for two sheets to compare
SelectSheet.SheetNames.Clear
For i = 1 To Worksheets.Count
SelectSheet.SheetNames.AddItem Worksheets(i).Name
Next
sFirstSheet = AskSheet
If (sFirstSheet = "") Then
Exit Sub
End If
Set Sheet1 = Sheets(sFirstSheet)
SelectSheetStart:
sSecondSheet = AskSheet
If (sSecondSheet = "") Then
Exit Sub
End If
If (sSecondSheet = sFirstSheet) Then
MsgBox "Please select different first and second sheets"
GoTo SelectSheetStart
End If
Set Sheet2 = Sheets(sSecondSheet)

' Find the column to use for marking
Dim sFromColumn As String
Dim nLastColumn As Integer
sTemp = Sheet1.UsedRange.Offset(1, 1).Address(True, True, 1)
sTemp = Mid(sTemp, InStr(sTemp, ":") + 1)
sFromColumn = Mid(sTemp, 2, InStrRev(sTemp, "$") - 2)
nLastColumn = Sheet1.UsedRange.Columns.Count

' Copy header
Sheet1.Range("A1:" & sFromColumn & "1").Copy sheetResult.Range("A1")
sheetResult.Range(sFromColumn & "1").Formula = "From Sheet"

' Compare stuff
LeaveOnlyDifferent Sheet2, Sheet1, sheetResult, sFromColumn, nLastColumn, 2
LeaveOnlyDifferent Sheet1, Sheet2, sheetResult, sFromColumn, nLastColumn, sheetResult.UsedRange.Rows.Count
End Sub

LeaveOnlyDifferent Function

This function is where the real solution lies, comparing and presenting the result. It accepts the sheets to compare and the destination, the columns and the first row. After copying the cells and removing duplicates it copies the result to the top of the destination sheet.

Function LeaveOnlyDifferent(Sheet1 As Worksheet, Sheet2 As Worksheet, sheetResult As Worksheet, sFromColumn As String, nLastColumn As Integer, nFirstCompareRow As Integer)
' Copy first sheet data
nFirstDataRowCount = Sheet1.UsedRange.Rows.Count - 1
Sheet1.Range("A2:" & sFromColumn & (nFirstDataRowCount + 1)).Copy sheetResult.Range("A" & nFirstCompareRow)

' Copy second sheet data below the first
nStartOfSecondData = nFirstCompareRow + nFirstDataRowCount
Sheet2.Range("A2:" & sFromColumn & Sheet2.UsedRange.Rows.Count).Copy sheetResult.Range("A" & nStartOfSecondData)

' Remove duplicates
Dim arColumns() As Variant
ReDim arColumns(0 To nLastColumn - 1)
For i = 0 To nLastColumn - 1
arColumns(i) = CVar(i + 1)
Next
sheetResult.Range("A" & nFirstCompareRow & ":" & sFromColumn & sheetResult.UsedRange.Rows.Count).RemoveDuplicates arColumns, xlYes

' Mark the different data as coming from the proper sheet
nDiffRowCount = sheetResult.UsedRange.Rows.Count - nStartOfSecondData + 1
If (nDiffRowCount > 0) Then
sheetResult.Range(sFromColumn & nStartOfSecondData & ":" & sFromColumn & sheetResult.UsedRange.Rows.Count).Formula = "Found only on " & Sheet2.Name
' Copy it to the top
sheetResult.Range("A" & nStartOfSecondData & ":" & sFromColumn & sheetResult.UsedRange.Rows.Count).Copy sheetResult.Range("A" & nFirstCompareRow)
End If

' Delete all the rest
sheetResult.Range("A" & (nFirstCompareRow + nDiffRowCount) & ":" & sFromColumn & sheetResult.UsedRange.Rows.Count).Delete
End Function

Over to You

As mentioned earlier, there are many ways to solve this particular Excel challenge - how would you solve it? Could you see this solution as being useful? Please share your thoughts and experiences with us on Facebook or Twitter.

About the author

This article was written by Yoav Ezer, the CEO of a company that creates PDF to XLS conversion software, called Cogniview.

Prior to that, cwas the CEO of Nocturnus, a technology-centered software solution company.

Displaying images from a database - MVC style

Exploring MVC 2 I started a small project that included displaying images from my database, I know this is quite the basics, but I was impressed of the simplicity & elegant style this simple task gets when using MVC.

In the database I have a table that includes images contained in varbinary(max) column.

CREATE TABLE [dbo].[Album](
[AlbumId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Picture] [varbinary](max) NULL,
...
...
CONSTRAINT [PK_Album] PRIMARY KEY CLUSTERED 
(
[AlbumId] ASC
)

Next in my data access layer I've added a linq query to retrieve an image by albumId.

public byte[] GetAlbumCover(int albumId)
{
var q = from album in _db.Album
where album.AlbumId == albumId
select album.Picture;

byte[] cover = q.First();

return cover;
}

As for the actual MVC...

The controller:

public ActionResult RetrieveImage(int id)
{
byte[] cover = _BL.GetAlbumCover(id);

if (cover != null)
return File(cover, "image/jpg");

return null;
}

The view:

<% foreach (var item in Model) { %>

<tr>
<td>
<%: item.ArtistName %>
</td>
<td>
<%: item.AlbumName %>
</td>
<td>
<img src="/MusicInfo/RetrieveImage/<%:item.AlbumId%>"  alt="" height=100 width=100/>
</td>
<td>
<%: Html.ActionLink("Edit", "Edit", new {id=item.AlbumId}) %>
</td>
</tr>

<% } %>

The link of the picture points to the controller (MusicInfo), the method inside the controller (RetrieveImage) & the specific image id to be able to retrieve it.

That's it...simple yet elegant.

Till next time
Diego

Import/Export DataSet to Excel file with ADO.NET

Often it is necessary to Import/Export DataSet to external databases or external file. Here I will present how Import/Export DataSet to excel file (*.xls or *.xlsx) with ADO.NET.

The most significant advantage of this method is that excel installation is not necessary on running station.

You will first need a connection string to connect to the Excel Workbook, which would be the following:

Excel 8 (Excel 1997):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=YES";

Excel 12 (Excel 2007):

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\MyExcel.xlsx;Extended Properties="Excel 12.0;HDR=YES";

Implementation of Import:

public static DataSet ImportFromExcel(string connectionString, string fileName)

{

DataSet returnDS = new DataSet();

string excelConnectionString = string.Format(connectionString, fileName);

string[] excelSheets = getExcelSheetNames(excelConnectionString);

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

DbDataAdapter adapter = factory.CreateDataAdapter();

using (DbConnection connection = factory.CreateConnection())

{

connection.ConnectionString = excelConnectionString;

foreach (string excelSheet in excelSheets)

{

DbCommand selectCommand = factory.CreateCommand();

selectCommand.CommandText = string.Format("SELECT * FROM [{0}]", excelSheet);

selectCommand.Connection = connection;

adapter.SelectCommand = selectCommand;

DataTable dt = new DataTable(excelSheet.Remove(excelSheet.Length - 1, 1));

adapter.Fill(dt);

returnDS.Tables.Add(dt);

}

}

return returnDS;

}

Implementation of Export:

public static void ExportToExcel(DataSet dataSet, string connectionString, string fileName)

{

if (dataSet != null && dataSet.Tables.Count > 0)

{

using (OleDbConnection connection = new OleDbConnection(string.Format(connectionString, fileName)))

{

OleDbCommand command = null;

connection.Open();

foreach (DataTable dt in dataSet.Tables)

{

command = new OleDbCommand(getCreateTableCommand(dt), connection);

command.ExecuteNonQuery();

for (int rowIndex = 0; rowIndex <>

{

command = new OleDbCommand(getInsertCommand(dt, rowIndex), connection);

command.ExecuteNonQuery();

}

}

}

}

}

How to use:

Lets define 2 variables:

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES""";

string fileName = @"C:\MyExcel.xls";

Import:

DataSet ds = ImportFromExcel(connectionString, fileName);

Export:

ExportToExcel(ds, connectionString, fileName);

Source Code: ExcelReaderWriter.cs

SQL Server performance - querying by date

In every application that includes a database we will probably need to query some table by some date column -

SELECT Id, MyDate
FROM #CompareDates
WHERE MyDate = GETDATE()

Although this is a very simple syntax, I will show you how it can be a performance hit.

We'll start by filling a table with some random dates(and one row of a specific date just to make sure it exists) and add a clustered index on this column.

The data we are looking for is all the rows with today's date.

DECLARE @myNow DATETIME
SET @myNow = '2010-09-06 22:47'

SELECT *
FROM #CompareDates
WHERE MyDate = @myNow

This query will work very fast, but, because the column 'MyDate' contains time and not just date - the result won't be as expected, only rows with the exact date & time will appear.

We can see in the execution plan that it uses 'clustered index seek' and results with one row which is the row we added manually with exact time.



We can try using DATEDIFF to eliminate the time in the column:

SELECT *
FROM #CompareDates
WHERE DATEDIFF(day,MyDate,@myNow)=0

This will get the exact result, but, using a function over the 'MyDate' column causes the sql server to scan the entire index!!




If the column does not contain time, we can eliminate the time in the '@myNow' parameter:

DECLARE @myNowNoTime DATETIME

SET @myNowNoTime = dateadd(dd,datediff(dd,0,GETDATE()),0)

SELECT *
FROM #CompareDates
WHERE MyDate = @myNowNoTime

This will also perform well as the 1st query, but again...what if 'myDate' column contains time??

The solution is to avoid doing functions (like DATEDIFF, DATEADD or any user defined function) over 'MyDate' column.

The solution is very simple - date range.
First I've created two functions that one returns the begining of the day (time will be '00:00:00') and the other the end of the day (time will be 23:59:59:997)

CREATE FUNCTION [dbo].[fnGetBeginOfDay]
(@myDate DATETIME)
RETURNS DATETIME
BEGIN
RETURN CAST(FLOOR(CAST(@myDate AS DECIMAL(12, 5))) AS DATETIME)
END
GO

CREATE FUNCTION [dbo].[fnGetEndOfDay]
(@myDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN DATEADD(ms, -3, DATEADD(day, 1, (dbo.fnGetBeginOfDay(@myDate))))
END
GO

Than the query will look like this:

DECLARE @beginOfDay DATETIME, @endOfDay DATETIME
SET @beginOfDay = dbo.fnGetBeginOfDay(GETDATE())
SET @endOfDay = dbo.fnGetEndOfDay(GETDATE())

SELECT *
FROM #CompareDates with (index(idx_MyDate))
WHERE MyDate BETWEEN @beginOfDay AND @endOfDay

And the execution plan looks like this, back to the wonderfull 'index seek' and returns the expected amount of rows:



Additional facts:
1. Testing the same queries with NONCLUSTERED index shows that the date range solution is as bad as DATEDIFF - both result with table scan

2. Adding to (1) a table hint (with(index(idx_MyDate))) to both queries shows that while datediff uses 'index scan', date range uses 'index seek' (which is obviously better).

Conclusions:

1. When querying using a date column which is included in an index - use 'date range' solution.

2. If the index is nonclustered - check if a table hint makes the difference.


Till next time....
Diego

Full sample test source code:

--fill table with random dates
CREATE TABLE #CompareDates (Id int identity(1,1), MyDate DATETIME)

DECLARE @myNow DATETIME
SET @myNow = '2010-09-06 22:47'
DECLARE @i int, @myDate DATETIME
SET @i = 0

WHILE @i < 10000
BEGIN
--random date (10x to Ben Nadel for the useful & short function :-)
SET @myDate = CAST(CAST( @myNow AS INT ) -5 * RAND( CAST( CAST( NEWID() AS BINARY(8) ) AS INT ) )AS DATETIME)

INSERT INTO #CompareDates (MyDate)
SELECT @myDate
SET @i=@i+1

IF @i=500
INSERT INTO #CompareDates (MyDate) VALUES(@myNow)
END

CREATE CLUSTERED INDEX idx_MyDate ON #CompareDates(MyDate)

SELECT * FROM #CompareDates

-----TESTING:

--fast, looks for specific date including time, not quite we are looking for..
SELECT *
FROM #CompareDates
WHERE MyDate = @myNow

--exact results, scans entire index
SELECT *
FROM #CompareDates
WHERE DATEDIFF(day,MyDate,@myNow)=0

--if the column does not contain time, you can eliminate time in the parameter
DECLARE @myNowNoTime DATETIME
SET @myNowNoTime = dateadd(dd,datediff(dd,0,@myNow),0)

SELECT *
FROM #CompareDates
WHERE MyDate = @myNowNoTime

--if column contains time, use range
DECLARE @beginOfDay DATETIME, @endOfDay DATETIME
SET @beginOfDay = dbo.fnGetBeginOfDay(@myNow)
SET @endOfDay = dbo.fnGetEndOfDay(@myNow)

SELECT *
FROM #CompareDates with (index(idx_MyDate))
WHERE MyDate BETWEEN @beginOfDay AND @endOfDay

Scanning images using WIA and TWAIN

Introduction

The development of windows imaging applications requires choosing an API to communicate with scanner s or cameras. The most used APIs are WIA and TWAIN.

The WIA (Windows Image Acquisition) platform enables imaging/graphics applications to interact with imaging hardware and standardizes the interaction between different applications and scanners. This allows those different applications to talk to and interact with those different scanners without requiring the application writers and scanner manufactures to customize their application or drivers for each application-device combination. (http://msdn.microsoft.com/en-us/library/ms630368(VS.85).aspx)

TWAIN is a standard software protocol and applications programming interface (API) that regulates communication between software applications and imaging devices such as scanners and digital cameras. (http://www.twain.org/)

Differences:

1. WIA uses a common dialog for all devices while TWAIN uses a dialog created by the device manufacturer. Practically speaking, this almost always means that the TWAIN dialog will provide more options and advanced control over the device.

2. TWAIN allows you to use custom capabilities that the device manufacturer has created even though they don't exist in the TWAIN specifications.

3. In general, when a device supports both Twain and WIA, TWAIN is better for scanners and WIA is better for acquiring images from cameras and video devices.

4. TWAIN has three transfer modes (Native, Memory, File) and WIA only has two (Memory, File).

5. Most TWAIN sources save the settings of the previous scan while WIA does not.

6. TWAIN supports options for each page when scanning in duplex mode but WIA uses the same settings for both sides.

Problem

Usually, older scanners (drivers) are build for TWAIN and does not supported by WIA platform, moreover newer devices which created under Microsoft standard does not supported by TWAIN API.

Solution (Full Source Download)

In order to build scanning application that will communicate with both types of devices I combined those APIs under a uniform interface which will provide the easy access to both type of devices.

ScannerAdapterBase

public abstract class ScannerAdapterBase

{

protected List<Image> m_ImagesList = null;

protected ScannerDeviceData m_ScannerDeviceData = null;

// Initializes the Adapter

public abstract void InitAdapter(nessScanning control, IMessageFilter messageFilter, IntPtr handle);

// Selects scanning device, and returns the indicator that the device selected

// Returns the indicator that the device selected

public abstract bool SelectDevice();

// Acquires images from scanning device and fills ImagesLis

public abstract void AcquireImages(bool showUI);

…

}

Factory Method

public static ScannerAdapterBase GetScannerAdapter(nessScanning control, IMessageFilter messageFilter, IntPtr handle)

{

lock (locker)

{

bool isTwainDeviceSelected = false;

if (m_ScannerAdapterBase != null)

{

return m_ScannerAdapterBase;

}

try

{

//Checks WIA Devices

m_ScannerAdapterBase = new WiaScannerAdapter();

m_ScannerAdapterBase.InitAdapter(control, messageFilter, handle);

isWiaDeviceSelected = m_ScannerAdapterBase.SelectDevice();

if (isWiaDeviceSelected)

{

return m_ScannerAdapterBase;

}

//Checks TWAIN Devices

m_ScannerAdapterBase = new TwainScannerAdapter();

m_ScannerAdapterBase.InitAdapter(control, messageFilter, handle);

isTwainDeviceSelected = m_ScannerAdapterBase.SelectDevice();

if (isTwainDeviceSelected)

{

return m_ScannerAdapterBase;

}

}

catch (ScannerException ex)

{

throw ex;

}

return null;

}

}

Wia Adapter AcquireImages method

public override void AcquireImages(bool showUI)

{

CommonDialogClass wiaCommonDialog = new CommonDialogClass();

//Select Device

if (m_DeviceID == null)

{

Device device = null;

try

{

device = wiaCommonDialog.ShowSelectDevice(WiaDeviceType.ScannerDeviceType, false, false);

if (device != null)

{

m_DeviceID = device.DeviceID;

FillDeviceData(device);

}

else

{

return;

}

}

catch (COMException ex)

{

if ((WiaScannerError)ex.ErrorCode == WiaScannerError.ScannerNotAvailable)

{

return;

}

else

{

WiaScannerException se = BuildScannerException(device, ex);

throw se;

}

}

}

//Create DeviceManager

DeviceManager manager = new DeviceManagerClass();

Device WiaDev = null;

foreach (DeviceInfo info in manager.DeviceInfos)

{

if (info.DeviceID == m_DeviceID)

{

WIA.Properties infoprop = null;

infoprop = info.Properties;

//connect to scanner

WiaDev = info.Connect();

break;

}

}

//Get Scanning Properties

WIA.Item scanningItem = null;

WIA.Items items = null;

if (showUI)

{

items = wiaCommonDialog.ShowSelectItems(WiaDev, WiaImageIntent.TextIntent, WiaImageBias.MinimizeSize, false, true, false);

}

else

{

items = WiaDev.Items;

}

if (items != null && items.Count > 0)

{

scanningItem = items[1] as WIA.Item;

}

WIA.ImageFile imgFile = null;

WIA.Item item = null;

//Prepare ImagesList

if (m_ImagesList == null)

{

m_ImagesList = new List<Image>;

}

//Start Scan

while (HasMorePages(WiaDev))

{

item = scanningItem;

try

{

//Scan Image

imgFile = (ImageFile)wiaCommonDialog.ShowTransfer(item, ImageFormat.Jpeg.Guid.ToString("B")/* wiaFormatJPEG*/, false);

byte[] buffer = (byte[])imgFile.FileData.get_BinaryData();

MemoryStream ms = new MemoryStream(buffer);

m_ImagesList.Add(Image.FromStream(ms));

imgFile = null;

}

catch (COMException ex)

{

if ((WiaScannerError)ex.ErrorCode == WiaScannerError.PaperEmpty)

{

break;

}

else

{

WiaScannerException se = BuildScannerException(WiaDev, ex);

throw se;

}

}

catch (Exception ex)

{

WiaScannerException se = BuildScannerException(WiaDev, ex);

throw se;

}

finally

{

item = null;

}

}

}

Wia Adapter HasMorePages method

private bool HasMorePages(Device WiaDev)

{

try

{

bool hasMorePages = false;

//determine if there are any more pages waiting

Property documentHandlingSelect = null;

Property documentHandlingStatus = null;

foreach (Property prop in WiaDev.Properties)

{

if (prop.PropertyID == WIA_PROPERTIES.WIA_DPS_DOCUMENT_HANDLING_SELECT)

documentHandlingSelect = prop;

if (prop.PropertyID == WIA_PROPERTIES.WIA_DPS_DOCUMENT_HANDLING_STATUS)

documentHandlingStatus = prop;

}

if (documentHandlingSelect != null) //may not exist on flatbed scanner but required for feeder

{

//check for document feeder

if ((Convert.ToUInt32(documentHandlingSelect.get_Value()) & WIA_DPS_DOCUMENT_HANDLING_SELECT.FEEDER) != 0)

{

hasMorePages = ((Convert.ToUInt32(documentHandlingStatus.get_Value()) & WIA_DPS_DOCUMENT_HANDLING_STATUS.FEED_READY) != 0);

}

}

return hasMorePages;

}

catch (COMException ex)

{

WiaScannerException se = BuildScannerException(WiaDev, ex);

throw se;

}

}

TWAIN Adapter AcquireImages method

public ArrayList TransferPictures()

{

ArrayList pics = new ArrayList();

if (srcds.Id == IntPtr.Zero)

{

return pics;

}

TwRC rc;

IntPtr hbitmap = IntPtr.Zero;

TwPendingXfers pxfr = new TwPendingXfers();

do

{

pxfr.Count = 0;

hbitmap = IntPtr.Zero;

TwImageInfo iinf = new TwImageInfo();

rc = DSiinf(appid, srcds, TwDG.Image, TwDAT.ImageInfo, TwMSG.Get, iinf);

if (rc != TwRC.Success)

{

CloseSrc();

return pics;

}

rc = DSixfer(appid, srcds, TwDG.Image, TwDAT.ImageNativeXfer, TwMSG.Get, ref hbitmap);

if (rc != TwRC.XferDone)

{

CloseSrc();

return pics;

}

rc = DSpxfer(appid, srcds, TwDG.Control, TwDAT.PendingXfers, TwMSG.EndXfer, pxfr);

if (rc != TwRC.Success)

{

CloseSrc();

return pics;

}

pics.Add(hbitmap);

}

while (pxfr.Count != 0);

rc = DSpxfer(appid, srcds, TwDG.Control, TwDAT.PendingXfers, TwMSG.Reset, pxfr);

return pics;

}