Test for time

This is the rhythm of the life!!

How to make all columns as Text in excel export in ASP.NET

Some time integer or any other datatype columns internally get formatted and shows scientific numbers instead of actual value.


To solve this issue we can use following "style" to make all columns as Text in excel.

string style = @"<style> TD { mso-number-format:\@; } </style>";

View State Vs. Session State Vs. Application State

Introduction

There are a number of articles and blogs available about ASP.NET state management. I have tried writing this article for beginners explaining these concepts in simple language and step-by-step.

This is a very basic article for beginners that show various state management techniques. It is very confusing for the developers when working with states in ASP.NET web applications. This article will help to understand how practically we should use these. Working with ASP.NET it is very important to understand and use the various states maintenance techniques available. Maintaining states during ASP.NET requests is very important from the application perspective.

Stateless Nature of Web application
Web applications run on HTTP protocols and this HTTP protocol is stateless in nature, meaning it does not remember state or retain any state between requests and responses.

Web application Processing

Whenever a web application is compiled, the entire source code of the project is compiled into an intermediate language and generates an output assembly that is a DLL residing in the bin folder of the project directory.

When the application URL is requested by a user, The web server loads the requested project DLL into memory and creates an instance of the web form requested that results in the creation of a new instance of web form and all the controls and variables available on that requested web form.

After creation it completes the page life cycle and renders the output as HTML and sends back the HTML output to the browser as a response. Then the web form object is immediately destroyed, meaning that the web form with their control is immediately something after rendering.

Various state management techniques

A. Client-side State Management: 

• View State
• Hidden Field
• Cookies
• Control State

B. Server-side State Management:

• Session
• Application Object
• Caching

We can see a number of ways of doing state management as listed above. But I am going to explain View state, Session State and application state in this article.

View State
View State is a technique to maintain the state of controls during page post-back, meaning it stores the page value at the time of post-back (sending and receiving information from the server) of your page and the view state data can be used when the page is posted back to the server and a new instance of the page is created.

View state data is nothing but a serialized base-64 encoded string stored in a hidden input field on the page and it travels between the browser and the server on every user request and response.

On the web server it is encoded on the page init event of the page's life and assigned back to the variable and after the HTML rendering and sent back to the browser with the values to client.

View State advantages: 

• Very easy to implement.
• Stored on the client browser in a hidden field as a form of Base64 Encoding String not encrypted and can be decoded easily.
• Good with HTTP data transfer

View State disadvantages:

• The performance overhead for the page is larger data stored in the view state.
• Stored as encoded and not very safe to use with sensitive information.

Where to Use
View state should be used when the user needs to store a small amount of data at the client browser with faster retrieval. The developer should not use this technique to retain state with larger data since it will create a performance overhead for the webpage. It should be used for sending data from one page to another. Not very secure to store sensitive information.

ASP.NET Session State

Session State is another state management technique to store state, meaning it helps in storing and using values from previous requests. Whenever the user requests a web form from a web application it will get treated as a new request. an ASP.NET session will be used to store the previous requests for a specified time period.

Session variables are stored in a SessionStateItemCollection object that is exposed through the HttpContext.Session property.

By default, an ASP.NET session state is enabled for all ASP.NET applications. Session state data is shared across all the web pages, in other words when navigating from one page session the data would available.

Session variables are single-user global data stored on the web server, meaning by default a session state variable is stored in the web server memory and is available across all pages but it will be for a single session.

SessionID

Client Machine Cookies are being used by a session to store a session id. This Session ID is being used by the web server to differentiate among requests of the same user or different ones. SessionID is nothing but a property to uniquely identify a browser with session data on the server. The SessionID value is randomly generated by ASP.NET and stored in a non-expiring session cookie in the browser. The SessionID value is then sent in a cookie with each request to the ASP.NET application.

Definition Reference: MSDN

Dear friends, unfortunately I am not explaining much about cookies here but it is important to spend some time for cookies also.

Cookies are nothing but a small bit of text that accompanies requests and pages as they go between the web server and the browser. The cookie contains information about the web application that can be read whenever the user visits the site.

The Session id will be sent as part of the URL in every request and if the session id is removed or changed it will be taken as a new request.

http://localhost:7291/(S(hkjtkowaucoyytjjfgha41ab))/Default.aspx


Session Modes

