Mobile Application Development in ASP.NET

Introduction

Mobile application development in ASP.NET is similar to traditional ASP.NET web application development. And it is very easy for ASP.NET developer to develop mobile application. All mobile web pages are inherit from MobilePage class which exists in System.Web.UI.MobileControls namespace.ASP.NET exposes a System.Web.Mobile namespace is for specifically to Web development.

Background

In this demonstration, you will create a mobile web page that dedicated to mobile device. The page will show a loan repament calculator and after passing valid parameter it will show repament amount of a pricipal amount with terms and rate.

Creating Web Application in ASP.NET

Click to open Microsoft Visual Studio 2008
On the File menu , choose New, and then choose Web Site.
The New Web Site dialog box appears.
Under Visual Studio installed templates, select ASP.NET Web Site.
Click Browse .
The Choose Location dialog box appears.
Location File System and LRC
Language Visual C#
Click OK button
A Default.aspx is added in your solution and it is traditional ASP.NET page which is inherited from System.Web.UI.Page. But you need to create page which inherit from MobilePage class in System.Web.UI.MobileControls namespace. In this demonstration, you will use controls from the System.Web.Mobile namespace that are specifically designed for devices that cannot display as much information as a desktop browser.

Creating Mobile Web Page in Application

Right-click the Default.aspx page in Solution Explorer and choose Delete.
Click OK in the dialog box.
Right-click the application in Solution Explorer and choose Add New Item
Choose Mobile Web Form under Visual Studio installed templates.

Figure 1

Dowanload(Download MobileWebFromTemplate.zip - 16.12 KB) mobile page template if you do not have mobile form template in Add New Item box and place tempalate according to instruction provided in readme file. After extrating MobileWebFromTemplate.rar file you will get two folder a 'Web Application' and another is 'Web Site'. Place RAR files in Web Application folder to '[My Documents]\Visual Studio 2008\Templates\ItemTemplates\Visual C#' and RAR files in Web Site folder to '[My Documents]\Visual Studio 2008\Templates\ItemTemplates\Visual Web Developer'. Now you will get Mobile Web Form template.

Name Loan_RepaymentCalculator.aspx
Choose Language Visual C#
Check Place code in separate file.
Click Add in the dialog box
Right click on Loan_RepaymentCalculator.aspx choose View Code define namespace for Loan_RepaymentCalculator class.

Collapse
namespace STL.Web.Mobile.UI
{
public partial class Loan_RepaymentCalculator : System.Web.UI.MobileControls.MobilePage
{
}
}
Set Inherits attribute value STL.Web.Mobile.UI.Loan_RepaymentCalculator in page directive of Loan_RepaymentCalculator's source file.

Collapse
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Loan_RepaymentCalculator.aspx.cs"
Inherits="STL.Web.Mobile.UI.Loan_RepaymentCalculator" %>

Design Mobile Web Page

In solution explorer double click on Loan_RepaymentCalculator.aspx to view source code and you will find mobile form form1 rename it as frmInput.From the Mobile Web Forms folder of the Toolbox, drag controls onto frmInput and set their properties as defined in the following.

1. Label control
a. ID = "lblHeading"
b. Runat = "Server"
c. EnableViewState = "False"
d. Wrapping = "Wrap"
e. StyleReference="StyleHeader"

2. Label control
a. ID = "lblPrincipal"
b. Runat = "Server"
c. EnableViewState = "False"
d. Text = "1. Amount"
e. StyleReference="StyleLabel"

3. TextBox control
a. ID = "PrincipalAmount"
b. Runat = "Server"
c. Numeric = "True"
d. MaxLength = "12"
e. Size = "10"
f. Title = "Principal Amount"
g. StyleReference="StyleTextBox"

4. RequiredFieldValidator control for validating principal amount that expect input from user.
a. ID = "rfvPrincipal"
b. Runat = "Server"
c. ControlToValidate ="PrincipalAmount"
d. ErrorMessage = "Amount Empty!"
e. StyleReference="StyleValidation"

