ObjectDataSource question

[Steven Blair]

I have the following code:

http://www.rafb.net/paste/results/6bi7v515.html

This is using an ObjectDataSource which directly binds to the GridView
in my app.
Is the connection closed for me, or do I have to close the connection
myself (and how could I do this?)

Another question.
Is it possible to cache this information, since its highly unlikely to
change therefore multiple users can use the cached version.
My ObjectDatSource method is in a seperate DLL from the Web app so I am
unsure if this dll can even access the Cache, or if this is a sensible
approach.

Thanks in advance.

Steven

 

[Pete Davis]

Steven,

This is pretty close, but there are a few issues:

1: You open the connection in the try handler, but you close in the catch.
This is a problem because if the open fails, then the close will fail as
well.

2: IDbCommand implements IDisposable, which you're ignoring.

A much better way to handl this would be to do the following:


using (cmd = new SqlCommand(SQLbuffer.ToString(), new
SqlConnection(GetConnectionString())))
{
try
{
cmd.Connection.Open();
return (IEnumerable)cmd.ExecuteReader();
}
catch (Exception e)
{
ErrorLog.WriteLine(CLASS_NAME,
MethodInfo.GetCurrentMethod().ToString(), e.Message);
return null;
}
}


The using statement will force the command to be disposed when it goes out
of scope. When the command is disposed, the connection will be closed (if
it's open), so you don't need the CommandBehavior for the ExecuteReader
call.


Pete

 

[Pete Davis]

Woops, my bad. I really blew that.

Command doesn't close the connection on dispose. You want to use the Using
on the connection. It should really look like this:

using (SqlConnection conn = new SqlConnection(GetConnectionString()))
{
cmd = new SqlCommand(SQLbuffer.ToString(), conn);
try
{
cmd.Connection.Open();
return (IEnumerable)cmd.ExecuteReader();
}
catch (Exception e)
{
ErrorLog.WriteLine(CLASS_NAME,
MethodInfo.GetCurrentMethod().ToString(), e.Message);
return null;
}
}

The connection willd efinitely get closed on disposal.

Sorry for the confusion.

Pete

 

[Steven Blair]

Thanks for that Pete.

Would it be possible to cache this data somehow?
I really only need to go to the Database once and after ever user could
use the same cached data.
I have managed this using a DataSet, but not sure how to go about doing
the same thing with a DataReader.

if its not possible, I think I need to keep the DataSet cached instead
of the DataReader example since I only hit the Database once.

 

[Pete Davis]

You're correct, you can't use the DataReader to cache the object. But you
can read the data into any data structure you want. You can certainly read
it into a dataset and hang onto the dataset.

The DataReader is directly associated with the connection and it's "live"
data. The DataSet is not associated with any back end data store or
connection, so it's not "live" data.

You could also just create an object to represent the data and copy the data
from the reader into an array of that object and hold onto the array. You
have all sorts of options.

Pete