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

Search This Blog


Tuesday, November 30, 2010

How to use build a mail system framework in Excel using OOP: Example

I will explain how to use the framework to build a new mail object using the framework I have build

Let'us suppose we want to build a mail to trade a Total Return Swap to be send to a broker

 1) Interface Layer: We first create a ITrsMailDataProvider and define its interface

Option Explicit

Public Function RetrieveBody(irsId As String, fundId As String) As Parameters

'No Code Here
End Function

Public Function RetrieveHeader(irsId As String, fundId As String) As HeaderDTO
'No Code Here
End Function

 2) DAL:  We then create a TrsMailDataExcelProvider that implements teh ITrsMailDataProvider interface. You   can use some mock data for testing. If you are getting data from Access just go ahead and create your TRSMailDataAccessProvider that implements the same interface

2) Interface Layer: Then we need to Create a IProviderFactory. This will be the abstract factory that define the abstract methods that return our interface ITrsMailDataProvider or a  IIrsMailDataProvider. The idea is that the abstract providers will be produced by your abstract factory. The business layer will use only those interface and will know nothing about which concrete provider it is in use

   This class will look like
   Class IProviderFactory
       GetTrsMailDataProvider as ITrsMailDataProvider  //only method signature
       GetIrsMailDataProvider as  IIrsMailDataProvider  //only method signature
   End Class

3) DAL Layer: We then need a class that implement the IProviderFactory. For Example a ExcelProviderFactory will take care to create of the MailDataProvider that have Excel as source.

Option Explicit

Implements IProviderFactory

Private mTrsMailDataProvider As TrsMailDataExcelProvider
Private mIrsMailDataProvider As IrsMailDataExcelProvider

Private Function IProviderFactory_GetIrsMailDataProvider() As IIrsMailDataProvider
If mIrsMailDataProvider Is Nothing Then
Set mIrsMailDataProvider = New IrsMailDataExcelProvider
'Do Nothing
End If
Set IProviderFactory_GetIrsMailDataProvider = mIrsMailDataProvider

End Function

Private Function IProviderFactory_GetTrsMailDataProvider() As ITrsMailDataProvider
If mTrsMailDataProvider Is Nothing Then
Set mTrsMailDataProvider = New TrsMailDataExcelProvider
'Do Nothing
End If

Set IProviderFactory_GetTrsMailDataProvider = mTrsMailDataProvider
End Function

4) BLL: Finally we will have the client of the Abstract Factory, as for the Abstract factory Pattern. We will call this class the DataProvider (this can be a static class in c# ore moduel in VBA) and it will sit in the business Layer. This Class has the main objective to
use the IProviderFactory interface to produce our Trs and Irs MailDataProvider. The diffuclt part here is that this class should istantiate a Real istance of the abstract class IProviderFactory, but as we know we want the business Layer to be agnostic of the DAL. To get this result in C# we can use reflection. We can just write a method so that it loads a .dll specified in a config file as a string, and create an istance of a class from this dll.
This way to reference to the DAL Layer are necessary. In VB 6.0 this is can be done with CreateObject.

Option Explicit

Private mFactory As IProviderFactory

Public Function IrsMail() As IIrsMailDataProvider
Set IrsMail = Factory.GetIrsMailDataProvider()
End Function

Public Function TrsMail() As ITrsMailDataProvider
Set TrsMail = Factory.GetTrsMailDataProvider()
End Function

Private Function Factory() As IProviderFactory
'Insert Code here
'This cose should use a config file and reflection to choose
'which concrete factory instantiate. Createobject could be used in VB 6.0
'to make this class decouple with the DAL Layer
'We keep things ease here.

If (mFactory Is Nothing) Then
Set mFactory = New ExcelProviderFactory 'I really should be using CreateObject
'to keep the class decoupled from the DAL
End If
Set Factory = mFactory

End Function

4) So fare we have done the following
    a) Business Layer -->  Interface Layer -->; DataBase Layer
    b) In the Interface Layer we have implemented the Model Provider Pattern and the Abstract Factory Pattern. Those class are just interface that define the methods that the business Layer can call to accesss the data
    c) In the DAL we have the real providers that just implement the provider interfaces and the real factory that just implement the factory methods.
    d) The transfer of the data between DAL and Business Layer is done using some object that are on the common layer such as MailDTO (data transfer object) or the Parameters collections (this is an utility object)
    e) The client of the abstract factory, the one we called DataProvider, is in the BLL and it stores in as a private field a reference to the IProviderFactory. This is where the magic happen: we can just switch the RealProvider withouth having to change any of the code that regards the BLL or Interface Layer.

Monday, November 29, 2010

How to get the scripting dictionary enumerator to use in the for each loop in visual basic

A common practice is writing VB 6.0 or VBA code to wrap the Collection object in order to create strongly type Collections.

An alternative to the collection object is the scripting.Dictionary object which you can find adding a reference to the Microsoft Scripting Runtime.

The Dictionary Object is an Hash Table, so it is preferred to the Collection object when you need to access elements in the collection by key.
In addtion it has few properties and methods that the Collection object is lacking.

Keys() returns all the keys as an array
Items() returns all the Items as an array
Exists(key): returns true if a key is in the dictionary.

The major draw back is that it does not have an enumerator so you cannot do something like

for Each v in objDictionary
'Do Something

Fortunately there is a work around. You can loop the Items or the Keys array

