Please Note that I have changed address
Go to
Baking Ways / Productive Bytes

Search This Blog


Sunday, May 23, 2010

How to Separate the DAL Layer and BLL Layer in a a C# application

Link to Class diagrma Layering
Link to Class Diagram Mail System.
NClass 2.0 or higher is needed to read the file.  NClass Website

In this article we are going to discuss how to separate the Data Access Layer (DAL) form the Business Logic Layer (BLL) using

1) The Abstract Factory Pattern
2) The Model Provider Pattern
3) Reflections

On the Class Diagram Mail System you will find many notes on how to separate the BLL from the DAL
I will first comment on the Class diagram you will find on the link. After I will develop an applicaton. This is a working in progress. As usual if you have any suggestion or improvement, just add a comment.

Parsing an XML file using C#

Link to the Code (VS 2008)

I will show you here two pieces of code in C# that will let you parse the Products.XML into a List object.
The first one makes use of the System.XML namespace, while the second use System.XML.Linq one.
The Linq code is pretty amazing, it is so simple I will not even comment it. I managed also to include a "where" clause to filter the output.

An additional note has to be done for the XMLReader methods class for the first example

xmlIn.ReadToDescendant("Product") == true  //This code move the cursor to the first "Product"

product.Code = xmlIn["Code"]; //Since we are already on the Product Element we can read its attribute with  the indexer

 xmlIn.ReadStartElement("Product"); // Checks that we are on Product Element and moves one raw ahead

product.Description = xmlIn.ReadElementContentAsString(); //Read the Content of the Element Description, and moves one raw ahead

product.Price = xmlIn.ReadElementContentAsDouble(); //Read the Content ofthe Element Price, and moves one raw ahead

xmlIn.ReadToNextSibling("Product") // Move the Cursor to the next product and return true if found

//Read The First Root Node
            if (xmlIn.ReadToDescendant("Product") == true) 
                    Product product = new Product();
                    product.Code = xmlIn["Code"];
                    product.Description = xmlIn.ReadElementContentAsString();
                    product.Price = xmlIn.ReadElementContentAsDouble();
                } while (xmlIn.ReadToNextSibling("Product"));



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WindowsFormsApplication1
    class Product
        public string Code { get; set; }
        public string Description { get; set; }
        public double Price { get; set; }

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.Xml.Linq;

namespace WindowsFormsApplication1
    public partial class Form1 : Form
        public Form1()

        private void button1_Click(object sender, EventArgs e)
            //Create the list
            List products = new List();

            //Create the path
            string path = @"..\..\Products.xml";

            //XmlReader Settings
            XmlReaderSettings settings = new XmlReaderSettings();
            settings.IgnoreComments = true;
            settings.IgnoreWhitespace = true;

            //XmlReader Objcet
            XmlReader xmlIn = XmlReader.Create(path, settings);

            //Read The First Root Node
            if (xmlIn.ReadToDescendant("Product") == true)
                    Product product = new Product();
                    product.Code = xmlIn["Code"];
                    product.Description = xmlIn.ReadElementContentAsString();
                    product.Price = xmlIn.ReadElementContentAsDouble();
                } while (xmlIn.ReadToNextSibling("Product"));



        private void button2_Click(object sender, EventArgs e)
            //Create the path
            string path = @"..\..\Products.xml";
            //Create the xmlDoc object
            XDocument xmlDoc = XDocument.Load(path);

            List products = (
                                from product in xmlDoc.Descendants("Product")
                                where product.Attribute("Code").Value == "A4CS"
                                select new Product
                                    Code = product.Attribute("Code").Value,
                                    Description = product.Element("Description").Value,
                                    Price = Convert.ToDouble(product.Element("Price").Value),

            foreach (Product product in products)



Wednesday, May 19, 2010

Developing COM exposed classes in C#

Press  here to download the template. will be downloaded.
For some code example see this  post

Here you can find a series of notes I took as reminders to develpod a COM Class in C#.

I will put it here just as a reference, I hope I will have more time in the future to show you a full example.
You can also find here a C# template you can use to start develop C# COM exposed class. You can delete all the comments out of it. I just add them there for my reference.

To install the template in your VS2008 you need first to find out where they are stored.
To do this, go to File, Export Template. After a few click you should find out where your exported templates
are stored
On my PC for example, they are store here
C:\Users\PP\Documents\Visual Studio 2008\My Exported Templates\

Once you know this path, just copy the in the following directory.
You must copy the .zip file. Do not unzip them.

C:\Users\PP\Documents\Visual Studio 2008\Templates\ItemTemplates\Visual C#.

If things go well (and it took me sometime to figure out how to do it) you should have a new template in your
Add New Item, Visual C# Item

 C# COM Rules:

 To expose properties and methods to COM, you must declare them on the class
 interface and mark them with a DispId attribute, and implement them in the class.
 The order in which the members are declared in the interface is the
 order used for the COM vtable.
    [DispId(1)] void Init(string userid , string password);
    [DispId(2)] bool ExecuteSelectCommand(string selCommand);

1)The Class must be public
2) Properties, methods and events that need to be exposed:
    a) must be public
    b) Properties and methods must be declared in the class interface. The class must implement this interface
    c) Event must be declared on the Event Interface. The class should not implement this interface
