small talk about Database Connections
I had a customer who had this PROBLEM. He said " My Connection strings have changed! I don’t know how to get the right connectionstring in my application, and with .NET’s automatic garbage collection, I’m never quite sure what’s going on with database connections in my application. "
After my best effort to calm him down , i tried to explain to him the database connections as i had understood w.r.t .NET :
Almost any application these days will store data somewhere. If there’s a substantial amount of data involved, the obvious choice for storage is a database manager. For Visual Basic .NET applications, SQL Server (or its stripped-down cousin, MSDE [Microsoft Data Engine]) is the usual choice. But even if you’ve worked with databases in earlier versions of Visual Basic, you may feel a bit lost when you first start dealing with databases in Visual Basic .NET. The format of SQL Server connection strings has changed a bit, and there are new rules about using connections efficiently. Fortunately, the .NET Framework and the Visual Studio .NET IDE both offer good support for working with database connections.
Creating and Testing Database Connections
You could sit down with the help of the System.Data.SqlClient.SqlConnection.Connection String property and try to memorize the options that apply to connection strings. But there’s an easier way to get the right connection string for your application. Visual Studio .NET includes a tool called Server Explorer that lets you build database connections using a familiar graphical interface.
When Do You Need an Open Connection?
The short answer is that you need an open connection only when you’re actually exchanging data with a database. This breaks down into three cases, depending on which class you’re using to communicate with the database:
If you’re executing a SqlCommand object (for example, to call a stored procedure in the database), then you must call the Open method of the associated SqlConnection object before calling any of the Execute methods. You can call the Close method of the Sql-Connection object as soon as the Execute call has completed.
If you’re retrieving data with a SqlDataReader object, you must call the Open method of the associated SqlConnection object before reading any data. The connection must remain open as long as you’re reading data.
If you’re using a SqlDataAdapter object to exchange data between a DataSet object and a database, then the connection must be open when you call the Fill or Update methods of SqlDataAdapter. There’s no need to keep the connection open after filling the DataSet. The data in the DataSet will remain valid, and you can open the connection again to callthe Update method at a later time.
In fact, if you’re using the SqlDataAdapter/DataSet combination, you never need to call the Open method of the associated SqlConnection object at all. The Framework takes care of opening and closing the connection as necessary. This guarantees the shortest possible time for open connections.
Understanding the Connection Pool
In general, you don’t worry about the exact time that a .NET Framework object ceases to exist. One of the features of .NET is automatic garbage collection. When you call the Dispose method of an object, or drop all references to the object, the .NET Framework knows that it’s safe to destroy the object. But exactly when it will decide to do so is determined by a complex set of internal algorithms.
This complicates things for relatively expensive objects such as database connections. Ideally, you’d like to manage your connections by getting rid of them when they’re no longer needed. This can save server resources as well as local application resources. Connection management is important for desktop applications that need to cooperate with other applications in the enterprise, but it’s even more important for ASP.NET, remoting, Web Services, and other distributed applications. An ASP.NET application, for example, might have hundreds or thousands of simultaneous sessions in progress. If each of those sessions required its own connection to the database, the drain on server resources would be severe. Fortunately, ADO.NET supports connection pooling, which lets a group of processes share a relatively small number of database connections.
A connection pool is a set of SQL Server connections maintained by ADO.NET that are used to communicate with SQL Server. This is important to the client as well as to the server, because the act of making a SQL Server connection is itself fairly costly. When ADO.NET opens a SqlConnection object, this delays the application. To minimize this delay, ADO.NET doesn’t throw SqlConnection objects away when you call their Close or Dispose methods. Instead, it returns them to the connection pool. If a future request for a SqlConnection can be satisfied by a pooled connection, then ADO.NET recycles that connection instead of opening a new one.
In general ADO.NET determines whether a pooled connection can be recycled by checking three things:
- Is the connection currently unused?
- Does the connection string match the desired connection string exactly?
- Does the transaction context of the thread match the desired transaction context exactly?
After this the customer was fairly convinced and we proceeded ahead with the troubleshooting ;-)