Using datareaders with sqltransactions


I am using SqlTransactions in my code for roll back purpose. Within the transaction I got multiple statements to be executed with may include selects inserts and updates. All these statements are within the scope of the sqltransaction. Everything works fine just for one problem. I am using datareaders for select statements . And these readers are closed once they are used. This forces the connection to be lost and every thing fails. Does any one have a solution on whether I can use datareaders within a sqltransaction??


A DataReader will only close the connection if the CommandBehavior.CloseConnection option was set when calling ExecuteReader.

You should be OK if you avoid setting this option.


You should open the SqlConnection by itself.<br /> The SqlDataReader doesn't close the SqlConnection when you close the DataReader

For example:

using(SqlConnection cn = GetConnection()) { cn.Open(); SqlTransaction tr = cn.BeginTransaction("myTransaction"); ..... SqlCommand command = new SqlCommand(sqlString, cn); using(SqlDataReader reader = command.ExecuteReader()) { ..... } SqlCommand command1 = new SqlCommand(sqlString1, cn); using(SqlDataReader reader1 = command1.ExecuteReader()) { ..... } tr.Commit(); }


  • DataReader of SocketStream for UWP App
  • Can you use 2 'using' statements for both sqlconnection and sqldatareader?
  • DataReader outlive connection object
  • reading CSV file and inserting it into 2d list in python
  • How to read specific column and cell in mysql in c#?
  • Entity Framework : Set MySQL custom environment variables
  • How to iterate through a large SQL result set with multiple related tables
  • How to modify the set value of update command npgsql postgresql c#
  • Adding email subscription to Wordpress theme
  • Compare a .txt and .csv file and need to replace with matching name in .csv file to .txt
  • Using GLUT to simply print text
  • Parsing CSV in PHP and trying to keep line breaks in field contents
  • How to update powerpivot pivot table filter via cell reference?
  • VBScript to check for open process by user
  • Symfony2 redirect to https route fails (uses wrong port)
  • Unable to run SDL program in Eclipse but able to do so in Windows Explorer
  • ASPNetCore MVC Routing Let Server Handle Specific Route
  • C# fibonacci function returning errors
  • npm 5.4.1 install/uninstall all failing
  • OpenGL 3.3 on Mac OSX El Capitan with LWJGL
  • Jenkins: How To Build multiple projects from a TFS repository?
  • Sony Xperia Z Tablet not found by adb
  • How to recover from a Spring Social ExpiredAuthorizationException
  • Excel - Autoshape get it's name from cell (value)
  • Check if a string to interpolate provides expected placeholders
  • ILMerge & Keep Assembly Name
  • Running a C# exe file
  • Join two tables and save into third-sql
  • How to model a transition system with SPIN
  • Large data - storage and query
  • ORA-29908: missing primary invocation for ancillary operator
  • How can I estimate amount of memory left with calling System.gc()?
  • How to get next/previous record number?
  • WOWZA + RTMP + HTML5 Playback?
  • RestKit - RKRequestDelegate does not exist
  • Traverse Array and Display in markup
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • Converting MP3 duration time