//Get a list of records that match the criteria specified in a filter
public virtual ArrayList GetRecordList(
string userId,
BaseClasses.Data.BaseFilter filter,
BaseClasses.Data.OrderBy orderBy,
int pageNumber,
int batchSize,
ref int totalRows)
{
if (userId != null)
{
filter = BaseFilter.CombineFilters(
CompoundFilter.CompoundingOperators.And_Operator,
filter,
BaseFilter.CreateUserIdFilter(((IUserTable)this), userId));
}
BaseClasses.Data.BaseFilter join = null;
return(((BaseClasses.ITable) this).GetRecordList(join, filter, null, orderBy, pageNumber, batchSize, ref totalRows));
}
/// <summary>
/// This is a shared function that can be used to get a AreasRecord record using a where and order by clause.
/// </summary>
public static AreasRecord GetRecord(string where, OrderBy orderBy)
{
SqlFilter whereFilter = null;
if (where != null && where.Trim() != "")
{
whereFilter = new SqlFilter(where);
}
BaseClasses.Data.BaseFilter join = null;
ArrayList recList = AreasTable.Instance.GetRecordList(join, whereFilter, null, orderBy, BaseTable.MIN_PAGE_NUMBER, BaseTable.MIN_BATCH_SIZE);
AreasRecord rec = null;
if (recList.Count > 0)
{
rec = (AreasRecord)recList[0];
}
return(rec);
}
//Get a list of records that match the user's name/password
public virtual ArrayList GetRecordList(
string userName,
string userPassword,
BaseClasses.Data.BaseFilter filter,
BaseClasses.Data.OrderBy orderBy,
int pageNumber,
int batchSize,
ref int totalRows)
{
//Set up a name/password filter
if ((userName != null) || (userPassword != null))
{
filter = BaseFilter.CombineFilters(
CompoundFilter.CompoundingOperators.And_Operator,
filter,
BaseFilter.CreateUserAuthenticationFilter(((IUserIdentityTable)this), userName, userPassword));
}
BaseClasses.Data.BaseFilter join = null;
return(((BaseClasses.ITable) this).GetRecordList(join, filter, null, orderBy, pageNumber, batchSize, ref totalRows));
}
//Get a list of roles to which the user belongs
public string[] GetUserRoles()
{
string[] roles;
if ((this as BaseClasses.IUserRoleRecord) != null)
{
string role = ((BaseClasses.IUserRoleRecord) this).GetUserRole();
roles = new string[] { role };
}
else
{
BaseClasses.IUserRoleTable roleTable =
((BaseClasses.IUserIdentityTable) this.TableAccess).GetUserRoleTable();
if (roleTable == null)
{
return(null);
}
else
{
ColumnValueFilter filter = BaseFilter.CreateUserIdFilter(roleTable, this.GetUserId());
BaseClasses.Data.OrderBy order = new BaseClasses.Data.OrderBy(false, false);
BaseClasses.Data.BaseFilter join = null;
ArrayList roleRecords = roleTable.GetRecordList(
join,
filter,
null,
order,
BaseClasses.Data.BaseTable.MIN_PAGE_NUMBER,
BaseClasses.Data.BaseTable.MAX_BATCH_SIZE);
ArrayList roleList = new ArrayList(roleRecords.Count);
foreach (BaseClasses.IUserRoleRecord roleRecord in roleRecords)
{
roleList.Add(roleRecord.GetUserRole());
}
roles = (string[])roleList.ToArray(typeof(string));
}
}
return(roles);
}
/// <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);
}