Surendra Sharma

Surendra Sharma

Search This Blog

Showing posts with label C#. Show all posts
Showing posts with label C#. Show all posts

Friday, October 19, 2018

ebook : SharePoint to Sitecore: Strategy and Implementation


Sharing my 6th eBook with Sitecore community - "SharePoint to Sitecore: Strategy and Implementation".

SharePoint to Sitecore
SharePoint to Sitecore


There are multiple ways to move data and images from SharePoint to Sitecore. I tried my best to include all the approaches but explained one approach which worked for me in Sitecore 9 update 1 instance.

You can read and download this ebook from here:


I hope you like this eBook.

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

Saturday, October 6, 2018

Quick way to convert excel, csv file data to C# Datatable


I received English and Arabic data in Excel file and I have to migrate this data to SQL server.

I don’t want to use Office API for this as it’s a bit complex.

So for this, I used Nuget package “ExcelDataReader” and “ExcelDataReader.DataSet” as below

ExcelDataReader Nuget Package
ExcelDataReader Nuget Package


Note :- Must include “ExcelDataReader.DataSet” Nuget package for getting AsDataSet() method in code.

Code for reading excel file and to get data table is very simple as 

using ExcelDataReader;
using System.IO;

namespace ExcelConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            //1. Create File Stream for Excel file
            FileStream stream = File.Open("d:\\NewsListWithAllColumns.xlsx", FileMode.Open, FileAccess.Read);

            //2.. Reading from a Excel file *.xlsx
            IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(stream);
            //...
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            //IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            //...

            //3. DataSet Configuration - Create column names from first row
            ExcelDataSetConfiguration excelDataSetConfiguration = new ExcelDataSetConfiguration
            {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = true
                }
            };

            //4. DataSet - The result of each spreadsheet will be created in the result.Tables
            var dataSet = excelReader.AsDataSet(excelDataSetConfiguration);

            //5. Get Datatable from dataset
            var dataTable = dataSet.Tables[0];

            //6. Read one of the cell to test
            var c11 = dataTable.Rows[2][2];

            //7. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();

        }
    }
}

Below is the screenshot while to view datatable output in program debug mode as

Excel to Datatable
Excel to Datatable

Let me know if you have any other idea to read excel file and get its values.

Friday, September 28, 2018

Manage Youtube videos from Sitecore

This is a series of 3 articles where I will show how you control Facebook, Twitter and Youtube feeds from Sitecore and display it on your web page.
 
In last 2 post, I showed how to manage Facebook feeds using Facebook graph API and Twitter Tweets by using its API.

In this post, we will check how to access and control Youtube video feeds from Sitecore. 

Youtube videos from Sitecore
Youtube videos from Sitecore


For this you need API keys from Google for using Youtube API. 

Implementation

Create Sitecore template having fields like

Sitecore fields for Youtube video feeds
Sitecore fields for Youtube video feeds

Code


Install Nuget package named “Google.Apis.YouTube.V3″ to your HELIX based feature project.

First we need to create Template mapping class for these Youtube fields as

namespace Sitecore.Feature.Social
{
    using Sitecore.Data;

    public struct Templates
    {
        public struct SocialFeedsManager
        {
            public static ID ID = new ID("{B1959961-A315-468D-8D03-CA50CB790C86}");
            public static ID ContentItemID = new ID("{EC8578D8-4C0F-413E-83F2-C9BA5B249E85}");


            public struct Fields
            {
            
