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

How to add custom filter lookup field based on OptionSet field selection in Dynamics CRM.

go to top image