internal static IEnumerable <TValue> DistinctValues <TValue>(this SPField field, TValue def) { var res = new List <TValue>(); SPSecurity.RunWithElevatedPrivileges( () => { var siteId = field.ParentList.ParentWeb.Site.ID; string connectionString; using (var site = new SPSite(siteId)) { connectionString = site.ContentDatabase.DatabaseConnectionString; } var colName = field.AttributeValue("ColName"); var ordinal = field.AttributeValueInteger("RowOrdinal"); var listId = field.ParentList.ID; using (var cmd = new SqlCommand { CommandType = CommandType.Text }) { cmd.CommandText = FieldDistinctValues.Replace("%SqlColName%", colName); cmd.Parameters.Add(new SqlParameter("@ListId", listId)); cmd.Parameters.Add(new SqlParameter("@RowOrdinal", ordinal)); using (var con = new SqlConnection(connectionString)) { cmd.Connection = con; con.Open(); var reader = cmd.ExecuteReader(); while (reader != null && reader.Read()) { res.Add(reader[0] is TValue ? (TValue)reader[0] : def); } } if (cmd.Connection.State != ConnectionState.Closed) { cmd.Connection.Close(); } } }); if (field.Type == SPFieldType.MultiChoice && typeof(TValue) == typeof(string)) { var vals = res.SelectMany(x => x.ToString().Split(new[] { ";#" }, StringSplitOptions.RemoveEmptyEntries)) .Distinct().Select(x => (TValue)Convert.ChangeType(x, typeof(TValue))).OrderBy(x => x); res = vals.ToList(); } return(res); }
internal static IEnumerable <DateTime> DistinctDateTimeValues(this SPField field, DateTime def) { var res = new List <DateTime>(); SPSecurity.RunWithElevatedPrivileges( () => { var siteId = field.ParentList.ParentWeb.Site.ID; string connectionString; using (var site = new SPSite(siteId)) { connectionString = site.ContentDatabase.DatabaseConnectionString; } var colName = string.Format("DATEADD(dd, 0, DATEDIFF(dd, 0, {0}))", field.AttributeValue("ColName")); var ordinal = field.AttributeValueInteger("RowOrdinal"); var listId = field.ParentList.ID; using (var cmd = new SqlCommand { CommandType = CommandType.Text }) { cmd.CommandText = FieldDistinctValues.Replace("%SqlColName%", colName); cmd.Parameters.Add(new SqlParameter("@ListId", listId)); cmd.Parameters.Add(new SqlParameter("@RowOrdinal", ordinal)); using (var con = new SqlConnection(connectionString)) { cmd.Connection = con; con.Open(); var reader = cmd.ExecuteReader(); while (reader != null && reader.Read()) { res.Add(reader[0] is DateTime ? (DateTime)reader[0] : def); } } if (cmd.Connection.State != ConnectionState.Closed) { cmd.Connection.Close(); } } }); return(res); }