3)  Other Class member that are not declared in the class interface, are not visible to COM but are
      visible to .NET classes
4)  The class must have a default parametereless constructor. Always write is down even if is empty.
     The class can have its constructors and methods overloaded.
5) COM does not support inheritance, only interface implementation
    So you can't  do Class Employees : List.
    This will not be exposed to COM
 6) C# can pass to COM zero based array using "ref" in the method signature.
     Without ref, the method will not work!
     ex:    double[] Compute(ref double[] a, double b)
     in VB 6.0 this will be like Dim a() as double
 7) Variant can be passed as type "object"
 8) Enum can be exposed.  Remeber you need to generate a unique Guid using
     C:\Programmi\Microsoft Visual Studio 9.0\Common7\Tools\guidgen.exe Registry format
    public enum MyEnum{
          [DispId(1)] A = 0,
          [DispId(1)] B = 1,
          [DispId(1)] C = 2

Please not that "public enum MyEnum : long" will compile but will cause method that have MyEnum
in their signature not to work at all
9) Collections.
Collections can be implemented using encapsulation and delegation.
We can encapsulate a SortedList and delegate to it the implementation of
Count, Item, Remove, Add methods defined in the COM Interface.
public interface INetSortedList : IEnumerable

     [DispId(-4)] new IEnumerator GetEnumerator();  //Iterator
     [DispId(1)]  void Add(object key, object value);
     [DispId(2)]  int Count{ get; }
     [DispId(3)]  void Remove(object key);
     [DispId(0)]  object this[object key] {get ; set; } //Default Property

 //Events Interface
public interface INetSortedListEvents
 //Class Implement the Class Interface
public class NetSortedList : INetSortedList
     private SortedList _sortedList;
     public NetSortedList() {  _sortedList = new SortedList();  }
     public IEnumerator GetEnumerator() { return _sortedList.GetEnumerator(); }
     public void Add(object key, object value) {  _sortedList.Add(key, value);}
     public int Count {  get { return _sortedList.Count; }  }
     public void Remove(object key) { _sortedList.Remove(key); }
     public object this[object key] {
         get { return _sortedList[key]; }
         set { _sortedList[key] = value; }
Please note that

 a) the indexer   "Employee this[int index] { get; set; }"    will be seen in VB6.0 as
    a default Item property so that you can do:  list(1) or list.Item(1).
    Its DispId must be set to = [DispId(0)] to work as default Property
b) System.Collections.IEnumerator GetEnumerator(); allows for the "for each" loop in VB 6.0. It

    must have a   [DispId(-4)]
    You cannot use in the interface a Generic enumerator like "List.Enumerator"  which is of type

    System.Collections.Generic.List.Enumerator. You must use the non generic type 
    The IEnumerator GetEnumerator() return in COM a IEnumVariant so it can be passed to NewEnum in

    VB  6.0

    Public Function NewEnun As IUnkonwn    
        NewEnum = obj.GetEnumerator()
    End Function

   In addition the "Current" propety of the Enumerator must return a type COM compatible,
   otherwise the For Each Loop will work but you will not be able to access the Item of the collection. For

   example for the code above the Current property will return a DictionaryEntry type that is not supported by
    COM. A work around to this is to do the following  
     ICollection keys = _sortedList.Keys;
     return (IEnumerator)keys.GetEnumerator();

     This way the "Current" property will return the Keys in the collection and not the DictionaryEntry Object.
     Another way around is to write your own Enumerator.
