CpContent class is used to retrieve Centralpoint content.
/// <remarks>
/// This class is used to retreive Centralpoint content.
/// </remarks>
public class CpContent : DataSet
{
private string _connectionString;
private SystemFilterOptions _systemFilterOptions;
private List<string> _relatedModules = null;
private List<string> _relatedKeywordLists = null;
private Dictionary<string, DataView> _views = null;
/// <summary>
/// This method is the default constructor.
/// </summary>
/// <param name="name">The name that uniquely identifies this object.</param>
public CpContent(string name)
: this(name, SystemFilterOptions.Enabled | SystemFilterOptions.Audience | SystemFilterOptions.Role | SystemFilterOptions.CurrentAndFutureDate)
{
}
/// <summary>
/// This method constructor used with a custom connection string.
/// </summary>
/// <param name="name">The name that uniquely identifies this object.</param>
/// <param name="options">A pipe separated list of system filter options.</param>
public CpContent(string name, SystemFilterOptions options)
: this(name, options, Management.Application.SelectConnectionString)
{
}
/// <summary>
/// This method constructor used with a custom connection string.
/// </summary>
/// <param name="name">The name that uniquely identifies this object.</param>
/// <param name="options">A pipe separated list of system filter options.</param>
/// <param name="connectionString">The select connection string.</param>
public CpContent(string name, SystemFilterOptions options, string connectionString)
{
this.DataSetName = name;
_systemFilterOptions = options;
_connectionString = connectionString;
}
/// <summary>
/// This method gets an audience and role specific item from the cache.
/// </summary>
/// <param name="name">The name that uniquely identifies this object.</param>
/// <param name="options">A pipe separated list of system filter options.</param>
/// <returns>The object that was stored in the cache.</returns>
public static CpContent GetCache(string name)
{
return CpContent.GetCache(name, SystemFilterOptions.Enabled | SystemFilterOptions.Audience | SystemFilterOptions.Role | SystemFilterOptions.CurrentAndFutureDate);
}
/// <summary>
/// This method gets an audience and role specific item from the cache.
/// </summary>
/// <param name="name">The name that uniquely identifies this object.</param>
/// <param name="options">A pipe separated list of system filter options.</param>
/// <returns>The object that was stored in the cache.</returns>
public static CpContent GetCache(string name, SystemFilterOptions options)
{
bool audienceSpecific = true, roleSpecific = true;
List<int> sfo = EnumReader.GetValuesFromEnum(typeof(SystemFilterOptions), (int)options);
if (!sfo.Contains((int)SystemFilterOptions.All))
{
if (sfo.Contains((int)SystemFilterOptions.None))
audienceSpecific = roleSpecific = false;
else
{
audienceSpecific = sfo.Contains((int)SystemFilterOptions.Audience);
roleSpecific = sfo.Contains((int)SystemFilterOptions.Role);
}
}
return CpCache.Get(name, audienceSpecific, roleSpecific) as CpContent;
}
/// <summary>
/// This method inserts an audience and role specific item into the cache.
/// </summary>
public void InsertCache()
{
this.InsertCache(null);
}
/// <summary>
/// This method inserts an audience and role specific item into the cache.
/// </summary>
/// <param name="absoluteExpiration">The time at which the inserted item is expired and removed from cache.</param>
public void InsertCache(DateTime absoluteExpiration)
{
this.InsertCache(null, absoluteExpiration);
}
/// <summary>
/// This method inserts an audience and role specific item into the cache.
/// </summary>
/// <param name="onRemoveCallback">A delegate that, if provided, will be called when an item is removed from cache.</param>
public void InsertCache(CacheItemRemovedCallback onRemoveCallback)
{
this.InsertCache(onRemoveCallback, Cache.NoAbsoluteExpiration);
}
/// <summary>
/// This method inserts an audience and role specific item into the cache.
/// </summary>
/// <param name="onRemoveCallback">A delegate that, if provided, will be called when an item is removed from cache.</param>
/// <param name="absoluteExpiration">The time at which the inserted item is expired and removed from cache.</param>
public void InsertCache(CacheItemRemovedCallback onRemoveCallback, DateTime absoluteExpiration)
{
bool audienceSpecific = true, roleSpecific = true;
List<int> sfo = EnumReader.GetValuesFromEnum(typeof(SystemFilterOptions), (int)this.SystemFilterOptions);
if (!sfo.Contains((int)SystemFilterOptions.All))
{
if (sfo.Contains((int)SystemFilterOptions.None))
audienceSpecific = roleSpecific = false;
else
{
audienceSpecific = sfo.Contains((int)SystemFilterOptions.Audience);
roleSpecific = sfo.Contains((int)SystemFilterOptions.Role);
}
}
CpCache.Insert(this.DataSetName, this, this.RelatedModules.ToArray(), this.RelatedKeywordLists.ToArray(), this.Tables.Contains("cpsys_Taxonomy"), false, false
, audienceSpecific, roleSpecific, onRemoveCallback, absoluteExpiration);
}
/// <summary>
/// This method gets the select connection string.
/// </summary>
public string ConnectionString
{
get { return _connectionString; }
}
/// <summary>
/// This property gets a list of related modules.
/// </summary>
public List<string> RelatedModules
{
get
{
if (_relatedModules == null) _relatedModules = new List<string>();
return _relatedModules;
}
}
/// <summary>
/// This property gets a list of related keyword lists.
/// </summary>
public List<string> RelatedKeywordLists
{
get
{
if (_relatedKeywordLists == null) _relatedKeywordLists = new List<string>();
return _relatedKeywordLists;
}
}
/// <summary>
/// This property gets or sets the default system filter options for all relations in the collection.
/// </summary>
public SystemFilterOptions SystemFilterOptions
{
get { return _systemFilterOptions; }
}
/// <summary>
/// Gets the system data view manager.
/// </summary>
private Dictionary<string, DataView> Views
{
get
{
if (_views == null) _views = new Dictionary<string, DataView>();
return _views;
}
}
#region readers
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReaderMax(string tableName, string fields)
{
return ExecuteReaderMax(tableName, fields, String.Empty);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReaderMax(string tableName, string fields, string orderBy)
{
return ExecuteReaderMax(tableName, fields, orderBy, String.Empty);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReaderMax(string tableName, string fields, string orderBy, string where)
{
return ExecuteReaderMax(tableName, fields, orderBy, where, String.Empty);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="joins">The joins clauses.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReaderMax(string tableName, string fields, string orderBy, string where, string joins)
{
return ExecuteReaderMax(tableName, fields, orderBy, where, joins, Management.Application.ExecuteConnectionString);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="joins">The joins clauses.</param>
/// <param name="connectionString">An execute connection string.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReaderMax(string tableName, string fields, string orderBy, string where, string joins, string connectionString)
{
// A using statement cannot be used here because this method returns a reader and the connection must be open when it returns. Exception handling has been added when the connection is opened.
return ExecuteReaderMax(tableName, fields, orderBy, where, joins, new SqlConnection(connectionString));
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="joins">The joins clauses.</param>
/// <param name="connection">An execute connection object.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReaderMax(string tableName, string fields, string orderBy, string where, string joins, SqlConnection connection)
{
return ExecuteReaderMax(tableName, fields, orderBy, where, joins, new SqlCommand(String.Empty, connection));
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="joins">The joins clauses.</param>
/// <param name="command">An execute command object.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReaderMax(string tableName, string fields, string orderBy, string where, string joins, SqlCommand command)
{
string max = Management.Application.Properties.Get("ExecuteReaderMax");
return CpContent.ExecuteReader(1, Convert.ToInt32(max), tableName, fields, orderBy, where, joins, command, Convert.ToInt32(max));
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReader(int pageIndex, int pageSize, string tableName, string fields)
{
return CpContent.ExecuteReader(pageIndex, pageSize, tableName, fields, String.Empty);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReader(int pageIndex, int pageSize, string tableName, string fields, string orderBy)
{
return CpContent.ExecuteReader(pageIndex, pageSize, tableName, fields, orderBy, String.Empty);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReader(int pageIndex, int pageSize, string tableName, string fields, string orderBy, string where)
{
return CpContent.ExecuteReader(pageIndex, pageSize, tableName, fields, orderBy, where, String.Empty);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="joins">The joins clauses.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReader(int pageIndex, int pageSize, string tableName, string fields, string orderBy, string where, string joins)
{
return CpContent.ExecuteReader(pageIndex, pageSize, tableName, fields, orderBy, where, joins, Management.Application.ExecuteConnectionString);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="joins">The joins clauses.</param>
/// <param name="connectionString">An execute connection string.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReader(int pageIndex, int pageSize, string tableName, string fields, string orderBy, string where, string joins, string connectionString)
{
// A using statement cannot be used here because this method returns a reader and the connection must be open when it returns. Exception handling has been added when the connection is opened.
return CpContent.ExecuteReader(pageIndex, pageSize, tableName, fields, orderBy, where, joins, new SqlConnection(connectionString));
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="joins">The joins clauses.</param>
/// <param name="connection">An execute connection object.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReader(int pageIndex, int pageSize, string tableName, string fields, string orderBy, string where, string joins, SqlConnection connection)
{
return CpContent.ExecuteReader(pageIndex, pageSize, tableName, fields, orderBy, where, joins, new SqlCommand(String.Empty, connection));
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="joins">The joins clauses.</param>
/// <param name="command">An execute command object.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReader(int pageIndex, int pageSize, string tableName, string fields, string orderBy, string where, string joins, SqlCommand command)
{
return CpContent.ExecuteReader(pageIndex, pageSize, tableName, fields, orderBy, where, joins, command, 0);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="joins">The joins clauses.</param>
/// <param name="command">An execute command object.</param>
/// <param name="limit">The maximum number of records to return.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteReader(int pageIndex, int pageSize, string tableName, string fields, string orderBy, string where, string joins, SqlCommand command, int limit)
{
if (String.IsNullOrEmpty(tableName)) throw new Exception("You cannot execute a reader with no table name.");
if (String.IsNullOrEmpty(fields)) throw new Exception("You cannot execute a reader with no fields.");
if (fields.Contains("*")) throw new Exception("You cannot execute a reader with a star (*) in the fields clause.");
if (fields.StartsWith("top ", StringComparison.CurrentCultureIgnoreCase)) throw new Exception("You cannot execute a reader with TOP in the fields clause, instead use the limit parameter.");
command.CommandText = "cpsys_DataCurrent_Select";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int));
int i = 0; command.Parameters[i].Value = pageIndex;
command.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int));
i++; command.Parameters[i].Value = pageSize;
command.Parameters.Add(new SqlParameter("@Limit", SqlDbType.Int));
i++; command.Parameters[i].Value = limit;
SqlParameter output = new SqlParameter("@TotalRecords", SqlDbType.Int);
output.Direction = ParameterDirection.Output;
i++; command.Parameters.Add(output);
command.Parameters.Add(new SqlParameter("@Table", SqlDbType.NVarChar));
i++; command.Parameters[i].Value = tableName;
command.Parameters.Add(new SqlParameter("@Fields", SqlDbType.NVarChar));
i++; command.Parameters[i].Value = fields;
if (!String.IsNullOrEmpty(where))
{
command.Parameters.Add(new SqlParameter("@Where", SqlDbType.NVarChar));
i++; command.Parameters[i].Value = where;
}
if (!String.IsNullOrEmpty(orderBy))
{
command.Parameters.Add(new SqlParameter("@OrderBy", SqlDbType.NVarChar));
i++; command.Parameters[i].Value = orderBy;
}
if (!String.IsNullOrEmpty(joins))
{
command.Parameters.Add(new SqlParameter("@Joins", SqlDbType.NVarChar));
i++; command.Parameters[i].Value = joins;
}
if (command.Connection == null) command.Connection = new SqlConnection();
bool opened = false;
try
{
CommandBehavior behavior = CommandBehavior.Default;
if (command.Connection.State == ConnectionState.Closed)
{
if (String.IsNullOrEmpty(command.Connection.ConnectionString))
command.Connection.ConnectionString = Management.Application.ExecuteConnectionString;
opened = true;
behavior = CommandBehavior.CloseConnection;
//HttpContext.Current.Trace.Warn("opened", opened.ToString());
//HttpContext.Current.Trace.Warn("behavior", behavior.ToString());
command.Connection.Open();
}
return command.ExecuteReader(behavior);
}
catch
{
Management.ExtendedError.AddItemNote("ExecuteReader.Connection.State", ((command == null) || (command.Connection == null)) ? "Command|Connection is null" : command.Connection.State.ToString());
if (opened) command.Connection.Close();
throw;
}
}
#endregion
#region tables
/// <summary>
/// This method adds a DataTable to the DataSet.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="commandText">The SQL command used to query the database.</param>
/// <returns>The DataTable created using the command text.</returns>
public async Task<DataTable> AddTableAsync(string tableName, string commandText)
{
DataTable dt = await CpContent.GetTableAsync(tableName, commandText, this.ConnectionString);
this.Tables.Add(dt);
return dt;
}
/// <summary>
/// This method adds a DataTable to the DataSet.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="commandText">The SQL command used to query the database.</param>
/// <returns>The DataTable created using the command text.</returns>
public DataTable AddTable(string tableName, string commandText)
{
DataTable dt = CpContent.GetTable(tableName, commandText, this.ConnectionString);
this.Tables.Add(dt);
return dt;
}
/// <summary>
/// This method gets a DataTable from the database.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="commandText">The SQL command used to query the database.</param>
/// <returns>The DataTable created using the command text.</returns>
public async static Task<DataTable> GetTableAsync(string tableName, string commandText)
{
return await CpContent.GetTableAsync(tableName, commandText, Management.Application.SelectConnectionString);
}
/// <summary>
/// This method gets a DataTable from the database.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="commandText">The SQL command used to query the database.</param>
/// <param name="connectionString">The select connection string.</param>
/// <returns>The DataTable created using the command text.</returns>
public async static Task<DataTable> GetTableAsync(string tableName, string commandText, string connectionString)
{
return await Utilities.Database.GetDataTableAsync(tableName, commandText, connectionString);
}
/// <summary>
/// This method gets a DataTable from the database.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="commandText">The SQL command used to query the database.</param>
/// <returns>The DataTable created using the command text.</returns>
public static DataTable GetTable(string tableName, string commandText)
{
return CpContent.GetTable(tableName, commandText, Management.Application.SelectConnectionString);
}
/// <summary>
/// This method gets a DataTable from the database.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="commandText">The SQL command used to query the database.</param>
/// <param name="connectionString">The select connection string.</param>
/// <returns>The DataTable created using the command text.</returns>
public static DataTable GetTable(string tableName, string commandText, string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
return CpContent.GetTable(tableName, commandText, connection);
}
}
/// <summary>
/// This method gets a DataTable from the database.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="commandText">The SQL command used to query the database.</param>
/// <param name="connection">A select connection object.</param>
/// <returns>The DataTable created using the command text.</returns>
public static DataTable GetTable(string tableName, string commandText, SqlConnection connection)
{
if (String.IsNullOrEmpty(tableName)) throw new Exception("You cannot execute a reader with no table name.");
if (String.IsNullOrEmpty(commandText)) throw new Exception("You cannot execute a reader with no command text.");
HttpContext.Current.Trace.Write("GetTable: Begin", commandText);
DataTable dt = new DataTable(tableName);
bool opened = false;
try
{
if (connection.State == ConnectionState.Closed)
{
if (String.IsNullOrEmpty(connection.ConnectionString))
connection.ConnectionString = Management.Application.SelectConnectionString;
opened = true;
//HttpContext.Current.Trace.Warn("opened", opened.ToString());
connection.Open();
}
SqlDataAdapter adapter = new SqlDataAdapter(commandText, connection);
HealthMonitor.Instance.RecordNote($"CpContent GetTable adapter fill start. {DateTime.Now}");
adapter.Fill(dt);
HealthMonitor.Instance.RecordNote($"CpContent GetTable adapter fill end. {DateTime.Now}");
}
finally
{
if (opened) connection.Close();
}
return dt;
}
#endregion
#region views
/// <summary>
/// This method initializes the default view for the given table.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView DefaultView(string tableName, string sort)
{
return this.DefaultView(tableName, sort, String.Empty);
}
/// <summary>
/// This method initializes the default view for the given table.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <param name="rowFilter">The row filter to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView DefaultView(string tableName, string sort, string rowFilter)
{
return this.DefaultView(tableName, sort, rowFilter, DataViewRowState.OriginalRows);
}
/// <summary>
/// This method initializes the default view for the given table.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <param name="rowFilter">The row filter to be applied to the DataTable.</param>
/// <param name="rowState">The state of the included rows.</param>
/// <returns>A DataView.</returns>
public DataView DefaultView(string tableName, string sort, string rowFilter, DataViewRowState rowState)
{
DataView dv = this.Tables[tableName].DefaultView;
dv.RowFilter = rowFilter;
dv.Sort = sort;
dv.RowStateFilter = rowState;
return dv;
}
/// <summary>
/// This method adds a view to the list of views.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <param name="tableName">The name of the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView AddView(string viewName, string tableName)
{
return this.AddView(viewName, tableName, String.Empty);
}
/// <summary>
/// This method adds a view to the list of views.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView AddView(string viewName, string tableName, string sort)
{
return this.AddView(viewName, tableName, sort, String.Empty);
}
/// <summary>
/// This method adds a view to the list of views.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <param name="rowFilter">The row filter to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView AddView(string viewName, string tableName, string sort, string rowFilter)
{
return this.AddView(viewName, tableName, sort, rowFilter, DataViewRowState.OriginalRows);
}
/// <summary>
/// This method adds a view to the list of views.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <param name="rowFilter">The row filter to be applied to the DataTable.</param>
/// <param name="rowState">The state of the included rows.</param>
/// <returns>A DataView.</returns>
public DataView AddView(string viewName, string tableName, string sort, string rowFilter, DataViewRowState rowState)
{
DataView dv = this.NewView(tableName, sort, rowFilter, rowState);
this.AddView(viewName, dv);
return dv;
}
/// <summary>
/// This method adds a view to the list of views.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <param name="dv">The DataView to be added.</param>
public void AddView(string viewName, DataView dv)
{
this.Views.Add(viewName, dv);
}
/// <summary>
/// This method generates a DataView using the DataSet.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView NewView(string tableName)
{
return this.NewView(tableName, String.Empty);
}
/// <summary>
/// This method generates a DataView using the DataSet.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView NewView(string tableName, string sort)
{
return this.NewView(tableName, sort, String.Empty);
}
/// <summary>
/// This method generates a DataView using the DataSet.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <param name="rowFilter">The row filter to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView NewView(string tableName, string sort, string rowFilter)
{
return this.NewView(tableName, sort, rowFilter, DataViewRowState.OriginalRows);
}
/// <summary>
/// This method generates a DataView using the DataSet.
/// </summary>
/// <param name="tableName">The name of the DataTable.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <param name="rowFilter">The row filter to be applied to the DataTable.</param>
/// <param name="rowState">The state of the included rows.</param>
/// <returns>A DataView.</returns>
public DataView NewView(string tableName, string sort, string rowFilter, DataViewRowState rowState)
{
return new DataView(this.Tables[tableName], rowFilter, sort, rowState);
}
/// <summary>
/// This method view from the current list of views.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <returns>A DataView.</returns>
public DataView View(string viewName)
{
return (this.Views.ContainsKey(viewName)) ? this.Views[viewName] : null;
}
#endregion
#region filters
/// <summary>
/// This method builds a filter based upon a DataId attribute.
/// </summary>
/// <param name="attribute">The system name of the data list box attribute in the module.</param>
/// <param name="query">A SQL query returning a single DataId column. The query should return only records that should exist in the attribute.</param>
/// <param name="moduleId">The module to which the field relates and the attribute is in. Passing the empty GUID will exclude the module filter.</param>
/// <param name="isReversed">Whether to select the DataId from the lookup table or AttributeDataId. This property should be set to true when the AttributeSystemName is not in the module from which the parent query is pulling.</param>
/// <param name="field">The name of the field that is being filtered against. It must contain a DataId field and may include a table name prefix. Passing the empty string will default to DataId.</param>
/// <param name="isScalar">Should be true if the query only returns one row (DataId) as with a TOP 1 clause.</param>
/// <returns>A SQL filter to include only records where the attribute contains the provided DataIds.</returns>
public static string DataIdAttributeFilter(string attribute, string query, Guid moduleId = default(Guid), bool isReversed = false, string field = "", bool isScalar = false)
{
if (String.IsNullOrWhiteSpace(query))
throw new ArgumentException("The query parameter must contain a SQL statement returning one DataId column.");
string module = (moduleId == Guid.Empty) ? "" : "(ModuleId = '" + moduleId.ToString() + "') AND ";
string selectField = "DataId", filterField = "AttributeDataId";
if (isReversed) { selectField = "AttributeDataId"; filterField = "DataId"; }
if (String.IsNullOrWhiteSpace(field)) field = "DataId";
if (isScalar)
return String.Format("({0} IN (SELECT {4} FROM cpsys_DataCurrentInDataCurrent WHERE {1}(AttributeSystemName = N'{2}') AND ({5} = ({3}))))", field, module, attribute.SqlEncode(), query, selectField, filterField);
else
return String.Format("({0} IN (SELECT {4} FROM cpsys_DataCurrentInDataCurrent WHERE {1}(AttributeSystemName = N'{2}') AND ({5} IN ({3}))))", field, module, attribute.SqlEncode(), query, selectField, filterField);
}
/// <summary>
/// This method builds a filter based upon a DataId attribute.
/// </summary>
/// <param name="attribute">The system name of the data list box attribute in the module.</param>
/// <param name="moduleId">The module to which the field relates and the attribute is in. Passing the empty GUID will exclude the module filter.</param>
/// <param name="dataIds">A list of DataIds that should exist in the attribute.</param>
/// <returns>A SQL filter to include only records where the attribute contains the provided DataIds.</returns>
public static string DataIdAttributeFilter(string attribute, Guid moduleId, params Guid[] dataIds)
{
return DataIdAttributeFilter(attribute, moduleId, false, "", dataIds);
}
/// <summary>
/// This method builds a filter based upon a DataId attribute.
/// </summary>
/// <param name="attribute">The system name of the data list box attribute in the module.</param>
/// <param name="moduleId">The module to which the field relates and the attribute is in. Passing the empty GUID will exclude the module filter.</param>
/// <param name="isReversed">Whether to select the DataId from the lookup table or AttributeDataId. This property should be set to true when the AttributeSystemName is not in the module from which the parent query is pulling.</param>
/// <param name="field">The name of the field that is being filtered against. It must contain a DataId field and may include a table name prefix. Passing the empty string will default to DataId.</param>
/// <param name="dataIds">A list of DataIds that should exist in the attribute.</param>
/// <returns>A SQL filter to include only records where the attribute contains the provided DataIds.</returns>
public static string DataIdAttributeFilter(string attribute, Guid moduleId, bool isReversed, string field, params Guid[] dataIds)
{
string module = (moduleId == Guid.Empty) ? "" : "(ModuleId = '" + moduleId.ToString() + "') AND ";
string selectField = "DataId", filterField = "AttributeDataId";
if (isReversed) { selectField = "AttributeDataId"; filterField = "DataId"; }
if (String.IsNullOrWhiteSpace(field)) field = "DataId";
switch (dataIds.Length)
{
case 0:
return "(" + field + " IS NULL)"; // return no records when you filter to find records that are in an empty list
case 1:
return String.Format("({0} IN (SELECT {4} FROM cpsys_DataCurrentInDataCurrent WHERE {1}(AttributeSystemName = N'{2}') AND ({5} = '{3}')))", field, module, attribute.SqlEncode(), dataIds[0], selectField, filterField);
default:
return String.Format("({0} IN (SELECT {4} FROM cpsys_DataCurrentInDataCurrent WHERE {1}(AttributeSystemName = N'{2}') AND ({5} IN ('{3}'))))", field, module, attribute.SqlEncode(), String.Join("', '", dataIds), selectField, filterField);
}
}
public static string UserIdAttributeFilter(string attribute, Guid moduleId, bool isReversed, string field, params Guid[] userIds)
{
var module = moduleId == Guid.Empty ? "" : $"(ModuleId = '{moduleId}') AND ";
string selectField = "DataId", filterField = "AttributeUserId";
if (isReversed) { selectField = "AttributeUserId"; filterField = "DataId"; }
if (String.IsNullOrWhiteSpace(field)) field = "DataId";
switch (userIds.Length)
{
case 0:
return "(" + field + " IS NULL)"; // return no records when you filter to find records that are in an empty list
case 1:
return String.Format("({0} IN (SELECT {4} FROM cpsys_UsersInDataCurrentAttributes WHERE {1}(AttributeSystemName = N'{2}') AND ({5} = '{3}')))", field, module, attribute.SqlEncode(), userIds[0], selectField, filterField);
default:
return String.Format("({0} IN (SELECT {4} FROM cpsys_UsersInDataCurrentAttributes WHERE {1}(AttributeSystemName = N'{2}') AND ({5} IN ('{3}'))))", field, module, attribute.SqlEncode(), String.Join("', '", userIds), selectField, filterField);
}
}
/// <summary>
/// This mthod builds a taxonomy filter.
/// </summary>
/// <param name="taxonomyId">The id of the taxonomy to find.</param>
/// <returns>A SQL where clause used to filter by the given taxonomy.</returns>
public static string TaxonomyFilter(Guid taxonomyId)
{
return CpContent.TaxonomyFilter(taxonomyId, true);
}
/// <summary>
/// This mthod builds a taxonomy filter.
/// </summary>
/// <param name="taxonomyId">The id of the taxonomy to find.</param>
/// <param name="includeDescendants">Whether the search should include data in descendant taxonomy items.</param>
/// <returns>A SQL where clause used to filter by the given taxonomy.</returns>
public static string TaxonomyFilter(Guid taxonomyId, bool includeDescendants)
{
return CpContent.TaxonomyFilter(taxonomyId, includeDescendants, String.Empty);
}
/// <summary>
/// This method builds a taxonomy filter.
/// </summary>
/// <param name="taxonomyId">The id of the taxonomy to find.</param>
/// <param name="includeDescendants">Whether the search should include data in descendant taxonomy items.</param>
/// <param name="tableName">The table name where the filter is being applied.</param>
/// <returns>A SQL where clause used to filter by the given taxonomy.</returns>
public static string TaxonomyFilter(Guid taxonomyId, bool includeDescendants, string tableName)
{
return TaxonomyFilter(new Guid[] { taxonomyId }, includeDescendants, tableName);
}
/// <summary>
/// This mthod builds a taxonomy filter.
/// </summary>
/// <param name="taxonomyIds">The ids of the taxonomy to find.</param>
/// <returns>A SQL where clause used to filter by the given taxonomy.</returns>
public static string TaxonomyFilter(Guid[] taxonomyId)
{
return CpContent.TaxonomyFilter(taxonomyId, false, String.Empty);
}
/// <summary>
/// This method builds a taxonomy filter.
/// </summary>
/// <param name="taxonomyIds">The ids of the taxonomy to find.</param>
/// <param name="includeDescendants">Whether the search should include data in descendant taxonomy items.</param>
/// <param name="tableName">The table name where the filter is being applied.</param>
/// <returns>A SQL where clause used to filter by the given taxonomy.</returns>
public static string TaxonomyFilter(Guid[] taxonomyIds, bool includeDescendants, string tableName)
{
return TaxonomyFilter(taxonomyIds, includeDescendants, tableName, 1);
}
/// <summary>
/// This method builds a taxonomy filter.
/// </summary>
/// <param name="taxonomyIds">The ids of the taxonomy to find.</param>
/// <param name="includeDescendants">Whether the search should include data in descendant taxonomy items.</param>
/// <param name="tableName">The table name where the filter is being applied.</param>
/// <param name="tableName">The number of taxonomy results that are required per returned item. It is common to pass taxonomyIds.Length to force the query to match ALL taxonomy as opposed to ANY taxonomy.</param>
/// <returns>A SQL where clause used to filter by the given taxonomy.</returns>
public static string TaxonomyFilter(Guid[] taxonomyIds, bool includeDescendants, string tableName, int requiredTaxonomyPerItem)
{
if (requiredTaxonomyPerItem < 1)
throw new ArgumentException("TaxonomyFilter must require at least one taxonmy per item.", "requiredTaxonomyPerItem");
string filter;
if (taxonomyIds.Length <= 0)
return String.Empty;
else if (taxonomyIds.Length == 1)
filter = String.Format("= '{0}'", taxonomyIds[0]);
else
filter = String.Format("IN ('{0}')", String.Join("', '", taxonomyIds.ToStringArray()));
string prefix = String.IsNullOrEmpty(tableName) ? String.Empty : tableName + ".";
string descendantsFilter = includeDescendants
? String.Format(" OR (TaxonomyId IN (SELECT TaxonomyId FROM cpsys_TaxonomyAncestors WHERE (AncestorTaxonomyId {0})))", filter)
: String.Empty;
filter = String.Format("({0}DataId IN (SELECT DataId FROM cpsys_DataCurrentInTaxonomy WHERE (TaxonomyId {1}){2}{3}))", prefix, filter, descendantsFilter
, (requiredTaxonomyPerItem > 1) ? String.Format(" GROUP BY DataId HAVING (COUNT(TaxonomyId) = {0})", requiredTaxonomyPerItem) : String.Empty);
//HttpContext.Current.Trace.Warn("TaxonomyFilter", filter);
return filter;
}
/// <summary>
/// This property gets the audience filter the current user.
/// </summary>
public static string AudienceFilter()
{
return CpContent.AudienceFilter(String.Empty);
}
/// <summary>
/// This property gets the audience filter the current user.
/// </summary>
/// <param name="tableName">The table name where the filter is being applied.</param>
public static string AudienceFilter(string tableName)
{
return AudienceFilter(tableName, Management.User.AudienceId);
}
/// <summary>
/// This property gets the audience filter the current user.
/// </summary>
/// <param name="tableName">The table name where the filter is being applied.</param>
/// <param name="audienceId">The audience Id.</param>
/// <param name="descendants">Whether to include records tagged to children of the current audience.</param>
public static string AudienceFilter(string tableName, Guid audienceId, bool children = false)
{
if (children)
return $"({tableName}.DataId IN (SELECT dbo.cpsys_DataCurrentInAudiences.DataId FROM dbo.cpsys_DataCurrentInAudiences WHERE (dbo.cpsys_DataCurrentInAudiences.AudienceId IN (SELECT dbo.cpsys_Audiences.AudienceId FROM dbo.cpsys_Audiences WHERE (dbo.cpsys_Audiences.AudienceId IN ('{Constants.AllAudienceId}', '{audienceId}')) OR (dbo.cpsys_Audiences.ParentAudienceId = '{audienceId}')) AND IsCda = 1)))";
else
return Audiences.Filter(tableName, audienceId, true, false, false) + Tenants.Instance.TenantFilter(tableName, null);
}
public static string AudienceFilter(string tableName, Guid[] audienceIds)
{
var prefix = String.IsNullOrWhiteSpace(tableName) ? "" : tableName + ".";
return $"({prefix}DataId IN (SELECT dbo.cpsys_DataCurrentInAudiences.DataId FROM dbo.cpsys_DataCurrentInAudiences WHERE (dbo.cpsys_DataCurrentInAudiences.AudienceId IN ('{Constants.AllAudienceId}', '{String.Join("', '", audienceIds)}') AND IsCda = 1)))";
}
/// <summary>
/// This method gets the role filter the current user.
/// </summary>
public static string RoleFilter()
{
return CpContent.RoleFilter(String.Empty);
}
/// <summary>
/// This method gets the role filter the current user.
/// </summary>
/// <param name="tableName">The table name where the filter is being applied.</param>
public static string RoleFilter(string tableName)
{
return RoleFilter(tableName, Management.User);
}
/// <summary>
/// This method gets the role filter the current user.
/// </summary>
/// <param name="tableName">The table name where the filter is being applied.</param>
/// <param name="user">The current user whose roles will be utilized.</param>
public static string RoleFilter(string tableName, User user)
{
return Centralpoint.Web.Cms.Roles.Filter(tableName, "DataId", user);
}
/// <summary>
/// This method gets the role filter the current user.
/// </summary>
/// <param name="tableName">The table name where the filter is being applied.</param>
/// <param name="roleId">The role id.</param>
public static string RoleFilter(string tableName, Guid roleId)
{
return Centralpoint.Web.Cms.Roles.Filter(tableName, "DataId", roleId);
}
/// <summary>
/// This method generates a role filter for the provided roles.
/// </summary>
/// <param name="tableName">The table name where the filter is being applied.</param>
/// <param name="roles">An array of role IDs.</param>
public static string RoleFilter(string tableName, Guid[] roles)
{
return Centralpoint.Web.Cms.Roles.Filter(tableName, "DataId", roles);
}
/// <summary>
/// This method gets the date filter.
/// </summary>
public static string DateFilter()
{
return CpContent.DateFilter(String.Empty);
}
/// <summary>
/// This method gets the date filter.
/// </summary>
/// <param name="tableName">The table name where the filter is being applied.</param>
public static string DateFilter(string tableName)
{
string prefix = String.IsNullOrEmpty(tableName) ? String.Empty : tableName + ".";
//return String.Format("(({0}StartDate <= '{1}') AND ({0}EndDate >= '{1}'))", prefix, DateTime.UtcNow); // 2013-08-09: Replacing DateTime.UtcNow with HttpContext.Current.Timestamp causes is to generate the same date and time for every request on one page which allows page level cache to work properly
return String.Format("(({0}StartDate <= '{1:s}') AND ({0}EndDate >= '{1:s}'))", prefix, HttpContext.Current.Timestamp.ToUniversalTime());
}
/// <summary>
/// This method gets the current and future date filter.
/// </summary>
public static string CurrentAndFutureDateFilter()
{
return CpContent.CurrentAndFutureDateFilter(String.Empty);
}
/// <summary>
/// This method gets the current and future date filter.
/// </summary>
/// <param name="tableName">The table name where the filter is being applied.</param>
public static string CurrentAndFutureDateFilter(string tableName)
{
string prefix = String.IsNullOrEmpty(tableName) ? String.Empty : tableName + ".";
//return String.Format("({0}EndDate >= '{1}')", prefix, DateTime.UtcNow); // 2013-08-09: Replacing DateTime.UtcNow with HttpContext.Current.Timestamp causes is to generate the same date and time for every request on one page which allows page level cache to work properly
return String.Format("({0}EndDate >= '{1:s}')", prefix, HttpContext.Current.Timestamp.ToUniversalTime());
}
/// <summary>
/// This method gets the enabled filter.
/// </summary>
public static string EnabledFilter()
{
return CpContent.EnabledFilter(String.Empty);
}
/// <summary>
/// This method gets the enabled filter.
/// </summary>
/// <param name="tableName">The table name where the filter is being applied.</param>
public static string EnabledFilter(string tableName)
{
string prefix = String.IsNullOrEmpty(tableName) ? String.Empty : tableName + ".";
return String.Format("({0}IsEnabled = 1)", prefix);
}
/// <summary>
/// This property gets the complete filter the current user.
/// </summary>
/// <returns>A system filter string.</returns>
public string SystemFilter()
{
return CpContent.SystemFilter(this.SystemFilterOptions);
}
/// <summary>
/// This property gets the complete filter the current user.
/// </summary>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>A system filter string.</returns>
public static string SystemFilter(SystemFilterOptions filterOptions)
{
return CpContent.SystemFilter(filterOptions, String.Empty);
}
/// <summary>
/// This property gets the complete filter the current user.
/// </summary>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>A system filter string.</returns>
public static string SystemFilter(SystemFilterOptions filterOptions, string tableName)
{
List<int> selected = EnumReader.GetValuesFromEnum(typeof(SystemFilterOptions), (int)filterOptions);
if (selected.Contains((int)SystemFilterOptions.None))
return String.Empty;
if (selected.Contains((int)SystemFilterOptions.All))
return String.Format("({0} AND {1} AND {2} AND {3})", CpContent.EnabledFilter(tableName), CpContent.DateFilter(tableName), CpContent.RoleFilter(tableName), CpContent.AudienceFilter(tableName));
StringBuilder sb = new StringBuilder(); int count = 0;
if (selected.Contains((int)SystemFilterOptions.Enabled))
{
sb.Append(CpContent.EnabledFilter(tableName));
count++;
}
if (selected.Contains((int)SystemFilterOptions.Date))
{
if (count > 0) sb.Append(" AND ");
sb.Append(CpContent.DateFilter(tableName)); count++;
}
else if (selected.Contains((int)SystemFilterOptions.CurrentAndFutureDate))
{
if (count > 0) sb.Append(" AND ");
sb.Append(CpContent.CurrentAndFutureDateFilter(tableName)); count++;
}
if (selected.Contains((int)SystemFilterOptions.Role))
{
if (count > 0) sb.Append(" AND ");
sb.Append(CpContent.RoleFilter(tableName));
count++;
}
if (selected.Contains((int)SystemFilterOptions.Audience))
{
if (count > 0) sb.Append(" AND ");
sb.Append(CpContent.AudienceFilter(tableName));
count++;
}
if (count > 1) { sb.Insert(0, "("); sb.Append(")"); }
return sb.ToString();
}
#endregion
#region order by
/// <summary>
/// This method gets the hits order by clause.
/// </summary>
/// <param name="tableName">The table name where the order by is being applied.</param>
/// <returns>An order by clause.</returns>
public static string HitsOrderBy(string tableName)
{
return HitsOrderBy(tableName, SortDirection.Descending);
}
/// <summary>
/// This method gets the hits order by clause.
/// </summary>
/// <param name="tableName">The table name where the order by is being applied.</param>
/// <param name="sd">The sort direction.</param>
/// <returns>An order by clause.</returns>
public static string HitsOrderBy(string tableName, SortDirection sd)
{
return String.Format("(SELECT SUM(Hits) AS Hits FROM cpsys_Leads WHERE (DataId = {0}.DataId)) {1}", tableName, (sd == SortDirection.Ascending) ? "ASC" : "DESC");
}
/// <summary>
/// This method gets the rating order by clause.
/// </summary>
/// <param name="tableName">The table name where the order by is being applied.</param>
/// <returns>An order by clause.</returns>
public static string RatingOrderBy(string tableName)
{
return RatingOrderBy(tableName, SortDirection.Descending);
}
/// <summary>
/// This method gets the rating order by clause.
/// </summary>
/// <param name="tableName">The table name where the order by is being applied.</param>
/// <param name="sd">The sort direction.</param>
/// <returns>An order by clause.</returns>
public static string RatingOrderBy(string tableName, SortDirection sd)
{
return String.Format("(SELECT AVG(Rating) AS Rating FROM cpsys_Leads WHERE (Rating > 0) AND (DataId = {0}.DataId)) {1}", tableName, (sd == SortDirection.Ascending) ? "ASC" : "DESC");
}
#endregion
#region relation readers
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReaderMax(string systemName)
{
return CpContent.ExecuteRelationReaderMax(systemName, CpSystem.RelationInfo(systemName).Fields);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReaderMax(string systemName, string fields)
{
return CpContent.ExecuteRelationReaderMax(systemName, fields, String.Empty);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReaderMax(string systemName, string fields, string orderBy)
{
return CpContent.ExecuteRelationReaderMax(systemName, fields, orderBy, String.Empty);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReaderMax(string systemName, string fields, string orderBy, string where)
{
return CpContent.ExecuteRelationReaderMax(systemName, fields, orderBy, where, SystemFilterOptions.All);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReaderMax(string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions)
{
return CpContent.ExecuteRelationReaderMax(systemName, fields, orderBy, where, filterOptions, Management.Application.ExecuteConnectionString);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="connectionString">An execute connection string.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReaderMax(string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions, string connectionString)
{
// A using statement cannot be used here because this method returns a reader and the connection must be open when it returns. Exception handling has been added when the connection is opened.
return CpContent.ExecuteRelationReaderMax(systemName, fields, orderBy, where, filterOptions, new SqlConnection(connectionString));
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="connection">An execute connection object.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReaderMax(string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions, SqlConnection connection)
{
return CpContent.ExecuteRelationReaderMax(systemName, fields, orderBy, where, filterOptions, new SqlCommand(String.Empty, connection));
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the web site propery.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="connection">An execute connection object.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReaderMax(string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions, SqlCommand command)
{
string max = Management.Application.Properties.Get("ExecuteReaderMax");
return CpContent.ExecuteRelationReader(1, Convert.ToInt32(max), systemName, fields, orderBy, where, filterOptions, command, Convert.ToInt32(max));
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="systemName">The relation system name.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReader(int pageIndex, int pageSize, string systemName)
{
return CpContent.ExecuteRelationReader(pageIndex, pageSize, systemName, CpSystem.RelationInfo(systemName).Fields);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReader(int pageIndex, int pageSize, string systemName, string fields)
{
return CpContent.ExecuteRelationReader(pageIndex, pageSize, systemName, fields, String.Empty);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReader(int pageIndex, int pageSize, string systemName, string fields, string orderBy)
{
return CpContent.ExecuteRelationReader(pageIndex, pageSize, systemName, fields, orderBy, String.Empty);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReader(int pageIndex, int pageSize, string systemName, string fields, string orderBy, string where)
{
return CpContent.ExecuteRelationReader(pageIndex, pageSize, systemName, fields, orderBy, where, SystemFilterOptions.All);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReader(int pageIndex, int pageSize, string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions)
{
return CpContent.ExecuteRelationReader(pageIndex, pageSize, systemName, fields, orderBy, where, filterOptions, Management.Application.ExecuteConnectionString);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="tableName">The table name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="connectionString">An execute connection string.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReader(int pageIndex, int pageSize, string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions, string connectionString)
{
// A using statement cannot be used here because this method returns a reader and the connection must be open when it returns. Exception handling has been added when the connection is opened.
return CpContent.ExecuteRelationReader(pageIndex, pageSize, systemName, fields, orderBy, where, filterOptions, new SqlConnection(connectionString));
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="connection">An execute connection object.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReader(int pageIndex, int pageSize, string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions, SqlConnection connection)
{
return CpContent.ExecuteRelationReader(pageIndex, pageSize, systemName, fields, orderBy, where, filterOptions, new SqlCommand(String.Empty, connection));
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="command">An execute command object.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReader(int pageIndex, int pageSize, string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions, SqlCommand command)
{
return CpContent.ExecuteRelationReader(pageIndex, pageSize, systemName, fields, orderBy, where, filterOptions, command, 0);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="pageIndex">The index of the page.</param>
/// <param name="pageSize">The size of each page.</param>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="command">An execute command object.</param>
/// <param name="limit">The maximum number of records to return.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteRelationReader(int pageIndex, int pageSize, string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions, SqlCommand command, int limit)
{
string newWhere = CpContent.SystemFilter(filterOptions);
if (!String.IsNullOrEmpty(where))
{
if (String.IsNullOrEmpty(newWhere))
newWhere = where;
else
newWhere = String.Format("({0}) AND {1}", newWhere, where);
}
return CpContent.ExecuteReader(pageIndex, pageSize, CpContent.RelationTableName(systemName), fields, orderBy, newWhere, String.Empty, command, limit);
}
#endregion
#region relations
/// <summary>
/// This method replaces one or more dataId(s) with the title.
/// </summary>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="values">A comma space separated list of data ids.</param>
/// <returns>A comma space separated list of replacement text.</returns>
public static string ReplaceDataIds(string relationSystemName, string values)
{
return CpContent.ReplaceDataIds(relationSystemName, values, "Title");
}
/// <summary>
/// This method replaces one or more dataId(s) with the corresponding relation column value.
/// </summary>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="values">A comma space separated list of data ids.</param>
/// <param name="relationColumnName">The name of the relation column that contains the replacement text.</param>
/// <returns>A comma space separated list of replacement text.</returns>
public static string ReplaceDataIds(string relationSystemName, string values, string relationColumnName)
{
string key = String.Format("CpContent.ReplaceDataIds:{0}:{1}", relationSystemName, relationColumnName);
DataTable dt = CpCache.Get(key, false, false) as DataTable;
if (dt == null)
{
using (SqlConnection connection = new SqlConnection(Management.Application.SelectConnectionString))
{
SqlDataAdapter da = new SqlDataAdapter(String.Format("SELECT DataId, [{0}] FROM {1} ORDER BY [{0}]", relationColumnName, CpContent.RelationTableName(relationSystemName)), connection);
dt = new DataTable(relationSystemName);
connection.Open();
da.Fill(dt);
List<string> modules = new List<string>();
foreach (string module in CpSystem.RelationInfo(relationSystemName).ModuleInfo.Keys) modules.Add(module);
CpCache.Insert(key, dt, modules.ToArray(), new string[] { }, false, false, false, false, false);
}
}
StringBuilder sb = new StringBuilder();
if (dt != null)
{
if (String.IsNullOrEmpty(values)) values = Guid.Empty.ToString();
StringBuilder filter = new StringBuilder("DataId IN ("); int i = 0;
foreach (string value in values.Split(new string[] { ", " }, StringSplitOptions.RemoveEmptyEntries))
{
if (i > 0) filter.Append(", "); i++;
filter.AppendFormat("CONVERT('{0}', 'System.Guid')", value);
}
filter.Append(")");
HttpContext.Current.Trace.Write("GetDataRelationshipView", filter.ToString());
DataRow[] rows = dt.Select(filter.ToString(), relationColumnName);
foreach (DataRow dr in rows)
{
if (sb.Length > 0) sb.Append(", ");
sb.Append(dr[relationColumnName]);
}
}
return sb.ToString();
}
/// <summary>
/// This method adds a relation DataTable to the DataSet.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>The relation DataTable.</returns>
public async Task<DataTable> AddRelationAsync(string systemName, string fields = "*", string orderBy = "", string where = "")
{
return await this.AddRelationAsync(systemName, fields, orderBy, where, this.SystemFilterOptions);
}
/// <summary>
/// This method adds a relation DataTable to the DataSet.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>The relation DataTable.</returns>
public async Task<DataTable> AddRelationAsync(string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions)
{
// maintain a list of related modules for caching
foreach (string module in CpSystem.RelationInfo(systemName).ModuleInfo.Keys)
if (!this.RelatedModules.Contains(module)) this.RelatedModules.Add(module);
DataTable dt = await CpContent.GetRelationAsync(systemName, fields, orderBy, where, filterOptions, false, this.ConnectionString);
this.Tables.Add(dt);
return dt;
}
/// <summary>
/// This method adds a relation DataTable to the DataSet.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <returns>The relation DataTable.</returns>
public DataTable AddRelation(string systemName)
{
return AddRelation(systemName, "*");
}
/// <summary>
/// This method adds a relation DataTable to the DataSet.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <returns>The relation DataTable.</returns>
public DataTable AddRelation(string systemName, string fields)
{
return AddRelation(systemName, fields, String.Empty);
}
/// <summary>
/// This method adds a relation DataTable to the DataSet.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <returns>The relation DataTable.</returns>
public DataTable AddRelation(string systemName, string fields, string orderBy)
{
return AddRelation(systemName, fields, orderBy, String.Empty);
}
/// <summary>
/// This method adds a relation DataTable to the DataSet.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>The relation DataTable.</returns>
public DataTable AddRelation(string systemName, string fields, string orderBy, string where)
{
return AddRelation(systemName, fields, orderBy, where, this.SystemFilterOptions);
}
/// <summary>
/// This method adds a relation DataTable to the DataSet.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>The relation DataTable.</returns>
public DataTable AddRelation(string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions)
{
DataTable dt = CpContent.GetRelation(systemName, fields, orderBy, where, filterOptions, this.ConnectionString);
this.Tables.Add(dt);
// maintain a list of related modules for caching
foreach (string module in CpSystem.RelationInfo(systemName).ModuleInfo.Keys)
if (!this.RelatedModules.Contains(module)) this.RelatedModules.Add(module);
return dt;
}
/// <summary>
/// This method gets a relation DataTable from the database.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause which must include StartDate and EndDate for filtration.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>The relation DataRow[].</returns>
public async static Task<DataRow[]> GetFilteredRelationAsync(string systemName, string fields = "*", string orderBy = "", string where = "")
{
DataTable dt = await CpContent.GetRelationAsync(systemName, fields, orderBy, where);
return dt.Select(CpContent.DateFilter());
}
/// <summary>
/// This method gets a relation DataTable from the database.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>The relation DataTable.</returns>
public async static Task<DataTable> GetRelationAsync(string systemName, string fields = "*", string orderBy = "", string where = ""
, SystemFilterOptions filterOptions = SystemFilterOptions.Enabled | SystemFilterOptions.Audience | SystemFilterOptions.Role | SystemFilterOptions.CurrentAndFutureDate, bool useCache = true)
{
return await CpContent.GetRelationAsync(systemName, fields, orderBy, where, filterOptions, useCache, Management.Application.SelectConnectionString);
}
/// <summary>
/// This method gets a relation DataTable from the database.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="connectionString">An execute connection string.</param>
/// <returns>The relation DataTable.</returns>
public async static Task<DataTable> GetRelationAsync(string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions, bool useCache, string connectionString)
{
DataTable dt = null;
string key = String.Empty;
bool audienceSpecific = false, roleSpecific = false;
if (useCache)
{
key = String.Format("GetRelation:{0}:{1}:{2}:{3}", fields, orderBy, where, filterOptions);
audienceSpecific = filterOptions.HasFlag(SystemFilterOptions.Audience);
roleSpecific = filterOptions.HasFlag(SystemFilterOptions.Role);
HttpContext.Current.Trace.Warn("GetRelationAsync.key", key);
HttpContext.Current.Trace.Warn("GetRelationAsync.audienceSpecific", audienceSpecific.ToString());
HttpContext.Current.Trace.Warn("GetRelationAsync.roleSpecific", roleSpecific.ToString());
dt = CpCache.Get(key, audienceSpecific, roleSpecific) as DataTable;
HttpContext.Current.Trace.Warn("GetRelationAsync.InCache", Convert.ToString(dt != null));
}
if (dt == null)
{
//dt = await CpContent.GetTableAsync(systemName, CpContent.GetRelationSql(systemName, fields, orderBy, where, filterOptions), connectionString);
dt = await CpContent.GetTableAsync(systemName, CpContent.GetRelationSql(systemName, fields, orderBy, where, filterOptions), connectionString);
if (useCache)
{
HttpContext.Current.Trace.Warn("GetRelationAsync.key", key);
CpCache.Insert(key, dt, new string[] { systemName }, new string[] { }, false, false, false, audienceSpecific, roleSpecific);
HttpContext.Current.Trace.Warn("GetRelationAsync.InCache", Convert.ToString(CpCache.Get(key, audienceSpecific, roleSpecific) != null));
}
}
return dt;
}
/// <summary>
/// This method gets a relation DataTable from the database.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <returns>The relation DataTable.</returns>
public static DataTable GetRelation(string systemName)
{
return GetRelation(systemName, "*");
}
/// <summary>
/// This method gets a relation DataTable from the database.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <returns>The relation DataTable.</returns>
public static DataTable GetRelation(string systemName, string fields)
{
return GetRelation(systemName, fields, String.Empty);
}
/// <summary>
/// This method gets a relation DataTable from the database.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <returns>The relation DataTable.</returns>
public static DataTable GetRelation(string systemName, string fields, string orderBy)
{
return GetRelation(systemName, fields, orderBy, String.Empty);
}
/// <summary>
/// This method gets a relation DataTable from the database. This method does not filter by date because it is intened to be cached.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>The relation DataTable.</returns>
public static DataTable GetRelation(string systemName, string fields, string orderBy, string where)
{
return GetRelation(systemName, fields, orderBy, where, SystemFilterOptions.Enabled | SystemFilterOptions.Audience | SystemFilterOptions.Role | SystemFilterOptions.CurrentAndFutureDate);
}
/// <summary>
/// This method gets a relation DataTable from the database.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>The relation DataTable.</returns>
public static DataTable GetRelation(string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions)
{
return GetRelation(systemName, fields, orderBy, where, filterOptions, Management.Application.SelectConnectionString);
}
/// <summary>
/// This method gets a relation DataTable from the database.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="connectionString">An execute connection string.</param>
/// <returns>The relation DataTable.</returns>
public static DataTable GetRelation(string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions, string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
return GetRelation(systemName, fields, orderBy, where, filterOptions, connection);
}
}
/// <summary>
/// This method gets a relation DataTable from the database.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="connection">An execute connection object.</param>
/// <returns>The relation DataTable.</returns>
public static DataTable GetRelation(string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions, SqlConnection connection)
{
return CpContent.GetTable(systemName, CpContent.GetRelationSql(systemName, fields, orderBy, where, filterOptions), connection);
}
/// <summary>
/// This method gets a relation SQL statement.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <returns>The relation SQL.</returns>
public static string GetRelationSql(string systemName)
{
return GetRelationSql(systemName, "*");
}
/// <summary>
/// This method gets a relation SQL statement.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <returns>The relation SQL.</returns>
public static string GetRelationSql(string systemName, string fields)
{
return GetRelationSql(systemName, fields, String.Empty);
}
/// <summary>
/// This method gets a relation SQL statement.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <returns>The relation SQL.</returns>
public static string GetRelationSql(string systemName, string fields, string orderBy)
{
return GetRelationSql(systemName, fields, orderBy, String.Empty);
}
/// <summary>
/// This method gets a relation SQL statement.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>The relation SQL.</returns>
public static string GetRelationSql(string systemName, string fields, string orderBy, string where)
{
return GetRelationSql(systemName, fields, orderBy, where, SystemFilterOptions.Enabled | SystemFilterOptions.Audience | SystemFilterOptions.Role | SystemFilterOptions.CurrentAndFutureDate);
}
/// <summary>
/// This method gets a relation SQL statement.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>The relation SQL.</returns>
public static string GetRelationSql(string systemName, string fields, string orderBy, string where, SystemFilterOptions filterOptions)
{
if (String.IsNullOrEmpty(systemName)) throw new Exception("You cannot get relation sql with no system name.");
if (String.IsNullOrEmpty(fields)) throw new Exception("You cannot get relation sql with no fields.");
string newWhere = CpContent.SystemFilter(filterOptions);
if (!String.IsNullOrEmpty(where))
{
if (String.IsNullOrEmpty(newWhere))
newWhere = where;
else
newWhere = String.Format("({0}) AND {1}", newWhere, where);
}
StringBuilder sql = new StringBuilder("SELECT ");
sql.Append((fields != "*") ? fields : CpSystem.RelationInfo(systemName).Fields);
sql.Append(" FROM ");
sql.Append(CpContent.RelationTableName(systemName));
if (!String.IsNullOrEmpty(newWhere))
{
sql.Append(" WHERE ");
sql.Append(newWhere);
}
if (!String.IsNullOrEmpty(orderBy))
{
sql.Append(" ORDER BY ");
sql.Append(orderBy);
}
return sql.ToString();
}
/// <summary>
/// This method gets a relation table from the current DataSet.
/// </summary>
/// <param name="systemName">The system name of the relation.</param>
/// <returns>The relation DataTable.</returns>
public DataTable Relation(string systemName)
{
return this.Tables[systemName];
}
/// <summary>
/// This method generates a relation table name.
/// </summary>
/// <param name="systemName">The system name of the relation.</param>
/// <returns>The relation table name.</returns>
public static string RelationTableName(string systemName)
{
return Centralpoint.Web.Cms.Relation.BuildTableName(systemName);
}
#endregion
#region relation views
/// <summary>
/// This method initializes the default view for the given relation.
/// </summary>
/// <param name="systemName">The system name of the relation.</param>
/// <returns>A DataView.</returns>
public DataView DefaultRelationView(string systemName)
{
return this.Tables[systemName].DefaultView;
}
/// <summary>
/// This method initializes the default view for the given relation.
/// </summary>
/// <param name="systemName">The system name of the relation.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView DefaultRelationView(string systemName, string sort)
{
DataView dv = this.DefaultRelationView(systemName);
dv.Sort = sort;
return dv;
}
/// <summary>
/// This method initializes the default view for the given relation.
/// </summary>
/// <param name="systemName">The system name of the relation.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <param name="rowFilter">The row filter to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView DefaultRelationView(string systemName, string sort, string rowFilter)
{
DataView dv = this.DefaultRelationView(systemName, sort);
dv.RowFilter = rowFilter;
return dv;
}
/// <summary>
/// This method initializes the default view for the given relation.
/// </summary>
/// <param name="systemName">The system name of the relation.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <param name="rowFilter">The row filter to be applied to the DataTable.</param>
/// <param name="rowState">The state of the included rows.</param>
/// <returns>A DataView.</returns>
public DataView DefaultRelationView(string systemName, string sort, string rowFilter, DataViewRowState rowState)
{
DataView dv = this.DefaultRelationView(systemName, sort, rowFilter);
dv.RowStateFilter = rowState;
return dv;
}
/// <summary>
/// This method adds a view to the list of views.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <param name="systemName">The system name of the relation.</param>
/// <returns>A DataView.</returns>
public DataView AddRelationView(string viewName, string systemName)
{
return this.AddRelationView(viewName, systemName, String.Empty);
}
/// <summary>
/// This method adds a view to the list of views.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <param name="systemName">The system name of the relation.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView AddRelationView(string viewName, string systemName, string sort)
{
return this.AddRelationView(viewName, systemName, sort, String.Empty);
}
/// <summary>
/// This method adds a view to the list of views.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <param name="systemName">The system name of the relation.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <param name="rowFilter">The row filter to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView AddRelationView(string viewName, string systemName, string sort, string rowFilter)
{
return this.AddRelationView(viewName, systemName, sort, rowFilter, DataViewRowState.OriginalRows);
}
/// <summary>
/// This method adds a view to the list of views.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <param name="systemName">The system name of the relation.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <param name="rowFilter">The row filter to be applied to the DataTable.</param>
/// <param name="rowState">The state of the included rows.</param>
/// <returns>A DataView.</returns>
public DataView AddRelationView(string viewName, string systemName, string sort, string rowFilter, DataViewRowState rowState)
{
DataView dv = this.NewRelationView(systemName, sort, rowFilter, rowState);
this.AddView(viewName, dv);
return dv;
}
/// <summary>
/// This method generates a DataView using the DataSet.
/// </summary>
/// <param name="systemName">The system name of the relation.</param>
/// <returns>A DataView.</returns>
public DataView NewRelationView(string systemName)
{
return this.NewRelationView(systemName, String.Empty);
}
/// <summary>
/// This method generates a DataView using the DataSet.
/// </summary>
/// <param name="systemName">The system name of the relation.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView NewRelationView(string systemName, string sort)
{
return this.NewRelationView(systemName, sort, String.Empty);
}
/// <summary>
/// This method generates a DataView using the DataSet.
/// </summary>
/// <param name="systemName">The system name of the relation.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <param name="rowFilter">The row filter to be applied to the DataTable.</param>
/// <returns>A DataView.</returns>
public DataView NewRelationView(string systemName, string sort, string rowFilter)
{
return this.NewRelationView(systemName, sort, rowFilter, DataViewRowState.OriginalRows);
}
/// <summary>
/// This method generates a DataView using the DataSet.
/// </summary>
/// <param name="systemName">The system name of the relation.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <param name="rowFilter">The row filter to be applied to the DataTable.</param>
/// <param name="rowState">The state of the included rows.</param>
/// <returns>A DataView.</returns>
public DataView NewRelationView(string systemName, string sort, string rowFilter, DataViewRowState rowState)
{
return this.NewView(systemName, sort, rowFilter, rowState);
}
#endregion
#region keyword list readers
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteKeywordListReader(string systemName)
{
return CpContent.ExecuteKeywordListReader(systemName, Management.Application.ExecuteConnectionString);
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="connectionString">An execute connection string.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteKeywordListReader(string systemName, string connectionString)
{
// A using statement cannot be used here because this method returns a reader and the connection must be open when it returns. Exception handling has been added when the connection is opened.
return CpContent.ExecuteKeywordListReader(systemName, new SqlConnection(connectionString));
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="connection">An execute connection object.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteKeywordListReader(string systemName, SqlConnection connection)
{
return CpContent.ExecuteKeywordListReader(systemName, new SqlCommand(String.Empty, connection));
}
/// <summary>
/// This method executes a paged stored procedure to get a SqlDataReader.
/// </summary>
/// <param name="systemName">The relation system name.</param>
/// <param name="command">An execute command object.</param>
/// <returns>A SqlDataReader.</returns>
public static SqlDataReader ExecuteKeywordListReader(string systemName, SqlCommand command)
{
string fields = "[Value], CASE LEFT([Text], 4) WHEN '<!--' THEN SUBSTRING([Text], CHARINDEX('-->', [Text]) + 3, 256) ELSE [Text] END AS [Text]";
string where = String.Format("KeywordListId IN (SELECT TOP 1 KeywordListId FROM cpsys_KeywordLists WHERE SystemName = N'{0}')", systemName);
string max = Management.Application.Properties.Get("ExecuteReaderMax");
return CpContent.ExecuteReader(1, Convert.ToInt32(max), "cpsys_Keywords", fields, "[Text] ASC", where, String.Empty, command, Convert.ToInt32(max));
}
#endregion
#region keyword lists
/// <summary>
/// This method replaces one or more keyword value(s) with the corresponding keyword text.
/// </summary>
/// <param name="keywordListSystemName">The keyword list system name.</param>
/// <param name="values">A comma space separated list of keyword values.</param>
/// <returns>A comma space separated list of keyword text.</returns>
public static string ReplaceKeywordValues(string keywordListSystemName, string values)
{
string key = "CpContent.ReplaceKeywordValues:" + keywordListSystemName;
DataTable dt = CpCache.GetKeywordList(key) as DataTable;
if (dt == null)
{
using (SqlConnection connection = new SqlConnection(Management.Application.SelectConnectionString))
{
SqlDataAdapter da = new SqlDataAdapter(String.Format("SELECT [Value], [Text] FROM cpsys_Keywords WHERE KeywordListId IN (SELECT KeywordListId FROM cpsys_KeywordLists WHERE SystemName = N'{0}') ORDER BY [Text]", keywordListSystemName), connection);
dt = new DataTable(keywordListSystemName);
connection.Open();
da.Fill(dt);
CpCache.InsertKeywordList(key, dt, keywordListSystemName);
}
}
StringBuilder sb = new StringBuilder();
if (dt != null)
{
DataRow[] rows = dt.Select(String.Format("Value IN ('{0}')", values.Replace(", ", "', '")), "Text");
foreach (DataRow dr in rows)
{
if (sb.Length > 0) sb.Append(", ");
sb.Append(dr["Text"]);
}
}
return sb.ToString();
}
/// <summary>
/// This method gets a keyword list DataTable.
/// </summary>
/// <param name="systemName">The system name of the keyword list.</param>
/// <returns>The keyword list DataTable.</returns>
public static DataTable GetKeywordList(string systemName)
{
return CpContent.GetKeywordList(systemName, Management.Application.SelectConnectionString);
}
/// <summary>
/// This method gets a keyword list DataTable.
/// </summary>
/// <param name="systemName">The system name of the keyword list.</param>
/// <param name="connectionString">A select connection string.</param>
/// <returns>The keyword list DataTable.</returns>
public static DataTable GetKeywordList(string systemName, string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
return CpContent.GetKeywordList(systemName, connection);
}
}
/// <summary>
/// This method gets a keyword list DataTable.
/// </summary>
/// <param name="systemName">The system name of the keyword list.</param>
/// <param name="connection">A select connection object.</param>
/// <returns>The keyword list DataTable.</returns>
public static DataTable GetKeywordList(string systemName, SqlConnection connection)
{
string key = "CpContent.GetKeywordList:" + systemName;
DataTable dt = CpCache.GetKeywordList(key) as DataTable;
if (dt != null) return dt;
string sql = String.Format("SELECT [Value], CASE LEFT([Text], 4) WHEN '<!--' THEN SUBSTRING([Text], CHARINDEX('-->', [Text]) + 3, 256) ELSE [Text] END AS [Text] FROM cpsys_Keywords WHERE KeywordListId IN (SELECT TOP 1 KeywordListId FROM cpsys_KeywordLists WHERE SystemName = N'{0}') ORDER BY cpsys_Keywords.[Text] ASC", systemName.SqlEncode());
dt = CpContent.GetTable(systemName, sql, connection);
CpCache.InsertKeywordList(key, dt, systemName);
return dt;
}
#endregion
#region taxonomy
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <returns>The taxonomy DataTable.</returns>
public DataTable AddTaxonomy(Guid rootTaxonomyId, string relationSystemName)
{
return AddTaxonomy(rootTaxonomyId, relationSystemName, String.Empty);
}
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <returns>The taxonomy DataTable.</returns>
public DataTable AddTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields)
{
return AddTaxonomy(rootTaxonomyId, relationSystemName, fields, String.Empty);
}
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <returns>The taxonomy DataTable.</returns>
public DataTable AddTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy)
{
return AddTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, String.Empty);
}
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>The taxonomy DataTable.</returns>
public DataTable AddTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where)
{
return AddTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, String.Empty);
}
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <returns>The taxonomy DataTable.</returns>
public DataTable AddTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere)
{
return AddTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, this.SystemFilterOptions);
}
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>The taxonomy DataTable.</returns>
public DataTable AddTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions)
{
return AddTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, false);
}
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <returns>The taxonomy DataTable.</returns>
public DataTable AddTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount)
{
return AddTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, false);
}
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <returns>The taxonomy DataTable.</returns>
public DataTable AddTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount)
{
return AddTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, false);
}
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <returns>The taxonomy DataTable.</returns>
public DataTable AddTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount)
{
return AddTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, dataCount, false);
}
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <param name="descendantTaxonomyDataCount">Whether to include the DescendantTaxonomyDataCount field.</param>
/// <returns>The taxonomy DataTable.</returns>
public DataTable AddTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount, bool descendantTaxonomyDataCount)
{
return AddTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, dataCount, descendantTaxonomyDataCount, false);
}
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <param name="descendantTaxonomyDataCount">Whether to include the DescendantTaxonomyDataCount field.</param>
/// <param name="dataFilterEnabled">Whether to exclude taxonomy that contains no data and has no descendants that contain data.</param>
/// <returns>The taxonomy DataTable.</returns>
public DataTable AddTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount, bool descendantTaxonomyDataCount, bool dataFilterEnabled)
{
return AddTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, dataCount, descendantTaxonomyDataCount, dataFilterEnabled, false);
}
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <param name="descendantTaxonomyDataCount">Whether to include the DescendantTaxonomyDataCount field.</param>
/// <param name="dataFilterEnabled">Whether to exclude taxonomy that contains no data and has no descendants that contain data.</param>
/// <param name="dataCountIncludingDescendantTaxonomy">Whether to include the DataCountIncludingDescendantTaxonomy field.</param>
/// <returns>The taxonomy DataTable.</returns>
public DataTable AddTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount, bool descendantTaxonomyDataCount, bool dataFilterEnabled, bool dataCountIncludingDescendantTaxonomy)
{
DataTable dt = CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, dataCount, descendantTaxonomyDataCount, dataFilterEnabled, Management.Application.ExecuteConnectionString, dataCountIncludingDescendantTaxonomy);
this.Tables.Add(dt);
// maintain a list of related modules for caching
foreach (string module in CpSystem.RelationInfo(relationSystemName).ModuleInfo.Keys)
if (!this.RelatedModules.Contains(module)) this.RelatedModules.Add(module);
return dt;
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, String.Empty);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, String.Empty);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, String.Empty);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, String.Empty);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, SystemFilterOptions.All);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, false);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, false);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, false);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, dataCount, false);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <param name="descendantTaxonomyDataCount">Whether to include the DescendantTaxonomyDataCount field.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount, bool descendantTaxonomyDataCount)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, dataCount, descendantTaxonomyDataCount, false);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <param name="descendantTaxonomyDataCount">Whether to include the DescendantTaxonomyDataCount field.</param>
/// <param name="dataFilterEnabled">Whether to exclude taxonomy that contains no data and has no descendants that contain data.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount, bool descendantTaxonomyDataCount, bool dataFilterEnabled)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, dataCount, descendantTaxonomyDataCount, dataFilterEnabled, Management.Application.ExecuteConnectionString);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <param name="descendantTaxonomyDataCount">Whether to include the DescendantTaxonomyDataCount field.</param>
/// <param name="dataFilterEnabled">Whether to exclude taxonomy that contains no data and has no descendants that contain data.</param>
/// <param name="connectionString">An execute connection string.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount, bool descendantTaxonomyDataCount, bool dataFilterEnabled, string connectionString)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, dataCount, descendantTaxonomyDataCount, dataFilterEnabled, connectionString, false);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <param name="descendantTaxonomyDataCount">Whether to include the DescendantTaxonomyDataCount field.</param>
/// <param name="dataFilterEnabled">Whether to exclude taxonomy that contains no data and has no descendants that contain data.</param>
/// <param name="connectionString">An execute connection string.</param>
/// <param name="dataCountIncludingDescendantTaxonomy">Whether to include the DataCountIncludingDescendantTaxonomy field.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount, bool descendantTaxonomyDataCount, bool dataFilterEnabled, string connectionString, bool dataCountIncludingDescendantTaxonomy)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, dataCount, descendantTaxonomyDataCount, dataFilterEnabled, connection, dataCountIncludingDescendantTaxonomy);
}
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <param name="descendantTaxonomyDataCount">Whether to include the DescendantTaxonomyDataCount field.</param>
/// <param name="dataFilterEnabled">Whether to exclude taxonomy that contains no data and has no descendants that contain data.</param>
/// <param name="connection">An execute connection object.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount, bool descendantTaxonomyDataCount, bool dataFilterEnabled, SqlConnection connection)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, dataCount, descendantTaxonomyDataCount, dataFilterEnabled, connection, false);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <param name="descendantTaxonomyDataCount">Whether to include the DescendantTaxonomyDataCount field.</param>
/// <param name="dataFilterEnabled">Whether to exclude taxonomy that contains no data and has no descendants that contain data.</param>
/// <param name="connection">An execute connection object.</param>
/// <param name="dataCountIncludingDescendantTaxonomy">Whether to include the DataCountIncludingDescendantTaxonomy field.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount, bool descendantTaxonomyDataCount, bool dataFilterEnabled, SqlConnection connection, bool dataCountIncludingDescendantTaxonomy)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, dataCount, descendantTaxonomyDataCount, dataFilterEnabled, new SqlCommand(String.Empty, connection), dataCountIncludingDescendantTaxonomy);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <param name="descendantTaxonomyDataCount">Whether to include the DescendantTaxonomyDataCount field.</param>
/// <param name="dataFilterEnabled">Whether to exclude taxonomy that contains no data and has no descendants that contain data.</param>
/// <param name="command">An execute command object.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount, bool descendantTaxonomyDataCount, bool dataFilterEnabled, SqlCommand command)
{
return CpContent.GetTaxonomy(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions
, childTaxonomyCount, descendantTaxonomyCount, dataCount, descendantTaxonomyDataCount, dataFilterEnabled, command, false);
}
/// <summary>
/// This method gets a taxonomy DataTable from the database.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <param name="childTaxonomyCount">Whether to include the ChildTaxonomyCount field.</param>
/// <param name="descendantTaxonomyCount">Whether to include the DescendantTaxonomyCount field.</param>
/// <param name="dataCount">Whether to include the DataCount field.</param>
/// <param name="descendantTaxonomyDataCount">Whether to include the DescendantTaxonomyDataCount field.</param>
/// <param name="dataFilterEnabled">Whether to exclude taxonomy that contains no data and has no descendants that contain data.</param>
/// <param name="command">An execute command object.</param>
/// <param name="dataCountIncludingDescendantTaxonomy">Whether to include the DataCountIncludingDescendantTaxonomy field.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomy(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions
, bool childTaxonomyCount, bool descendantTaxonomyCount, bool dataCount, bool descendantTaxonomyDataCount, bool dataFilterEnabled, SqlCommand command, bool dataCountIncludingDescendantTaxonomy)
{
if (String.IsNullOrEmpty(relationSystemName)) throw new Exception("You cannot execute a reader with no relation system name.");
string newWhere = Web.Cms.Audiences.Filter("cpsys_Taxonomy", Management.User.AudienceId, true, false, false, false);
if (!String.IsNullOrEmpty(where)) newWhere = String.Format("({0}) AND ({1})", newWhere, where);
//HttpContext.Current.Trace.Warn("newWhere", newWhere);
string newDataWhere = CpContent.SystemFilter(filterOptions);
if (!String.IsNullOrEmpty(dataWhere))
{
if (String.IsNullOrEmpty(newDataWhere))
newDataWhere = dataWhere;
else
newDataWhere = String.Format("({0}) AND {1}", newDataWhere, dataWhere);
}
//HttpContext.Current.Trace.Warn("newDataWhere", newDataWhere);
HttpContext.Current.Trace.Write("GetTaxonomy", relationSystemName);
HttpContext.Current.Trace.Write("where", newDataWhere);
command.CommandText = "cpsys_Taxonomy_SelectByRelation";
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 60; // 1 minute
command.Parameters.Add(new SqlParameter("@RelationTableName", SqlDbType.NVarChar, 56));
int i = 0; command.Parameters[i].Value = CpContent.RelationTableName(relationSystemName);
command.Parameters.Add(new SqlParameter("@RootTaxonomyId", SqlDbType.UniqueIdentifier));
i++; command.Parameters[i].Value = rootTaxonomyId;
if (!String.IsNullOrEmpty(fields))
{
command.Parameters.Add(new SqlParameter("@Fields", SqlDbType.NVarChar));
i++; command.Parameters[i].Value = fields;
}
command.Parameters.Add(new SqlParameter("@Where", SqlDbType.NVarChar));
i++; command.Parameters[i].Value = newWhere;
if (!String.IsNullOrEmpty(orderBy))
{
command.Parameters.Add(new SqlParameter("@OrderBy", SqlDbType.NVarChar));
i++; command.Parameters[i].Value = orderBy;
}
command.Parameters.Add(new SqlParameter("@ChildTaxonomyCount", SqlDbType.Bit));
i++; command.Parameters[i].Value = childTaxonomyCount;
command.Parameters.Add(new SqlParameter("@DescendantTaxonomyCount", SqlDbType.Bit));
i++; command.Parameters[i].Value = descendantTaxonomyCount;
command.Parameters.Add(new SqlParameter("@DataCount", SqlDbType.Bit));
i++; command.Parameters[i].Value = dataCount;
command.Parameters.Add(new SqlParameter("@DescendantTaxonomyDataCount", SqlDbType.Bit));
i++; command.Parameters[i].Value = descendantTaxonomyDataCount;
command.Parameters.Add(new SqlParameter("@DataCountIncludingDescendantTaxonomy", SqlDbType.Bit));
i++; command.Parameters[i].Value = dataCountIncludingDescendantTaxonomy;
command.Parameters.Add(new SqlParameter("@DataFilterEnabled", SqlDbType.Bit));
i++; command.Parameters[i].Value = dataFilterEnabled;
if (!String.IsNullOrEmpty(newDataWhere))
{
command.Parameters.Add(new SqlParameter("@CustomDataFilter", SqlDbType.NVarChar));
i++; command.Parameters[i].Value = newDataWhere;
}
DataTable dt = new DataTable("cpsys_Taxonomy");
if (command.Connection == null) command.Connection = new SqlConnection();
bool opened = false;
try
{
if (command.Connection.State == ConnectionState.Closed)
{
if (String.IsNullOrEmpty(command.Connection.ConnectionString))
command.Connection.ConnectionString = Management.Application.ExecuteConnectionString;
opened = true;
//HttpContext.Current.Trace.Warn("opened", opened.ToString());
command.Connection.Open();
}
//HttpContext.Current.Trace.Warn("sql", command.CommandText);
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);
}
finally
{
if (opened) command.Connection.Close();
}
return dt;
}
/// <summary>
/// This method adds a taxonomy DataTable to the DataSet where related records exist in the given relation.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
public DataTable AddTaxonomyWithDataCounts(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions)
{
DataTable dt = CpContent.GetTaxonomyWithDataCounts(rootTaxonomyId, relationSystemName, fields, orderBy, where, dataWhere, filterOptions);
this.Tables.Add(dt);
// maintain a list of related modules for caching
foreach (string module in CpSystem.RelationInfo(relationSystemName).ModuleInfo.Keys)
if (!this.RelatedModules.Contains(module)) this.RelatedModules.Add(module);
return dt;
}
/// <summary>
/// This method gets a taxonomy DataTable from the database when related records exist in the given relation.
/// </summary>
/// <param name="rootTaxonomyId">The root taxonomy id.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="dataWhere">The where clause applied to the relation.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>The taxonomy DataTable.</returns>
public static DataTable GetTaxonomyWithDataCounts(Guid rootTaxonomyId, string relationSystemName, string fields, string orderBy, string where, string dataWhere, SystemFilterOptions filterOptions)
{
if (String.IsNullOrEmpty(relationSystemName)) throw new Exception("You cannot get taxonomy with data counts without a relation system name.");
if (rootTaxonomyId == Guid.Empty) rootTaxonomyId = Constants.RootTaxonomyId;
if (String.IsNullOrEmpty(fields)) fields = "cpsys_Taxonomy.TaxonomyId, cpsys_Taxonomy.ParentTaxonomyId, cpsys_Taxonomy.SystemName, cpsys_Taxonomy.[Name], cpsys_Taxonomy.SortOrder";
if (String.IsNullOrEmpty(orderBy)) orderBy = "cpsys_Taxonomy.ParentTaxonomyId, cpsys_Taxonomy.SortOrder, cpsys_Taxonomy.[Name]";
string newWhere = Web.Cms.Audiences.Filter("cpsys_Taxonomy", Management.User.AudienceId, true, false, false, true);
if (!String.IsNullOrEmpty(where)) newWhere = String.Format("({0}) AND ({1})", newWhere, where);
//HttpContext.Current.Trace.Warn("newWhere", newWhere);
string relationTableName = Centralpoint.Web.Cms.Relation.BuildTableName(relationSystemName);
string newDataWhere = CpContent.SystemFilter(filterOptions, relationTableName);
if (!String.IsNullOrEmpty(dataWhere))
{
if (String.IsNullOrEmpty(newDataWhere))
newDataWhere = dataWhere;
else
newDataWhere = String.Format("({0}) AND {1}", newDataWhere, dataWhere);
}
if (!String.IsNullOrEmpty(newDataWhere)) newDataWhere = " WHERE " + newDataWhere;
string sql = String.Format(@"SELECT {2}, COUNT(cpsys_DataCurrentInTaxonomy.DataId) AS cpsys_DataCount
FROM cpsys_DataCurrentInTaxonomy
LEFT JOIN cpsys_Taxonomy ON cpsys_DataCurrentInTaxonomy.TaxonomyId = cpsys_Taxonomy.TaxonomyId
WHERE ((cpsys_DataCurrentInTaxonomy.TaxonomyId = '{0}') OR (cpsys_DataCurrentInTaxonomy.TaxonomyId IN (SELECT TaxonomyId FROM cpsys_TaxonomyAncestors WHERE (AncestorTaxonomyId = '{0}'))))
AND (cpsys_DataCurrentInTaxonomy.DataId IN (SELECT DataId FROM {1}{5}))
AND {4}
GROUP BY cpsys_Taxonomy.TaxonomyId, cpsys_Taxonomy.ParentTaxonomyId, cpsys_Taxonomy.SystemName, cpsys_Taxonomy.[Name], cpsys_Taxonomy.SortOrder
ORDER BY {3}", rootTaxonomyId, relationTableName, fields, orderBy, newWhere, newDataWhere);
//HttpContext.Current.Trace.Warn("GetTaxonomyWithDataCounts", sql);
DataTable dt = new DataTable("cpsys_Taxonomy");
using (SqlConnection connection = new SqlConnection(Management.Application.SelectConnectionString))
{
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);
}
return dt;
}
/// <summary>
/// This method gets the taxonomy table from the current DataSet.
/// </summary>
/// <returns>The taxonomy DataTable.</returns>
public DataTable Taxonomy()
{
return this.Tables["cpsys_Taxonomy"];
}
#endregion
#region roles
/// <summary>
/// This method adds a roles DataTable to the DataSet.
/// </summary>
/// <returns>The roles DataTable.</returns>
public DataTable AddRoles()
{
return this.AddRoles(String.Empty);
}
/// <summary>
/// This method adds a roles DataTable to the DataSet.
/// </summary>
/// <param name="fields">The fields clause.</param>
/// <returns>The roles DataTable.</returns>
public DataTable AddRoles(string fields)
{
return this.AddRoles(fields, String.Empty);
}
/// <summary>
/// This method adds a roles DataTable to the DataSet.
/// </summary>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <returns>The roles DataTable.</returns>
public DataTable AddRoles(string fields, string orderBy)
{
return this.AddRoles(fields, orderBy, String.Empty);
}
/// <summary>
/// This method adds a roles DataTable to the DataSet.
/// </summary>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>The roles DataTable.</returns>
public DataTable AddRoles(string fields, string orderBy, string where)
{
DataTable dt = CpContent.GetRoles(fields, orderBy, where);
this.Tables.Add(dt);
return dt;
}
/// <summary>
/// This method gets a roles DataTable from the database.
/// </summary>
/// <returns>The roles DataTable.</returns>
public static DataTable GetRoles()
{
return CpContent.GetRoles(String.Empty);
}
/// <summary>
/// This method gets a roles DataTable from the database.
/// </summary>
/// <param name="fields">The fields clause.</param>
/// <returns>The roles DataTable.</returns>
public static DataTable GetRoles(string fields)
{
return CpContent.GetRoles(fields, String.Empty);
}
/// <summary>
/// This method gets a roles DataTable from the database.
/// </summary>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <returns>The roles DataTable.</returns>
public static DataTable GetRoles(string fields, string orderBy)
{
return CpContent.GetRoles(fields, orderBy, String.Empty);
}
/// <summary>
/// This method gets a roles DataTable from the database.
/// </summary>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <returns>The roles DataTable.</returns>
public static DataTable GetRoles(string fields, string orderBy, string where)
{
return CpContent.GetRoles(fields, orderBy, where, Management.Application.SelectConnectionString);
}
/// <summary>
/// This method gets a roles DataTable from the database.
/// </summary>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="connectionString">An execute connection string.</param>
/// <returns>The roles DataTable.</returns>
public static DataTable GetRoles(string fields, string orderBy, string where, string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
return CpContent.GetRoles(fields, orderBy, where, connection);
}
}
/// <summary>
/// This method gets a roles DataTable from the database.
/// </summary>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="connection">An execute connection object.</param>
/// <returns>The roles DataTable.</returns>
public static DataTable GetRoles(string fields, string orderBy, string where, SqlConnection connection)
{
return CpContent.GetRoles(fields, orderBy, where, new SqlCommand(String.Empty, connection));
}
/// <summary>
/// This method gets a roles DataTable from the database.
/// </summary>
/// <param name="fields">The fields clause.</param>
/// <param name="orderBy">The order by clause.</param>
/// <param name="where">The where clause.</param>
/// <param name="command">A select command object.</param>
/// <returns>The roles DataTable.</returns>
public static DataTable GetRoles(string fields, string orderBy, string where, SqlCommand command)
{
if (String.IsNullOrEmpty(fields)) fields = "*";
if (String.IsNullOrEmpty(orderBy)) orderBy = "[Name] ASC";
string whereClause = String.IsNullOrEmpty(where) ? String.Empty : " WHERE " + where;
string sql = String.Format("SELECT {0} FROM cpsys_Roles{1} ORDER BY {2}", fields, where, orderBy);
HttpContext.Current.Trace.Write("GetRoles", sql);
command.CommandText = sql;
command.CommandType = CommandType.Text;
DataTable dt = new DataTable("cpsys_Roles");
if (command.Connection == null) command.Connection = new SqlConnection();
bool opened = false;
try
{
if (command.Connection.State == ConnectionState.Closed)
{
if (String.IsNullOrEmpty(command.Connection.ConnectionString))
command.Connection.ConnectionString = Management.Application.ExecuteConnectionString;
opened = true;
//HttpContext.Current.Trace.Warn("opened", opened.ToString());
command.Connection.Open();
}
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);
}
finally
{
if (opened) command.Connection.Close();
}
return dt;
}
/// <summary>
/// This method gets the roles table from the current DataSet.
/// </summary>
/// <returns>The roles DataTable.</returns>
public DataTable Roles()
{
return this.Tables["cpsys_Roles"];
}
#endregion
#region relationships
/// <summary>
/// This method adds the role DataRelations to the DataSet.
/// </summary>
/// <param name="relationSystemName">The relation system name.</param>
/// <returns>The DataRelation.</returns>
public DataRelation AddRolesRelationship(string relationSystemName)
{
return this.AddRolesRelationship(relationSystemName, String.Empty);
}
/// <summary>
/// This method adds the role DataRelations to the DataSet.
/// </summary>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="where">The where clause.</param>
/// <returns>The DataRelation.</returns>
public DataRelation AddRolesRelationship(string relationSystemName, string where)
{
return this.AddRolesRelationship(relationSystemName, where, this.SystemFilterOptions);
}
/// <summary>
/// This method adds the role DataRelations to the DataSet.
/// </summary>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>The DataRelation.</returns>
public DataRelation AddRolesRelationship(string relationSystemName, string where, SystemFilterOptions filterOptions)
{
if (!this.Tables.Contains(relationSystemName)) throw new NullReferenceException("You must add the relation table before adding the roles relationship.");
string lookupTable = relationSystemName + "InRoles";
if (!this.Tables.Contains(lookupTable))
{
string sql = String.Format("SELECT DataId, RoleId FROM cpsys_DataCurrentInRoles WHERE (DataId IN ({0}))", CpContent.GetRelationSql(relationSystemName, "DataId", String.Empty, where, filterOptions));
this.AddTable(lookupTable, sql);
}
if (this.Tables.Contains("cpsys_Roles")) this.AddRelationship("RoleIn" + relationSystemName, "cpsys_Roles", "RoleId", lookupTable, "RoleId");
return this.AddRelationship(lookupTable, relationSystemName, "DataId", lookupTable, "DataId");
}
/// <summary>
/// This method determines if a row is in a given role.
/// </summary>
/// <param name="data">The DataRow.</param>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="roleId">The role id.</param>
/// <returns>Whether the row is in the given role.</returns>
[Obsolete("This method does not support WebSiteRoleFiltrationAndSecurity=All.")]
public bool DataIsInRole(DataRow data, string relationSystemName, Guid roleId)
{
if (!this.Relations.Contains(relationSystemName + "InRoles")) throw new NullReferenceException("You must call AddRolesRelationship before checking if a row is in a role.");
foreach (DataRow role in data.GetChildRows(this.RolesRelationship(relationSystemName, true)))
{
Guid id = (Guid)role["RoleId"];
if ((id == roleId) || (id == Constants.EveryoneRoleId)) return true;
}
return false;
}
/// <summary>
/// This method gets a role relationship.
/// </summary>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="children">Whether to retrieve the RelationInRoles as opposed to RolesInRelation.</param>
/// <returns>A DataRelation.</returns>
public DataRelation RolesRelationship(string relationSystemName, bool relation)
{
return this.Relations[relation ? relationSystemName + "InRoles" : "RolesIn" + relationSystemName];
}
/// <summary>
/// This method adds the taxonomy DataRelations to the DataSet.
/// </summary>
/// <param name="relationSystemName">The relation system name.</param>
/// <returns>The DataRelation.</returns>
public DataRelation AddTaxonomyRelationship(string relationSystemName)
{
return this.AddTaxonomyRelationship(relationSystemName, String.Empty);
}
/// <summary>
/// This method adds the taxonomy DataRelations to the DataSet.
/// </summary>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="where">The where clause.</param>
/// <returns>The DataRelation.</returns>
public DataRelation AddTaxonomyRelationship(string relationSystemName, string where)
{
return this.AddTaxonomyRelationship(relationSystemName, where, this.SystemFilterOptions);
}
/// <summary>
/// This method adds the taxonomy DataRelations to the DataSet.
/// </summary>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="where">The where clause.</param>
/// <param name="filterOptions">The system filter options.</param>
/// <returns>The DataRelation.</returns>
public DataRelation AddTaxonomyRelationship(string relationSystemName, string where, SystemFilterOptions filterOptions, bool removeRecordDuplicates = false)
{
if (!this.Tables.Contains(relationSystemName)) throw new NullReferenceException("You must add the relation table before adding the taxonomy relationship.");
bool taxonomyExists = this.Tables.Contains("cpsys_Taxonomy");
string lookupTable = relationSystemName + "InTaxonomy";
if (!this.Tables.Contains(lookupTable))
{
string sql = String.Empty;
if (removeRecordDuplicates)
{
sql = String.Format(@"SELECT DataId, TaxonomyId
FROM
(
SELECT DataId, cpsys_DataCurrentInTaxonomy.TaxonomyId, [Path],
ROW_NUMBER() OVER (PARTITION BY DataId ORDER BY LEN([Path]) - LEN(REPLACE([Path], '/', '')) DESC) AS rn
FROM cpsys_DataCurrentInTaxonomy
INNER JOIN cpsys_Taxonomy ON cpsys_Taxonomy.TaxonomyId = cpsys_DataCurrentInTaxonomy.TaxonomyId
WHERE (DataId IN ({0}))
) as DerivedTable
WHERE rn = 1 ", CpContent.GetRelationSql(relationSystemName, "DataId", String.Empty, where, filterOptions));
}
else
{
sql = String.Format("SELECT DataId, TaxonomyId FROM cpsys_DataCurrentInTaxonomy WHERE (DataId IN ({0}))", CpContent.GetRelationSql(relationSystemName, "DataId", String.Empty, where, filterOptions));
}
this.AddTable(lookupTable, sql);
}
if (this.Tables.Contains("cpsys_Taxonomy"))
{
// if the data contains records with no taxonomy add an empty taxonomy row to the cpsys_Taxonomy data table and place the data with no taxonomy in the new empty taxonomy
string newWhere = "(DataId NOT IN (SELECT DataId FROM cpsys_DataCurrentInTaxonomy))";
if (!String.IsNullOrEmpty(where)) newWhere += " AND " + where;
DataTable dt = CpContent.GetRelation(relationSystemName, String.Format("DataId, CONVERT(UNIQUEIDENTIFIER, '{0}') AS TaxonomyId", Guid.Empty), String.Empty, newWhere, filterOptions);
if (dt.Rows.Count > 0)
{
DataTable taxonomy = this.Taxonomy();
DataRow empty = taxonomy.NewRow();
foreach (DataColumn col in taxonomy.Columns)
{
switch (col.ColumnName)
{
case "TaxonomyId":
case "ParentTaxonomyId":
empty[col.Ordinal] = Guid.Empty;
break;
case "SystemName":
empty[col.Ordinal] = "cpsys_NoTaxonomy";
break;
case "Path":
empty[col.Ordinal] = "/";
break;
case "Name":
empty[col.Ordinal] = "No Taxonomy Selected";
break;
case "Description":
empty[col.Ordinal] = "This taxonomy item was automatically generated to contain data with no taxonomy selected.";
break;
case "CreateDate":
case "ModifyDate":
empty[col.Ordinal] = DateTime.UtcNow;
break;
case "cpsys_ChildTaxonomyCount":
case "cpsys_DescendantTaxonomyCount":
case "cpsys_DataCount":
case "cpsys_DescendantTaxonomyDataCount":
empty[col.Ordinal] = 0;
break;
}
}
taxonomy.Rows.Add(empty);
this.Tables[lookupTable].Merge(dt);
}
this.AddRelationship("TaxonomyIn" + relationSystemName, "cpsys_Taxonomy", "TaxonomyId", lookupTable, "TaxonomyId");
}
return this.AddRelationship(lookupTable, relationSystemName, "DataId", lookupTable, "DataId");
}
/// <summary>
/// This method gets a taxonomy relationship.
/// </summary>
/// <param name="relationSystemName">The relation system name.</param>
/// <param name="children">Whether to retrieve the RelationInTaxonomy as opposed to TaxonomyInRelation.</param>
/// <returns>A DataRelation.</returns>
public DataRelation TaxonomyRelationship(string relationSystemName, bool relation)
{
return this.Relations[relation ? relationSystemName + "InTaxonomy" : "TaxonomyIn" + relationSystemName];
}
/// <summary>
/// This method adds a data relationship to the DataSet. This can only be used with single select data list boxes.
/// </summary>
/// <param name="relationshipName">The name of the relationship.</param>
/// <param name="relationSystemName">The system name of the parent relation.</param>
/// <param name="relationColumnName">The name of the parent column. This column must be of the type UniqueIdentifier.</param>
/// <param name="childRelationSystemName">The system name of the child relation.</param>
/// <returns>A DataRelation.</returns>
public DataRelation AddDataRelationship(string relationshipName, string relationSystemName, string relationColumnName, string childRelationSystemName)
{
return this.AddRelationship(relationshipName, relationSystemName, relationColumnName
, childRelationSystemName, "DataId");
}
/// <summary>
/// This method adds a relationship to the DataSet.
/// </summary>
/// <param name="relationshipName">The name of the relationship.</param>
/// <param name="parentTableName">The name of the parent table.</param>
/// <param name="parentColumnName">The name of the parent column.</param>
/// <param name="childTableName">The name of the child table.</param>
/// <param name="childColumnName">The name of the child column.</param>
/// <returns>A DataRelation.</returns>
public DataRelation AddRelationship(string relationshipName, string parentTableName, string parentColumnName, string childTableName, string childColumnName)
{
return this.AddRelationship(relationshipName, parentTableName, parentColumnName, childTableName, childColumnName, false);
}
/// <summary>
/// This method adds a relationship to the DataSet.
/// </summary>
/// <param name="relationshipName">The name of the relationship.</param>
/// <param name="parentTableName">The name of the parent table.</param>
/// <param name="parentColumnName">The name of the parent column.</param>
/// <param name="childTableName">The name of the child table.</param>
/// <param name="childColumnName">The name of the child column.</param>
/// <param name="createConstraints">Whether to create constraints.</param>
/// <returns>A DataRelation.</returns>
public DataRelation AddRelationship(string relationshipName, string parentTableName, string parentColumnName, string childTableName, string childColumnName, bool createConstraints)
{
//HttpContext.Current.Trace.Warn("relationshipName", relationshipName);
//HttpContext.Current.Trace.Warn("parentTableName", parentTableName);
//HttpContext.Current.Trace.Warn("parentColumnName", parentColumnName);
//HttpContext.Current.Trace.Warn("childTableName", childTableName);
//HttpContext.Current.Trace.Warn("childColumnName", childColumnName);
DataRelation dr = new DataRelation(relationshipName, this.Tables[parentTableName].Columns[parentColumnName], this.Tables[childTableName].Columns[childColumnName], createConstraints);
this.Relations.Add(dr);
return dr;
}
/// <summary>
/// This method gets a relationship.
/// </summary>
/// <param name="relationshipName">The name of the relationship.</param>
/// <returns>A DataRelation.</returns>
public DataRelation Relationship(string relationshipName)
{
return this.Relations[relationshipName];
}
/// <summary>
/// This method gets a relation filtered by the given values.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <param name="values">A comma space separated list of value used to create the row filter.</param>
/// <returns>A filtered DataView.</returns>
public DataView GetDataRelationshipView(string viewName, string values)
{
return this.GetDataRelationshipView(viewName, values, String.Empty);
}
/// <summary>
/// This method gets a relation view filtered by the given values.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <param name="values">A comma space separated list of value used to create the row filter.</param>
/// <param name="relationSystemName">The name of the relation that will be viewed.</param>
/// <returns>A filtered DataView.</returns>
public DataView GetDataRelationshipView(string viewName, string values, string relationSystemName)
{
return this.GetDataRelationshipView(viewName, values, relationSystemName, String.Empty);
}
/// <summary>
/// This method gets a relation view filtered by the given values.
/// </summary>
/// <param name="viewName">The name of the view.</param>
/// <param name="values">A comma space separated list of value used to create the row filter.</param>
/// <param name="relationSystemName">The name of the relation that will be viewed.</param>
/// <param name="sort">The sort string to be applied to the DataTable.</param>
/// <returns>A filtered DataView.</returns>
public DataView GetDataRelationshipView(string viewName, string values, string relationSystemName, string sort)
{
if (String.IsNullOrEmpty(values)) values = Guid.Empty.ToString();
StringBuilder rowFilter = new StringBuilder("DataId IN ("); int i = 0;
foreach (string value in values.Split(new string[] { ", " }, StringSplitOptions.RemoveEmptyEntries))
{
if (i > 0) rowFilter.Append(", "); i++;
rowFilter.AppendFormat("CONVERT('{0}', 'System.Guid')", value);
}
rowFilter.Append(")");
HttpContext.Current.Trace.Write("GetDataRelationshipView", rowFilter.ToString());
DataView dv = this.View(viewName);
if (dv == null)
{
if (String.IsNullOrEmpty(sort)) sort = "Title ASC";
return this.AddRelationView(viewName, relationSystemName, sort, rowFilter.ToString());
}
else
{
dv.RowFilter = rowFilter.ToString();
if (!String.IsNullOrEmpty(sort)) dv.Sort = sort;
}
return dv;
}
#endregion
public override string ToString()
{
var sb = new StringBuilder();
foreach (DataTable table in Tables)
{
if (sb.Length > 0) sb.Append(", ");
sb.Append($"{table.TableName} ({table.Rows.Count} rows)");
}
return sb.ToString();
}
}
This method executes a paged stored procedure to get a SqlDataReader with the maximum number of records as defined by the website property.
using (SqlConnection connect = new SqlConnection(ExecuteConnectionString))
{
SqlCommand command = new SqlCommand();
command.Connection = connect;
connect.Open();
using (SqlDataReader dr = CpContent.ExecuteRelationReader(1, 10, "PubIssues", "DataId, Title, CoverImage, Document", "StartDate DESC", String.Empty, SystemFilterOptions.All, command))
{
dgContent.DataSource = dr;
dgContent.DataBind();
}
}
CpContent also has GetTaxonomy method which will help you to retrieve Taxonomy. GetTaxonomy has 17 overrides. this example shows you the most used.
string specialtiesQuery = Request.QueryString["spec"];
string filter = !String.IsNullOrEmpty(specialtiesQuery) ? String.Format("Name='{0}'", specialtiesQuery.SqlEncode()) : String.Empty;
bSpec.DataSource = CpContent.GetTaxonomy(new Guid("d76438e9-4d38-40a3-9c8f-460e8933c1f3"), "Specialties", "TaxonomyId, Name", "SortOrder, Name", filter);
bSpec.DataTextField = "Name";
bSpec.DataValueField = "TaxonomyId";
bSpec.DataBind();