Creation of SPGridView Webpart with Sorting, Filtering and Pagination OOTB

Hi,

This post will simply explain how to use SPGridView in SharePoint 2010 VisualWebpart with sorting, filtering and paging functionality without writing much code, mostly using OOTB features,

This includes below feature

  • SPGridView Webpart
  • Pagination
  • Sorting
  • Filtering
  • Export to Ecel

How it looks:

1. Screen when page load:

Screen when page load

2. Screen when filter:

Screen when filter

3. Item per page:

Item per page

See the below code how to implement the above screen with ootb

1. CSS in user control

<style type=”text/css”>
.pagination
{
font-size: 80%;
}

.pagination a
{
text-decoration: none;
color: #15B;
}

.pagination a, .pagination span
{
font-family: Sans-Serif;
font-size: 11px;
border-bottom: #ccc 1px solid;
border-left: #ccc 1px solid;
padding-bottom: 3px;
margin: 1px;
padding-left: 4px;
padding-right: 4px;
display: inline-block;
border-top: #ccc 1px solid;
font-weight: bold;
border-right: #ccc 1px solid;
padding-top: 3px;
-moz-border-radius: 3px;
-webkit-border-radius: 3px;
}
.pagination .current
{
background: #26B;
color: #fff;
border: solid 1px #AAE;
}
.lblWTHeading
{
font-family: Arial;
font-size: 18px;
font-weight: bold;
color: White;
}
.textAlignLeft
{
text-align: left;
}
</style>

2. SPGridView  and ObjectDataSource in user control

<table>
<tr>
<td align=”right” style=”border: 1px ridge #C0C0C0″>
<table cellpadding=”3″ cellspacing=”3″>
<tr>
<td>
<asp:Label ID=”Label2″ runat=”server” Text=”Export To:” Font-Size=”13px” Font-Names=”Arial”></asp:Label>
</td>
<td>
<asp:ImageButton ID=”imgExcel” runat=”server” ImageUrl=”~/_layouts/images/ExportToExcel.png”
OnClick=”imgExcel_Click” ToolTip=”Export to Excel” Height=”22px” Width=”22px” />
</td>
<td>
<asp:Label ID=”Label1″ runat=”server” Text=”Item per page:” Font-Size=”13px” Font-Names=”Arial”></asp:Label>
</td>
<td>
<asp:DropDownList ID=”DropDownList1″ runat=”server” AutoPostBack=”True” OnSelectedIndexChanged=”DropDownList1_SelectedIndexChanged”>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>50</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style=”border: 1px ridge #C0C0C0″>
<SharePoint:SPGridView ID=”oGrid” runat=”server” AutoGenerateColumns=”false” AllowSorting=”True”
DataSourceID=”myDataSource” FilterDataFields=”Title,Status” AllowFiltering=”true”
AllowPaging=”True” FilteredDataSourcePropertyName=”FilterExpression” FilteredDataSourcePropertyFormat=”{1} like ‘{0}'”
Font-Names=”Arial” Font-Size=”13px”>
<Columns>
<SharePoint:SPBoundField DataField=”Title” HeaderText=”Title” SortExpression=”Title”>
<ControlStyle Width=”220px” />
</SharePoint:SPBoundField>
<SharePoint:SPBoundField DataField=”Status” HeaderText=”Status” SortExpression=”Status”>
<ControlStyle Width=”100px” />
</SharePoint:SPBoundField>
<asp:TemplateField HeaderText=”Get Candidates”>
<ItemTemplate>
<asp:LinkButton ID=”lnkBtnGetCandidates” runat=”server” Text=”Get Candidates” CausesValidation=”False”
CommandName=”Select” CommandArgument='<%#Eval(“Description”)%>’></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<PagerStyle CssClass=”pagination” HorizontalAlign=”Center” VerticalAlign=”Middle”
Font-Size=”14pt” Wrap=”True” BackColor=”White” />
<RowStyle HorizontalAlign=”Left” />
</SharePoint:SPGridView>
<asp:ObjectDataSource ID=”myDataSource” runat=”server” TypeName=”SPGridview.VisualWebPart1.DataTableWrapper, $SharePoint.Project.AssemblyFullName$”
SelectMethod=”GetTable”></asp:ObjectDataSource>
</td>
</tr>
</table>

3. Code in Visual web part

private DataTable sourceDataTable;
private DataTableWrapper myDataTable;
private string[] _ssep = { “AND” };
private char[] _sep = { ‘,’ };

