Building a Dynamic Hierarchy Comment Section with SQL Server Stored Procedures

Introduction

In this article, we will delve into the creation of stored procedures for implementing a Hierarchy Comment Section in SQL Server. The foundation for this system was laid out in a previous article on creating the necessary database tables. If you haven't set up the database yet, you can find detailed instructions in my earlier post on Comment Hierarchy Comments Section Database in SQL Server.

StoredProcedure : spCheckUserCredentials

Our first stored procedure, spCheckUserCredentials, is crucial for user authentication. It checks whether a given username and password combination exists in the UserTable.

CREATE PROCEDURE spCheckUserCredentials(
@Username varchar(25),
@Password varchar(25))
As
 Begin
  Select * from UserTable where Username=@Username and Password=@Password
 End

StoredProcedure : spCommentInsert

The spCommentInsert stored procedure facilitates the insertion of new parent comments into the database table. It takes the username, comment message, and automatically captures the comment date.

CREATE PROCEDUREspCommentInsert(
@Username varchar(25),
@CommentMessage varchar(300))
As
 Begin
  Insert into ParentCommentTable(Username, CommentMessage, CommentDate)
         values(@Username, @CommentMessage, GETDATE())
 End

StoredProcedure : spCommentUpdate

For updating existing comments, we introduce the spCommentUpdate stored procedure. Users can modify their comments by providing the comment ID, username, and the new comment message.

CREATE PROCEDUREspCommentUpdate(
@CommentID int,
@Username varchar(25),
@CommentMessage varchar(300))
As
 Begin
  Update ParentCommentTable set CommentMessage=@CommentMessage 
                            where CommentID=@CommentID and Username=@Username
 End

StoredProcedure : spCommentReplyInsert

Handling comment replies is simplified with the spCommentReplyInsert stored procedure. It allows users to insert replies to existing parent comments, linking them through the ParentCommentID.

CREATE PROCEDUREspCommentReplyInsert(
@Username varchar(25),
@CommentMessage varchar(300),
@ParentCommentID int)
As
 Begin
  Insert into ChildCommentTable(Username, CommentMessage, CommentDate, ParentCommentID)
         values(@Username,@CommentMessage, GETDATE(),@ParentCommentID)
 End

StoredProcedure : spGetParentComment

To retrieve all comments stored in the ParentCommentTable, we introduce the spGetParentComment stored procedure.

CREATE PROCEDUREspGetParentComment
As
 Begin
  Select * from ParentCommentTable
 End

StoredProcedure : spGetParentCommentByUsername

For obtaining comments by a specific user, the spGetParentCommentByUsername stored procedure is utilized. It filters comments based on the provided username.

CREATE PROCEDUREspGetParentCommentUsername(
@Username varchar(25))
As
 Begin
  Select * from ParentCommentTable where Username=@Username
 End

StoredProcedure : spGetChildCommentByParentCommentID

The spGetChildCommentByParentCommentID stored procedure fetches all child comments associated with a particular parent comment ID.

CREATE PROCEDURE spGetParentCommentByParentCommentID(
@ParentCommentID int)
As
 Begin
  Select * from ChildCommentTable where ParentCommentID=@ParentCommentID
 End

Conclusion

With these stored procedures, you now have a comprehensive system for managing a Hierarchy Comment Section in SQL Server. These procedures provide essential functionality, including user authentication, comment insertion and updating, and efficient handling of comment replies. Feel free to incorporate and customize these procedures into your ASP.NET application, providing users with an interactive and dynamic commenting experience. If you want to revisit the earlier steps, the associated links are conveniently placed at the end of this article for your reference.

For more articles in this series, check the following links:

1. Database Design for Hierarchy Comments Section in ASP.NET
2. Stored Procedure Implementation for Hierarchy Comments Section in SQL Server
3. Building a Dynamic Comment Section in ASP.NET
4. Web Design for Comment Section in ASP.NET
5. Creating a Hierarchical Comment Section in ASP.NET – Like Facebook and YouTube
6. Download Source Code of Hierarchical Comment Section