Network Programming in .NET

If you hold a database of products or services priced in one currency, you might make it easier for your customers to display the offers in their own currency. But, you need to keep your exchange rates up to date, otherwise a sudden dip in a currency might mean that you loose money, or quote the wrong price to consumers.

There are plenty of free, and accurate feeds for currencies. I chose to use one from the ECB (European Central Bank), which I would trust.

Here is the XML Feed url:

https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml

Now, we want to pull that data on a daily basis, and import it into our database, using a serverless architecture on AWS. Using Lambda, Node, and I’m using SQL server as the database.

First off, in order to trigger a Lambda function on a schedule, you need to set up another service called cloudwatch. You create a cloudwatch rule, and set a schedule for one day – but for debugging purposes, set it to one minute initially, until the system is working. You connect this rule to your Lambda Function.

Now, I installed a number of NPM packages, Request, xml2Js, and MSsql. Those are to make HTTP requests easier, help handling XML, and connecting to SQL server, respectively.

Now, to set up my database, I created a table:

Create Table ExchangeRates
(
Currency char(3),
Rate float,
DateUpdated datetime default getdate()
)

The DateUpdated, is not strictly necessary, but it helps me see that the update function is running reliably.

I have to write some node code to execute my SQL statement, which I place in a file called database.js

const sql = require(‘mssql’);

(function() {

// ——- Database support ——- //

module.exports.execute = function (sqlQuery, callback)
{
const config = {
user: ‘xxxxx’,
password: ‘xxxx’,
server: ‘xxx.xxx.xxx’,
database: ‘xxxxx’,
};
sql.connect(config, (err) => {
if (err) {
console.log(err);
callback(err);
} else {
const req = new sql.Request();
req.query(sqlQuery, (error, result) => {
if (error) {
console.log(error);
callback(error);
} else {
console.log(result);
sql.close();
callback(null, result.recordset);
}
});
}
});

sql.on(‘error’, (err) => {
console.log(err);
callback(err);
});

};

}());

Now, I can simply say database.execute( SQL , callback) and the SQL will run.

Now, to make the request, parse the XML, and convert it into a SQL statement, I use this code;

function GetRatesFromECB(callback)
{
var strUrl = “https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml”;
request(strUrl, function (error, response, body) {
console.log(‘error:’, error);
console.log(‘statusCode:’, response && response.statusCode);
xml2js.parseString(body, function (_err, result) {
var strSQL = “truncate table ExchangeRates; \r\n”;
var cube = result[“gesmes:Envelope”][“Cube”][0][“Cube”][0][“Cube”];
for(var i in cube)
{
var exchange = cube[i][“$”];
strSQL += “insert into ExchangeRates (currency,rate) values (‘” + exchange.currency + “‘,” + exchange.rate + “); \r\n”;
console.log(exchange);
}
database.execute(strSQL, function(){
callback();
});
});
});
}

What this does, is that it reads all the rates from the XML file, then creates an SQL statement that wipes the table, and re-populates it with the latest rates.

Then, the standard boilerplate entry point is as follows

exports.handler = (event, context, callback) => {
GetRatesFromECB(function(){
callback(null, “OK”);
});
};