private IEnumerable <ReferenceItem> GetReferenceItemsFromSource(ReferenceType type, bool isBrokerEnabled = true)
{
var referenceItems = new List <ReferenceItem>();
var schemaInfo = new ReferenceTypeSchemaInfo(type, _connectionString);
using (var conn = GetConnection())
using (var dataReader = GetDataReader(schemaInfo, conn, isBrokerEnabled))
{
while (dataReader.Read())
{
var referenceItem = new ReferenceItem();
for (var o = 0; o < dataReader.FieldCount; o++)
{
PopulateOrdinalValue(dataReader, o, referenceItem, schemaInfo);
}
if (referenceItem.StartDate == DateTime.MinValue)
{
referenceItem.StartDate = DateTime.Now.AddDays(-1);
}
referenceItems.Add(referenceItem);
}
return(referenceItems);
}
}
private SqlDataReader GetDataReader(ReferenceTypeSchemaInfo schemaInfo, SqlConnection conn,
bool isBrokerEnabled)
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var cmd = new SqlCommand(schemaInfo.GetReferenceTableSql(), conn);
if (isBrokerEnabled)
{
var dependency = new SqlDependency(cmd);
dependency.OnChange += (sender, e) =>
{
var ck = GetCacheKey(schemaInfo.Type);
_referenceCache.Remove(ck);
cmd.Dispose();
};
}
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
try
{
return(cmd.ExecuteReader());
}
catch (InvalidOperationException opEx)
{
if (!opEx.Message.Contains("SqlDependency.Start()"))
{
throw;
}
StartSqlDependencyService();
return(cmd.ExecuteReader());
}
}
}
private void PopulateOrdinalValue(IDataRecord record, int ordinal, ReferenceItem referenceItem,
ReferenceTypeSchemaInfo schemaInfo)
{
var cName = record.GetName(ordinal);
if (cName.Equals(schemaInfo.PrimaryKeyName, StringComparison.OrdinalIgnoreCase))
{
referenceItem.Id = record.GetInt32(ordinal);
}
if (cName.Equals(schemaInfo.DescriptionColumnName, StringComparison.OrdinalIgnoreCase))
{
referenceItem.Description = record.IsDBNull(ordinal) ? string.Empty : record.GetString(ordinal);
}
if (schemaInfo.FlagColumns.Contains(cName) && !referenceItem.Flags.ContainsKey(cName))
{
referenceItem.Flags.Add(record.GetName(ordinal), record.GetBoolean(ordinal));
}
switch (record.GetName(ordinal).ToLower())
{
case "id":
referenceItem.Id = record.GetInt32(ordinal);
break;
case "code":
referenceItem.Code = record.IsDBNull(ordinal) ? string.Empty : record[ordinal].ToString();
break;
case "description":
referenceItem.Description = record.IsDBNull(ordinal) ? string.Empty : record.GetString(ordinal);
break;
case "abbreviation":
referenceItem.Abbreviation = record.IsDBNull(ordinal) ? string.Empty : record.GetString(ordinal);
break;
case "displayorder":
referenceItem.DisplayOrder = record.IsDBNull(ordinal) ? null : (int?)record.GetInt32(ordinal);
break;
case "startdate":
referenceItem.StartDate = record.GetDateTime(ordinal);
break;
case "enddate":
referenceItem.EndDate = record.IsDBNull(ordinal) ? null : (DateTime?)record.GetDateTime(ordinal);
break;
case "isselectable":
referenceItem.IsSelectable = record.IsDBNull(ordinal) || record.GetBoolean(ordinal);
break;
case "parent":
referenceItem.Parent = record.IsDBNull(ordinal) ? string.Empty : record[ordinal].ToString();
break;
// Catch all for every other column that may exist.
default:
var name = record.GetName(ordinal);
if (cName.Equals(schemaInfo.PrimaryKeyName, StringComparison.OrdinalIgnoreCase) ||
cName.Equals(schemaInfo.DescriptionColumnName, StringComparison.OrdinalIgnoreCase) ||
cName.Equals("startdate", StringComparison.OrdinalIgnoreCase))
{
break;
}
var value = record.IsDBNull(ordinal) ? string.Empty : record[ordinal].ToString();
if (referenceItem.OtherColumns.ContainsKey(name))
{
break;
}
referenceItem.OtherColumns.Add(name, value);
break;
}
}