c) The collection is 0 based.
d) You could have the NetSortedList to implement System.Collections.Generic.ICollection

10) To make C# create and register the typelibrary go to
Project/Properties/Build tick Register for COM interop. (Reccomended Choice)
Otherwise you need to use REGASM. (asembly registration tool). The Assembly Registration tool reads
the metadata within an assembly and adds the necessary entries to the registry, which allows COM clients to create .NET Framework classes transparently. This utility is necessary when you need to expose to COM e .exe (winform application) .net assembly. You can create a type libray in this way. MyAssembly.dll can be also MyAssembly.exe

/codebase MyAssembly.dll /tlb:MyAssembly.tlb
To unregister just do
REGASM /u MyAssembly.dll /tlb:MyAssembly.tlb

As a note. When you add a COM dll to a project, C# calls REGASM for you, create a tlb file out of the
COM dll and put it in your project folder
For more details see the  session DEPLOYMENT

11) It is important that you do not check the   Project/Properties/Application/Assembly Information...
Make assembly COM-Visible. This option will set
[assembly: ConVisible(true)]
in the AssemblyInfo.cs file
Which will make all the Class in the Project COM visible. This in practice will make C# to generate
new Guids for each class on which we did not specify a Guid attribute each time we recompile, causing a registry bloat.
It is much better to set
[assembly: ConVisible(false)], and use ComVisible(true) at class level to specify which class should be
visible for COM interop
12) The assembly should be given a strong name.
Go to Properties/Signing. Tick Sign the assembly check box.
Then go to Choose a stroing name key file
Choose a name  ex   "MyLibrary_COM_Key"
This will create a MyLibray_COM_Key.snk file in the project folder.
The assembly is now signed with a strong name.
To deploy the Dll we need to register the .net assembly of a computer for COM interop.
To register it manually we have two ways
01) The dll assembly will reside on a specific folder decided at the moment of the assembly registration
You need to type the following commands:
regasm /u "FullPath\MyLibray.dll" /tlb
regasm /codebase  "FullPath\MyLibray.dll" /tlb
  ragasm is located in  c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\regasm.exe
  The first line unregister MyLibrary.dll and its type library
  The second line register  MyLibrary.dll create the type libary  and register them in the COM registry
  the /codebase option saves in the regestry the path of the dll
  This is exactly what the option "Register for COM interop" Does
  If we do not put the /codebase option, VB6.0 will complain that it  cannot find the dll file
02) The dll assembly will reside in the GAC c:\WINDOWS\assembly. The assembly MUST be strongly named if you want  to install it in the GAC.
  You need to type the following commands
  regasm "FullPath\MyLibray.dll" /tlb
  gacutil /if "FullPath\MyLibray.dll"
  The first line register the assembly for COM interop. Please note that we must leave out /codebase, because
  we will put the dll in the GAC.
  The second line, registers MyLibray in the GAC.
  This mean that all the application will look at the GAC when we run the assembly
  To unregister we do
  regasm /u "FullPath\MyLibrary.dll" /tlb
  gacutil /uf MyLibrary
  To Remove from the GAC a specif version
  gacutil /uf MyLibrary , Version=
  Please note the we do not use neither the FullPath nor the .dll/.exe to remove an assembly from the GAC
  in addition each .NET Framework has its own gacutil.exe. The one for .NET 3.5 can be found here
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\gacutil.exe"
Installing the assembly in the GAC ensures that multiple versions of the component can exist side-by-side.
An additional hard requirement for COM is that you change the GUIDs of the public interfaces and classes
when you change their definitions.  Failing to do so will wipe out the registry info for the old component
and will make old client programs that have not been recompiled with the new component crash and burn.
  A problem better known as DLL Hëll.
  if We need to change the interace of our COM component we need to
  1) Change the version number of the assembly. (Do not change the strong key name of file)
  2) Change the GUIDs for both the Class and interface of the class that has been changed

