Reconnecting removed SqlConnection without rollback transaction after changing IP address

Question

I have several clients connecting to SQL Server over an untrusted (wireless / gprs) network and performing a large number of small queries and inserts in a few minutes. If the network connection is interrupted during the process, the entire transaction is rolled back and must be restarted. Due to business requirements, the process must be transactional (i.e., Other clients see either a complete set of data from other clients, or do not see it at all).

I would like to be able to detect when the connection is down, and to be able to reconnect to SQL Server and continue the process in the same transaction that was just deleted, and avoid restarting from the very beginning. sp_getbindtoken now, I'm using sp_getbindtoken right after opening the connection, set CommandTimeout to a small value (much less than TCP KeepAlive), and if I get a timeout during ExecuteNonQuery , I will open a new connection to the server and call sp_bindsession with a token from the very beginning of the process . Then I continue the process using a new session connection associated with the transaction of the previous process.

While it works almost perfectly, but according to MSDN , this API is outdated and will be removed in future versions of SQL Server. The question arises: how can I achieve the same results without these two teams? Is there any other way to resume a transaction from a dropped TCP connection?

Edit / Additional Information: The client application runs on Windows CE devices with barcode scanners. I provide both devices and software, so I can freely post anything I need there. The DB is hosted in a secure environment by a third party, and neither I nor the client control it. I have only 50 MB of daily sales data to send. I can use SP to save data, but it still needs to be passed, and one call to SP with one large argument is close to the 0% success rate for the GPRS / EDGE link.

Since the whole solution works in a production environment, I would like to keep the changes to a minimum. An alternative API with the same semantics as sp_bindsession would be ideal.

Answer 1

I just don’t buy that ~ 50 MB of daily sales data should be in one transaction. I buy that individual sales transactions need to be wrapped in sql transactions, but they will be more like 1K each. Are you sure you cannot perform several small transactions on the server in a stored procedure? If it should be all or nothing from each device, load the device into the staging table using small transactions. When the device is completed, use the stored procedure on the server in transactions to clear the staging tables. Or just put a boolean column at boot time, and then flip this flag in one update when the download is complete. A 50 MB transaction would indeed clog the transaction log and block other updates.

Answer 2

The MSDN link you offer suggests using MARS . According to the article:

MARS allows you to use a connection for both read and data operations (DML) with more than one pending operation. This feature eliminates the need for the application to fix busy errors. In addition, MARS can replace the user of server cursors, which typically consume more resources. Finally, since several operations can work with the same connection, they can use the same transaction context, eliminating the need for using the stored procedures of the sp_getbindtoken and sp_bindsession systems.

This way you can use BeginTrasaction, and the transaction will be automatically rolled back if you do not explicitly commit it. You can catch the commit statement and try to resubmit it recursively until the transaction returns successfully. Just an idea. Maybe something like this:

 private static void ExecuteSqlTransaction(string connectionString) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction; // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = connection; command.Transaction = transaction; try { command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"; command.ExecuteNonQuery(); command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"; command.ExecuteNonQuery(); // Attempt to commit the transaction. transaction.Commit(); //Both records are written to database. } catch (Exception ex) { // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. ExecuteSqlTransaction(connectionString); } } } }