Hi I have a question concerning SqlConnections and when they should be
opened and closed.
Right now I have a database utilities class. I instantiate it when in
page_load and dispose it in page_prerender, which opens and closes class
level sqlconnection.
Now, say I need to run a SqlCommand, all I do is:
SqlCommand oCmd = DBUtils.CreateSqlCommand("spName");
And I get a SqlCommand, type storedprocedure, with the connection set to the
classes database. I add parameters etc, and execute. Everything seems
great. I've been told (#mscorlib and #asp.net on efnet) that this isn't a
good approach. I should be using late open and early close on my database
connection. Especially to take advantage of connection pooling. This means
I need code like...
using (SqlCommand oCmd = DBUtils.CreateSqlCommand("spName"))
{
oCmd.Connection.Open();
oCmd.ExecuteScalar();
oCmd.Connection.Close();
}
Assuming all things are equal except the connection in dbutils is closed.
Is that a better approach? To me that doesn't guarantee the connection
closes or anything.
Maybe I should use...
using (SqlConnection oConn = DBUtils.DatabaseReference)
{
oCmd.Connection = oConn
oCmd.Connection.Open();
oCmd.ExecuteScalar();
oCmd.Connection.Close();
}
Or something like that??? Basically I'm trying to farm out as much of the
database coding as possible. Is this possible? What is everyone else
doing?
What if I have a class in my page that needs to access the database? What
do I do now? Pass reference to my database class?
Please, any help / advise will be greatly appreciated!
Thanks,
Ben 4 1076
"Ben" <be*@online.nospam> wrote in message
news:aO****************@fe37.usenetserver.com... Hi I have a question concerning SqlConnections and when they should be opened and closed.
Right now I have a database utilities class. I instantiate it when in page_load and dispose it in page_prerender, which opens and closes class level sqlconnection.
Now, say I need to run a SqlCommand, all I do is: SqlCommand oCmd = DBUtils.CreateSqlCommand("spName");
And I get a SqlCommand, type storedprocedure, with the connection set to
the classes database. I add parameters etc, and execute. Everything seems great. I've been told (#mscorlib and #asp.net on efnet) that this isn't a good approach.
Whoever told you was wrong. Except possibly that page_prerender is possibly
not the best place to close the connection. Instead override Page.Dispose
and close the connection there. If an exception occurs somewhere you still
need to close the connection, and Page.Dispose will run even if another page
method throws an exception.
But the basic idea of tying the life of the connection to the life of the
page instance is fine. If you open and close your connections around each
command you may get slightly fewer connections in your connection pool, but
I wouldn't worry about it. You will never have more connections than
ASP.NET worker threads. No matter what you do you may have that many, so
it's just fine.
David
Hi Ben,
As for the connection management problem, I agree with David that it dosn't
matter much which coding style you use, as long as we remember to close the
connection after using it. Also, the
========================
I should be using late open and early close on my database
connection.
========================
you mentioned is also right, that'll help the connection pool to better
determine how to adjust the pooled connections. And since the actual
connections are managed by the connectdion pool which is apparent to us(how
many acutal instances of connection will it maintain), we don't need to
care about it.
#Connection Pooling for the .NET Framework Data Provider for SQL Server http://msdn.microsoft.com/library/en...ectionpoolingf
orsqlservernetdataprovider.asp?frame=true
So, as for your situation, you'd like to use connection in a certain page,
just open it and be sure to close it before the page is disposed, it's
enough.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
Get Preview at ASP.NET whidbey http://msdn.microsoft.com/asp.net/whidbey/default.aspx
Thanks,
I'll digest everything and see what fits my senerio.
"Steven Cheng[MSFT]" <v-******@online.microsoft.com> wrote in message
news:uF**************@cpmsftngxa10.phx.gbl... Hi Ben,
As for the connection management problem, I agree with David that it
dosn't matter much which coding style you use, as long as we remember to close
the connection after using it. Also, the ======================== I should be using late open and early close on my database connection. ======================== you mentioned is also right, that'll help the connection pool to better determine how to adjust the pooled connections. And since the actual connections are managed by the connectdion pool which is apparent to
us(how many acutal instances of connection will it maintain), we don't need to care about it.
#Connection Pooling for the .NET Framework Data Provider for SQL Server http://msdn.microsoft.com/library/en...ectionpoolingf orsqlservernetdataprovider.asp?frame=true
So, as for your situation, you'd like to use connection in a certain page, just open it and be sure to close it before the page is disposed, it's enough.
Regards,
Steven Cheng Microsoft Online Support
Get Secure! www.microsoft.com/security (This posting is provided "AS IS", with no warranties, and confers no rights.)
Get Preview at ASP.NET whidbey http://msdn.microsoft.com/asp.net/whidbey/default.aspx
"David Browne" <davidbaxterbrowne no potted me**@hotmail.com> wrote in
message news:Oy*************@tk2msftngp13.phx.gbl... "Ben" <be*@online.nospam> wrote in message news:aO****************@fe37.usenetserver.com... Hi I have a question concerning SqlConnections and when they should be opened and closed.
Right now I have a database utilities class. I instantiate it when in page_load and dispose it in page_prerender, which opens and closes class level sqlconnection.
Now, say I need to run a SqlCommand, all I do is: SqlCommand oCmd = DBUtils.CreateSqlCommand("spName");
And I get a SqlCommand, type storedprocedure, with the connection set to the classes database. I add parameters etc, and execute. Everything seems great. I've been told (#mscorlib and #asp.net on efnet) that this isn't
a good approach.
Whoever told you was wrong. Except possibly that page_prerender is
possibly not the best place to close the connection. Instead override Page.Dispose and close the connection there. If an exception occurs somewhere you
still need to close the connection, and Page.Dispose will run even if another
page method throws an exception.
But the basic idea of tying the life of the connection to the life of the page instance is fine. If you open and close your connections around each command you may get slightly fewer connections in your connection pool,
but I wouldn't worry about it. You will never have more connections than ASP.NET worker threads. No matter what you do you may have that many, so it's just fine.
David
Thanks for the pointers, I will move my class dispose to page.dispose. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: 11abacus |
last post by:
Hi,
Just wondering if any of you has thought about or is working on a
payment gateway abstraction layer (PEAR-style)?
I'm interested on developing that or at least start a discussion about...
|
by: Ruby Tuesday |
last post by:
Which one is better to do dynamic websites using MySQL? Thanks
a.. ADODB, http://php.weblogs.com/ADOdb/
b.. Metabase, http://www.phpclasses.org/browse.html/package/20.html
c.. PEAR::DB,...
|
by: ambika |
last post by:
Hello,
I have a very basic doubt.
Why is C called a structured programming language??why structured?
C++ is called a Object Oriented language 'cos it obeys the OOP's
concepts..Why is C called a...
|
by: Mark Broadbent |
last post by:
this might sound like an obvious question but I have found that usually
these two evolve at the same time.
One of the biggest reasons for creating the abstraction in the first place
(in my...
|
by: Colin McKinnon |
last post by:
Hi all,
There's lots of DB abstraction layers out there, but a quick look around
them hasn't turned up anything which seems to met my requirements. Before I
go off and write one I thought I'd...
| |
by: Motoma |
last post by:
This article is cross posted from my personal blog. You can find the original article, in all its splendor, at http://motomastyle.com/creating-a-mysql-data-abstraction-layer-in-php/.
Introduction:...
|
by: rickycornell |
last post by:
Greetings All,
On past projects in PHP4 I had always just written my own libraries to
deal with database interaction. Somehow I was operating in the dark
that there were all these database...
|
by: subramanian100in |
last post by:
Is my following understanding correct ?
Data abstraction means providing the interface - that is, the set of
functions that can be called by the user of a class.
Information hiding means...
|
by: Ivan S |
last post by:
What are your recommendations for lightweight database abstraction
library (Oracle/MySQL)?
I prefer OOP. :)
Tnx, Ivan.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |