473,326 Members | 2,128 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Database connection (using and close)

Hi,

I'm writing a data layer, and would like all components
to have a single place for retrieving a connection to the
(SQL Server) database. Currently, I'm treating the
connection as somewhat of a "singleton", that is, I have
a static reference to a single SqlConnection object.
However, I'm starting to think this is the _wrong_ thing
to do. From what I can gather about connection pooling, I
can simply create a "new" connection each time
(remembering to open before using it, then closing it
afterwards). That way, I can better handle simultaneous
access to the database (the pool will hand out more than
one connection iff necessary). Also, I'm thinking I
should _not_ be using the _using_ keyword (which I am to
a certain extent now), since this will dispose of the
connection altogether, forcing an expensive recreating of
the database connection for each access. Am I right? Any
help on architecture/usage would be much appreciated.
Nov 15 '05 #1
5 1817
Hi Ranier,

"Ranier Dunno" <an*******@discussions.microsoft.com> wrote in message
news:01****************************@phx.gbl...
Hi,

I'm writing a data layer, and would like all components
to have a single place for retrieving a connection to the
(SQL Server) database. Currently, I'm treating the
connection as somewhat of a "singleton", that is, I have
a static reference to a single SqlConnection object.
However, I'm starting to think this is the _wrong_ thing
to do.
Indeed.

From what I can gather about connection pooling, I can simply create a "new" connection each time
(remembering to open before using it, then closing it
afterwards). That way, I can better handle simultaneous
access to the database (the pool will hand out more than
one connection iff necessary).
Absolutely correct.

Also, I'm thinking I should _not_ be using the _using_ keyword (which I am to
a certain extent now), since this will dispose of the
connection altogether, forcing an expensive recreating of
the database connection for each access. Am I right?
Absolutely not :)
You are right to use using keyword - as it disposes the sqlconnection object
while the physicall connection is returned to pool and it is not closed.

Any help on architecture/usage would be much appreciated.


You've pretty much figured it out.
Keep a connection string global and create connections from it (when
needed).
Close them asap.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
Nov 15 '05 #2
Hi Miha,

Thanks a lot for your reply - sometimes it is necessary
to verify that I'm understanding things correctly. And
it's even better to identify MISunderstandings :)
-----Original Message-----
Hi Ranier,

"Ranier Dunno" <an*******@discussions.microsoft.com> wrote in messagenews:01****************************@phx.gbl...
Hi,

I'm writing a data layer, and would like all components
to have a single place for retrieving a connection to the (SQL Server) database. Currently, I'm treating the
connection as somewhat of a "singleton", that is, I have a static reference to a single SqlConnection object.
However, I'm starting to think this is the _wrong_ thing to do.
Indeed.

From what I can gather about connection pooling, I
can simply create a "new" connection each time
(remembering to open before using it, then closing it
afterwards). That way, I can better handle simultaneous
access to the database (the pool will hand out more than one connection iff necessary).


Absolutely correct.

Also, I'm thinking I
should _not_ be using the _using_ keyword (which I am to a certain extent now), since this will dispose of the
connection altogether, forcing an expensive recreating of the database connection for each access. Am I right?


Absolutely not :)
You are right to use using keyword - as it disposes the

sqlconnection objectwhile the physicall connection is returned to pool and it is not closed.
Any
help on architecture/usage would be much appreciated.
You've pretty much figured it out.
Keep a connection string global and create connections

from it (whenneeded).
Close them asap.

--
Miha Markic - RightHand .NET consulting & software developmentmiha at rthand com
.

Nov 15 '05 #3
Hi again,

I have one further question: would it be preferable to
use a static class or a singleton object as the source
for connection objects?

Best regards.
Nov 15 '05 #4
Hi Ranier,

I normally use a public static readonly property.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

"Ranier Dunno" <an*******@discussions.microsoft.com> wrote in message
news:06****************************@phx.gbl...
Hi again,

I have one further question: would it be preferable to
use a static class or a singleton object as the source
for connection objects?

Best regards.

Nov 15 '05 #5
-----Original Message-----
Hi Ranier,

I normally use a public static readonly property.


Ok, excellent, thanks again.
Nov 15 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Mudge | last post by:
Hi, My hosting provider only allows me to use 50 connections to my MySQL database that my Web site will use. I don't know what this 50 connections means exactly. Does this mean that only 50...
11
by: pradeep_TP | last post by:
Hi all, I have a few questions that I have been wanting to ask for long. These are all related to ADO.net and specifically to conenction to database. 1) If I have opened a connection to a...
2
by: Bryan | last post by:
Hello, I'm just starting to develop in asp.net and i have a question about using a database connection globally in my app. I have set up the procedures for getting all my connection string info...
7
by: Lau Lei Cheong | last post by:
Hello, Actually I think I should have had asked it long before, but somehow I haven't. Here's the scenerio: Say we have a few pages in an ASP.NET project, each of them needs to connect to...
35
by: Terry Jolly | last post by:
Web Solution Goal: Have a global database connection Why: (There will be 30+ tables, represented by 30+ classes) I only want to reference the database connection once. I put the connection...
6
by: Arsalan Ahmad | last post by:
Hi all, I am creating a website in which in an Item detail page there are a number of web controls (7 or 8) and what is happening that inside each of control's Page_Load() function I am creating...
22
Frinavale
by: Frinavale | last post by:
How To Use A Database In Your Program Many .NET solutions are database driven and so many of us often wonder how to access the database. To help you understand the answer to this question I've...
3
by: fniles | last post by:
In the Windows application (using VB.NET 2005) I use connection pooling like the following: In the main form load I open a connection using a connection string that I stored in a global variable...
5
by: Usman Jamil | last post by:
Hi I've a class that creates a connection to a database, gets and loop on a dataset given a query and then close the connection. When I use netstat viewer to see if there is any connection open...
1
Curtis Rutland
by: Curtis Rutland | last post by:
How To Use A Database In Your Program Part II This article is intended to extend Frinny’s excellent article: How to Use a Database in Your Program. Frinny’s article defines the basic concepts...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.