Session modes explain how session variables are being stored, in other words what storage type is used by the variable and what is their behavior.

The default behavior is in memory in an ASP .NET worker process.

Here are the various session modes available in ASP.NET. 

• Off
• InProc
• StateServer
• SQLServer
• Custom

Each mode has a different behavior in a web application. They have their own advantages and disadvantages.

Guys, It is very important to understand about the session modes when you are working with an ASP.NET application with session variables as state management techniques. The session modes selected as mode in webconfig enables the ways session variable are stored and it will be then responsible for the application behavior.

OffIf an application has no requirement or need for session state then it's very important to use the off mode. By using this application performance will be better.

InProc Mode
InProc mode can be done in an ASP.NET web application using a configuration file by setting the mode attribute in the element SessionState.

When Inproc session state is used the session variables are being stored on the web server memory inside the ASP.NET worker process.

Confused?

Here is the MSDN Definition that says:

ASP.NET runs within a process known as the ASP.NET worker process. All ASP.NET functionality runs within the scope of this process.

Basically session variables are stored in the executable that is an IIS worker process. So if the request is coming from the same user the data will be available.

Advantages of InProc: 

• Easy to Implement.
• Complex Objects can be added without serialization.
• Best in performance compared to out-of-process modes.

Disadvantages of InProc :

• Not able to sustain the session values when the worker process/IIS is restarted. In that case data loss will happen witch make the application break.
• Scalability is a major problem.
• Not good for applications with a large user base.

Where to Use
In Proc mode is best suited for the application that is hosted on a single server and mid size use base or the session variable used is not big, to avoid data loss and scalability issues. When there is a requirement for a web farm  or web garden deployments the “out of process “modes like state server or SQL Server modes are the best option.

State Server Session Mode
The disadvantage of session data loss is due to the worker process recycle that can be reduced using another mode, the state server mode.

Reference MSDN DefinitionStateServer mode, that stores session state in a separate process called the ASP.NET state service. This ensures that session state is preserved if the web application is restarted and also makes session state available to multiple Web servers in a Web farm.

ASP.NET is a Windows services that stores the session variable data in their process.

Procedure to set up state server mode
Go to Run then enter "Services.msc" then Start ASP.NET State Service.

By default ASP.NET state service is in manual mode.

Server Name/IP Port

StateConnectionstring basically consists of the following;

Server/system: where an ASP.NET Windows service is running.
Port: By default the state service listens to the TCP port 42424 that is configurable using the registry.

Here in my example I am using my machine's Windows service to implement the state server mode. It could be a separate dedicated machine or a web server in the network depending on requirements.

So now the session state variables are stored in a Widows service of the my machine so the recycling process of the ASP.NET worker process does not have any impact on the session variables.

Advantages of State Server Mode: 

• Worker process recycling does not impact session variable data
• Can be stored on the same web server or different dedicated machine

Disadvantage of State Server Mode:

• Restart of sate service could lead to session data loss.
• Slower than in proc mode

SQL Server Session Mode
When the Session mode of an ASP.NET application is set by SQL Server then the session variables are being stored in the SQL Server database. This mode also provides a reliable way to store session data and preserve values after an IIS or web server restart and best suited for the web farm like application deployment.

Since we are storing the session variables in a SQL Server database we need a databse and table to store the data.

At the location "C:\Windows\Microsoft.NET\Framework64\v4.0.30319" an exe named aspnet_regsql is present.

It depends on the Window's operating system and the version of .Net framework installed on the machine. In my case I have Windows 7 and framework version 4.0 installed.

Procedure

Go to Run then enter cmd then open a command window then enter "cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319".

This will navigate to the location where aspnet_regsql.exe is present.



Run the tool from that location using the command below.










Web Config
Do change the mode to SQL Server and provide the SQL connection string.

1. <sessionState mode="SQLServer"  customProvider="DefaultSessionProvider"   
2.                   sqlConnectionString="Data Source=abhishek-HP\devAbhi;integrated security=SSPI">  

After doing all the required set up now our application is ready to run with SQL Server session mode.



Advantages of SQL Server Mode

• Very secure and most reliable option for the session management.
• Session data will be able to survive after worker process restart or state window service restart.
• Most scalable compared to the other modes.
• Most suited for web garden or web farm type deployments and able to handle larger data in the session.

Disadvantages of SQL Server mode

• Slow performance
• Overhead for serialization and deserialization of complex data.

