Manage Blog comments on CM and CD environments without web services

At first really thankful to my senior Yogeshwer Sharma for this blog.

While working with Sitecore WeBlog module we found that some times with configuration of our CM and CD environments, the CD server doesn’t allow calling of the WeBlog web service, which is responsible for creating comment items on the CM server. As we know WeBlog stores the blog comments on CM server via a web service called from the CD servers.

We have seen similar issue with our configuration also and due to some security constraints on the CD servers the WeBlog web service was not able to be executed.

Here we started thinking about some alternate solution to manage blog comments in the Sitecore CM server without any web service.

Now with our one day analysis we came up with an idea to manage blog comments same as WeBlog is managing with the help of a custom SQL database.

Blog Comments

We divided our approach in below steps –

  1. Create a custom database to store comment details.
  2. Add connection string for custom database in connectionString.config file.
  3. Store comment details in custom database table.
  4. Create a Sitecore scheduler to migrate the data from Custom database to Sitecore content tree.

With the help of this approach we don’t need to tweak any of the security settings on the CD server.

Now we will go through above steps in details –

1.   Create a custom database to store comment details –

 We have created a custom DB which will be common for all CD server’s as well as it will be accessible from the CM server. In this database we have created a table with all the fields of Comment Form as well as we have also added some additional fields as given below.

  1. BlogID – To store blog detail page id to map with comments.
  2. FullName – FullName field value of comment form.
  3. Email – Email field value of comment form.
  4. Comment – Comment field value of comment form.
  5. IsSync -Using as a flag to keep track of sync process of moving comments from custom DB to Sitecore.
  6. CommentDateTime – Comment adding date and time.
  7. UpdateDateTime – Sync date and time with Sitecore.

Click here to download SQL script to create database and table.

Note – This script file also contains some stored procedures, which we will use in later steps. You can execute complete script so you don’t need to put efforts in later steps.

2.   Add connection string for custom database in connectionString.config file. –

Add new connection string for custom DB in connectionString.config file as given below.

<add name=”custom” connectionString=”user id=[username];password=[password];Data Source=[Datasource];Database=[Database Name]” />

3.   Store comment details in the custom database table –

We have written code to store comment form details in custom database with the help of ADO.NET and SQL procedures.

After submitting the comments form we are storing data into our custom database. Here we are storing comment form fields (Fullname, Email and Comment), BlogID current blog page item ID, IsSync (I will explain the uses of this field later), CommentDateTime (Comment Entry date and time) and UpdateDateTime.

Now let me explain why we are using BlogID and IsSync fields.

Blog ID – We are using this field to map the comment items with associated blog detail page while moving comment data from custom database to Sitecore.

IsSync – Default value of this field is set to 0 (Zero). We are using scheduler to migrate data from custom DB to Sitecore and after successfully migrate the record we are updating this field value by 1 (One).  So next time scheduler will execute and it will ignore the record which contains IsSync field value 1(One).

We can store data in SQL server with multiple ways. We have used SQL stored procedure to do this task.

Click here to find SQL script of stored procedures. If you have already executed complete SQL script file as per given in step 1 then you don’t need to run it again.

 

We have created a method in C# class which will run on click of comment form submit button. This method is storing comment form data into custom database, see below code –

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

public void AddComment(string name, string email, string comment, String itemID)

        {

            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["custom"].ToString()))

            {

                using (SqlCommand cmd = new SqlCommand("usp_AddComment", con))

                {

                    try

                    {

                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Parameters.Add("@FullName", SqlDbType.VarChar).Value = name;

                        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = email;

                        cmd.Parameters.Add("@Comment", SqlDbType.VarChar).Value = comment;

                        cmd.Parameters.Add("@BLogID", SqlDbType.VarChar).Value = itemID;

                        con.Open();

                        cmd.ExecuteNonQuery();

                        

                    }

                    catch (Exception ex)

                    {

                        Sitecore.Diagnostics.Log.Info(ex.Message, this);

                    }

                }

            }

     }

4.   Create a Sitecore scheduler to migrate the data from Custom database to Sitecore content tree (CM Server).

This is the last step of our functionality and in this step we have created a Sitecore Scheduler, if you are not familiar with how to create scheduler in Sitecore please read this post.

With the help of this scheduler we are performing 2 tasks. One is moving comment data from custom DB to Sitecore (CM server) and other one is updating IsSync field of the custom database if the record is successfully added into Sitecore.

