Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Question on connection pooling

Reply
Thread Tools

Question on connection pooling

 
 
Steve Franks
Guest
Posts: n/a
 
      10-27-2005
Using VS.NET 2005 RC - Am I understanding correctly from the docs that
connection pooling for my ADO.NET db connections will happen automatically?
Do I need to do anything to enable this feature?

Basically I have code in a method that does this:

SqlConnection conn = null;
try
{
conn = new SqlConnection("Data Source=myserver;Initial
Catalog=mydb;Persist Security Info=True;User ID=mydb;Password=mypass;
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO [mytable]
([col1],[col2]) VALUES (@col1, @col2)", conn);
cmd.Parameters.AddWithValue("@col1", "testing col1");
cmd.Parameters.AddWithValue("@col2", "testing col2");
int x = cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}
}

As I call into this method from my Page_Load command, am I correct to assume
that this db connection really stays open under the hood, despite the code
doing a .Close() on it? Also am I correct that I do not need to do anything
fancy like create the new SqlConnection object once and not close it and
just reuse it? That's not needed right, because ADO.NET is taking care of
that in the background for me?

Likewise, sometimes I need to do a few inserts and updates on the same page
request. So I assume its ok to have each type of operation in its own
method like this and just call into the method, rather than creating one
method that does all the work and keeps reusing the same SqlConnection
object for each .ExecuteNonQuery() statement?

Also on an unrelated note - I do not want to use stored procedures, so give
that, does my approach to using parameterized queries shown above look ok or
is there a better way?

Thanks!

Steve


 
Reply With Quote
 
 
 
 
=?Utf-8?B?Q3VydF9DIFtNVlBd?=
Guest
Posts: n/a
 
      10-27-2005
IIRC you need Win2003 (IIS 6) for this to happen.

--
Curt Christianson
site: http://www.darkfalz.com
blog: http://blog.darkfalz.com



"Steve Franks" wrote:

