Saturday, May 19, 2012

PowerShell : Mount-SPContentDatabase / Dismount-SPContentDatabase database

Situation

If a client provides a database backup of the SharePoint site running on their server and we want to use that database in our environment. Using powershell we have to change the mounting of database and use the database what client sent.

Prerequesites

Restore the database given by client in your Sharepoint database instance. Once that is done run the below given powershell in "SharePoint 2010 Management Shell".

Powershell

# Url of the site collection which the database is going to point
$SiteUrl = "http://MyMachine"
# The current database site is using.
$CurrentContentDatabase = "WSS_Content"
# New database which the site will point
$NewContentDatabase = "WSS_Content_NewDB"
# SQL SERVER Instanne
$DatabaseServer = "MYMachine\SHAREPOINT"

# The command will dismount the current databae
Dismount-SPContentDatabase -Identity $CurrentContentDatabase -confirm:$false

# Mounts the new database
Mount-SPContentDatabase -name $NewContentDatabase -DatabaseServer $DatabaseServer -WebApplication $SiteUrl -confirm:$false

Friday, May 18, 2012

Apply "Multiple" column Group By on DataTable in C#

Goal

A few days ago a reader asked for Multiple column group by on DataTable. This blos is in reply of that question. This was my old blog Apply group by clause on Datatable in C#.

Solution

#region System
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
#endregion

namespace SampleApplication
{
    #region Enums

    /// <summary>
    /// The functions which can be used for aggreation
    /// </summary>
    public enum AggregateFunction
    {
        Sum,
        Avg,
        Count,
        Max,
        Min
    }

    #endregion

    #region Entity

    /// <summary>
    /// The class which will have properties of function to be performed and on which field
    /// </summary>
    public class DataTableAggregateFunction
    {
        /// <summary>
        /// The function to be performed
        /// </summary>
        public AggregateFunction enmFunction { get; set; }

        /// <summary>
        /// Performed for which column
        /// </summary>
        public string ColumnName { get; set; }

        /// <summary>
        /// What should be the name after output
        /// </summary>
        public string OutPutColumnName { get; set; }
    }

    #endregion
    

    public class Helper
    {
        /// <summary>
        /// Demo for Group By
        /// </summary>
        public void DemoGroupBy()
        { 
            //Gets the mock data table
            DataTable _dt = GetDataTable();

            //Add columns which you want to group by
            IList<string> _groupByColumnNames = new List<string>();
            _groupByColumnNames.Add("State");
            _groupByColumnNames.Add("City");

            //Functions you want to perform on which fields
            IList<DataTableAggregateFunction> _fieldsForCalculation = new List<DataTableAggregateFunction>();
            _fieldsForCalculation.Add(new DataTableAggregateFunction() { enmFunction = AggregateFunction.Avg, ColumnName = "Population", OutPutColumnName = "PopulationAvg" });
            _fieldsForCalculation.Add(new DataTableAggregateFunction() { enmFunction = AggregateFunction.Sum, ColumnName = "Population", OutPutColumnName = "PopulationSum" });
            _fieldsForCalculation.Add(new DataTableAggregateFunction() { enmFunction = AggregateFunction.Count, ColumnName = "Population", OutPutColumnName = "PopulationCount" });
            _fieldsForCalculation.Add(new DataTableAggregateFunction() { enmFunction = AggregateFunction.Max, ColumnName = "Year", OutPutColumnName = "YearMax" });
            _fieldsForCalculation.Add(new DataTableAggregateFunction() { enmFunction = AggregateFunction.Min, ColumnName = "Year", OutPutColumnName = "YearMin" });

            //Gets the result after grouping by
            DataTable dtGroupedBy = GetGroupedBy(_dt, _groupByColumnNames, _fieldsForCalculation);
        }


