Question on connection pooling

Discussion in 'ASP .Net' started by Steve Franks, Oct 27, 2005.

  1. Steve Franks

    Steve Franks Guest

    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
     
    Steve Franks, Oct 27, 2005
    #1
    1. Advertising

  2. 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
    >
    >
    >
     
    =?Utf-8?B?Q3VydF9DIFtNVlBd?=, Oct 27, 2005
    #2
    1. Advertising

  3. Steve Franks

    Paul Clement Guest

    On Thu, 27 Oct 2005 09:02:26 -0400, "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?

    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)
     
    Paul Clement, Oct 27, 2005
    #3
  4. 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/d...nectionPoolingForSQLServerNETDataProvider.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
    >
    >
    >
     
    =?Utf-8?B?U3JlZWppdGggUmFt?=, Oct 27, 2005
    #4
  5. Steve Franks

    BradC Guest

    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
     
    BradC, Oct 27, 2005
    #5
  6. Steve Franks

    Steve Franks Guest

    > 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
     
    Steve Franks, Oct 27, 2005
    #6
  7. Steve Franks

    BradC Guest

    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


    >> 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, Oct 27, 2005
    #7
  8. Steve Franks

    Paul Clement Guest

    On Thu, 27 Oct 2005 09:20:25 -0700, BradC <> 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)
     
    Paul Clement, Oct 28, 2005
    #8
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Trevor Hartman

    connection pooling

    Trevor Hartman, Jul 28, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    565
    Mark Heimonen
    Jul 28, 2003
  2. Chris Szabo

    connection pooling error

    Chris Szabo, Aug 19, 2003, in forum: ASP .Net
    Replies:
    6
    Views:
    1,884
    Chris Szabo
    Aug 19, 2003
  3. William \(Bill\) Vaughn

    Re: SqlConnection and connection pooling

    William \(Bill\) Vaughn, Nov 14, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    493
    William \(Bill\) Vaughn
    Nov 14, 2003
  4. =?Utf-8?B?UGllcnNvbiBD?=

    Connection Pooling, Dispose/Close/Using

    =?Utf-8?B?UGllcnNvbiBD?=, Oct 14, 2004, in forum: ASP .Net
    Replies:
    9
    Views:
    4,624
    speedy
    Nov 26, 2008
  5. =?Utf-8?B?VmFtJHk=?=

    Connection pooling

    =?Utf-8?B?VmFtJHk=?=, Nov 24, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    493
    Patrice
    Nov 24, 2004
Loading...

Share This Page