Surendra Sharma

Surendra Sharma

Search This Blog

Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Tuesday, August 22, 2017

How to deal with large Sitecore databases: Part II




In first part, I had covered how to shrink the log file of database. This article focus on how to take backup of large database in multiple files at different locations and how to restore them.

Split database into multiple files
Split database into multiple files

If you asked to any developer about how to take database backup, they will generally reply - its very easy. Just use SQL server backup window or use the ready-made script.

But if you asked them take the backup of 20 GB database and upload on some different machine?
If you are making a single heavy backup file with 20GB and try to upload on FTP server then it might take hours. By any chance, this upload operation truncate in between then again you have to waste your time by uploading the same file on FTP server as well as you have monitor them now.

Is there any better way?

Yes. SQL server provide a way where you can split single database into multiple smaller file. Let suppose you have 20 GB database and now you can take backup in 10 different files and each file having 2 GB in size.

Here are other advantages of splitting database into multiple files

  • Store each file in different drive or locations.
  • ZIP individual file 
  •  Upload individual file on FTP location


Here is a sample script for backup database in 3 different files at three different locations.

BACKUP DATABASE SitecoreLessonsSitecore_Master
TO DISK = 'D:\Backup\SitecoreLessonsSitecore_Master\1.bak',
   DISK = 'C:\Other_Drive_Backup\SitecoreLessonsSitecore_Master\2.bak',
   DISK = 'E:\Daily_Backup\SitecoreLessonsSitecore_Master\3.bak'
GO

Note:- Folder path must exist for successful backup operation.

Use below query to restore these files

RESTORE DATABASE [SitecoreLessonsSitecore_Master_Staging]
FROM DISK = N'D:\Backup\SitecoreLessonsSitecore_Master\1.bak',
     DISK = N'D:\Backup\SitecoreLessonsSitecore_Master\2.bak',
     DISK = N'D:\Backup\SitecoreLessonsSitecore_Master\3.bak'
GO

Note:- Restore work on server where this database is not exist.

You can backup and restore by using SQL server management studio also. Here is a reference link for it https://solutioncenter.apexsql.com/how-to-create-sql-server-database-split-backup-files/

I have used this backup script multiple times.

I hope you like this Sitecore database trick. Stay tuned for more Sitecore articles.

Till that happy Sitecoring :)

Please leave your comments or share this database trick if it’s useful for you.
 
To know more about Sitecore database, Download this eBook.

Friday, December 9, 2016

LexBlog To Sitecore - Final Part - SQL Server to Sitecore



This is fifth and final article in a series for LexBlog to Sitecore. You can refer fourth article for LexBlog SQL Server database from here.

Once templates are in place, we need to create items for tags, categories and blog items along with media items in Sitecore.

Refer below code for getting data form SQL server and creating items in Sitecore.

Uploading Media items to Sitecore Media Library


We have to upload our Lexblog downloaded media files in Sitecore. You can use below code for the same.

using LexBlog.ContentMigration.Classes;
using Sitecore.Data;
using Sitecore.Data.Fields;
using Sitecore.Data.Items;
using Sitecore.Data.Managers;
using Sitecore.Globalization;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Sitecore.Security.Accounts;
using Sitecore.Resources.Media;
using System.Text.RegularExpressions;


namespace LexBlog.ContentMigration.Utility
{
    public class UploadBlogMedia
    {

        /// <summary>
        /// Method to Upload Files in the Media Library
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sitecorePath"></param>
        /// <param name="mediaItemName"></param>
        /// <returns></returns>
        public MediaItem AddFile(string filePath, string sitecorePath, string mediaItemName)
        {
            try
            {
                // Create the options
                Sitecore.Resources.Media.MediaCreatorOptions options = new Sitecore.Resources.Media.MediaCreatorOptions();
                // Store the file in the database, not as a file
                options.FileBased = false;
                // Remove file extension from item name
                options.IncludeExtensionInItemName = false;
                // Overwrite any existing file with the same name
                options.KeepExisting = false;
                // Do not make a versioned template
                options.Versioned = false;
                // set the path
                options.Destination = sitecorePath + "/" + mediaItemName;
                // Set the database
                options.Database = Sitecore.Configuration.Factory.GetDatabase("master");

                // Now create the file
                Sitecore.Resources.Media.MediaCreator creator = new Sitecore.Resources.Media.MediaCreator();
                MediaItem mediaItem = creator.CreateFromFile(filePath, options);

                File.AppendAllText(LexBlogConstants.LogPath, "File Added at " + sitecorePath + "/" + mediaItemName);

                return mediaItem;
            }
            catch (Exception ex)
            {
                File.AppendAllText(LexBlogConstants.LogPath, "Error in AddFile");
                return null;
            }
        }

