Edit Question - KnowledgeHub

Title Body

I am executing several long-running SQL queries as part of a reporting module. These queries are constructed dynamically at run-time. Depending on the user's input, they may be single or multi-statement, have one or more parameters and operate on one or more database tables - in other words, their form cannot be easily anticipated. Currently, I am just executing these statements on an ordinary SqlConnection , i.e. using (SqlConnection cn = new SqlConnection(ConnectionString)) { cn.Open(); // command 1 // command 2 // ... // command N } Because these queries (really query batches) can take a while to execute, I am concerned about locks on tables holding up reads/writes for other users. It is not a problem if the data for these reports changes during the execution of the batch; the report queries should never take precedence over other operations on those tables, nor should they lock them. For most long-running/multi-statement operations that involve modifying data, I would use transactions. The difference here is that these report queries are not modifying any data. Would I be correct in wrapping these report queries in an SqlTransaction in order to control their isolation level? i.e: using (SqlConnection cn = new SqlConnection(ConnectionString)) { cn.Open(); using (SqlTransaction tr = cn.BeginTransaction(IsolationLevel.ReadUncommitted)) { // command 1 // command 2 // ... // command N tr.Commit(); } } Would this achieve my desired outcome? Is it correct to commit a transaction, even though no data has been modified? Is there another approach?

Tags (comma-separated) Cancel