How to create archives in a blog

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();
        }
    }
}