/// <summary>
/// Verify field names against database. Combines the ability to add and remove
/// fields from Database.
/// </summary><returns>true if modified, false if not needed, throws exception if fail</returns>
public bool VerifyOrCreateAndRemoveFields(bool createTableIfMissing = true)
{
VerifyOrCreateClassFieldsInDbTable(createTableIfMissing);
var dbAllFieldNames = _dbFieldNames.ToArray().ToList();
dbAllFieldNames.Add(IDField);
var extraDbFieldNames = GetDbFieldNames().Except(dbAllFieldNames).ToArray();
if (extraDbFieldNames.Length == 0)
{
return(false);
}
var sb = new StringBuilder();
bool firstField = true;
foreach (var fieldName in extraDbFieldNames)
{
if (firstField)
{
sb.Append(",");
}
sb.Append(fieldName);
sb.Append(" ");
firstField = false;
}
MyDataSource.ExecuteSql($"alter table {TableName} drop {sb}");
return(true);
}
/// <summary>
/// Add missing fields to database that are found in record class.
/// </summary><returns>true if changed, false if no need to change, throw exceptions on errors</returns>
public bool VerifyOrCreateClassFieldsInDbTable(bool createTableIfMissing = true)
{
if (createTableIfMissing && !MyDataSource.GetDataTableNames().Contains(TableName))
{
CreateDataTable();
return(true);
}
// field name exceptions, index matches location in fieldInfos
var missingDbFieldNames = _dbFieldNames.Except(GetDbFieldNames()).ToArray();
if (missingDbFieldNames.Length == 0)
{
return(false);
}
try {
var sb = new StringBuilder();
bool firstField = true;
foreach (var dbFieldName in missingDbFieldNames)
{
if (!firstField)
{
sb.Append(",");
}
int fiIndex = _dbFieldNames.IndexOf(dbFieldName);
string fixDbFn = dbFieldName;
int fnSepIdx = fixDbFn.IndexOf(".[", StringComparison.Ordinal);
if (MyDataSource is DataFileSource dfSrc && dfSrc.FileType != DataFileType.Access2007 &&
dfSrc.FileType != DataFileType.AccessMdb && fnSepIdx >= 0)
{
fixDbFn = fixDbFn.Substring(fnSepIdx + 2, fixDbFn.Length - fnSepIdx - 3);
}
sb.Append(fixDbFn);
sb.Append(" ");
sb.Append(FieldInfoToSqlType(_fieldInfos[fiIndex]));
firstField = false;
}
MyDataSource.ExecuteSql($"alter table {TableName} add {sb}");
} catch {
//on fail remove all fieldInfos not contained in database.
foreach (var dbFieldName in missingDbFieldNames)
{
int fiIndex = _dbFieldNames.IndexOf(dbFieldName);
_fieldInfos.RemoveAt(fiIndex);
_dbFieldNames.RemoveAt(fiIndex);
}
throw;
}
return(true);
}