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

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/

The simplest solution is using option CommandBehavior.CloseConnection.

Example:
sqlCommand.ExecuteReader(CommandBehavior.CloseConn ection);

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



"Julian Smith" <JulianSmith (AT) discussions (DOT) microsoft.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×
ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:F2CCB3F7-F3C9-44AF-82F5-FB247546F036 (AT) microsoft (DOT) com...

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 (AT) xquisoft (DOT) com" wrote:

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


"Denis Nikolaev" wrote: