Friday 15 January 2016

Jquery Datatables Plugin - Tabular display of data

Server Side
----------------------    
 public class Result
        {
            public int articleid { get; set; }
            public string Name { get; set; }
            public string Position { get; set; }
            public string Office { get; set; }
            public string Extn { get; set; }
            public string Startdate { get; set; }
            public string Salary { get; set; }
        }

        [WebMethod]
        [ScriptMethod(UseHttpGet = true)]
        public static string GetJsonEmps()
        {
            List<Result> lsts = new List<Result>();
            for (int i = 1; i <= 10; i++)
            {
                lsts.Add(new Result() { articleid = i, Name = "AA" + i, Position = "home" + i, Office = "aas" + i, Extn = "extn" + i, Startdate = "date" + i, Salary = "sal" + i });
            }
            JavaScriptSerializer js = new JavaScriptSerializer();
            string json = js.Serialize(new { data = lsts });
            if (!string.IsNullOrEmpty(json))
            {
                return json;
            }
            return "";
        }

Client Side
-------------------

 <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" />
    <script src="Scripts/jquery-1.11.1.js" type="text/javascript"></script>
    <script src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js" type="text/javascript"></script>


 $(document).ready(function () {


            $("#btnSave").click(function (e) {              
                var table = $('#example').DataTable();
                e.preventDefault();
                $(".chkcls").each(function () {
                    if (this.checked) {                     
                        var $row = $(this).closest('tr');
                        // Get row data
                        var data = table.row($row).data();
                        // Get row ID
                        var rowId = data[0];
                        alert("row id is " + rowId);
                    }
                });
            });


            createtable();

        });

     function drawTable(arr) {
            var table = $('#example').DataTable({
                iDisplayLength: 5,
                responsive: true,
                processing: true,
                "aaData": arr,
                columns: [
            { title: "Select" },
            { title: "Name" },
            { title: "Position" },
            { title: "Office" },
            { title: "Extn" },
            { title: "Startdate" },
            { title: "Salary" }
           ],
                "aoColumnDefs": [{
                    "aTargets": [0],
                    //"mData": "download_link",
                    "mRender": function (data, type, full) {
                        //debugger;
                        if (isarticlesubmittable(data)) {
                            return '<input type=\"checkbox\" class=\"chkcls\" disabled="disabled" checked value="' + data + '">';
                        } else {
                            return '<input type=\"checkbox\" class=\"chkcls\" value="' + data + '">';
                        }
                    }
                }]
            });
        }

        function isarticlesubmittable(status) {
            return status == 1 || status == 3;
        }

 var datatable;
        function createtable() {
            $.ajax({
                type: "GET",
                url: "Jquery-datatable.aspx/GetJsonEmps",
                data: "{}",
                dataType: "json",
                contentType: "application/json; charset=utf-8",
                success: function (response) { drawtable(response); },
                error: function (xhr, ajaxOptions, thrownError) { alert(xhr.responseText); }

            });
        }
        function csave() {           
            datatable.destroy();
          
        }

        function drawtable(result) {
            var result = $.parseJSON(result.d);
            var arrayReturn = [];
            for (var i = 0, len = result.data.length; i < len; i++) {
                var res = result.data[i];
                arrayReturn.push([res.articleid, res.Name, res.Position, res.Office, res.Extn, res.Startdate, res.Salary]);
            }       

            drawTable(arrayReturn);          
        }

Final Output
-------------------


Publishing , Packaging & Deploying Provider Hosted App – Vol 2

This tutorial contains information about Packaging , Publishing & Deployment of Apps using Visual Studio 2012 or later.
Today we gonna discuss how to package our provider hosted application and publish it through App Catalog.The Publishing procedure contains two phase , in first we will publish the App Web Project and in second we’ll do it for all the App Projects.An App project is which is deployed/installed in SharePoint server(Online or On-Premise) pointing to a web application(Web App Project) hosted at some location(Infrastructure) provided by Organization i.e IIS Server or Windows Azure.


Some Prerequisites:
  • Microsoft SharePoint Server 2013 or SharePoint online(Office365).
  • Visual Studio 2012 with Office Developer Tools for VS2012 else VS2013(No need of additionally installing Developer Tools).

Setting the Scenario:

At this stage we are supposed to have Provider Hosted App working fine when deployed and Installed Directly using VS(Optional).This practically means we have ClientID, ClientSecret and IssurID in web.config of Web Project ,set and configured correctly to SharePoint.Refer this link to Know More.