protected override void CreateChildControls()
{
string js = @”_spSuppressFormOnSubmitWrapper = true;”;
this.Page.ClientScript.RegisterStartupScript(this.GetType(), “js”, js, true);

myDataSource.ObjectCreating += new ObjectDataSourceObjectEventHandler(ds_ObjectCreating);
#region “Pagination”

oGrid.PageSize = Convert.ToInt16(DropDownList1.SelectedValue);
oGrid.RowDataBound += new GridViewRowEventHandler(oGrid_RowDataBound);

//Default Pagination
oGrid.PageIndexChanging += new GridViewPageEventHandler(oGrid_PageIndexChanging);
oGrid.PagerTemplate = null;
#endregion
base.CreateChildControls();

}

void oGrid_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
oGrid.PageIndex = e.NewPageIndex;
oGrid.DataBind();
}

private void oGrid_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (sender == null || e.Row.RowType != DataControlRowType.Header)
{ return; }

SPGridView grid = sender as SPGridView;

// Show icon on filtered and sorted columns
for (int i = 0; i < grid.Columns.Count; i++)
{
DataControlField field = grid.Columns[i];

if (((Microsoft.SharePoint.WebControls.SPGridView)(sender)).FilterFieldName.Contains(field.SortExpression))
{
try
{
PlaceHolder panel = HeaderImages(field, “/_layouts/images/filter.gif”);
e.Row.Cells[i].Controls[0].Controls.Add(panel);
break;
}
catch (Exception)
{ }
}

}
}

void ds_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
{

myDataTable = new DataTableWrapper(sourceDataTable);
e.ObjectInstance = myDataTable;

}

private PlaceHolder HeaderImages(DataControlField field, string imageUrl)
{
Image filterIcon = new Image();
filterIcon.ImageUrl = imageUrl;
filterIcon.Style[HtmlTextWriterStyle.MarginLeft] = “2px”;

Literal headerText = new Literal();
headerText.Text = field.HeaderText;

PlaceHolder panel = new PlaceHolder();
panel.Controls.Add(headerText);

//add the sort icon if needed
if (FilterExpression.Contains(field.SortExpression) &&
SortExpression.Contains(field.SortExpression))
{
string url = sortImage(field);
Image sortIcon = new Image();
sortIcon.ImageUrl = url;
sortIcon.Style[HtmlTextWriterStyle.MarginLeft] = “1px”;
panel.Controls.Add(sortIcon);
//change the left margin to 1
filterIcon.Style[HtmlTextWriterStyle.MarginLeft] = “1px”;
}

panel.Controls.Add(filterIcon);
return panel;
}

private string sortImage(DataControlField field)
{
string url = string.Empty;
string[] fullSortExp = SortExpression.Split(_sep);
List<string> fullSortExpression = new List<string>();
fullSortExpression.AddRange(fullSortExp);

//does the sort expression already exist?
int index = fullSortExpression.FindIndex(s => s.Contains(field.SortExpression));
if (index >= 0)
{
string s = fullSortExpression[index];
if (s.Contains(“ASC”))
{ url = “_layouts/images/sortup.gif”; }
else
{ url = “_layouts/images/sortdown.gif”; }
}
return url;
}

protected override void LoadViewState(object savedState)
{
base.LoadViewState(savedState);

if (Context.Request.Form[“__EVENTARGUMENT”] != null &&
Context.Request.Form[“__EVENTARGUMENT”].Contains(“__ClearFilter__”))
{
// Clear FilterExpression
ViewState.Remove(“FilterExpression”);
}
}

string FilterExpression
{
get
{
if (ViewState[“FilterExpression”] == null)
{ ViewState[“FilterExpression”] = “”; }

return (string)ViewState[“FilterExpression”];
}
set
{
string thisFilterExpression = “(” + value.ToString() + “)”;
List<string> fullFilterExpression = new List<string>();

if (ViewState[“FilterExpression”] != null)
{
string[] fullFilterExp = ViewState[“FilterExpression”].ToString().Split(_ssep, StringSplitOptions.RemoveEmptyEntries);
fullFilterExpression.AddRange(fullFilterExp);

//if the filter is gone expression already exist?
int index = fullFilterExpression.FindIndex(s => s.Contains(thisFilterExpression));
if (index == -1)
{ fullFilterExpression.Add(thisFilterExpression); }
}
else
{
fullFilterExpression.Add(thisFilterExpression);
}
//loop through the list<T> and serialize to string
string filterExp = string.Empty;
fullFilterExpression.ForEach(s => filterExp += s + ” AND “);
filterExp = filterExp.Remove(filterExp.LastIndexOf(” AND “));
if (!filterExp.EndsWith(“))”) && filterExp.Contains(“AND”))
{ filterExp = “(” + filterExp + “)”; }
ViewState[“FilterExpression”] = filterExp;
}
}

