To preserve some database space on my .Text installation, I have deleted some
of the trackbacks, links and null records. All my trackbacks are p o k e r links
anyway. There were also p o k e r comments but I got rid of them with
Clearscreen HIP Captcha.
My other 2 little improvement to .Text is the "Recent Posts" and
"Popular Posts" modules. I was
jealous about other blogging engines and their modules so I have decided to
write this one. I have the version 0.95.102 code of .Text. I basically created a stored
procedure first to read last 10 records for the displayed blog and then
constructed a repeater with title and the permanent link of the entry. I have
also created a small method to construct permanent links, I am sure there is a
ready to use function somewhere in .Text to format permanent links.
Here is the stored procedure:
| CREATE Proc blog_Last10Items ( @PostType int, –later to list comments @BlogID int ) AS Select Top 10 A.id, A.Title, A.DateAdded, B.Application From blog_Content A, blog_Config B Where posttype = @PostType AND A.BlogID = @BlogID AND A.BlogID = B.BlogID Order By DateAdded ASC |
I have created a class giving the name Last10Module in
Dottext.Web.UI.Controls and written the code
below:
|
.cf { font-family: Courier; font-size: 10pt; color: black;} .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: olive; } .cb3 { color: purple; } namespace Dottext.Web.UI.Controls { using System; using System.Configuration; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using Dottext.Framework.Data; using Dottext.Framework.Configuration; using Dottext.Framework.Components; /// <summary> /// Summary description for Last10Module. /// </summary> public class Last10Module : BaseControl { protected System.Web.UI.WebControls.Repeater LastEntries;
protected override void OnLoad(EventArgs e) { base.OnLoad (e);
string sql = “blog_Last10Items”; string conn = Dottext.Framework.Providers.DbProvider.Instance().ConnectionString;
ArrayList myLastItems = new ArrayList();
int BlogID = CurrentBlog.BlogID; //current blog id is here — finally found it
//PostType = 1 is the entries SqlParameter[] p = { SqlHelper.MakeInParam(“@PostType”,SqlDbType.Int,4,1), SqlHelper.MakeInParam(“@BlogID”,SqlDbType.Int,4,BlogID) };
DataTable dt = SqlHelper.ExecuteDataTable(conn,CommandType.StoredProcedure,sql,p);
int count = dt.Rows.Count;
for(int i = 0; i<count; i++) { DataRow dr = dt.Rows[i];
string title = (string) dr[“Title”];
DateTime dateAdded =(DateTime) dr[“DateAdded”]; string id = dr[“id”].ToString();
string myURL = URLFormat(application, dateAdded,id);
myLastItems.Add(new PositionItems(title,myURL));
}
LastEntries.DataSource = myLastItems; LastEntries.DataBind(); }
public string URLFormat(string dbApplication, DateTime dbDateAdded, string dbEntryID) { string myURL = ConfigurationSettings.AppSettings[“AggregateURL”];
myURL = myURL + “/” + dbApplication + “/”;
string myYear = dbDateAdded.Year.ToString(); string myMonth = dbDateAdded.Month.ToString(); string myDay = dbDateAdded.Day.ToString();
int Month = int.Parse(myMonth); int Day = int.Parse(myDay);
if (Month<10) { myMonth = String.Concat(“0”,myMonth); } if (Day<10) { myDay = String.Concat(“0”,myDay); }
myURL = myURL +“archive” + “/” + myYear + “/” +
return myURL; } }
public class PositionItems {
private string title; private string URL;
public PositionItems(string title, string URL) { this.title = title; this.URL = URL; }
public string Title { get { return title; } }
public string url { get { return URL; } } } } |
And here is the ascx file:
|
.cf { font-family: Courier; font-size: 10pt; color: black;} .cl { margin: 0px; } .cb1 { background: yellow; } .cb2 { color: blue; } .cb3 { color: maroon; } .cb4 { color: fuchsia; } .cb5 { color: red; } <%@ Control Language=”c#” AutoEventWireup=”false” Inherits=”Dottext.Web.UI.Controls.Last10Module” %> <h3>Recent Posts</h3> <asp:Repeater id=”LastEntries” runat=”server”> <ItemTemplate> <div class=”item”> <font size=”2″> <a href=”<%# DataBinder.Eval(Container.DataItem, “url”) %>“> <%# DataBinder.Eval(Container.DataItem, “Title”) %> </a> </font> </div> </ItemTemplate> </asp:Repeater> |
The rest is installing the ascx into skin templates. Once you copy
the file into Skin/Controls folder, add the necessary entries to
PageTemplate.ascx.
"Popular Entries" module was pretty much the same as this one
except the stored procedure is different as I need to read it from the
Blog_Referrals and Blog_Content tables:
| CREATE PROCEDURE blog_GetTop10byBlogId @BlogId int AS Select Top 10 Sum(A.count) tCount, A.entryid, B.title, B.dateadded From Blog_Referrals A, Blog_Content B Where A.BlogId = @BlogId –param AND A.entryid = B.[id] Group By A.entryid, B.Title, B.dateadded Order By tCount DESC |
May not be the best piece of code but kept me busy during the
weekend given my wife working at night shift at the hotel.
Have fun.
