Close SQLDataSource Connection

Question

I bind a SQLDataSource to a GridView using GridView.DataBind() and these queries often take a significant time to execute.

What code would cancel an in-progress DataBind()?

I can't seem to find any method to stop the DataBind() or even close the DB connection.

Rebind Attempt:

myConn.ConnectionsString = ""
myConn.SelectCommand = ""
myGrid.DataSource = Nothing
myGrid.DataBind()

Dispose Attempt

myConn.Dispose()
myGrid.Dispose()

Neither attempt actually closed the connection. Oracle still indicated it was still connected and executing the query.

Answer 1

You could manually bind the GridView and that would allow you to insert logic into the binding procedure to allow you to exit that procedure.

To do this subscribe to the onDataBinding event of the GridView.

You can read more about manually binding the GridView here: http://www.aarongoldenthal.com/post/2009/04/19/Manually-Databinding-a-GridView.aspx

Answer 2

How about addressing the problem from a different direction. Have you looked at optimizing the query, either by changing joins and subqueries, or simply by pulling less data, maybe based on other inputs on the page?

Answer 3

I agree with Dave, although I would suggest paging in this scenario. There are many variations, but my first approach here would be to limit the number of rows pulled back to a specific number at a time. Trying to make the connection, then break it if it dosen't respond in time is only going to frustrate your application users.

Here's one post that might help you get started.

http://stackoverflow.com/questions/711019/smart-paging-with-datagrid

Answer 4

using{} block suits this situations. use your connections with using block

Answer 5

Just close the Connection and free all the resource.

myConn.Close();
myConn.Dispose();