Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / C#
Article

Automatic currency rate updates every day to your database.

Rate me:
Please Sign up or sign in to vote.
4.00/5 (3 votes)
18 Apr 20042 min read 47.9K   865   21   4
Update currency information in your database automatically with currency feed from xe.com.

Introduction

Are you running an online store and want to keep your currency conversion rates up to date? Then this program can save you some time. I developed this as a prototype to show how such data can be loaded.

The program can accept currency feeds from xe.com. Just go to xe.com and subscribe for email updates of currency here.

Block Diagram of the System

Block diagram

The program will connect to the mail box to which updates are sent from xe.com, retrieve the email, parse it and update a SQL database. This program can be scheduled using Windows Scheduler to keep your database with upto date currency information. Feel free to edit the source and use as needed. This is the functionality in brief.

Let's look at the details.

xe.com email updates consist of the following text layout.

Rates as of 2002.03.14 21:20:02 UTC (GMT). Base currency is USD.

Currency Unit                 USD per Unit     Units per USD
================            ===============   ===============
USD United States Dollars     1.00000           1.00000
EUR Euro                      0.882525          1.13311
GBP United Kingdom Pounds     1.42053           0.703961
......ZMK Zambia Kwacha       0.000223339       4477.50

The core of the system consist of a class called CurrencyUpdater.

The class has a single constructor. It takes no arguments. The constructor loads the configuration information about the mail box user name, password and POP3 server name from the App.config file:

C#
public CurrencyUpdater()
{
 user=
  System.Configuration.ConfigurationSettings.AppSettings.Get("currency_mailbox_user");
 ...
}

Then the main routine invoke the UpdateCurrency() method on the CurrencyUpdater class. This function does the following things:

  1. Calls the GetUpdateString() and gets the rates string from the POP3 email box.
  2. Calls ProcessEmailString() which processes the string and generates the appropriate SQL and executes it against the database.
    C#
    public string GetUpdateString()
    {
      Log("Connecting to mailbox....");
    
      // Connect to XeRates Currency update email box
      string body=null;
      string subject;
      long messagecount=0;
      try
      {
        p=new Pop3Client(user,password,server);
        if(p!=null)
        {
          p.OpenInbox();
          messagecount=p.MessageCount;
          for(int i=1;i<messagecount;i++)
          {
            p.NextEmail();
            subject=p.Subject;
            if(subject!="")
            {
              if(subject.StartsWith("Today's Currency Update (SGD)"))
              {
                body=p.Body;
                break;
              }
            }
          }
        }
      }
      catch(Exception ex)
      {
        body=null;
        throw ex;
      }
      if(p!=null)
        p.CloseConnection();
      Log("Got string from mailbox...");
      return body;
    }

Configure the App.config file. It's pretty straight forward:

XML
<add key="currency_mailbox_user" value="<<mailbox user name>>" />
 <add key="currency_mailbox_password" value="<<mail password>>" />
 <add key="currency_mailbox_server" value="<<mail server>>" />
 <add key="constring" value="<<connection string>>" />
 <add key="MailServer" value="<<mail server>>" />
 <add key="NOTIFY_SUCCESS" value="1" />
 <add key="NOTIFY_FAIL" value="1" /> 
 <add key="SUCCESS_FROM" value="<<from email for success>>" />
 <add key="SUCCESS_TO" value="<<to email for successful run>>" />
 <add key="SUCCESS_CC" value="<<cc email for successful run>>" /> 
 <add key="FAIL_FROM" value="<<from email for failures>>" />
 <add key="FAIL_TO" value="<<to email for failures>>" />
 <add key="FAIL_CC" value="<<cc email for failures>>" /> 
 <add key="SQL_1" 
   value="select Rate from tbCurrency where Currency='[[CURRENCY_CODE]]'" />
 <add key="SQL_2" 
   value="update tbCurrency set Rate=[[RATE]], 
     UpdatedOn=getdate() where Currency='[[CURRENCY_CODE]]'" />

In the above keys, the keys which need further explanation are SQL_1 and SQL_2:

  • SQL_1 - This is the SQL to retrieve the current currency rates from the table. This is for sending update success email with old rates.
  • SQL_2 - This is the update SQL. Note that [[RATE]] and [[CURRENCY_CODE]] will be substituted at run time.

Compile the project.

There you go .......

This project uses the POP3 component developed by Desmond McCarter and hosted here.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Singapore Singapore
Working with XM Asia, Singapore as Senior Solutions Engineer. MCSD.NET Early achiever. SCJP 1.4, with 6 years development experience.

Comments and Discussions

 
GeneralNice work ! But Little Trouble Pin
stigma196726-Oct-09 23:36
stigma196726-Oct-09 23:36 
GeneralRe: Nice work ! But Little Trouble Pin
swap_koki9-Aug-15 21:53
swap_koki9-Aug-15 21:53 
GeneralGood Idea.. Pin
Domingo M. Asuncion1-Mar-07 21:36
Domingo M. Asuncion1-Mar-07 21:36 
Generalyou can also use this Pin
Daniel Fisher (lennybacon)20-Apr-04 3:32
Daniel Fisher (lennybacon)20-Apr-04 3:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.