advertisingprofitcontrol-alpha/AdvertsingProfitControl/DatabaseVersionControl.cs

using System;
using System.IO;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AdvertsingProfitControl
{
    internal class DatabaseVersionControl
    {
        FrmLogConsole _console = FrmLogConsole.GetStaticInstance;

        public string GetDatabaseVerionNumber(string connectionString)
        {
            var versionNumber = "0.0.0.0";
            var oleDbCommand = new OleDbCommand
            {
                CommandText = "SELECT VersionNumber FROM Version"
            };
            var connection = new OleDbConnection(connectionString);
            oleDbCommand.Connection = connection;

            try
            {
                connection.Open();
                var reader = oleDbCommand.ExecuteReader();
                while(reader != null && reader.Read())
                {
                    versionNumber = reader[0].ToString();
                }
            }
            catch (OleDbException e)
            {
                //Due to primitive nature of the original database (v0.3.0.0), which did not contain a Version table, an error 
                //will be kicked out trying to extract the version number from the database. So, just assume this isn't an 
                //OleDb error of some type.
                _console.WriteToLog(FrmLogConsole.Level.Warning, "Testing for the database's version yielded an error.\nVersion number will be assumed to be 0.3.0.0.");
                _console.WriteToLog(FrmLogConsole.Level.Debug, e.Message);
                versionNumber = "0.3.0.0";
            }
            finally
            {
                connection.Close();
            }

            return versionNumber;
        }

        public bool UpdateVersionPointFive(string connectionString, out string versionNumber)
        {
            var updated = false;
            //Create a backup of the database
            File.Copy(Application.StartupPath + "\\APCDatabase.accdb", Application.StartupPath + "\\APCDatabase.bak", true);
            var connection = new OleDbConnection(connectionString);
            var oleDbCommand = new OleDbCommand
            {
                CommandText = "SELECT FK_AdItemID, FK_DateID, FK_GroupID FROM APC",
                Connection = connection
            };

            using (connection)
            {
                connection.Open();
                var apcReturnTable = new List<string>();

                using(var reader = oleDbCommand.ExecuteReader())
                {
                    //Each record's structure is as follows (index based):
                    // 0 - FK_AdItemID, 1 - FK_DateID and 2 - FK_GroupID
                    var groupID = 0;
                    var rowAttribute = 0;
                    var adItemID = "";
                    var dateID = "";
                    while(reader != null && reader.Read())
                    {
                        apcReturnTable.Add(reader[0] + "," + reader[1] + "," + reader[2]);
                    }
                    reader?.Close();
                    //Update the FK_GroupID's data type.
                    //oleDbCommand.CommandText = "ALTER TABLE APC ALTER COLUMN FK_GroupID NUMBER";
                    //oleDbCommand.ExecuteNonQuery();
                    //Insert the new columns into the APC table.
                    oleDbCommand.CommandText = "ALTER TABLE APC ADD COLUMN RowPosition NUMBER, RowAttribute NUMBER";
                    oleDbCommand.ExecuteNonQuery();

                    //Change the object to update mode
                    oleDbCommand.CommandText =
                        "UPDATE APC SET RowAttribute = ?, RowPosition = ?, FK_GroupID = ? WHERE FK_AdItemID = ? AND FK_DateID = ?";

                    var rowPosition = 1;
                    foreach (var splitRow in apcReturnTable.Select(row => row.Split(',')))
                    {
                        if (splitRow[1] != dateID)
                        {
                            rowPosition = 1;
                        }
                        groupID = 0;
                        rowAttribute = 0;
                        if (splitRow.Length == 3)
                        {
                            adItemID = splitRow[0];
                            dateID = splitRow[1];
                            var splitGroupIDs = splitRow[2].Split('|');
                            if (splitGroupIDs.Length == 2)
                            {
                                if (splitGroupIDs[1] != "")
                                {
                                     groupID = int.Parse(splitGroupIDs[1]);
                                }
                                if (splitGroupIDs[0] != "")
                                {
                                    rowAttribute = int.Parse(splitGroupIDs[0]);
                                }
                                
                            }
                            else if (splitGroupIDs.Length == 1)
                            {
                                //Try parsing the value to an int and check how big it is.
                                //Less then three (3) means either a header or member row identifier, whereas a three (3) or greater means an ad special row.
                                var parsedVal = 0;
                                if (int.TryParse(splitGroupIDs[0], out parsedVal))
                                {
                                    if (parsedVal > 2)
                                    {
                                        groupID = parsedVal;
                                    }
                                    else if (parsedVal < 3)
                                    {
                                        rowAttribute = parsedVal;
                                    }
                                }
                            }
                        }
                        else
                        {
                            adItemID = splitRow[0];
                            dateID = splitRow[1];
                        }

                        //Update the new row with the information.
                        oleDbCommand.Parameters.Clear();
                        if (rowAttribute == 0)
                        {
                            oleDbCommand.Parameters.AddWithValue("RowAttribute", OleDbType.Empty);
                        }
                        else
                        {
                            oleDbCommand.Parameters.AddWithValue("RowAttribute", rowAttribute);            
                        }
                        oleDbCommand.Parameters.AddWithValue("RowPosition", rowPosition);
                        if (groupID == 0)
                        {
                            oleDbCommand.Parameters.AddWithValue("RowAttribute", OleDbType.Empty);
                        }
                        else
                        {
                            oleDbCommand.Parameters.AddWithValue("GroupID", groupID);
                        }    
                        oleDbCommand.Parameters.AddWithValue("AdItemID", adItemID);
                        oleDbCommand.Parameters.AddWithValue("DateID", dateID);

                        if (oleDbCommand.ExecuteNonQuery() != 1)
                        {
                            MessageBox.Show("An error has occurred trying to upgrade the database", "Fatal Error");
                            break;
                        }

                        rowPosition++;
                    }
                    //Update the FK_GroupID's data type.
                    oleDbCommand.Parameters.Clear();
                    oleDbCommand.CommandText = "ALTER TABLE APC ALTER COLUMN FK_GroupID NUMBER";
                    oleDbCommand.ExecuteNonQuery();
                    oleDbCommand.CommandText = "ALTER TABLE APC ALTER COLUMN ProjectionSold TEXT";
                    oleDbCommand.ExecuteNonQuery();
                    //Apply an update patch for version 0.3.0.0 database by adding a version table and inserting the version number into the database.
                    oleDbCommand.CommandText = "CREATE TABLE Version (ID AUTOINCREMENT PRIMARY KEY, VersionNumber TEXT)";
                    oleDbCommand.ExecuteNonQuery();
                    //Insert the version number into the new table
                    oleDbCommand.CommandText = "INSERT INTO Version (VersionNumber) VALUES ('0.5.0.0')";
                    oleDbCommand.ExecuteNonQuery();

                    versionNumber = "0.5.0.0";

                    updated = true;
                }
            }

            return updated;
        }
    }
}