/// <summary>
        /// Find Executable items 
        /// Find XAML items 
        /// Union Executable items and XAML items if the union is needed.  
        /// Union happens only when the filter in the UI is set to “All” which means no filtering is applied.  
        /// In all other filter values we either search for XAML items or for Executable items. 
        /// Apply filtering Apply sorting
        /// Select the requested page
        /// </summary>
        /// <param name="request"></param>
        /// <returns>MarketplaceSearchResult object</returns>
        public static MarketplaceSearchResult SearchMarketplace(MarketplaceSearchQuery request)
        {
            MarketplaceSearchResult result = null;
            var resultCollection = new List<MarketplaceAsset>();
            MarketplaceAsset sab = null;

            try
            {
                var spInputParam = ConvertSearchQueryToStoredProc(request);
                if (spInputParam == null)
                    throw new ArgumentException(string.Format("Marketplace Search Query is error. Filter: {0} or UserRole: {1}",
                        request.FilterType,request.UserRole));

                //This is an invalid operation since the author is not allowed to view templates or publishing workflows.
                if (request.UserRole == Author && (request.FilterType == MarketplaceFilter.Templates || request.FilterType == MarketplaceFilter.PublishingWorkflows))
                    return null;

                if (request.SortCriteria == null)
                    request.SortCriteria = new List<SortCriterion>() { new SortCriterion() { FieldName = "UpdatedDate", IsAscending = false } };

                Database database = DatabaseFactory.CreateDatabase();
                DbCommand command = database.GetStoredProcCommand("[dbo].[Marketplace_Search]");

                database.AddParameter(command, "@SearchText", DbType.String, ParameterDirection.Input, null, DataRowVersion.Default, request.SearchText);
                database.AddParameter(command, "@AssetType", DbType.Int16, ParameterDirection.Input, null, DataRowVersion.Default, spInputParam.Item1);
                database.AddParameter(command, "@GetTemplates", DbType.Byte, ParameterDirection.Input, null, DataRowVersion.Default, spInputParam.Item2);
                database.AddParameter(command, "@GetPublishingWorkflows", DbType.Byte, ParameterDirection.Input, null, DataRowVersion.Default, spInputParam.Item3);
                database.AddParameter(command, "@PageSize", DbType.Int32, ParameterDirection.Input, null, DataRowVersion.Default, request.PageSize);
                database.AddParameter(command, "@PageNumber", DbType.Int32, ParameterDirection.Input, null, DataRowVersion.Default, request.PageNumber);
                database.AddParameter(command, "@SortColumn", DbType.String, ParameterDirection.Input, null, DataRowVersion.Default, request.SortCriteria[0].FieldName);
                database.AddParameter(command, "@SortAscending", DbType.Byte, ParameterDirection.Input, null, DataRowVersion.Default, request.SortCriteria[0].IsAscending);
                database.AddParameter(command, "@FilterOlder", DbType.Byte, ParameterDirection.Input, null, DataRowVersion.Default, request.IsNewest);

                using (IDataReader reader = database.ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        result = new MarketplaceSearchResult();
                        sab = new MarketplaceAsset();
                        sab.Id = Convert.ToInt64(reader["Id"]);
                        sab.Name = Convert.ToString(reader["Name"]);
                        sab.CreatedBy = Convert.ToString(reader["InsertedByUserAlias"]);
                        sab.UpdatedBy = Convert.ToString(reader["UpdatedByUserAlias"]);
                        sab.UpdatedDate = Convert.ToDateTime(reader["UpdatedDateTime"]);
                        sab.Version = Convert.ToString(reader["Version"]);
                        if (Convert.ToString(reader["AssetType"]) == "XAML")
                            sab.AssetType = AssetType.Project;
                        else
                            sab.AssetType = AssetType.Activities;
                        sab.IsTemplate = reader["IsTemplate"] == DBNull.Value ? (bool?)null : Convert.ToBoolean(reader["IsTemplate"]);
                        sab.IsPublishingWorkflow = reader["IsPublishingWorkflow"] == DBNull.Value ? (bool?)null : Convert.ToBoolean(reader["IsPublishingWorkflow"]);
                        resultCollection.Add(sab);
                    }

                    reader.NextResult();
                    if (reader.Read())
                    {
                        result.PageNumber = Convert.ToInt32(reader["PageNumber"]);
                        result.PageCount = Convert.ToInt32(reader["PageCount"]);
                    }
                    if (result != null)
                    {
                        result.Items = resultCollection;
                        result.PageSize = request.PageSize;
                    }
                }
            }
            catch (SqlException ex)
            {
                ex.HandleException();
            }

            return result;
        }
        /// <summary>
        /// load marketplace data for UI displaying
        /// </summary>
        /// <param name="results"></param>
        private void LoadMarketplace(MarketplaceSearchResult results)
        {
            if (results == null)
            {
                PageCount = 0;
                CurrentPage = 1;
                ResultList = null;
                return;
            }

            SetDownloadSelections();
            this.PageCount = results.PageCount;
            this.CurrentPage = results.PageNumber;
            this.ResultList = new ObservableCollection<MarketplaceAssetModel>(results.Items.ToList().Select(a =>
            {
                MarketplaceAssetModel m = (MarketplaceAssetModel)a;
                if (activitiesSelections.ContainsKey(m.Id) || projectsSelections.ContainsKey(m.Id))
                    m.IsMarkedForDownload = true;

                if (m.AssetType == AssetType.Project)
                    m.IsDownloaded = IsProjectExists(m);
                else if (Caching.ActivityAssemblyItems.Any(i => i.Name == m.Name && i.Version.ToString() == m.Version))
                {
                    m.IsDownloaded = true;
                    m.Location = Caching.ActivityAssemblyItems.Where(i => i.Name == m.Name && i.Version.ToString() == m.Version).FirstOrDefault().Location;
                }
                return m;
            }));
        }