Professional Web Applications Themes

SqlConnection, SqlCommand - ASP.NET Web Services

I'm trying to understand SqlConnection(), SqlCommand() For example. I use SqlConnection() as a parameter when I create a new SqlCommand. Then I open SqlConnection(), then I execute the SqlCommand(). At that point I cannot use SqlConnection() again. Unless I .Close() it and then .Open it right? Then what is 'connection pooling' ? Can't I run multiple SqlCommand()s on the same SqlConnection() somehow ? -- incognito [url]http://kentpsychedelic.blogspot.com/[/url]...

  1. #1

    Default SqlConnection, SqlCommand


    I'm trying to understand SqlConnection(), SqlCommand()

    For example. I use SqlConnection() as a parameter when I create a new
    SqlCommand. Then I open SqlConnection(), then I execute the SqlCommand().
    At that point I cannot use SqlConnection() again. Unless I .Close() it and
    then .Open it right?

    Then what is 'connection pooling' ?

    Can't I run multiple SqlCommand()s on the same SqlConnection() somehow ?


    --
    incognito [url]http://kentpsychedelic.blogspot.com/[/url]
    Donnie Darko Guest

  2. #2

    Default RE: SqlConnection, SqlCommand

    Now there are couple of things here

    1) If you open a SqlConnection then you CAN execute multiple different SqlCommand.ExecuteNonQuery and SqlCommand.ExecuteScalar methods.
    2) If you open a SqlConnection then you can execute only ONE SqlCommand.ExecuteReader. This is because, unlike the previous methods, Execute reader keeps the data live and hence the connection to the prticular table also is live so only one of them can execute untill you close the SqlConnection or close the SqlDataReader.
    3) Connection Pooling is a different conecpt. Creating managed connections to the database are expensive. Hence when Connection Pooling is enabled, and you call Close on the SqlConnection object, although the SqlConnection object is closed, the underlying connection to the database is not closed, but its returned to the pool. When another request for a new connection comes in it utilizes the connection from the pool. This way the cost of creating and deleting connections to the database is saved.

    --
    Regards,
    Saurabh Nandu
    AksTech Solutions, reflecting your needs...
    [ [url]www.AksTech.com[/url] ]
    [ [url]www.MasterCSharp.com[/url] ]



    "Donnie Darko" wrote:
    >
    > I'm trying to understand SqlConnection(), SqlCommand()
    >
    > For example. I use SqlConnection() as a parameter when I create a new
    > SqlCommand. Then I open SqlConnection(), then I execute the SqlCommand().
    > At that point I cannot use SqlConnection() again. Unless I .Close() it and
    > then .Open it right?
    >
    > Then what is 'connection pooling' ?
    >
    > Can't I run multiple SqlCommand()s on the same SqlConnection() somehow ?
    >
    >
    > --
    > incognito [url]http://kentpsychedelic.blogspot.com/[/url]
    >
    Saurabh Nandu Guest

  3. #3

    Default Re: SqlConnection, SqlCommand

    Hello

    An SqlConnection can have only one SqlDataReader open. If you close the
    reader, you can use the connection with another command. Note that
    ExecuteNonQuery and ExecuteScalar call ExecuteReader internally, so both
    methods open a SqlDataReader and close it. I recommend you use the using c#
    keyword to ensure that every SqlDataReader you open is closed. When you
    close a connection .NET connection object is deallocated, but the actual
    connection to Sql server is not closed, it goes back to a connection pool,
    when you open a connection to the same server again, an available connection
    from the connection pool is used. This is connection pooling.

    using(SqlConnection connection = new SqlConnection(myConnectionString))
    {
    using(SqlCommand command = new SqlCommand(myCommand, connection))
    {
    using(SqlDataReader reader = command.ExecuteReader())
    {
    while(reader.Read())
    {
    // read data
    }
    // you can't execute another command here with the same
    connection, because a reader is already open
    }
    // you can execute another command here on the same connection
    because the reader is closed
    }
    // you can execute another command here on the same connection because
    the reader is closed
    }
    // the connection is closed (the actual connection with SQL Server is not
    closed, it is back to the connection pool)

    Best regards,
    Sherif
    "Donnie Darko" <cunningvisions.cellar.door> wrote in message
    news:%ooKc.6046$mL5.5463newsread1.news.pas.earthl ink.net...
    >
    > I'm trying to understand SqlConnection(), SqlCommand()
    >
    > For example. I use SqlConnection() as a parameter when I create a new
    > SqlCommand. Then I open SqlConnection(), then I execute the
    SqlCommand().
    > At that point I cannot use SqlConnection() again. Unless I .Close() it
    and
    > then .Open it right?
    >
    > Then what is 'connection pooling' ?
    >
    > Can't I run multiple SqlCommand()s on the same SqlConnection() somehow ?
    >
    >
    > --
    > incognito [url]http://kentpsychedelic.blogspot.com/[/url]

    Sherif ElMetainy Guest

  4. #4

    Default Re: SqlConnection, SqlCommand

    Donnie Darko wrote:
    > I'm trying to understand SqlConnection(), SqlCommand()
    >
    > For example. I use SqlConnection() as a parameter when I create a new
    > SqlCommand. Then I open SqlConnection(), then I execute the SqlCommand().
    > At that point I cannot use SqlConnection() again. Unless I .Close() it and
    > then .Open it right?
    >
    > Then what is 'connection pooling' ?
    >
    > Can't I run multiple SqlCommand()s on the same SqlConnection() somehow ?
    >
    >
    SqlConnection and SqlCommand are classes, the functions you reference
    would be the constructors. If you open your SqlConnection, and use it
    to execute an SqlDataReader, then you will not be able to re-use that
    connection until you close the SqlDataReader.

    Something like: (in C#)
    --
    SqlConnection connection = new SqlConnection("SomeDBString");
    SqlCommand command = new SqlCommand("Some SQL", connection);

    //Add parameters...
    //Done adding parameters...

    SqlDataReader dr = command.ExecuteReader();
    while (dr.Read()){
    //Do stuff
    }
    dr.Close();
    --
    Then you can re-use the connection later on in your function.

    If you are talking about re-using the connection in other functions, you
    have to remember that the scope of the variable is limited to the
    function only (unless you assign it to an internal class variable).

    If you are using an SqlDataAdapter, then you can re-use the connection
    immediately after calling the Fill() method. If you are just executing
    non-queries, then there's no need to close anything.
    Mike Newton Guest

Similar Threads

  1. help with sqlconnection!!
    By Shakazulu89 in forum Macromedia Flex General Discussion
    Replies: 7
    Last Post: April 15th, 09:45 AM
  2. SqlConnection Problem
    By Calvin Willman in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 23rd, 02:09 PM
  3. Retrieve result from SqlCommand
    By Wong CS in forum ASP.NET Web Services
    Replies: 1
    Last Post: March 8th, 10:55 PM
  4. Return Value from SqlCommand in ASP.Net
    By Adhik in forum ASP.NET General
    Replies: 1
    Last Post: July 19th, 04:23 PM
  5. Reflection and SqlConnection
    By WebDevHokie in forum ASP.NET General
    Replies: 1
    Last Post: July 10th, 06:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139