Static class

Hi

I required  one clarification among the followig.

can i use the connection pooling instead of static class, where connection pooling

will return the same object for same connectionstring and static class will return

the same object.

if so In what situation i have to use each of them.
praveen1981Asked:
Who is Participating?
 
Jacques Bourgeois (James Burger)PresidentCommented:
A static class is a class on which you do not instantiate objects. That is a class where you do not need to create a variable to call its methods. A simple example is the System.IO.File class. You can call any of its methods simply by specifying the class:

System.IO.Delete("foo.txt") is sufficient to delete a file.

If you have been programming in VB for a while, a module is a static class.

Connection pooling is something entirely different, but it might look the same at first sight. As kaufmed pointed, this is handled by the system, so it is completely transparent for you. It works as the application level, so it does not come into play if 2 applications are using the database at the same time.

It works the following way. See for yourself if it suits your scenario.

When the application first opens a connection with a given connection string, a connection is physically opened on the database (this requires a little time and some resources).

When you close the connection in your code, the connection is not really close on the server. It stays opened for a period that is set to a given interval (5 minutes by default according to tests I made a few years ago).

If during that period you open a new connection (Connection object/variable) with the exact same connection string, instead of reopening a new connection on the database, the pooler will reuse the connection that is already opened. This saves the overhead of reopening the connection (sending a command to the database, validating the user, etc.).

If you do not reopen a connection with the same string during that delay, a command is send to the server to close the connection. It will be recreated the next time you call Open again on a connection object set to that ConnectionString.

This has many benefits. You do not have to use a public (global) variable to maintain a connection, local variables do the job and do not add the overhead of closing and opening the connection all the time. If a user goes away for some reason and stops using his computer for a period of time, you do not hold a connection opened uselessly on the server. The server connection is freed for another user, but the application connection will reopen when needed.

All of this without anything to do on your side. Simply Open, work, Close, and the system will optimize the database connection for you.

Note that there is a little more than that to connection pooling. For instance, unless you add special instructions in the connection string, you can end up with multiple connections on the database from one application (see MARS - Multiple Active Recordsets in online help).

But typically, when connections are used properly (local variables that don't leave the connection opened when they are done), connection pooling is a way to enhance performance on the server.
0
 
käµfm³d 👽Commented:
You are talking about two different things, I think. A static class (module in VB) is something you use in code; connection pooling is something the runtime handles for you and something it coordinates with the database. Connection pooling allows you to maintain open (TCP) connections to the database so that when you say New SqlConnection it takes less time to process. When you dispose of your SqlConnection, the underlying TCP connection isn't closed or terminated, rather it is returned back to the pool. You would still pass the same connection string to New SqlConnection. There is just a bit less work for the runtime to do when it tries to establish a TCP connection to the database--it's already done.
0
 
käµfm³d 👽Commented:
@JamesBurger
For instance, unless you add special instructions in the connection string, you can end up with multiple connections on the database from one application (see MARS - Multiple Active Recordsets in online help).
Your comment is not clear. Are you saying that MARS uses multiple connections? If so, then that is not what I am reading in the documentation.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Jacques Bourgeois (James Burger)PresidentCommented:
Read carefully, I am saying the reverse: "unless you add special instructions"

If you do not use MARS, the following will create 2 connections, even if both objects have the exact same connection string:

con1.Open
...
con2.Open
...
con2.Close
con1.Close

But if you set MARS in the connection string, the pooling will use the same connection for both.
0
 
käµfm³d 👽Commented:
MARS is something different than connection pooling. Even without MARS you can avoid having multiple connections open. MARS is for passing more than one query in the same query execution.

e.g.

cmd.CommandText("SELECT * FROM table1; SELECT * FROM table2");

Open in new window


So I'm afraid I still don't understand what you are saying. Sorry  : \
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
MARS is not connection pooling, but in influence how connection pooling works.

In your CommandText, you won't have 2 connections, even without MARS, because you are executing 2 queries one after the other and on the same connection object.

MARS might mostly be needed when you use a DataReader or in some transaction scenarios.

When you use a DataReader, the query is not "released" until after you close the DataReader. During all the time that the DataReader is opened, the connection that it uses cannot be reused for another operation. A typical situation would be to need 2 DataReaders opened simultaneously

Dim con As SqlConnection = CreateConnection()

Using cmd As New SqlCommand("InventoryGetType", con)
	cmd.CommandType = CommandType.StoredProcedure
	cmd.Parameters.AddWithValue("@MaterialTypeID", materialTypeID)
	cmd.Parameters.AddWithValue("@Full", full)
	cmd.Parameters.AddWithValue("@Sorted", sorted)

	Dim drd As SqlDataReader
	drd = cmd.ExecuteReader(CommandBehavior.CloseConnection)
	drd.Read()

	Dim cmd2 As New SqlCommand("SELECT PartID FROM Part", con)
	Dim drd2 As SqlDataReader
	drd2 = cmd2.ExecuteReader(CommandBehavior.CloseConnection)
	drd2.Read()

End Using	 'cmd

Open in new window

Although both datareaders use the same connection object, there will be 2 connections on the server.

You would get the same problem in the following. Because we are reusing the same connection string everywhere in the application (provided by CreateConnection), we might think that we will always get connection pooling. And because the 2 DataReaders are created in different methods, it might not be evident at first sight that we will have 2 connections on the server.

Dim con As SqlConnection = CreateConnection()

Using cmd As New SqlCommand("InventoryGetType", con)
	cmd.CommandType = CommandType.StoredProcedure
	cmd.Parameters.AddWithValue("@MaterialTypeID", materialTypeID)
	cmd.Parameters.AddWithValue("@Full", full)
	cmd.Parameters.AddWithValue("@Sorted", sorted)

	Dim drd As SqlDataReader
	drd = cmd.ExecuteReader(CommandBehavior.CloseConnection)
	drd.Read()

	Test()

End Using	 'cmd

...

Public Sub Test
	Dim cmd2 As New SqlCommand("SELECT PartID FROM Part", con)
	Dim drd2 As SqlDataReader
	drd2 = cmd2.ExecuteReader(CommandBehavior.CloseConnection)
	drd2.Read()
End Sub

Open in new window


Because the first datareader still has a hold on cmd, a second connection to the server will be initiated in the Test method.

I have seen that problem a few times in applications where the number of licences for the server were used up with only one or two application/users connected. We were stuck with that in older versions of ADO.NET and SQL Server, and we had to create workarounds.

At some point, Microsoft introduced Multiple Active Result Sets (MARS) so that we did not have to care about that and could let connection pooling do its job no matter what.
0
 
praveen1981Author Commented:
Thanks for your clarifications.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.