Surendra Sharma

Surendra Sharma

Search This Blog

Saturday, December 3, 2016

LexBlog To Sitecore - Part 3 - XML to SQL Server



This is third article in a series for LexBlog to Sitecore. You can refer second article for LexBlog SQL Server database from here.

This third article is about migrating LexBlog XML file data into SQL Server using Windows Application.

We will migrate content from LexBlog XML file to Sitecore in two phases as 

Phase-I => From LexBlog XML to SQL Server
Phase-II => From SQL Server to Sitecore

LexBlog to Sitecore
LexBlog to Sitecore


Use below code to fetched data from LexBlog XML file to SQL Server for tags, categories, blog items and media assets.

XML File Mapper class


Below is a C# class which is code representation of LexBlog XML file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace XMLtoSQLInserter
{
   public class wordpressxml
    {


        /// <remarks/>
        [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
        [System.Xml.Serialization.XmlRootAttribute(Namespace = "", IsNullable = false)]
        public partial class rss
        {

            private rssChannel channelField;

            private decimal versionField;

            /// <remarks/>
            public rssChannel channel
            {
                get
                {
                    return this.channelField;
                }
                set
                {
                    this.channelField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlAttributeAttribute()]
            public decimal version
            {
                get
                {
                    return this.versionField;
                }
                set
                {
                    this.versionField = value;
                }
            }
        }

        /// <remarks/>
        [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
        public partial class rssChannel
        {

            private string titleField;

            private string linkField;

            private string descriptionField;

            private string pubDateField;

            private string languageField;

            private decimal wxr_versionField;

            private string base_site_urlField;

            private string base_blog_urlField;

            private author[] authorField;

            private category[] categoryField;

            private tag[] tagField;

            private term[] termField;

            private string generatorField;

            private rssChannelItem[] itemField;

            /// <remarks/>
            public string title
            {
                get
                {
                    return this.titleField;
                }
                set
                {
                    this.titleField = value;
                }
            }

            /// <remarks/>
            public string link
            {
                get
                {
                    return this.linkField;
                }
                set
                {
                    this.linkField = value;
                }
            }

            /// <remarks/>
            public string description
            {
                get
                {
                    return this.descriptionField;
                }
                set
                {
                    this.descriptionField = value;
                }
            }

            /// <remarks/>
            public string pubDate
            {
                get
                {
                    return this.pubDateField;
                }
                set
                {
                    this.pubDateField = value;
                }
            }

            /// <remarks/>
            public string language
            {
                get
                {
                    return this.languageField;
                }
                set
                {
                    this.languageField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public decimal wxr_version
            {
                get
                {
                    return this.wxr_versionField;
                }
                set
                {
                    this.wxr_versionField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public string base_site_url
            {
                get
                {
                    return this.base_site_urlField;
                }
                set
                {
                    this.base_site_urlField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public string base_blog_url
            {
                get
                {
                    return this.base_blog_urlField;
                }
                set
                {
                    this.base_blog_urlField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute("author", Namespace = "http://wordpress.org/export/1.2/")]
            public author[] author
            {
                get
                {
                    return this.authorField;
                }
                set
                {
                    this.authorField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute("category", Namespace = "http://wordpress.org/export/1.2/")]
            public category[] category
            {
                get
                {
                    return this.categoryField;
                }
                set
                {
                    this.categoryField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute("tag", Namespace = "http://wordpress.org/export/1.2/")]
            public tag[] tag
            {
                get
                {
                    return this.tagField;
                }
                set
                {
                    this.tagField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute("term", Namespace = "http://wordpress.org/export/1.2/")]
            public term[] term
            {
                get
                {
                    return this.termField;
                }
                set
                {
                    this.termField = value;
                }
            }

            /// <remarks/>
            public string generator
            {
                get
                {
                    return this.generatorField;
                }
                set
                {
                    this.generatorField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute("item")]
            public rssChannelItem[] item
            {
                get
                {
                    return this.itemField;
                }
                set
                {
                    this.itemField = value;
                }
            }
        }

        /// <remarks/>
        [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true, Namespace = "http://wordpress.org/export/1.2/")]
        [System.Xml.Serialization.XmlRootAttribute(Namespace = "http://wordpress.org/export/1.2/", IsNullable = false)]
        public partial class author
        {

            private ushort author_idField;

            private string author_loginField;

            private string author_emailField;

            private string author_display_nameField;

            private string author_first_nameField;

            private string author_last_nameField;

            /// <remarks/>
            public ushort author_id
            {
                get
                {
                    return this.author_idField;
                }
                set
                {
                    this.author_idField = value;
                }
            }

            /// <remarks/>
            public string author_login
            {
                get
                {
                    return this.author_loginField;
                }
                set
                {
                    this.author_loginField = value;
                }
            }

            /// <remarks/>
            public string author_email
            {
                get
                {
                    return this.author_emailField;
                }
                set
                {
                    this.author_emailField = value;
                }
            }

            /// <remarks/>
            public string author_display_name
            {
                get
                {
                    return this.author_display_nameField;
                }
                set
                {
                    this.author_display_nameField = value;
                }
            }

            /// <remarks/>
            public string author_first_name
            {
                get
                {
                    return this.author_first_nameField;
                }
                set
                {
                    this.author_first_nameField = value;
                }
            }

            /// <remarks/>
            public string author_last_name
            {
                get
                {
                    return this.author_last_nameField;
                }
                set
                {
                    this.author_last_nameField = value;
                }
            }
        }

        /// <remarks/>
        [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true, Namespace = "http://wordpress.org/export/1.2/")]
        [System.Xml.Serialization.XmlRootAttribute(Namespace = "http://wordpress.org/export/1.2/", IsNullable = false)]
        public partial class category
        {

            private ushort term_idField;

            private string category_nicenameField;

            private string category_parentField;

            private string cat_nameField;

            /// <remarks/>
            public ushort term_id
            {
                get
                {
                    return this.term_idField;
                }
                set
                {
                    this.term_idField = value;
                }
            }

            /// <remarks/>
            public string category_nicename
            {
                get
                {
                    return this.category_nicenameField;
                }
                set
                {
                    this.category_nicenameField = value;
                }
            }

            /// <remarks/>
            public string category_parent
            {
                get
                {
                    return this.category_parentField;
                }
                set
                {
                    this.category_parentField = value;
                }
            }

            /// <remarks/>
            public string cat_name
            {
                get
                {
                    return this.cat_nameField;
                }
                set
                {
                    this.cat_nameField = value;
                }
            }
        }

        /// <remarks/>
        [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true, Namespace = "http://wordpress.org/export/1.2/")]
        [System.Xml.Serialization.XmlRootAttribute(Namespace = "http://wordpress.org/export/1.2/", IsNullable = false)]
        public partial class tag
        {

            private ushort term_idField;

            private string tag_slugField;

            private string tag_nameField;

            /// <remarks/>
            public ushort term_id
            {
                get
                {
                    return this.term_idField;
                }
                set
                {
                    this.term_idField = value;
                }
            }

            /// <remarks/>
            public string tag_slug
            {
                get
                {
                    return this.tag_slugField;
                }
                set
                {
                    this.tag_slugField = value;
                }
            }

            /// <remarks/>
            public string tag_name
            {
                get
                {
                    return this.tag_nameField;
                }
                set
                {
                    this.tag_nameField = value;
                }
            }
        }

        /// <remarks/>
        [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true, Namespace = "http://wordpress.org/export/1.2/")]
        [System.Xml.Serialization.XmlRootAttribute(Namespace = "http://wordpress.org/export/1.2/", IsNullable = false)]
        public partial class term
        {

            private ushort term_idField;

            private string term_taxonomyField;

            private string term_slugField;

            private object term_parentField;

            private string term_nameField;

            private string term_descriptionField;

            /// <remarks/>
            public ushort term_id
            {
                get
                {
                    return this.term_idField;
                }
                set
                {
                    this.term_idField = value;
                }
            }

            /// <remarks/>
            public string term_taxonomy
            {
                get
                {
                    return this.term_taxonomyField;
                }
                set
                {
                    this.term_taxonomyField = value;
                }
            }

            /// <remarks/>
            public string term_slug
            {
                get
                {
                    return this.term_slugField;
                }
                set
                {
                    this.term_slugField = value;
                }
            }

            /// <remarks/>
            public object term_parent
            {
                get
                {
                    return this.term_parentField;
                }
                set
                {
                    this.term_parentField = value;
                }
            }

            /// <remarks/>
            public string term_name
            {
                get
                {
                    return this.term_nameField;
                }
                set
                {
                    this.term_nameField = value;
                }
            }

            /// <remarks/>
            public string term_description
            {
                get
                {
                    return this.term_descriptionField;
                }
                set
                {
                    this.term_descriptionField = value;
                }
            }
        }

        /// <remarks/>
        [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
        public partial class rssChannelItem
        {

            private string titleField;

            private string linkField;

            private string pubDateField;

            private string creatorField;

            private rssChannelItemGuid guidField;

            private object descriptionField;

            private string encodedField;

            private string encoded1Field;

            private ushort post_idField;

            private string post_dateField;

            private string post_date_gmtField;

            private string comment_statusField;

            private string ping_statusField;

            private string post_nameField;

            private string statusField;

            private ushort post_parentField;

            private sbyte menu_orderField;

            private string post_typeField;

            private object post_passwordField;

            private byte is_stickyField;

            private rssChannelItemCategory[] categoryField;

            private string attachment_urlField;

            private postmeta[] postmetaField;

            /// <remarks/>
            public string title
            {
                get
                {
                    return this.titleField;
                }
                set
                {
                    this.titleField = value;
                }
            }

            /// <remarks/>
            public string link
            {
                get
                {
                    return this.linkField;
                }
                set
                {
                    this.linkField = value;
                }
            }

            /// <remarks/>
            public string pubDate
            {
                get
                {
                    return this.pubDateField;
                }
                set
                {
                    this.pubDateField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://purl.org/dc/elements/1.1/")]
            public string creator
            {
                get
                {
                    return this.creatorField;
                }
                set
                {
                    this.creatorField = value;
                }
            }

            /// <remarks/>
            public rssChannelItemGuid guid
            {
                get
                {
                    return this.guidField;
                }
                set
                {
                    this.guidField = value;
                }
            }

            /// <remarks/>
            public object description
            {
                get
                {
                    return this.descriptionField;
                }
                set
                {
                    this.descriptionField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://purl.org/rss/1.0/modules/content/")]
            public string encoded
            {
                get
                {
                    return this.encodedField;
                }
                set
                {
                    this.encodedField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute("encoded", Namespace = "http://wordpress.org/export/1.2/excerpt/")]
            public string encoded1
            {
                get
                {
                    return this.encoded1Field;
                }
                set
                {
                    this.encoded1Field = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public ushort post_id
            {
                get
                {
                    return this.post_idField;
                }
                set
                {
                    this.post_idField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public string post_date
            {
                get
                {
                    return this.post_dateField;
                }
                set
                {
                    this.post_dateField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public string post_date_gmt
            {
                get
                {
                    return this.post_date_gmtField;
                }
                set
                {
                    this.post_date_gmtField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public string comment_status
            {
                get
                {
                    return this.comment_statusField;
                }
                set
                {
                    this.comment_statusField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public string ping_status
            {
                get
                {
                    return this.ping_statusField;
                }
                set
                {
                    this.ping_statusField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public string post_name
            {
                get
                {
                    return this.post_nameField;
                }
                set
                {
                    this.post_nameField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public string status
            {
                get
                {
                    return this.statusField;
                }
                set
                {
                    this.statusField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public ushort post_parent
            {
                get
                {
                    return this.post_parentField;
                }
                set
                {
                    this.post_parentField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public sbyte menu_order
            {
                get
                {
                    return this.menu_orderField;
                }
                set
                {
                    this.menu_orderField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public string post_type
            {
                get
                {
                    return this.post_typeField;
                }
                set
                {
                    this.post_typeField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public object post_password
            {
                get
                {
                    return this.post_passwordField;
                }
                set
                {
                    this.post_passwordField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public byte is_sticky
            {
                get
                {
                    return this.is_stickyField;
                }
                set
                {
                    this.is_stickyField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute("category")]
            public rssChannelItemCategory[] category
            {
                get
                {
                    return this.categoryField;
                }
                set
                {
                    this.categoryField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute(Namespace = "http://wordpress.org/export/1.2/")]
            public string attachment_url
            {
                get
                {
                    return this.attachment_urlField;
                }
                set
                {
                    this.attachment_urlField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlElementAttribute("postmeta", Namespace = "http://wordpress.org/export/1.2/")]
            public postmeta[] postmeta
            {
                get
                {
                    return this.postmetaField;
                }
                set
                {
                    this.postmetaField = value;
                }
            }
        }

        /// <remarks/>
        [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
        public partial class rssChannelItemGuid
        {

            private bool isPermaLinkField;

            private string valueField;

            /// <remarks/>
            [System.Xml.Serialization.XmlAttributeAttribute()]
            public bool isPermaLink
            {
                get
                {
                    return this.isPermaLinkField;
                }
                set
                {
                    this.isPermaLinkField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlTextAttribute()]
            public string Value
            {
                get
                {
                    return this.valueField;
                }
                set
                {
                    this.valueField = value;
                }
            }
        }

        /// <remarks/>
        [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
        public partial class rssChannelItemCategory
        {

            private string domainField;

            private string nicenameField;

            private string valueField;

            /// <remarks/>
            [System.Xml.Serialization.XmlAttributeAttribute()]
            public string domain
            {
                get
                {
                    return this.domainField;
                }
                set
                {
                    this.domainField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlAttributeAttribute()]
            public string nicename
            {
                get
                {
                    return this.nicenameField;
                }
                set
                {
                    this.nicenameField = value;
                }
            }

            /// <remarks/>
            [System.Xml.Serialization.XmlTextAttribute()]
            public string Value
            {
                get
                {
                    return this.valueField;
                }
                set
                {
                    this.valueField = value;
                }
            }
        }

        /// <remarks/>
        [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true, Namespace = "http://wordpress.org/export/1.2/")]
        [System.Xml.Serialization.XmlRootAttribute(Namespace = "http://wordpress.org/export/1.2/", IsNullable = false)]
        public partial class postmeta
        {

            private string meta_keyField;

            private string meta_valueField;

            /// <remarks/>
            public string meta_key
            {
                get
                {
                    return this.meta_keyField;
                }
                set
                {
                    this.meta_keyField = value;
                }
            }

            /// <remarks/>
            public string meta_value
            {
                get
                {
                    return this.meta_valueField;
                }
                set
                {
                    this.meta_valueField = value;
                }
            }
        }



    }
}







Lexblog entities classes


We also need to create entities classes for holding data of authors, categories and blog articles as

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace XMLtoSQLInserter
{
    public class BlogClass
    {
        public class Blog
        {
            public string author { get; set; }
            public string title { get; set; }
            public DateTime date { get; set; }
            public string body { get; set; }
            public string extendedBody { get; set; }
            public string excerpt { get; set; }
            public string keywords { get; set; }
            public string Guid { get; set; }
        }

        public class BlogPrimaryCategories
        {
            public string PrimaryCategory { get; set; }
            public string Guid { get; set; }
        }

        public class BlogCategories
        {
            public string Category { get; set; }
            public string Guid { get; set; }
        }

        public class Author
        {
            public string authorEmail { get; set; }
            public string authorId { get; set; }
            public string authorLogin { get; set; }
            public string authorCategory { get; set; }
            public string authorFirstName { get; set; }
            public string authorLastName { get; set; }
            public string authorDisplayName { get; set; }
        }
    }
}



Download Media from Lexblog site articles


If you didn’t get media related with LexBlog site then below scripts can download images and PDFs for you in you local environment. 

I have created windows application for downloading images, fetched XML file data and transfer it into SQL server.

You can put this code in web pages as well but I observerd that sometimes program takes long time to process the XML file. SO I decided to use windows application for this.

Here is a screenshot of my windows application.

Windows Application for LexBlog
Windows Application for LexBlog


It scans all the articles description and find the LexBlog site related links and if links matches with image, pdf extension then code tried to download the related file.

        private void ImportMedia(wordpressxml.rssChannel content)
        {
            Regex regx = new Regex("http://([\\w+?\\.\\w+])+([a-zA-Z0-9\\~\\!\\@\\#\\$\\%\\^\\&amp;\\*\\(\\)_\\-\\=\\+\\\\\\/\\?\\.\\:\\;\\'\\,]*)?", RegexOptions.IgnoreCase);

            var mainItems = content.item == null ? null : content.item.ToList();
            if (mainItems != null)
            {
                foreach (var itemMain in mainItems)
                {
                    if (itemMain.post_type == "post")
                    {
                        var encodedData = itemMain.encoded == string.Empty ? null : itemMain.encoded.ToString();
                        if (encodedData != null)
                        {
                            MatchCollection matches = regx.Matches(encodedData.ToString());
                            foreach (Match matchedItems in matches)
                            {
                                if (matchedItems.Value.ToLower().Contains("www." + SITENAME.ToLower() + ".com"))
                                {
                                    var extension = matchedItems.Value.ToString().Split('.').Last();
                                    if (extension == "jpg" || extension == "png" || extension == "pdf" || extension == "gif")
                                    {
                                        lblCurrentActivity.Text = "Processing - " + matchedItems.Value.ToString();
                                        SaveImage(matchedItems.Value.ToString());
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }



Migrate Lexblog Articles Data to SQL


Use the same windows application and refer below code to read blog articles from XML file as

TextReader txtReader = new StreamReader(openFileDialog1.FileName);
XmlSerializer xmlSerializer = new XmlSerializer(typeof(wordpressxml.rss));
var serializeObject = (wordpressxml.rss)xmlSerializer.Deserialize(txtReader);
wordpressxml.rssChannel content = new wordpressxml.rssChannel();
content = serializeObject.channel;

ImportDataToSQL(content);

Once we get wordpress XML object, we can use below code to read its basic details, authors, categories, tags, terms and its articles. The same code will insert all this data in SQL server tables. 

private void ImportDataToSQL(wordpressxml.rssChannel content)
        {
            var connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();

            int blogId = 1;
            List<string> lstAuthors = new List<string>();

            #region Blog Data to Database

            using (SqlCommand cmd = new SqlCommand("SP_TitleDescInsert", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@title", content.title);
                cmd.Parameters.AddWithValue("@description", content.description);
                cmd.Parameters.AddWithValue("@new_identity", blogId);
                blogId = Convert.ToInt32(cmd.ExecuteScalar());
            }

            foreach (var item in content.author)
            {
                using (SqlCommand cmd = new SqlCommand("SP_AuthorsAdd", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@author_id", (int?)item.author_id);
                    cmd.Parameters.AddWithValue("@author_login", item.author_login);
                    cmd.Parameters.AddWithValue("@author_email", item.author_email);
                    cmd.Parameters.AddWithValue("@author_display_name", item.author_display_name);
                    cmd.Parameters.AddWithValue("@author_first_name", item.author_first_name);
                    cmd.Parameters.AddWithValue("@author_last_name", item.author_last_name);
                    cmd.Parameters.AddWithValue("@blogId", blogId);
                    cmd.ExecuteNonQuery();
                }
            }

            var query = "select author_login from author order by author_login";
            SqlCommand cmdAuthor = new SqlCommand(query, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmdAuthor);
            DataSet dsLookup = new DataSet();
            da.Fill(dsLookup);

            if (dsLookup.Tables.Count > 0)
            {
                if (dsLookup.Tables[0].Rows.Count > 0)
                {
                    for (int row = 0; row <= dsLookup.Tables[0].Rows.Count - 1; row++)
                    {
                        string authorLoginName = !DBNull.Value.Equals(dsLookup.Tables[0].Rows[row][0]) ? Convert.ToString(dsLookup.Tables[0].Rows[row][0]) : "";
                        if (!string.IsNullOrEmpty(authorLoginName))
                        {
                            lstAuthors.Add(authorLoginName.ToLower());
                        }
                    }
                }
            }

            if (content.term != null)
            {
                foreach (var itemExtraAuthor in content.term)
                {
                    if (itemExtraAuthor.term_taxonomy == "author" && !(lstAuthors.Contains(itemExtraAuthor.term_slug.ToLower()) || lstAuthors.Contains(itemExtraAuthor.term_name.ToLower())))
                    {

                        string term_slug = string.Empty;
                        try
                        {
                            if (!string.IsNullOrEmpty(itemExtraAuthor.term_description))
                            {
                                string authorData = itemExtraAuthor.term_description.ToString();
                                if (!string.IsNullOrEmpty(authorData))
                                {
                                    term_slug = itemExtraAuthor.term_slug.ToString();

                                    Author objAuthor = new Author();
                                    string[] authorValues = authorData.Split(' ');
                                    int totalCount = authorValues.Count();
                                    if (totalCount == 7)
                                    {
                                        objAuthor.authorEmail = authorValues[authorValues.Count() - 1].ToString();
                                        objAuthor.authorId = authorValues[authorValues.Count() - 2].ToString();
                                        objAuthor.authorLogin = authorValues[authorValues.Count() - 3].ToString();
                                        objAuthor.authorCategory = itemExtraAuthor.term_slug.ToString();

                                        if (Math.Ceiling((decimal)(authorValues.Count() - 3) / 2) == 2)
                                        {
                                            objAuthor.authorFirstName = authorValues[0];
                                            objAuthor.authorLastName = authorValues[1];
                                        }
                                        objAuthor.authorDisplayName = objAuthor.authorFirstName + " " + objAuthor.authorLastName;
                                    }
                                    else if (totalCount == 9)
                                    {
                                        objAuthor.authorEmail = authorValues[authorValues.Count() - 1].ToString();
                                        objAuthor.authorId = authorValues[authorValues.Count() - 2].ToString();
                                        objAuthor.authorLogin = authorValues[authorValues.Count() - 3].ToString();
                                        objAuthor.authorCategory = itemExtraAuthor.term_slug.ToString();

                                        if (Math.Ceiling((decimal)(authorValues.Count() - 3) / 2) > 2)
                                        {
                                            objAuthor.authorFirstName = authorValues[0] + " " + authorValues[1];
                                            objAuthor.authorLastName = authorValues[2];
                                        }
                                        objAuthor.authorDisplayName = objAuthor.authorFirstName + " " + objAuthor.authorLastName;
                                    }
                                    else if (totalCount == 11)
                                    {
                                        objAuthor.authorEmail = authorValues[authorValues.Count() - 1].ToString();
                                        objAuthor.authorId = authorValues[authorValues.Count() - 2].ToString();
                                        objAuthor.authorLogin = authorValues[authorValues.Count() - 3].ToString();
                                        objAuthor.authorCategory = itemExtraAuthor.term_slug.ToString();

                                        if (Math.Ceiling((decimal)(authorValues.Count() - 3) / 2) > 2)
                                        {
                                            objAuthor.authorFirstName = authorValues[0] + " " + authorValues[1] + " " + authorValues[2];
                                            objAuthor.authorLastName = authorValues[3];
                                        }
                                        objAuthor.authorDisplayName = objAuthor.authorFirstName + " " + objAuthor.authorLastName;
                                    }
                                    else if (totalCount == 13)
                                    {
                                        objAuthor.authorEmail = authorValues[authorValues.Count() - 1].ToString();
                                        objAuthor.authorId = authorValues[authorValues.Count() - 2].ToString();
                                        objAuthor.authorLogin = authorValues[authorValues.Count() - 3].ToString();
                                        objAuthor.authorCategory = itemExtraAuthor.term_slug.ToString();

                                        if (Math.Ceiling((decimal)(authorValues.Count() - 3) / 2) > 2)
                                        {
                                            objAuthor.authorFirstName = authorValues[0] + " " + authorValues[1] + " " + authorValues[2] + " " + authorValues[3];
                                            objAuthor.authorLastName = authorValues[4];
                                        }
                                        objAuthor.authorDisplayName = objAuthor.authorFirstName + " " + objAuthor.authorLastName;
                                    }
                                    else
                                    {
                                        try
                                        {


                                            objAuthor.authorEmail = authorValues[authorValues.Count() - 1].ToString();
                                            objAuthor.authorId = authorValues[authorValues.Count() - 2].ToString();
                                            objAuthor.authorLogin = authorValues[authorValues.Count() - 3].ToString();
                                            objAuthor.authorCategory = itemExtraAuthor.term_slug.ToString();

                                            int start = authorData.ToLower().IndexOf(" " + authorValues[0].ToLower(), authorValues.Length);
                                            string fullName = authorData.Substring(0, start).Trim();
                                            objAuthor.authorFirstName = fullName;
                                            objAuthor.authorLastName = "";
                                            objAuthor.authorDisplayName = fullName;

                                            File.AppendAllText(LOGPATH, "Full Name Author = " + fullName + " from " + authorData + Environment.NewLine);
                                        }
                                        catch
                                        {

                                            File.AppendAllText(LOGPATH, "Naming problem in Author = " + itemExtraAuthor.term_slug.ToString() + Environment.NewLine);
                                            continue;
                                        }
                                    }

                                    InsertAuthorData(objAuthor, blogId);
                                }
                            }
                            else
                            {
                                File.AppendAllText(LOGPATH, "Term Description is Empty for author " + itemExtraAuthor.term_slug.ToString() + Environment.NewLine);
                            }
                        }
                        catch (Exception ex)
                        {
                            File.AppendAllText(LOGPATH, "Error " + ex.Message + " ---- " + "Stack Track " + ex.StackTrace + " ---- For Aothor " + term_slug + Environment.NewLine);
                        }
                    }
                }

            }

            if (content.category != null)
            {
                foreach (var item in content.category)
                {
                    if (item.term_id > 0)
                    {
                        using (SqlCommand cmd = new SqlCommand("SP_CategoryAdd", conn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@term_id", (int?)item.term_id);
                            cmd.Parameters.AddWithValue("@category_nicename", item.category_nicename);
                            cmd.Parameters.AddWithValue("@category_parent", item.category_parent);
                            cmd.Parameters.AddWithValue("@cat_name", item.cat_name);
                            cmd.Parameters.AddWithValue("@blogId", blogId);
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }

            if (content.tag != null)
            {
                foreach (var item in content.tag)
                {
                    using (SqlCommand cmd = new SqlCommand("SP_TagAdd", conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@term_id", (int?)item.term_id);
                        cmd.Parameters.AddWithValue("@tag_slug", item.tag_slug);
                        cmd.Parameters.AddWithValue("@tag_name", item.tag_name);
                        cmd.Parameters.AddWithValue("@blogId", blogId);
                        cmd.ExecuteNonQuery();
                    }
                }
            }

            #endregion

            List<ushort> postList = new List<ushort>();
            conn.Close();
            conn.Open();

            foreach (var item in content.item)
            {
                if (item.post_type == "post" && !string.IsNullOrEmpty(item.post_date) && Convert.ToDateTime(item.post_date) > Convert.ToDateTime(txtDate.Text))
                {
                    using (SqlCommand cmd = new SqlCommand("SP_MainItemAdd", conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@title", item.title);
                        cmd.Parameters.AddWithValue("@post_id", (int?)item.post_id);
                        cmd.Parameters.AddWithValue("@post_date", item.post_date);
                        cmd.Parameters.AddWithValue("@main_content", item.encoded);
                        cmd.Parameters.AddWithValue("@blogId", blogId);
                        cmd.Parameters.AddWithValue("@post_name", item.post_name);
                        cmd.Parameters.AddWithValue("@post_status", item.status);
                        cmd.Parameters.AddWithValue("@post_link", item.link);
                        //@post_Status
                        cmd.ExecuteNonQuery();

                        postList.Add(item.post_id);
                    }
                }
            }

            var CategoryInsertion = content.item == null ? null : content.item.ToList();
            if (CategoryInsertion != null)
            {
                foreach (var item in CategoryInsertion)
                {
                    if (!postList.Contains(item.post_id))
                    {
                        continue;
                    }

                    var categoryWiseItems = item.category == null ? null : item.category.ToList();

                    if (categoryWiseItems != null)
                    {
                        foreach (var CategoryVal in categoryWiseItems)
                        {
                            if (CategoryVal.domain == "author")
                            {
                                using (SqlCommand cmd = new SqlCommand("ItemAuthorInsert", conn))
                                {
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    cmd.Parameters.AddWithValue("@Category", CategoryVal.Value);
                                    cmd.Parameters.AddWithValue("@post_id", (int?)item.post_id);
                                    cmd.Parameters.AddWithValue("@nicename", CategoryVal.nicename);
                                    cmd.Parameters.AddWithValue("@blogId", blogId);
                                    cmd.ExecuteNonQuery();
                                }
                            }

                            else if (CategoryVal.domain == "post_tag")
                            {
                                using (SqlCommand cmd = new SqlCommand("ItemTagInsert", conn))
                                {
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    cmd.Parameters.AddWithValue("@Category", CategoryVal.Value);
                                    cmd.Parameters.AddWithValue("@post_id", (int?)item.post_id);
                                    cmd.Parameters.AddWithValue("@nicename", CategoryVal.nicename);
                                    cmd.Parameters.AddWithValue("@blogId", blogId);
                                    cmd.ExecuteNonQuery();
                                }
                            }

                            else if (CategoryVal.domain == "category")
                            {
                                using (SqlCommand cmd = new SqlCommand("ItemCategoryInsert", conn))
                                {
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    cmd.Parameters.AddWithValue("@Category", CategoryVal.Value);
                                    cmd.Parameters.AddWithValue("@post_id", (int?)item.post_id);
                                    cmd.Parameters.AddWithValue("@nicename", CategoryVal.nicename);
                                    cmd.Parameters.AddWithValue("@blogId", blogId);
                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                }
            }

            conn.Close();
        }



That’s it for the part III. In next part, I will walk through on templates required in Sitecore.

I hope you like this Sitecore lessons. Stay tuned for more Sitecore related articles.

Till that happy sitecoring :)

Please leave your comments or share this article if it’s useful for you.

No comments:

Post a Comment