Preface:
GridView is awesome, and if you combine it with Sql Server 2005, DetailsView and FormView you have a formidable force to build a rich data aware page. You can achieve almost everything that you may require on a page without even writing a single line of code in your ASPX, even Custom paging (it requires you to write some views in TSQL).
Requirement Analysis:
Prerequisites:
You need Visual Studio 2005, Sql Server 2005, and ASP.net AJAX 1.0
Architecture:
The DAL:
It is implemented using a Dataset. This is a strongly typed dataset, and contains all the required Stored procedures and queries.
We will concentrate on the class "Entity" only.
The following is the implementation of the BAL.
The presentation layer has only 1 page for now. I hope this is what we need now. It looks like the following:
As we can see, we have highly Customized the datagrid. Lets dive deeper into the functionality.
The BAL:
The following code snippet contains Insert, Edit, and Delete functions required to do usual stuff. Its self explanatory.
/// <summary>
/// This is the Insert function for the class.
/// It takes all the columns as arguments and uses a DatabaseDirect mode to update the database directly.
/// </summary>
/// <param name="EntityID"></param>
/// <param name="UserID"></param>
/// <param name="Title"></param>
/// <param name="Description"></param>
/// <returns></returns>
public int Insert(int EntityID, string UserID, string Title, string Description)
{
int rowsAffected = 0;
try
ManageabilityDSTableAdapters.EntityTableAdapter ad = new ManageabilityDSTableAdapters.EntityTableAdapter();
//Update the DB directly
//Insert function is defined in the DataSet
rowsAffected = ad.Insert(UserID, Title, Description);
}
catch
//throw all the error that are catched here
throw;
return rowsAffected;
/// This is the Edit function for the class.
public int Edit(int EntityID, string UserID, string Title, string Description)
//Update function is defined in the DataSet
rowsAffected = ad.Update(UserID, Title, Description, EntityID, EntityID);
/// This is the Delete function for the class.
public int Delete(int EntityID)
//Delete function is defined in the DataSet
rowsAffected = ad.Delete(EntityID);
//throw all the error that are catched here or apply
Now the Select functions. These functions are the key to our demo. We will explain each of them at a later part in this post.
/// This function selects all the records filtered by UserID
public ManageabilityDS.EntityDataTable Select(string UserID)
//GetEntitiesByUser function is defined in the DataSet
return ad.GetEntitiesByUser(UserID);
/// This function selects all the records filtered by UserID and EntityID
public ManageabilityDS.EntityDataTable Select(string UserID, int EntityID)
//GetEntityByUserAndEntityID function is defined in the DataSet
return ad.GetEntityByUserAndEntityID(UserID, EntityID);
/// This function gets the maximum rows from the table after its sequenced using Row_number
public long GetMaxNumberOfRows(string UserID)
long num = 0;
//GetNumberOfRows function is defined in the DataSet
num = (long)ad.GetNumberOfRows(UserID);
return num;
/// This function return a strongly type list containing paged data
/// A benefit of using Generic here is to minimize the size of the object
/// <param name="StartRowIndex"></param>
/// <param name="MaximumRows"></param>
public List<Entity> Select(string UserID, int StartRowIndex, int MaximumRows)
ManageabilityDS.EntityDataTable tbl = null;
List<Entity> t = new List<Entity>();
//GetPagedEntityByUser function is defined in the DataSet
tbl = ad.GetPagedEntityByUser(StartRowIndex, MaximumRows, UserID);
//fill the strongly typed List with data
foreach (ManageabilityDS.EntityRow row in tbl.Rows)
Entity en = new Entity();
en.EntityID = row.EntityID;
en.UserId = row.UserID;
en.Title = row.Title;
en.Description = row.Description;
t.Add(en);
return t;
/// This function return a strongly type list containing paged and sorted data
/// <param name="ColumnToOrder"></param>
/// <param name="OrderDirection"></param>
public List<Entity> Select(string UserID, int StartRowIndex, int MaximumRows, string ColumnToOrder, string OrderDirection)
//GetPagedEntityByUserOrdered function is defined in the DataSet
tbl = ad.GetPagedEntityByUserOrdered(StartRowIndex, MaximumRows, UserID, ColumnToOrder, OrderDirection);
The Presentation Layer:
The datagrid looks like following in code:
<asp:GridView ID="MainGrid" runat="server" EnableViewState="true" AutoGenerateColumns="False"
CellPadding="4" ForeColor="#333333" Width="100%" PageSize="2" DataKeyNames="EntityID"
EmptyDataText="No data available" OnRowDeleting="MainGrid_RowDeleting">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle Font-Bold="True" HorizontalAlign="Left" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:TemplateField HeaderText="Details">
<HeaderStyle Font-Bold="True" Width="80%" />
<ItemTemplate>
<table width="100%">
<tr>
<td width="15%">
<span><b>ID:</b></span>
</td>
<td>
<asp:Label ID="EntityIDLabel" runat="server" Text='<%#Bind("EntityID")%>'></asp:Label>
</tr>
<td bgcolor="silver" colspan="2">
<span><b>Title:</b></span>
<asp:Label ID="TitleLabel" runat="server" Text='<%#Bind("Title")%>'></asp:Label>
<span><b>Description:</b></span>
<asp:Label ID="DescriptionLabel" runat="server" Font-Italic="true" Text='<%#Bind("Description")%>'></asp:Label>
</table>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Options">
<td valign="top">
<asp:LinkButton ID="Select" CommandName="Select" runat="server" Text="Select"></asp:LinkButton>
<asp:LinkButton ID="Delete" CommandName="Delete" runat="server" Text="Delete" OnClientClick="return confirm('Are you sure you want to delete this record?');"></asp:LinkButton>
<HeaderStyle Font-Bold="False" />
</Columns>
</asp:GridView>
We have made extensive use of template columns to give it a different look. To bind the data we simply used: Text='<%#Bind("Column_Name")%>' e.g.Text = '<%#Bind("Title")%>'
Notice that the paging is disabled. DataKeyNames is set to the Primary Key,and we have defined OnRowDeleting event for deleting a record.
Remember those days, when we used to code OnItemDatBound event in ASP.Net 1.1 to bind the Delete button attribute with an OnClick event. Now, its as easy as assigning OnClientClick = [required javascript] e.g.OnClientClick = "return confirm(Are you sure?)"
The DetailsView looks like the following in code:
<asp:DetailsView ID="EntityDetailsView" runat="server" AutoGenerateRows="False" DataKeyNames="EntityID"
DataSourceID="odsDetailView" Height="50px" Width="100%" OnItemInserted="EntityDetailsView_ItemInserted"
OnItemUpdated="EntityDetailsView_ItemUpdated">
<Fields>
<asp:BoundField DataField="EntityID" HeaderText="ID:" InsertVisible="False" ReadOnly="True"
SortExpression="EntityID" HeaderStyle-Width="15%" HeaderStyle-Font-Bold="true" />
<asp:BoundField DataField="UserID" HeaderText="UserID" SortExpression="UserID" Visible="False" />
<asp:TemplateField HeaderText="Title:" HeaderStyle-VerticalAlign="Top" SortExpression="Title"
HeaderStyle-Font-Bold="true">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Title") %>' CssClass="textbox"></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
</InsertItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Title") %>'></asp:Label>
<asp:TemplateField HeaderText="Description:" HeaderStyle-VerticalAlign="Top" SortExpression="Description"
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Description") %>' CssClass="textarea"
TextMode="MultiLine" Rows="5" Width="80%"></asp:TextBox>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("Description") %>'></asp:Label>
<asp:CommandField ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
Check out the following:
We have set DataKeyField to the Primary Key and set the datasource ID to an ObjectDataSource called "odsDetailView". It fetches all the data via theBusiness layer and it doesn't need to go to the DAL.
The ObjectDataSource truely helps us to build a 3-tier application. Earlier, in this model the UI layer used to bind directly to the Data layer. This actually used to break the tier, and caused scalability and maintenance hassles.
We have OnItemInserted and OnItemUpdated events handled to refresh theGridView whenever an update is made using the DetailsView.
<asp:DetailsView ID="EntityDetailsView" runat="server" AutoGenerateRows="False" DataKeyNames="EntityID" DataSourceID="odsDetailView" Height="50px" Width="100%" OnItemInserted="EntityDetailsView_ItemInserted" OnItemUpdated="EntityDetailsView_ItemUpdated">
So, whenever you click on the Select button in a row inside the GridView,the ObjectDataSource retrieves the PrimaryKey from the SelectedValue propertyof the GridView. See the definition of the ASP ControlParameter below.
<SelectParameters>
<asp:SessionParameter DefaultValue="fareast\sanjeets" Name="UserID" SessionField="UserID"
Type="String" />
<asp:ControlParameter ControlID="MainGrid" Name="EntityID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
So far, we have discussed how the controls interact with each other to give youyou a nice clutter free UI for simple operations like Insert, Update, and Delete.Now, lets move on the bigger picture.
I have entered about 50K rows in the Entities table.
Fair enough, I need paging now. Talking about normal paging would be painful, so thats ruled out (I hope you understand what does the normal paging will do, it will bring all 50K rows and show you 10 rows... it hurts).
GridView alongwith ObjectDataSource brings to CustomPaging, so all you have to do,is to use the magic of Row_Number in Sql Server 2005 to bring sequential rows.But, thats again almost codeless. So, you have lesser control over it? I hope youstill remember scalability? right?
Refer to Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0 for more information about how to almost codelessly implement custom paging.
Lets take a look into the 2 Stored Procedures:The first one as the name suggests Selects Paged rows by a given condition.
ALTER Procedure [dbo].[SelectPagedEntityByUser]
@StartRowIndex int,
@MaximumRows int,
@UserID varchar(50)
AS
Select EntityID, UserID, Title, [Description]
From AllEntities
Where
SrNum Between @StartRowIndex AND (@StartRowIndex+@MaximumRows)-1
AND UserID = @UserID
It uses the following view:
ALTER View [dbo].[AllEntities]
Select
EntityID, UserID, Title, [Description],
Row_Number() Over(Order By EntityID) as SrNum
From Entity
The second one, as the name suggests Selects Paged rows by a given condition with a sortcondition:
ALTER Procedure [dbo].[SelectPagedEntityByUserOrdered]
@UserID varchar(50),
@ColumnToOrder varchar(50),
@OrderDirection varchar(10)
if @ColumnToOrder like '%ID'
begin
if @OrderDirection = 'asc'
From (
) as temp
end
else
Row_Number() Over(Order By EntityID DESC) as SrNum
if @ColumnToOrder = 'title'
Row_Number() Over(Order By Title) as SrNum
Row_Number() Over(Order By Title DESC) as SrNum
if @ColumnToOrder like 'desc%'
Row_Number() Over(Order By [Description]) as SrNum
Row_Number() Over(Order By [Description] DESC) as SrNum
Their corresponding functions in the BL looks like the following:Notice the use of Generics.For the unsorted:
For the sorted:
Let's get back to the Presentation Layer. Here, as part of customization we have theGridView inside a table which is sitting inside an asp:UpdatePanel... AJAX effect!!!Simple, sweet and effective.
We had to create the PageLinks for navigation. The ellipsis and all other links are createdon the fly and provides smooth and fast navigation at any point.When you click on the link, only PageSize number of rows are fetched from the database,so practically it doesn't matter if you have 50K or 500K records!!!
Lets see how the Links are created:
/// This sub creates all the links on the page
private void CreateButtons()
int totalRecords = int.Parse(RecordCountHdn.Value);
int pageSize = int.Parse(PageSizeHdn.Value);
//calculate the total number of links to be created
int totalPages = totalRecords % pageSize == 0 ? totalRecords / pageSize : (totalRecords / pageSize) + 1;
//this is for the ellipsis, keeps a track of the previous bunch of links
int x = int.Parse(ViewState["PageLinkLastBunchCount"].ToString());
//its the counter that checks whether all the links are created
int p = MAX_PAGE_LINKS * (x + 1);
//The ellipsis linkbutton
LinkButton ellipsis = null;
////To AJAXify the linkbuttons you need to have triggers
AsyncPostBackTrigger t = null;
//clear any previous links in the placeholder
PageLinksPH.Controls.Clear();
if (p < totalPages)
//if you are on the first bunch of links
if (x == 0)
for (int i = MAX_PAGE_LINKS * x; i < p; i++)
LinkButton l = new LinkButton();
l.ID = "pagelink"+i;
l.Text = string.Format("{0}", i + 1);
l.ToolTip = string.Format("{0}+...", (i * pageSize) + 1);
l.CommandArgument = string.Format("{0}", (i * pageSize) + 1);
l.CssClass = "pagelink";
//add event handler to each linkbutton
l.Click += new EventHandler(l_Click);
PageLinksPH.Controls.Add(l);
t = new AsyncPostBackTrigger();
t.ControlID = l.ID;
AjaxPanel.Triggers.Add(t);
ellipsis = new LinkButton();
ellipsis.ID = "ellipsisNext";
ellipsis.Text = ">>";
ellipsis.ToolTip = "Next";
ellipsis.CommandArgument = string.Format("next");
ellipsis.CssClass = "pagelink";
ellipsis.Click += new EventHandler(ellipsis_Click);
PageLinksPH.Controls.Add(ellipsis);
t.ControlID = ellipsis.ID;
else //this will show next and the previous ellipsis
ellipsis.Text = "<<";
ellipsis.ID = "ellipsisPrevious";
ellipsis.ToolTip = "Previous";
ellipsis.CommandArgument = string.Format("previous");
l.ID = "pagelink" + i;
Event handler for the ellipsis:
/// The ellipsis' event handler. It shows the next/previous bunch of links
/// <param name="sender"></param>
/// <param name="e"></param>
void ellipsis_Click(object sender, EventArgs e)
//throw new Exception("The method or operation is not implemented.");
LinkButton l = (LinkButton)sender;
if (l.CommandArgument.ToLower().Equals("previous"))
x--;
else if (l.CommandArgument.ToLower().Equals("next"))
x++;
ViewState["PageLinkLastBunchCount"] = x;
CreateButtons();
Event handler for the links:
/// This handler will send the query to the database fetching the
/// exact number of records only. It also takes care of sorting.
void l_Click(object sender, EventArgs e)
//Response.Write("l_Click");
l.CssClass = "pagelinkSelected";
int startIndex = int.Parse(l.CommandArgument);
ViewState["PageStartIndex"] = startIndex;
using (Entity en = new Entity())
if (_isPageSorted)
MainGrid.DataSource = en.Select(_userID, startIndex, pageSize, ColumnList.SelectedValue.ToString(), OrderDirectionList.SelectedValue.ToString());
MainGrid.DataSource = en.Select(_userID, startIndex, pageSize);
MainGrid.DataBind();
InfoLabel.Text = string.Format("You are at Page# {0}", l.Text);
catch(Exception ex)
ErrorLabel.Text = ex.Message;
After you follow the steps above, I am sure it will be a cake walk for you all to createor maintain any such page. It might appear to be lots of work, but its highly reliableand scalable. No blackboxes!!!
So, this brings us to an end of this long post, I sincerely hope it will help you a lot.
See you soon...