Cancelling a call to stored procedure

Question

We have recently begin working on a new application, this application will be used in a very bandwidth limited environment. as such my designer has concerns with time taken to load data.

Currently we have a system as follows:

A Listview filled with Shipments, clicking on a shipment shows a side panel. The side panel has a button "Details" clicking on this will make a stored procedure call to load the selected shipment's details.

Now in our previous applications, this was not threaded, the loading time was so small as to cause no issues (being on an internal network). However with the new limitations we now thread the call to the procedure and show a loading animation, however my designer wants to add the ability to cancel the loading.

This is where i'm stuck, since the button calls a single method on my static Repository (LoadDetails) I cannot see a way to cancel this loading. Added to this is the fact the user could select a shipment click details, then select a second, third forth shipment clicking details on all of these, spooling multiple threads loading data.

The Repository LoadDetails is as follows:

private static bool LoadDetails(int shipmentId)
    {
        DataConnection dbCon = null;
        try
        {
            dbCon = ApplicationRoleService.EnableAppRole();
            if (dbCon.SqlConn == null)
            {
                return false;
            }

            SqlCommand cmd = new SqlCommand("LBN.sel_shipments_details");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = dbCon.SqlConn;

            SqlParameter paramShipmentId = new SqlParameter();
            paramShipmentId.ParameterName = "shipment_id";
            paramShipmentId.Direction = ParameterDirection.Input;
            paramShipmentId.Value = shipmentId;
            cmd.Parameters.Add(paramShipmentId);

            SqlParameter paramReturn = new SqlParameter();
            paramReturn.ParameterName = "@return_value";
            paramReturn.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(paramReturn);

            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adp.Fill(ds);

            if ((int)paramReturn.Value < 0)
            {
                //TODO: Log Error here.
                log.Error("Get Shipment Details Failed.");
            }

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                App.Current.Dispatcher.Invoke((System.Action)delegate
                {
                    Shipment s = GetShipment(shipmentId);
                    s.ShipmentDetails(
                          row["bankContact"] as string,
                          row["bankContactTel"] as string,
                          (row["containerCount"] == DBNull.Value) ? 0 : (int)row["containerCount"],
                          (row["sentToSiteComp"] == DBNull.Value) ? 0 : (int)row["sentToSiteComp"], 
                          (row["arrivedAtSiteComp"] == DBNull.Value) ? 0 : (int)row["arrivedAtSiteComp"],                                                          
                          (row["sentToPortComp"] == DBNull.Value) ? 0 : (int)row["sentToPortComp"],
                          (row["depostRecComp"] == DBNull.Value) ? 0 : (int)row["depostRecComp"]
                        );
                });
            }
            IoC.Get<IEventAggregator>().PublishOnUIThread(new ShipmentDetailsLoaded());
            return true;
        }
        catch (Exception ex)
        {
            log.Error("Unable to get ShipmentsList.", ex);
            return false;
        }
        finally
        {
            ApplicationRoleService.CloseConnection(dbCon);
        }
    }

How could i go about adding the ability to cancel this method from a the main thread?

You're creating a new thread for each LoadDetails? Can't you use thread.abort()? Or you want to cancel the stored procedure call? — artm, Oct 02 '14 at 11:06
If it is a bandwidth that a bottleneck, the most resource consuming operation would be DataAdapter.Fill (you have to read all the data by net); to make it async and let it be cancelled see http://stackoverflow.com/questions/2108917/sqldataadapter-fill-asynchronous-approach — Dmitry Bychenko, Oct 02 '14 at 11:07
`we now thread the call to the procedure` So cancel the thread. — Renatas M., Oct 02 '14 at 11:12

score 2 · Accepted Answer · answered Oct 02 '14 at 11:29

2

There's no reliable and safe way to forcibly cancel any background operation from .NET code, except running the operation in a separate process and kill the process (which isn't an universal solution). Especially, if that operation is running native code at the moment.

The best options you can do here are:

to split huge amount of data into chunks;
to follow graceful cancellation pattern in you code (see this and this)
to forget about result of current loading operation, if the user has pressed "Cancel" button (or done something similar, like leaving current record and positioning on next one).

I'd not rely on SqlCommand.Cancel. Thread.Abort also isn't an option.

answered Oct 02 '14 at 11:29

Dennis

37,026
10
82
150

Could you elaborate on why `Thread.Abort` and `SqlCommand.Cancel` are poor choices? – Ann L. Oct 02 '14 at 12:25
About `Thread.Abort`: http://stackoverflow.com/questions/1559255/whats-wrong-with-using-thread-abort About `SqlCommand.Cancel`: as it stated in docs, `Cancel` just *tries* to cancel. This try *could* be successful (and could be not, and there are objective reasons why). Moreover, there's no guarantee, that `Cancel` will end at time, that will fit your requirements. – Dennis Oct 02 '14 at 12:30

Cancelling a call to stored procedure

1 Answers1