Manage WeBlog module blog comments on CM and CD environments without web services

Posted 07/13/2016 by Yogeshwer Sharma

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 a connection string for custom database in connectionString.config file.
  3. Store the comment details in custom database table.
  4. Create a Sitecore scheduler to migrate the data from Custom database to the 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 – 

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).

  2. 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 – 


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.



Share:

Add your comment

 
 

 

Comments

  • Salesforce Customization Company said:
    2/2/2017 4:42 AM

    Thanks for sharing this nice website. I read it completely and get some interesting website content . I am waiting for your new website pleas share new website information my Email id. Thanks a lot....

  • salesforce implementation said:
    2/9/2017 8:50 PM

    Thanks for website. It is very useful i want to read again and again...

  • salesforce services said:
    2/10/2017 1:35 AM

    Thanks for sharing this nice website. I read it completely and get some interesting website content . I am waiting for your new website pleas share new website information my Email id. Thanks a lot....

  • スーパーコピーブランド専門店 said:
    12/11/2017 7:41 AM

    SALEが開催中 ◆ブラントコピー人気通販店の大SALEが開催中◆ 弊社は長年の豊富な経験と実績を持ち、 ブラントスーパーコピー品の完壁な品質を維持するために、 一流の素材を選択し、 精巧な作り方でまるで本物のようなな製品を造ります。 また、お客様のご注文商品を責任を持ってお届けいたします。

  • ヒューゴボス コピー通販 said:
    7/10/2018 1:35 AM

    2018超限定即完売HUGO BOSS http://chenine.com/brand-123.html ヒューゴボス コピー通販ビジネス用メンズ半袖Tシャツです!ファッション性も機能性も欲しいって人に!吸放湿や断熱効果に優れ、ウールによる防臭効果も持つ機能的なアイテムだ。メンズのインナーとしてはもちろん、シンプルに単品で楽しむこともできる、http://chenine.com/ 偽物 ブランドファッション性と汎用性を備えた1着となっている。

Archive

Syndication