        public void ImportBlogMedia()
        {
            string[] blogNames = { "sampleblog" };

            foreach (var blogName in blogNames)
            {
                File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Processing blog " + blogName);

                UploadBlogFiles(blogName);

                File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Processing blog completed " + blogName);
            }
        }

        public void UploadBlogFiles(string blogName)
        {
            string destinationPathMedia = "/sitecore/media library/LexBlog/Images/Blog Images/" + blogName;
            var db = Database.GetDatabase("master");
            try
            {
                var files = Directory.GetFiles(@"D:\Blog\" + blogName, "*.*", SearchOption.AllDirectories);
                int i = 1;
                foreach (string filePath in files)
                {
                    try
                    {
                        if (Regex.IsMatch(filePath, @".jpg|.png|.pdf|.gif"))
                        {
                            string fileName = Path.GetFileName(filePath);
                            string mediaItemName = LexBlogConstants.RemoveSpecialChars(fileName); //For Blogs
                            int count = mediaItemName.Length;
                            mediaItemName = mediaItemName.Insert(count - 3, "_");

                            var sampleMediaPath = (destinationPathMedia + "/" + mediaItemName);
                            Item MediaItem = db.GetItem(sampleMediaPath);
                            if (MediaItem == null)
                            {
                                File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + i.ToString() + " > ");
                                AddFile(filePath, destinationPathMedia, mediaItemName);
                            }
                            else
                            {
                                File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + i.ToString() + " > File already Exist: " + filePath + "------" + Environment.NewLine);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + i.ToString() + " > File error occured in: " + Path.GetFileName(filePath) + "------" + ex + Environment.NewLine);
                    }
                    i++;
                }
            }
            catch (Exception ex)
            {
                File.AppendAllText(LexBlogConstants.LogPath, "Error " + ex.Message + " ---- " + "Stack Track " + ex.StackTrace + " ---- " + Environment.NewLine);
            }
        }
    }

        public static string RemoveSpecialChars(string strText)
        {
            try
            {
                strText = strText.Replace("[", "");
                strText = strText.Replace("]", "");
                strText = strText.Replace("{", "");
                strText = strText.Replace("}", "");
                strText = strText.Replace("\"", "");
                strText = strText.Replace("/", "");
                strText = strText.Replace("*", "");
                strText = strText.Replace("?", "");
                strText = strText.Replace(">", "");
                strText = strText.Replace("<", "");
                strText = strText.Replace("_", "");
                strText = strText.Replace("!", "");
                strText = strText.Replace("^", "");
                strText = strText.Replace("+", "");
                strText = strText.Replace("~", "");
                strText = strText.Replace("&", "");
                strText = strText.Replace(":", "");
                strText = strText.Replace("'", "");
                strText = strText.Replace("’", "");
                strText = strText.Replace("£", "");
                strText = strText.Replace("$", "");
                strText = strText.Replace("#39;", "");
                strText = strText.Replace("`", "");
                strText = strText.Replace("%", "");
                strText = strText.Replace("(", "");
                strText = strText.Replace(")", "");
                strText = strText.Replace("=", "");
                strText = strText.Replace(".", "");
                strText = strText.Replace("“", "");
                strText = strText.Replace("”", "");
                strText = strText.Replace(",", "");
                strText = strText.Replace("–", "");
                strText = strText.Replace("€", "");
                strText = strText.Replace("©", "");
                strText = strText.Replace("@", "");
                strText = strText.Replace(";", "");
                strText = strText.Replace("—", "");
                strText = strText.Replace("•", "");
                strText = strText.Replace("´", "");
                strText = strText.Replace("  ", " ");

                strText = strText.Trim();
            }
            catch (Exception ex)
            {
                Sitecore.Diagnostics.Log.Error("RemoveSpecialChars " + ex, null);
            }
            return strText;
        }

public static string LogPath = Convert.ToString(ConfigurationManager.AppSettings["MigrationUtilityLogPath"]);

}


Mapping Classes


We need mapping classes for getting and storing data from SQL server.

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

namespace LexBlog.ContentMigration.Classes
{
    public class MigratedCategories
    {
        public string Name { get; set; }
        public string NiceName { get; set; }
    }

    public class MigratedTags
    {
        public string TagName { get; set; }
        public string TagSlug { get; set; }
    }

    public class MigratedBlogs
    {
        public string MainItemTitle { get; set; }
        public string MainItemPostDate { get; set; }
        public string MainItemDescription { get; set; }
        public string MainItemPostName { get; set; }
        public string MainItemPostId { get; set; }
        public string MainItemBody { get; set; }
        public string MainItemExtendedBody { get; set; }
        public string MainItemExcerpt { get; set; }
        public string MainItemKeyword { get; set; }
        public string OtherAuthors { get; set; }
    }

}