string SortExpression
{
get
{
if (ViewState[“SortExpression”] == null)
{ ViewState[“SortExpression”] = “”; }

return (string)ViewState[“SortExpression”];
}
set
{
string[] thisSE = value.ToString().Split(‘ ‘);
string thisSortExpression = thisSE[0];
List<string> fullSortExpression = new List<string>();

if (ViewState[“SortExpression”] != null)
{
string[] fullSortExp = ViewState[“SortExpression”].ToString().Split(_sep);
fullSortExpression.AddRange(fullSortExp);

//does the sort expression already exist?
int index = fullSortExpression.FindIndex(s => s.Contains(thisSortExpression));
if (index >= 0)
{
string s = string.Empty;
if (value.ToString().Contains(“DESC”))
{ s = value.ToString(); }
else
{
s = fullSortExpression[index];
if (s.Contains(“ASC”))
{ s = s.Replace(“ASC”, “DESC”); }
else
{ s = s.Replace(“DESC”, “ASC”); }
}
//reset the sort direction
fullSortExpression[index] = s;
}
else
{
if (value.ToString().Contains(“DESC”))
{ fullSortExpression.Add(value.ToString()); }
else
{ fullSortExpression.Add(thisSortExpression + ” ASC”); }
}
}
else
{
if (value.ToString().Contains(“DESC”))
{ fullSortExpression.Add(value.ToString()); }
else
{ fullSortExpression.Add(thisSortExpression + ” ASC”); }
}
//loop through the list<T> and serialize to string
string sortExp = string.Empty;
fullSortExpression.ForEach(s => sortExp += s);
sortExp = sortExp.Replace(” ASC”, ” ASC,”);
sortExp = sortExp.Replace(” DESC”, ” DESC,”);
ViewState[“SortExpression”] = sortExp.Remove(sortExp.LastIndexOf(‘,’));
}
}

private void ExportToExcel(DataTable dt)
{
Table table = ConvertDatatabletoTable(dt);
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);

table.RenderControl(htw);

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(“content-disposition”, string.Format(“attachment; filename={0}”, “CountryState.xls”));
HttpContext.Current.Response.ContentType = “application/ms-excel”;
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}

protected Table ConvertDatatabletoTable(DataTable dtSource)
{
Table tbl = new Table();
tbl.CellPadding = 0;
tbl.CellSpacing = 0;
bool AddedColumnName = false;

foreach (DataRow dtRow in dtSource.Rows)
{
TableRow row = new TableRow();
foreach (DataColumn col in dtSource.Columns)
{
if (AddedColumnName == false)
{
TableCell cell = new TableCell();
cell.Text = col.ColumnName;
row.Cells.Add(cell);
}
else
{
TableCell cell = new TableCell();
cell.Text = dtRow[col].ToString();
row.Cells.Add(cell);
}
}
tbl.Rows.Add(row);
AddedColumnName = true;
}
return tbl;
}

protected void imgExcel_Click(object sender, ImageClickEventArgs e)
{

DataTable dt = (DataTable)HttpContext.Current.Session[“Hello”];
ExportToExcel(dt);

}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
oGrid.PageSize = Convert.ToInt16(DropDownList1.SelectedValue);
}

public class DataTableWrapper
{
private DataTable _dt = new DataTable();

public DataTableWrapper(DataTable dt)
{
_dt = dt;
}

public DataTable GetTable()
{
return _dt;
}
}

Reference:

1. http://www.codeproject.com/Articles/35536/Creation-of-a-SPGridview-Webpart-having-Pagination

2. http://sharethefrustration.blogspot.co.uk/2010/02/spgridview-webpart-with-multiple-filter.html

Download code:

1. http://sdrv.ms/15bXI12

2. https://skydrive.live.com/redir?resid=CDA16F1269A2B92C!809

Advertisements

How to copy DLL from GAC in most easiest way

Hi All,

Pre-requisite

–          Visual Studio

Open/Create any dummy project in Visual Studio

Step 1. Right click on reference -> click on Add Reference.

 

Step 2. Select browse tab->Select folder C:\Windows\Assembly\GAC_MSIL

Step 3. Copy what you want.  🙂

Cheers

Sanjiv

Disable Event firing in webpart code in Sharepoint

Hi All,

There are very simple approach the same, as you can see below code

Step 1. Create one class

public class DisableEventFiring : SPItemEventReceiver, IDisposable{
  public DisableEventFiring()
  {
   base.EventFiringEnabled = false;
  }

  void IDisposable.Dispose()
  {
   base.EventFiringEnabled = true;
  }
}