                public static readonly ID YoutubeAPIKey = new ID("{D2B9B6B6-2A4C-4E7F-B63E-D9D119E599AA}");
                public static readonly ID YoutubeChannelId = new ID("{AEB5A02B-9CC8-4A30-92D6-63BA90F10C2B}");
                public static readonly ID YoutubeQueryPart = new ID("{BB545EE0-FFD7-4372-8D2B-AF36470A435C}");
                public static readonly ID YoutubePlatformName = new ID("{34465AAC-CB7A-4B39-96A5-2EB029C288D9}");
                public static readonly ID YoutubeFrom = new ID("{C1BE81E3-62E9-49A0-A805-F9A7A1B1B08D}");
                public static readonly ID YoutubeMaxFeedsInAll = new ID("{FAB9C465-C484-4928-8A89-C7191ADBCE61}");
                public static readonly ID YoutubeMaxFeeds = new ID("{E99D9E1D-D930-4244-B7A0-7C0E3923C44A}");
                public static readonly ID YoutubeIsActive = new ID("{D2508648-EB83-40A7-86F2-845EFA86A6F9}");            }
        }


    }
}

Create Model classes to holds values of Sitecore item fields values and JSON data return by Youtube for video feeds as
·         ChannelName – Enum for different social channel
·         YoutubeFeeds – Get video feeds from Youtube
·         SocialMediaFeed – Class to hold single video feed data from Youtube JSON data

using System.Collections.Generic;
using System.Linq;
using Google.Apis.YouTube.v3;
using Google.Apis.Services;

namespace Sitecore.Feature.Social.Models
{
    public class YoutubeFeeds
    {
        public List<SocialMediaFeed> GetYoutubeVideos(string apiKey, string queryPart, string channelId, string platformName, string from, int maxResult = 2)
        {
            YouTubeService yt = new YouTubeService(new BaseClientService.Initializer() { ApiKey = apiKey });

            var searchListRequest = yt.Search.List(queryPart);
            searchListRequest.MaxResults = maxResult;
            searchListRequest.Order = SearchResource.ListRequest.OrderEnum.Date;
            searchListRequest.ChannelId = channelId;
            var searchListResult = searchListRequest.Execute();

            var lstYoutubeFeeds = searchListResult.Items.Select(x => new SocialMediaFeed
            {
                FeedID = x.Id.VideoId.ToString(),
                PlatformName = platformName,
                Title = x.Snippet.Title,
                Description = x.Snippet.Description,
                ImageURL = (x.Snippet.Thumbnails.Medium ?? x.Snippet.Thumbnails.High ?? x.Snippet.Thumbnails.Standard ?? x.Snippet.Thumbnails.Default__).Url,
                FeedURL = "https://www.youtube.com/watch?v=" + x.Id.VideoId.ToString(),
                From = from,
                Timestamp = x.Snippet.PublishedAt.Value
            }).ToList();

            return lstYoutubeFeeds;
        }
    }
}

using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using Sitecore.Data.Items;
using Sitecore.Foundation.SitecoreExtensions.Extensions;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using static Sitecore.Feature.Social.Templates;

namespace Sitecore.Feature.Social.Models
{
    public enum ChannelName
    {
        All,
        Facebook,
        Twitter,
        Instagram,
        Youtube
    }

    /// <summary>
    /// Social Feeds Model for getting Sitecore fields data
    /// </summary>
    public class SocialFeedsManagerModel
    {

        //Youtube
        public string YoutubeAccessToken { get; set; }
        public string YoutubeAccessTokenSecret { get; set; }
        public string YoutubeConsumerKey { get; set; }
        public string YoutubeConsumerSecret { get; set; }
        public string YoutubePlatformName { get; set; }
        public int YoutubeMaxFeedsInAll { get; set; }
        public int YoutubeMaxFeeds { get; set; }
        public bool YoutubeIsActive { get; set; }

        public SocialFeedsManagerModel() { }

