public static IEnumerable <MediaType> GetAllAsList() { var mediaTypes = new List <MediaType>(); using (IRecordsReader dr = SqlHelper.ExecuteReader(m_SQLOptimizedGetAll.Trim(), SqlHelper.CreateParameter("@nodeObjectType", MediaType._objectType))) { while (dr.Read()) { //check if the document id has already been added if (mediaTypes.Where(x => x.Id == dr.Get <int>("id")).Count() == 0) { //create the DocumentType object without setting up MediaType dt = new MediaType(dr.Get <int>("id"), true); //populate it's CMSNode properties dt.PopulateCMSNodeFromReader(dr); //populate it's ContentType properties dt.PopulateContentTypeNodeFromReader(dr); mediaTypes.Add(dt); } else { //we've already created the document type with this id, so we'll add the rest of it's templates to itself var dt = mediaTypes.Where(x => x.Id == dr.Get <int>("id")).Single(); } } } return(mediaTypes.OrderBy(x => x.Text).ToList()); }
/// <summary> /// Loads all properties from database into objects. If this method is re-called, it will re-query the database. /// </summary> /// <remarks> /// This optimizes sql calls. This will first check if all of the properties have been loaded. If not, /// then it will query for all property types for the current version from the db. It will then iterate over each /// cmdPropertyData row and store the id and propertyTypeId in a list for lookup later. Once the records have been /// read, we iterate over the cached property types for this ContentType and create a new property based on /// our stored list of proeprtyTypeIds. We then have a cached list of Property objects which will get returned /// on all subsequent calls and is also used to return a property with calls to getProperty. /// </remarks> private void InitializeProperties() { m_LoadedProperties = new Properties(); if (this.ContentType == null) { return; } //Create anonymous typed list with 2 props, Id and PropertyTypeId of type Int. //This will still be an empty list since the props list is empty. var propData = m_LoadedProperties.Select(x => new { Id = 0, PropertyTypeId = 0 }).ToList(); string sql = @"select id, propertyTypeId from cmsPropertyData where versionId=@versionId"; using (IRecordsReader dr = SqlHelper.ExecuteReader(sql, SqlHelper.CreateParameter("@versionId", Version))) { while (dr.Read()) { //add the item to our list propData.Add(new { Id = dr.Get <int>("id"), PropertyTypeId = dr.Get <int>("propertyTypeId") }); } } foreach (PropertyType pt in this.ContentType.PropertyTypes) { if (pt == null) { continue; } //get the propertyId var property = propData .Where(x => x.PropertyTypeId == pt.Id) .SingleOrDefault(); if (property == null) { continue; } var propertyId = property.Id; Property p = null; try { p = new Property(propertyId, pt); } catch { continue; //this remains from old code... not sure why we would do this? } m_LoadedProperties.Add(p); } }
/// <summary> /// from a collection of document or media properties, create and add any required ghost images to the areaDiv /// </summary> /// <param name="properties"></param> private void CreateGhostImages(IEnumerable <Property> properties) { foreach (Property property in properties) { ImagePoint imagePoint = new ImagePoint(); ((uQuery.IGetProperty)imagePoint).LoadPropertyValue(property.Value.ToString()); if (imagePoint.HasCoordinate) { Image ghostImage = new Image(); ghostImage.ImageUrl = this.Page.ClientScript.GetWebResourceUrl(this.GetType(), "uComponents.DataTypes.ImagePoint.ImagePointGhost.png"); ghostImage.CssClass = "ghost"; // using sql here, else would have to query document and media seperately using (IRecordsReader iRecordsReader = uQuery.SqlHelper.ExecuteReader(@" SELECT A.contentNodeId AS 'Id', B.text AS 'Name', C.Name AS 'PropertyName' FROM cmsPropertyData A LEFT OUTER JOIN umbracoNode B ON A.ContentNodeId = B.Id LEFT OUTER JOIN cmsPropertyType C ON A.propertytypeid = C.id WHERE A.id = @propertyDataId" , uQuery.SqlHelper.CreateParameter("propertyDataId", property.Id))) { iRecordsReader.Read(); // there should only be a single row returned // if the properties are on the same item, then label with property name, otherwise default to labeling with the node / media or member name if (iRecordsReader.Get <int>("Id") == this.CurrentContentId) { ghostImage.ToolTip = iRecordsReader.Get <string>("PropertyName"); } else { ghostImage.ToolTip = iRecordsReader.Get <string>("Name"); } } ghostImage.Style.Add(HtmlTextWriterStyle.Left, (imagePoint.X - 7).ToString() + "px"); ghostImage.Style.Add(HtmlTextWriterStyle.Top, (imagePoint.Y - 7).ToString() + "px"); areaDiv.Controls.Add(ghostImage); } } }
/// <summary> /// Tries the get column value. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="reader">The recordsreader.</param> /// <param name="columnName">Name of the column.</param> /// <param name="value">The value.</param> /// <returns>Returns the value of the specified column.</returns> public static bool TryGetColumnValue <T>(this IRecordsReader reader, string columnName, out T value) { if (reader.ContainsField(columnName) && !reader.IsNull(columnName)) { value = reader.Get <T>(columnName); return(true); } value = default(T); return(false); }
/// <summary> /// Returns all task types stored in the database /// </summary> /// <returns></returns> public static IEnumerable <TaskType> GetAll() { var sql = "SELECT id, alias FROM cmsTaskType"; var types = new List <TaskType>(); using (IRecordsReader dr = SqlHelper.ExecuteReader(sql)) { while (dr.Read()) { types.Add(new TaskType() { _alias = dr.GetString("alias"), _id = Convert.ToInt32(dr.Get <object>("id")) }); } } return(types); }
public Dictionary <string, string> GetRecentDocuments() { //not yet used anywhere, incomplete Authorize(); string sql = "SELECT TOP 100 * FROM cmsDocument ORDER BY updateDate DESC"; IRecordsReader reader = SqlHelper.ExecuteReader(sql); while (reader.Read()) { HttpContext.Current.Response.Write(reader.Get <int>("nodeId").ToString()); } SqlHelper.Dispose(); returnValue.Add("status", status.SUCCESS.ToString()); return(returnValue); }
public static List <UrlTrackerModel> GetUrlTrackerEntries(int?maximumRows, int?startRowIndex, string sortExpression = "", bool _404 = false, bool include410Gone = false, bool showAutoEntries = true, bool showCustomEntries = true, bool showRegexEntries = true, string keyword = "", bool onlyForcedRedirects = false) { List <UrlTrackerModel> urlTrackerEntries = new List <UrlTrackerModel>(); int intKeyword = 0; string query = "SELECT * FROM icUrlTracker WHERE Is404 = @is404 AND RedirectHttpCode != @redirectHttpCode"; if (onlyForcedRedirects) { query = string.Concat(query, " AND ForceRedirect = 1"); } if (!string.IsNullOrEmpty(keyword)) { query = string.Concat(query, " AND (OldUrl LIKE @keyword OR OldUrlQueryString LIKE @keyword OR OldRegex LIKE @keyword OR RedirectUrl LIKE @keyword OR Notes LIKE @keyword"); if (int.TryParse(keyword, out intKeyword)) { query = string.Concat(query, " OR RedirectNodeId = @intKeyword"); } query = string.Concat(query, ")"); } List <IParameter> parameters = new List <IParameter> { _sqlHelper.CreateParameter("is404", _404 ? 1 : 0), _sqlHelper.CreateParameter("redirectHttpCode", include410Gone ? 0 : 410) }; if (!string.IsNullOrEmpty(keyword)) { parameters.Add(_sqlHelper.CreateParameter("keyword", "%" + keyword + "%")); } if (intKeyword != 0) { parameters.Add(_sqlHelper.CreateParameter("intKeyword", intKeyword)); } using (IRecordsReader reader = _sqlHelper.ExecuteReader(query, parameters.ToArray())) { while (reader.Read()) { urlTrackerEntries.Add(new UrlTrackerModel(reader.GetInt("Id"), reader.GetString("OldUrl"), reader.GetString("OldUrlQueryString"), reader.GetString("OldRegex"), reader.GetInt("RedirectRootNodeId"), reader.Get <int?>("RedirectNodeId"), reader.GetString("RedirectUrl"), reader.GetInt("RedirectHttpCode"), reader.GetBoolean("RedirectPassThroughQueryString"), reader.GetBoolean("ForceRedirect"), reader.GetString("Notes"), reader.GetBoolean("Is404"), reader.GetString("Referrer"), reader.GetDateTime("Inserted"))); } } urlTrackerEntries = urlTrackerEntries.Where(x => x.RedirectNodeIsPublished).ToList(); if (!showAutoEntries || !showCustomEntries || !showRegexEntries || !string.IsNullOrEmpty(keyword)) { IEnumerable <UrlTrackerModel> filteredUrlTrackerEntries = urlTrackerEntries; if (!showAutoEntries) { filteredUrlTrackerEntries = filteredUrlTrackerEntries.Where(x => x.ViewType != UrlTrackerViewTypes.Auto); } if (!showCustomEntries) { filteredUrlTrackerEntries = filteredUrlTrackerEntries.Where(x => x.ViewType != UrlTrackerViewTypes.Custom || (showRegexEntries ? string.IsNullOrEmpty(x.OldUrl) : false)); } if (!showRegexEntries) { filteredUrlTrackerEntries = filteredUrlTrackerEntries.Where(x => !string.IsNullOrEmpty(x.OldUrl)); } //if (!string.IsNullOrEmpty(keyword)) //{ // filteredUrlTrackerEntries = filteredUrlTrackerEntries.Where(x => // (x.CalculatedOldUrl != null && x.CalculatedOldUrl.ToLower().Contains(keyword)) || // (x.CalculatedRedirectUrl != null && x.CalculatedRedirectUrl.ToLower().Contains(keyword)) || // (x.OldRegex != null && x.OldRegex.ToLower().Contains(keyword)) || // (x.Notes != null && x.Notes.ToLower().Contains(keyword)) // ); //} urlTrackerEntries = filteredUrlTrackerEntries.ToList(); } if (!string.IsNullOrEmpty(sortExpression)) { string sortBy = sortExpression; bool isDescending = false; if (sortExpression.ToLowerInvariant().EndsWith(" desc")) { sortBy = sortExpression.Substring(0, sortExpression.Length - " desc".Length); isDescending = true; } switch (sortBy) { case "RedirectRootNodeName": urlTrackerEntries = (isDescending ? urlTrackerEntries.OrderByDescending(x => x.RedirectRootNodeName) : urlTrackerEntries.OrderBy(x => x.RedirectRootNodeName)).ToList(); break; case "CalculatedOldUrl": urlTrackerEntries = (isDescending ? urlTrackerEntries.OrderByDescending(x => x.CalculatedOldUrl) : urlTrackerEntries.OrderBy(x => x.CalculatedOldUrl)).ToList(); break; case "CalculatedRedirectUrl": urlTrackerEntries = (isDescending ? urlTrackerEntries.OrderByDescending(x => x.CalculatedRedirectUrl) : urlTrackerEntries.OrderBy(x => x.CalculatedRedirectUrl)).ToList(); break; case "RedirectHttpCode": urlTrackerEntries = (isDescending ? urlTrackerEntries.OrderByDescending(x => x.RedirectHttpCode) : urlTrackerEntries.OrderBy(x => x.RedirectHttpCode)).ToList(); break; case "Referrer": urlTrackerEntries = (isDescending ? urlTrackerEntries.OrderByDescending(x => x.Referrer) : urlTrackerEntries.OrderBy(x => x.Referrer)).ToList(); break; case "NotFoundCount": urlTrackerEntries = (isDescending ? urlTrackerEntries.OrderByDescending(x => x.NotFoundCount) : urlTrackerEntries.OrderBy(x => x.NotFoundCount)).ToList(); break; case "Notes": urlTrackerEntries = (isDescending ? urlTrackerEntries.OrderByDescending(x => x.Notes) : urlTrackerEntries.OrderBy(x => x.Notes)).ToList(); break; case "Inserted": urlTrackerEntries = (isDescending ? urlTrackerEntries.OrderByDescending(x => x.Inserted) : urlTrackerEntries.OrderBy(x => x.Inserted)).ToList(); break; } } if (startRowIndex.HasValue) { urlTrackerEntries = urlTrackerEntries.Skip(startRowIndex.Value).ToList(); } if (maximumRows.HasValue) { urlTrackerEntries = urlTrackerEntries.Take(maximumRows.Value).ToList(); } return(urlTrackerEntries); }
public static UrlTrackerModel GetUrlTrackerEntryById(int id) { string query = "SELECT * FROM icUrlTracker WHERE Id = @id"; using (IRecordsReader reader = _sqlHelper.ExecuteReader(query, _sqlHelper.CreateParameter("id", id))) { if (reader.Read()) { return(new UrlTrackerModel(reader.GetInt("Id"), reader.GetString("OldUrl"), reader.GetString("OldUrlQueryString"), reader.GetString("OldRegex"), reader.GetInt("RedirectRootNodeId"), reader.Get <int?>("RedirectNodeId"), reader.GetString("RedirectUrl"), reader.GetInt("RedirectHttpCode"), reader.GetBoolean("RedirectPassThroughQueryString"), reader.GetBoolean("ForceRedirect"), reader.GetString("Notes"), reader.GetBoolean("Is404"), reader.GetString("Referrer"), reader.GetDateTime("Inserted"))); } } return(null); }
/// <summary> /// Loads all properties from database into objects. If this method is re-called, it will re-query the database. /// </summary> /// <remarks> /// This optimizes sql calls. This will first check if all of the properties have been loaded. If not, /// then it will query for all property types for the current version from the db. It will then iterate over each /// cmdPropertyData row and store the id and propertyTypeId in a list for lookup later. Once the records have been /// read, we iterate over the cached property types for this ContentType and create a new property based on /// our stored list of proeprtyTypeIds. We then have a cached list of Property objects which will get returned /// on all subsequent calls and is also used to return a property with calls to getProperty. /// </remarks> private void InitializeProperties() { _loadedProperties = new Properties(); if (ContentBase != null) { //NOTE: we will not load any properties where HasIdentity = false - this is because if properties are // added to the property collection that aren't persisted we'll get ysods _loadedProperties.AddRange(ContentBase.Properties.Where(x => x.HasIdentity).Select(x => new Property(x))); return; } if (this.ContentType == null) { return; } //Create anonymous typed list with 2 props, Id and PropertyTypeId of type Int. //This will still be an empty list since the props list is empty. var propData = _loadedProperties.Select(x => new { Id = 0, PropertyTypeId = 0 }).ToList(); string sql = @"select id, propertyTypeId from cmsPropertyData where versionId=@versionId"; using (var sqlHelper = Application.SqlHelper) using (IRecordsReader dr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@versionId", Version))) { while (dr.Read()) { //add the item to our list propData.Add(new { Id = dr.Get <int>("id"), PropertyTypeId = dr.Get <int>("propertyTypeId") }); } } foreach (PropertyType pt in this.ContentType.PropertyTypes) { if (pt == null) { continue; } //get the propertyId var property = propData.LastOrDefault(x => x.PropertyTypeId == pt.Id); if (property == null) { continue; //var prop = Property.MakeNew(pt, this, Version); //property = new {Id = prop.Id, PropertyTypeId = pt.Id}; } var propertyId = property.Id; Property p = null; try { p = new Property(propertyId, pt); } catch { continue; //this remains from old code... not sure why we would do this? } _loadedProperties.Add(p); } }
protected void Page_Load(object sender, EventArgs e) { uToolsCore.Authorize(); List<umbraco.cms.businesslogic.template.Template> allTemplates=umbraco.cms.businesslogic.template.Template.GetAllAsList(); //get the template/doc counts Dictionary<int, int> counts = new Dictionary<int, int>(); string sql = "SELECT templateId, count(*) as count FROM cmsDocument cd LEFT JOIN umbracoNode un on un.id=cd.nodeId WHERE id is not null and trashed=0 and newest=1 and templateId is not null group by templateId order by templateId"; IRecordsReader reader = SqlHelper.ExecuteReader(sql); while (reader.Read()) { counts[reader.Get<int>("templateId")] = reader.Get<int>("count"); } SqlHelper.Dispose(); HtmlGenericControl table, thead, tr, th, td, ul, li; table = new HtmlGenericControl("table"); resultsWrapper.Controls.Add(table); thead = new HtmlGenericControl("thead"); table.Controls.Add(thead); tr = new HtmlGenericControl("tr"); thead.Controls.Add(tr); th = new HtmlGenericControl("th"); tr.Controls.Add(th); th.InnerHtml = "<a href='#'>ID</a>"; th = new HtmlGenericControl("th"); tr.Controls.Add(th); th.InnerHtml = "<a href='#'>Name</a>"; th = new HtmlGenericControl("th"); tr.Controls.Add(th); th.InnerHtml = "<a href='#'>Alias</a>"; th = new HtmlGenericControl("th"); tr.Controls.Add(th); th.InnerHtml = "<a href='#'>GUID</a>"; th = new HtmlGenericControl("th"); tr.Controls.Add(th); th.InnerHtml = "<a href='#'>File Path</a>"; th = new HtmlGenericControl("th"); tr.Controls.Add(th); th.InnerHtml = "<a href='#'>Node Path</a>"; th = new HtmlGenericControl("th"); tr.Controls.Add(th); th.InnerHtml = "<a href='#'>Master Template</a>"; th = new HtmlGenericControl("th"); tr.Controls.Add(th); th.InnerHtml = "<a href='#'>Children?</a>"; th = new HtmlGenericControl("th"); tr.Controls.Add(th); th.InnerHtml = "<a href='#'>Content Placeholders</a>"; th = new HtmlGenericControl("th"); tr.Controls.Add(th); th.InnerHtml = "<a href='#'># Docs Using</a>"; th = new HtmlGenericControl("th"); tr.Controls.Add(th); th.InnerHtml = "<a href='#'>Edit</a>"; foreach (umbraco.cms.businesslogic.template.Template thisTemplate in allTemplates) { tr = new HtmlGenericControl("tr"); table.Controls.Add(tr); td = new HtmlGenericControl("td"); tr.Controls.Add(td); td.InnerHtml = thisTemplate.Id.ToString(); td = new HtmlGenericControl("td"); tr.Controls.Add(td); td.InnerHtml = thisTemplate.Text; td = new HtmlGenericControl("td"); tr.Controls.Add(td); td.InnerHtml = thisTemplate.Alias; td = new HtmlGenericControl("td"); tr.Controls.Add(td); td.InnerHtml = thisTemplate.UniqueId.ToString(); td = new HtmlGenericControl("td"); tr.Controls.Add(td); td.InnerHtml = thisTemplate.TemplateFilePath; td = new HtmlGenericControl("td"); tr.Controls.Add(td); td.InnerHtml = thisTemplate.Path; td = new HtmlGenericControl("td"); tr.Controls.Add(td); td.InnerHtml = thisTemplate.MasterTemplate.ToString(); td = new HtmlGenericControl("td"); tr.Controls.Add(td); td.InnerHtml = thisTemplate.HasChildren.ToString(); ul=new HtmlGenericControl("ul"); foreach (string thisPlaceHolderID in thisTemplate.contentPlaceholderIds()) { li = new HtmlGenericControl("li"); ul.Controls.Add(li); li.InnerHtml = thisPlaceHolderID; } td = new HtmlGenericControl("td"); tr.Controls.Add(td); td.Controls.Add(ul); td = new HtmlGenericControl("td"); tr.Controls.Add(td); try { td.InnerHtml = counts[thisTemplate.Id].ToString(); } catch (Exception e2) { td.InnerHtml = "0"; } td = new HtmlGenericControl("td"); tr.Controls.Add(td); td.InnerHtml = "<a rel='"+thisTemplate.Id+"' class='editTemplate'>Edit</a>"; } }