Where to Use
Here we have learned about session state and various modes to store data in session variables. Every mode has some advantages and disadvantages for use in web applications. Basically it depends on the application behavior, use base and kind of deployment which session should be used. Guys, be careful when choosing the session modes since it leads to performance issues and data loss that hamper the web application.

Application State

The MSDN Definition says: Application state is a data repository available to all classes in an ASP.NET application. Application state is stored in memory on the server and is faster than storing and retrieving information in a database. Unlike session state, which is specific to a single user session, application state applies to all users and sessions.

Application state is stored in an instance of the HttpApplicationState class. This class exposes a key-value dictionary of objects.

Application state variables are also used to store data when navigatiing from one page to another. It's multi-user Global data meaning it will be accessible across all pages and all sessions. Application state variables are stored on the web server in ASP.NET worker process memory.

Sample Code

Addition of data in application variables.

1. namespace Test  
2. {  
3.     public partial class applicationState : System.Web.UI.Page  
4.     {  
5.         protected void Page_Load(object sender, EventArgs e)  
6.         {  
7.             if (!IsPostBack)  
8.             {  
9.                 if (Application["Counter"] == null)  
10.                 {  
11.                     Application["Counter"] = 0;  
12.                 }  
13.                 TextBox1.Text = Application["Counter"].ToString();  
14.   
15.             }  
16.         }  
17.   
18.         protected void Button1_Click(object sender, EventArgs e)  
19.         {  
20.             if (Application["Counter"] != null)  
21.             {  
22.                 int ApplicationCounter = (int)Application["Counter"] + 1;  
23.                 TextBox1.Text = ApplicationCounter.ToString();  
24.                 Application["Counter"] = ApplicationCounter;  
25.             }  
26.   
27.         }  
28.   
29.         protected void Button2_Click(object sender, EventArgs e)  
30.         {  
31.             Response.Redirect("ApplicationStateTest.aspx");  
32.         }  
33.     }  
34. }  

Reading data from application variables:

1. namespace Test  
2. {  
3.     public partial class ApplicationStateTest : System.Web.UI.Page  
4.     {  
5.         protected void Page_Load(object sender, EventArgs e)  
6.         {  
7.             if (Application["Counter"] != null)  
8.             {  
9.                 Label1.Text = Application["Counter"].ToString();  
10.             }  
11.         }  
12.     }  
13. }  

An application state variable does not have any default time unlike session variables. It will end when the application hosting process is restarted or the application ends.

Application state variable data is not reliable for web farms or web garden type deployment since they are not shared across multiple web servers or multiple worker processes on the same machine so using application variable data could cause the problem of data loss and the application breaks.

An application state variable is not thread safe, meaning multiple users from a different session can access the variable and can manipulate the variable.

To ensure the data integrity and resolve concurrency issues while using application state variables, lock and unlock methods should be used.

Important Note

Web farm: It's a situation where the web application is deployed on different servers with load balancer.

Web garden: It's a situation where the web application is deployed on the same server with multiple worker processes.

Advantages of application state: 

• Application variable data is multi-user global data stored in memory.
• Easy to access.
• Fast retrieval.

Disadvantages of application state:

• Application variable data is not able to survive the IIS restart and worker process recycling.
• Not suited for web farm and web garden like deployment situation.

Where to Use
An application variable is used only when the variable needs to have global access and when you need them for the entire time, during the lifetime of an application.

Conclusion
Guys, in the preceding explanation of view state, the session state and application state management techniques all have some advantages and disadvantages in web applications. We should very intelligently pick the technique analyzing our application usage and functionality used in the application.

What is running on your SQL Server?

 We can check execution status on our SQL Server by using sys.dm_exec_requests like follows. It also tell us the execution status:

e.g. 
SELECT  t.text, r.*
FROM     sys.dm_exec_requests r 
CROSS APPLY   sys.dm_exec_sql_text(r.sql_handle) t

How to dump DLL exports

You can dump .dll using this method

To print the function names that a Windows dynamic link library exports (DLL), you can use the dumpbin.exe tool, which comes with Visual Studio. For example, the following command line exports all functions in mshtml.dll :

C:\>dumpbin.exe /exports C:\WINDOWS\ie8\mshtml.dll
Microsoft (R) COFF/PE Dumper Version 9.00.30729.01
Copyright (C) Microsoft Corporation. All rights reserved.


