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::
<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> </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;
<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("");
}
}
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> </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
Post a Comment