19876

Using datareaders with sqltransactions

Question:

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??

Answer1:

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.

Answer2:

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(); }

Recommend

  • 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