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.

Performance Optmization with .NET

I have a web service, so the handler is called multiple times concurrently all the time.

Inside I create SqlConnection and SqlCommand. I have to execute about 7 different commands. Different commands require various parameters, so I just add them once:

command.Parameters.Add(new SqlParameter("@UserID", userID));
command.Parameters.Add(new SqlParameter("@AppID", appID));
command.Parameters.Add(new SqlParameter("@SID", SIDInt));
command.Parameters.Add(new SqlParameter("@Day", timestamp.Date));
command.Parameters.Add(new SqlParameter("@TS", timestamp));

How to connect to an external SQL server database in ASP.NET / VB.NET

Connecting to databases in .NET is different if you are coming from other languages such as PHP. To connect to a database in ASP.NET / .NET in general, you use "Connection Strings" that essentially is the connection information to the database.

First and foremost, in your code behind file within an ASP.NET application (or simply the .vb or .cs file of your .NET desktop application), you will need to first import the namespace that has the relevant database-related classes and methods, etc.

Note: All examples use the Visual Basic language, but the concept is the same for both Visual Basic and C#, for example.

SQL Server Ranking Functions

SQL Server 2005 introduced new ranking functions. This article is tailored as an introduction to these functions, difference between them and when to use each and a few examples.

Basic differences between Oracle and SQL Server

most obvious differences are:

• The FIRST biggest difference: Transaction control. In Oracle EVERYTHING is a transaction and it is not permanent until you COMMIT. In SQL Server, there is (by default) no transaction control. An error half way through a stored procedure WILL NOT ROLLBACK the DDL in previous steps.

Obviously, if you wrap the TSQL DML in BEGIN TRANSACTION and COMMIT then it will roll back but this is rare in SQL Server code I've seen.

• The SECOND biggest difference: MVCC. In SQL Server and Oracle is different. SQL Server will allow dirty reads, and writes can block reads in MS SQL (Again, it's configurable but the default in SQL Server is for performance and not read consistency, unlike Oracle where read consistency is default and unbendable.

Also consider:

• When you setup an Oracle server, you tend to have one database with many "users/schemas", and tablespaces that are shared by all your users. SQL Server has separate databases that do not share disk files.
• SQL Server uses "logins" to give you access to the SQL Server instance and each database has "users" that map to a login to get individual access to the tables and views etc.
• Typically, all the objects in a database are owned by dbo.
• TSQL is similar to PL/SQL, but (in my opinion) less powerful. You may need to simplify your SQL to get it to work as well as you'd expect in Oracle.
• The SQL Server Management Studio (2008 SP1) is fantastic!
• If you like Oracle, all the "getting under the hood" and "explain plan optimisation" then this training and experience will work well for you against guy's who just code straight SQL Server TSQL and expect the server to perform fast by magic.
• SQL Server does not have packages. This might start off as a bonus (PL/SQL packages can be a PITA) but eventually you'll start to get a big nest of similarly named stored procedures in the database and you'll wish there was a way you could organise and group then them better.

PARSENAME in Sql

DECLARE @t TABLE (A varchar(25))

INSERT @t values ('77.88.99.100') 

SELECT
  PARSENAME(A,1) AS 'First selected'
, PARSENAME(A,2) AS '2nd selected'
, PARSENAME(A,3) AS '3rd selected'
, PARSENAME(A,4) AS '4th selected'
 from @t

The question is: (select 1)

a. Are the values returned  First selected 77,  2nd selected 88,3rd selected 99,4th selected 100'

b. Are the values returned first selected 100,  2nd selected 99,3rd selected 88, 4th selected 77

c. No values are returned.

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.