Returning datareader question

[stricknyn]

Hi all,

Had a datareader question.

Return myDataReader

I have a function which is retuning a datareader to various sections of my website which is then being bound to dropdown lists, gridviews, etc. Normally the last line in a function when is the data reader and connection getting closed?

Is it best practive to return a data reader or a dataset/datatable (or something even better than these two)?

Thanks,

Strick

[gep13]

Hey,

If it were me, I would be returning a business object which represented the information that is in the reader. i.e. create a business object class with properties that represents all the information, and then loop through your data reader, creating an instance on this class. From there, you can then bind this list to the controls on your page. This means that you can leave the data reader where it belongs, in the DAL, rather than binding it directly to the UI.

Gary

[brin351]

I agree with Gary but for your info you can close the reader & connection from the calling function like follows

function getReader() as sqldatareader
'setup connection, command etc

myReader = myCommand.executeReader(commandBehavour.closeConnection)
return myReader
end function

dim reader as sqldatareader = getReader()
'use reader
reader.close() 'reader & connection destroyed

[Pradeep1210]

Don't use Data Readers if you want to pass data around in your application. They follow a connected architecture. They keep the connection open for their lifetime and and will burden your application as well as your database server. Moreover if you are opening each reader in a new connection, you can imagine the hell yourself. You won't be able to keep more than a specific number of users connected to your application.

Read data into a dataset using your Reader (or Data Adapter) and then pass that dataset around. Better way is still what gary told you already.

[brin351]

I not sure I get your logic Pradeep,

The web is disconnected so connections, data are built and destroyed each request unless you persist data in memory. Reader can be more efficent because they don't use memory where as datasets etc do.

The only correlation is if your repetadely getting the same data in a reader in one request, it would be more efficient to use a in memory object in that case.

[Pradeep1210]

The tip would be more applicable to VB.NET applications rather than ASP.NET web applications. Nevertheless it should be equally useful for web applications too. Ideally you should be passing data around instead of Data Readers or connections.

So how many requests will your application be able to handle simultaneously? Try a stress test both ways and see the result. I haven't tested this so can't comment much on this.

[gep13]

Ideally you should be passing data around instead of Data Readers or connections.

I think this is the heart of the matter, and what everything boils down to. At the end of the day, DataReader's, Connection, Commands, DataAdapters, (in my opinion) should stay in the DAL where they belong.

Gary

[brin351]

I love DALs until the customer starts changing the project requirements and I have to re-build it.

So how many requests will your application be able to handle simultaneously? Try a stress test both ways and see the result

A straight out reader will win out over datasets & DAL's with in memory objects in terms of resources used etc. There are lots of articles on this showing graphs and the like. That does not mean an adhoc approach to managing data is best, it's not it's terrable clutter of code on pages and hell to find runtime errors. Win server & IIS these days have heaps of resources and manage the app/connection pool very well so I agree with both of you - go the DAL. If a server didn't have enough resources to cope with my DAL I'd ask for 2 servers

[gep13]

If a server didn't have enough resources to cope with my DAL I'd ask for 2 servers

That is always a good plan as well. Thinking about the architecture of your planned installation always helps when thinking about code segregation as well.

Gary