"Joanna Carter [TeamB]" <joanna@not.for.spam> wrote in message news:OmkS37glGHA.1640@TK2MSFTNGP02.phx.gbl...
> Hi Folks
>
> .NET 2.0 and SQLServer2005
>
> I am just trying to get my head around whether I can use a single
> SQLConnection for the life of the application or whether I should create it
> only when needed.
>
> I want to create cached SQLCommand objects that are then prepared to reduce
> subsequent execution time, but these can't be prepared unless their
> Connection property has been set.
>
> This then causes me to think that, if I create/dispose connections in a
> "using" block, the vommands are going to keep a reference to the connection
> alive anyway, so why not just keep the connection alive for the duration of
> the application ?
>
> If I make a connection for the first time, then prepare the commands, then
> call Dispose on the connection, will the commands have to be prepared the
> next time I instantiate the connection, using pooling ?
>
> So I have two options :
>
> 1. Create connection, prepare commands on first call then keep them in a
> list, only close connection when app closes
>
> 2. Create connection in "using" block, this then means I don't keep a list
> of prepared commands and have to take the speed hit on running the commands
> every time I get a new connection.
>
> What is the better route ?
>
> Joanna
>
> --
> Joanna Carter [TeamB]
> Consultant Software Engineer
>
>

"Joanna Carter [TeamB]" <joanna@not.for.spam> wrote in message
news:uUbsLAqlGHA.2056@TK2MSFTNGP03.phx.gbl...
> "Carl Prothman" <c***@prothman.online.org> a écrit dans le message de
> news:
> uhxjj6hlGHA.1***@TK2MSFTNGP03.phx.gbl...
>
> | It's best practice to use Connection Pooling.  So open and close your
> SqlConnection only when it's needed. Make sure to close the SqlConnection,
> otherwise it won't go back into the Connection Pool.
>
> I wasn't planning on leaving the connection open, my query was that I was
> under the impression that connection pooling only worked if you
> created/disposed the connection, rather than just open/close.
>>> Nope, you don't have to dispose the connection to release it to the
>>> pool, just Close it. Use Open to acquire a connection from the pool or
>>> create a new one.
>
> | Are your SqlCommands using in-line SQL or are they calling Stored
> Procedures?  It's best practice to use Stored Procedures.   If the SQL is
> slow, there are a lot of tricks to speeding up it up depending on the
> query,
> schema, etc.
>
> We were not going to bother with stored procs as the SQL we will be using
> will be mainly single table CRUD operations; I don't think the performance
> would be that much different, would it ?
>>> Nope, as long as you're using Parameters and not user-supplied values
>>> concatenated into the CRUD.

>
> | I would not worry about creating prepared commands other than having the
> ADO.NET code in place (e.g. data class).
>
> My question here is : if I have prepared a command and then I close the
> connection, when I opern the connection again, will the same command still
> be prepared or will it need preparing again ?

>>> The query plans cached in RAM on the server can still be accessed by
>>> other (subsequent) connections.

>
> Joanna
>
> --
> Joanna Carter [TeamB]
> Consultant Software Engineer
>
>

"Joanna Carter [TeamB]" <joanna@not.for.spam> wrote in message news:O7QlI99lGHA.856@TK2MSFTNGP03.phx.gbl...
> "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> a crit dans le
> message de news: u66WNt8lGHA.***@TK2MSFTNGP03.phx.gbl...
>
> | See >>>
>
> Thanks Bill. Could I just confirm that, for a desktop app, it is OK to
> create a connection that will live all day, being opened and closed during
> that time when required ?
>
> Joanna
>
> --
> Joanna Carter [TeamB]
> Consultant Software Engineer
>
>

"Joanna Carter [TeamB]" <joanna@not.for.spam> wrote in message
news:OmkS37glGHA.1640@TK2MSFTNGP02.phx.gbl...
> Hi Folks
>
> .NET 2.0 and SQLServer2005
>
> I am just trying to get my head around whether I can use a single
> SQLConnection for the life of the application or whether I should create
> it
> only when needed.
>
> I want to create cached SQLCommand objects that are then prepared to
> reduce
> subsequent execution time, but these can't be prepared unless their
> Connection property has been set.
>
> This then causes me to think that, if I create/dispose connections in a
> "using" block, the vommands are going to keep a reference to the
> connection
> alive anyway, so why not just keep the connection alive for the duration
> of
> the application ?
>
> If I make a connection for the first time, then prepare the commands, then
> call Dispose on the connection, will the commands have to be prepared the
> next time I instantiate the connection, using pooling ?
>
> So I have two options :
>
> 1. Create connection, prepare commands on first call then keep them in a
> list, only close connection when app closes
>
> 2. Create connection in "using" block, this then means I don't keep a list
> of prepared commands and have to take the speed hit on running the
> commands
> every time I get a new connection.
>
> What is the better route ?
>
> Joanna
>
> --
> Joanna Carter [TeamB]
> Consultant Software Engineer
>
>

"William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> schreef in bericht
news:u%23wmAailGHA.3732@TK2MSFTNGP05.phx.gbl...
> While Carl's suggestions have merit, I'm of the opinion that it's
> perfectly okay to open a connection and leave it open for the life of the
> application--assuming you're not using ASP. Persistent connections give
> you a way to maintain and mange state on the server including server-side
> cursors, temp tables, SET variables and more. SQL Server can support
> hundreds to thousands of users (on a single system). Each connect open
> consumes workstation, network and server resources. Doing this each time
> you need to query is more expensive than simply leaving the connection
> open. You do, however, have to make sure that the connection's channel is
> cleared by properly closing DataReaders as a connection can only support
> one operation at a time. (Yes you can try MARS but this does not fix that
> problem--just delays the time when it fails).
>
> The connect, query, disconnect approach is essential for ASP--not for
> client/server.
>
> HTH
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
>
> "Joanna Carter [TeamB]" <joanna@not.for.spam> wrote in message
> news:OmkS37glGHA.1640@TK2MSFTNGP02.phx.gbl...
>> Hi Folks
>>
>> .NET 2.0 and SQLServer2005
>>
>> I am just trying to get my head around whether I can use a single
>> SQLConnection for the life of the application or whether I should create
>> it
>> only when needed.
>>
>> I want to create cached SQLCommand objects that are then prepared to
>> reduce
>> subsequent execution time, but these can't be prepared unless their
>> Connection property has been set.
>>
>> This then causes me to think that, if I create/dispose connections in a
>> "using" block, the vommands are going to keep a reference to the
>> connection
>> alive anyway, so why not just keep the connection alive for the duration
>> of
>> the application ?
>>
>> If I make a connection for the first time, then prepare the commands,
>> then
>> call Dispose on the connection, will the commands have to be prepared the
>> next time I instantiate the connection, using pooling ?
>>
>> So I have two options :
>>
>> 1. Create connection, prepare commands on first call then keep them in a
>> list, only close connection when app closes
>>
>> 2. Create connection in "using" block, this then means I don't keep a
>> list
>> of prepared commands and have to take the speed hit on running the
>> commands
>> every time I get a new connection.
>>
>> What is the better route ?
>>
>> Joanna
>>
>> --
>> Joanna Carter [TeamB]
>> Consultant Software Engineer
>>
>>
>
>