Step 2. use the class while adding or update the item value.

objDocItem[“Title”] = “Sanjiv”;

using(DisableEventFiring threadScope = new DisableEventFiring())
{
    objDocItem.Update()
}

 

 

Ref:

http://www.directsharepoint.com/2011/03/disable-enable-event-firing.html

 

How to update properties of existing Custom Site Column in C#

Hi,

Some time you may required to update property of existing custom site column like need to convert type of column or many thing can be possible, there are many way to do the same, see the simplest way to do pro grammatically in C#.

Just you need to write simple line of Code, you will see the change after executing the below code.

using (SPSite site = new SPSite(“http://localhost:5000&#8221;))
{
using (SPWeb web = site.OpenWeb())
{

SPField fa = web.Fields[“Document Approver”];
fa.SchemaXml = fa.SchemaXml.Replace(“<Field”, “<Field List=\”UserInfo\” “);
fa.PushChangesToLists = true;
fa.Update();

}
}

There is an custom field with User type named “Document Approver”, the above code is adding one property “List=UserInfo”.

Before updation the schema xml of custom field,

<Field ID=”{b81d6548-115e-43b8-8745-e7ba162c893c}” Type=”User” Name=”Document_Approver”
StaticName=”Document_Approver” DisplayName=”Document Approver”
Required=”FALSE” Group=”custom” SourceID=”{1a517130-6e88-4412-ae5e-b79c6b6b1f32}” Version=”2″ />

After updation of schema xml of custom field,

<Field List=”UserInfo” ID=”{b81d6548-115e-43b8-8745-e7ba162c893c}” Type=”User” Name=”Document_Approver”
StaticName=”Document_Approver” DisplayName=”Document Approver”
Required=”FALSE” Group=”custom” SourceID=”{1a517130-6e88-4412-ae5e-b79c6b6b1f32}” Version=”2″ />

Do let me know, in case of any query.

Cheers

Sanjiv Kumar

Reconfigure SharePoint with products configuration wizard

Hi,

This blog has been useful for those,

Problem:

Whose machine name has been rename and accordingly database server name is also renamed. in that case when user will try to open any SharePoint site, display an message, “Can not connect to configuration database”, very common message.

Solution 1:

Change the registry key value,

Key:- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\WSS\ServerType

Value: CLEAN_INSTALL

and re lunch the products configuration wizard again.

Solution 2:

1. Delete all database  from database server and site from IIS

2. Execute the below stsadm command :

psconfig.exe -cmd configdb -disconnect
psconfig.exe -cmd configdb -create -server <DatabaseServer> -database <Sharepoint_Config>

3. Re lunch the products configuration wizard.

Get the list of sharepoint web applications using C#

Here there is a function which return the list of all web application in the farm using C#

public DataTable GetAllWebApplication()
{
DataTable dt = new DataTable();
dt.Columns.Add(“URL”);
dt.Columns.Add(“Title”);
SPSecurity.RunWithElevatedPrivileges(delegate()
{
SPWebServiceCollection webServices = new SPWebServiceCollection(SPFarm.Local);
foreach (SPWebService webService in webServices)
{
foreach (SPWebApplication wa in webService.WebApplications)
{
dt.Rows.Add(new object[] { wa.GetResponseUri(SPUrlZone.Default).AbsoluteUri, wa.Name });
}
}

});

return dt;
}

 

 

Get workflow status programmatically in SharePoint

Hi,

I am writing very simple method which will return the workflow status in form of  string.

protected string GetWorkflowStatus(SPListItem itm)

{

string workflowName = “ApprovalStatus”; //name of the workflow

string statusText = string.Empty;

try

{

SPWorkflowManager manager = itm.Web.Site.WorkflowManager;

//SPWorkflowFilter filter = new SPWorkflowFilter()

//{

//    ExclusiveFilterStates = SPWorkflowState.Completed | SPWorkflowState.Cancelled

//};

//Get a list of the workflows that are running

foreach (SPWorkflow instance in manager.GetItemWorkflows(itm))

{

if (instance.ParentAssociation.Name == workflowName)

{

 

foreach (SPField field in instance.ParentList.Fields)

{

 

if (field is SPFieldWorkflowStatus)

{

SPFieldWorkflowStatus statusField = (SPFieldWorkflowStatus)field;

 

if (statusField.Title == workflowName)

{

int statusValue = int.Parse(itm[statusField.StaticName].ToString());

statusText = statusField.GetFieldValueAsHtml(statusValue);

break;

}

}

}

 

}

 

}

}

catch (Exception ex)

{

log.LogInfo(ex.Message, ex.StackTrace);

}

return statusText;

}