How to fetch data from Microsoft Dynamics CRM OnPremise and show them on Gridview with Search, Select, Paging and Sorting feature in Asp.Net, C#

In this blog I am going to show how we can fetch data from Dynamics CRM OnPremise and Show them in a gridview on Web Page. Not only this but also following points:

Selecting value of row by clicking anywhere on a row.
Searching data in Gridview.
Sorting of data in Gridview.
Paging in Gridview.

First of all take a Textbox for serach box and Gridview on your web page and place below code::

 <form id="form1" runat="server">  

     <script type="text/javascript">
       function RefreshParent(row) {
         if (window.opener != null && !window.opener.closed) {
             var message = "";
             message += "Id: " + $("td", row).eq(0).html();
             message += "\nName: " + $("td", row).eq(1).html();
             alert(message);
           window.opener.location.reload();
         }
       }
       window.onbeforeunload = RefreshParent;
     </script>
     <div>
       <div>
         Search:
     <asp:TextBox ID="txtSearch" runat="server" />
         <asp:Button Text="Search Account " runat="server" OnClick="Search" /><div>&nbsp;</div>
       </div>
     <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
     <script type="text/javascript">
       $(function () {
         $("[id*=gvDetails] td").click(function () {
           RefreshParent($(this).closest("tr"));
         });
       });
     </script>
     <asp:GridView ID="gvDetails" runat="server" OnRowDataBound="gvDetails_RowDataBound" OnSorting="gvItemGrid_Sorting" AllowPaging="True"
       OnPageIndexChanging="gvDetails_PageIndexChanging" PageSize="3"
        DataKeyNames="name" AllowSorting="True" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3">
       <FooterStyle BackColor="White" ForeColor="#000066" />
       <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
       <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
       <RowStyle ForeColor="#000066" />
       <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="#660033" />
       <SortedAscendingCellStyle BackColor="#F1F1F1" />
       <SortedAscendingHeaderStyle BackColor="#007DBB" />
       <SortedDescendingCellStyle BackColor="#CAC9C9" />
       <SortedDescendingHeaderStyle BackColor="#00547E" />
     </asp:GridView>
     </div>
   </form>

Now add below code in code window::

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;
using System;
using System.Data;
using System.Net;
using System.ServiceModel.Description;
using System.Text.RegularExpressions;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page
{
    DataView m_dataview = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ViewState["SORT"] = "ASC";
        }
        this.FillGrid("");

    }

    private void FillGrid(string sortExpr)
    {
        ViewState["SORTExp"] = sortExpr;
        m_dataview = GetData(sortExpr);
        gvDetails.DataSource = m_dataview;
        gvDetails.DataBind();
    }

    private DataView GetData(string sortExpr)
    {
        //Authenticate using credentials of the logged in user;    
        ClientCredentials Credentials = new ClientCredentials();
        System.Net.NetworkCredential network = new System.Net.NetworkCredential
                                                {
                                                    UserName = "UserName",
                                                    Password = "Password",
                                                    Domain = "DomainName"
                                                };
        Credentials.Windows.ClientCredential = network;
        Credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;

        //This URL needs to be updated to match the servername and Organization for the environment.
//You may get it here, Goto:: Settings -> Customization -> Developer resources -> Service Root Url
        Uri OrganizationUri = new Uri("http://DomainName/OrgName/XRMServices/2011/Organization.svc");
        Uri HomeRealmUri = null;

        OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri, Credentials, null);

        IOrganizationService _orgService = (IOrganizationService)serviceProxy;
        serviceProxy.Authenticate();

        IOrganizationService service = (IOrganizationService)serviceProxy;
        DataTable dtLead = null;

       
    string strXml = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>  

                 <entity name='account'>
                 <attribute name='name' />
                 <attribute name='accountnumber' />
                 <attribute name='accountid' />
                 <link-entity name='systemuser' to='owninguser' alias='mainuser'>
                 <attribute name='fullname' />
                 </link-entity >
                   <filter type='and' >
                   <condition attribute='name' operator='like' value='%" + txtSearch.Text.Trim() + @"%' />
                 </filter>
                 </entity>
                 </fetch>";

        dtLead = ProjectCommon.QueryByFetchXml(service.RetrieveMultiple(new FetchExpression(strXml)));
        m_dataview = new DataView(dtLead);
        if (sortExpr != "")
        {
            m_dataview.Sort = sortExpr;
        }
        dtLead.Dispose();
        return m_dataview;
    }

    //For Search
    protected void Search(object sender, EventArgs e)
    {
        this.FillGrid("");
    }

    protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[0].Text = Regex.Replace(e.Row.Cells[0].Text, txtSearch.Text.Trim(), delegate(Match match)
            {
                return string.Format("{0}", match.Value);
            }, RegexOptions.IgnoreCase);
        }
    }

    //For Selecting row on clicking anywhere in the row
    protected void gvDetails_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Attributes["onmouseover"] = "this.style.backgroundColor='aquamarine';";
            e.Row.Attributes["onmouseout"] = "this.style.backgroundColor='white';";
            e.Row.Attributes["onclick"] = Page.ClientScript.GetPostBackClientHyperlink(gvDetails, "Select$" + e.Row.RowIndex);
            e.Row.ToolTip = "Click to select this row.";
        }
    }

    //For Sorting
    protected void gvItemGrid_Sorting(object sender, System.Web.UI.WebControls.GridViewSortEventArgs e)
    {
        string expr = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection);

        if (expr != "")
        {
            m_dataview.Sort = expr;
        }
        gvDetails.DataSource = m_dataview;
        gvDetails.DataBind();
    }

    private string ConvertSortDirectionToSql(SortDirection sortDirection)
    {
        string newSortDirection = String.Empty;
        if (ViewState["SORT"].ToString() == "ASC")
        {
            newSortDirection = "ASC";
            ViewState["SORT"] = "DESC";
        }
        else
        {
            newSortDirection = "DESC";
            ViewState["SORT"] = "ASC";
        }
        return newSortDirection;
    }

    //For Paging
    protected void gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        string expr = ViewState["SORTExp"].ToString();
        gvDetails.DataSource = m_dataview;
        gvDetails.PageIndex = e.NewPageIndex;
        gvDetails.DataBind();
    }

    protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvDetails.PageIndex = e.NewPageIndex;
        this.FillGrid("");
    }
}



Comments

Popular posts from this blog

How to show enlarge image when mouse hover on image or link in Asp.Net(c#) using JavaScript

StateCode and StatusCode Values for mostly used entities in Microsoft Dynamics CRM 2013

Search data in Gridview on Textbox Key press event using JQuery in Asp.Net- C#

go to top image