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; } }