How to share transaction between classic ADO and ADO.Net

Level: Advanced

If you want to share a transaction between your ADO connection and your ADO.net connection, you can use bound connections. Now you might ask why would you ever want to do that, I was faced with a scenario where i was calling my .net modules from my existing VB code using COM interop. (I am planning to discuss COM Interop sometime soon!). So in my case VB/ADO was starting a transaction, then it calls a .net module to do something where I needed the data that was part of the VB/ADO transaction. So only way for me toto get to that data was by enlisting my ADO.Net calls in the same transaction.
Now to the point, we need to make 2 steps,
Step 1: Get a unique identifier for my ADO transaction using sp_getbindtoken
Step 2: Enlist my ADO.net transaction using that identifier and sp_bindsession
Note: You will need to pass the transaction identifier to your .net code

Example: VB Code:
Set cmd = New ADODB.Commands
SPName = "sp_getbindtoken"
cmd.CommandText = sSPName
cmd.ActiveConnection = oDBConnection
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@out_token", adVarChar, adParamOutput, 255)
cmd.Execute
transactionID = cmd.Parameters(0).Value

Pass this transactionID to .net code

In .Net side, (C#)

string spName = "EXEC sp_bindsession '" + sToken + "'";
SqlCommand cmd = new SqlCommand("EXEC sp_bindsession '" + sToken + "'",sqlConnection); cmd.CommandType = CommandType.Text;
try {
sqlConnection.Open();
cmd.ExecuteNonQuery();
cmd.Connection = null; }
catch (SqlException ex) { throw (ex); }

IMPORTANT!!!
When you are executing sp_bindsession, SQL Server somehow expects the T-SQL statement to be executed using the provider.If you user any other formats to execute this, it will not work!!!

Once you have completed these 2 steps, your ADO.Net transaction is enlisted in the parent ADO transaction and you will be able to see the data that was changes/inserted by the ADO within the ADO.Net process.

If you are using SqlHelper (Microsoft Data Access Application Block), you will have to modify the SqlHelper class to take this new transaction identifier as one of the parameter to SqlHelper methods and call sp_bindsession where ever you are using a transaction.