ROW_NUMBER() in SQL

Hello guys,

i have make a simple application which contains A Gridview with the thousend of records.
so i am manage it with paging.

IF I WANT TO FILTER GRIDVIEW RECORDS WITH SQL ?

So, i have take two textbox two filtering the gridview as follow.

and i am sending the value in the database as follow.

create table #tmp (id int,name varchar(10))

insert into #tmp values(1,'a')
insert into #tmp values(2,'b')
insert into #tmp values(3,'c')
insert into #tmp values(4,'d') 

select id,name,rownumber,totalrowcnt from
(
    select #tmp.*,ROW_NUMBER() over (order by #tmp.id) rownumber,count(*) over() totalrowcnt
    from #tmp
) result
where result.rownumber between VALUE1 and VALUE2

* here value1 and value2 are textbox value.

So, with this simple SQL query i can filter the gridview records.

Gridview in disconnected mode

aspx.cs

using BusinessLayer;
using PropertyLayer;
using System.Data.SqlClient;

namespace Gv_Disconnected_NTier_
{
    public partial class Gv_DisconnectedMode : System.Web.UI.Page
    {
        DataTable d1 = new DataTable();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Page.IsPostBack == false)
            {
                GvBusinessLayer objBusinessLayer = new GvBusinessLayer();
                d1 = objBusinessLayer.GetStudentDetails("student_records");
                ViewState["d1"] = d1;
                bind();
            }
            else
            {
                 d1 = (DataTable)ViewState["d1"];
            }
        }

Display custom messages or images when there is no records in GridView Data Source

Display custom messages or images when there is no records in GridView Data Source

This is a very frequent requirements to display Custom Messages or Images when there is no records in Grid View. I found people used custom code to achieve this. But ASP.NET Gridview having some inbuilt features to do the same.   You can use EmptyDataTemplate using GridView to display any custom message or Images or even web controls to add new records or whatever you want as per your requirements.

Gridview Paging using C#

A GridView is most often used to display the data retrieved from the database in Tabular form with features of Gridview like data paging, sorting and auto formats.

You can use C# code to bind the SQL data with GridView control and follow the following simple steps to make your ASP.Net GridView control with paging enabled.

First of all drag the GridView control from Data controls menu. It will add the GridView control HTML source code as given above. Now click on GridView control to load the control properties at right side panel.

What is Entity Framework?

asfasfThe Microsoft ADO.NET Entity Framework is an Object/Relational Mapping (ORM) framework that enables developers to work with relational data as domain-specific objects, eliminating the need for most of the data access plumbing code that developers usually need to write. Using the Entity Framework, developers issue queries using LINQ, then retrieve and manipulate data as strongly typed objects. The Entity Framework’s ORM implementation provides services like change tracking, identity resolution, lazy loading, and query translation so that developers can focus on their application-specific business logic rather than the data access fundamentals.

RAISERROR statement in SQL server

RAISERROR statement is used to return error messages to the business applications that executes SQL statements. The usual errors returned by the SQL server may not make much sense to the business applications users hence we overwrite it by using RAISERROR to display meaningful messages. The statement uses same format as a system error or warning messages generated by SQL server.

You can return a user-defined error message by using RAISERROR.  The general syntax is as below

General Page Life-Cycle Stages

When an ASP.NET page runs, the page goes through a life cycle in which it performs a series of processing steps. These include initialization, instantiating controls, restoring and maintaining state, running event handler code, and rendering.

General Page Life-Cycle Stages

In general terms, the page goes through the stages outlined in the following :

1 Page request   : The page request occurs before the page life cycle begins. When the page is requested by a user, ASP.NET determines whether the page needs to be parsed and compiled (therefore beginning the life of a page), or whether a cached version of the page can be sent in response without running the page.
 

2 Start :  In the start stage, page properties such as Request and Response are set. At this stage, the page also determines whether the request is a postback or a new request and sets the IsPostBack property. The page also sets the UICulture property.

3  Initialization : During page initialization, controls on the page are available and each control's UniqueID property is set. A master page and themes are also applied to the page if applicable. If the current request is a postback, the postback data has not yet been loaded and control property values have not been restored to the values from view state.

4  Load : During load, if the current request is a postback, control properties are loaded with information recovered from view state and control state.

5  Validation : During validation, the Validate method of all validator controls is called, which sets the IsValid property of individual validator controls and of the page.