5. RegularExpressionValidator control for validating principal amount that expect only numeric(fractional) value from user .
a. ID = "revPrincipal"
b. Runat = "Server"
c. ControlToValidate = "PrincipalAmount"
d. ErrorMessage = "Invalid Amount!"
e. ValidationExpression = "^([0-9]+)?\.?\d{1,2}"
f. StyleReference="StyleValidation"

6. Label control
a. ID = "lblTerm"
b. Runat = "Server"
c. EnableViewState = "False"
d. Text = "2. Term(Year)"
e. StyleReference="StyleLabel"

7. TextBox control
a. ID = "Term"
b. Runat = "Server"
c. Numeric = "True"
d. MaxLength = "6"
e. Size = "10"
f. Title = "Term"
g. StyleReference="StyleTextBox"

8.RequiredFieldValidator control for validating term that expect input from user.
a. ID = "rfvTerm"
b. Runat = "Server"
c. ControlToValidate ="Term"
d. ErrorMessage ="Term Empty!"
e. StyleReference="StyleValidation"

9. RegularExpressionValidator control for validating term that expect only numeric(not fractional) value from user .
a. ID = "revTerm"
b. Runat = "Server"
c. ControlToValidate = "Term"
d. ErrorMessage = "Invalid Amount!"
e. ValidationExpression = "^[1-9]([0-9]+)?"
f. StyleReference="StyleValidation"

10. Label control
a. ID = "lblRate"
b. Runat = "Server"
c. EnableViewState = "False"
d. Text = "3. Rate(%)"
e. StyleReference="StyleLabel"

11. TextBox control
a. ID = "Rate"
b. Runat = "Server"
c. Numeric = "True"
d. MaxLength = "5"
e. Size = "10"
f. Title = "Rate"
g. StyleReference="StyleTextBox"

12. RequiredFieldValidator control for validating rate that expect input from user.
a. ID = "rfvRate"
b. Runat = "Server"
c. ControlToValidate ="Rate"
d. ErrorMessage ="Rate Empty!"
e. StyleReference="StyleValidation"

13. RangeValidatorcontrol for validating rate that expect only numeric value between 1 to 100 from user.
a. ID = "rvRate"
b. Runat = "Server"
c. Type="Double"
d. ControlToValidate = "Rate"
e. ErrorMessage = "Invalid Rate!"
f. MinimumValue="0"
g. MaximumValue="100"
h. StyleReference="StyleValidation"

14. Command control
a. ID = "cmdRepayment"
b. Runat = "Server"
e. Text = "Repayment"
f. OnClick="cmdRepayment_Click"

The Command control provides a way to invoke ASP.NET event handlers from UI elements, thus posting user input from UI elements back to the server. The command is for calculate repayment. Event OnClick of cmdRepayment is bind with cmdPayment_Click event procedure, it will disscus later in this demonestration.

The Form mobile control enables you to break up complex pages into a collection of forms on a mobile Web page. With this ability, you can minimize the effort required to port Web-based applications to mobile devices.

ASP.NET mobile web page can contain more than one form control and mobile application displays only one form at a time. And a form control cannot be a inner element of another form control.

Add second form control into Loan_RepaymentCalculator.aspx page after frmInput from the Mobile Web Forms folder of the Toolbox, and define form control ID is frmResult

Now from the Mobile Web Forms folder of the Toolbox, drag controls onto frmResult and set their properties as defined in the following.


1. Label control
a. ID = "lblHeadingResult"
b. Runat = "Server"
c. EnableViewState = "False"
d. Wrapping = "Wrap"
e. StyleReference="StyleHeader"

2. TextView control to display result
a. ID = "tvLoanDetails"
b. Runat = "Server"
c. EnableViewState = "False"
d. StyleReference="StyleLabelResult"

3. Command control it is a navigation button to go previous form control
a. ID = "cmdBack"
b. Runat = "Server"
e. Text = "Back"
f. OnClick="cmdBack_Click"

Event OnClick of the cmdBack command button bind with cmdBack_Click event procedure will discuss later in this demonstration.

