internal static Dictionary <int, string> DistinctMultiLookupValues(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 list = field.ParentList;
var web = list.ParentWeb;
var lookupListId = new Guid(field.AttributeValue("List"));
var lookupFieldName = field.AttributeValue("ShowField");
var lookupList = web.Lists[lookupListId];
var lookupField = lookupList.Fields[lookupFieldName];
var lookupFieldColName = lookupField.AttributeValue("ColName");
var listId = field.ParentList.ID;
using (var cmd = new SqlCommand {
CommandType = CommandType.Text
})
{
cmd.CommandText = FieldDistinctMultiLookupValues
.Replace("%SqlColName%", (string.IsNullOrEmpty(lookupFieldColName) ? "nvarchar1" : lookupFieldColName));
cmd.Parameters.Add(new SqlParameter("@ListId", listId));
cmd.Parameters.Add(new SqlParameter("@FieldId", field.Id));
using (var con = new SqlConnection(connectionString))
{
cmd.Connection = con;
con.Open();
var reader = cmd.ExecuteReader();
while (reader != null && reader.Read())
{
res.Add((int)reader[0], (string)reader[1]);
}
}
if (cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
}
}
});
return(res);
}
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);
}
internal static Guid SourceListId(this SPField field)
{
var attrVal = field.AttributeValue("List");
return(new Guid(attrVal));
}
public static bool IsMulti(this SPField field)
{
var attrVal = field.AttributeValue("Mult");
return(string.Compare(attrVal, "TRUE") == 0);
}