Dim v as variant
For Each v in objDictionary.Items
'Do Something

Not that being Items an array, v must be declared as a variant type.
It would be much nicer if we could loop using a strongly typed objected instead.
You can do this by wrapping the scripting.Dictionary class in a customized class and letting the Items method return a Collection object.
You can find here an example

Parameter Class

Option Explicit

Private mValue As Variant
Private mName As String
Private mFormat As String

Private Sub Class_Initialize()
Me.format = ""
Me.Name = ""
Me.value = Empty
End Sub

Public Property Get Name() As String
Name = mName
End Property

Public Property Let Name(strName As String)
mName = strName
End Property

Public Property Get value() As Variant
If IsObject(mValue) Then
Set value = mValue
value = mValue
End If

End Property

Public Property Let value(varValue As Variant)

If IsObject(varValue) Then
Set mValue = varValue
mValue = varValue
End If

End Property

Public Property Get format() As String
format = mFormat
End Property

Public Property Let format(strFormat As String)
mFormat = strFormat
End Property

Then create a Parameters.cls file and paste the code here.

Parameters Class

Option Explicit

Private Const ErrItemIsMissingNum = vbObjectError + 1001
Private Const ErrItemIsMissingSrc = "FFM:Parameters:Item"
Private Const ErrItemIsMissingDes = "Item is missing form the collection"

Private mKeys As Collection
Private mParsDic As Dictionary

Private Sub Class_Initialize()

Set mParsDic = New Dictionary

End Sub

Private Sub Class_Terminate()
Set mParsDic = Nothing
End Sub

Public Function Keys() As Collection
Dim v As Variant
Dim h As Collection
Set h = New Collection
For Each v In mParsDic.Keys
Call h.Add(v)
Set Keys = h
End Function

Public Function Items() As Collection
Dim v As Variant
Dim h As Collection
Set h = New Collection
For Each v In mParsDic.Items
Call h.Add(v)
Set Items = h

End Function

Public Function Item(Index As Variant) As Parameter

Set Item = mParsDic.Item(Index)

End Function

Public Sub Add00(Item As Parameter)
Call mParsDic.Add(Item.Name, Item)
End Sub

Public Sub Add01(Name As String, value As Variant, Optional format As String = "")
Dim objParameter As Parameter
Set objParameter = New Parameter

objParameter.Name = Name
objParameter.value = value
objParameter.format = format
Call Me.Add00(objParameter)
End Sub

Public Function Count() As Long
Count = mParsDic.Count
End Function

Public Function Remove(key As String)

mParsDic.Remove (key)

End Function

Public Function IsInCollection(Name As String) As Boolean

IsInCollection = mParsDic.Exists(Name)

End Function

Public Function Duplicate() As Parameters
'This function Create a New Parameter Collection
Dim objDuplicate As Parameters
Dim par As Parameter
Set objDuplicate = New Parameters
For Each par In Me.Items
Call objDuplicate.Add01(par.Name, par.value, par.format)
Set Duplicate = objDuplicate

End Function

Once you have done that you will be able to write code like

Dim colPars As Parameters
Dim aa As Parameter, bb As Parameter, cc As Parameter
Dim h As Variant

Set colPars = New Parameters
Set aa = New Parameter
Set bb = New Parameter
Set cc = New Parameter

aa.Name = "Mario"
bb.Name = "Gennaro"
Call colPars.Add00(aa)
Call colPars.Add00(bb)

For Each cc In colPars.Items
Debug.Print cc.Name

Monday, November 15, 2010

How to send a mail message in C#

Here you can find a piece of code to send mail using c#.
Please not that you need to know your Smtp Host server name to be able to sent out any mail.
In addtion I added a couple of lines to get the current user name.
You can map the current user name to an email address so that you can send the mail out from the current user mail address

ex: mailAddress = abeti then use as mail address

using System.Net.Mail;
using System.Security.Principal;

private void button1_Click(object sender, EventArgs e)

string mailAddress;
mailAddress = WindowsIdentity.GetCurrent().Name;
MailMessage message = new MailMessage();
message.From = new MailAddress("");
message.To.Add(new MailAddress(""));
message.CC.Add(new MailAddress(""));
message.Subject = "Message From .Net";
message.Body = "This is the content";
string pdfPath = "C:\\test\\";
string pdfFileName = "prova.pdf";
Attachment pdfFile = new Attachment(pdfPath + pdfFileName);
SmtpClient client = new SmtpClient();

Thursday, November 11, 2010

How to read write and save to a config file in C#

Here you can find some c# code to read/write/save to a config file in c#.
Please note that you need to add a reference to
References -> Add Reference -> System.Configuration


using System.Configuration.
In Addtion you need to add an application configuration file

Application -> Add -> New Item -> Application Configuration File
Than you need to add an <appSettings> session to it

<?xml version="1.0" encoding="utf-8" ?>
    <add key="cnnString" value="prova"/>
    <add key="LastUpdateDate" value="10 Jan 2012"/>

When you run the application and debug the code you find that it is not working !! You can sse the effect when you run the exe generated in release directory.


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

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

private void button1_Click(object sender, EventArgs e)
string cnnString;

// Get the current configuration file.
Configuration config =ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
cnnString = config.AppSettings.Settings["cnnString"].Value;
config.AppSettings.Settings["cnnString"].Value = "Cavolo";
cnnString = config.AppSettings.Settings["cnnString"].Value;