> Using VS.NET 2005 RC - Am I understanding correctly from the docs that
> connection pooling for my ADO.NET db connections will happen automatically?
> Do I need to do anything to enable this feature?
>
> Basically I have code in a method that does this:
>
> SqlConnection conn = null;
> try
> {
> conn = new SqlConnection("Data Source=myserver;Initial
> Catalog=mydb;Persist Security Info=True;User ID=mydb;Password=mypass;
> conn.Open();
> SqlCommand cmd = new SqlCommand("INSERT INTO [mytable]
> ([col1],[col2]) VALUES (@col1, @col2)", conn);
> cmd.Parameters.AddWithValue("@col1", "testing col1");
> cmd.Parameters.AddWithValue("@col2", "testing col2");
> int x = cmd.ExecuteNonQuery();
> }
> finally
> {
> if (conn != null)
> {
> conn.Close();
> }
> }
>
> As I call into this method from my Page_Load command, am I correct to assume
> that this db connection really stays open under the hood, despite the code
> doing a .Close() on it? Also am I correct that I do not need to do anything
> fancy like create the new SqlConnection object once and not close it and
> just reuse it? That's not needed right, because ADO.NET is taking care of
> that in the background for me?
>
> Likewise, sometimes I need to do a few inserts and updates on the same page
> request. So I assume its ok to have each type of operation in its own
> method like this and just call into the method, rather than creating one
> method that does all the work and keeps reusing the same SqlConnection
> object for each .ExecuteNonQuery() statement?
>
> Also on an unrelated note - I do not want to use stored procedures, so give
> that, does my approach to using parameterized queries shown above look ok or
> is there a better way?
>
> Thanks!
>
> Steve
>
>
>

 
Reply With Quote
 
 
 
 
Paul Clement
Guest
Posts: n/a
 
      10-27-2005
On Thu, 27 Oct 2005 09:02:26 -0400, "Steve Franks" <(E-Mail Removed)> wrote:

Using VS.NET 2005 RC - Am I understanding correctly from the docs that
connection pooling for my ADO.NET db connections will happen automatically?

Yes.

Do I need to do anything to enable this feature?


No. As long as the provider supports connection pooling it is enabled by default.

Basically I have code in a method that does this:

SqlConnection conn = null;
try
{
conn = new SqlConnection("Data Source=myserver;Initial
Catalog=mydb;Persist Security Info=True;User ID=mydb;Password=mypass;
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO [mytable]
([col1],[col2]) VALUES (@col1, @col2)", conn);
cmd.Parameters.AddWithValue("@col1", "testing col1");
cmd.Parameters.AddWithValue("@col2", "testing col2");
int x = cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}
}

As I call into this method from my Page_Load command, am I correct to assume
that this db connection really stays open under the hood, despite the code
doing a .Close() on it? Also am I correct that I do not need to do anything
fancy like create the new SqlConnection object once and not close it and
just reuse it? That's not needed right, because ADO.NET is taking care of
that in the background for me?


When you close the connection it is released to a connection pool. At this point your application
knows nothing about it. Upon a subsequent request for a connection, the pool that is associated with
the application process (or app pool) and connection string properties is checked for an available
connection. If available a connection is returned, if not a new connection is created and returned.

Likewise, sometimes I need to do a few inserts and updates on the same page
request. So I assume its ok to have each type of operation in its own
method like this and just call into the method, rather than creating one
method that does all the work and keeps reusing the same SqlConnection
object for each .ExecuteNonQuery() statement?

Also on an unrelated note - I do not want to use stored procedures, so give
that, does my approach to using parameterized queries shown above look ok or
is there a better way?

Not really. Ideally stored procedures should be used. Otherwise you will have to edit check your
data for certain characters such as single quotes, double quotes, etc. or your query will fail with
a syntax error when they are embedded within text.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Reply With Quote
 
=?Utf-8?B?U3JlZWppdGggUmFt?=
Guest
Posts: n/a
 
      10-27-2005
Steve,

Each connection pool is associated with a distinct connection string. So
when you use same connection string to open a connection the connection is
taken from the pool. When you use a new connection string first time, a pool
is created.

This artcle explains it verywell


http://msdn.microsoft.com/library/de...taProvider.asp

HTH

"Steve Franks" wrote:

> Using VS.NET 2005 RC - Am I understanding correctly from the docs that
> connection pooling for my ADO.NET db connections will happen automatically?
> Do I need to do anything to enable this feature?
>
> Basically I have code in a method that does this:
>
> SqlConnection conn = null;
> try
> {
> conn = new SqlConnection("Data Source=myserver;Initial
> Catalog=mydb;Persist Security Info=True;User ID=mydb;Password=mypass;
> conn.Open();
> SqlCommand cmd = new SqlCommand("INSERT INTO [mytable]
> ([col1],[col2]) VALUES (@col1, @col2)", conn);
> cmd.Parameters.AddWithValue("@col1", "testing col1");
> cmd.Parameters.AddWithValue("@col2", "testing col2");
> int x = cmd.ExecuteNonQuery();
> }
> finally
> {
> if (conn != null)
> {
> conn.Close();
> }
> }
>
> As I call into this method from my Page_Load command, am I correct to assume
> that this db connection really stays open under the hood, despite the code
> doing a .Close() on it? Also am I correct that I do not need to do anything
> fancy like create the new SqlConnection object once and not close it and
> just reuse it? That's not needed right, because ADO.NET is taking care of
> that in the background for me?
>
> Likewise, sometimes I need to do a few inserts and updates on the same page
> request. So I assume its ok to have each type of operation in its own
> method like this and just call into the method, rather than creating one
> method that does all the work and keeps reusing the same SqlConnection
> object for each .ExecuteNonQuery() statement?
>
> Also on an unrelated note - I do not want to use stored procedures, so give
> that, does my approach to using parameterized queries shown above look ok or
> is there a better way?
>
> Thanks!
>
> Steve
>
>
>

 
Reply With Quote
 
BradC
Guest
Posts: n/a
 
      10-27-2005
Hello Paul,

-snip-
> Basically I have code in a method that does this:
>
> SqlConnection conn = null;
> try
> {
> conn = new SqlConnection("Data Source=myserver;Initial
> Catalog=mydb;Persist Security Info=True;User
> ID=mydb;Password=mypass;
> conn.Open();
> SqlCommand cmd = new SqlCommand("INSERT INTO [mytable]
> ([col1],[col2]) VALUES (@col1, @col2)", conn);
> cmd.Parameters.AddWithValue("@col1", "testing col1");
> cmd.Parameters.AddWithValue("@col2", "testing col2");
> int x = cmd.ExecuteNonQuery();
> }
> finally
> {
> if (conn != null)
> {
> conn.Close();
> }
> }


-snip-

> Also on an unrelated note - I do not want to use stored procedures,
> so give
> that, does my approach to using parameterized queries shown above
> look ok or
> is there a better way?


> Not really. Ideally stored procedures should be used. Otherwise you
> will have to edit check your data for certain characters such as
> single quotes, double quotes, etc. or your query will fail with a
> syntax error when they are embedded within text.
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)


Paul:

That's true if he is using just string concatenation to put together his
SQL statments:
strSQL = "Select * From myTable where Dept = '" & varDept & "'"

But if he is using parameterized queries, as his code sample shows, it should
automatically do proper character escaping, etc, just like using stored procedures.


BradC


 
Reply With Quote
 
Steve Franks
Guest
Posts: n/a
 
      10-27-2005
> That's true if he is using just string concatenation to put together his
> SQL statments:
> strSQL = "Select * From myTable where Dept = '" & varDept & "'"
>
> But if he is using parameterized queries, as his code sample shows, it
> should automatically do proper character escaping, etc, just like using
> stored procedures. BradC


Thanks guys. Does using parameterized queries also protect against SQL
injection attacks, or do I need to do something special for that?

Steve


 
Reply With Quote
 
BradC
Guest
Posts: n/a
 
      10-27-2005
Yes, it does, Steve.

That's the primary advantage of Parameterized queries over just cobbling
together your own SQL statments with strings.

Note that I usually use a slightly more verbose method for adding SQL parameters:

Dim cmd As New SqlCommand("INSERT INTO department VALUES" & "(@DepartmentID,
@DepartmentName)", cn)
Dim parmDepartmentID = New SqlParameter("@DepartmentID", SqlDbType.Int)
parmDepartmentID.Direction = ParameterDirection.Input
cmd.Parameters.Value = 10
cmd.Parameters.Add(parmDepartmentID)

The advantage of this syntax is that it actually enforces proper data types,
just like a stored procedure.

ALSO, parameterized queries even get some of the PERFORMANCE advantage of
a stored procedure:

"You can think of parameterized SQL statements as sort of a cross between
stored procedures and dynamic SQL. Like stored procedures, they can accept
different parameter values at runtime. Like dynamic SQL, they're not persistent
in the database. However, unlike with dynamic SQL, SQL Server parses parameterized
SQL and creates the access plan only once-when it first prepares the statement.
Subsequent statement execution takes advantage of the existing access plan"

From http://msdn.microsoft.com/library/de...SqlCommand.asp

Hope this helps,

Brad


>> That's true if he is using just string concatenation to put together
>> his
>> SQL statments:
>> strSQL = "Select * From myTable where Dept = '" & varDept & "'"
>> But if he is using parameterized queries, as his code sample shows,
>> it should automatically do proper character escaping, etc, just like
>> using stored procedures. BradC
>>

> Thanks guys. Does using parameterized queries also protect against
> SQL injection attacks, or do I need to do something special for that?
>
> Steve
>



 
Reply With Quote
 
Paul Clement
Guest
Posts: n/a
 
      10-28-2005
On Thu, 27 Oct 2005 09:20:25 -0700, BradC <(E-Mail Removed)> wrote:

Hello Paul,

-snip-
> Basically I have code in a method that does this:
>
> SqlConnection conn = null;
> try
> {
> conn = new SqlConnection("Data Source=myserver;Initial
> Catalog=mydb;Persist Security Info=True;User
> ID=mydb;Password=mypass;
> conn.Open();
> SqlCommand cmd = new SqlCommand("INSERT INTO [mytable]
> ([col1],[col2]) VALUES (@col1, @col2)", conn);
> cmd.Parameters.AddWithValue("@col1", "testing col1");
> cmd.Parameters.AddWithValue("@col2", "testing col2");
> int x = cmd.ExecuteNonQuery();
> }
> finally
> {
> if (conn != null)
> {
> conn.Close();
> }
> }

-snip-

> Also on an unrelated note - I do not want to use stored procedures,
> so give
> that, does my approach to using parameterized queries shown above
> look ok or
> is there a better way?

> Not really. Ideally stored procedures should be used. Otherwise you
> will have to edit check your data for certain characters such as
> single quotes, double quotes, etc. or your query will fail with a
> syntax error when they are embedded within text.
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

Paul:

That's true if he is using just string concatenation to put together his
SQL statments:
strSQL = "Select * From myTable where Dept = '" & varDept & "'"

But if he is using parameterized queries, as his code sample shows, it should
automatically do proper character escaping, etc, just like using stored procedures.


Yes, that is true if he is using bind variables.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Connection Pooling, Dispose/Close/Using =?Utf-8?B?UGllcnNvbiBD?= ASP .Net 9 11-26-2008 02:58 PM
Connection pooling =?Utf-8?B?VmFtJHk=?= ASP .Net 1 11-24-2004 09:45 AM
Re: SqlConnection and connection pooling William \(Bill\) Vaughn ASP .Net 0 11-14-2003 07:27 PM
connection pooling error Chris Szabo ASP .Net 6 08-19-2003 07:19 PM
connection pooling Trevor Hartman ASP .Net 2 07-28-2003 07:58 PM



Advertisments