garbage collection and data connections

garbage collection and data connections

I am starting to work with c# having several years of experience with vb6

In the past I have used standard data access functions to either execute 
stored procedures or retrieve recordsets.

I would like to achieve something similar in c#. I can see that there are 
uses for datasets but I would have thought it is often useful to rely on a 
datareader mainly because it is more lightweight.

The problem I have is as follows it I  use code such as 

    public static SqlDataReader GrabDR(string connectionString, string 
queryString)
    {
        SqlConnection connection = new SqlConnection(connectionString);
        SqlCommand sqlCmd = new SqlCommand(queryString, connection);
        sqlCmd.Connection.Open();
        SqlDataReader sqlDreader = sqlCmd.ExecuteReader();
        //leave all destroying of opbjects to garbage collection
        return sqlDreader;
    }

I become dependant on the .net garbage collection to release all used 
resources once I go out of scope.

It cannot see a way of passing a datareader back to the calling function if 
it's connection has been closed beforehand.

I am therefore concerned about the speed and scalability of using the above 
function

It could be that datasets are less cumbersome than I imagine - either way I 
would appreciate some advice on this issue.
-- 
Julian

date: Tue, 7 Feb 2006 10:25:32 -0800 author: Julian Smith

Re: garbage collection and data connections

Don't return datareaders to callers.  return the data from the reader
in some kind of container.  This is why alot of people use datasets.  I
assume you dislike datasets?  That's fine, I don't use them either.
Use custom entities.  Your data layer can then return a collection of
some custom entity class instead of a reader or dataset.  Then you
don't have to worry if the reader gets closed properly (by the caller),
because you do it after filling the custom entities.

I generally follow the provider pattern in an application.  You'll end
up with the same business component and data component architecture,
but a component diagram looks different than the standard 3-tier
application model.

First create a component for your entites and your providers.  Example
classes:
Employee.cs
EmployeeCollection.cs
EmployeeProvider.cs
EmployeeManager.cs

The manager contains all static methods and is for business functions,
like GetEmployee(int id).  The provider class implements all the same
methods as the manager but they are abstract instance methods.  The
default provider acts as a base class for the data layer to implement.

Second create your data component.  It should reference the first
component.  Example classes:
DbEmployeeProvider.cs
EmployeeFactory.cs
EmployeeDbMap.cs

The DbEmployeeProvider delegates all calls to the EmployeeFactory.  It
derives from the base EmployeeProvider.  So if your scenario really was
as simple as this few of classes, then the data provider class and the
factory class could become one.  I separate them because one provider
may call multiple factories.  There is a factory for each custom entity
type.  A provider may be a grouping of functionality that ultimately
pulls from multiple tables.  The DbMap file contains constants for all
the database entities so that if the database changes you have one
place in your code to change for each table.

For a practical example of the above data classes, see the example
classes in the documentation for my open source project, "xqs-provider"
on sourceforge.

For an explanation of custom entities see this article:
http://msdn.microsoft.com/asp.net/default.aspx?pull=/library/en-us/dnaspp/html/custentcls.asp

You can get the open source data component here:
http://sourceforge.net/projects/xqs-data/

The help is on-line here:
http://www.xquisoft.com/xqsdn/documentation/index.html
See XQuiSoft.Data.DataManager, and XQuiSoft.Data.IDataFactory
or
http://www.xquisoft.com/xqsdn/documentation/XQuiSoft.Data.DataManager.html
http://www.xquisoft.com/xqsdn/documentation/XQuiSoft.Data.IDataFactory.html

Please give feedback on the usefullness of the help files using the
link at the bottom of each help file.  If more details are needed in
the example, i will add it to the on-line documentation.

Michael Lang 
XQuiSoft LLC 
http://www.xquisoft.com/

date: 10 Feb 2006 07:19:08 -0800 author: unknown

Re: garbage collection and data connections

The simplest solution is using option CommandBehavior.CloseConnection.

Example:
sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

Description from MSDN:
When the command is executed, the associated Connection object is closed 
when the associated DataReader object is closed.



"Julian Smith"  /  
 : 
news:F2CCB3F7-F3C9-44AF-82F5-FB247546F036@microsoft.com...
>I am starting to work with c# having several years of experience with vb6
>
> In the past I have used standard data access functions to either execute
> stored procedures or retrieve recordsets.
>
> I would like to achieve something similar in c#. I can see that there are
> uses for datasets but I would have thought it is often useful to rely on a
> datareader mainly because it is more lightweight.
>
> The problem I have is as follows it I  use code such as
>
>    public static SqlDataReader GrabDR(string connectionString, string
> queryString)
>    {
>        SqlConnection connection = new SqlConnection(connectionString);
>        SqlCommand sqlCmd = new SqlCommand(queryString, connection);
>        sqlCmd.Connection.Open();
>        SqlDataReader sqlDreader = sqlCmd.ExecuteReader();
>        //leave all destroying of opbjects to garbage collection
>        return sqlDreader;
>    }
>
> I become dependant on the .net garbage collection to release all used
> resources once I go out of scope.
>
> It cannot see a way of passing a datareader back to the calling function 
> if
> it's connection has been closed beforehand.
>
> I am therefore concerned about the speed and scalability of using the 
> above
> function
>
> It could be that datasets are less cumbersome than I imagine - either way 
> I
> would appreciate some advice on this issue.
> -- 
> Julian

date: Mon, 13 Feb 2006 11:47:07 +0300 author: Denis Nikolaev

Re: garbage collection and data connections

Many thanks for this detailed reply to my question.

I have been overloaded at work for the last few months and have only just 
been able to read through this properly.

I have found it very useful - especially the links to the article on custom 
entities and have had a brief look ar codesmith which looks VERY interesting.

I suspect that I shall slowly migrate from working with data sets to custom 
entities as my OO experience grows.

Thanks again
-- 
Julian


"michael.lang@xquisoft.com" wrote:

> Don't return datareaders to callers.  return the data from the reader
> in some kind of container.  This is why alot of people use datasets.  I
> assume you dislike datasets?  That's fine, I don't use them either.
> Use custom entities.  Your data layer can then return a collection of
> some custom entity class instead of a reader or dataset.  Then you
> don't have to worry if the reader gets closed properly (by the caller),
> because you do it after filling the custom entities.
> 
> I generally follow the provider pattern in an application.  You'll end
> up with the same business component and data component architecture,
> but a component diagram looks different than the standard 3-tier
> application model.
> 
> First create a component for your entites and your providers.  Example
> classes:
> Employee.cs
> EmployeeCollection.cs
> EmployeeProvider.cs
> EmployeeManager.cs
> 
> The manager contains all static methods and is for business functions,
> like GetEmployee(int id).  The provider class implements all the same
> methods as the manager but they are abstract instance methods.  The
> default provider acts as a base class for the data layer to implement.
> 
> Second create your data component.  It should reference the first
> component.  Example classes:
> DbEmployeeProvider.cs
> EmployeeFactory.cs
> EmployeeDbMap.cs
> 
> The DbEmployeeProvider delegates all calls to the EmployeeFactory.  It
> derives from the base EmployeeProvider.  So if your scenario really was
> as simple as this few of classes, then the data provider class and the
> factory class could become one.  I separate them because one provider
> may call multiple factories.  There is a factory for each custom entity
> type.  A provider may be a grouping of functionality that ultimately
> pulls from multiple tables.  The DbMap file contains constants for all
> the database entities so that if the database changes you have one
> place in your code to change for each table.
> 
> For a practical example of the above data classes, see the example
> classes in the documentation for my open source project, "xqs-provider"
> on sourceforge.
> 
> For an explanation of custom entities see this article:
> http://msdn.microsoft.com/asp.net/default.aspx?pull=/library/en-us/dnaspp/html/custentcls.asp
> 
> You can get the open source data component here:
> http://sourceforge.net/projects/xqs-data/
> 
> The help is on-line here:
> http://www.xquisoft.com/xqsdn/documentation/index.html
> See XQuiSoft.Data.DataManager, and XQuiSoft.Data.IDataFactory
> or
> http://www.xquisoft.com/xqsdn/documentation/XQuiSoft.Data.DataManager.html
> http://www.xquisoft.com/xqsdn/documentation/XQuiSoft.Data.IDataFactory.html
> 
> Please give feedback on the usefullness of the help files using the
> link at the bottom of each help file.  If more details are needed in
> the example, i will add it to the on-line documentation.
> 
> Michael Lang 
> XQuiSoft LLC 
> http://www.xquisoft.com/
> 
>

date: Wed, 15 Mar 2006 08:01:32 -0800 author: Julian Smith

Re: garbage collection and data connections

Thanks - that will certainly do the trick at the moment!
-- 
Julian


"Denis Nikolaev" wrote:

> The simplest solution is using option CommandBehavior.CloseConnection.
> 
> Example:
> sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
> 
> Description from MSDN:
> When the command is executed, the associated Connection object is closed 
> when the associated DataReader object is closed.
> 
> 
> 
> "Julian Smith"  ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × 
> ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ: 
> news:F2CCB3F7-F3C9-44AF-82F5-FB247546F036@microsoft.com...
> >I am starting to work with c# having several years of experience with vb6
> >
> > In the past I have used standard data access functions to either execute
> > stored procedures or retrieve recordsets.
> >
> > I would like to achieve something similar in c#. I can see that there are
> > uses for datasets but I would have thought it is often useful to rely on a
> > datareader mainly because it is more lightweight.
> >
> > The problem I have is as follows it I  use code such as
> >
> >    public static SqlDataReader GrabDR(string connectionString, string
> > queryString)
> >    {
> >        SqlConnection connection = new SqlConnection(connectionString);
> >        SqlCommand sqlCmd = new SqlCommand(queryString, connection);
> >        sqlCmd.Connection.Open();
> >        SqlDataReader sqlDreader = sqlCmd.ExecuteReader();
> >        //leave all destroying of opbjects to garbage collection
> >        return sqlDreader;
> >    }
> >
> > I become dependant on the .net garbage collection to release all used
> > resources once I go out of scope.
> >
> > It cannot see a way of passing a datareader back to the calling function 
> > if
> > it's connection has been closed beforehand.
> >
> > I am therefore concerned about the speed and scalability of using the 
> > above
> > function
> >
> > It could be that datasets are less cumbersome than I imagine - either way 
> > I
> > would appreciate some advice on this issue.
> > -- 
> > Julian 
> 
> 
>

date: Wed, 15 Mar 2006 08:01:32 -0800 author: Julian Smith