Dump of file C:\WINDOWS\ie8\mshtml.dll

File Type: DLL

Section contains the following exports for MSHTML.dll

00000000 characteristics
41107F09 time date stamp Wed Aug 04 11:45:37 2004
0.00 version
1 ordinal base
19 number of functions
19 number of names

ordinal hint RVA name

1 0 00141BB8 CreateHTMLPropertyPage
2 1 000BDFF6 DllCanUnloadNow
3 2 0013D3C2 DllEnumClassObjects
4 3 000E1AA6 DllGetClassObject
5 4 0013D243 DllInstall
6 5 0013D319 DllRegisterServer
7 6 0013D38D DllUnregisterServer
8 7 0019BCA4 MatchExactGetIDsOfNames
9 8 00140BD5 PrintHTML
10 9 001AD7CB RNIGetCompatibleVersion
11 A 001AA454 RunHTMLApplication
12 B 001A691E ShowHTMLDialog
13 C 001A692E ShowHTMLDialogEx
14 D 001A689A ShowModalDialog
15 E 001A5FEA ShowModelessHTMLDialog
16 F 001AD7D6 com_ms_osp_ospmrshl_classInit
17 10 001ADA6E com_ms_osp_ospmrshl_copyToExternal
18 11 001ADB14 com_ms_osp_ospmrshl_releaseByValExternal
19 12 001AD80C com_ms_osp_ospmrshl_toJava

Summary

D000 .data
1F000 .reloc
1B000 .rsrc
29A000 .text

How to configure SQL Server 2005 to allow remote connections

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition

You must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point toConfiguration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expandDatabase Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply. 
   
   Note Click OK when you receive the following message:
   Changes to Connection Settings will not take effect until you restart the Database Engine service.
4. On the Surface Area Configuration for Services and Connections page, expandDatabase Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Enable the SQL Server Browser service

If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections. For example, SQL Server 2005 Express is installed with a default instance name of Computer Name\SQLEXPRESS. You are only required to enable the SQL Server Browser service one time, regardless of how many instances of SQL Server 2005 you are running. To enable the SQL Server Browser service, follow these steps.

Important These steps may increase your security risk. These steps may also make your computer or your network more vulnerable to attack by malicious users or by malicious software such as viruses. We recommend the process that this article describes to enable programs to operate as they are designed to, or to implement specific program capabilities. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this process in your particular environment. If you choose to implement this process, take any appropriate additional steps to help protect your system. We recommend that you use this process only if you really require this process.

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point toConfiguration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply. 
   
   Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
4. Click Start, and then click OK.

Note When you run the SQL Server Browser service on a computer, the computer displays the instance names and the connection information for each instance of SQL Server that is running on the computer. This risk can be reduced by not enabling the SQL Server Browser service and by connecting to the instance of SQL Server directly through an assigned TCP port. Connecting directly to an instance of SQL Server through a TCP port is beyond the scope of this article. For more information about the SQL Server Browser server and connecting to an instance of SQL Server, see the following topics in SQL Server Books Online:

• SQL Server Browser Service
• Connecting to the SQL Server Database Engine
• Client Network Configuration

Create exceptions in Windows Firewall

These steps apply to the version of Windows Firewall that is included in Windows XP Service Pack 2 (SP2) and in Windows Server 2003. If you are using a different firewall system, see your firewall documentation for more information. 

If you are running a firewall on the computer that is running SQL Server 2005, external connections to SQL Server 2005 will be blocked unless SQL Server 2005 and the SQL Server Browser service can communicate through the firewall. You must create an exception for each instance of SQL Server 2005 that you want to accept remote connections and an exception for the SQL Server Browser service.

SQL Server 2005 uses an instance ID as part of the path when you install its program files. To create an exception for each instance of SQL Server, you must identify the correct instance ID. To obtain an instance ID, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point toConfiguration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
3. On the SQL Server Browser Properties page, click the Advanced tab, locate the instance ID in the property list, and then click OK.

To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK.

Create an exception for SQL Server 2005 in Windows Firewall

To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe executable program, click Open, and then click OK. 
   
   Note The path may be different depending on where SQL Server 2005 is installed.MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.

Create an exception for the SQL Server Browser service in Windows Firewall

To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK. 
   
   Note The path may be different depending on where SQL Server 2005 is installed.