Hey,
I've been trying to work this out for a while now. We have a reporting system that calls reports that can be pretty huge. Some users give up and close their browser thinking the process has failed. This leaves the query running on the server. What I want to do is kill the query when the user closes the browser.

I first thought I'd create an async db connection and poll the isCompleted flag like so:

SqlCommand cmdData = new SqlCommand("_Async_Sleep_Test_10");
cmdData.CommandType = CommandType.StoredProcedure;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["connstring_Async"].ConnectionString;
cmdData.Connection = conn;
conn.Open();
IAsyncResult asyResult = cmdData.BeginExecuteReader();
while(!asyResult.IsCompleted) {

	if(!Response.IsClientConnected) {

		cmdData.Cancel();
		conn.Close();
		Response.End();

	}

}
SqlDataReader drResult = cmdData.EndExecuteReader(asyResult);
DataTable dtResult = new DataTable();
dtResult.Load(drResult);
if(conn.State != ConnectionState.Closed) {
	conn.Close();
}

This works BUT the issue I have is our queries can have multiple DataTables so the Reader is no good. I need a DataSet.
Does anyone know how to do this with a DataSet that has multiple DataTables? I'd also consider other solutions.

Thanks,

Patrick

Patrick, since your last statement was open to other solutions, why not make the entire process much easier on the user. Streamline by creating a "DataMart" (a database optimized for reporting rather than OLTP). These very large reports could be precalculated during the ETL. Any dynamic columns could be added to the report on the fly based on the user's selection for the report. After that it is a simple select -- increasing performance drastically.

-Chris

Hi,

possible the following solution works. Use the DataReader's ReadNext method see

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.nextresult.aspx

for details. Possible you should think of another solution. generate the reports on a daily base at night or whatever. Also i wanted to mention, that you only can stop the database server from processing your sp at some point, so possible the database query will continue to run. Depending on the SQL you are executing.

But here is an example:

private void CloseConnection(SqlConnection conn) {
            if (conn.State != ConnectionState.Closed) {
                conn.Close();
            }
        }

        private void AddDataTable(SqlDataReader drResult, DataSet ds) {
            DataTable dtResult = new DataTable();
            dtResult.Load(drResult);
            ds.Tables.Add(dtResult);
        }

        private void LoadData() {

            SqlCommand cmdData = new SqlCommand("_Async_Sleep_Test_10");
            cmdData.CommandType = CommandType.StoredProcedure;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["connstring_Async"].ConnectionString;
            cmdData.Connection = conn;
            conn.Open();
            
            IAsyncResult asyResult = cmdData.BeginExecuteReader();
            while (!asyResult.IsCompleted) {

                if (!Response.IsClientConnected) {
                    cmdData.Cancel();
                    conn.Close();
                    Response.End();
                }

            }
            SqlDataReader drResult = cmdData.EndExecuteReader(asyResult);

            if (!drResult.HasRows) {
                CloseConnection(conn);
                return;
            }

            DataSet ds = new DataSet();
            // adds the current result
            AddDataTable(drResult, ds);

            // iterate through all availabe results
            while (drResult.NextResult()) {
                // add each result
                AddDataTable(drResult, ds);
            }
            
            // ds should now contain all datatables

            CloseConnection(conn);
        }

Thanks Guys,

I've gone the bernhardkircher's solution for now. I wasn't aware that the reader got more than one result back. We are planning on looking at some kind of data cube solution for long term.

I had to make some small changes to the code to get it to work. Turns out when the reader is loaded it advances the result set. If it is the last result in the set the reader is automatically closed. So I just changed the code to loop while the reader is open (!drResult.IsClosed).

IAsyncResult asyResult = cmdData.BeginExecuteReader();
while(!asyResult.IsCompleted) {

	if(!Response.IsClientConnected) {
		cmdData.Cancel();
		conn.Close();
		Response.End();
	}
}

SqlDataReader drResult = cmdData.EndExecuteReader(asyResult);

DataSet dsAllTables = new DataSet();

if(drResult.HasRows) {

	// iterate through all availabe results
	do {
		// add each result
		AddDataTable(drResult, dsAllTables);
	} while(!drResult.IsClosed);

}

// ds should now contain all datatables
if(conn.State != ConnectionState.Closed) {
	conn.Close();
}

Thanks again for your help. I was really stuck on this one.
Patrick