Can't keep DataReader open and get new rows as they become available

Question

I would like to be able to execute a query only once and keep the resulting DataReader open all the time to get new rows as they are added to the database table. Something like this:

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from table";
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)){
    while (true){
        while (!reader.HasRows)
            Thread.Sleep(1000);
        if (reader.Read())
            LoadCurrentRow(reader);
    }
}

This does not work the way I would like it to. I only get the rows available at the time of executing the query. I do not get new rows I add while this code runs. The BeginExecuteReader/EndExecuteReader pattern seems more appropriate but produces the same result. Unless there's something I did not figure out...

Can someone help me make this work or suggest an alternative which does not involve periodically polling for new rows by running the query over and over?

Answer 1

You're right - this doesn't work. Nothing you change will make it work. A SqlDataReader does what it says on the tin - it reads data returned from a single query.

If you need to be notified when data in the database changes, I suggest looking into SQL Server Notification Services. If you can't use those for some reason (i.e. if you're running SQL Server 2000 or older), then you will have to periodically poll the database for changes.