Hi all.  I have a routine I found in a Wrox book for a web application as 
follows:

Function GetDataView(ByVal strSQL As String, ByVal strTable As String) As 
DataView
   Try
      Dim objConnect As New OleDbConnection(GetConnectString())
      Dim objDataAdapter As New OleDbDataAdapter(strSQL, objConnect)
      Dim objDataSet As New DataSet()
      objDataAdapter.Fill(objDataSet, strTable)
      Return objDataSet.Tables(0).DefaultView
      Catch objError As Exception
         Return Nothing
   End Try
End Function

1) Does the sql connection automatically close after running this 
routine?  I mean, is closing the connection part of the FILL command?

2) I believe that web applications automatically frees most resources 
once the page is delivered....does this pertain to sql connections?  I 
mean, if the sql connection is NOT closed, does it automatically free up 
once the web page is delivered to the user??  OR...does the sql 
connection stay open for a certain period of time and then after a 
certain period of not being used, free itself up for use by someone else??

Thanks...Jim

Hi Jim,

The new ado .net paradigm is 'disconnected' - ie, when the dataset is
filled, the connection is released.  If the dataset is worked on and updates
etc are required, there are additional commands that essentially reopen the
connection and make the changes (first verifying that nothing has changed in
the interim, etc).

HTH,

Bernie Yaeger
----- Original Message -----
From: "J Donahue" <jdonahue@f...>
To: "ASP+" <aspx@p...>
Sent: Thursday, August 08, 2002 3:09 PM
Subject: [aspx] SQL Server Connections


> Hi all.  I have a routine I found in a Wrox book for a web application as
> follows:
>
> Function GetDataView(ByVal strSQL As String, ByVal strTable As String) As
> DataView
>    Try
>       Dim objConnect As New OleDbConnection(GetConnectString())
>       Dim objDataAdapter As New OleDbDataAdapter(strSQL, objConnect)
>       Dim objDataSet As New DataSet()
>       objDataAdapter.Fill(objDataSet, strTable)
>       Return objDataSet.Tables(0).DefaultView
>       Catch objError As Exception
>          Return Nothing
>    End Try
> End Function
>
> 1) Does the sql connection automatically close after running this
> routine?  I mean, is closing the connection part of the FILL command?
>
> 2) I believe that web applications automatically frees most resources
> once the page is delivered....does this pertain to sql connections?  I
> mean, if the sql connection is NOT closed, does it automatically free up
> once the web page is delivered to the user??  OR...does the sql
> connection stay open for a certain period of time and then after a
> certain period of not being used, free itself up for use by someone else??
>
> Thanks...Jim
>

Hi Bernie.  Thank you very much!  You've cleared up ADO.NET but how about 
OLE DB...if I open an OLEDB connection and do NOT close it before the web 
page is delivered....does the connection automatically close once the web 
page is delivered?  Or does that OLE DB connection basically remain "used 
up"??

And..thank you again!!!

> Hi Jim,

The new ado .net paradigm is 'disconnected' - ie, when the dataset is
filled, the connection is released.  If the dataset is worked on and 
updates
etc are required, there are additional commands that essentially reopen 
the
connection and make the changes (first verifying that nothing has changed 
in
the interim, etc).

HTH,

Bernie Yaeger
----- Original Message -----
From: "J Donahue" <jdonahue@f...>
To: "ASP+" <aspx@p...>
Sent: Thursday, August 08, 2002 3:09 PM
Subject: [aspx] SQL Server Connections


> Hi all.  I have a routine I found in a Wrox book for a web application 
as
> follows:
>
> Function GetDataView(ByVal strSQL As String, ByVal strTable As String) 
As
> DataView
>    Try
>       Dim objConnect As New OleDbConnection(GetConnectString())
>       Dim objDataAdapter As New OleDbDataAdapter(strSQL, objConnect)
>       Dim objDataSet As New DataSet()
>       objDataAdapter.Fill(objDataSet, strTable)
>       Return objDataSet.Tables(0).DefaultView
>       Catch objError As Exception
>          Return Nothing
>    End Try
> End Function
>
> 1) Does the sql connection automatically close after running this
> routine?  I mean, is closing the connection part of the FILL command?
>
> 2) I believe that web applications automatically frees most resources
> once the page is delivered....does this pertain to sql connections?  I
> mean, if the sql connection is NOT closed, does it automatically free up
> once the web page is delivered to the user??  OR...does the sql
> connection stay open for a certain period of time and then after a
> certain period of not being used, free itself up for use by someone 
else??
>
> Thanks...Jim
>