Import Tag to Sitecore


Below code is used to fetch tag records from SQL to Sitecore.


    public class BlogTemplateInfoAll
    {

        public string destinationPath;
        public string connetionString;
        public string blogMediaFolderName;
        public string blogURL;
        public string BlogCategoryFolder;
        public string BlogTagFolder;
        public string language = "en";
        public string WhereCluase;
    }

        public static List<BlogTemplateInfoAll> GetAllBlogInfo()
        {
            List<BlogTemplateInfoAll> blogTypesList = new List<BlogTemplateInfoAll>();
            blogTypesList.Add(new BlogTemplateInfoAll() { WhereCluase = "post_status= 'publish'", destinationPath = "{E5D9B768-57DF-400F-9858-C6B25D93391C}", connetionString = string.Format(ConnectionString.ConnectionStringDataExcel, "sampleblog"), blogMediaFolderName = "sampleblog", blogURL = "www.sampleblog.com", BlogCategoryFolder = "{8B266C45-9EB1-4006-B4BB-BD2B47716878}", BlogTagFolder = "{ED84594E-47E2-4FA1-9689-6BCF874DC04F}" });

            return blogTypesList;
        }







using LexBlog.ContentMigration.Classes;
using Sitecore.Data;
using Sitecore.Data.Fields;
using Sitecore.Data.Items;
using Sitecore.Data.Managers;
using Sitecore.Globalization;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Sitecore.Security.Accounts;

