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 Dictionary <int, string> DistinctUserValues(this SPField field)
{
var res = new Dictionary <int, string>();
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 = FieldDistinctUserValues.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())
{
try
{
res.Add((int)reader[0], (string)reader[1]);
}
catch { /*TODO*/ }
}
}
if (cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
}
}
});
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);
}