If you are using SqlDataReaders you must explicitly close the dataReader and
the connection, preferably in a finally block.  The SqlConnection resources
are freed up for you when you us a DataAdapter.  (Though, if this is garbage
collected, and hence nondeterministic, I would guess that you can never be
sure just when the physical connection is actually broken).  With Oracle
I've "read" that things are very different, and their seem to be some issues
regarding running out of resources with Oracle if your not careful (but I
have no first hand experience of this). Transactions would obviously muddy
the waters too.

Graham


-----Original Message-----
From: J Donahue [mailto:jdonahue@f...]
Sent: Thursday, August 08, 2002 3:09 PM
To: ASP+
Subject: [aspx] SQL Server Connections


Hi all.  I have a routine I found in a Wrox book for a web application as
follows:

Function GetDataView(ByVal strSQL As String, ByVal strTable As String) As
DataView
   Try
      Dim objConnect As New OleDbConnection(GetConnectString())
      Dim objDataAdapter As New OleDbDataAdapter(strSQL, objConnect)
      Dim objDataSet As New DataSet()
      objDataAdapter.Fill(objDataSet, strTable)
      Return objDataSet.Tables(0).DefaultView
      Catch objError As Exception
         Return Nothing
   End Try
End Function

1) Does the sql connection automatically close after running this
routine?  I mean, is closing the connection part of the FILL command?

2) I believe that web applications automatically frees most resources
once the page is delivered....does this pertain to sql connections?  I
mean, if the sql connection is NOT closed, does it automatically free up
once the web page is delivered to the user??  OR...does the sql
connection stay open for a certain period of time and then after a
certain period of not being used, free itself up for use by someone else??

Thanks...Jim

Hi Jim,

OLE DB is simply a provider that ADO .net uses - OLE DB, ODBC and the more
direct SQL providers are all subordinate to ADO .net.  If, however, you were
to use classic ADO 2.6, say, via interop, it would have a connected
recordset (unless you intentionally set the cursor to disconnected).

The short answer, therefor, is that whenever you use ADO .net with a
dataadapter to fill a dataset, it is disconnected, whether you use an OLE DB
provider, and ODBC provider, etc.  I understand from Microsoft that the next
update to ADO .net (about 7 months away) will have an option for a connected
recordset, which I on occasion do need.


Regards,

Bernie

----- Original Message -----
From: "J Donahue" <jdonahue@f...>
To: "ASP+" <aspx@p...>
Sent: Thursday, August 08, 2002 3:58 PM
Subject: [aspx] Re: SQL Server Connections


> Hi Bernie.  Thank you very much!  You've cleared up ADO.NET but how about
> OLE DB...if I open an OLEDB connection and do NOT close it before the web
> page is delivered....does the connection automatically close once the web
> page is delivered?  Or does that OLE DB connection basically remain "used
> up"??
>
> And..thank you again!!!
>
> > Hi Jim,
>
> The new ado .net paradigm is 'disconnected' - ie, when the dataset is
> filled, the connection is released.  If the dataset is worked on and
> updates
> etc are required, there are additional commands that essentially reopen
> the
> connection and make the changes (first verifying that nothing has changed
> in
> the interim, etc).
>
> HTH,
>
> Bernie Yaeger
> ----- Original Message -----
> From: "J Donahue" <jdonahue@f...>
> To: "ASP+" <aspx@p...>
> Sent: Thursday, August 08, 2002 3:09 PM
> Subject: [aspx] SQL Server Connections
>
>
> > Hi all.  I have a routine I found in a Wrox book for a web application
> as
> > follows:
> >
> > Function GetDataView(ByVal strSQL As String, ByVal strTable As String)
> As
> > DataView
> >    Try
> >       Dim objConnect As New OleDbConnection(GetConnectString())
> >       Dim objDataAdapter As New OleDbDataAdapter(strSQL, objConnect)
> >       Dim objDataSet As New DataSet()
> >       objDataAdapter.Fill(objDataSet, strTable)
> >       Return objDataSet.Tables(0).DefaultView
> >       Catch objError As Exception
> >          Return Nothing
> >    End Try
> > End Function
> >
> > 1) Does the sql connection automatically close after running this
> > routine?  I mean, is closing the connection part of the FILL command?
> >
> > 2) I believe that web applications automatically frees most resources
> > once the page is delivered....does this pertain to sql connections?  I
> > mean, if the sql connection is NOT closed, does it automatically free up
> > once the web page is delivered to the user??  OR...does the sql
> > connection stay open for a certain period of time and then after a
> > certain period of not being used, free itself up for use by someone
> else??
> >
> > Thanks...Jim
> >
>
>
>