Steps to Publish Provider Hosted App

It’s a Pre-Requirement from Microsoft that if our Provide hosted application has App Web Application i.e Web Project then we need to deploy it first after that we’ll publish our App Project.
Note:In order to deploy ClientID, ClientSecret and IssuerID to sharepoint along with Web Project we need to Publish(Register) it through SharePoint App Register Page.

Registering The App to SharePoint Server

you can open it for your site collection by appending “/_layouts/15/appregnew.aspx/” at last of URL.
For example: http://(server-name):(port-number)/_layouts/15/appregnew.aspx/

appregnew
On that page enter ClientId & ClientSecret in place of AppId and AppSecret Respectively.
Give tile of your app in Title text box and in App Domain enter the Server Name(Machine Name) already set as App domain in Central Admin where your App will be hosted.
Redirect URL is optional here. After all set up hit “Create”.
Next , we will move to Publish Our Web Project. Here are the steps:

Publishing App Web Project to SharePoint Server

    First of all in VS , select App Web Project say “DemoAppWeb”. Right click “DemoAppWeb” and click on “Publish”.It will open Publish Web Application Form.
  • Here the first step is to Choose/Import a Publish Profile or Create a new One.This Publish Profile basically specifies your Host Server for App Web.You can have more then one Profile based on your requirements.For now we are creating a new One.It will open create publishing wizard for profile.Enter the name for new Publish Profile.Hit next.
  • Now, For publish method we’ll choose Web Deployment Package.
  • Next give a path for package location where we gonna save this package file.you can store it anywhere say in “D:”drive.
  • After that give the site/application name where this Web Application going to be hosted in IIS Server.So give a path like this.
    http://(target-server-name):(port-number)/
    note:Save this “port-number” somewhere , as we need to create a site application in IIS Server with this same Port number which will act as Virtaul Directory for our App Web Project.
  • Then hit next tab.
  • Now comes the settings section , here we’ll choose “Release” as our option.Hit on next and Publish.This will create Some files along with a Zip File with name as“DemoAppWeb.zip”. Save location of this file.We’ll need it.

Hosting Web Project in IIS(local) Server

  • First of all unzip the “DemoAppWeb.zip” file it will create a folder with name “DemoAppWeb”. This Folder’s content will be used for the Virtual Directory we gonna create in Next Step.
  • Now browse to PackageTmp folder in the Extracted Zip Folder and copy its path.
  • Create a new App Pool in IIS Server using .Net Framework 4.0.
  • Now, we have to add a new Site in IIS Serer assign this App Pool created above to it and give it a port we have saved earlier for this purpose. I can remember we have giver 1234 as port number.:)
  • Click on Create website and it will create a new website under sites folder in IIS.
  • Open Authentications Settings for this website and Enable Windows Authentication and Disable Anonymous Authentication.
  • We have to give Read & Write Permission on this folder (PackageTmp) for “Everyone” and “IIS_IUSRS”Users.Find “IIS_IUSRS” by selecting the Server as location while adding the permission group.

Publishing App Project to be deployed via App Catalog

Now, the last step in Publishing is to publish and create package file for App Project. Follow below steps for this:
  • In VS Solution, right click on App Project i.e “DemoApp” in this example and select publish.
  • In Profile section, select an old profile or create a new one.We’ll go for new profile here.
  • Then it’ll ask for host location where this is gonna be hosted. Here give the URL of the App Web hosted on the IIS in “Hosting Web Project in IIS(local) Server” section above. i.e
    https://(target-server-name):1234/
    Note:For the https above we need to add https binding under Site Bindings with default port number for it.
    Or For you can put https:// for meanwhile just for publishing app. and later you can modify it back to https by editing .app file.This will work. HTTPS will not be supported by many browsers or ask for allowing content to render data,so better you make it HTTP only.
  • Provide ClientIDCertificatePath,CertificatePassword and IssuerID used in Web.config under App settings tag.
    webconfig
  • Now Click on Next and finish it Off.
  • After publishing successfully it will open the publish folder , there we can find “DemoApp.app” file very easily.This is our App file.

Deploying Provider Hosted App using App Catalog to SharePoint

Now, we need to just deploy this App to SharePoint Site Collection using App catalog.