Add last form control into Loan_RepaymentCalculator.aspx page after frmResult from the Mobile Web Forms folder of the Toolbox, and define form control ID is frmError. If runtime error occurs application will show this error form.

Now from the Mobile Web Forms folder of the Toolbox, drag controls onto frmError and set their properties as defined in the following.

1. Label control
a. ID = "lblHeadingError"
b. Runat = "Server"
c. EnableViewState = "False"
d. Wrapping = "Wrap"
e. StyleReference="StyleHeader"

2. TextView control to display error
a. ID = "tvError"
b. Runat = "Server"
c. EnableViewState = "False"
d. Text ="Sorry For Inconvenience!"
e. StyleReference="StyleError"

3. Command control it is a navigation button to go previous form control
a. ID = "cmdHome"
b. Runat = "Server"
e. Text = "Home"
f. OnClick="cmdBack_Click"

Event OnClick of the cmdBack command button bind with cmdBack_Click event procedure will disscuss later in this demonstration.

StyleSheet

StyleSheet can be internal or external in mobile ASP.NET application. External stylesheet is for entire application while internal stylesheet only for page specific. The stylesheet control is need to implement style in application. Stylesheet control can contain any number of style elements, or elements that inherits from the style element. Each style element must have a unique name property. You can use the Name property to refer to each Style element in the StyleSheet control from other controls on the same MobilePage object.

To create the external style sheet, you create a user control, in an .ascx file, and place a single style-sheet control with a set of styles in it. Then, to refer to this file, you place a style-sheet control on the page and set its ReferencePath property to the relative URL of the user control.

Now add a StyleSheet folder in LRC Application. To do this follow the below steps:
1. Right Click on LRC application
2. Choose New Folder
3. Rename it StyleSheet

Add Mobile Web User Control in StyleSheet folder. Follow the below steps:

1. Right Click on StyleSheet folder in LRC application
2. Choose Add New Item

Add New Item dialogbox appear as below,


Figure 2

3. Name LRC_StyleSheet.ascx
4. Labguage Visual C#
5. Click Add in the dialog box.

set STL.Web.Mobile.UI namespace for LRC_StyleSheet class in LRC_StyleSheet.ascx.cs file and Set
Inherit="STL.Web.Mobile.UI.LRC_StyleSheet" in control directive of LRC_StyleSheet's source file.

To define style sheet you need to add a StyleSheet control on the page from Toolbox under Mobile Web Forms Folder. And define styles as bellow:

Collapse

Name="StyleForm" Font-Size="Small">

Name="StyleHeader" ForeColor="#999966" Font-Size="Small" Font-Bold="True">

Name="StyleLabel" ForeColor="#cc3399" Font-Size="Small" Font-Bold="False">

Name="StyleTextBox" ForeColor="#cc3399" Font-Size="Small" Font-Bold="False">

Name="StyleValidation" ForeColor="Red" Font-Size="Small" Font-Bold="False">

Name="StyleLabelResult" ForeColor="#cc0066" Font-Size="Small" Font-Bold="False">

Name="StyleError" ForeColor="Red" Font-Size="Small">


To add style reference from this external StyleSheet into Loan_RepaymentCalculator.aspx, Just go to the source of this page and add a StyleSheet control from the
Toolbox under Mobile Web Froms add set ReferencePath="~/StyleSheet/LRC_StyleSheet.ascx"

Collapse


Now you can a add StyleReference in elements of a mobile web page.

Collapse



Class

Add a class under STL.Web.Mobile.UI namespace in LRC Application for UI constants

Steps:
1. Right Click on the App_Code folder
2. Choose Add New Item
3. Choose Class
4. Name UIConstant.cs
5. Click Add in the dialog box.

Figure 3

Add constants in UIConstant.cs files
Collapse

namespace STL.Web.Mobile.UI
{
public class UIConstant
{
private UIConstant()
{
}
public const String TITLE_BAR="Loan Payment Calculator";
public const String PAGE_TITLE = "Loan Payment Calculator";
}
}

Events

Add Microsoft.VisualBasic.dll reference in application to calculate monthly payment using Financial.Pmt method.