        public void InitializeData(Item dataSourceItem, ChannelName channelName)
        {
            if (channelName == ChannelName.Youtube || channelName == ChannelName.All)
            {
                //Youtube
                this.YoutubeIsActive = dataSourceItem.Fields[SocialFeedsManager.Fields.YoutubeIsActive].IsChecked();
                if (this.YoutubeIsActive)
                {
                    this.YoutubeAPIKey = dataSourceItem[SocialFeedsManager.Fields.YoutubeAPIKey];
                    this.YoutubeChannelId = dataSourceItem[SocialFeedsManager.Fields.YoutubeChannelId];
                    this.YoutubeFrom = dataSourceItem[SocialFeedsManager.Fields.YoutubeFrom];
                    this.YoutubeMaxFeeds = System.Convert.ToInt32(dataSourceItem[SocialFeedsManager.Fields.YoutubeMaxFeeds]);
                    this.YoutubeMaxFeedsInAll = System.Convert.ToInt32(dataSourceItem[SocialFeedsManager.Fields.YoutubeMaxFeedsInAll]);
                    this.YoutubePlatformName = dataSourceItem[SocialFeedsManager.Fields.YoutubePlatformName];
                    this.YoutubeQueryPart = dataSourceItem[SocialFeedsManager.Fields.YoutubeQueryPart];
                }
            }
        }
    }

    /// <summary>
    /// Model for Social Media Feeds returning as JSON
    /// </summary>
    public class SocialMediaFeed
    {
        public string FeedID { get; set; }
        public string PlatformName { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public string ImageURL { get; set; }
        public string FeedURL { get; set; }
        public DateTime Timestamp { get; set; }
        public string From { get; set; }
    }
}

Below is Controller code for fetching video feeds from Youtube model class and then as per settings in Sitecore item, return JSONResult of Youtube Feeds which finally received by AJAX request to show it on page.

namespace Sitecore.Feature.Social.Controllers
{
    using Newtonsoft.Json;
    using Sitecore.Data.Items;
    using Sitecore.Feature.Social.Models;
    using Sitecore.Foundation.SitecoreExtensions.Extensions;
    using Sitecore.Mvc.Presentation;
    using System;
    using System.Collections.Generic;
    using System.Globalization;
    using System.IO;
    using System.Linq;
    using System.Net;
    using System.Security.Cryptography;
    using System.Text;
    using System.Web.Mvc;
    using System.Web.Script.Serialization;
    using static Sitecore.Feature.Social.Templates;

    public class SocialController : Controller
    {
        private List<SocialMediaFeed> lstSocialFeeds = null;

        public ActionResult GetSocialFeeds(string channelName, string token)
        {
            string result = "Error - ";

            if (string.IsNullOrEmpty(channelName) || string.IsNullOrEmpty(token) || !token.Equals("accesstoken"))
            {
                result += "Unauthorized request!!!";
            }

            channelName = channelName ?? "all";
            result = CollectSocialFeeds(channelName, result);

            if (lstSocialFeeds != null && lstSocialFeeds.Count > 0)
            {
                return Json(new { success = true, responseResult = lstSocialFeeds }, JsonRequestBehavior.AllowGet);
            }
            else
            {
                return Json(new { success = false, responseResult = result }, JsonRequestBehavior.AllowGet);
            }

        }
        /// <summary>
        /// Collect all feeds in list
        /// </summary>
        /// <param name="channelName"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        private string CollectSocialFeeds(string channelName, string result)
        {
            try
            {
                SocialFeedsManagerModel _SocialFeedsManagerModel = new SocialFeedsManagerModel();

                Item dataSourceItem = null;

                if (RenderingContext.CurrentOrNull != null)
                {
                    var dataSourceId = RenderingContext.CurrentOrNull.Rendering.DataSource;
                    dataSourceItem = Sitecore.Context.Database.GetItem(dataSourceId);
                }

                if (!dataSourceItem?.IsDerived(Templates.SocialFeedsManager.ID) ?? true)
                {
                    dataSourceItem = Context.Database.GetItem(SocialFeedsManager.ContentItemID);
                }

                lstSocialFeeds = new List<SocialMediaFeed>();

                switch (channelName.ToLower())
                {                    
                    case "youtube":
                        {
                            _SocialFeedsManagerModel.InitializeData(dataSourceItem, ChannelName.Youtube);

                            if (_SocialFeedsManagerModel.YoutubeIsActive)
                            {
                                YoutubeFeeds youtube = new YoutubeFeeds();
                                lstSocialFeeds = youtube.GetYoutubeVideos(_SocialFeedsManagerModel.YoutubeAPIKey, _SocialFeedsManagerModel.YoutubeQueryPart
                                    , _SocialFeedsManagerModel.YoutubeChannelId, _SocialFeedsManagerModel.YoutubePlatformName, _SocialFeedsManagerModel.YoutubeFrom,
                                    _SocialFeedsManagerModel.YoutubeMaxFeeds);
                            }
                            else
                            {
                                result += " Youtube feeds are disabled.";
                            }
                            break;
                        }       
                    default:
                        {
                            result += " Invalid Social Media.";
                            lstSocialFeeds = null;
                            break;
                        }
                }
            }
            catch (Exception ex)
            {
                result += " Occurred." + ex.Message;
                lstSocialFeeds = null;
                string str = ex.Message;
            }

            return result;
        }
    }
}

Write AJAX script and view in HTML to load the feeds 

@model Sitecore.Feature.Social.Models.JSONOutput

<div id="SocialFeedsDiv">


    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

