Monday, June 20, 2011

How to Include Simple Row Number to a SQL Query : SQL Server 2005

SQL Server 2005 provides a new function called Row_Number() which helps to generate row numbers in the query.

The syntax is

ROW_NUMBER() OVER (ORDER BY your_primary_column_name ASC) AS ROWID, * FROM your_table_name

Sample Query for a Customer Table is

SELECT ROW_NUMBER() OVER (ORDER BY CusCode ASC) AS ROWID, * FROM Customers Where DeptNo='HR'

Happy Queries!! 

How to Convert a SqlDataReader To a DataTable or DataSet in Asp.Net?

To convert a SqlDataReader to DataTable or DataSet, use DataTable's Load method. Just look at the sample block of statements below.

string sql = "Select * from MyTable";
SqlCommand cmd = new SqlCommand(sql, MyConnection);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();

//Load the SqlDataReader object to the DataTable object as follows.
dt.Load(dr);

Response.Write(dt.Rows.Count.ToString());

Thats it.

Set the File Upload Size to Maximum in Asp.Net

To set the maximum file upload size supported by Asp.Net, you must add the <httpRuntime> under <system.web> section in the web.config file. The syntax is below

<httpRuntime maxRequestLength="Maximum size you want to upload in KB"
executionTimeout="No. of seconds for Execution Time Out" /> 

How to avoid Session Timeouts in an Asp.Net Page?

Regardless of the session timeout that is set in the IIS or in the web.congif file, we can increase the time limit of the sessions in one particular or many Asp.Net pages. Place the below JavaScript code in your Asp.Net page that need more time than the Session Timeout time,

<script language="javascript" type="text/javascript">
function fnKeepSessionAlive()
{
var myurl = "Activate_Session.aspx";

if (window.XMLHttpRequest) {
xhttp = new XMLHttpRequest()
}
else {
xhttp = new ActiveXObject("Microsoft.XMLHTTP")
}
xhttp.open("POST", myurl, true);
xhttp.send("");



window.setTimeout("fnKeepSessionAlive();", 60000);
}

fnKeepSessionAlive();
</script>

The above JavaScript function called "fnKeepSessionAlive()" uses the XMLHttpRequest to call another Asp.Net page as an asynchronous request. So the request to the page is internal and there won’t be any postback actions visible in the client browser. This JavaScript function is called recursively for every minute. You can adjust the time to 5, 10 or any number of minutes as per your requirement.

Notes:
1. The time to call the function fnKeepSessionAlive() should not exceed the Session Timeout time.
2. Make sure "Activate_Session.aspx" is physically present in your application folder. This must be an aspx page with or without codes.

How To use two Web.Config Files in Asp.Net Application?

This tip’s will help you to maintain two web.config files in an Asp.Net web application. The known factor is, by default, every web application will have single config file named as “Web.Config”. You can add keys and values under the <appSettings/> tag and retrieve it from the web application code-behind. For some additional purpose, the application can have more than one config file. But the application can recognize the file named as “Web.Config” or the only config file available in the application. So let us examine how we can make the application to retrieve the values from the second config file.

Step 1: In the Asp.Net web application, add the second web.config file named as “Web2.config”
Step 2: Remove all the default tags in Web2.Config file and add only <appSettings/> tag and its key-value as follows.
<appSettings>
<add key="TestKey" value="Test Value From Web2.Config"/>
</appSettings>
Step 3: In the default Web.Config file, at the <appSettings> tag, add an attribute as ‘file="Web2.config"’ as follows.
<appSettings file="Web2.config">
</appSettings>
Step 4: Now access the value from the second config file as usual.
Response.Write(ConfigurationManager.AppSettings.Get("TestKey"));

Sunday, June 19, 2011

Put your ASP.NET 2.0 application Offline - the user friendly way!



Every web application has its Life Cycle. This cycle involves (among many other things) developing, deploying, debugging, maintenance etc.

When your web application is already deployed on production server, making some critical changes on it can be a challenging
task if you have multiple visitors online.

Developers at Microsoft added one nice feature to ASP.NET 2.0 that addresses this very problem:

if you upload a html file named "app_offline.htm"  to the root of your website, ASP.NET recognizes this file, shuts down your application, and for every next requested page, contents of  your "app_offline.htm" file is server instead.

