• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 629
  • Last Modified:

Data Layer/DataReader

I've created a data layer class, and have coded all my database connections, data objects, etc. within this class. I call a function from an ASP.NET page -- GetDataReader --- that returns a DataReader object. My understanding is that when using a DataReader object, the connection is not closed until the DataReader object is closed using CommandBehavior.Close. If I issue the close call within GetDataReader, my calling page fails because there no longer exists any data. How do I close the DataReader after it's been passed to the calling page without losing my data --- or is there a better way of implementing this?

Thanks
0
Junoti
Asked:
Junoti
  • 4
2 Solutions
 
_TAD_Commented:
First, you have some mis-conceptions.

A Database Connection is dstinctly different from a datareader.

Think of a datareader as a forward-only recordset or cursor.

When you close the datareader, you may still have an open connection, but the datareader is strongly linked to the dataconnection.  If you close the connection, you close the datareader.


As for your question... persisting data after closing your connection.

I suggest loading your data into a datatable.

You can think of a dataTable a dis-joint recordset that you can copy and pass along at will completely outside of an active data connection.

There are two easy ways to fill a dataTable from a database.

1) use a data reader
2) use a dataAdapter.

A datareader and a dataAdapter are used pretty much in the same way.  A dataReader is much faster, but a DataAdapter has a ton more functionality.
0
 
_TAD_Commented:

load a dataTable from a dataReader


public dataTable GetData()
{

//Create a Connection Object

//open connection

//Create a DataCommand Object and open the Data Reader Object


//Pseudo Code
DataTable dt = new DataTable();

for(int i=0,i<DataReader.Columns.Count;i++)
       dt.Columns.Add(dataReader[i].Name);

While(DataReader.Read())
{
    DataRow dr = Dt.newRow();

    for(int i=0,i<DataReader.Columns.Count;i++)
       dr[i] = DataReader[i].Value;

    dr.AddRow(dr);
}


return dt;

}
0
 
_TAD_Commented:
load a dataTable from a dataAdapter


public dataTable GetData()
{

//Create a Connection Object

//open connection

//Create a DataCommand Object and open the DataAdapter Object


DataTable dt = new DataTable();

DataAdapter.Fill(dt);

return dt;
}
0
 
_TAD_Commented:


Oh... and one last thing...


*just before* the "return dt" command you should close or dispose everything.


DataAdapter.Close();
DataReader.Close();
DataCommand.Dispose();
etc...



As for programming tactics...

As you can see the DataAdapter is much easier, but there is some extra overhead.

I leave it to you to decide which method to use, they both work equally well.

From my experience I used the DataAdapter for loading data that I wish to present and I use the DataReader when I do internal scans for the program or returning a single result.


That is, If I want to check for the presence of a particular value or record in a table in order for the program to do something, I use a datareader.  If I am going to display the data and have the user interact with it I use the dataAdapter.
0
 
JunotiAuthor Commented:
Thanks _TAD_ for the extra comments! I'm always looking for "best practices" type information!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now