Follow below Steps for Deployment.
  • In the App Catalog Site created in Target Web Application, add new app to the “Apps for SharePoint” list and browse to the .app file we got after publishing. Upload this .app file and fill the details form.
  • After this .app file is successfully added to the list of apps.Just select this app item and click on View Details page.
  • Add this App to the App Catalog.This will get added and listed in All Site Contents.
  • Next you need to Deploy it to the desired Site Collection under this Web Application.
  • At last consume this app from your target site collection and you are done.

HOW TO CREATE PROVIDER HOSTED APP IN SHAREPOINT 2013

This tutorial will cover creation of Provider Hosted App in SharePoint 2013 On-Premise using Visual Studio 2012. We’ll discuss complete Configuration and settings required to create a provider hosted app with a step by step approach.Also In a Continuing Article we’ll share procedure to Publish Your App in Front-End Server(Client Side) with Some Trouble Shooting tips.SharePoint App Model has become popular among developers , you can realize this by seeing progressive graph of number of Apps in SharePoint App Store.

Microsoft Introduces Apps Model in its SharePoint 2013 Model having “Everything is an App” Policy.We are provided with three Kind of Apps:
  • SharePoint Hosted Apps
  • Provider Hosted Apps
  • Auto Hosted Apps
To know More About all these Apps Refer this Link.
We’ll go with Provider Hosted App in this article.
Prerequisites
You need to set your SharePoint Application Server with these required Software’s:
1.Windows Server 2008 R2 OS -64 Bit/Windows 7 and other Variant.
2.Microsoft Sharepoint 2013 Server – Enterprise Edition(For Enhanced Functionality).
3.Visual Studio 2012- Professional/Ultimate.
4.Microsoft Office Developer Tools for Visual Studio 2012 (RTM).
5.Microsoft Web Deploy – v3.0 or higher(Required for Publishing Apps).
6.Microsoft Exchange Web Services-EWS Managed API 2.0 or Latest.
Some Server Level Settings:
1.Check below mentioned Services are up and running on Server
Central Admin -> System Settings -> Manage Services on Server.
(1) App Management Service.
(2) Claims to Windows Token Service.
(3) Microsoft SharePoint Foundation Subscription Settings Service.
(4) Request Management Service.
(5) User Profile Synchronization Service.
2.Features to be activated at Web Application Level
Central Admin ->Application Management -> Manage Web Application Features (in ribbon control)
Apps that require accessible internet facing endpoints (For SharePoint Store- optional).
Open Visual Studio “Run As Administrator”. -> New Project -> Find Apps for SharePoint 2013 Under Office/SharePoint Template Group.
SharePoint Solution
Note:
(1)If you are facing Error “An error occurred whilst trying to load some required components, Please ensure the following prerequisite components are installed…” . referThis Link to resolve this.
(2)Also you need to Run Visual Studio with System Admin Account also Farm Admin for Sharepoint Server. But this account should not be used while configuring and installing SharePoint Farm otherwise you will face Error.You won’t be able to deploy it.Same issue has been Listed On MSDN Forum here…, To resolve this you can create a new Account in Active Directory , Add it in System Administrators Group and also add it as Farm Admin. Run using this account.

  • In Next Page VS will ask for name for the App, Debugging location (Site collection URL where you want to deploy) and Type of hosting.Choose Provider Hosted from the drop down list and click next.
  • App Hosting Type
    Next you need to give Authentication Settings , you can see two options in RadioButton, First is using ACS(Low-Trust Apps) and second is using a Certificate(High-Trust Apps). Choose second option Use a Certificate. Insert Certificate location, password and Issuer ID. How to Create OAuth Certificate In SharePoint 2013 For High-Trust Apps.Also that certificate has to be registered under SPTrustedSecurityTokenIssuer.Lean how to Register Here.

    OAuthCertificate
    • You’ll see that the New Solution for Provider Hosted App is Ready.The Solution will contain two projects one App and one Web. In Web Project we have TokenHelper.cs Class it contains Methods to setup trust across Server and authenticate client for accessing this app.Solution Structure
    • Now Select Web Project & Right Click->Properties.Under Web tag you can use IIS Express or custom IIS Web Server.For IIS Server in Project URL you will have like http://localhost/{Project-Name} for Port:80. Click on Create Virtual Directory button , it will create a virtual directory for your app in IIS Server under Site SharePoint -80 having Port 80.You can create under any other site also changing the Port Number.Virtual Directory
    • Now in App Project Double click on AppManifest.xml file in App Project. And go under Permissions Tab there add Scope to Web, Site Collection, Lists, Taxonomy, User Profiles and other fields depending upon your Requirement.These are permissions you gonna give to your App or user depending upon the App policy You chose.Permissions
      After that right click on AppManifest.xml and choose view Code. You will find all permissions given above inside tag. And put AllowAppOnlyPolicy attribute set to true for App Only App policy i.e App having Elevated Privileges in one way.
    • Now we need one ClientId for our app registered as an App Principle for SharePoint.Learn how to register App Principal here.
      Registering an App Principal
      For each App we’ll need a Client Id registered with Sharepoint. We need to register it before deploying our app.Client Id is just a GUID with all letters in small case. We can generate a GUID for this via following Page. URL will be like
      http://Root-url/_Layouts/15/appregnew.aspx
      We’ll use above App Id as our Client Id and register it with Sharepoint. We have PowerShell script for this, shown below:

      cls
      Add-PSSnapin "Microsoft.SharePoint.PowerShell"
      # set intialization values for new app principal
      $appDisplayName = "(Identifier-name)"
      $clientID = "(Generated-ClientId-Above)"
      $targetSiteUrl = "(Site-URL)"
      $targetSite = Get-SPSite $targetSiteUrl
      $realm = Get-SPAuthenticationRealm -ServiceContext $targetSite
      $fullAppPrincipalIdentifier = $clientID + '@' + $realm
      Write-Host "Registering new app principal"
      $registeredAppPrincipal = Register-SPAppPrincipal -NameIdentifier $fullAppPrincipalIdentifier -Site $targetSite.RootWeb -DisplayName $AppDisplayName
      $registeredAppPrincipal | select * | Format-List
      $registeredAppPrincipal | select * | Format-List | Out-File -FilePath "Output.txt"
      Write-Host "Registration Completed"
      #Get-SpAppPrincipal -?
      Note: Above mentioned values in (), has to be chnaged according to your setup, where:
      (Identifier-name):- Name of App Identifier you need to use for Registering App Principal.
      (Generated-ClientId-Above):- ClientID generated above from App registration Page.
      (Site-URL):-Target Site Collection URL.
    • Now this ClientID we need to replace in Two Places. One in Web.Config under Web Project you will find all values given previously for Cerificare in tag. Put Value for ClientID that we just registered & other in Appmanifest.xml file under App Project.
    • That’s It.Now just press F5 in debug mode.It will go through a series of Steps and end with Deployment.After deployment it will open App Principal Trust Page.As the app is going to access resources, so it’ll ask you if you trust this app or not.This page will come once only for the first time after deployment, until you retract it again.Trust App Principal
    • Trust it, it will redirect to Default.aspx(Default aspx web form) page deployed locally.The Url will be like
      http://localhost/{Project-Name}/Pages/Default.aspx?{StandardTokans}
      This URL is set in AppManifest.xml under:
      ~remoteAppUrl/Pages/Default.aspx?{StandardTokens}
      appmanifest

