Dataset -> Collection

[Craig Lister]

Newish to c#, so go easy...

I have a data class that does all my database work..


One method is 'getData'. I pass it a string which contains the SQL string,
and it returns me a SqlDataReader. However, this means that where ever I
call this class from, I need to include 'using System.Data.SqlClient'.

I'd prefer to return something else, and it looks like a Collection is the
right idea... It looks like I could populate this object with my rows and
columns, and then pass it back, instead of the dataset object.

Is this possible?

How would I write my dataset to a collection, and return it instead ?

Thanks,
Craig

 

[Peter Bromberg [C# MVP]]

There are a couple of inconsistencies in your post. You say it returns
you a SqlDataReader, then in the last paragraph you say dataset. Bear
in mind that when a class method returns a SqlDataReader, you need to
iterate over that reader immediately and close it, or you've got a
SqlConnection tied up.

You might want to look into making your class return a colection of
structs or lightweight classes that represent the particular table of
data, It would not be difficult to write code that would blast through
your Datareader, populating these classes, then return a class with a
public ArrayList of these.

As a general rule, because of the Connection thing, I try to avoid
sending DataReaders outside of the data class and I think you are on
the right track. Or, you could create a DataTable out of the reader,
close the reader, and return the DataTable.

 

[Craig Lister]

There are a couple of inconsistencies in your post. You say it returns

you a SqlDataReader, then in the last paragraph you say dataset. Bear
in mind that when a class method returns a SqlDataReader, you need to
iterate over that reader immediately and close it, or you've got a
SqlConnection tied up..

Thanks! This was the first of my errors, as I was getting strange 'already
open' type errors... and I wasn't sure how to get past this...

You might want to look into making your class return a colection of
structs or lightweight classes that represent the particular table of
data, It would not be difficult to write code that would blast through
your Datareader, populating these classes, then return a class with a
public ArrayList of these.

This is exactly what I'm trying to do, I think..

As a general rule, because of the Connection thing, I try to avoid
sending DataReaders outside of the data class and I think you are on
the right track. Or, you could create a DataTable out of the reader,
close the reader, and return the DataTable.

As I am brand new to c# and .NET, I'm hoping to get guidance on this.
Returning a dataTable seems what I'd like, as then I can handle it like a
dataset, and hopefully refer to the fields by names... This would eliminate
the use of using the data controls outside my data object, as you say, and
seems cleaner and better use of OOP (Another thing I haven't used that much
in my past development career...).

Could you shed some light on the objects I'll be using to achieve the latter
advice you have given, as this seems like the best way to go..

Thanks,
Craig