Introduction
In this Article I will give some brief on sorting, searching, paging in gridview with ObjectDataSource. There are many articles on the internet which had demoed the same but had used SqlDataSources which can�t be done in live projects, ObjectDataSource will preserve the three tier architecture of an Application.
Prerequisite
� Sql Server 2000(Northwind database is used for sample app)
� Visual Studio 2005
� EntLib 2.0 (DataAccess)
The Sample Application
The application used for this article is having
� Default.aspx
� BizLayer.cs
� DataAccess.cs
In this Application the Gridview will use object data source for auto paging, sorting etc. Object data source will be using bizlayer methods for selection and searching.
Sample Application Architecture
Getting Started
Drag and drop a grid view, an object data source form the toolbox data tab on default.aspx, once done you will get something like
Now configure the data source by clicking the Configure data source as show above and the wizard will take us forward.
Populating the gridview (The select Method)
In This method we will be selecting all the records available in Customers table of Northwind database.
Step 1: Choose your business object
Step 2: Select BizLayer from the drop down.
Step 3: Choose a method of the business object that will return data associated with the select operation. We will select GetCustomers method from the drop down list for our application.
Step 4: selecting a Data source for our grid. Click the smart tag of our grid
Step 5: Select odsNorthWind(object data source) form the Choose data source drop down list.
Enabling auto paging/Sorting
After selecting the data source for the grid we will get a menu where check the enable paging and sorting option for auto paging/sorting, and that all now our grid will have paging and sorting capability with out writing a single line of code, isn�t that great.
Search
Its very common scenario where we are supposed to filter the data in grid, Object data source has given a property to do the same, what we are only suppose to do is just supply the search string to filter expression property of object data source and that�s all J.
FilterExpression Property: Gets or sets a filtering expression that is applied when the method that is specified by the SelectMethod property is called (as per MSDN).
In the Sample app we have provide a search on company name, so the filterexpression property for the same can be coded as
protected void btnSearch_Click(object sender, EventArgs e)
{
if (txtCustName.Text != "")
{
odsNorthWind.FilterExpression = "CompanyName LIKE '%" + txtCustName.Text + "%'";
}
}
Update
Again updating is also a very common scenario in data manipulation applications, In the sample application we will update the contact name of a selected customer.
Editing gridview data
For enabling editing in a gridview row first add an edit column to the gridview. To do that first click the grid view smart tag then click edit column link and then add an Edit, update, delete command from Command field as shown below.
Click Ok.
In the sample application the grid is having only three columns form the customer table and a command column.
Code Manipulation for gridview edit
1. Make the readonly property of BoundField to false for all the fiield which are supposed to me edited in gridview.
2. Set the DataKeyNames property of gridview to the ID of the table, in sample app DataKeyNames="CustomerID"
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="Customer ID"
ReadOnly="True" SortExpression="CustomerID" />
<asp:BoundField DataField="CompanyName" HeaderText="Company Name"
ReadOnly="True" SortExpression="CompanyName" />
<asp:BoundField DataField="ContactName" HeaderText="ContactName"
ReadOnly="false" SortExpression="ContactName" />
<asp:CommandField ShowEditButton="True" />
</Columns>
<asp:GridView ID="gvCustomers" runat="server" DataKeyNames="CustomerID" AllowPaging="True" AllowSorting="True" CellPadding="4" DataSourceID="odsNorthWind" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False" EmptyDataText="No Record Available">
Adding update method to Object Data Source
The last part left is to add an update method to the data source control,to do so click the smart tag of ObjectDataSource,then click Configure data source and click next then select update tab. Select an update method form Chosse a method drop down as shown below, in sample app we had selected UpdateCustomer method.
The Sample App Code
BizLayer.cs
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;
public class BizLayer
{
public BizLayer()
{
//
// TODO: Add constructor logic here
//
}
public DataSet GetCustomers()
{
DataAccess dataAcess = null;
try
{
dataAcess = new DataAccess();
return dataAcess.GetCustomers();
}
finally
{
dataAcess = null;
}
}
public void UpdateCustomers(string CustomerID,string contactName)
{
DataAccess dataAcess = null;
try
{
dataAcess = new DataAccess();
dataAcess.UpdateCustomers(CustomerID, contactName);
}
finally
{
dataAcess = null;
}
}
}
DataAccess.cs
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.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
public class DataAccess
{
public DataAccess()
{
//
// TODO: Add constructor logic here
//
}
public DataSet GetCustomers()
{
DataSet dsGetCustomer = null;
DbCommand dbCommand = null;
Database db = DatabaseFactory.CreateDatabase("Northwind");
dbCommand = db.GetSqlStringCommand("select * from Customers");
dsGetCustomer = db.ExecuteDataSet(dbCommand);
return dsGetCustomer;
}
public void UpdateCustomers(string CustomerID,string contactName)
{
DbCommand dbCommand = null;
Database db = DatabaseFactory.CreateDatabase("Northwind");
dbCommand = db.GetSqlStringCommand("update Customers SET ContactName = '"+contactName+"' where CustomerID = '" + CustomerID+"'");
db.ExecuteNonQuery(dbCommand);
}
}
Default.aspx.cs
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;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSearch_Click(object sender, EventArgs e)
{
if (txtCompanyName.Text != "")
{
odsNorthWind.FilterExpression = "CompanyName LIKE '%" + txtCompanyName.Text + "%'";
}
}
}
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>GridView and ObjectDataSource making Life easy</title>
</head>
<body>
<form id="form1" runat="server">
<table border="0" cellpadding="0" width="100%">
<tr>
<td>
<asp:Label ID="Label1" runat="server" Text="Enter Company Name :"></asp:Label>
<asp:TextBox ID="txtCompanyName" runat="server"></asp:TextBox>
<asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" />
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td colspan="3">
<asp:GridView ID="gvCustomers" runat="server" DataKeyNames="CustomerID" AllowPaging="True" AllowSorting="True"
CellPadding="4" DataSourceID="odsNorthWind" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False" EmptyDataText="No Record Available">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="Customer ID" ReadOnly="True" SortExpression="CustomerID" />
<asp:BoundField DataField="CompanyName" HeaderText="Company Name" ReadOnly="True" SortExpression="CompanyName" />
<asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
<asp:CommandField ShowEditButton="True" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="odsNorthWind" runat="server" SelectMethod="GetCustomers"
TypeName="BizLayer" UpdateMethod="UpdateCustomers">
<UpdateParameters>
<asp:Parameter Name="CustomerID" Type="String" />
<asp:Parameter Name="contactName" Type="String" />
</UpdateParameters>
</asp:ObjectDataSource>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
</table>
</form>
</body>
</html>