6 Postback event handling  : if the request is a postback, any event handlers are called.

 7 Rendering : Before rendering, view state is saved for the page and all controls. During the rendering phase, the page calls the Render method for each control, providing a text writer that writes its output to the OutputStream of the page's Response property.

8  Unload :  Unload is called after the page has been fully rendered, sent to the client, and is ready to be discarded. At this point, page properties such as Response and Request are unloaded and any cleanup is performed.

Hide a Parent Node

I have a TreeView and SiteMapDatasource on my .aspx page. Web.config has siteMapProvider settings and a SiteMap file (Web.sitemap). 

.aspx file:

<asp:TreeView ID="TreeView1" runat="server" AutoGenerateDataBindings="False" DataSourceID="SiteMapDataSource1" ontreenodedatabound="TreeView1_TreeNodeDataBound">
</asp:TreeView>
<br />
<asp:SiteMapDataSource ID="SiteMapDataSource1" runat="server" />

Above is .aspx file with controls for this example. You should note we have a event handler for OnTreeNodeDataBound event of TreeView.

Web.config:

<siteMap defaultProvider="XmlSiteMapProvider" enabled="true"> <providers> <add name="XmlSiteMapProvider" type="System.Web.XmlSiteMapProvider"
siteMapFile="Web.siteMap" securityTrimmingEnabled="true"/> </providers> </siteMap>

Part of web.config is shown above with SiteMapProvider. Note we have siteMapFile="Web.sitemap"  and securityTrimmingEnabled="true" for Roles to be applied to the sitemap.

Web.SiteMap:

<siteMap xmlns="http://schemas.microsoft.com/AspNet/SiteMap-File-1.0" > <siteMapNode url="~/Default.aspx" title="Home" description="Home">
<siteMapNode url="~/Level1.aspx" title="Level 1" description="" > <siteMapNode url="~/Page1.aspx" title="Page1" description="" />
<siteMapNode url="~/Page2.aspx" title="Page2" description="" /> </siteMapNode>
<siteMapNode url="~/NoChilde.aspx" title="Level 2" description="" /> <siteMapNode url="~/Level3.aspx" title="Level 3" description="" >
<siteMapNode url="~/Page3.aspx" title="Page3" description="" /> </siteMapNode>
</siteMapNode> </siteMap>

The above sitemap is not very realistic looking but will serve the purpose for our demonstration. We see that siteMapNode "Level 2" does not have any childnode. So we want to hide it and not show in our TreeView. In real case scenario it might have few child nodes with Roles attribute set. If user is not in particular role the SiteMapProvider will hide those node.

Without the code-behind which we are going to see in a moment the TreeView will look like below:

We now want to hide "Level 2" node as it does not have any child node.

.aspx.cs file (code-behind):

protected void TreeView1_TreeNodeDataBound(object sender, TreeNodeEventArgs e) {
SiteMapNode node = (SiteMapNode)e.Node.DataItem;
if(node.HasChildNodes ==false && e.Node.Depth ==1) {
TreeView1.Nodes[0].ChildNodes.Remove(e.Node);

}
}

In the above code what we are doing is first getting the DataItem for the CurrentNode that is being DataBound. For this we have to use SiteMapNode.
1: we check if the node has Child with node.HasChildNodes ==false.
2: "Home" is at Depth 0 while Level 1,2 and 3 are at Depth 1 and Page1,Page2 and so on are at Depth=2. So to make sure that we are not hiding Home or any leaf nodes (page1,Page2 etc) we are checking that depth of current Node is 1.
If the above two conditions are met then we will remove the current node (i.e. e.Node) from TreeView1.
For this we first get the NodesCollection for TreeView1. i.e. TreeView1.Nodes[0], Here 0 is because we have all Nodes under "Home" parent Node.
After that we call the ChildNodes.Remove method to remove the current Node.
That is it. Now if we run the code we will get the following output for TreeView:

ShowStartingNode=False set for SiteMapDataSource:

 If you set the ShowStartingNode=False for SiteMapDataSource then you might want to chage the Depth=0 and also make little change on how to remove node.
The code should look like below:

protected void TreeView1_TreeNodeDataBound(object sender, TreeNodeEventArgs e) {
SiteMapNode node = (SiteMapNode)e.Node.DataItem;
if(node.HasChildNodes ==false && e.Node.Depth ==0) {
TreeView1.Nodes.Remove(e.Node);
}
}

Thats it. Thanks for reading this article.