Skip to main content

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

Things have also changed in .NET 6 - you can now add a COM reference, namely Microsoft Excel XX.X Object Library and use it in your code. You might need Microsoft excel installed on your computer in order to run this. First, add it to your project.










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

Popular posts from this blog

How to fix Umbraco v9 "Boot Failed : Umbraco failed to boot, if you are the owner of the website please see the log file for more details." error

If you have started working with Umbraco v9 and done your first Azure deployment to one of your testing environments, there is a possibility that you might see the following "Boot Failed" error. Error: Checking the logs In order to understand the problem, you should check the Umbarco log file.  The default location for this file is umbraco/Logs and this file contains the Machine name, along with the date information. You can reach this file via Azure's Kudu Service  or alternatively, you can get download your Azure App Service's publish profile and connect your App Service via an FTP application, i.e. FileZilla. See the FileZilla screen below; Once you get your log file, you can download it to your local machine and open it with a text editor, i.e. Notepad++. When you open it, you will see all logs, including the error message. Please be aware, as most things with Umbraco, logging is also customizable, so you can either use the default Umbraco logging which is Micros

How to use JQuery Ajax Methods for Async ASP.NET MVC Action Methods

Making repeatedly calls to async methods can be a nightmare. In this case, it makes sense to use 2 ajax methods, instead of one. Here is a simple solution to overcome this problem. See that  ajaxcalls   is emptied after the success response for the first ajax call and then the second ajax method is used to make one single call to the async action method. Hope it helps. View: @section Scripts{     < script type ="text/javascript">         var smartDebitObject = new Object();         smartDebitObject.MembershipNumber = $( "#MembershipNumber" ).val();         smartDebitObject.ProfileId = $( "#ProfileId" ).val();         smartDebitObject.FirstName = $( "#FirstName" ).val();         smartDebitObject.LastName = $( "#LastName" ).val();         smartDebitObject.AddressLine1 = $( "#AddressLine1" ).val();         smartDebitObject.Postcode = $( "#Postcode" ).val();         smartDebitObject

How to fix Umbraco CMS Build Error NU1104: Unable to find project '..\Umbraco-CMS\tools\Umbraco.Core\Umbraco.Core.csproj'.

Yesterday during an online meeting , I was talking about how to contribute to Umbraco CMS . As part of this talk, in order to set up my local Umbraco CMS project ,  I cloned the latest Umbraco.CMS code to my local machine, but when I built it I saw the following error: Error NU1104 Unable to find project ' D : \git \repos \Umbraco -CMS \tools \Umbraco .Core \Umbraco .Core .csproj ' . Check that the project reference is valid and that the project file exists . Umbraco .JsonSchema D : \git \repos \Umbraco -CMS \tools \Umbraco .JsonSchema \Umbraco .JsonSchema .csproj I was surprised to see this error as I did the exact same thing a couple of days ago and saw no problems.  After trying a few things, I checked the Umbraco.JsonSchema project's project references and realised that Umbraco.Core project reference was broken.  After removing Umbraco.Core from the Umbraco.JsonSchema and adding it again fixed the problem and I could then do my local database setup as normal.