ASP.NET MS SQL Insert Record(s) + Transaction

 

   ASP.NET MS SQL Insert Record(s) + Transaction (必要部分)
 
   Private Sub Create_Transaction_Insert()
 
      Dim connectionString1 As String = "Data Source=.\SqlExpress; Initial Catalog=book; Integrated Security=SSPI"
      Dim con1 As SqlConnection = New SqlConnection(connectionString1)
      con1.Open()
 
      Dim mssql_transaction As SqlTransaction = con1.BeginTransaction
      Dim sql_statement As String = "INSERT INTO dbo.test (a, b, c, d) VALUES (@a, @b, @c, @d)"
 
      Dim cmd As New SqlCommand(sql_statement, con1, mssql_transaction)
      cmd.Parameters.Add("@a", SqlDbType.Int)
      cmd.Parameters.Add("@b", SqlDbType.Int)
      cmd.Parameters.Add("@c", SqlDbType.Int)
      cmd.Parameters.Add("@d", SqlDbType.Int)
 
      Try
         For i As Integer = 1 To 100000
         cmd.Parameters("@a").Value = i.ToString.Trim
         cmd.Parameters("@b").Value = i.ToString.Trim
         cmd.Parameters("@c").Value = i.ToString.Trim
         cmd.Parameters("@d").Value = i.ToString.Trim
         cmd.ExecuteNonQuery()
         Next
 
         mssql_transaction.Commit()
      Catch ex As Exception
         mssql_transaction.Rollback()
      Finally
         con1.Close()
         con1.Dispose()
         mssql_transaction.Dispose()
         cmd.Dispose()
      End Try
 
   End Sub