Forum Moderators: open
Oh and assume that the connection string is set... that walks back to a propery that is initialized in the constructor... and errorCondition is the backing to a public property that the client uses when an error situation occurs.
here's the sample
class blog
{
public SqlDataReader TopPosts()
{
SqlDataReader sdReader;
SqlConnection mCnn = new SqlConnection(mConnectionString);
SqlCommand mCmd = new SqlCommand("blog_TopPosts", mCnn);
mCmd.CommandType = CommandType.StoredProcedure;
try
{
mCnn.Open();
sdReader = mCmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(System.Exception se)
{
errorCondition += "\nSP:blog_TopPosts failed and generated an error. Return value: " + se.ToString();
sdReader = null;
}
finally
{
mCnn.Close();
}
return sdReader;
}
}
blog mBlog = new blog();
SqlDataReader rd = mBlog.TopPosts();
I get a runtime error as soon as I try to read forward like this:
while (rd.Read())
The error: Invalid attempt to Read when reader is closed.
So if my method returns a SqlDataReader I'm forced to leave my connection open which I cannot do because of high volume on the database and I don't want to bind it to a DataGrid because the data unwinds much faster when I spin it out with my own html.
I also tried to force an automatic Dispose like this:
public SqlDataReader TopPosts()
{
SqlDataReader sdReader;
using(SqlConnection mCnn = new SqlConnection(mConnectionString))
{
using(SqlCommand mCmd = new SqlCommand("blog_TopPosts", mCnn))
{
mCmd.CommandType = CommandType.StoredProcedure;
try
{
mCnn.Open();
sdReader = mCmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(System.Exception se)
{
errorCondition += "\nSP:blog_TopPosts failed and generated an error. Return value: " + se.ToString();
sdReader = null;
}
}
}
return sdReader;
}
No dice... know why? According to the documentation:
While the SqlDataReader is in use, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.
That's what I believe I was attempting to do but passing back a SqlDataReader is causing loads of open connections on the database because it will only work if I pass the SqlDataReader back from the object without explicitly closing the connection in the object.
You code is very bad!
I know it can be difficult to move to asp.net from asp, but your code is wrong in a lot of places.
Try going to the offical asp.net site and using their example code as a starting point.
I am trying to be helpful but your code is so wrong their are no easy corrections to make :)
dregs33
In DotNetNuke they use the exact same thing and it works. The DAL returns a datareader. I've used it and it works find. The only difference is that I've used the Microsoft Data Application block to return the reader
I also found these VB.Net examples of doing the same thing like you did....it should work.
The MSDN documentation indicates to use CommandBehavior.CloseConnection to force the connection to close when using SqlDataReader with a command object and the MSDN has similar samples.
Too, Anders suggests calling 'Using' to force an automatic Dispose call.
It's actually very easy to move to .net, what's not easy is trying to have a low level discussion with someone that doesn't know what they're talking about.
I actually figured it out... it absolutely requires CommandBehavior.CloseConnection when executing the command or that connection will remain open for the default period or longer. So you cannot manually close the connection or force a dispose.
I looked at those samples too... notice that the methods that return SqlDataReader types don't explicitly close the connections. That's where one could get into connection difficulty if the behavior is not included when returning a SqlDataReader Type.