To perform this we have created 2 stored procedures in SQL server.

    1. GetUnProcessedComments – This stored procedure will return all the data with IsSync field value 0 (Zero).
  1. UpdateProcessedResults – This stored procedure will update the field value of IsSync with 1 (One) for all successfully processed records. Also we have added custom table type field for the stored procedure input parameters.

Click here to find SQL script of stored procedures. If you have already executed complete SQL script file as per given in step 1 then you don’t need to run it again.

We have created a C# class file for Sitecore scheduler and scheduler will execute this class file code in specific time interval.

  1. Get all un-processed records from custom DB and create comment items under appropriate blog detail page.
  2. If the comment item is successfully created then the IsSync field value is set to 1 (One) so next time when scheduler will go to custom DB for new records (comment items) it will only get data which IsSync field contains value 0 (Zero).

See below C# code to perform above actions –

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

public void Execute(Item[] items, Sitecore.Tasks.CommandItem command, Sitecore.Tasks.ScheduleItem schedule)

        {

            try

            {

                Sitecore.Security.Accounts.User user = Sitecore.Security.Accounts.User.FromName(@"extranet\Anonymous"true);

                using (new UserSwitcher(user))

                {

                    DataSet getUnprocessedComments = new DataSet();

                    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["custom"].ToString()))

                    {

                        using (SqlCommand cmd = new SqlCommand("usp_GetUnProcessedComments", con))

                        {

                            cmd.CommandType = CommandType.StoredProcedure;

                            using (SqlDataAdapter da = new SqlDataAdapter(cmd))

                            {

                                da.Fill(getUnprocessedComments);

                            }

                        }

 

                        Database master = Sitecore.Configuration.Factory.GetDatabase("master");

                        TemplateItem template = master.GetTemplate("[Comment Data Template ID]"); //TODO: Please enter your comment data template ID

            string workflowID = template.StandardValues[Sitecore.FieldIDs.DefaultWorkflow];

                        bool IsSuccessfullyAdded = false;

                        foreach (DataRow DBValue in getUnprocessedComments.Tables[0].Rows)

                        {

                            Item parent = master.Items[DBValue["BlogID"].ToString()];

                            string validItemName = ItemUtil.ProposeValidItemName(DBValue["FullName"].ToString()).Trim();

                            Item item = parent.Add("comments by " + validItemName, template);

                            try

                            {

                                //TODO: Please update field name of comment template

                item.Editing.BeginEdit();

                                item.Fields["Name"].Value = DBValue["FullName"].ToString();

                                item.Fields["Email"].Value = DBValue["Email"].ToString();

                                item.Fields["Comment"].Value = DBValue["Comment"].ToString();

                               

                 if (!string.IsNullOrEmpty(workflowID))

                                {

                                    item.Editing.BeginEdit();

                                    item.Fields[Sitecore.FieldIDs.Workflow].Value = workflowID;

                                    IWorkflow wf = master.WorkflowProvider.GetWorkflow(workflowID);

                                    wf.Start(item);

                                }

                                item.Editing.EndEdit();

                                IsSuccessfullyAdded = true;

                            }

                            catch (Exception ex)

                            {

                                IsSuccessfullyAdded = false;

                                Sitecore.Diagnostics.Log.Info("Comment Scheduler: Error in adding comment item under blog item " + ex.Message, this);

                                break;

                            }

                        }

                        if (IsSuccessfullyAdded == true)

                        {

                            using (SqlCommand cmd = new SqlCommand("usp_UpdateProcessedResults", con))

                            {

                                try

                                {

                                    cmd.CommandType = CommandType.StoredProcedure;

                                    cmd.Parameters.AddWithValue("@commentItems", getUnprocessedComments.Tables[0]);

                                    con.Open();

                                    cmd.ExecuteNonQuery();

                                    con.Close();

                                }

                                catch (Exception ex)

                                {

                                    Sitecore.Diagnostics.Log.Info("Comment Scheduler: Error in updating flag entry for added comment items " + ex.Message, this);

                                    con.Close();

                                }

                            }

                        }

                    }

                }

            }

            catch (Exception ex)

            {

                Sitecore.Diagnostics.Log.Info("Comment Scheduler: Error in Create Comment Scheduler " + ex.Message, this);

            }

        }

Click Here to find the code files and SQL script file on the GitHub. If you have any queries please enter your comments in below comment section.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s