Datareader, dataset, else?

[Patreek]

Hi,

I'm writing my first real asp.net app at my job, and I'd like opinions
please. In my classic ASP apps that I've written, I'd often have separate
files for retreiving data and returning the data to other pages that would
call it. I'd return the data as arrays so that I wouldn't have to have my
DB connections open all the time while the pages ran. Now in .net, it seems
like I have to choose between using a SQldatareader or a dataset. From what
I'm reading, I can see parallels between the sqldatareader and an classic
connected recordset, whereas a dataset seems to be kind of like a
disconnected recordset. But the dataset has lots of overhead.

So, it seems as thought if I use a function to return a datareader, I have
to leave the connection open. That seems sloppy as I'd wind up with
something like.

dataclass.Openconnection
thedatagrid.datasource dataclass.functionThatReturnsDatareader
thedatagrid.databind
dataclass.Closeconnection


I just feel that the open/close lines would get on my nerves after a while.

I could use a dataset instead, but I'm scared to use that after what I've
read about the unnecessary bloat. Does anyone have any examples of what you
do that you think is nice and clean and still keeps your database code
separate from your app?

Thanks!!!

 

[Frankie]

You can use a DataTable. A DataSet is simply a container for DataTable
objects. There is no rule that states a DataTable must exist inside of a
DataSet.

A DataTable is more closely analogous to a RecordSet from classic ADO.

A DataReader is most closely analogous to a Read-Only/Forward-Only Recordset
(IIRC "static recordset").

A DataSet can hold the equivalent of a full-blown relational database in
memory - complete with multiple tables related explicitly through
DataRelation objects within the DataSet.

You don't have to leave a connection open when you use a DataReader (it has
to be opened only while you're reading it). Upon opening it you can have it
automatically close the connection - like this:

SqlConnection myConnection = new SqlConnection(myConnectionString);
SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
myConnection.Open();
SqlDataReader myReader =
myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while(myReader.Read())
{
Console.WriteLine(myReader.GetString(0));
}
myReader.Close();
//Implicitly closes the connection because
CommandBehavior.CloseConnection was specified.

-HTH

 

[Patreek]

Thanks Frankie!

Patreek

You can use a DataTable. A DataSet is simply a container for DataTable
objects. There is no rule that states a DataTable must exist inside of a
DataSet.

A DataTable is more closely analogous to a RecordSet from classic ADO.

A DataReader is most closely analogous to a Read-Only/Forward-Only
Recordset (IIRC "static recordset").

A DataSet can hold the equivalent of a full-blown relational database in
memory - complete with multiple tables related explicitly through
DataRelation objects within the DataSet.

You don't have to leave a connection open when you use a DataReader (it
has to be opened only while you're reading it). Upon opening it you can
have it automatically close the connection - like this:

SqlConnection myConnection = new SqlConnection(myConnectionString);
SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
myConnection.Open();
SqlDataReader myReader =
myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while(myReader.Read())
{
Console.WriteLine(myReader.GetString(0));
}
myReader.Close();
//Implicitly closes the connection because
CommandBehavior.CloseConnection was specified.

-HTH