People please stop passing DataReader to the presentation layer. If you want to use datareader object then use it to fill a custom collection and then pass the custom collection to the presentation layer where it can be bind to a control or whatever.
Check out this code:
public static SqlDataReader GetCategories()
{
string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand myCommand = new SqlCommand("SELECT * FROM Categories", myConnection);
SqlDataReader dr = null;
myConnection.Open();
dr = myCommand.ExecuteReader();
myConnection.Close();
return dr;
}
Now, use the GetCategories method.
protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = BL.GetCategories();
GridView1.DataBind();
}
But what happened!! You recieved the error: Invalid attempt to FieldCount when reader is closed.
The Reader is not able to run because the connection is closed. However you can solve this problem using the following code:
public static SqlDataReader GetCategories()
{
string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand myCommand = new SqlCommand("SELECT * FROM Categories", myConnection);
SqlDataReader dr = null;
myConnection.Open();
dr = myCommand.ExecuteReader();
// I ain't closing the connection
//myConnection.Close();
return dr;
}
But now we are not closing the connection. That's even bad than the error we had last time :).
Offcourse you can close the connection on the client side but thats not a good idea. Why does the SqlDataReader even reach the client side + the connection is still open. So basically we are wondering on the presentation layer with our connection open.
If you want to make good use of SqlDataReader then populate a custom collection with it and return the custom collection to the presentation layer. This way you are sending a disconnected entity to the user interface rather a connected entity.
powered by IMHO