Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Static class

Posted on 2012-08-16
7
Medium Priority
?
362 Views
Last Modified: 2012-08-27
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.
0
Comment
Question by:praveen1981
  • 3
  • 3
7 Comments
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 750 total points
ID: 38303767
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
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 750 total points
ID: 38307181
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38307284
@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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 40
ID: 38307300
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
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 750 total points
ID: 38307812
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
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 750 total points
ID: 38307928
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
 

Author Closing Comment

by:praveen1981
ID: 38339648
Thanks for your clarifications.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question