When you are done updating your website, you can delete this file or just rename it, and your website will start working again

NOTE:
One thing to be careful about: It is recommended that your "app_offline.htm" file size is minimum 512 bytes in order to be shown correctly in Internet Explorer 6.
(This is because IE6 has setting called "Show Friendly Http Errors" that shows its own error messages instead of the pages returned from web server - if they have HTTP status code different than 200 and if they are smaller than 512 bytes in size).

How to show number of online users visitors for ASP.NET website


There are many ways to count number of active visitors on your ASP.NET website.
They differ in technique and accuracy.

here is the simplest approach that delivers acceptable accuracy when configured optimally:

Step 1:

    - first we need to add these lines to our global.asax file
      (if your website do not have it, create it by right-clicking on website in solution explorer,
       and choosing Add New Item > Global Application Class option)

    void Application_Start(object sender, EventArgs e)
    {
        // Code that runs on application startup
        Application["OnlineUsers"] = 0;
    }

    void Session_Start(object sender, EventArgs e)
    {
        // Code that runs when a new session is started
        Application.Lock();
        Application["OnlineUsers"] = (int)Application["OnlineUsers"] + 1;
        Application.UnLock();
    }

    void Session_End(object sender, EventArgs e)
    {
        // Code that runs when a session ends.
        // Note: The Session_End event is raised only when the sessionstate mode
        // is set to InProc in the Web.config file. If session mode is set to StateServer
        // or SQLServer, the event is not raised.
        Application.Lock();
        Application["OnlineUsers"] = (int)Application["OnlineUsers"] - 1;
        Application.UnLock();
    }

This will allow us that whenever some distant web visitor opens our website in his browser,
and new session is created for him, our  "OnlineUsers" variable in the global HttpApplicationState class instance
is increased.

Also when user closes his browser or does not click on any links in our website, session expires,
and our "OnlineUsers" global variable is decreased.

To know more about ApplicationState and HttpApplicationState class visit this MSDN link:
msdn2.microsoft.com/en-us/library/system.web.httpapplicationstate(VS.80).aspx

NOTE: we are using Application.Lock and Application.Unlock methods to prevent multiple threads
from changing this variable at the same time.

By calling Application.Lock we are receiving exclusive right to change the values in Application state.
But we must not forget to call Application.Unlock() afterwards.

Step 2:
    In order for this to work correctly we need to enable sessionstate and configure its mode to InProc value (in our web.config file):

        <system.web>
        <sessionState mode="InProc" cookieless="false" timeout="20" />
        </system.web>

In-process mode stores session state values and variables in memory on the local Web server.
It is the only mode that supports the Session_OnEnd event that we used previously.

Timeout value (in minutes, not in seconds) configures how long our sessions are kept 'alive' - in other words
here we set how long our users can be inactive before considered Offline.

In this example timeout is set to 20 minutes, so while our visitors click on some links in our website at least once
in a 20 minutes, they are considered online.
If they do not open any pages on our website longer than 20 minutes, they are considered Offline, and their session
is destroyed, so our online visitor counter is decreased by 1.
(You can experiment with lower or higher values of Timeout settings to see what is the best for your website).

Ok, so now we are done with configuration steps, let us see how to use this:

To show number of online visitors/users on your ASPX page you can use this code:
        Visitors online: <%= Application["OnlineUsers"].ToString() %>

Next you could put this code snippet in you UserControl, or inside Asp.Net AJAX UpdatePanel control, and
use Timer to refresh it in regular intervals without refreshing the whole page, but that is another story

How to maintain ScrollBar position on postbacks in ASP.NET Page?



After user clicks on postback controls, ASP.NET web page is reloaded in the browser and scroll position is set to the top of the page.

If your pages are high/long and controls that cause postback are placed on the bottom it can be really annoying for users to manually scroll down after every postback.

Fortunately, this can be easily solved using the Page.MaintainScrollPositionOnPostBack property like this:

<%@ Page Language="C#" AutoEventWireup="true" ... MaintainScrollPositionOnPostback="true" %>

After the postback and reload of the page, the previous scroll position will be restored.

NOTE: this only works in .Net Framework 2.0 and higher!