/// <summary>
/// Sets a database record values with values retrieved from data file.
/// </summary>
/// <param name="rowValues"></param>
/// <param name="record"></param>
private bool UpdateColumnValuesInRecord(string[] rowValues, IRecord record, bool isResolvedForeignKeysChecked)
{
int j = 0;
bool isRecordUpdated = false;
ColumnCount = 1;
foreach (string data in rowValues)
{
ColumnCount++;
if (j > this.ImportList.Count - 1)
{
return(isRecordUpdated);
}
try
{
if (this.ColumnNameList[j].ToString() != "" && ((CheckBox)this.ImportList[j]).Checked)
{
ForeignKey fkColumn = null;
BaseColumn currentColumn = this.DBTable.TableDefinition.ColumnList.GetByAnyName((string)this.ColumnNameList[j]);
if (isResolvedForeignKeysChecked)
{
fkColumn = this.DBTable.TableDefinition.GetForeignKeyByColumnName(currentColumn.InternalName);
}
String colValue = "";
// Check if the foreign key has DFKA. If so, then check the calue from csv file agains the DFKA column in the parent/foreign key table.
// If a match is found retrieve its ID and set that as value to be insterted in the current table where you are adding records.
if (fkColumn != null)
{
TableDefinition originalTableDef = fkColumn.PrimaryKeyTableDefinition;
BaseTable originalBaseTable = originalTableDef.CreateBaseTable();
WhereClause wc = null;
ArrayList records = new ArrayList();
BaseColumn pkColumn = (BaseColumn)originalTableDef.PrimaryKey.Columns[0];//Index is zero because we handle only those tables which has single PK column not composite keys.
if (fkColumn.PrimaryKeyDisplayColumns != null && fkColumn.PrimaryKeyDisplayColumns != "" && (!fkColumn.PrimaryKeyDisplayColumns.Trim().StartsWith("=")))
{
wc = new WhereClause(originalTableDef.ColumnList.GetByAnyName(fkColumn.PrimaryKeyDisplayColumns), BaseFilter.ComparisonOperator.EqualsTo, data);
}
else if (fkColumn.PrimaryKeyDisplayColumns != null && fkColumn.PrimaryKeyDisplayColumns != "" && (fkColumn.PrimaryKeyDisplayColumns.Trim().StartsWith("=")))
{
string primaryKeyDisplay = GetDFKA(fkColumn);
if (primaryKeyDisplay != null)
{
wc = new WhereClause(originalTableDef.ColumnList.GetByAnyName(primaryKeyDisplay), BaseFilter.ComparisonOperator.EqualsTo, data);
}
else
{
wc = new WhereClause(pkColumn, BaseFilter.ComparisonOperator.EqualsTo, data);
}
}
else
{
// if the foreign key does not have DFKA then just check in the foreign key table if the id exists. If not create a record with the specified ID
// before adding to current table
wc = new WhereClause(pkColumn, BaseFilter.ComparisonOperator.EqualsTo, data);
}
BaseClasses.Data.BaseFilter join = null;
records = originalBaseTable.GetRecordList(join, wc.GetFilter(), null, null, 0, 100);
if (records.Count > 0)
{
// take the first record and retrieve its ID.
BaseRecord rec = (BaseRecord)records[0];
colValue = (rec.GetValue(pkColumn)).ToString();
}
else
{
// IF there is not match found then you have to create a record in the foreign key table with DFKA value and then retreive its ID
if (data != null & data != "")
{
IRecord tempRec;
if (fkColumn.PrimaryKeyDisplayColumns != null && fkColumn.PrimaryKeyDisplayColumns != "" && (!fkColumn.PrimaryKeyDisplayColumns.Trim().StartsWith("=")))
{
tempRec = originalBaseTable.CreateRecord();
TableDefinition tableDef = originalBaseTable.TableDefinition;
foreach (BaseColumn newCol in tableDef.Columns)
{
if (fkColumn.PrimaryKeyDisplayColumns == newCol.InternalName)
{
tempRec.SetValue(data, newCol.UniqueName);
}
}
}
else
{
tempRec = originalBaseTable.CreateRecord(data);
}
tempRec.Save();
colValue = (tempRec.GetValue(pkColumn)).ToString();
}
// colValue = data;
}
}
else
{
colValue = data;
}
// set the table row's column for value
record.SetValue(colValue, currentColumn.UniqueName);
isRecordUpdated = true;
}
j++;
}
catch (Exception ex)
{
throw new Exception(ex.InnerException.Message);
}
}
return(isRecordUpdated);
}
protected internal object GetValueForExcelExport(ExcelColumn col, BaseRecord rec)
{
object val = "";
bool isNull = false;
decimal deciNumber = default(decimal);
if (col == null)
{
return(null);
}
//DFKA value is evaluated in the <tablename>ExportExcelButton_Click method in the <tablename>.controls file
//if (col.DisplayColumn.TableDefinition.IsExpandableNonCompositeForeignKey(col.DisplayColumn))
//{
// // Foreign Key column, so we will use DFKA and String type.
// val = rec.Format(col.DisplayColumn);
//}
//else
//{
switch (col.DisplayColumn.ColumnType)
{
case BaseColumn.ColumnTypes.Number:
case BaseColumn.ColumnTypes.Percentage:
case BaseColumn.ColumnTypes.Currency:
ColumnValue numVal = rec.GetValue(col.DisplayColumn);
//If the value of the column to be exported is nothing, add an empty cell to the Excel file
if (numVal.IsNull)
{
isNull = true;
}
else
{
deciNumber = numVal.ToDecimal();
val = deciNumber;
}
break;
case BaseColumn.ColumnTypes.Date:
ColumnValue dateVal = rec.GetValue(col.DisplayColumn);
if (dateVal.IsNull)
{
isNull = true;
}
else
{
// Specify the default Excel format for the date field
// val = rec.Format(col.DisplayColumn, "s");
// val += ".000";
val = rec.GetValue(col.DisplayColumn).Value;
}
break;
case BaseColumn.ColumnTypes.Very_Large_String:
val = rec.GetValue(col.DisplayColumn).ToString();
break;
case BaseColumn.ColumnTypes.Boolean:
val = rec.Format(col.DisplayColumn);
break;
default:
val = rec.Format(col.DisplayColumn);
break;
}
//}
if (isNull)
{
return(null);
}
else
{
return(val);
}
}
protected internal string GetValueForExcelExport(ExcelColumn col, BaseRecord rec)
{
String val = "";
bool isNull = false;
decimal deciNumber;
if (col == null)
return null;
//DFKA value is evaluated in the <tablename>ExportExcelButton_Click method in the <tablename>.controls file
//if (col.DisplayColumn.TableDefinition.IsExpandableNonCompositeForeignKey(col.DisplayColumn))
//{
// // Foreign Key column, so we will use DFKA and String type.
// val = rec.Format(col.DisplayColumn);
//}
//else
//{
switch (col.DisplayColumn.ColumnType)
{
case BaseColumn.ColumnTypes.Number:
case BaseColumn.ColumnTypes.Percentage:
case BaseColumn.ColumnTypes.Currency:
ColumnValue numVal = rec.GetValue(col.DisplayColumn);
//If the value of the column to be exported is nothing, add an empty cell to the Excel file
if (numVal.IsNull)
{
isNull = true;
}
else
{
deciNumber = numVal.ToDecimal();
val = deciNumber.ToString(System.Globalization.CultureInfo.InvariantCulture);
if (col.DisplayColumn.ColumnType == BaseColumn.ColumnTypes.Currency)
{
val = rec.Format(col.DisplayColumn, "c");
}
else if (col.DisplayColumn.ColumnType == BaseColumn.ColumnTypes.Percentage)
{
val = rec.Format(col.DisplayColumn, "F2");
} else if (col.DisplayFormat == "Standard")
{
val = rec.Format(col.DisplayColumn, "F2");
}
}
break;
case BaseColumn.ColumnTypes.Date:
ColumnValue dateVal = rec.GetValue(col.DisplayColumn);
if (dateVal.IsNull)
{
isNull = true;
}
else
{ // Specify the default Excel format for the date field
// val = rec.Format(col.DisplayColumn, "s");
// val += ".000";
val = rec.Format(col.DisplayColumn, "d");
}
break;
case BaseColumn.ColumnTypes.Very_Large_String:
val = rec.GetValue(col.DisplayColumn).ToString();
break;
case BaseColumn.ColumnTypes.Boolean:
val = rec.Format(col.DisplayColumn);
break;
default:
val = rec.Format(col.DisplayColumn);
break;
}
//}
if (isNull)
return null;
else
return val;
}