    <script>


        $(document).ready(function () {
            var b = null;
            $("#all").click(function () {
                loadfeeds("All")
            });

            $("#Facebook").click(function () {
                loadfeeds("Facebook")
            });

            $("#Twitter").click(function () {
                loadfeeds("Twitter")
            });

            $("#Youtube").click(function () {
                loadfeeds("Youtube")
            });

            $("#Instagram").click(function () {
                loadfeeds("Instagram")
            });


            function loadfeeds(channelName) {
                $.get("/api/sitecore/social/GetSocialFeeds?channelName=" + channelName + "&token=accesstoken", function (data, status) {
                    var index = 1;
                    $("#feedlist").html("");
                    $.each(data.responseResult, function (i, field) {
                        var imageurl = "";
                        if (field.ImageURL !="" || field.ImageURL != null) {
                            imageurl = "ImageURL : <a href='" + field.FeedURL + "' ><img src='" + field.ImageURL + "' alt='" + field.Title + "' /> </a> <br>";
                        }
                        $("#feedlist").append(
                            "<li>Sr No :" + index + "<br>" +
                            "FeedID :" + field.FeedID + "<br>" +
                            "PlatformName :" + field.PlatformName + "<br>" +
                            "Title :" + field.Title + "<br>" +
                            "Description :" + field.Description + "<br>" + imageurl
                            +
                            "FeedURL :" + field.FeedURL + "<br>" +
                            "Timestamp :" + field.Timestamp + "<br>" +
                            "From :" + field.From + "<br><br><br>" + "</li>"
                        );
                        index++;
                    });
                });
            }
        });

    </script>

     <br />
    <a href="javascript:void(0)" id="all">All</a><br />
    <a href="javascript:void(0)" id="Facebook">Facebook</a><br />
    <a href="javascript:void(0)" id="Twitter">Twitter</a><br />
    <a href="javascript:void(0)" id="Youtube">Youtube</a><br />
    <a href="javascript:void(0)" id="Instagram">Instagram</a><br />

    <div id="feed">

    </div>

    <br />
    <br />

    <div id="HTMLfeed">
        <ul id="feedlist"></ul>


    </div>

</div>

We can access this action method from URL AJAX as

This will return JSON result which include individual feeds
{
    "success": true,
    "responseResult": [
        {
            "FeedID": "111111111_2222222",
            "PlatformName": "Youtube",
            "Title": "Title of video",
            "Description": " We all love free things. If you apply same concept on study, its great for professional life as well as for future",
            "ImageURL": "https://i.ztimg.com/vi/111111/default.jpg",
            "FeedURL": "https://www.youtube.com/watch?v=111111",
            "Timestamp": "/Date(1530689661000)/",
            "From": " Bill Gates"
        }
    ]
}

Once we have JSON, we can show it in page in any desired HTML format.
That’s it.

I hope you enjoy this Sitecore article. 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.