Saturday 7 February 2015

html multilevel ul li menu

c# code to create necessary html on run time 
 private void CreateMenu()
        {
            string conString = ConfigurationManager.ConnectionStrings["Yourconnection"].ToString();

            using (SqlConnection myConnection = new SqlConnection(conString))
            {
                string query = "select * from tbl_Menu";
                SqlCommand oCmd = new SqlCommand(query, myConnection);
                myConnection.Open();
                using (SqlDataReader oReader = oCmd.ExecuteReader())
                {
                    while (oReader.Read())
                    {
                        MenuEntity menuObj = new MenuEntity();
                        menuObj.ID = Convert.ToInt32(oReader["ID"].ToString());
                        menuObj.Text = oReader["MenuText"].ToString();
                        menuObj.URL = oReader["MenuUrl"].ToString();
                        if (!oReader.IsDBNull(oReader.GetOrdinal("ParentID")))
                        {
                            menuObj.PID = (int)oReader["ParentID"];
                        }
                        else
                        {
                            menuObj.PID = 0;
                        }
                        menucol.Add(menuObj);
                    }
                    myConnection.Close();
                }

            }
            StringBuilder sb = new StringBuilder();
            sb.Append("
    ");
            foreach (var s in menucol)
            {
                if (s.PID == 0)
                {
                    sb.Append("


  • " + s.Text + "");

  •                     sb.Append(this.getChildren(s.ID));
                        sb.Append("
    ");                }
                }
                sb.Append("
    ");            listContainer.InnerHtml = sb.ToString();
            }

            private string getChildren(int parenid)
            {
                StringBuilder sb = new StringBuilder();
                var childmenus = from m in menucol
                                 where m.PID == parenid
                                 orderby m.Text
                                 select m;
                if (childmenus.Count() > 0)
                {
                    sb.Append("
      ");
                    foreach (var s in childmenus)
                    {
                        sb.Append("


  • " + s.Text + "");

  •                     sb.Append(getChildren(s.ID));
                        sb.Append("
    ");                }
                    sb.Append("
    ");            }
                return sb.ToString();
            }

    To render menu and have a toggle effect:
     <script>
            $(document).ready(function () {
                $('#listContainer li:has(ul)').addClass("has-sub");
                $('#listContainer').find('.has-sub').find('a:first').addClass("sub");
                $('#listContainer ul ul').addClass("sub-menu");
                $(".sub-menu").hide();
                // $('#listContainer').find('li:has(ul)').addClass('collapsed');
                $('#listContainer').find('li:not(:has(ul))').find('img').remove();           
                $('#listContainer').find('li:has(ul)').find('img').attr('src', 'down.png'); 
                $('#listContainer li img').click(function (e) {
                   
                    var checkElement = $(this).siblings('ul');  

                    if ($(this).closest('li').siblings('li:has(ul)').not(':visible')) {
                        $(this).closest('li').siblings('li:has(ul)').find('img').removeAttr('src');
                        $(this).closest('li').siblings('li:has(ul)').find('img').attr('src', 'down.png');
                    }

                    $.each($(this).closest('li').children('ul').find('li'), function (i, v) {

                        if ($(this).find('img').attr('src') == 'up.jpg') {
                            $(this).find('img').removeAttr('src');
                            $(this).find('img').attr('src', 'down.png');
                            $(this).find('ul').slideUp();

                        }
                    });


                    if ($(this).closest('li').siblings('li:has(ul)').find('ul>li:has(ul)').find('img').attr('src') == 'up.jpg') {
                        $(this).closest('li').siblings('li:has(ul)').find('ul>li:has(ul)').find('img').removeAttr('src');
                        $(this).closest('li').siblings('li:has(ul)').find('ul>li:has(ul)').find('img').attr('src', 'down.png');
                        $(this).closest('li').siblings('li:has(ul)').find('ul').slideUp('normal');

                    }
                    if ((checkElement.is('ul')) && (checkElement.is(':visible'))) {


                        $(this).closest('img').removeAttr('src');      //new code
                        $(this).closest('img').attr('src', 'down.png');   //new code

                        if ($(this).closest('li').find('ul>li:has(ul)').find('img').attr('src') == 'up.jpg') {
                            $(this).closest('li').find('ul>li:has(ul)').find('img').removeAttr('src');
                            $(this).closest('li').find('ul>li:has(ul)').find('img').attr('src', 'down.png');
                            $(this).closest('li').find('ul').slideUp('normal');

                        }
                        if ($(this).closest('li').siblings('li:has(ul)').children('ul').is(':visible')) {
                            $(this).closest('li').siblings('li:has(ul)').children('ul').slideUp('normal');
                            $(this).closest('li').siblings('li:has(ul)').find('ul.li').find('img').removeAttr('src');
                            $(this).closest('li').siblings('li:has(ul)').find('ul.li').find('img').attr('src', 'down.png');

                        }
                        checkElement.slideUp('normal');

                    }

                    if ((checkElement.is('ul')) && (!checkElement.is(':visible'))) {

                        $(this).closest('img').removeAttr('src');      //new code
                        $(this).closest('img').attr('src', 'up.jpg');   //new code
                        $('#listContainer ul:visible').not(checkElement.parentsUntil('#listContainer')).slideUp('normal');
                        checkElement.slideDown('normal');

                    }

                });
            });
        </script>
     

    Menu will look like:




    Friday 25 April 2014

    Custom approve reject with nintex workflow webservice

    This blog post will demonstrate how nintex workflow task can be assigned through custom code and how approve or reject call interacts with nintex through c#.

    First we need to add a web reference to our project.

    http://ServerName/_vti_bin/NintexWorkflow/Workflow.asmx

    Lets name it NintexApprovalService .

    Now on approve button click write the following code:

      try
                {
                    string url = string.Empty;

                    ProcessTaskResponseResult? result = null;
                    url = service.GetItemURLBySomeCondition(your parameter here); //  write a function which will return web.url + "/"+ splistitem.url based on condition.
                   NintexApprovalService.NintexWorkflowWS objNintextClient = new NintexApprovalService.NintexWorkflowWS();
                   objNintextClient.UseDefaultCredentials = true;
                   NintexApprovalService.UserTask[] userTask =                     objNintextClient.GetRunningWorkflowTasksForCurrentUser(url);    //This webmethod will give array of assigned tasks.

                  foreach (NintexApprovalService.UserTask objTask in userTask)
                        {
                            int taskID = objTask.SharePointTaskId;
                            result = CallProcessTaskResponse(taskID);
                        } 


                 }
    catch(Exception e)
    {
    Logger.write(e.message + "--" + e.stackTrace);
    }



     private NintexApprovalService.ProcessTaskResponseResult CallProcessTaskResponse(int TaskId)
     {

        NintexApprovalService.ProcessTaskResponseResult? obj= null;
        string taskList = "Workflow Tasks";
               {
                                   NintexApprovalService.NintexWorkflowWS obj = new
    NintexApprovalService.NintexWorkflowWS();
                    obj.UseDefaultCredentials = true;             
                    ob = obj.ProcessFlexiTaskResponse2(approvalComments, "Approve", TaskId,
    taskList);
                }
                return ob.Value;

      }


    Likely we can write a function for rejection case which will be called in reject button click.
    Just we need to pass Reject in second parameter to ProcessFlexiTaskResponse2() method.

    We can also check whether task is already assigned by this way:

     NintexApprovalService.UserTask[] userTask = objNintextClient.GetRunningWorkflowTasksForCurrentUser(url);
                            if (userTask != null && userTask.Length > 0)
                            {
                               //Write logic accordingly.
                            }


    Friday 9 August 2013

    How to perform sorting on Gridview bound to generic list collection

    Let's say we have a list or collection based on some entity class and the underlying data is bound to the grid.

    Following code performs sorting operation on such kind of grid whose data source is collection.

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                 CellPadding="4" 
                EnableModelValidation="True" ForeColor="#333333" GridLines="None" 
                AllowSorting="True" 
                onsorting="GridView1_Sorting">
                <AlternatingRowStyle BackColor="White" />
                <EmptyDataTemplate><h2>No Data Found</h2></EmptyDataTemplate>
                <Columns>           
                    <asp:TemplateField HeaderText="ProductId" SortExpression="ProductId">
                        <ItemTemplate>
                            <asp:Label ID="lblEmpNo" runat="server" Text='<%# Eval("ProductId") %>' />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="ProducName" SortExpression="ProductName">
                        <ItemTemplate>
                            <asp:Label ID="lblEmpNo" runat="server" Text='<%# Eval("ProductName") %>' />
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
                <EditRowStyle BackColor="#2461BF" />
                <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#EFF3FB" />
                <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            </asp:GridView>

    Now create a function like this which returns list of products.
     private List<Products> GetProducts()    //Products is class with two properties ProductId & ProductName
            {
                List<Products> lst = new List<Products>();
                lst.Add(new Products { ProductId = 11, ProductName = "Samsung" });
                lst.Add(new Products { ProductId = 12, ProductName = "nokia" });
                lst.Add(new Products { ProductId = 14, ProductName = "Whirepool" });
                lst.Add(new Products { ProductId = 22, ProductName = "pupil" });
                lst.Add(new Products { ProductId = 31, ProductName = "softtech" });
                return lst;

            }

     protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
            {
                List<Products> lstcoll = GetProducts();
                lstcoll.Sort(new GenericComparer<Products>(e.SortExpression, GridViewSortDirection));
                GridView1.DataSource = lstcoll;
                GridView1.DataBind();
            }
            public SortDirection GridViewSortDirection
            {
                get
                {                            
                    if (ViewState["sortDirection"] == null)
                        ViewState["sortDirection"] = SortDirection.Ascending;
                    else
                    {
                        if (((SortDirection)ViewState["sortDirection"]) == SortDirection.Ascending)
                        {
                            ViewState["sortDirection"] = SortDirection.Descending;
                        }
                        else
                        {
                            ViewState["sortDirection"] = SortDirection.Ascending;
                        }
                    }
                    return (SortDirection)ViewState["sortDirection"];
                }
                set
                {
                    ViewState["sortDirection"] = value;
                }
            }

    Now implement the GenericComparer where constructor takes sortexpression and sortdirection inputs.

    using System;
    using System.Data;
    using System.Configuration;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    using System.Collections;
    using System.Collections.Generic;
    using System.Reflection;


    public class GenericComparer<T> : IComparer<T>
    {
        private SortDirection sortDirection;
        public SortDirection SortDirection
        {
            get { return this.sortDirection; }
            set { this.sortDirection = value; }
        }

        private string sortExpression;
        public GenericComparer(string sortExpression, SortDirection sortDirection)
        {
            this.sortExpression = sortExpression;
            this.sortDirection = sortDirection;
        }

        public int Compare(T x, T y)
        {
            PropertyInfo propertyInfo = typeof(T).GetProperty(sortExpression);
            IComparable obj1 = (IComparable)propertyInfo.GetValue(x, null);
            IComparable obj2 = (IComparable)propertyInfo.GetValue(y, null);
            if (SortDirection == SortDirection.Ascending)
            {
                return obj1.CompareTo(obj2);
            }
            else return obj2.CompareTo(obj1);
        }


    }

    Monday 3 June 2013

    SQL Helper class for different Database Operations

    How we pass or add parameters in SQLCommand class?
    For beginners it is like:

    SqlCommand cmd=new SqlCommand("SQL Query",con);

    But with dictionary collection it can be done very easily where we can keep logic in separate layer.Here is an example.

    in code behind let's declare a dictionary.
    Dictionary<string, object> parameters = new Dictionary<string, object>();
    Add parameters
    parameters.Add("@name",txtName.Text);
    dal.ExecNonQuery("insertrecord", parameters);

    Here 'insertrecord' is stored procedure name and we pass parameter as object.

    Now Let's have the implementation of this ExecNonQuery method.

     public void ExecNonQuery(string spName, Dictionary<string, object> values)
            {
                try
                {

                    SqlCommand cmd = new SqlCommand(spName, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = spName;

                    foreach (string item in values.Keys)
                    {
                        SqlParameter param = new SqlParameter(item, values[item]);
                        cmd.Parameters.Add(param);
                    }
                    cmd.ExecuteNonQuery();
                }

                catch (Exception ex)
                {
                    throw new Exception();
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

    Now let's have the implementation of SqlDataReader also.

     public SqlDataReader ExecSPDataReader(string spName, Dictionary<string, object> values)
            {
                SqlConnection conn = null;
                SqlDataReader dr = null;
                try
                {
                   con = new SqlConnection("Your connection string");
                   con.Open();
                   SqlCommand cmd = new SqlCommand(spName, con);
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.CommandText = spName;
                   foreach (string item in values.Keys)
                    {
                        SqlParameter param = new SqlParameter(item, values[item]);
                        cmd.Parameters.Add(param);
                    }
                    dr = cmd.ExecuteReader();
                }
                catch 
                {              

                }
                finally
                {             
                    con.Close();
                    con.Dispose();
                }
                return dr;

            }

    Now similarly we can define ExecuteScalar also

    public object ExecSPScalar(string spName, Dictionary<string, object> values)
            {

                object returnVal= null;
                try
                {
                    conn = ConfigurationManager.AppSettings["Your DB Connection"].ToString();
                    con = new SqlConnection(conn);
                    con.Open();
                    SqlCommand cmd = new SqlCommand(spName, con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = spName;
                    foreach (string item in values.Keys)
                    {
                        SqlParameter param = new SqlParameter(item, values[item]);
                        cmd.Parameters.Add(param);
                    }
                    returnVal= cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    throw new Exception();
                }

                finally
                {
                    con.Close();
                    con.Dispose();
                }
                return returnVal;

            }

    Now how to call it from  code behind?

      Dictionary<string, object> parameters = new Dictionary<string, object>();
      parameters.Add("@username",txtUserName.Text);
      parameters.Add("@password",txtPassword.Text);
      object value = dal.ExecSPScalar("SPName", parameters);
                    if ((int)value== 1)
                    {
                        Session["User"] = txtUserName.Text;
                        Response.Redirect("Welcome.aspx");

                    }

    Hope this helps.