Solved

Data Layer/DataReader

Posted on 2003-11-06
5
617 Views
Last Modified: 2012-05-04
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
Comment
Question by:Junoti
  • 4
5 Comments
 
LVL 22

Accepted Solution

by:
_TAD_ earned 250 total points
ID: 9697446
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
 
LVL 22

Assisted Solution

by:_TAD_
_TAD_ earned 250 total points
ID: 9697513

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
 
LVL 22

Expert Comment

by:_TAD_
ID: 9697526
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
 
LVL 22

Expert Comment

by:_TAD_
ID: 9697555


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
 
LVL 1

Author Comment

by:Junoti
ID: 9697561
Thanks _TAD_ for the extra comments! I'm always looking for "best practices" type information!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question