protected void CreateXML()
{
try
{
XmlDocument SettingsXML = new XmlDocument();
if (File.Exists(Server.MapPath(SettingsXMLPath)))
{
SettingsXML.Load(Server.MapPath(SettingsXMLPath));
XmlNodeList nodeXMLList = SettingsXML.SelectNodes("/UserGroups/Authentication");
XmlNode node = CreateNode(SettingsXML);
//Get reference to the book node and append the book node to it
XmlNode userGroupsNode = SettingsXML.SelectSingleNode("/UserGroups");
userGroupsNode.AppendChild(node);
}
else
{
XmlNode declarationNode = SettingsXML.CreateXmlDeclaration("1.0", "utf-8", "");
SettingsXML.AppendChild(declarationNode);
XmlNode userGroupsNode = SettingsXML.CreateElement("/UserGroups");
XmlNode node = CreateNode(SettingsXML);
userGroupsNode.AppendChild(node);
SettingsXML.AppendChild(userGroupsNode);
}
SettingsXML.Save(Server.MapPath(SettingsXMLPath));
if (Flag != "True")
{
lblMessage.Text = "User Registered Successfully.";
}
else
{
lblMessage.Text = "UserId already exists.";
}
txtUserId.Text = "";
txtPassword.Text = "";
}
catch (Exception ex)
{
HttpContext.Current.Response.Write(ex.Message);
}
}
XmlNode CreateNode(XmlDocument doc)
{
XmlNode node = doc.CreateElement("Authentication");
XmlAttribute userIDAttribute = doc.CreateAttribute("UserID");
userIDAttribute.Value = Guid.NewGuid().ToString();
node.Attributes.Append(userIDAttribute);
//Add all the children of the node
XmlNode authType = doc.CreateElement("AuthType");
authType.InnerText = ddlUserType.SelectedValue;
node.AppendChild(authType);
XmlNode accessId = doc.CreateElement("AccessId");
accessId.InnerText = txtUserId.Text;
node.AppendChild(accessId);
XmlNode accessKey = doc.CreateElement("AccessKey");
accessKey.InnerText = txtPassword.Text;
node.AppendChild(accessKey);
XmlNode creationDate = doc.CreateElement("CreationDate");
creationDate.InnerText = DateTime.Now.ToLongDateString();
node.AppendChild(creationDate);
return node;
}
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[sp_WorkingDays]
(
@StartDate DATETIME,
@EndDate DATETIME
)
AS
BEGIN
DECLARE @WeekNumber VARCHAR(50)
--DECLARE @StartDate DATETIME
--DECLARE @EndDate DATETIME
DECLARE @StDate VARCHAR(50)
DECLARE @Yr VARCHAR(10)
DECLARE @Month VARCHAR(10)
DECLARE @EndMonth VARCHAR(10)
DECLARE @WDayCount BIGINT
DECLARE @WDay BIGINT
DECLARE @NumberOfDays INT
DECLARE @lastDayOfMonth BIGINT
DECLARE @WeekDay VARCHAR(50)
DECLARE @Counter BIGINT
DECLARE @Pos BIGINT
DECLARE @HDayCount BIGINT
--SET @startdate = '2010-02-20'
--SET @enddate = '2010-03-31'
SET @EndMonth = MONTH(@Enddate)
-- Calculating Working Day Excluding Sunday
SET @NumberOfDays = DATEDIFF(d, @startdate, @enddate) + 1
SET DATEFIRST 7
SET ROWCOUNT @NumberOfDays
DECLARE @numbers TABLE (i INT IDENTITY(0,1), x BIT)
INSERT INTO @numbers SELECT NULL FROM master.dbo.sysobjects a,
master.dbo.sysobjects b, master.dbo.sysobjects c
SET ROWCOUNT 0
SELECT @WDayCount = COUNT(d)
FROM
(SELECT DATEADD(dd, i, @startdate) d FROM @numbers) dates
WHERE NOT DATEPART(dw, d) = 1
-- Calculation For Working Days Excluding Sunday
SET @StDate = @StartDate
DECLARE outer_Cursor CURSOR FOR
SELECT DATEPART(YEAR, d) AS YEAR, DATEPART(MONTH, d) AS MONTH, COUNT(d) AS 'WorkingDay'
FROM
(SELECT DATEADD(dd, i, @startdate) d FROM @numbers) dates
WHERE NOT DATEPART(dw, d) = 1 GROUP BY DATEPART(YEAR, d), DATEPART(MONTH, d)
ORDER BY 1, 2
OPEN outer_Cursor
FETCH NEXT FROM outer_Cursor INTO @Yr,@Month,@WDay
WHILE @@FETCH_STATUS = 0
BEGIN
-- Year wise Month Checking
IF @Yr = YEAR(@EndDate)
BEGIN
IF MONTH(@StDate) <= @EndMonth
BEGIN
IF @Pos = 1
BEGIN
SET @StDate = CONVERT(DATETIME,'01' + '-' + CONVERT(VARCHAR,MONTH(@StDate)) + '-' + CONVERT(VARCHAR,YEAR(@StDate)),103)
END
END
ELSE
BEGIN
SET @StDate = @Enddate
END
END
ELSE
BEGIN
IF @Pos = 1
BEGIN
SET @StDate = CONVERT(DATETIME,'01' + '-' + CONVERT(VARCHAR,MONTH(@StDate)) + '-' + CONVERT(VARCHAR,YEAR(@StDate)),103)
END
END
-- Calculate The Week No
SELECT @lastDayOfMonth = DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StDate)+1,0)))
IF MONTH(@StDate) = @EndMonth
BEGIN
SET @lastDayOfMonth = DAY(@Enddate)
END
SET @Counter = DAY(@StDate)
WHILE @Counter <= @lastDayOfMonth
BEGIN
-- Code to check weather it is holiday
SELECT @HDayCount=COUNT(*) FROM HoliDayList WHERE HoliDay = @StDate
IF @HDayCount > 0
BEGIN
-- Calling function to get WeekDay Name
SET @WeekDay = dbo.GetWeekDayNameOfDate(@StDate)
IF CONVERT(BIGINT,DAY(@StDate)) <= @lastDayOfMonth
BEGIN
IF @WeekDay = 'Saturday'
BEGIN
IF MONTH(@StDate) < 10
BEGIN
SET @Month = '0' + CONVERT(VARCHAR(2),MONTH(@StDate))
END
ELSE
BEGIN
SET @Month = MONTH(@StDate)
END
-- Calling Function to get Week No
SET @WeekNumber = dbo.fn_WeekDays(@Month,@StDate)
IF @WeekNumber <> 2 OR @WeekNumber <> 4
BEGIN
SET @WDayCount = @WDayCount - 1
END
END
ELSE IF @WeekDay <> 'Sunday'
BEGIN
SET @WDayCount = @WDayCount - 1
END
SET @StDate = CONVERT(DATETIME,DATEADD(d,1,@StDate),103)
END
END
ELSE
BEGIN
-- Calling function to get WeekDay
SET @WeekDay = dbo.GetWeekDayNameOfDate(@StDate)
IF CONVERT(BIGINT,DAY(@StDate)) <= @lastDayOfMonth
BEGIN
IF @WeekDay = 'Saturday'
BEGIN
IF MONTH(@StDate) < 10
BEGIN
SET @Month = '0' + CONVERT(VARCHAR(2),MONTH(@StDate))
END
ELSE
BEGIN
SET @Month = MONTH(@StDate)
END
-- Calling Function to get Week No
SET @WeekNumber = dbo.fn_WeekDays(@Month,@StDate)
IF @WeekNumber = 2 OR @WeekNumber = 4
BEGIN
SET @WDayCount = @WDayCount - 1
END
END
SET @StDate = CONVERT(DATETIME,DATEADD(d,1,@StDate),103)
END
END
SET @Counter = @Counter + 1
END
SET @Pos = 1
SET @WeekNumber = 1
FETCH NEXT FROM outer_Cursor INTO @Yr,@Month,@WDay
END
CLOSE outer_Cursor
DEALLOCATE outer_Cursor
SELECT @WDayCount AS TotalWorkingDays, (@WDayCount * 9) AS TotalWorkingHours
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[sp_OrderId]
AS
BEGIN
DECLARE @Date VARCHAR(50)
DECLARE @Time VARCHAR(50)
DECLARE @Num INT
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @Name VARCHAR(50)
DECLARE @TimeFormat VARCHAR(50)
DECLARE @OrderId VARCHAR(50)
SELECT @Date = CONVERT(VARCHAR(50), GETDATE(),112)
SELECT @Time = Convert(Varchar(50),GETDATE(), 114)
SET @Num = 0
SET @Pos = 1
SET @TimeFormat = ''
WHILE(@Pos <= LEN(@Time))
BEGIN
SELECT @NextPos = CHARINDEX(N':', @Time, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@Time) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@Time, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1
IF @TimeFormat = ''
SET @TimeFormat = @Name
ELSE
SET @TimeFormat = @TimeFormat + @Name
SET @Num = @Num + 1
END
SET @OrderId = @Date + @TimeFormat
SELECT @OrderId = RTRIM(LTRIM(SUBSTRING(@OrderId, 1, len(@OrderId) - 3 )))
SELECT CONVERT(BIGINT, @OrderId) AS OrderId
END
Hey Friends,
here is the code to generate resize image at the time of uploading. Hope It will help.
public string ResizeBookImage(FileUpload ftpControl, int intWidth, int intHeight, string UploadDirectory, string strTempDirectory)
{
string strFileName = "";
if (ftpControl.HasFile)
{
imgResizedName = Guid.NewGuid();
string ext = Path.GetExtension(ftpControl.FileName);
strFileName = imgResizedName.ToString();
string strFName = ftpControl.FileName;
ftpControl.SaveAs(strTempDirectory + ftpControl.FileName);
string strFCName = strTempDirectory + strFName;
System.Drawing.Image image = System.Drawing.Image.FromFile(strFCName);
int srcWidth = image.Width;
int srcHeight = image.Height;
//int thumbHeight = (srcHeight / srcWidth) * thumbWidth;
Bitmap bmp = new Bitmap(intWidth, intHeight);
string dest = UploadDirectory + strFileName + ".jpg";
System.Drawing.Graphics gr = System.Drawing.Graphics.FromImage(bmp);
gr.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;
gr.CompositingQuality = System.Drawing.Drawing2D.CompositingQuality.HighQuality;
gr.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.High;
System.Drawing.Rectangle rectDestination = new System.Drawing.Rectangle(0, 0, intWidth, intHeight);
gr.DrawImage(image, rectDestination, 0, 0, srcWidth, srcHeight, GraphicsUnit.Pixel);
bmp.Save(dest, System.Drawing.Imaging.ImageFormat.Jpeg);
bmp.Dispose();
image.Dispose();
File.Delete(strTempDirectory + strFName);
}
return strFileName;
}
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchString]
(
@Keywords VARCHAR(MAX),
@SearchType VARCHAR(100)
)
AS
DECLARE @SearchText VARCHAR(MAX)
DECLARE @Num INT
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @TableCount INT
DECLARE @CountSep INT
DECLARE @Count INT
DECLARE @BookId UNIQUEIDENTIFIER
DECLARE @AuthorName VARCHAR(500)
DECLARE @ISBN VARCHAR(50)
DECLARE @BookName VARCHAR(500)
DECLARE @PubYear VARCHAR(20)
BEGIN
IF @SearchType='General'
BEGIN
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='temp')
SET @TableCount = 1
ELSE
BEGIN
CREATE TABLE temp (BookID UNIQUEIDENTIFIER,AuthorName VARCHAR(500),
ISBN VARCHAR(50),BookName VARCHAR(500),PubYear VARCHAR(20))
SET @TableCount = 0
END
SELECT @CountSep = CHARINDEX(N':', @Keywords,1)
IF @CountSep = 0
BEGIN
SELECT BookID FROM (
SELECT DISTINCT Books.BookID AS BookID, Books.PublisherId, Books.IsDeleted FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN BookAuthor ON BookAuthor.BookId=Books.BookId INNER JOIN
Author ON Author.AuthorId=BookAuthor.AuthorId INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE BookPrice.ISBN like'%' + @Keywords + '%' OR
Books.BookName like'%' + @Keywords + '%'
OR Books.YearofPublication like'%' + @Keywords + '%'
OR Author.AuthorName like'%' + @Keywords + '%' AND Publisher.Publisher='XXX' ) AS tmpBook
INNER JOIN Publisher ON tmpBook.PublisherId=Publisher.PublisherId WHERE Publisher.Publisher='XXX'
AND tmpBook.IsDeleted = 0
END
ELSE
BEGIN
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@Keywords))
BEGIN
SELECT @NextPos = CHARINDEX(N':', @Keywords, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
BEGIN
SELECT @NextPos = LEN(@Keywords) + 1
END
SELECT @SearchText = RTRIM(LTRIM(SUBSTRING(@Keywords, @Pos, @NextPos - @Pos)))
IF @Pos = 1
BEGIN
DECLARE Outer_Cursor CURSOR FOR
SELECT BookID,AuthorName,ISBN,BookName,PubYear FROM (
SELECT DISTINCT Books.BookID AS BookID, Books.PublisherId,
Books.IsDeleted, Author.AuthorName AS AuthorName,
BookPrice.ISBN AS ISBN,
Books.BookName AS BookName, Books.YearofPublication AS PubYear
FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN BookAuthor ON BookAuthor.BookId=Books.BookId INNER JOIN
Author ON Author.AuthorId=BookAuthor.AuthorId INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE BookPrice.ISBN like'%' + @SearchText + '%' OR
Books.BookName like'%' + @SearchText + '%'
OR Books.YearofPublication like'%' + @SearchText + '%'
OR Author.AuthorName like'%' + @SearchText + '%' AND Publisher.Publisher='XXX' ) AS tmpBook
INNER JOIN Publisher ON tmpBook.PublisherId=Publisher.PublisherId WHERE Publisher.Publisher='XXX'
AND tmpBook.IsDeleted = 0
OPEN Outer_Cursor
FETCH NEXT FROM outer_cursor INTO @BookId, @AuthorName, @ISBN, @BookName, @PubYear
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO temp (BookId, AuthorName, ISBN, BookName, PubYear)
VALUES (@BookId, @AuthorName, @ISBN, @BookName, @PubYear)
FETCH NEXT FROM outer_cursor INTO @BookId, @AuthorName, @ISBN, @BookName, @PubYear
END
CLOSE outer_cursor
DEALLOCATE outer_cursor
END
ELSE
BEGIN
DELETE FROM temp WHERE BookID NOT IN (
SELECT BookID FROM temp
WHERE ISBN like'%' + @SearchText + '%' OR
BookName like'%' + @SearchText + '%'
OR PubYear like'%' + @SearchText + '%'
OR AuthorName like'%' + @SearchText + '%'
)
END
SET @Pos = @NextPos+1
END
SELECT BookID FROM temp AS tblRecordset
DROP TABLE temp
END
END
ELSE IF @SearchType='ISBN'
SELECT DISTINCT Books.BookID AS BookID FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND BookPrice.ISBN like'%' + @Keywords + '%' AND BookPrice.IsDeleted=0
ELSE IF @SearchType='Title'
SELECT DISTINCT Books.BookID AS BookID FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND Books.BookName like'%' + @Keywords + '%' AND Books.IsDeleted=0
ELSE IF @SearchType='Author'
SELECT DISTINCT Books.BookID AS BookID FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN BookAuthor ON BookAuthor.BookId=Books.BookId INNER JOIN
Author ON Author.AuthorId=BookAuthor.AuthorId INNER JOIN Publisher ON Books.PublisherId = Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND Author.AuthorName like'%' + @Keywords + '%' AND BookAuthor.IsDeleted=0
ELSE IF @SearchType='PublicationYear'
SELECT DISTINCT Books.BookID AS BookID FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND Books.YearofPublication like'%' + @Keywords + '%' AND Books.IsDeleted=0
ELSE IF @SearchType='Subject'
SELECT DISTINCT BooksCategory.BookID AS BookID FROM
BooksCategory INNER JOIN Category ON BooksCategory.CategoryId=Category.CategoryId
INNER JOIN Books ON BooksCategory.BookId = Books.BookId INNER JOIN Publisher
ON Books.PublisherId=Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND Category.Category like'%' + @Keywords + '%' AND BooksCategory.IsDeleted=0
END
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();
}
}
}