Hi All,
Here is the code to create Blog Archive in asp.net c#.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
ApplicationGeneral appGen = new ApplicationGeneral();
int Rows;
DataTable dt, dtMonth, dtTitle;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
PopulateBlog();
PopulateArchive(trArchive);
PopulateBlogCategory();
}
}
protected void PopulateBlog()
{
string strSqlSelectBlog = "";
dt = new DataTable();
if (Request.QueryString["YearId"] == null && Request.QueryString["MonthId"] == null)
{
strSqlSelectBlog = @"SELECT TOP 5 * FROM BlogMain WHERE YEAR(GETDATE())=YEAR(PostingDate) ORDER BY PostingDate DESC";
SqlParameter[] param = null;
dt = appGen.ExecuteReader(strSqlSelectBlog, param);
}
else if (Request.QueryString["YearId"] != null && Request.QueryString["MonthId"] == null)
{
strSqlSelectBlog = @"SELECT * FROM BlogMain WHERE YEAR(PostingDate)=@Year ORDER BY PostingDate DESC";
SqlParameter[] param =
{
appGen.MakeParam("@Year",SqlDbType.Int,16,ParameterDirection.Input,Convert.ToInt32(Request.QueryString["YearId"]))
};
dt = appGen.ExecuteReader(strSqlSelectBlog, param);
}
else if(Request.QueryString["YearId"] != null && Request.QueryString["MonthId"] != null)
{
strSqlSelectBlog = @"SELECT * FROM BlogMain WHERE YEAR(PostingDate)=@Year AND MONTH(PostingDate)=@Month ORDER BY PostingDate DESC";
SqlParameter[] param =
{
appGen.MakeParam("@Year",SqlDbType.Int,16,ParameterDirection.Input,Convert.ToInt32(Request.QueryString["YearId"])),
appGen.MakeParam("@Month",SqlDbType.Int,16,ParameterDirection.Input,Convert.ToInt32(Request.QueryString["MonthId"]))
};
dt = appGen.ExecuteReader(strSqlSelectBlog, param);
}
if (dt.Rows.Count > 0)
{
dlBlog.DataSource = dt;
dlBlog.DataBind();
}
}
protected void dlBlog_ItemDataBound(object sender, DataListItemEventArgs e)
{
Label lblBlogId = (Label)e.Item.FindControl("lblBlogId");
Label lblNoofComments = (Label)e.Item.FindControl("lblNoofComments");
Label lblLatestComment = (Label)e.Item.FindControl("lblLatestComment");
LinkButton lnkMore = (LinkButton)e.Item.FindControl("lnkMore");
Label lblComment = (Label)e.Item.FindControl("lblComment");
Label lblCommentBy = (Label)e.Item.FindControl("lblCommentBy");
Label lblCommentDate = (Label)e.Item.FindControl("lblCommentDate");
string strSqlCount = "SELECT ISNULL(Count(*),0) AS CommentCount FROM BlogComment WHERE BlogId=@BlogId AND IsApproved=1";
SqlParameter[] param ={ appGen.MakeParam("@BlogId", SqlDbType.VarChar, 50, ParameterDirection.Input, lblBlogId.Text) };
Rows = appGen.ExecuteScalar(strSqlCount, param);
if (Rows > 0)
{
lblNoofComments.Text = Rows + " Comments";
lblLatestComment.Visible = true;
lnkMore.Visible = true;
string strSqlGetComment = "SELECT TOP 1 * FROM BlogComment WHERE BlogId=@BlogId AND IsApproved=1 ORDER By CommentDate DESC";
SqlParameter[] parameter ={ appGen.MakeParam("@BlogId", SqlDbType.UniqueIdentifier, 16, ParameterDirection.Input, new Guid(lblBlogId.Text)) };
dt = new DataTable();
dt = appGen.ExecuteReader(strSqlGetComment, parameter);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
lblComment.Text = Server.HtmlDecode(dr["Comment"].ToString());
lblCommentBy.Text = dr["CommentedBy"].ToString();
lblCommentDate.Text = Convert.ToDateTime(dr["CommentDate"].ToString()).ToLongDateString();
}
}
}
else
{
lblNoofComments.Text = "Be the first one to post comment";
lblLatestComment.Visible = false;
lnkMore.Visible = false;
}
}
protected void PopulateArchive(TreeView trvControl)
{
string strSqlArchiveYear = @"SELECT YearList, TotalCount,
CONVERT(VARCHAR(50),YearList) + ' (' + CONVERT(VARCHAR(5),TotalCount) + ')' AS BlogYearList FROM (
SELECT YEAR(PostingDate) AS YearList, COUNT(*) AS TotalCount FROM BlogMain
GROUP BY YEAR(PostingDate) ) AS EXPR ORDER BY YearList DESC";
dt = new DataTable();
dt = appGen.ExecuteReader(strSqlArchiveYear, null);
if (dt.Rows.Count > 0)
{
foreach (DataRow drYear in dt.Rows)
{
TreeNode rootNode = new TreeNode();
rootNode.Text = drYear["BlogYearList"].ToString();
rootNode.NavigateUrl = "~/Default.aspx?YearId=" + drYear["YearList"].ToString();
rootNode.SelectAction = TreeNodeSelectAction.Expand;
string strSqlArchiveMonth = @"SELECT MonthName, TotalCount,PostMonth,YearList,
MonthName+ ' (' + CONVERT(VARCHAR(5),TotalCount) + ')' AS BlogMonthList FROM (
SELECT DATENAME(m,PostingDate) AS MonthName, MONTH(PostingDate) AS PostMonth ,
COUNT(*) AS TotalCount,YEAR(PostingDate) AS YearList FROM BlogMain
WHERE YEAR(PostingDate) =@Year GROUP BY DATENAME(m,PostingDate),MONTH(PostingDate),YEAR(PostingDate)) AS EXPR
ORDER BY PostMonth DESC";
SqlParameter[] param ={ appGen.MakeParam("@Year", SqlDbType.VarChar, 20, ParameterDirection.Input, drYear["YearList"].ToString()) };
dtMonth = new DataTable();
dtMonth = appGen.ExecuteReader(strSqlArchiveMonth, param);
PopulateChildNodes(rootNode, trvControl, dtMonth);
}
}
}
protected void PopulateChildNodes(TreeNode rootNode, TreeView trvControl, DataTable dtMonth)
{
if(dtMonth.Rows.Count>0)
{
int rowCount = 0;
foreach(DataRow drMonth in dtMonth.Rows)
{
TreeNode childNode=new TreeNode();
childNode.Text = drMonth["BlogMonthList"].ToString();
childNode.NavigateUrl = "~/Default.aspx?YearId=" + drMonth["YearList"].ToString() + "&MonthId=" + drMonth["PostMonth"].ToString();
childNode.SelectAction = TreeNodeSelectAction.Expand;
rootNode.ChildNodes.Add(childNode);
TreeNode trvNode = new TreeNode();
trvNode = rootNode.ChildNodes[rowCount];
string strSqlTitle = "SELECT Title, BlogId, YEAR(PostingDate) AS YearList FROM BlogMain WHERE YEAR(PostingDate)=@Year AND MONTH(PostingDate)=@Month";
SqlParameter[] param ={ appGen.MakeParam("@Year", SqlDbType.VarChar, 50, ParameterDirection.Input, drMonth["YearList"].ToString()), appGen.MakeParam("@Month", SqlDbType.VarChar, 50, ParameterDirection.Input, drMonth["PostMonth"].ToString()) };
dtTitle = new DataTable();
dtTitle = appGen.ExecuteReader(strSqlTitle, param);
PopulateTitle(trvNode, trvControl, dtTitle);
rowCount = rowCount + 1;
}
trvControl.Nodes.Add(rootNode);
}
}
protected void PopulateTitle(TreeNode rootNode, TreeView trvControl, DataTable dtTitle)
{
if (dtTitle.Rows.Count > 0)
{
foreach (DataRow drTitle in dtTitle.Rows)
{
TreeNode childTitleNode = new TreeNode();
childTitleNode.Text = drTitle["Title"].ToString();
childTitleNode.NavigateUrl = "~/PostComment.aspx?Id=" + drTitle["BlogId"].ToString();
childTitleNode.SelectAction = TreeNodeSelectAction.Expand;
rootNode.ChildNodes.Add(childTitleNode);
}
}
}
protected void PopulateBlogCategory()
{
string strBlogCat = "SELECT * FROM BlogCategory ORDER BY Category DESC";
dt = new DataTable();
dt = appGen.ExecuteReader(strBlogCat, null);
if (dt.Rows.Count > 0)
{
dlBlogCategory.DataSource = dt;
dlBlogCategory.DataBind();
}
}
}
9:08 PM
Subscribe to:
Posts (Atom)