Question on connection pooling

[Steve Franks]

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

[Curt_C [MVP]]

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

[Paul Clement]

On Thu, 27 Oct 2005 09:02:26 -0400, "Steve Franks" <pl****@postreplyhere.com> 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)

[Sreejith Ram]

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

[BradC]

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

[Steve Franks]

> 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

[BradC]

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

[Paul Clement]

On Thu, 27 Oct 2005 09:20:25 -0700, BradC <br********@yahoo.com> 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)