Steps:
1. Right Click on the LRC Application
2. Choose Add Reference
3. Choose Microsoft.VisualBasic
4. Click Add in the dialog box.

Figure 4

Using Microsoft.VisualBasic namespace in Loan_RepaymentCalculator.aspx.cs file

Collapse
using Microsoft.VisualBasic;
OnClick event of cmdRepayment command in frmInput form is as bellow

Collapse

protected void cmdRepayment_Click(object sender, EventArgs e)
{
if (!Page.IsValid) return;
try
{
Double dblPrincipal = double.Parse(this.PrincipalAmount.Text);
Double dblApr = double.Parse(this.Rate.Text);
Double dblMonthlyInterest = (Double)(dblApr / (12 * 100));
Int64 intTermInMonths = Int64.Parse(this.Term.Text) * 12;
Double dblMonthlyPayment;
//Calculate monthly payment
dblMonthlyPayment = Microsoft.VisualBasic.Financial.Pmt(dblMonthlyInterest, intTermInMonths, -dblPrincipal, 0, Microsoft.VisualBasic.DueDate.BegOfPeriod);
this.ActiveForm = this.frmResult;
StringBuilder sbDetailsSpec = new StringBuilder("");
sbDetailsSpec.Append(String.Format("{0} @ {1}% for {2} years
Payment: ", dblPrincipal.ToString ("C0"), dblApr.ToString(), this.Term.Text));
sbDetailsSpec.Append("" + dblMonthlyPayment.ToString("C") + "");
this.tvLoanDetails.Text = sbDetailsSpec.ToString();
}
catch
{
//If runtime error occurs then go to error form.
this.ActiveForm = frmError;
}
}

OnClick event of cmdBack command in frmInput form is as bellow

Collapse
protected void cmdBack_Click(object sender, EventArgs e)
{
//To back to input form
this.ActiveForm = this.frmInput;
}

Initialize user method to initialize elements in the mobile web page

Collapse

private void Initialize()
{
this.frmInput.Title = UIConstant.TITLE_BAR;
this.frmResult.Title = UIConstant.TITLE_BAR;
this.frmError.Title = UIConstant.TITLE_BAR;

this.lblHeading.Text = UIConstant.PAGE_TITLE;
this.lblHeadingResult.Text = UIConstant.PAGE_TITLE;
this.lblHeadingError.Text = UIConstant.PAGE_TITLE;
}

Load event of the page

Collapse
protected void Page_Load(object sender, EventArgs e)
{
Initialize();
}
Application Level Errors

To handle application level error you need to add a error page. To add page

Steps:
1. Right click on the LRC application
2. Choose Add New Item
3. Name ErrorPage.aspx
4. Click Add in the dialog

Set Inherits="STL.Web.Mobile.UI.ErrorPage" in page directive of ErrorPage.aspx. And define STL.Web.Mobile.UI namespace for
ErrorPage

Collapse
namespace STL.Web.Mobile.UI
{
public partial class ErrorPage : System.Web.UI.MobileControls.MobilePage
{
}
}
Add a StyleSheet control in the page and set ReferencePath="~/StyleSheet/LRC_StyleSheet.ascx"

Add a form control in the ErrorPage and set ID="frmError"

Add control in frmError that is defined as following:

1. Label control
a. ID = "lblHeadingError"
b. Runat = "Server"
c. EnableViewState = "False"
d. Wrapping = "Wrap"
e. StyleReference="StyleHeader"

2. TextView control to display error
a. ID = "tvError"
b. Runat = "Server"
c. EnableViewState = "False"
d. Text ="Sorry For Inconvenience!"
e. StyleReference="StyleError"

3. Command control it is a navigation button to go previous form control
a. ID = "cmdHome"
b. Runat = "Server"
e. Text = "Home"
f. OnClick="cmdBack_Click"

Code of the ErrorPage file is as follows:

Collapse
public partial class ErrorPage : System.Web.UI.MobileControls.MobilePage
{
#region Event

protected void Page_Load(object sender, EventArgs e)
{
Intitalize();
}

protected void cmdHome_Click(object sender, EventArgs e)
{
//To redirect to Loan_RepaymentCalculator page
Response.Redirect("~/Loan_RepaymentCalculator.aspx");
}

#endregion Event

#region Method

private void Intitalize()
{
this.frmError.Title = UIConstant.TITLE_BAR;
this.lblHeadingError.Text = UIConstant.PAGE_TITLE;
}

#endregion Method
}

Web.config

You nedd to change configuration in Web.config file to redirect to Error Page when application level error is occured.
set mode="on" and defaultRedirect="~/ErrorPage.aspx" in customErros element under System.Web element.

Test Application

To test the application you can use Microsoft Mobile Explorer 3.0 . If not avilable Microsoft Mobile Explorer 3.0 you can use your desktop browser or free download it from net. Install Microsoft Mobile Explorer 3.0 in your system.

To browse with Microsoft Mobile Explorer 3.0 you need to do as follows:
1. Right click on Loan_RepaymentCalculator.aspx file
2. Choose Browse With
( If Microsoft Mobile Explorer 3.0 is not avilable in Browsers list of Browse With dialog, you need add it)
3. Click Add
4. Browse your location where you installed Microsoft Mobile Explorer 3.0 (mmeemu.exe)
5. Select Microsoft Mobile Explorer
6. Click Set as Default in the dialog box.
Figure 5



Press F5 to run the application. Microsoft Mobile Explorer Emulator will appear. Click ASP.NET Development Server icon in the system tray to get application URL name
and its port. It may be different in your system.

Figure 6



In the Microsoft Mobile Explorer Emulator type URL as http://localhost:1439/LRC/Loan_RepaymentCalculator.aspx


Figure 7



Enter Amount, Term & Rate. Click on Repayment button in the screen. You will get result like bellow,


Figure 8

Tools

For testing application in Mobile Emulator
http://devhood.com/tools/tool_details.aspx?tool_id=52
http://www.codeproject.com/KB/aspnet/Loan_RepaymentCalculator.aspx

SQL Server Connection Pooling (ADO.NET)

Table of Contents

ADO.NET Connection Pooling at a Glance
Connection Pool Creation
Connection Pool Deletion / Clearing Connection Pool
Controlling Connection Pool through Connection String
Sample Connection String with Pooling Related Keywords
Simple Ways to View Connections in the Pool Created by ADO.NET
Common Issues/Exceptions/Errors with Connection Pooling
Points to Ponder
Other Useful Reads/References on Connection Pooling
Wrapping up


ADO.NET Connection Pooling at a Glance

Establishing a connection with a database server is a hefty and high resource consuming process. If any application needs to fire any query against any database server, we need to first establish a connection with the server and then execute the query against that database server.

Not sure whether you felt like this or not; when you are writing any stored proc or a query, the query returns the results with better response time than the response time, when you execute that same query from any of your client applications. I believe, one of the reasons for such behavior is the overheads involved in getting the desired results from the database server to the client application; and one of such overheads is establishing the connection between the ADO.

Web applications frequently establish the database connection and close them as soon as they are done. Also notice how most of us write the database driven client applications. Usually, we have a configuration file specific to our application and keep the static information like Connection String in it. That in turn means that most of the time we want to connect to the same database server, same database, and with the same user name and password, for every small and big data.

ADO.NET with IIS uses a technique called connection pooling, which is very helpful in applications with such designs. What it does is, on first request to database, it serves the database call. Once it is done and when the client application requests for closing the connection, ADO.NET does not destroy the complete connection, rather it creates a connection pool and puts the released connection object in the pool and holds the reference to it. And next time when the request to execute any query/stored proc comes up, it bypasses the hefty process of establishing the connection and just picks up the connection from the connection pool and uses that for this database call. This way, it can return the results comparatively faster.

Let us see Connection Pooling Creation Mechanism in more detail.

Connection Pool Creation

Connection pool and connection string go hand in hand. Every connection pool is associated with a distinct connection string and that too, it is specific to the application. In turn, what it means is – a separate connection pool is maintained for every distinct process, app domain and connection string.

When any database request is made through ADO.NET, ADO.NET searches for the pool associated with the exact match for the connection string, in the same app domain and process. If such a pool is not found, ADO.NET creates a new one for it, however, if it is found, it tries to fetch the usable connection from that pool. If no usable free connection is found in the pool, a new connection is created and added to the pool. This way, new connections keep on adding to the pool till Max Pool Size is reached, after that when ADO.NET gets request for further connections, it waits for Connection Timeout time and then errors out.

Now the next question that arises is - How is any connection released to the pool to be available for such occasions? Once any connection has served and is closed/disposed, the connection goes to the connection pool and becomes usable. At times, connections are not closed/disposed explicitly, these connections do not go to the pool immediately. We can explicitly close the connection by using Close() or Dispose() methods of connection object or by using the using statement in C# to instantiate the connection object. It is highly recommended that we close or dispose (don't wait for GC or connection pooler to do it for you) the connection once it has served the purpose.


Connection Pool Deletion / Clearing Connection Pool

Connection pool is removed as soon as the associated app domain is unloaded. Once the app domain is unloaded, all the connections from the connection pool become invalid and are thus removed. Say for example, if you have an ASP.NET application, the connection pool gets created as soon as you hit the database for the very first time, and the connection pool is destroyed as soon as we do iisreset. We'll see it later with example. Note that connection pooling has to do with IIS Web Server and not with the Dev Environment, so do not expect the connection pool to be cleared automatically by closing your Visual Studio .NET dev environment.

ADO.NET 2.0 introduces two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections in use at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.

Refer to the section "Simple Ways to View Connections in the Pool Created by ADO.NET" for details on how to determine the status of the pool.


Controlling Connection Pool through Connection String

Connection string plays a vital role in connection pooling. The handshake between ADO.NET and database server happens on the basis of this connection string only. Below is the table with important Connection pooling specific keywords of the connection strings with their description.

Name	Default	Description
Connection Lifetime	0	When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified byConnection Lifetime. A value of zero (0) causes pooled connections to have the maximum connection timeout.
Connection Timeout	15	Maximum Time (in secs) to wait for a free connection from the pool
Enlist	'true'	When true, the pooler automatically enlists the connection in the creation thread's current transaction context. Recognized values aretrue, false, yes, and no. Set Enlist = "false" to ensure that connection is not context specific.
Max Pool Size	100	The maximum number of connections allowed in the pool.
Min Pool Size	0	The minimum number of connections allowed in the pool.
Pooling	'true'	When true, the SQLConnection object is drawn from the appropriate pool, or if it is required, is created and added to the appropriate pool. Recognized values are true, false, yes, and no.
Incr Pool Size	5	Controls the number of connections that are established when all the connections are used.
Decr Pool Size	1	Controls the number of connections that are closed when an excessive amount of established connections are unused.

* Some table contents are extracted from Microsoft MSDN Library for reference.

Other than the above mentioned keywords, one important thing to note here. If you are using Integrated Security, then the connection pool is created for each user accessing the client system, whereas, when you use user id and password in the connection string, single connection pool is maintained across for the application. In the later case, each user can use the connections of the pool created and then released to the pool by other users. Thus using user id and password are recommended for better end user performance experience.

Sample Connection String with Pooling Related Keywords


The connection string with the pooling related keywords would look somewhat like this
initial catalog=Northwind; Data Source=localhost; Connection Timeout=30;
User Id=MYUSER; Password=PASSWORD; Min Pool Size=20; Max Pool Size=200;
Incr Pool Size=10; Decr Pool Size=5;


Simple Ways to View Connections in the Pool Created by ADO.NET


We can keep a watch on the connections in the pool by determining the active connections in the database after closing the client application. This is database specific stuff, so to see the active connections in the database server we must have to use database specific queries. This is with the exception that connection pool is perfectly valid and none of the connections in the pool is corrupted.

For Microsoft SQL Server: Open the Query Analyser and execute the query : EXEC SP_WHO.

For Oracle : Open SQL Plus or any other editor like PL/SQL Developer or TOAD and execute the following query -- SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL.

All right, let us do it with SQL Server 2000:

Create a Sample ASP.NET Web Application
Open an instance of Query Analyzer and run the EXEC SP_WHO query. Note the loginname column, and look for MACHINENAME\ASPNET. If you have not run any other ASP.NET application, you will get no rows with loginname as MACHINENAME\ASPNET.
On Page load of default startup page, add a method that makes a database call. Say your connection string is "initial catalog=Northwind; Min Pool Size=20;Max Pool Size=500; data source=localhost; Connection Timeout=30; Integrated security=sspi".
Run your ASP.NET application
Now repeat Step 2 and observe that there are exactly 20 (Min Pool Size) connections in the results. Note that you made the database call only once.
Close the Web page of your Web application and repeat step 2. Observe that even after you closed the instance of the Web page, connections persist.
Now Reset the IIS. You can do that by executing the command iisreset on the Run Command.
Now Repeat Step 2 and observe that all the 20 connections are gone. This is because your app domain has got unloaded with IIS reset.


Common Issues/Exceptions/Errors with Connection Pooling

You receive the exception with the message: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached" in your .NET client application.

This occurs when you try using more than Max Pool Size connections. By default, the max pool size is 100. If we try to obtain connections more than max pool size, then ADO.NET waits for Connection Timeout for the connection from the pool. If even after that connection is not available, we get the above exception.

Solution(s):

The very first step that we should do is – Ensure that every connection that is opened, is closed explicitly. At times what happens is, we open the connection, perform the desired database operation, but we do not close the connection explicitly. Internally it cannot be used as an available valid connection from the pool. The application would have to wait for GC to claim it, until then it is not marked as available from the pool. In such case, even though you are not using max pool size number of connection simultaneously, you may get this error. This is the most probable cause of this issue.
Increase Max Pool Size value to a sufficient Max value. You can do so by including "Max Pool Size = N;" in the connection string, where N is the new Max Pool size.
Set the pooling Off. Well, this indeed is not a good idea as connection pooling puts a positive performance effect, but it definitely is better than getting any such exceptions.
You receive the exception with the message: "A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - Shared Memory Provider: )" in your ASP.NET application with Microsoft SQL Server.

This occurs when Microsoft SQL Server 2000 encounters some issues and has to refresh all the connections and ADO.NET still expects the connection from the pool. Basically, it occurs when connection pool gets corrupted. What in turn happens is, ADO.NET thinks that the valid connection exists with the database server, but actually, due to database server getting restarted it has lost all the connections.

Solution(s):

If you are working with .NET and Oracle using ODP.NET v 9.2.0.4 or above, you can probably try adding "Validate Connection=true" in the connection string. Well, in couple of places, I noticed people saying use "validcon=true" works for them for prior versions on ODP.NET. See which works for you. With ODP.NET v 9.2.0.4, "validcon=true" errors out and "Validate Connection=true" works just fine.
If you are working with .NET 2.0 and Microsoft SQL Server, You can clear a specific connection pool by using the static (shared in Visual Basic .NET) method SqlConnection.ClearPool or clear all of the connection pools in an appdomain by using the SqlConnection.ClearPools method. Both SqlClient and OracleClient implement this functionality.
If you are working with .NET 1.1 and Microsoft SQL Server:
In the connection string, at run time, append a blank space and try establishing the connection again. What in turn it would do is, a new connection pool would be created and will be used by your application, In the meantime the prior pool will get removed if it's not getting used.
Do exception handling, and as soon as you get this error try connection afresh repeatedly in the loop. With time, ADO.NET and database server will automatically get in sync.
Well, I am not totally convinced with either approach, but frankly speaking, I could not get any better workable solution for this so far.
Leaking Connections

When we do not close/dispose the connection, GC collects them in its own time, such connections are considered as leaked from pooling point of view. There is a strange possibility that we reach max pool size value and at that given moment of time without actually using all of them, having couple of them leaked and waiting for GC to work upon them. This would actually lead to the exception mentioned above, even if we are not using max pool size number of connections.

Solution:

Ensure that we Close/Dispose the connections once its usage is over.

http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx