Question on connection pooling

S

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
 
P

Paul Clement

¤ 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)
 
B

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
 
S

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
 
B

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/d...n-us/dnsqlmag04/html/ADO_NET101SqlCommand.asp

Hope this helps,

Brad
 
P

Paul Clement

¤ 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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Members online

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top