Re: Using one instance of SqlConnection in an ASP.NET page
From: Bin Song, MCP (anonymous_at_discussions.microsoft.com)
Date: 04/08/04
- Next message: Ron Fluegge: "ODBC Connection with SQL Managed Provider"
- Previous message: Bob: "Re: Using one instance of SqlConnection in an ASP.NET page"
- In reply to: Bob: "Re: Using one instance of SqlConnection in an ASP.NET page"
- Next in thread: Joe Fallon: "Re: Using one instance of SqlConnection in an ASP.NET page"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 8 Apr 2004 14:06:02 -0700
Oh, I c.
I think this is depend on the usage of your application like number of users, the capacity of your server. Option 2 might be more scalable because of the pooling. This is also the way I did. Just consider if there are hundreds of request in the same time. In option 1, it will only return to pool after the page is done.
Bin Song, MCP
----- Bob wrote: -----
Hmm, that's still not what I was asking. I understand how and where to
properly close a connection, why it should be done, where to set the pool
size and timeout etc. What I'm trying to get at is that, if I have a page
that needs to make a series of calls to the database, say, call SP 1 to get
some data to populate a drop down, then call SP 2 to get some other data to
populate a datagrid, in the same page a user control on the page calls SP 3
to get some status data to display on the sidebar. So I face a design
decision here. One way is to create one instance of the SqlConnection
object (could be handled by a middle tier but for discussion purpose let's
say I'm doing this directly from the aspx.cs class) and use it throughout
the page, and put the .Close() in the page Dispose() method so the
connection is closed at the end and only at the end of the page processing.
Alternatively, I can design it to let each call create its own instance of
the SqlConnection object and close it immediate after the call is done.
This way, I can wrap each call in its self contained function, and
particularly for the user control on the page, I don't have to get the
connection instance from the main page. This makes the code simpler and the
logic a lot easier to follow. However, the second option would use 3
different connections. Assuming the connections are already in the pool so
it doesn't have to create brand new ones, it would be getting and returning
connections to the pool 3 times, rather than 1 in the first option. So back
to my original questions, if getting and returning connections to the pool
has become so efficient that doing it 2 times more is no big deal at all
then I probably should go with option 2, otherwise I should still do option
1, which has more complicated coding particularly if a middle tier is
involved.
Thanks
"Bin Song, MCP" <anonymous@discussions.microsoft.com> wrote in message
news:2C2B2982-E372-4A3E-B610-610CB134EFE7@microsoft.com...
> Hi, Bob
>> I understand your question now.
> The returning of Connection to pool depend on when you close the
connection or the connection time out. The pool size and timeout can be
defined in the connection string.
> It is recommended that you always close the Connection when you are
finished using it in order for the connection to be returned to the pool.
This can be done using either the Close or Dispose methods of the Connection
object. Connections that are not explicitly closed might not be added or
returned to the pool. For example, a connection that has gone out of scope
but that has not been explicitly closed will only be returned to the
connection pool if the maximum pool size has been reached and the connection
is still valid.
>> Please see the following article:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconConnectionPoolingForSQLServerNETDataProvider.asp
>> Bin Song, MCP
>> ----- Bob wrote: -----
>> Alex, thanks for your reply. I understand that the same connection
string
> reults in connections in the same pool. My question is whether
getting and
> returning connections to the pool have become so efficient in ADP.NET
and
> Sql Data Provider that there is no need to put in extra code in order
to
> reduce the number of getting and returning from say, 3 to 1.
>> Thanks
> Bob
>> "Alex Papadimoulis" <alexp@papadimoulis.com> wrote in message
> news:107b75bbivib4e1@corp.supernews.com...
>> Bob, IIRC, so long as you open the connection with the same
connection
>> string, it'll be pooled.
>>> Alex Papadimoulis
>> " Bob" <bobatkpmg@yahoo.com> wrote in message
>> news:OgfJlbZHEHA.328@TK2MSFTNGP11.phx.gbl...
>>> In our new .NET web applications, we try to limit the use of
> SqlConnection
>>> to just one instance per page, even if there are multiple accesses
to
>>> various queries. The thinking behind is that this reduces the
need to
>>> getting and returning connections to the pool repeatedly if a page
has
>>> multiple calls to the DB, and each one manages its own connection.
>> However,
>>> this does requires more deliberate coding, like calling the
>>> SqlConnection.Close() method in the page's Dispose() method so
it's
>>> garanteed that the connection is closed when the page processing
is
> done,
>>> and also is not closed too early. What I'm thinking is whether
this is
>>> actually necessary because passing the Connection object into
child
>> controls
>>> becomes a pretty big hassle when there are several user controls
or
> custom
>>> controls on the page. If the connection pool management is very
> efficient
>>> then opening and closing connections repeatly in the code (which
is
> really
>>> getting and returning connections to the pool) wouldn't be a big
deal,
> and
>>> keeping the code simple would be more important. Could anyone
give some
>>> suggestions?
>>>>>>>
- Next message: Ron Fluegge: "ODBC Connection with SQL Managed Provider"
- Previous message: Bob: "Re: Using one instance of SqlConnection in an ASP.NET page"
- In reply to: Bob: "Re: Using one instance of SqlConnection in an ASP.NET page"
- Next in thread: Joe Fallon: "Re: Using one instance of SqlConnection in an ASP.NET page"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
