on db call

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

How in .NET can i have one call to open the db instead of haveing to code an
open connection everytime i need to access data from the database?
 
(Assuming you are SQL server is your data store

I suppose you can cache the SqlConnection object in the session or some kind of global object... But why would you want to do that? Have you noticed any performance problems

When you open a SQLConnection in .NET(using ADO.NET) it's not actually opening a new brand connection, instead it's using an already open connection from the connection pool. Also when you close a SQLConnection it's not actually being closed, instead it's released back to the pool to be re-used

So the caching which you seem to want to handle on your own(i think..) is already being taken care of for you by .NET

HTH
Suresh


----- Mike wrote: ----

How in .NET can i have one call to open the db instead of haveing to code a
open connection everytime i need to access data from the database
 
Hello,

Something to keep in mind is that the connection pooling does NOT work if
you are using windows integerated security. Connection pooling reguires
that the exact connection string is used to connect each time, however, when
using windows integrated security something happens under the cover that
does not allow for pooling.

I open and close the connection basically every method and rely on the
connection pooling. However, if you would rather not you could store the
connection as static in a Global class. Forinstance Global.GetConn(); If
you do this, remember that your application will not scale well as more
users are connecting at the same time and you may run out of allowed
connections.

private static SqlConnection _conn;

public static SqlConnection GetConn() {

if (_conn != null && _conn.State != SqlConnectionState.Open()) {
_conn.ConnectionString = ...
_conn.open();
}

return _conn;
}

Note: this code may not be perfect, but it is close.

Take Care,

Tom Krueger
Blue Pen Solutions
http://www.BluePenSolutions.com



Suresh said:
(Assuming you are SQL server is your data store)

I suppose you can cache the SqlConnection object in the session or some
Click to expand...
kind of global object... But why would you want to do that? Have you
noticed any performance problems?
When you open a SQLConnection in .NET(using ADO.NET) it's not actually
Click to expand...
opening a new brand connection, instead it's using an already open
connection from the connection pool. Also when you close a SQLConnection
it's not actually being closed, instead it's released back to the pool to be
re-used.
So the caching which you seem to want to handle on your own(i think..) is
Click to expand...
already being taken care of for you by .NET.
 
Good call. I completely overlooked that part

----- Tom Krueger wrote: ----

Hello

Something to keep in mind is that the connection pooling does NOT work i
you are using windows integerated security. Connection pooling reguire
that the exact connection string is used to connect each time, however, whe
using windows integrated security something happens under the cover tha
does not allow for pooling

I open and close the connection basically every method and rely on th
connection pooling. However, if you would rather not you could store th
connection as static in a Global class. Forinstance Global.GetConn(); I
you do this, remember that your application will not scale well as mor
users are connecting at the same time and you may run out of allowe
connections

private static SqlConnection _conn

public static SqlConnection GetConn()

if (_conn != null && _conn.State != SqlConnectionState.Open())
_conn.ConnectionString = ..
_conn.open()


return _conn


Note: this code may not be perfect, but it is close

Take Care

Tom Kruege
Blue Pen Solution
http://www.BluePenSolutions.co



Suresh said:
(Assuming you are SQL server is your data store
Click to expand...
kind of global object... But why would you want to do that? Have yo
noticed any performance problemsopening a new brand connection, instead it's using an already ope
connection from the connection pool. Also when you close a SQLConnectio
it's not actually being closed, instead it's released back to the pool to b
re-used
 
your code is not thread safe, and the design (once locking was added) would
only allow page to run at a time - pretty poor scaling.

-- bruce (sqlwork.com)
 
Back
Top