namespace LexBlog.ContentMigration.Utility
{
    public class ImportTags
    {
        public void ImportBlogTags()
        {
            var destinationTemplateId = "{7DA49B50-B1C3-4410-9C4C-9FBC4945935F}";
            var lookupUtility = new LookupUtility();
            var allBlogInfo = LexBlogConstants.GetAllBlogInfo();
            foreach (var singleBlogInfo in allBlogInfo)
            {
                try
                {
                    var cnn = LexBlogConstants.GetConnectionStringLocal(singleBlogInfo.connetionString);

                    string destinationPath = singleBlogInfo.BlogTagFolder;                    List<Item> ListDestinationTagItems = Database.GetDatabase("master").GetItem(destinationPath).GetChildren().ToList();

                    var blogCategoryFolderItem = Database.GetDatabase("master").GetItem(destinationPath);
                    var query = "select Tag.tag_name, Tag.tag_slug from Tag where tag_name != '' or tag_name is not null order by tag_name";
                    cnn.Open();
                    SqlCommand cmd = new SqlCommand(query, cnn);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet dsLookup = new DataSet();
                    da.Fill(dsLookup);

                    string TagName = string.Empty;
                    string TagSlug = string.Empty;

                    if (dsLookup.Tables.Count > 0)
                    {
                        if (dsLookup.Tables[0].Rows.Count > 0)
                        {
                            for (int row = 0; row <= dsLookup.Tables[0].Rows.Count - 1; row++)
                            {
                                bool previousCheck = false;
                                TagName = !DBNull.Value.Equals(dsLookup.Tables[0].Rows[row][0]) ? Convert.ToString(dsLookup.Tables[0].Rows[row][0]) : "";
                                TagSlug = !DBNull.Value.Equals(dsLookup.Tables[0].Rows[row][1]) ? Convert.ToString(dsLookup.Tables[0].Rows[row][1]) : "";

                                string itemName = LexBlogConstants.ValidateItemName(TagName);

                                if (string.IsNullOrEmpty(itemName))
                                {
                                    continue;
                                }

                                var itemFields = new MigratedTags();
                                itemFields.TagName = TagName;
                                itemFields.TagSlug = TagSlug;

                                foreach (var presentTag in ListDestinationTagItems)
                                {
                                    if (presentTag.Fields["Slug"].Value.Trim().ToLower() == itemFields.TagSlug.Trim().ToLower() || presentTag.Name.Trim().ToLower() == itemName.Trim().ToLower())
                                    {
                                        previousCheck = true;
                                        break;
                                    }
                                }

                                if (!previousCheck)
                                {
                                    CreateSitecoreItem(destinationPath, LexBlogConstants.ValidateItemName(itemName), destinationTemplateId, itemFields);
                                }
                                else
                                {
                                    File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + singleBlogInfo.blogMediaFolderName + "." + TagName + " Tag already exist " + Environment.NewLine);
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    File.AppendAllText(LexBlogConstants.LogPath, "Error " + ex.Message + " ---- " + "Stack Track " + ex.StackTrace + " ---- " + Environment.NewLine);
                }
            }
        }

        public static Item CreateSitecoreItem(string destinationPath, string itemName, string targetTemplateId, MigratedTags itemFields)
        {
            Item sitecoreItem = null;
            try
            {
                // The SecurityDisabler overrides the current security model, allowing you
                // to access the item without any security. It's like the user being an administrator
                using (new Sitecore.SecurityModel.SecurityDisabler())
                {
                    // Get the master database
                    Database master = Database.GetDatabase("master");
                    // Get the place in the site tree where the new item must be inserted
                    Item parentItem = master.GetItem(destinationPath);
                    if (parentItem != null)
                    {
                        // Get the template to base the new item
                        TemplateItem template = master.GetItem(targetTemplateId);
                        // Add the item to the site tree
                        sitecoreItem = parentItem.Add(itemName, template);
                    }
                    if (sitecoreItem != null)
                    {
                        using (new Sitecore.SecurityModel.SecurityDisabler())
                        {
                            sitecoreItem.Editing.BeginEdit();

                            if (sitecoreItem.Fields["Name"] != null)
                            {
                                sitecoreItem.Fields["Name"].Value = itemFields.TagName;
                            }

                            if (sitecoreItem.Fields["Slug"] != null)
                            {
                                sitecoreItem.Fields["Slug"].Value = itemFields.TagSlug;
                            }

                            sitecoreItem.Editing.EndEdit();

                            File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Created new Tag " + itemFields.TagName + Environment.NewLine);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                File.AppendAllText(LexBlogConstants.LogPath, "add New Item " + itemName + " Error Message " + ex.Message + " ---- " + "trace " + ex.StackTrace + Environment.NewLine);
            }

            return sitecoreItem;
        }

        public static string ValidateItemName(string strText)
        {
            try
            {
                strText = Regex.Replace(strText, "[^a-zA-Z0-9 ]+", "");
            }
            catch (Exception ex)
            {
                Sitecore.Diagnostics.Log.Error("Item Name Validation Error: " + ex, null);
            }

            return strText.Trim();
        }


    }
}


Import Categories to Sitecore


Below code is used to fetch categories records from SQL to Sitecore.

using LexBlog.ContentMigration.Classes;
using Sitecore.Data;
using Sitecore.Data.Fields;
using Sitecore.Data.Items;
using Sitecore.Data.Managers;
using Sitecore.Globalization;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Sitecore.Security.Accounts;


namespace LexBlog.ContentMigration.Utility
{
    public class ImportCategories
    {
        public void ImportBlogCategories()
        {
            var destinationTemplateId = "{92A55CB8-2403-425F-857A-462EBAF6ACE4}";
            var lookupUtility = new LookupUtility();
             var allBlogInfo = LexBlogConstants.GetAllBlogInfo();
             foreach (var singleBlogInfo in allBlogInfo)
             {
                 try
                 {
                     var cnn = LexBlogConstants.GetConnectionStringLocal(singleBlogInfo.connetionString);

                     string destinationPath = singleBlogInfo.BlogCategoryFolder;

                     List<Item> ListDestinationCategoryItems = Database.GetDatabase("master").GetItem(destinationPath).GetChildren().ToList();

                     var blogCategoryFolderItem = Database.GetDatabase("master").GetItem(destinationPath);
                     var query = "select Category.cat_name, Category.category_nicename from Category order by category_nicename";
                     cnn.Open();
                     SqlCommand cmd = new SqlCommand(query, cnn);
                     SqlDataAdapter da = new SqlDataAdapter(cmd);
                     DataSet dsLookup = new DataSet();
                     da.Fill(dsLookup);

                     string Name = string.Empty;
                     string NiceName = string.Empty;

                     if (dsLookup.Tables.Count > 0)
                     {
                         if (dsLookup.Tables[0].Rows.Count > 0)
                         {
                             for (int row = 0; row <= dsLookup.Tables[0].Rows.Count - 1; row++)
                             {
                                 bool previousCheck = false;
                                 Name = !DBNull.Value.Equals(dsLookup.Tables[0].Rows[row][0]) ? Convert.ToString(dsLookup.Tables[0].Rows[row][0]) : "";
                                 NiceName = !DBNull.Value.Equals(dsLookup.Tables[0].Rows[row][1]) ? Convert.ToString(dsLookup.Tables[0].Rows[row][1]) : "";

                                 string itemName = LexBlogConstants.ValidateItemName(dsLookup.Tables[0].Rows[row][0].ToString());

                                 var itemFields = new MigratedCategories();
                                 itemFields.Name = Name;
                                 itemFields.NiceName = NiceName;

                                 foreach (var presentCategory in ListDestinationCategoryItems)
                                 {
                                     if (presentCategory.Fields["NiceName"].Value.Trim().ToLower() == itemFields.NiceName.Trim().ToLower() || presentCategory.Name.Trim().ToLower() == itemName.Trim().ToLower())
                                     {
                                         previousCheck = true;
                                         break;
                                     }
                                 }

                                 if (!previousCheck)
                                 {
                                     CreateSitecoreItem(destinationPath, LexBlogConstants.ValidateItemName(itemName), destinationTemplateId, itemFields);
                                 }
                                 else
                                 {
                                     File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + singleBlogInfo.blogMediaFolderName + "." + Name + " category already exist " + Environment.NewLine);
                                 }
                             }
                         }
                     }
                 }
                 catch (Exception ex)
                 {
                     File.AppendAllText(LexBlogConstants.LogPath, "Error " + ex.Message + " ---- " + "Stack Track " + ex.StackTrace + " ---- " + Environment.NewLine);
                 }
             }
        }

        public static Item CreateSitecoreItem(string destinationPath, string itemName, string targetTemplateId, MigratedCategories itemFields)
        {
            Item sitecoreItem = null;
            try
            {
                // The SecurityDisabler overrides the current security model, allowing you
                // to access the item without any security. It's like the user being an administrator
                using (new Sitecore.SecurityModel.SecurityDisabler())
                {
                    // Get the master database
                    Database master = Database.GetDatabase("master");
                    // Get the place in the site tree where the new item must be inserted
                    Item parentItem = master.GetItem(destinationPath);
                    if (parentItem != null)
                    {
                        // Get the template to base the new item
                        TemplateItem template = master.GetItem(targetTemplateId);
                        // Add the item to the site tree
                        sitecoreItem = parentItem.Add(itemName, template);
                    }
                    if (sitecoreItem != null)
                    {
                        using (new Sitecore.SecurityModel.SecurityDisabler())
                        {
                            sitecoreItem.Editing.BeginEdit();

                            if (sitecoreItem.Fields["Name"] != null)
                            {
                                sitecoreItem.Fields["Name"].Value = itemFields.Name;
                            }

                            if (sitecoreItem.Fields["NiceName"] != null)
                            {
                                sitecoreItem.Fields["NiceName"].Value = itemFields.NiceName;
                            }

                            sitecoreItem.Editing.EndEdit();

                            File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Created new category " + itemFields.Name + Environment.NewLine);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                File.AppendAllText(LexBlogConstants.LogPath, "add New Item " + itemName + " Error Message " + ex.Message + " ---- " + "trace " + ex.StackTrace + Environment.NewLine);
            }

            return sitecoreItem;
        }
    }
}


Import Blog articles to Sitecore


Below code is used to fetch article records from SQL to Sitecore.

using LexBlog.ContentMigration.Classes;
using Sitecore.Data;
using Sitecore.Data.Fields;
using Sitecore.Data.Items;
using Sitecore.Data.Managers;
using Sitecore.Globalization;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Sitecore.Security.Accounts;
using Sitecore.Resources.Media;
using System.Text.RegularExpressions;


namespace LexBlog.ContentMigration.Utility
{
    public class ImportBlog
    {
        public void ImportBlogRecordsUseLess()
        {
            string destinationPath = "{E5D9B768-57DF-400F-9858-C6B25D93391C}";
            string connetionString = string.Format(ConnectionString.ConnectionStringDataExcel, "sampleblog");
            string blogURL = "www.sampleblog.com";
            string blogMediaFolderName = "sampleblog";
            string BlogCategoryFolder = "{8B266C45-9EB1-4006-B4BB-BD2B47716878}";
            string BlogTagFolder = "{ED84594E-47E2-4FA1-9689-6BCF874DC04F}";
        }

        public void ImportBlogRecords()
        {
            string ParentId = "{0359FC3F-9B7D-4757-98CE-EA79EA1A2F47}";
            string TargetTemplateId = "{A444B71D-D695-44FD-B980-FC062FDD171E}";
            List<Item> ParentFolderChildrens = new List<Item>();
            Item ParentFolderItem = Database.GetDatabase("master").GetItem(new Sitecore.Data.ID(ParentId));
            ParentFolderChildrens = ParentFolderItem.Axes.GetDescendants().Where(x => x.TemplateID.ToString().ToLower().Equals(TargetTemplateId.ToLower().Trim())).ToList();

            var lookupUtility = new LookupUtility();
            var allBlogInfo = LexBlogConstants.GetAllBlogInfo();
            foreach (var singleBlogInfo in allBlogInfo)
            {
                try
                {
                    File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "<<<<<<<<<<<<<<<<<< New blog started >>>>>>>>>" + singleBlogInfo.blogMediaFolderName + Environment.NewLine);

                    var cnn = LexBlogConstants.GetConnectionStringLocal(singleBlogInfo.connetionString);

                    var lookupFolderItem = Database.GetDatabase("master").GetItem(singleBlogInfo.destinationPath);
                    var query = "select MainItem.title, MainItem.post_date, MainItem.main_content, MainItem.post_name, MainItem.post_id from MainItem where " + singleBlogInfo.WhereCluase;
                    cnn.Open();
                    SqlCommand cmd = new SqlCommand(query, cnn);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet dsLookup = new DataSet();
                    da.Fill(dsLookup);

                    string mainItemTitle = string.Empty;
                    string mainItemPostDate = string.Empty;
                    string mainItemDescription = string.Empty;
                    string mainItemPostName = string.Empty;
                    string mainItemPostId = string.Empty;

                    if (dsLookup.Tables.Count > 0)
                    {
                        if (dsLookup.Tables[0].Rows.Count > 0)
                        {
                            for (int row = 0; row <= dsLookup.Tables[0].Rows.Count - 1; row++)
                            {
                                try
                                {
                                    List<string> Categories = new List<string>();
                                    List<string> Tags = new List<string>();
                                    List<string> Authors = new List<string>();

                                    mainItemTitle = !DBNull.Value.Equals(dsLookup.Tables[0].Rows[row][0]) ? Convert.ToString(dsLookup.Tables[0].Rows[row][0]) : "";
                                    mainItemPostDate = !DBNull.Value.Equals(dsLookup.Tables[0].Rows[row][1]) ? Convert.ToString(dsLookup.Tables[0].Rows[row][1]) : "";
                                    mainItemDescription = !DBNull.Value.Equals(dsLookup.Tables[0].Rows[row][2]) ? Convert.ToString(dsLookup.Tables[0].Rows[row][2]) : "";
                                    mainItemPostName = !DBNull.Value.Equals(dsLookup.Tables[0].Rows[row][3]) ? Convert.ToString(dsLookup.Tables[0].Rows[row][3]) : "";
                                    mainItemPostId = !DBNull.Value.Equals(dsLookup.Tables[0].Rows[row][4]) ? Convert.ToString(dsLookup.Tables[0].Rows[row][4]) : "";

                                    string itemName = (string.IsNullOrEmpty(mainItemTitle) ? (string.IsNullOrEmpty(mainItemPostName) ? string.Empty : LexBlogConstants.ValidateItemName(mainItemPostName)) : LexBlogConstants.ValidateItemName(mainItemTitle)).Trim();


                                    if (!string.IsNullOrEmpty(itemName))
                                    {
                                        File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + (row + 1).ToString() + " >>>> New Article creation started. Article Name is ----" + itemName + Environment.NewLine);

                                        string mediaLinksReplacement = mainItemDescription;

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

                                        MatchCollection matches = regx.Matches(mediaLinksReplacement.ToString());
                                        foreach (Match matchedItems in matches)
                                        {
                                            if (matchedItems.Value.Contains(singleBlogInfo.blogURL)) //To Change
                                            {
                                                var extension = matchedItems.Value.ToString().Split('.').Last();
                                                if (extension == "jpg" || extension == "png" || extension == "pdf" || extension == "gif")
                                                {
                                                    string FileName = Path.GetFileName(matchedItems.ToString());
                                                    string NewFileName = LexBlogConstants.RemoveSpecialChars(FileName);
                                                    NewFileName = NewFileName.Insert(NewFileName.Length - 3, "_");

                                                    string filePath = "/sitecore/media library/LexBlog/Images/Blog Images/" + singleBlogInfo.blogMediaFolderName + "/" + NewFileName; //To Change
                                                    Item MediaItem = Database.GetDatabase("master").GetItem(filePath);
                                                    if (MediaItem != null)
                                                    {
                                                        string MediaId = MediaItem.ID.ToString().Replace("{", "").Replace("}", "").Replace("-", "").ToLower();
                                                        if (!string.IsNullOrEmpty(MediaId))
                                                        {
                                                            string ReplacementPath = "~/media/" + MediaId + ".ashx";
                                                            mediaLinksReplacement = mediaLinksReplacement.Replace(matchedItems.ToString(), ReplacementPath);
                                                        }
                                                        else
                                                        {
                                                            File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Media Item does not Exist ----" + FileName + Environment.NewLine);
                                                        }
                                                    }
                                                    else
                                                    {
                                                        File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Media Item does not Exist ----" + FileName + Environment.NewLine);
                                                    }
                                                }
                                            }
                                        }

                                        var queryCategory = "select Item_Category.nicename, Item_Category.post_id from Item_Category where post_id = '" + mainItemPostId + "' order by nicename";
                                        SqlCommand cmdCategory = new SqlCommand(queryCategory, cnn);
                                        SqlDataAdapter daCategory = new SqlDataAdapter(cmdCategory);
                                        DataSet dsLookupCategory = new DataSet();
                                        daCategory.Fill(dsLookupCategory);

                                        if (dsLookupCategory.Tables.Count > 0)
                                        {
                                            if (dsLookupCategory.Tables[0].Rows.Count > 0)
                                            {
                                                for (int rowCategory = 0; rowCategory <= dsLookupCategory.Tables[0].Rows.Count - 1; rowCategory++)
                                                {
                                                    string CategoryNiceName = !DBNull.Value.Equals(dsLookupCategory.Tables[0].Rows[rowCategory][0]) ? Convert.ToString(dsLookupCategory.Tables[0].Rows[rowCategory][0]) : "";

                                                    Item BlogCategoryFolderItem = Database.GetDatabase("master").GetItem(singleBlogInfo.BlogCategoryFolder);
                                                    if (BlogCategoryFolderItem != null)
                                                    {
                                                        var CategoryItemId = BlogCategoryFolderItem.Children.Where(x => x.Fields["NiceName"].Value.ToLower().Trim().Equals(CategoryNiceName.ToLower().Trim())).Select(x => x.ID.ToString()).FirstOrDefault();

                                                        if (!string.IsNullOrEmpty(CategoryItemId))
                                                        {
                                                            Categories.Add(CategoryItemId);
                                                        }
                                                        else
                                                        {
                                                            File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Category not found. Category Nicename is ----" + CategoryNiceName + Environment.NewLine);
                                                        }
                                                    }
                                                    else
                                                    {
                                                        File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Blog Category Folder does not exist!! ----" + Environment.NewLine);
                                                    }
                                                }
                                            }
                                        }

                                        var queryTags = "select Item_Tag.nicename, Item_Tag.post_id from Item_Tag where post_id = '" + mainItemPostId + "' order by nicename";
                                        SqlCommand cmdTags = new SqlCommand(queryTags, cnn);
                                        SqlDataAdapter daTags = new SqlDataAdapter(cmdTags);
                                        DataSet dsLookupTags = new DataSet();
                                        daTags.Fill(dsLookupTags);

                                        if (dsLookupTags.Tables.Count > 0)
                                        {
                                            if (dsLookupTags.Tables[0].Rows.Count > 0)
                                            {
                                                for (int rowTags = 0; rowTags <= dsLookupTags.Tables[0].Rows.Count - 1; rowTags++)
                                                {
                                                    string TagNiceName = !DBNull.Value.Equals(dsLookupTags.Tables[0].Rows[rowTags][0]) ? Convert.ToString(dsLookupTags.Tables[0].Rows[rowTags][0]) : "";

                                                    Item TagFolderItem = Database.GetDatabase("master").GetItem(singleBlogInfo.BlogTagFolder);
                                                    if (TagFolderItem != null)
                                                    {
                                                        var TagItemId = TagFolderItem.Children.Where(x => x.Fields["Slug"].Value.ToLower().Trim().Equals(TagNiceName.ToLower().Trim())).Select(x => x.ID.ToString()).FirstOrDefault();

                                                        if (!string.IsNullOrEmpty(TagItemId))
                                                        {
                                                            Tags.Add(TagItemId);
                                                        }
                                                        else
                                                        {
                                                            File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Tag not found. Tag Nicename is ----" + TagNiceName + Environment.NewLine);
                                                        }
                                                    }
                                                    else
                                                    {
                                                        File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Blog Tag Folder does not exist!! ----" + Environment.NewLine);
                                                    }
                                                }
                                            }
                                        }

                                        var queryAuthors = "select Author.author_display_name, Item_Author.post_id, Author.author_id, Author.author_email from Item_Author inner join Author on Item_Author.author_id = Author.author_id where Item_Author.post_id = '" + mainItemPostId + "' order by Author.author_display_name";
                                        SqlCommand cmdAuthors = new SqlCommand(queryAuthors, cnn);
                                        SqlDataAdapter daAuthors = new SqlDataAdapter(cmdAuthors);
                                        DataSet dsLookupAuthors = new DataSet();
                                        daAuthors.Fill(dsLookupAuthors);
                                        StringBuilder otherAuthors = null;

                                        if (dsLookupAuthors.Tables.Count > 0)
                                        {
                                            if (dsLookupAuthors.Tables[0].Rows.Count > 0)
                                            {
                                                otherAuthors = new StringBuilder();

                                                for (int rowAuthors = 0; rowAuthors <= dsLookupAuthors.Tables[0].Rows.Count - 1; rowAuthors++)
                                                {
                                                    string AuthorDisplayName = !DBNull.Value.Equals(dsLookupAuthors.Tables[0].Rows[rowAuthors][0]) ? Convert.ToString(dsLookupAuthors.Tables[0].Rows[rowAuthors][0]) : "";
                                                    string AuthorId = !DBNull.Value.Equals(dsLookupAuthors.Tables[0].Rows[rowAuthors][2]) ? Convert.ToString(dsLookupAuthors.Tables[0].Rows[rowAuthors][2]) : "";
                                                    string AuthorEmail = !DBNull.Value.Equals(dsLookupAuthors.Tables[0].Rows[rowAuthors][3]) ? Convert.ToString(dsLookupAuthors.Tables[0].Rows[rowAuthors][3]) : "";
                                                   
                                                    otherAuthors.Append("," + AuthorDisplayName);
                                                }
                                            }
                                        }

                                        MigratedBlogs newBlog = new MigratedBlogs();
                                        newBlog.MainItemTitle = mainItemTitle;
                                        newBlog.MainItemPostName = mainItemPostName;
                                        newBlog.MainItemPostId = mainItemPostId;
                                        newBlog.MainItemDescription = mediaLinksReplacement;
                                        newBlog.MainItemPostDate = mainItemPostDate;

                                        if (otherAuthors != null && !string.IsNullOrEmpty(Convert.ToString(otherAuthors)))
                                        {
                                            newBlog.OtherAuthors = Convert.ToString(otherAuthors).TrimStart(',');
                                        }

                                        CreateSitecoreItem(singleBlogInfo.destinationPath, itemName, LexBlogConstants.BlogTemplateId, newBlog, Categories, Tags, Authors);

                                        File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Processing of Item done Successfully!" + Environment.NewLine);
                                    }
                                }
                                catch (Exception ex)
                                {
                                    File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Item encountered error in For Loop. Item exception is  ----" + ex + Environment.NewLine);
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Error occured in Main Entry. Error is -----" + ex + Environment.NewLine);
                }
            }
        }

        public static Item CreateSitecoreItem(string destinationPath, string itemName, string targetTemplateId, MigratedBlogs itemFields, List<string> blogCats, List<string> blogTags, List<string> blogAuthors)
        {
            Item sitecoreItem = null;
            try
            {
                // The SecurityDisabler overrides the current security model, allowing you
                // to access the item without any security. It's like the user being an administrator
                using (new Sitecore.SecurityModel.SecurityDisabler())
                {
                    // Get the master database
                    Database master = Database.GetDatabase("master");
                    // Get the place in the site tree where the new item must be inserted
                    Item parentItem = master.GetItem(destinationPath);
                    if (parentItem != null)
                    {
                        // Get the template to base the new item
                        BranchItem template = master.GetItem(targetTemplateId);
                        // Add the item to the site tree
                        sitecoreItem = parentItem.Add(itemName, template);
                    }
                    if (sitecoreItem != null)
                    {

                        using (new Sitecore.SecurityModel.SecurityDisabler())
                        {
                            File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Blog Creation Started. Blog Name is  ----" + itemName + Environment.NewLine);

                            LexBlogConstants.SetItemTitles(sitecoreItem, itemFields.MainItemTitle);
                            sitecoreItem.Editing.BeginEdit();

                            sitecoreItem.Fields["Title"].Value = itemFields.MainItemTitle;
                            sitecoreItem.Fields["Description"].Value = itemFields.MainItemDescription;
                            sitecoreItem.Fields["PublishDate"].Value = Sitecore.DateUtil.ToIsoDate(Convert.ToDateTime(itemFields.MainItemPostDate));
                            sitecoreItem.Name = itemName;
                            sitecoreItem.Fields["OtherAuthors"].Value = itemFields.OtherAuthors;

                            var multiListFldCategory = (MultilistField)sitecoreItem.Fields["Categories"];
                            foreach (var itemId in blogCats)
                            {
                                multiListFldCategory.Add(itemId);
                            }

                            var multiListFldTags = (MultilistField)sitecoreItem.Fields["Tags"];
                            foreach (var itemId in blogTags)
                            {
                                multiListFldTags.Add(itemId);
                            }

                            var treeListFldAuthors = (MultilistField)sitecoreItem.Fields["Authors"];
                            foreach (var itemId in blogAuthors)
                            {
                                treeListFldAuthors.Add(itemId);
                            }

                            File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Blog Creation done successfully!"+ Environment.NewLine);

                            sitecoreItem.Editing.EndEdit();
                        }

                        File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "With Category " + blogCats.Count
                                + " Tags=" + blogTags.Count + " Authors=" + blogAuthors.Count + Environment.NewLine);
                    }
                }
            }
            catch (Exception ex)
            {
                File.AppendAllText(LexBlogConstants.LogPath, Environment.NewLine + "Error occured during blog creation. Error is -----" + ex + Environment.NewLine);
            }

            return sitecoreItem;
        }
    }
}




After running all these code, our Content tree for migrated blog looks as

LexBlog Items in Sitecore Content Tree
LexBlog Items in Sitecore Content Tree

 
Hurreyy we successfully migrated our LexBlog to Sitecore.

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

Till that happy sitecoring :)

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