        /// <summary>
        /// Returns a mock data table
        /// </summary>
        /// <returns></returns>
        private DataTable GetDataTable()
        {
            //Declarations
            DataTable _dt = new DataTable();
            DataRow _dr;
            
            //Create columns
            _dt.Columns.Add(new DataColumn() { ColumnName = "State" });
            _dt.Columns.Add(new DataColumn() { ColumnName = "City" });
            _dt.Columns.Add(new DataColumn() { ColumnName = "Year", DataType = typeof(System.Int32) });
            _dt.Columns.Add(new DataColumn() { ColumnName = "Population", DataType = typeof(System.Int32) });

            //Add mock data
            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Ahmedabad"; _dr["Year"] = 2009; _dr["Population"] = 6000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Surat"; _dr["Year"] = 2009; _dr["Population"] = 2000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Rajkot"; _dr["Year"] = 2009; _dr["Population"] = 1000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Maharastra"; _dr["City"] = "Mumbai"; _dr["Year"] = 2009; _dr["Population"] = 3000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Maharastra"; _dr["City"] = "Pune"; _dr["Year"] = 2009; _dr["Population"] = 3000000; _dt.Rows.Add(_dr);

            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Ahmedabad"; _dr["Year"] = 2010; _dr["Population"] = 8000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Maharastra"; _dr["City"] = "Mumbai"; _dr["Year"] = 2010; _dr["Population"] = 8000000; _dt.Rows.Add(_dr);

            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Ahmedabad"; _dr["Year"] = 2011; _dr["Population"] = 6000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Surat"; _dr["Year"] = 2011; _dr["Population"] = 2000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Rajkot"; _dr["Year"] = 2011; _dr["Population"] = 1000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Maharastra"; _dr["City"] = "Mumbai"; _dr["Year"] = 2011; _dr["Population"] = 3000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Maharastra"; _dr["City"] = "Pune"; _dr["Year"] = 2011; _dr["Population"] = 3000000; _dt.Rows.Add(_dr);

            //Return table
            return _dt;
        }

        /// <summary>
        /// Group by DataTable
        /// </summary>
        /// <param name="_dtSource"></param>
        /// <param name="_groupByColumnNames"></param>
        /// <param name="_fieldsForCalculation"></param>
        /// <returns></returns>
        private DataTable GetGroupedBy(DataTable _dtSource, IList<string> _groupByColumnNames, IList<DataTableAggregateFunction> _fieldsForCalculation)
        {
            
            //Once the columns are added find the distinct rows and group it bu the numbet
            DataTable _dtReturn = _dtSource.DefaultView.ToTable(true, _groupByColumnNames.ToArray());

            //The column names in data table
            foreach (DataTableAggregateFunction _calculatedField in _fieldsForCalculation)
            {
                _dtReturn.Columns.Add(_calculatedField.OutPutColumnName);
            }

            //Gets the collection and send it back
            for (int i = 0; i < _dtReturn.Rows.Count; i = i + 1)
            {
                #region Gets the filter string
                string _filterString = string.Empty;
                for (int j = 0; j < _groupByColumnNames.Count; j = j + 1)
                {
                    if (j > 0)
                    {
                        _filterString += " AND ";
                    }
                    if (_dtReturn.Columns[_groupByColumnNames[j]].DataType == typeof(System.Int32))
                    {
                        _filterString += _groupByColumnNames[j] + " = " + _dtReturn.Rows[i][_groupByColumnNames[j]].ToString() + "";
                    }
                    else
                    {
                        _filterString += _groupByColumnNames[j] + " = '" + _dtReturn.Rows[i][_groupByColumnNames[j]].ToString() + "'";
                    }
                }
                #endregion

                #region Compute the aggregate command

                foreach (DataTableAggregateFunction _calculatedField in _fieldsForCalculation)
                {
                    _dtReturn.Rows[i][_calculatedField.OutPutColumnName] = _dtSource.Compute(_calculatedField.enmFunction.ToString() + "(" + _calculatedField.ColumnName + ")", _filterString);
                }
                
                #endregion
            }

            return _dtReturn;
        }
    }
}


How to Use?

Call the function from somewhere.
(new SampleApplication.Helper()).DemoGroupBy();


Screenshot

Before Group By :
After Group By :


Please let me know if this helped.

Thursday, May 17, 2012

SharePoint 2010 : Placing the SharePoint:PeopleEditor icons on right instead of bottom.

Goal

The SharePoint:PeopleEditor show the icons on the right side instead of bottom.

Sample Page : ASPX

<style type="text/css">
        .ppimg{border: 0px;vertical-align: middle !important;}
    </style>
    <br /><br /><br /><br />
    <table border="0" cellpadding="10" cellspacing="5" style="border:1px solid #808080">
        <tr>
            <td colspan="2">People picker on right instead of bottom</td>
        </tr>
        <tr>
            <td>
                Default
            </td>
            <td>
                <SharePoint:PeopleEditor AllowEmpty="false" ValidatorEnabled="true" ID="ppDefault"
                    runat="server" ShowCreateButtonInActiveDirectoryAccountCreationMode="false" SelectionSet="User,SPGroup"
                    Rows="1" Width="300px" />
            </td>
        </tr>
        <tr>
            <td nowrap="nowrap">
                Custom View
            </td>
            <td>
                <table>
                    <tr>
                        <td>
                            <SharePoint:PeopleEditor AllowEmpty="false" ValidatorEnabled="true" ID="ppCustom"
                                runat="server" ShowCreateButtonInActiveDirectoryAccountCreationMode="false" SelectionSet="User,SPGroup"
                                Rows="1" Width="300px" ShowButtons="false" />
                        </td>
                        <td valign="top">
                            <a id="aPPChekNames" runat="server" title="Check Name" href="javascript:">
                                <img src="/_layouts/images/checknames.png" alt="Check Names" class="ppimg" /></a>
                        </td>
                        <td valign="top">
                            <a id="aPPBrowse" runat="server" title="Browse" href="javascript:">
                                <img src="/_layouts/images/addressbook.gif" alt="Browse" class="ppimg" /></a>
                        </td>
                    </tr>
                </table>
            </td>
        </tr>
    </table>


Sample Page : Code

//Apply the Javascript to the Icons
aPPChekNames.Attributes.Add("onclick", "if(!ValidatePickerControl('" + ppCustom.ClientID + "')){ ShowValidationError(); return false;} var arg=getUplevel('" + ppCustom.ClientID + "'); var ctx='" + ppCustom.ClientID + "';EntityEditorSetWaitCursor(ctx);WebForm_DoCallback('" + ppCustom.UniqueID + "',arg,EntityEditorHandleCheckNameResult,ctx,EntityEditorHandleCheckNameError,true);return false;");
aPPBrowse.Attributes.Add("onclick", "javascript:__Dialog__" + ppCustom.ClientID + "(); return false;");


Result



I am not sure if many know about this or this is normal, but just wanted to share. Let me know if this helped.

SharePoint 2010 : Call server side function from XSL.

Goal

To use a server side function from a XSL used in a web part.

Solution

It is simple to use a server side function. Just need to make sure the dll should be deployed in GAC. In this example I created a class with name "ClassName", the project name is "SampleApplication". Deployed the dll in GAC. Modify the XSL head and add xmlns:helper="SampleApplication.ClassName" on the head. "helper" is like an tag which will point to the class.

XSL
<xsl:stylesheet 
  version="1.0" 
  exclude-result-prefixes="x d xsl msxsl cmswrt helper"
  xmlns:x="http://www.w3.org/2001/XMLSchema" 
  xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" 
  xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime"
  xmlns:cmswrt="http://schemas.microsoft.com/WebParts/v3/Publishing/runtime"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"
  xmlns:helper="SampleApplication.ClassName">


SampleApplication.ClassName
#region System
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
#endregion

namespace SampleApplication
{
    public class ClassName
    {
        #region Properties
        
        /// <summary>
        /// Name 
        /// </summary>
        private static string Name = "Maulik Dhorajia";
        
        /// <summary>
        /// Today Date
        /// </summary>
        private static string TodayDate = System.DateTime.Now.ToString();

        #endregion

        /// <summary>
        /// Gets the property
        /// </summary>
        /// <param name="propertyKey"></param>
        /// <returns></returns>
        public static string GetProperty(string propertyKey)
        {
            if (propertyKey == "Name")
            {
                return Name;
            }
            else if (propertyKey == "TodayDate")
            {
                return TodayDate;
            }
            else 
            {
                return "";
            }
        }
    }
}


Calling the function from XSL
<xsl:value-of select="helper:GetLabel('Name')"/>
<xsl:value-of select="helper:GetLabel('TodayDate')"/>


This should be all to show the stuff on the page. Let me know if this helped.

Wednesday, May 16, 2012

SharePoint 2010 : Insert custom icon in a custom group on Ribbon of Document Library.

Goal

Insert a custom icon(Email icon) on the SharePoint Document Library. The Icon should show in a Custom Group.

Challange

I thought getting the Icon on the top was easy(actually it was easy to place an icon on the top). But to place the icon in a Custom Group was a bit tough that it seems.

Follow the steps to achieve

  1. Open Visual Studio 2010, Create an empty SharePoint Application.
  2. Add a Module, Edit the Element.xml
    <?xml version="1.0" encoding="utf-8"?>
    <Elements xmlns="http://schemas.microsoft.com/sharepoint/">
      <CustomAction
          Id="EmailDocsCustom"
          Location="CommandUI.Ribbon"
          RegistrationType="ContentType"
          RegistrationId="0x01">
        <CommandUIExtension>
          <CommandUIDefinitions>
            <CommandUIDefinition Location="Ribbon.Documents.Groups._children">
              <Group
                  Id="EmailDocsCustomGroup"
                  Sequence="100"
                  Description="E-Mail Controls"
                  Title="E-Mail Controls"
                  Template="EmailDocsCustomGroupTemplate">
                <Controls Id="EmailDocsCustomGroupControl">
                  <Button
                      Id="EmailDocsCustomGroupControlButton"
                      Sequence="5"
                      Command="SimpleAlert"
                      Image32by32="/_layouts/1033/images/formatmap32x32.png" Image32by32Left="-448" Image32by32Top="-128"
                      ToolTipTitle="Email Documents"
                      ToolTipDescription="Select the documents which you want to email!"
                      LabelText="Email Selected Documents"
                      TemplateAlias="o1" />
                </Controls>
              </Group>
            </CommandUIDefinition>
            <CommandUIDefinition Location="Ribbon.Templates._children">
              <GroupTemplate Id="EmailDocsCustomGroupTemplate">
                <Layout Title="LargeLarge">
                  <OverflowSection Type="OneRow" TemplateAlias="o1" DisplayMode="Large"/>
                  <OverflowSection Type="OneRow" TemplateAlias="o2" DisplayMode="Large"/>
                </Layout>
              </GroupTemplate>
            </CommandUIDefinition>
            <CommandUIDefinition Location="Ribbon.Documents.Scaling._children">
              <MaxSize Id="Ribbon.Documents.Scaling.Custom.MaxSize" Sequence="15" GroupId="EmailDocsCustomGroup" Size="LargeLarge" />
            </CommandUIDefinition>
          </CommandUIDefinitions>
          <CommandUIHandlers>
            <CommandUIHandler Command="SimpleAlert" CommandAction="javascript:OpenEmailPage();" />
          </CommandUIHandlers>
        </CommandUIExtension>
      </CustomAction>
    
      <CustomAction
       Id="EmailRelatedScript"
       Location="ScriptLink"
       ScriptSrc ="/_layouts/WhatEvePathYouHave/EmailScript.js"/>
      
    </Elements>
    
  3. Place the EmailScript.js at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\WhatEvePathYouHave(You can map the folder and do this). With the help of this script you can do manipulations on the click of the icon placed on the top ribbon. Below is the script in EmailScript.js.
    //Get the current user and add the values found in the Email settings list
    function OpenEmailPage() {
    
        //Gets the current Context
        var _ctx = new SP.ClientContext.get_current();
        //Selected Items Variables
        var _itemIds = "";
        //Get current list id
        var _listId = SP.ListOperation.Selection.getSelectedList();
        var _listUrl = window.location.href;
        //get all selected list items
        var _selectedItems = SP.ListOperation.Selection.getSelectedItems(_ctx);
        //collect selected item ids
        for (var i = 0; i < _selectedItems.length; i++) {
            if (_itemIds == "")
            { _itemIds += _selectedItems[i].id; }
            else
            { _itemIds += "," + _selectedItems[i].id; }
        }
    
        alert('ListID = ' + _listId + "\nListUrl = " + _listUrl + "\nIDs=" + _itemIds);    
    }
  4. Before deploying the solution, check a document library. The ribbon on the top in the documents should like the below given image.
  5. Deploy the solution. Visit a Document Library, select docs and the new Icon should show up on the top right corner.
I am not very good with Ribbons and spent a lot of time to get just this portion. So thought if this can help many other who dont have time and have to get this quickly.