How I Solved Using SQL Methods While Working Inside a Transaction

While debugging the Ggdag website, I ran into an interesting problem. The thing is, I use transactions there, and I was also using methods from the class MSStoredProceduresI.

When I tried to get the number of rows in a table, it threw this error: “ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.
The Transaction property of the command has not been initialized.” In other words, the SqlCommand must have its transaction set.

I don’t know yet whether it somehow checks the context, or whether, as long as there is just a single transaction running in the DB at that moment, I have to pass the transaction as well with all commands sent to it during that time, perhaps even as null.

Now to how I finally decided to solve it. As always, I had several options.

1. Don’t use transactions – this seemed to me the least suitable option, because even though I don’t really need transactions yet (and I could get by without them here too), that doesn’t mean I won’t make full use of them in the future.
2. Use transactions everywhere – this seemed like a fairly reasonable solution, but on the other hand I would have to specify almost everywhere null, and every call to the MSStoredProceduresI methods would have to be modified.
3. Create 1 class for transactions, where, for example, the transaction methods would end with “T” – This was one serious candidate, but in the end I decided on option 4)
4. Create 2 classes and put the methods I use in transactions into the 2nd one. – This is the option I chose. The hardest part for me was choosing the name of this second class, but in the end I went with MSTSP (I very often name classes with acronyms)