Support changing settings between versions of a product's lifecycle

[DaveRMaltby]

Currently, if you add/remove properties from any of your setting POCO classes, you must delete the backing settings file or you will get an exception the next time that you run your application. The settings files should follow the lead of the POCOs and morph as needed.

Requirements:

• Both adding and removing properties must be supported.
• Existing settings in the files must be preserved.
• Any new properties on a settings POCO will have their default values set in the backing file.

REF: https://github.com/Servant-Software-LLC/FileBased.DataProviders/issues/9 and https://github.com/Servant-Software-LLC/SqlBuildingBlocks/issues/7

[DaveRMaltby]

1. Query the Database Schema Using ADO.NET, you can query the database schema information for a specific table to get a list of existing columns. This can be done by executing schema-related queries or using built-in schema collection methods available in ADO.NET. For example, you can use the GetSchema method on an open DbConnection object to retrieve metadata about the database objects.

2. Compare the Schema with the Model After retrieving the list of columns for a table, you can reflect on your model class to get its properties. By comparing the property names with the column names you retrieved from the database, you can identify which properties do not have corresponding columns.

3. Modify the Database Schema if Necessary For properties that do not have corresponding columns in the database, you can dynamically generate and execute SQL statements to alter the table and add the necessary columns. This step should be done with caution to ensure that the changes to the database schema do not inadvertently affect other parts of your application or data integrity.

using System.Data.Common;
using System.Reflection;

// Assuming 'connection' is an open DbConnection object to your database
var tableName = "YourTableName";
var modelProperties = typeof(YourModelClass).GetProperties();

// Retrieve column information for the table
var columnsDataTable = connection.GetSchema("Columns", new[] { null, null, tableName, null });

var columnNames = columnsDataTable.AsEnumerable()
    .Select(row => row["COLUMN_NAME"].ToString())
    .ToList();

foreach (var property in modelProperties)
{
    if (!columnNames.Contains(property.Name))
    {
        // Property does not have a corresponding column, so add it
        var addColumnSql = $"ALTER TABLE {tableName} ADD {property.Name} {GetSqlTypeForProperty(property)};";
        // Execute the SQL command to add the column
        using var command = connection.CreateCommand();
        command.CommandText = addColumnSql;
        command.ExecuteNonQuery();
    }
}

// This method should return the SQL column type based on the property type
string GetSqlTypeForProperty(PropertyInfo property)
{
    // Implement logic to determine SQL column type based on the property's .NET type
    // This is just an illustrative placeholder
    return "VARCHAR(255)";
}