Prevent Repeater.DataBind() from closing SqlDataReader RRS feed

  • Question

  • User-1458165390 posted

    I have a problem with binding data to two Repeaters using an SqlDataReader and its method; NextResult().

    The SqlDataReader is fetched from a middle-layer function. I use the "CloseConnection" CommandBehavior with the ExecuteReader() method on the SqlCommand object.
    When the presentation layer gets the SqlDataReader(dr), I bind it to two Repeaters, like in this simplified example:

    Repeater1.DataSource = dr;
    Repeater2.DataSource = dr;


    Now, I found out that calling the DataBind method on the first Repeater( Repeater1.DataBind(); ), automatically close the SqlDataReader, further closing the DB connection, because of the
    CloseConnection CommandBehavior on the SqlCommand object. The result of this is obvious; Repeater2 gets no data.

    Due to my architecture it's not an option to remove the CloseConnection CommandBehavior. (in case you wanna know, this is because I use a DAL that, in this case, return the SqlDataReader only. Such, using CloseConnection CommandBehavior is the only way of closing the DB connection, as the calling code has no access to the SqlConnection object. )

    So, Im interested to know if you have a solution to this problem.

    Im thinking one solution to this problem is to prevent the DataBind method from closing the SqlDataReader. I just dont know how.

    Also, I looked at the System.Data.Common.DbEnumerator class, which take an IDataReader and a boolean as parameters in its overloaded constructor. The boolean indicates whether to close the DataReader after iterating through the DataReader. Though, I can't use an instance of the DbEnumerator class as DataSource for a Repeater Control, as it doesn't implement the IEnumerable interface. Any solutions to this problem?

    Thank you.

    Tuesday, June 7, 2005 1:54 PM

All replies

  • User-1804824805 posted
    You could use a DataSet instead...
    Tuesday, June 7, 2005 2:28 PM
  • User-1458165390 posted

    Im only interested in using a connected model due to performance reasons.

    Tuesday, June 7, 2005 2:37 PM
  • User-1804824805 posted
    Given your restraints, I do not know how you can do what you are looking to do, other than possibly implementing your own class to manage the addition of items to your repeater.
    Tuesday, June 7, 2005 3:05 PM
  • User-1458165390 posted
    I haven't found a solution to this problem. Any suggestion are appreciated.
    Wednesday, June 8, 2005 3:02 PM
  • User-1630302068 posted
    I do not see how this would be useful in any way.  DataBind will suck all the data there is to suck out of a DataReader.  Since DataReaders are 'forward-only', you'd end up with a useless DataReader after DataBind if DataBind didnt close it.
    Thursday, June 9, 2005 8:40 AM
  • User-1458165390 posted

    I see it as a very usefull and effecient way to databind a number of controls with different result sets, with the performance given by SqlDataReader.
    It works perfectly if I exclude the CloseConnection behavior on the command object. But as said before, it's not an option due to app. architecture.

    The first databind method, which closes the SqlDataReader, iterates through the first result set only. Otherwise it would cause an error if it tried to databind from the second resultset, which contains completely different data columns. That said; the DataBind method does not iterate through all result sets in the data source, only the current result set.

    Thursday, June 9, 2005 10:53 AM
  • User-1804824805 posted
    His datareader has two resultsets.  That is the issue.  After the first is bound, the datareader is closed (even though there is another resultset waiting to be read).  As mentioned, he could easily get around this by using a DataSet instead, or less easily by manually adding rows to the repeater, but he has pronounced both of those options to be unacceptable.
    Thursday, June 9, 2005 10:57 AM
  • User-1790400708 posted

    Hi Kim,

    I ran into a similar issue on some legacy code belonging to a client (in which the connection was remaning open because the original developer called the data layer method with "CommandBehavior.Default", which kept it open). In my case, the original code was exactly what you're trying to do (ie, control1.DataSource = dr, control1.DataBind(), then dr.NextResult() and on to control2).

    I resolved my issue by just returning a DataSet and binding to the two tables, but if that won't work for you, is there a reason you can't just loop through the datareader yourself, and create a new anonymous object for each record, adding it to a System.Collections.Generic.List collection, and then bind to that?

    Something like:

    var tempList = new List<dynamic>();
    while (dr.Read())
         tempList.Add(new {prop1 = (string) dr["col1"], prop2 = (int) dr["col2"]});
    Repeater1.DataSource = tempList;
    Repeater2.DataSource = dr;

    You could probably just make the List of type object, actually, although dynamic works (I just tried it). Note that I put in dummy column names and cast to string and int as an example.

    OOPS!  I just realized this question is from 2005, d'oh!  Well, the method is still valid anyway, although back in 2005 you probably would have had to create an actual class or struct to hold the data since there were no anonymous types in C# then I think (?)


    Tuesday, June 25, 2013 3:03 PM
  • User899491172 posted

    cheers Kirk

    found myself in a similar situation, i'm a bit rusty with my c# coz i'm a front end dev now, searched the net for ages and your code sample was pretty much what i needed

    Thursday, January 23, 2014 3:25 AM