Changing them all could be better to avoid trouble when they depend on each other.

UDFs for Excel in VSTO

VSTO does not support yet the introduction of UDFs, which is a kind of crazy!
However there are few work around.

UDFs as Automation Add-in

This technique is good if you want to make the function available for every workbook in the Excel Application. It is based on the development of an "Excel Automation Add-in".
This consist of developing a kind of C# COM exposed class.
It was a good idea that the author included in the example the GUID identifiers to avoid registry bloating, however I don't know why he did not defined an Internface and let the class implement it, which is the standard way to develop COM exposed class in .NET

UDFs in Code behind files
This technique is good when you want to define workbook level UDFs. It is based on a COM exposed class and some VBA wrapper that you need to write at workbook level. The only problem I had was to the the Exel.Application.Run command work fine. My mistake was due to the fact that I was adding the VBA code the the Excel file that shows up after building and running the program. I did not realized that each time I run, the original .xls file used by the solution was overwriting the one I was editing.

Given the fact that VSTO does not suggest to save aa .XLA, a .XLS files based on VSTO solution, it is still quite hard to deploy an add-in that can use and excel file as data storage.
For example I developed some years ago an Excel Add-in .XLA to bootstrap the Interest Rate Swap Curve in US and Euro area, and I was using a spreadsheet in the .xls file to store data. The add-in makes available to every workbook a set of functions to compute some fixed income analytics : PV, duration, factor analysis...
Of course if this is the idea, the code-behind pattern VSTO solution is ruled out (it cannot make available function to everywork book that runs in an Excel application).
 We are left with the Automation add-in solution, which is basically a COM Exposed Class. However, the automation add-in does not have any worksheet to store the data. To be honest one could also develop a COM based add-in (which I do not cover here), but I think it would not sort the problem out.

If anybody has any idea on how to develop .xla kind of add-in, ie a VSTO solution that allows to

1) make UDFs available at application level
2) use an Excel file to store some data

as an .xla solution does, please let me know.

Windows 7 run command...

Where has the Run command gone in Windows 7?

You need to add it.
This are the steps

1) Right Click the Start Button
2) Properties
3) Select Start Menu Tab
4) Click Customize
5) Select Run Command
6) Click OK Twice.

You are done.

VSTO "Excel Disigner Could Not Be Activated" error

After Installing VSTO on my PC, and trying our my first "Code Behind" project, I could not have access to the Excel designer. No controls were displayed in the Control toolbox and I could not any button or any type of control on the excel worksheet I was working on
The error message was a pretty scary one

"Excel Designer Could Not Be Activated"

After a bit of diggin on google I found this Post on the msdn forum, which helped me out sort the problem.

Among the different suggestions the one that worked for me was to reinstall the VSTO run time.

If you are developing solution for Excel 2003 and VS2008 this is the one you need

Microsoft Visual Studio 2005 Tools for Office Second Edition Runtime (VSTO 2005 SE) (x86)

or the latest version (up to date of publishing)

Microsoft Visual Studio 2005 Tools for Office Second Edition Runtime (VSTO 2005 SE) (x86)(build 8.0.50727.940)

If you are developing solution for Excel 2007 and VS2008 this is the other link

Microsoft Visual Studio Tools for the Microsoft Office system (version 3.0 Runtime) SP1 (x86)

Office 2003, PIA Installation guidelines for .NET platform

In case you have problem getting the Primary Interop Assembly (PIA) working fine with your .NET Visual Studio platform, just check this link.

Installing the PIA for Office 2003

For example I kept having a "tlbimp.exe" generated assembly (not the PIA) when adding a COM Reference to Microsoft.Office.Interop.Excel, from the "Add Reference", COM tab window.

This means the the .dll path of this reference was pointing to a local dll (local to the project), rather than the one registered in the GAC.

I sorted the problem just changing the "Copy Local" property to false, removing the reference and adding it again.

However, you could also have had some problem with the registration of the PIA in the GAC (Global Assembly Cache). The above link help you sort this kind of problems.