Hi AnikSol,
Based on my understanding, you are using TransactionScope class in your
WinForms application and within this transaction scope you pass multiple
SQL statements to the CommandText property of the SqlCommand object to
execute. If the SqlCommand's timeout period elapses prior to completion of
the operation, the transaction should be rolled back. However, the fact is
that the transaction is not rolled back. If I misunderstand you, please
feel free to correct me.
I have performed a test but I didn't reproduce the problem. I add a table
called TestTable in the SQL Server 2005. The table has two fields(ID int,
Name varchar(50)). I set up a WinForms applicaton and add a button on the
form. In the button's Click event handler, I set the CommandTimeout
property of the SqlCommand object to the value of 1 and then pass 10000
pieces of insert SQL statement to the SqlCommand.CommandText to insert
10000 records in the TestTable.
The following is the code of my test.
Imports System.Transactions
imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Using ts As TransactionScope = New TransactionScope()
Dim myConnection As New SqlConnection("server=.;Integrated
Security=SSPI;database=northwind")
Dim myCommand As New SqlCommand()
myCommand.CommandTimeout = 1
myCommand.Connection = myConnection
myConnection.Open()
For i As Integer = 1 To 10000
myCommand.CommandText = "insert into TestTable(ID,Name)
values(" + i.ToString() + "," + i.ToString() + ")"
myCommand.ExecuteNonQuery()
Next
myConnection.Close()
'Commit the transaction
ts.Complete()
End Using
End Sub
End Class
When the program is run and I click the button, an exception occurs saying
"" and the transaction is abored because no record is inserted into the
TestTable in the SQL Server at last.
In fact, as long as the Complete method of the TransactionScope is not
called within the transaction scope, the transaction won't be committed,
i.e. it will be rolled back.
Is there any difference between your code and mine? If so, could you please
show me your sample code, or make a sample project that could just
reproduce the problem and send it to me? To get my actual email address,
remove 'online' from my displayed email address.
I look forward to your reply.
Sincerely,
Linda Liu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
