How to improve the availability and performance of a Umbraco v10+ website by storing 3rd party data in a database
I have a Umbraco v10.3.2 that consumes multiple (old) SOAP API web services to get details like Chartered Flights, Arrivals and Departures Flights etc, and these flights have details like Airport IATA codes, Airline ICAO or IATA codes but not Airport names or Airline names and I needed to get these names for my front-end website.
Now, in my Umbraco project, I have got sections to create airports and airline data, when I create an airport, I create details like name and IATA code and for an airline, I create details like name, ICAO code and IATA code. I could potentially get the name details from Umbraco but for this, I first needed to create a lot of airport and airline details in Umbraco (I'm talking about 6168 airlines and 9070 airports), and then for each web service result, I needed to get the name details from Umbraco (as the web services returned only the codes but not names) - this just didn't make sense!
Solution - Excel comes to the rescue
When was the last time easyJet changed its name? Or Bristol Airport had a different name? Probably never, but the important thing is that Airline or Airport names don't change that often, so I decided to get all Airline details with their ICAO and IATA codes and names of course and store them in a database (forever!), the same idea for Airports with their IATA codes and names. My idea was to never go to Umbraco to get name details but get Airline and Airport data from the same database. This way I could always go and change the name of an airport or airline directly from the database if they ever change.
The idea was simple and I thought I could just find a list of all airlines and airports on the internet with all the details that I needed - but no, that wasn't the case! There are no such useful lists, but there are of course websites that you can use to create your lists and this is exactly what I did.
I used https://airlinecodes.info/icao to get the airline details for the whole world and similarly Wikipedia for the airport data for the whole world. It took me a whole day to create the following excel list and 1 hour to write the code - and let me save you a day by sharing my excel file with you here.
Code to read the data from excel and save it into a SQL database without any packages or OLEDB
Second, write your code as shown below;
public void CreateAirlinesInFeedsDatabase()
{
//
To see the airline codes online see https://airlinecodes.info/icao
string filePath = "D:\\git\\repos\\XYZ-Umbraco-Website\\src\\AirlineAndAirportCodes.xlsx";
Application excel = new Application();
Workbook workbook =
excel.Workbooks.Open(filePath);
Worksheet worksheet =
workbook.Worksheets[1]; // Airlines sheet
var airlines = new List<Airline>();
var totalRows = 5297;
var firstRow = 1;
for (int i = firstRow; i <= totalRows; i++)
{
Range icaoCodeCell =
worksheet.Cells[i, 1]; // Get the value of first
column
var icaoCodeCellValue =
Convert.ToString(icaoCodeCell.Value);
Range iataCodeCell = worksheet.Cells[i, 2]; // Get the value of first column
var iataCodeCellValue =
Convert.ToString(iataCodeCell.Value);
Range nameCell = worksheet.Cells[i, 3]; // Get the value of third column
var nameCellValue =
Convert.ToString(nameCell.Value);
//Add the ICAO codes and airline names
if (!string.IsNullOrEmpty(icaoCodeCellValue)
&& !string.IsNullOrEmpty(nameCellValue))
{
airlines.Add(new Airline()
{
ICAOorIATACode =
icaoCodeCellValue,
Name = nameCellValue
});
}
// Add airlines by the IATA codes
if (!string.IsNullOrEmpty(iataCodeCellValue)
&& !string.IsNullOrEmpty(nameCellValue))
{
airlines.Add(new Airline()
{
ICAOorIATACode =
iataCodeCellValue,
Name = nameCellValue
});
}
}
if (airlines != null && airlines.Count > 0)
{
// build the SQL query string
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("INSERT INTO Airline ");
stringBuilder.Append("(ICAOorIATACode, Name) ");
stringBuilder.Append("VALUES (@ICAOorIATACode, @Name)");
try
{
SqlConnection sqlConnection
= new
SqlConnection(_configuration.GetValue<string>("ConnectionStrings:FeedsDB"));
using (sqlConnection)
{
sqlConnection.Open();
foreach (Airline airline in airlines.GroupBy(x => x.ICAOorIATACode).Select(x => x.First()))
{
try
{
using (SqlCommand sqlCommand = new
SqlCommand(stringBuilder.ToString(), sqlConnection))
{
// add SQL parameters
sqlCommand.Parameters.AddWithValue("@ICAOorIATACode", airline.ICAOorIATACode);
sqlCommand.Parameters.AddWithValue("@Name", airline.Name);
sqlCommand.ExecuteNonQuery();
}
// successfully updated the database
}
catch (Exception ex)
{
// insertion failed, so report error and quit the process
//break;
//throw new Exception("sqlCommand.ExecuteNonQuery
DataUpdateFailure, ", ex);
}
}
}
}
catch (Exception ex)
{
// could not connect to the SQL database
}
}
}
public void CreateAirportsInFeedsDatabase()
{
//
To see the airport codes online see https://en.wikipedia.org/wiki/List_of_airports_by_IATA_airport_code:_A
string filePath = "D:\\git\\repos\\Bristol-Airport-Umbraco-Website\\src\\AirlineAndAirportCodes.xlsx";
Application excel = new Application();
Workbook workbook = excel.Workbooks.Open(filePath);
Worksheet worksheet =
workbook.Worksheets[2]; // Airports sheet
var airports = new List<Airport>();
var totalRows = 9079;
var firstRow = 1;
for (int i = firstRow; i <= totalRows; i++)
{
Range codeCell =
worksheet.Cells[i, 1]; // Get the value of first
column
string codeCellValue =
codeCell.Value;
Range nameCell =
worksheet.Cells[i, 2]; // Get the value of second
column
string nameCellValue =
nameCell.Value;
if (!string.IsNullOrEmpty(codeCellValue)
&& !string.IsNullOrEmpty(nameCellValue))
{
airports.Add(new Airport()
{
IATACode =
codeCellValue,
Name = nameCellValue
});
}
else
{
// Do whatever you want to do in case you need to do anything
}
}
if (airports != null && airports.Count > 0)
{
// build the SQL query string
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("INSERT INTO Airport ");
stringBuilder.Append("(IATACode, Name) ");
stringBuilder.Append("VALUES (@IATACode, @Name)");
try
{
SqlConnection sqlConnection
= new
SqlConnection(_configuration.GetValue<string>("ConnectionStrings:FeedsDB"));
using (sqlConnection)
{
sqlConnection.Open();
foreach (Airport airport in airports)
{
try
{
using (SqlCommand sqlCommand = new
SqlCommand(stringBuilder.ToString(), sqlConnection))
{
// add SQL parameters
sqlCommand.Parameters.AddWithValue("@IATACode", airport.IATACode);
sqlCommand.Parameters.AddWithValue("@Name", airport.Name);
sqlCommand.ExecuteNonQuery();
}
// successfully updated the database
}
catch (Exception ex)
{
// insertion failed, so report error and quit the process
//break;
//throw new Exception("sqlCommand.ExecuteNonQuery
DataUpdateFailure, ", ex);
}
}
}
}
catch (Exception ex)
{
// could not connect to the SQL database
}
}
}
Finally, remember to create your tables. You can now run your code and that should generate all your Airline and Airport data for your project. You can then retrieve your data and show it.
Hope this article saves you a lot of time. Happy coding.
Comments
Post a Comment