Translate

Sunday, February 19, 2012

connection pooling in .net



Connection Pooling in asp.net

If you are using asp.net to a SQL database, you will most likely use a SQL connection object. If you are using SQL connection object, something called connection pooling is enabled by default. Now the question that naturally arises is what is connection pooling?  When your asp.net application connects to SQL server, opening a connection is resource intensive and takes time. So when you call close() on a connection, instead of actually closing the connection it gets returned to a "pool of connections".  So this connection is in fact still open. Then next time you call an Open() on a sql connection, this pre existing open connection from the connection pool gets reused. This is called connection pooling. Connection pooling in managed by the data provider ( in the example below, .NET Framework Data Provider for SQL Server) .

Now what if multiple users perform an operation at the exact same time all of which require database connection? In that case new connections to the database will be opened, then all of them will be returned to the connection pool when close() is called on those connections.

In case you don't close connections and your application pool reaches the maximum number of open connections (default is 100 open connections per connection pool), trying to open another connection after that will throw this error

"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.
"

So it is necessary to close a connection after the required operation is complete.

SqlConnection.ClearAllPools() actually closes all the open SQL connections for a particular application. (if you run an EXEC sp_who in SQL server before and after the  ClearAllPools(), you can see the connections were closed.


Affect of connection poling on performance

Just for the fun of it, I ran the Query below once with the connection pooling disabled and the second time with connection pooling enabled. All I am doing is opening and closing a connection over and over, 100000 times. That would show us how expensive opening and closing a connection can be, without connection pooling.

for (int i = 0; i < 100000; i++)
{

    SqlConnection conn = new SqlConnection();

    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["PlayGroundConnectionString"].ConnectionString;

    conn.ConnectionString = connectionString;

    conn.Open();
    conn.Close();
}

These are the results


Connection Pool Enabled?
Time
False
240 seconds
True
2.4 seconds

As you can see with the connection pooling enabled, the performance gains are extra ordinary.

Connection Pooling Quiz

Q>How to disable connection pooling
A> This can be done in the connection string.

<add name="myConnectionString" connectionString="Data Source=MyServerName;Initial Catalog=MyDataBaseName;User ID=mySqlUserName;Password=myPassWord;Pooling=False;""
      providerName="System.Data.SqlClient" /> 


Q>How many connection pools can an application have?
A>Many. "Connections are pooled per process, per application domain, per connection string and when integrated security is used, per Windows identity." Even one character difference in two connection strings will result in two different connection pools. Hence it is safer to store the connection string in the web.config file. 

Q>If connection pooling is disabled will the connections still be limited to 100 open connections?
A> No.

Q>If you enable windows authentication, what is its significance with respect to connection pooling?
  <connectionStrings>
    <add name="myConnectionString" connectionString="Data Source=MyServerName;Initial Catalog=MyDataBaseName;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

A> Every time a new user logs in a new connection must be opened (because a new connection pool is created for every user). That would cause a large number of open connections if the application has a large number of users.

Q>If you reach the maximum number of permissible connections in a connection pool, how long would it wait before the next connection request is timed out?
A>15 seconds (default time out)

Q>If connection pooling is enabled, when you try to connect to the database, will you see the login event on SQL trace?
A> If an open connection was fetched from the application pool you will not see it.

Further reading

Connection Pooling (ADO.NET)

Saturday, February 18, 2012

How to read connection string from web.config (asp.net)

To read connection string from web.config do the following

1>Store connection string in web.config

For using Windows Authentication, your connection string would look like

<configuration>
  <connectionStrings>
    <add name="myConnectionString" connectionString="Data Source=MyServerName;Initial Catalog=MyDataBaseName;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

For using SQL Authentication, your connection string would look like

<configuration>
  <connectionStrings>
<add name="myConnectionString" connectionString="Data Source=MyServerName;Initial Catalog=MyDataBaseName;User ID=mySqlUserName;Password=myPassWord"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>



2>Read connection string from C# code

string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;



Thursday, February 16, 2012

Some useful tools for .net developers (Developer productivity tools)



Productivity tools:


Tool
URL
Purpose
Free?

fiddler


Monitor the traffic between the browser and the websites

Yes
Resharper
ReSharper is a renowned productivity tool that makes Microsoft Visual Studio a much better IDE.
30 day trial
lumzy
Create  mock ups for websites for the clients to view and approve before you actually start building the website.
Yes
Beyond Compare
A very useful   tool for comparing differences in files and folders.
30 day trial
Collapse all   projects within visual studio
An extension   for Visual studio that would minimize (collapse) all the projects
Yes
ANTS Memory Profiler 
Find memory   leaks in your .net application
14 day trial
SQL formatter
An online tool   for formatting your SQL code
Yes
SQL Comments

An online tool for creating SQL flower box comments
Yes
ie developer toolbar
The Internet Explorer Developer Toolbar provides several features for exploring and understanding Web pages
Yes
dual monitor taskbar





If you use 2 monitors with this tool, you can have a task bar on the second monitor too.
Yes
CLR Profiler





The CLR Profiler allows developers to see the allocation profile of their managed applications.
Yes
Test Driven .net

http://www.testdriven.net/Enables you to run gallio unit tests from within Visual studio (as opposed to using Icarus GUI test runner)Yes
Wireshark
Wireshark is a free and open-source packet analyzer. It is used for network troubleshooting, analysis, software and communications protocol development, and education. 
Yes
EPPlus
EPPlus is a .net library that reads and writes Excel 2007/2010 files.
Yes
Sticky Notes
A software that helps you save notes.
Yes
JSON
Viewer
    http://jsonviewer.stack.hu/
An online tool that lets you parse JSON
Yes
Nmap
A tool that scans your local network for open ports and servers.
Yes
dotpeek
Decompiles ,net dlls into C# code
Yes
Windows Sysinternals
Various tools from Microsoft to trouble shoot and diagnose Windows applications.
Yes
Namespaces and the dlls they belong to

Yes
Moment.js
For easier date manipulation in javascript
Yes
SQL Search
A free tool to search all objects within your SQL server databases.
Yes
Microsoft Windows SDK for Windows 7 and .NET Framework 4
WinDbg a multipurpose debugger for Microsoft Windows comes with this installation.
Yes
Linqpad
http://www.linqpad.net/
 Linqpad lets you interactively query databases and OData webservices in LINQ
Basic version is free.


Ready made controls:

Plugin
URL
Purpose
Free

teleric


Ready made UI controls, Productivity tools and data tools

No
Infragistics
UI components and UI testing tools
No


Some other Useful software


Software
URL
Purpose
Free
Gimp
Image editor
Yes
Json.Net
(Newtonsoft.Json)
http://james.newtonking.com/projects/json-net.aspx
Json.NET is a popular high-performance JSON framework for .NET
Yes
lockhunter
http://lockhunter.com/
A tool to free up locked files so that you can delete them.
Yes





















Wednesday, February 15, 2012

Last entered row SQL Server

 These are the various choices you have to get the latest identity entered into a table in SQL server


SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT(‘tablename’)


The most often I personally use is SELECT SCOPE_IDENTITY()



I will defer to an excellent article from Pinal Dave for an explanation on each of these