Tuesday, September 4, 2012

JQuery : Quick paging solution with JQuery

Goal

Many of the times we have to give the paging solutions with JQuery. I have created a small generic script that will allow you to apply paging on html table very quickly. Just a few settings and script will manage the paging(Note:- Right now the script will apply one one table on a page).

Settings

Table ID : Apply an ID to the table with prefix "rpt". In our case we have used "rptPagingTable".
Paging Items : All the rows which are going to be shown on the basis of page. Apply a custom tag to the row(tag="item").
Next/Previous : The paging box will have a tag(tag="paging"). There are seperate tags assigned to the button Next and Previous. For Previous it is (tag="pre") and for Next it is (tag="next").

Solution

<html>
<head>
    <style type="text/css">
        body{font-family:Calibri,Verdana,Arial,Times New Roman;}
        .datagrid{border: 1px solid #808080;}
        .datagrid td{border: 1px solid #efefef;border-collapse: none;padding: 5px;}
        .header{background-color: #808080;color: #fff;text-align: center;font-size: 20px;}
        .itemtemplate{background-color: #fff;}
        .alternatingitemtemplate{background-color: #efefdf;}
        .footer{background-color: #808080;color: #fff;font-size: 15px;}
        .pager{background-color: #CED3D6;color: #000;font-size: 15px;text-align: center;}
        .pager a{color: #0877BD;}
        .pre{float: left;}
        .next{float: right;}
    </style>
    <script>!window.jQuery && document.write('<script src="http://code.jquery.com/jquery-1.4.2.min.js"><\/script>');</script>
    <script language="javascript" type="text/javascript">
        //CODE WHICH WILL HELP IN PAGING

        //Variable for paging
        var _currentPage = 1;
        var _pageSize = 0;
        var _totalPages = 1;

        $(document).ready(function () {

            //Setting the page size
            _pageSize = $("table[id*='rpt']").attr("pagesize");

            //Total Item
            var _trItem = $("tr[tag='item']");
            var _pager = $("tr[tag='paging']");

            //Calculate the page size
            _totalPages = parseInt($(_trItem).length / _pageSize) + ($(_trItem).length % _pageSize > 0 ? 1 : 0);

            //Hide the pager if the items are less than 13
            if ($(_trItem).length > _pageSize) {
                $(_pager).show();
            }
            else {
                $(_pager).hide();
            }

            //Page One Settings
            Paging();

            //Previous
            $("a[tag='pre']").click(function () {

                if (_currentPage == 1) {
                    return;
                }

                //Reduce the page index
                _currentPage = _currentPage - 1;

                //Paging
                Paging();
            });

            //Next
            $("a[tag='next']").click(function () {

                if (_currentPage == _totalPages) {
                    return;
                }

                //Increase the page index
                _currentPage = _currentPage + 1;

                //Paging
                Paging();
            });

        });

        //Shows/Hides the tr
        function Paging() {

            //Get the correct start index and end index
            var _endIndex = (_currentPage * _pageSize);
            var _startIndex = (_endIndex - _pageSize) + 1;

            //Hide all first
            $("tr[tag='item']").hide();

            //Show correct items
            $("tr[tag='item']").each(function (i) {
                var x = i + 1;
                if (x >= _startIndex && x <= _endIndex) {
                    $(this).show();
                }
            });
        }

    </script>
</head>
<body>
    <table id="rptPagingTable" cellpadding="0" cellspacing="0" border="0" class="datagrid"
        style="width: 700px" align="center" calculativegrid="PM" pagesize="5">
        <tr class="header"><td>No.</td><td style="width: 100%;">Instant Paging</td></tr>
        <tr tag="item" class='itemtemplate'><td>1</td><td>Ahmedabad</td></tr>
        <tr tag="item" class='alternatingitemtemplate'><td>2</td><td>Mumbai</td></tr>
        <tr tag="item" class='itemtemplate'><td>3</td><td>Delhi</td></tr>
        <tr tag="item" class='alternatingitemtemplate'><td>4</td><td>Calcutta</td></tr>
        <tr tag="item" class='itemtemplate'><td>5</td><td>Chicago</td></tr>
        <tr tag="item" class='alternatingitemtemplate'><td>6</td><td>New York</td></tr>
        <tr tag="item" class='itemtemplate'><td>7</td><td>New Jersy</td></tr>
        <tr tag="item" class='alternatingitemtemplate'><td>8</td><td>Bangalore</td></tr>
        <tr tag="item" class='itemtemplate'><td>9</td><td>Hyderabad</td></tr>
        <tr tag="item" class='alternatingitemtemplate'><td>10</td><td>Noida</td></tr>
        <tr tag="item" class='itemtemplate'><td>11</td><td>Mangalore</td></tr>
        <tr tag="item" class='alternatingitemtemplate'><td>12</td><td>Pune</td></tr>
        <tr tag="paging" class="pager">
            <td colspan="6">
                <a tag="pre" class="pre" href="javascript:void(0);"><< Prev</a>
                <a tag="next" class="next" href="javascript:void(0);">Next >></a>
            </td>
        </tr>
    </table>   
</body>
</html>

Display

Please let me know if this was helpful.

Tuesday, June 19, 2012

WCF Services : Simplest way to create a WCF REST(Representational State Transfer) service.

Goal

To create a WCF REST(Representational State Transfer) service. The main goal is to show it in the most simplest way.

Follow the below given steps

Open Visual Studio 2010.
File >> New Project >> WCF >> WCF Service Application. I named it SimpleWCFService.
I removed the default "IService1.cs" and "Service1.svc". Added a new "Sample.svc". This will automatically create an interface in file "ISample.cs". The structure will look like the image given below.

ISample.cs

#region System
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.ServiceModel.Web;
#endregion

namespace SimpleWCFService
{
    [ServiceContract]
    public interface ISample
    {
        /// <summary>
        /// The parameters are passed in URL expression.
        /// </summary>
        /// <param name="Param1"></param>
        /// <param name="Param2"></param>
        /// <returns></returns>
        [OperationContract]
        [WebGet(UriTemplate = "SampleCheck/{Param1}/{Param2}")]
        string SampleCheckUrl(string Param1, string Param2);

        /// <summary>
        /// The parameters are passed as QueryString.
        /// </summary>
        /// <param name="Param1">{Param1}</param>
        /// <param name="Param2">{Param2}</param>
        /// <returns></returns>
        [OperationContract]
        [WebGet(UriTemplate = "SampleCheck?Param1={Param1}&Param2={Param2}")]
        string SampleCheckQueryString(string Param1, string Param2);
    }
}

Sample.svc.cs

#region System
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Web.Script.Serialization;
#endregion

namespace SimpleWCFService
{

    /// <summary>
    /// Simple Class
    /// </summary>
    [DataContract]
    public class SimpleClass
    {
        [DataMember]
        public string CParam1 { get; set; }

        [DataMember]
        public string CParam2 { get; set; }

        [DataMember]
        public string CMessage { get; set; }
    }


    /// <summary>
    /// The simplest way to use a WCF REST(Representational State Transfer) service
    /// </summary>
    public class Sample : ISample
    {

        /// <summary>
        /// The parameters are passed in URL expression.
        /// </summary>
        /// <param name="Param1"></param>
        /// <param name="Param2"></param>
        /// <returns></returns>
        public string SampleCheckUrl(string Param1, string Param2)
        {
            JavaScriptSerializer _jScript = new JavaScriptSerializer();
            SimpleClass _simple = new SimpleClass()
            {
                CParam1 = Param1,
                CParam2 = Param2,
                CMessage = "Success with SampleCheckUrl(int Param1, string Param2)!"
            };
            return _jScript.Serialize(_simple);            
        }

        /// <summary>
        /// The parameters are passed as QueryString.
        /// </summary>
        /// <param name="Param1"></param>
        /// <param name="Param2"></param>
        /// <returns></returns>
        public string SampleCheckQueryString(string Param1, string Param2)
        {
            JavaScriptSerializer _jScript = new JavaScriptSerializer();
            SimpleClass _simple = new SimpleClass()
            {
                CParam1 = Param1,
                CParam2 = Param2,
                CMessage = "Success with SampleCheckQueryString(int Param1, string Param2)!"
            };
            return _jScript.Serialize(_simple);
        }
    }
}

Web.config - MOST IMPORTANT

<?xml version="1.0"?>
<configuration> 
  <system.web>
    <compilation debug="true" targetFramework="4.0" />
  </system.web>
  <system.serviceModel>
    <behaviors>
      <serviceBehaviors>
        <behavior name="SimpleWCFServiceBC">
          <serviceMetadata httpGetEnabled="true"/>
          <serviceDebug includeExceptionDetailInFaults="false"/>
        </behavior>
      </serviceBehaviors>
      <endpointBehaviors>
        <behavior name="restBehaviour">
          <webHttp automaticFormatSelectionEnabled="true" helpEnabled="true"/>
        </behavior>
      </endpointBehaviors>
    </behaviors>
    <bindings>
      <webHttpBinding>
        <binding name="RestBinding" />
      </webHttpBinding>
      <basicHttpBinding>

      </basicHttpBinding>
    </bindings>
    <services>
      <service name="SimpleWCFService.Sample" behaviorConfiguration="SimpleWCFServiceBC">
        <endpoint address="" binding="basicHttpBinding" contract="SimpleWCFService.ISample"></endpoint>
        <endpoint address="rest" binding="webHttpBinding" behaviorConfiguration="restBehaviour" bindingConfiguration="RestBinding" contract="SimpleWCFService.ISample"></endpoint>
        <endpoint contract="IMetadataExchange" binding="mexHttpBinding" address="mex" />
      </service>
    </services>
  </system.serviceModel>

  <system.webServer>
    <modules runAllManagedModulesForAllRequests="true"/>
  </system.webServer>

</configuration>

Checking REST Service - URL FORMAT

Navigate to the below given url(Note:Replace the port number.).
"http://{LOCALHOST:PORTNUMBER}/Sample.svc/rest/SampleCheck/Args1/Args2".
This should give the result in below given format.

<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">{"CParam1":"Args1","CParam2":"Args2","CMessage":"Success with SampleCheckUrl(int Param1, string Param2)!"}</string>

Checking REST Service - QueryString

Navigate to the below given url(Note:Replace the port number.).
"http://{LOCALHOST:PORTNUMBER/Sample.svc/rest/SampleCheck?Param1=Args1&Param2=Args2".
This should give the result in below given format.

<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">{"CParam1":"Args1","CParam2":"Args2","CMessage":"Success with SampleCheckQueryString(int Param1, string Param2)!"}</string>

I spent a lot of time trying to figure this out so writing this up for the ones who just want solutions. Let me know if this helped.

Friday, June 8, 2012

SQL Server : Using CTE(Common Table Expression) fetch a Tree View

Goal

Using CTE show a complete tree view.

Challenge

When a developer tries to search CTE in internet, there are millions of articles which already exists which says to get a tree view using CTE. But there is a problem with most of them. They dont give correct output. For making the data show in correct way users should enter data in an order, else it wont come. So first I will explain how the examples on internet mislead.

SQL - Create temp table

-- DECLARE
DECLARE @Company AS TABLE(EmpID INT, ParentID INT, PersonName VARCHAR(100));

-- Insert Temp Records
INSERT INTO @Company(EmpID, ParentID, PersonName) 
VALUES(1 , NULL , 'Maulik Dhorajia')
    , (2 , NULL , 'Bhavesh Gohel')
    , (3 , NULL , 'Dinesh Padhiyar')
    , (4 , 2 , 'Vijay Kumar')
    , (5 , 1 , 'Jitendra Makwana')
    , (6 , 4 , 'Jayesh Dhobi')
    , (7 , 1 , 'Shivpalsinh Jhala')
    , (8 , 5 , 'Amit Patel')
    , (9 , 3 , 'Abidali Suthar')

-- Default data

SELECT * FROM @Company;

SQL - Which usually misleads to get correct tree view

-- Incorrect result which we usually find on Internet
;WITH CTECompany
AS
(
    SELECT EmpID, ParentID, PersonName , 0 AS HLevel 
    FROM @Company
    WHERE ParentID IS NULL
    
    UNION ALL
    
    SELECT C.EmpID, C.ParentID, C.PersonName , (CTE.HLevel + 1) AS HLevel 
    FROM @Company C
    INNER JOIN CTECompany CTE ON CTE.EmpID = C.ParentID
    WHERE C.ParentID IS NOT NULL
)

-- Misleading SQL
SELECT * FROM
(
    SELECT 
        EmpID
        , ParentID
        , HLevel
        , (REPLICATE( '----' , HLevel ) + PersonName) AS Person
    FROM CTECompany
) AS P
ORDER BY HLevel;

SQL - Correct CTE with Tree View

-- Working Example
;WITH CTECompany
AS
(
    SELECT 
        EmpID, 
        ParentID, 
        PersonName , 
        0 AS HLevel,
        CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),EmpID),20) AS VARCHAR(MAX)) AS OrderByField
    FROM @Company
    WHERE ParentID IS NULL
    
    UNION ALL
    
    SELECT 
        C.EmpID, 
        C.ParentID, 
        C.PersonName , 
        (CTE.HLevel + 1) AS HLevel,
        CTE.OrderByField + CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),C.EmpID),20) AS VARCHAR(MAX)) AS OrderByField
    FROM @Company C
    INNER JOIN CTECompany CTE ON CTE.EmpID = C.ParentID
    WHERE C.ParentID IS NOT NULL
)

-- Working Example
SELECT 
    EmpID
    , ParentID
    , HLevel
    , PersonName
    , (REPLICATE( '----' , HLevel ) + PersonName) AS Person
FROM CTECompany
ORDER BY OrderByField,PersonName;

Hope this helped and saved a lot of your time!

Reply for the comment "Nice try. still relies on order of data entry. add (44 , 1 , 'XXitendra Makwana')". I tried to see what was wrong but I can see the correct tree with the same query. Not sure what is wrong. Can anyone else see the issue and post a comment? Thanks in advance.

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.