"Linda Liu [MSFT]" <v-l***@online.microsoft.com> wrote in message
news:JdAgTrZ$GHA.3164@TK2MSFTNGXA01.phx.gbl...
> 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.
>